Transform arriving rows

tutorial

TL;DR

Create a streaming view to filter or transform each arriving row into a derived topic. Use a streaming catalog, create the source and view before sending input, and query the derived result separately. Aggregations, joins and operations requiring buffered rows are rejected; use windowed views for time-based aggregates.

Message sequenceA streaming view transforms arrivals into a separate topic

The source and derived topic have separate completion checks. Do not assume automatic backfill or exactly-once derived output.

Example setup

Connect through PGWire to a streaming catalog where you can create tables. Run these examples in a test schema. They use the following source tables:

CREATE TABLE events (user_id BIGINT, event_type VARCHAR, ts TIMESTAMP);
CREATE TABLE orders (amount DOUBLE, ts TIMESTAMP);
CREATE TABLE trades (price DOUBLE, created_at TIMESTAMP);

Create the views before sending input. Use current timestamps when trying event-time windows, and allow time for a window to close before expecting aggregate results.

CREATE STREAMING VIEW

Streaming views process every row as it arrives, applying filters and transformations continuously. Each streaming view has its own derived topic with hot and cold tiers.

-- Filtered stream
CREATE STREAMING VIEW click_events AS
  SELECT * FROM events WHERE event_type = 'click';

-- Transformed stream
CREATE STREAMING VIEW enriched_events AS
  SELECT
    user_id,
    event_type,
    UPPER(event_type) AS event_type_upper,
    DATE_TRUNC('hour', ts) AS event_hour,
    CASE
      WHEN event_type IN ('purchase', 'checkout') THEN 'conversion'
      WHEN event_type IN ('click', 'view') THEN 'engagement'
      ELSE 'other'
    END AS activity_category
  FROM events;

Supported streaming-view operations

Streaming views support SQL operations that can execute row-by-row without buffering:

Filtering: WHERE clauses
Projections: column selection, aliases
Transformations: CASE, UPPER(), LOWER(), DATE_TRUNC(), casts
Scalar functions: supported server functions that operate on a single row

Not supported in streaming views

These require buffering and are rejected at creation time with a clear error:

Aggregations (GROUP BY, COUNT(), SUM(), etc.)
Window functions (ROW_NUMBER(), LAG(), LEAD())
JOINs
Subqueries, CTEs
ORDER BY, DISTINCT, LIMIT/OFFSET
UNION / INTERSECT / EXCEPT

Use CREATE MATERIALIZED VIEW (below) for aggregations.

Error handling

Scenario	Result
Non-streaming SQL (`GROUP BY`, `JOIN`, etc.)	Error with specific reason
Base topic doesn’t exist	Error: `"Base topic 'X' does not exist"`
View name already exists	Error: `"Streaming view 'X' already exists"`
Not in a `__stream` catalog	Error: `"Streaming views require a __stream catalog"`

If creation fails, check whether the view exists before retrying.

Dropping streaming views

DROP STREAMING VIEW click_events;
DROP STREAMING VIEW IF EXISTS enriched_events;

DROP STREAMING VIEW fails if the view doesn’t exist. Use IF EXISTS for idempotent drops.

Reading results and handling failures

A regular view runs when queried. Streaming views transform arriving rows into derived topics; materialized views write results when their windows close. A successful source write does not mean those derived results are already available.

Do not assume materialized-view output triggers every live subscription or derived view. Check the intended workflow before building a chain of dependent outputs. After a failure, reconcile window results before replaying input; exactly-once output is not guaranteed.

Choose the query interface using the compatibility matrix. PGWire and managed DuckDB do not currently promise identical visibility of unpublished recent rows.

window_size must not exceed 80% of hot-tier retention. Keep enough retained data for the windows you intend to calculate.