Skip to content
BoilStream

Choose a view

concept
TL;DR

Choose a regular view for query-time logic, a streaming view for row-by-row filtering and transformation, or a materialized view for windowed aggregation. Streaming and windowed views require a streaming catalog. Create them before sending example input, and check derived results separately from source-write completion.

ComparisonChoose a view by when work runs and where results live
Choose a view by when work runs and where results liveRegular views expand when queried, streaming views transform arriving rows and materialized views aggregate time windows. Only the latter two write derived topics.Runs whenProducesRegular viewA query reads the viewQuery result; no outputtopicStreaming viewNew rows arriveTransformed rows in aderived topicWindowed viewA window closesAggregated rows in anoutput topicChoose a view by when work runs and where results liveRegular views expand when queried, streaming views transform arriving rows and materialized views aggregate time windows. Only the latter two write derived topics.Runs whenProducesRegular viewA queryreads theviewQueryresult; nooutput topicStreamingviewNew rowsarriveTransformedrows in aderivedtopicWindowedviewA windowclosesAggregatedrows in anoutput topic
Create continuous views before sending example input. Source-write completion does not mean derived output is ready.

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

Create a regular view

Section titled “Create a regular view”

Connect to an authorized catalog with DDL permission and create an events table with user_id BIGINT, event_type VARCHAR and ts TIMESTAMP columns before running this example. A regular view expands its query when read; it does not create an output topic.

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

Continuous processing

Section titled “Continuous processing”

Reading results and handling failures

Section titled “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.