Views & Materialized Views

BoilStream supports three types of views on __stream DuckLake catalogs:

Syntax	Purpose	Output	When it runs
CREATE VIEW	Query-time SQL expansion	None (virtual)	On every query
CREATE STREAMING VIEW	Continuous row-by-row transform	Derived topic	On every row
CREATE MATERIALIZED VIEW ... WITH (...)	Windowed aggregation	Output topic	On window close

Streaming DuckLakes

Streaming and materialized views require a DuckLake catalog with the __stream suffix. Ordinary CREATE VIEW is also available in warehouse catalogs:

my_analytics__stream

Tables in __stream catalogs are streaming topics with real-time ingestion, automatic metadata column injection, and Parquet streaming to cloud storage.

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 VIEW

Plain CREATE VIEW passes through to DuckLake unchanged. The view is expanded at query time — no output topic, no streaming processing.

CREATE VIEW recent_events AS
  SELECT * FROM events WHERE ts > NOW() - INTERVAL '1 hour';

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.

CREATE MATERIALIZED VIEW

Materialized views aggregate data over time windows. They require a WITH clause specifying window parameters.

-- Tumbling window: 1-minute SUM/COUNT aggregation
CREATE MATERIALIZED VIEW sales_per_minute AS
  SELECT SUM(amount) AS total, COUNT(*) AS cnt
  FROM orders
  WITH (window_type='tumbling', window_size='1 minute', timestamp_column='ts');

-- Sliding window: 5-minute average sliding every 30 seconds
CREATE MATERIALIZED VIEW avg_price_5m AS
  SELECT AVG(price) AS avg_price, COUNT(*) AS cnt
  FROM trades
  WITH (window_type='sliding', window_size='5 minutes',
        slide_interval='30 seconds', timestamp_column='created_at');

Ingestion Timestamp Mode

If your source data doesn't have a timestamp column, you can omit timestamp_column to window by ingestion timestamp (__boils_meta_timestamp) instead of event time:

-- Windows based on when data was ingested by the server
CREATE MATERIALIZED VIEW events_per_minute AS
  SELECT COUNT(*) AS cnt
  FROM events
  WITH (window_type='tumbling', window_size='1 minute');

This is useful for sources without timestamps (Kafka streams, API events) or when ingestion time is the relevant time dimension.

WITH Parameters

Parameter	Required	Description
window_type	Yes	'tumbling' or 'sliding'
window_size	Yes	Window duration: '<N> <unit>' where unit is seconds, minutes, hours, or days
timestamp_column	No	Column used for windowing. If omitted, uses server ingestion timestamp (__boils_meta_timestamp)
slide_interval	Sliding only	How often the window advances. Must evenly divide window_size
output_topic	No	Output topic name. Defaults to {view_name}_matview

Output Schema

The output topic contains the columns from your SELECT plus two automatic columns:

• window_start — start of the aggregation window (TIMESTAMP)
• window_end — end of the aggregation window (TIMESTAMP)

Supported materialized-view operations

Materialized views support windowed aggregations, GROUP BY, scalar functions and CASE. An existing WHERE clause filters rows within the selected time window. Validate more complex queries with your server release before depending on them.

Dropping Materialized Views

DROP MATERIALIZED VIEW sales_per_minute;
DROP MATERIALIZED VIEW IF EXISTS avg_price_5m;

This stops window processing and deregisters the view. The output topic and its data remain.

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.

Next Steps

• Learn about DuckLake Integration for catalog management
• Explore the Ingestion Interfaces for data streaming
• Check out Getting Started for a complete setup walkthrough