DataSimplePractical GuideLog inOpen DataSimple
DataSimple
Chapter 15Transform and model

DS Tables: declare the tables, DataSimple builds them

Back to the index · Previous: Sarah: ask for a report

What DS Tables is#

DS Tables (DataSimple Tables) is how you build a set of lake tables by declaring them instead of scripting every step. For each table you say three things: what it is made from, how it is written (replace, add or merge on a key) and which quality rules it must pass. ds.run() then works out the order, builds each table after the tables it reads, applies the rules, writes through the governed path and registers every table in Unity Catalog.

You write it in a notebook with the ds helper (@ds.table, ds.read, ds.run). It plays the role of a declarative pipeline framework: it is DataSimple's own and is not tied to one format.

A DS Tables notebook is a small pipeline. Run it by hand, schedule it as a job, or put it in a Pipelines step.

How it works#

  1. Declare. @ds.table("silver.orders", mode=..., keys=..., expectations=...) on a function that returns a DataFrame. Nothing runs yet.
  2. Run. ds.run() goes through the declared tables. Before it builds one, it builds every declared table that one reads, so the order is always right, whatever order you declared them in.
  3. Read what was stored. Inside a run, ds.read("silver.orders") returns the table as it was stored: after its quality rules and after the merge. A downstream table never sees rows its source dropped or replaced.
  4. Write. overwrite replaces the table, append adds rows, merge upserts on keys (one row per key; with sequence_by, a change only replaces a stored row that is not newer). The first merge run creates the table with one row per key.
  5. Check. Quality rules run on each table before it is written: fail stops the run, warn reports and keeps the rows, drop removes the failing rows and, with a tag, keeps them in <table>__quarantine__<tag>.
  6. Report. Each table prints the rows stored. ds.run() returns {table: rows stored} in build order, and ds.status() shows each table's mode, whether it was built and what it read.

Every table is written as you, under your grants, and registered in the catalog with its comment, so it appears in the Lakehouse tree with lineage.

End-to-end example: orders to revenue by region#

A small shop: orders and customers arrive in Bronze from DataBridge. We build:

Table Made from How
silver.shop_customers bronze.shop_customers_raw change feed with history (SCD type 2), so a customer who moves region keeps both versions
silver.shop_orders bronze.shop_orders_raw one row per order, the latest change wins; an order without an amount is quarantined; a negative amount is reported
gold.shop_sales_by_region the two Silver tables revenue and order count by the customer's current region and day

Every output below is from a real run of this code on the DataSimple Spark cluster (under test table names; only the names differ).

Step 1 · The source lands in Bronze#

DataBridge normally does this (chapter 4). To try the example without a source system, paste this in a Python cell:

Python
orders = spark.createDataFrame([
    (1, "C1", 120.0, "2026-10-01", "2026-10-01 09:00:00", "I"),
    (2, "C2",  80.0, "2026-10-01", "2026-10-01 10:00:00", "I"),
    (3, "C1",  -5.0, "2026-10-02", "2026-10-02 08:00:00", "I"),   # negative amount
    (4, "C3",  None, "2026-10-02", "2026-10-02 09:00:00", "I"),   # no amount
    (2, "C2",  95.0, "2026-10-01", "2026-10-02 12:00:00", "U"),   # order 2 changed
], "order_id INT, customer_id STRING, amount DOUBLE, order_date STRING, updated_at STRING, _op_type STRING")
customers = spark.createDataFrame([
    ("C1", "Acme",    "West", "2026-09-01 00:00:00", "I"),
    ("C2", "Globex",  "East", "2026-09-01 00:00:00", "I"),
    ("C3", "Initech", "West", "2026-09-01 00:00:00", "I"),
], "customer_id STRING, name STRING, region STRING, updated_at STRING, _op_type STRING")
save_table(orders, "bronze.shop_orders_raw", mode="append")
save_table(customers, "bronze.shop_customers_raw", mode="append")

Step 2 · Declare the tables#

One cell. Declare the tables in any order: ds.run() builds each one after the tables it reads. Nothing to import: ds, F (Spark SQL functions), Window and T (Spark SQL types) are ready in every cell.

Python
ds.reset()   # start clean when you re-run the cell

