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:
- DataBridge brings the changes in, continuously or on a short schedule.
- Bronze keeps one current row per key, with delete markers.
- 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. - Materialized views keep heavy Gold results ready.
- 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:
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.
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 withREFRESH STREAMING TABLE silver.orders_current;gold: a notebook withREFRESH 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#
@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#
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 inWATERMARK 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#
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:
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_, ingoldorsilver. - 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#
- Chapter 4 · Extract with DataBridge: CDC, Kafka, file drop and schedules.
- Chapter 6 · Silver: Apply changes: the rules and bookkeeping columns.
- Chapter 8 · Pipelines and workflows: steps, crons and Flink SQL.