Tumbling window
Fixed, non-overlapping windows. Use them for per-minute order totals, hourly telemetry counts, or daily usage summaries.
Streaming SQL
Build streaming materialized views with DuckDB SQL
Turn incoming events into tumbling or sliding window aggregates. BoilStream executes the SQL when each window closes and writes the results to an output table.
Does DuckDB support streaming materialized views?
DuckDB itself provides regular views but does not continuously maintain materialized views. BoilStream adds continuous ingestion and time-window aggregation to DuckDB SQL, so tumbling and sliding aggregates can be stored in output tables.
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 | Stored result table |
Tumbling and sliding windows
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 region, 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
- BoilStream selects the rows in the completed time window.
- Your SQL calculates the totals, averages, or grouped results.
- The results are stored in an output table, ready to query.
Because the query runs over window data, it can use DuckDB aggregations, GROUP BY, scalar functions, and CASE expressions. See the view guide for setup and window configuration.
Common streaming SQL patterns
Real-time dashboards
Pre-aggregate event streams into smaller output tables for dashboard queries.
Operational metrics
Calculate counts, rates, sums, and averages on event-time windows without maintaining a separate stream-processing application.
Derived pipelines
Filter rows with streaming views and aggregate windows with materialized views. Check downstream results and completion for the deployed release.
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 I group results by a business dimension?
Yes. Use GROUP BY to calculate metrics by region, product, device, or another column in your source data.
How do I use the results?
Query the output table with SQL and use the summaries in dashboards, reports, and applications.
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.