# Silver customers: a change feed with history (SCD type 2).
ds.apply_changes("silver.shop_customers", "bronze.shop_customers_raw",
                 keys=["customer_id"], sequence_by="updated_at",
                 apply_as_deletes="_op_type = 'D'", except_columns=["_op_type"],
                 stored_as_scd_type=2)

# Silver orders: one row per order, the latest change wins.
@ds.table("silver.shop_orders", mode="merge", keys=["order_id"], sequence_by="updated_at",
          expectations=[{"name": "amount_present", "rule": "amount IS NOT NULL",
                         "action": "drop", "tag": "no_amount"}],
          comment="Orders, one row per order, latest change wins")
def shop_orders():
    df = ds.read("bronze.shop_orders_raw").filter("_op_type <> 'D'").drop("_op_type")
    return ds.expect(df, "positive_amount", "amount IS NULL OR amount >= 0", action="warn")

# Gold: revenue by region and day, from each customer's current region.
@ds.table("gold.shop_sales_by_region", mode="overwrite", comment="Order value by region and day")
def shop_sales_by_region():
    orders = ds.read("silver.shop_orders")
    regions = ds.read("silver.shop_customers").filter("__is_current").select("customer_id", "region")
    return (orders.join(regions, "customer_id", "left")
                  .groupBy("region", "order_date")
                  .agg(F.round(F.sum("amount"), 2).alias("revenue"), F.count("*").alias("orders")))

ds.run()
ds.status()

ds.apply_changes runs straight away (it writes as it goes). The two @ds.table declarations run in ds.run().

Step 3 · First run#

Text
ds: declared tables cleared
ds.apply_changes: silver.shop_customers versions +3 closed 0 (type 2)
=== ds.run: 2 table(s) declared ===
  building silver.shop_orders ...
[ds] Expectation 'positive_amount' failed on 1 rows (rule: amount IS NULL OR amount >= 0)
[ds] 1 rows -> silver.shop_orders__quarantine__no_amount
[ds] dropped 1 rows for 'amount_present'
  -> silver.shop_orders: 3 rows stored (merge)
  building gold.shop_sales_by_region ...
  -> gold.shop_sales_by_region: 3 rows stored (overwrite)
=== ds.run complete: 2 table(s) ===
Table                              Mode       Built     Reads
------------------------------------------------------------------------
gold.shop_sales_by_region          overwrite  yes       silver.shop_orders, silver.shop_customers
silver.shop_orders                 merge      yes       bronze.shop_orders_raw

What was stored:

silver.shop_orders: order 2 appears once, with its latest amount; order 4 is not here.

order_id customer_id amount updated_at
1 C1 120 2026-10-01 09:00:00
2 C2 95 2026-10-02 12:00:00
3 C1 -5 2026-10-02 08:00:00

silver.shop_orders__quarantine__no_amount: the order that failed the drop rule, kept for someone to fix.

order_id customer_id amount
4 C3 null

gold.shop_sales_by_region:

region order_date revenue orders
East 2026-10-01 95 1
West 2026-10-01 120 1
West 2026-10-02 -5 1

The negative order is kept because its rule is warn. Make it drop (with a tag) to keep it out of Gold too.

Step 4 · The next load lands#

A change to order 1, a new order, a late change to order 2 that is older than what Silver already has, and a customer who moved:

Python
save_table(spark.createDataFrame([
    (1, "C1", 150.0, "2026-10-01", "2026-10-03 09:00:00", "U"),   # order 1 changed
    (5, "C3", 200.0, "2026-10-03", "2026-10-03 10:00:00", "I"),   # new order
    (2, "C2",  70.0, "2026-10-01", "2026-10-01 11:00:00", "U"),   # late, older than Silver's row
], "order_id INT, customer_id STRING, amount DOUBLE, order_date STRING, updated_at STRING, _op_type STRING"),
    "bronze.shop_orders_raw", mode="append")
save_table(spark.createDataFrame([
    ("C3", "Initech", "East", "2026-10-03 00:00:00", "U"),          # Initech moved to East
], "customer_id STRING, name STRING, region STRING, updated_at STRING, _op_type STRING"),
    "bronze.shop_customers_raw", mode="append")

Step 5 · Run the same cell again#

Nothing in Step 2 changes. Run it again:

