DataSimplePractical GuideLog inOpen DataSimple
DataSimple
Chapter 07dTransform and model

Streaming, materialized views and incremental tables

Back to the index · Previous: 7c. Python reference · Next: Pipelines and workflows

A streaming table keeps reading new data from a source; a materialized view is a stored query that is refreshed. DataSimple has both, as SQL commands, and keeps the whole path incremental:

  1. DataBridge brings the changes in, continuously or on a short schedule.
  2. Bronze keeps one current row per key, with delete markers.
  3. Streaming tables (CREATE STREAMING TABLE … FROM STREAM bronze.…) read only the Bronze rows that arrived since their last refresh, and append them or apply them by key.
  4. Materialized views keep heavy Gold results ready.
  5. A schedule or a pipeline refreshes them as often as you need, from every minute.

Which one to use#

You want Use Freshness
Inserts, updates and deletes from a database, as they happen DataBridge change-log CDC (SQL Server, PostgreSQL …) on a schedule Minutes
Messages from a Kafka topic, straight into a table CREATE STREAMING TABLE … FROM STREAM KAFKA('DS-20', 'orders') with a SCHEDULE CRON From every minute
Messages from a Kafka topic as a copy in the lake (Parquet, Delta, Iceberg) DataBridge Kafka (stream): each run reads from the stored offsets to the end, as a micro-batch Minutes
Files dropped in a folder DataBridge File drop: only new files are read Minutes
Rows changed since the last run DataBridge watermark load Minutes to hours
A Silver table that follows Bronze, reading only the new rows CREATE STREAMING TABLE … KEYS (…) … FROM STREAM bronze.… with a SCHEDULE CRON As often as its schedule
A log of every change, appended CREATE STREAMING TABLE … FROM STREAM bronze.… without KEYS As often as its schedule
A Silver table rebuilt from the whole of Bronze each run APPLY CHANGES INTO in a scheduled notebook, or DataBridge's Silver option As often as the schedule
A Gold result that is slow to compute CREATE MATERIALIZED VIEW … SCHEDULE CRON As often as its schedule
Several steps in order, Bronze to Gold A pipeline on a cron As often as the cron
A continuous Flink SQL job A Flink SQL step in a pipeline Seconds

Example: orders from a source database to a Gold report, every 15 minutes#

Step 1: bring the changes in. In DataBridge, load dbo.Orders with Change-log CDC, Bronze auto-apply on, and Repeat every 15 minutes (chapter 4). Every run copies only the changes; Bronze bronze.dbo_Orders keeps one row per order, with _op_type = 'D' for deletes.

Step 2: a Silver streaming table that reads only what is new. Run once in a SQL cell:

SQL
CREATE OR REFRESH STREAMING TABLE silver.orders_current
KEYS (OrderID) SEQUENCE BY _load_ts
APPLY AS DELETES WHEN _op_type = 'D'
STORED AS SCD TYPE 1
AS SELECT OrderID, OrderDate, Status, Amount, _op_type, _load_ts
   FROM STREAM bronze.dbo_Orders;

The first refresh reads all of Bronze. Each later refresh reads only the Bronze rows loaded since the one before; running it twice changes nothing.

Step 3: a Gold result kept ready.

SQL
CREATE MATERIALIZED VIEW IF NOT EXISTS gold.mv_orders_by_status_daily
AS SELECT CAST(OrderDate AS DATE) AS order_day, Status, COUNT(*) AS orders, SUM(Amount) AS amount
   FROM silver.orders_current
   GROUP BY CAST(OrderDate AS DATE), Status;

Step 4: run it in order. A pipeline with two steps on the cron */15 * * * * (chapter 8):

  • silver: a notebook with REFRESH STREAMING TABLE silver.orders_current;
  • gold: a notebook with REFRESH MATERIALIZED VIEW gold.mv_orders_by_status_daily;

Without the pipeline, give each its own SCHEDULE CRON; a pipeline keeps them in order.

Result: the report is at most about 15 minutes behind the source database, and every step only touches what changed.

The same in Python, with DS Tables#

Python
@ds.table("silver.orders_current", mode="merge", keys=["OrderID"], sequence_by="_load_ts")
def orders_current():
    return ds.read("bronze.dbo_Orders").filter("_op_type <> 'D'")

@ds.table("gold.orders_by_status", mode="overwrite")
def orders_by_status():
    return (ds.read("silver.orders_current")
              .groupBy("Status").agg(F.count("*").alias("orders"), F.sum("Amount").alias("amount")))

ds.run()

In merge mode a row only replaces a stored row with an older sequence, so a re-run is safe. See chapter 15.

Streaming tables in short#

SQL
CREATE STREAMING TABLE silver.orders_log
AS SELECT * FROM STREAM bronze.dbo_Orders;          -- append every new row

REFRESH STREAMING TABLE silver.orders_log;          -- the rows loaded since the last refresh
SHOW STREAMING TABLES;
DESCRIBE STREAMING TABLE silver.orders_log;         -- watermark reached, last refresh, schedule
  • The source is read by its _load_ts (or the column in WATERMARK BY): each refresh reads the rows above the value the last one reached.
  • With KEYS (…) the new rows are applied by key (updates, deletes, SCD type 1 or 2); without, they are appended.
  • A Kafka topic is read directly: FROM STREAM KAFKA('DS-20', 'orders', format => 'json'). Each refresh reads the messages since the offsets it reached, keeps them once in a landing table, and applies them.
  • All clauses and commands: 7b · Streaming tables and Reading a Kafka topic directly.

Materialized views in short#

SQL
CREATE MATERIALIZED VIEW gold.mv_sales_revenue_by_region_daily
SCHEDULE CRON '0 * * * *' TIMEZONE 'UTC'
AS SELECT region, order_date, SUM(net_amount) AS revenue FROM silver.sales GROUP BY region, order_date;

REFRESH MATERIALIZED VIEW gold.mv_sales_revenue_by_region_daily;

A cell that reads information_schema holds that one statement only, so check the refresh in its own cell:

SQL
SELECT * FROM datasimple.information_schema.materialized_views;     -- last refresh, next refresh
  • The result is a Delta table of the same name; reads are as fast as any table.
  • Names start with mv_, in gold or silver.
  • Every create, refresh and change is in the definition log.
  • All commands: 7b · Materialized views.

How fresh can it be#

Part Shortest interval
DataBridge schedule 5 minutes
Notebook schedule, pipeline, materialized view, streaming table 1 minute (a 5-field cron)
New files in a File drop Read when they have been unchanged for about 2 minutes
Flink SQL step Continuous

For most reports, 5 to 15 minutes is enough and costs far less than a job that never stops.

Read more#

Back to the index · Next: Pipelines and workflows