Build streaming materialized views with DuckDB SQL

Turn incoming events into tumbling or sliding window aggregates. BoilStream executes the SQL when each window closes, persists completion watermarks, and routes the output back through the hot and cold data pipeline.

Read the complete syntax Try the Quick Start

Does DuckDB support streaming materialized views? DuckDB itself provides regular views but does not continuously maintain materialized views. BoilStream adds a streaming catalog and window executor around DuckDB SQL, so tumbling and sliding aggregates become derived topics with their own hot and cold tiers.

Choose the view that matches the job

BoilStream supports three SQL view types on catalogs whose names end in __stream. They share DuckDB SQL syntax but differ in when they run and whether they produce a derived topic.

View type	Best for	Execution	Output
`CREATE VIEW`	Reusable query logic	Expanded at query time	No stored output
`CREATE STREAMING VIEW`	Filter, project, and transform each row	Runs continuously as rows arrive	Derived topic with hot and cold tiers
`CREATE MATERIALIZED VIEW`	Aggregations over time windows	Runs when each window closes	Derived topic routed through the full ingestion path

Tumbling and sliding windows

Tumbling window

Fixed, non-overlapping windows. Use them for per-minute order totals, hourly telemetry counts, or daily usage summaries.

Sliding window

Overlapping windows that advance on a separate slide interval. Use them for rolling averages, moving rates, and recent anomaly signals.

Timestamp choice

Window by an event timestamp column or omit it to use BoilStream's ingestion timestamp metadata.

-- One result row per minute
CREATE MATERIALIZED VIEW sales_per_minute AS
  SELECT
    region,
    SUM(amount) AS revenue,
    COUNT(*) AS orders
  FROM order_events
  GROUP BY region
  WITH (
    window_type = 'tumbling',
    window_size = '1 minute',
    timestamp_column = 'event_time'
  );

-- Five-minute average, refreshed every 30 seconds
CREATE MATERIALIZED VIEW avg_price_5m AS
  SELECT AVG(price) AS avg_price, COUNT(*) AS samples
  FROM quote_events
  WITH (
    window_type = 'sliding',
    window_size = '5 minutes',
    slide_interval = '30 seconds',
    timestamp_column = 'created_at'
  );

What happens when a window closes

The executor detects a completed window boundary.
BoilStream runs the view's DuckDB SQL over the matching rows.
The result is inserted through the main ingestion pipeline into the derived topic.
The output receives the standard hot tier, Parquet cold tier, CDC, and downstream-view handling.
The completed watermark is persisted so a restart does not execute the same finished window again.

Because the query runs over window data, it can use DuckDB aggregations, GROUP BY, scalar functions, and CASE expressions. The configured window must fit within the source topic's retained hot data.

Common streaming SQL patterns

Real-time dashboards

Pre-aggregate event streams into dashboard-sized result topics and push updates to browsers with the SSE consumer.

Operational metrics

Calculate counts, rates, sums, and averages on event-time windows without maintaining a separate stream-processing application.

Derived pipelines

Filter and enrich rows with streaming views, aggregate them with a materialized view, then attach downstream views to the output topic.

Streaming materialized view FAQ

What is the difference between a streaming view and a materialized view?

A streaming view processes each row independently for filters, projections, and transformations. A materialized view runs a batch query over a completed time window and can aggregate many rows.

Can materialized-view output feed another view?

Yes. Materialized-view results re-enter the ingestion path, so the output topic can trigger CDC and downstream derived views.

What happens after a restart?

BoilStream persists completed window watermarks and resumes from that state, avoiding re-execution of windows already marked complete.

Do I need an event timestamp column?

No. You can omit timestamp_column to window by BoilStream's ingestion timestamp. Provide an event-time column when source timestamps should determine the windows.