Text
ds.apply_changes: silver.shop_customers versions +1 closed 1 (type 2)
=== ds.run: 2 table(s) declared ===
  building silver.shop_orders ...
  -> silver.shop_orders: 4 rows stored (merge)
  building gold.shop_sales_by_region ...
  -> gold.shop_sales_by_region: 4 rows stored (overwrite)

silver.shop_orders: order 1 is updated, order 5 is new, and order 2 keeps 95 because the late change was older (sequence_by).

order_id customer_id amount updated_at
1 C1 150 2026-10-03 09:00:00
2 C2 95 2026-10-02 12:00:00
3 C1 -5 2026-10-02 08:00:00
5 C3 200 2026-10-03 10:00:00

silver.shop_customers for Initech: the West version is closed, the East version is current.

customer_id region __start_at __end_at __is_current
C3 West 2026-09-01 00:00:00 2026-10-03 00:00:00 false
C3 East 2026-10-03 00:00:00 null true

gold.shop_sales_by_region: order 5 counts under East, Initech's current region.

region order_date revenue orders
East 2026-10-01 95 1
East 2026-10-03 200 1
West 2026-10-01 150 1
West 2026-10-02 -5 1

Step 6 · Put it on a schedule#

Save the notebook with the Step 2 cell (leave out the Step 1 and Step 4 sample data). Then either:

  • Schedule it. Notebook → Schedule → daily at 06:00. Each run picks up whatever DataBridge loaded since (chapter 7).
  • Make it a pipeline step. Pipelines → add the notebook as a step after the DataBridge load, so it runs when the load finishes (chapter 8).
  • Ask in Everything. "Run my shop notebook every day at 6" proposes the schedule for your approval.

Star schema and One Big Table#

Three more declarations build a star schema in Gold, and a One Big Table (OBT) from it. They are DS Tables like the others: ds.run() builds them in the right order, after the tables they read.

Declaration What it builds
@ds.dimension(name, keys, scd=1 or 2, sequence_by=…) One row per natural key (type 1), or one row per version with __start_at, __end_at, __is_current (type 2). Adds a surrogate key (gold.dim_customer gets customer_key) that never changes once given, and an Unknown row with key -1.
@ds.fact(name, grain=[…], measures=[…], dimensions={fk: dim}, event_time=…) The fact rows with each foreign key swapped for the dimension's surrogate key. For a type 2 dimension the key is the version in force at event_time. A row whose key is not in the dimension gets -1 and is counted, never dropped.
@ds.obt(name, fact=…, as_of="event" or "current", columns=[…]) The fact joined to all its dimensions in one wide table. as_of="event" shows the values at the time of each row; "current" shows today's. Built from the star, so the two always agree.

Example: sales star#

Python
ds.reset()

@ds.dimension("gold.dim_customer", keys=["customer_id"], scd=2, sequence_by="updated_at", track=["name", "region"])
def dim_customer():
    return ds.read("silver.customers").select("customer_id", "name", "region", "updated_at")

@ds.dimension("gold.dim_product", keys=["product_id"])
def dim_product():
    return ds.read("silver.products").select("product_id", "product", "category")

@ds.fact("gold.fact_sales", grain=["order_id", "line_no"], measures=["qty", "amount"],
         dimensions={"customer_id": "gold.dim_customer", "product_id": "gold.dim_product"},
         event_time="order_ts")
def fact_sales():
    return ds.read("silver.order_lines")

@ds.obt("gold.obt_sales", fact="gold.fact_sales")
def obt_sales(df):
    return df            # or change it: add columns, rename, filter

ds.run()
ds.checks()

What a run did on our dev lake. Customer C3 moved from West to East on 3 October, and one order came from a customer the dimension does not know (c9):

gold.dim_customer customer_key customer_id region __start_at __end_at __is_current
-1 Unknown 1900-01-01 true
3 C3 West 1900-01-01 2026-10-03 false
4 C3 East 2026-10-03 true
gold.obt_sales order_id customer_id order_date region
99 C3 2026-09-15 West
5 C3 2026-10-03 East
3 c9 2026-02-11 Unknown
  • History starts from what the source holds. The first build of a type 2 dimension from a source with several rows per key (a Silver history table) keeps each change as a version, in sequence_by order. Its first version starts in 1900, so older facts still find a row.
  • Keys never move. A rerun with the same data changes nothing. A key that disappears from the source stays in the dimension, because facts may still point at it.
  • Rebuilding. Facts and OBTs are rebuilt on each run (mode="merge" on the grain is available for a fact in the Lakehouse).

Join checks#

Every build checks its joins and stops on anything that would make the numbers wrong:

Check On Fails the run when
one_row_per_key dimension two source rows share a natural key and no sequence_by says which wins
natural_key_present dimension (warning) rows have no natural key; they are left out
grain_unique fact two rows repeat the grain
join row count fact a lookup turns N rows into more than N (a dimension key that is not unique)
known_<fk> fact (warning) rows have a key not in the dimension; kept as -1, counted. Add max_unknown_ratio=0.05 to fail above 5%
rows_match_fact OBT the wide table has a different row count than the fact

The results print under the cell, come back from ds.checks(), and are recorded on the Data Quality page (suite join checks). A failed check raises an alert there.

Lakehouse or Data Lake#

Every declaration takes the same storage arguments:

  • target="lakehouse" (default): a managed table, format="delta" (default) or "iceberg".
  • target="lake", path="models/sales/dim_customer", format="parquet" (or "delta"): files under that path (a folder under the lake, or s3://bucket/prefix), registered in the catalog as an external table.

Grants, row filters, column masks and lineage apply to both.

From Everything, without code#

Ask build a star schema for the orders in silver.shop_orders, with a customer dimension that keeps history, and a One Big Table:

  1. The agent drafts the plan from the catalog: grain, measures, event time, and each foreign key matched to a table that looks like its dimension. It then corrects the draft with what you said.
  2. The approval card draws it: the fact in the middle, the dimensions around it with the key on each line, the OBT underneath.
  3. Approve, and it saves a DS Tables notebook (Star schema · Orders) and a pipeline that builds it. Nothing is built until you run the pipeline (ask run it) or open the notebook and run it.

Every table in an AI-made star must be a new gold.* name; an existing table is never replaced. Rebuilding a star that already exists is your action: run its pipeline or notebook.

Sarah reads OBTs first when they have the columns a question needs. Otherwise she joins the fact to its dimensions on the surrogate key, and she says which table answered.

Good to know#

  • Re-running is safe. merge with sequence_by and ds.apply_changes only apply changes newer than what is stored, so reading the whole Bronze table each run gives the same result as reading only the new rows.
  • Quarantine adds up. A drop rule with a tag appends the failing rows every run. When the body re-reads all of Bronze, the same bad row is quarantined again each run (order 4 is there twice after Step 5). Fix the source row, or read only new rows (for example ds.read(...).filter("_load_ts > ...")).
  • merge never deletes. It inserts and updates. For deletes, use ds.apply_changes(..., apply_as_deletes=...), as the customers table does.
  • overwrite replaces the table on every run. Use it for Gold summaries that are rebuilt from Silver.
  • Each table's function runs once per ds.run(). Outside a run, ds.read("silver.shop_orders") shows what the function returns, without writing anything: handy for checking a body before running.
  • Circular reads stop the run with the chain of tables, for example gold.a -> silver.b -> gold.a.
  • Sarah and the Agent never run a DS Tables rebuild that replaces data. A person runs it, under their own role (AI guardrail).

Reference#

Call What it does
@ds.table(name, mode="merge", keys=None, sequence_by=None, expectations=None, comment=None) Declare a table; the function returns its DataFrame. mode: overwrite, append, merge (needs keys).
ds.read(name) A declared table (built and stored first inside a run) or any catalog table, with your grants, row filters and column masks applied.
ds.expect(df, name, rule, action="fail", tag=None) A quality rule inside a function: fail, warn or drop (with tag, kept in <table>__quarantine__<tag>).
expectations=[{"name", "rule", "action", "tag"}] The same rules, declared on the table and applied before it is written.
ds.run() Build every declared table in dependency order; returns {table: rows stored}.
ds.status() / ds.reset() Show the declared tables, their mode, whether built and what they read / forget them all.
ds.apply_changes(...), ds.apply_changes_from_snapshot(...), ds.create_target_table(...) Change feeds to current-state (type 1) or history (type 2) tables: chapter 6.

The complete helper reference is in the developer guide. Notebooks written with the older dlt_* names keep working; they are aliases of ds.*. Not to be confused with the dlt Python library DataBridge uses for incremental extracts (DATABRIDGE-DLT.md).

Previous: 14. Sarah · Next: 16. Everything · Back to the index