DataSimpleDeveloper HandbookLog inOpen DataSimple
DataSimple
Chapter 21Shape and govern data

Declarative tables and change handling

Build Silver and Gold by declaring tables instead of scripting every step: @ds.table and ds.run, quality rules with quarantine, ds.apply_changes and APPLY CHANGES for Type 1 / Type 2 history, materialized views, and star schemas with a one big table.

Overview#

DS Tables (DataSimple Tables) is the notebook helper ds, available in every Python cell of the Spark session. You declare each table: 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() builds the tables in dependency order, writes them through the governed path, registers them in the catalog and records lineage. A notebook with declarations is a small pipeline: run it by hand, schedule it, or make it a Pipelines step.

Change handling (slowly changing dimensions) is part of the same helper: ds.apply_changes keeps a current-state (Type 1) or history (Type 2) table from a change feed, and APPLY CHANGES INTO … is its SQL form.

Who uses it: data engineers building Silver and Gold. Where: Notebooks (Python and SQL cells), Schedules, Pipelines. For the platform map see Architecture; for the Spark session these helpers run in, see Spark engine.

Key concepts#

Term Meaning
Declaration @ds.table("silver.orders", …) on a function that returns a DataFrame. Nothing runs until ds.run().
Dependency order ds.run() builds every declared table a body reads (through ds.read) before the table itself. Circular reads stop the run with the chain.
Write mode overwrite (replace), append (add rows), merge (upsert on keys; with sequence_by, a change with the same or a newer sequence replaces a stored row). Default merge.
Expectation A quality rule: 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>.
SCD Type 1 One row per key, current state; deletes remove the row.
SCD Type 2 One row per version with __start_at, __end_at, __is_current, __op; deletes close the current version.
sequence_by The column or expression that orders changes. An older change never overwrites newer state. In ds.apply_changes a change must be strictly newer to apply; in merge mode a change with the same sequence also replaces the stored row.
Materialized view A stored query result (a Delta table of the same name) refreshed on demand or on a cron schedule.
Star schema / OBT @ds.dimension, @ds.fact, @ds.obt: dimensions with surrogate keys and history, a fact looked up as of the event time, and one wide table built from the star.

How it works#

Declarative tables: Bronze, change handling, Silver Type 1 and Type 2, declare and run, Gold
Declarative tables: Bronze, change handling, Silver Type 1 and Type 2, declare and run, Gold
  1. A Python cell runs in your Spark session, where the ds helper is always available.
  2. A SQL cell is sent to DataSimple. APPLY CHANGES runs as a ds.apply_changes call on the same session. MERGE CHANGES runs as a Delta MERGE. Materialized view statements create, refresh and schedule the stored result.
  3. Every table is read as you (grants, row filters, masks), written under your grants, stamped with _tenantid, registered in the catalog, and logged in the definition log with method ds_tables or apply_changes. Each built table prints a lineage line, and exactly those edges appear in Lineage.

Declare and run#

Python
@ds.table(name, mode="merge", keys=None, sequence_by=None, expectations=None, comment=None)
def body():
    return ds.read("bronze.public_orders")      # any DataFrame
Call What it does
ds.read(name) Inside a run: a declared table, built and stored first (after its rules and merge), or any catalog table, governed. Outside a run: shows what the body returns, without writing.
ds.expect(df, name, rule, action="fail", tag=None) A rule inside a body.
expectations=[{"name", "rule", "action", "tag"}] The same rules on the declaration, applied before the write.
ds.run() Builds every declared table in dependency order; returns {table: rows stored}. Each body runs once per run.
ds.status() / ds.reset() / ds.checks() Show declarations, mode, built flag and reads / clear them / show the join checks of the last run.

Rules to remember:

  • merge never deletes. It inserts and updates. Use ds.apply_changes(apply_as_deletes=…) for deletes.
  • Re-running is safe with merge + sequence_by and with ds.apply_changes: older changes never overwrite newer data.
  • Quarantine adds up. A drop rule with a tag appends failing rows every run. If the body re-reads all of Bronze, the same bad row is quarantined again.

ds.apply_changes#

Python
ds.apply_changes(target, source, keys, sequence_by,
                 apply_as_deletes=None, stored_as_scd_type=1,
                 except_columns=None, track_history_except_columns=None,
                 history_table=None, track_sequence_changes=False)
Argument Meaning
target Catalog name (silver.customers; datasimple. accepted). Created on the first call (Delta by default) and registered.
source A catalog name (read with read_table, governed) or a DataFrame.
keys, sequence_by Required.
apply_as_deletes SQL boolean over the source marking deletes, for example "_op_type = 'D'".
stored_as_scd_type 1 current state, 2 history.
except_columns Source columns not stored. May not name _tenantid.
track_history_except_columns Type 2: a change only in these columns opens no version.
history_table Type 1 only: also keep a Type 2 table of that name.
track_sequence_changes Type 2, default False: when sequence_by is a column, a row re-sent with identical data and only a newer sequence opens no version. True records every re-send as a version. A sequence_by expression is never excluded.

The merge rules:

Incoming change Type 1 Type 2
Unknown key, not a delete inserted new version, __op = I
Known key, newer sequence row updated current version closed, new version __op = U
Known key, newer sequence, delete row removed current version closed, nothing opened
Known key, older or equal sequence ignored ignored
Several changes for one key in one batch last one wins every change kept in sequence order
Re-run of the same batch no change no change

A change without a sequence value never replaces stored data. Type 1 still inserts it when its key is new. Type 2 does not apply it and prints a warning in the cell output. _tenantid is a system column: a change only in it opens no version.

Type 2 is one MERGE. Closing the current versions and inserting the new ones is a single Spark SQL MERGE, so the table is never left with a closed version and no replacement. Changes older than the stored history are reported, not applied. If the single MERGE cannot run, it fails before writing anything and DataSimple falls back to a two-step update on the last change per key, with a warning in the cell output.

ds.apply_changes_from_snapshot(target, snapshot, keys, stored_as_scd_type=1, snapshot_version=None) works from a full copy instead of a feed: a key missing from the snapshot is removed (Type 1) or closed (Type 2); a row equal in every non-key column is untouched. ds.create_target_table(name, comment=None, columns=None, format="delta") creates a target ahead of time (idempotent).

Bookkeeping columns: __sequence, __updated_at (Type 1); __start_at, __end_at, __is_current, __op (Type 2). A target created earlier without them gets them on the first apply.

SQL: APPLY CHANGES#

The SQL form of ds.apply_changes, for interactive and scheduled SQL cells. FROM takes a table or a (subquery); clauses after it may come in any order. The result is one summary row.

SQL
APPLY CHANGES INTO silver.customers
FROM bronze.public_customers
KEYS (customer_id)
SEQUENCE BY updated_at
APPLY AS DELETES WHEN _op_type = 'D'
STORED AS SCD TYPE 2
EXCEPT COLUMNS (_op_type)
TRACK HISTORY EXCEPT COLUMNS (last_login);

APPLY CHANGES FROM SNAPSHOT INTO silver.customers_current
FROM source.dbo_customers
KEYS (customer_id)
STORED AS SCD TYPE 1
SNAPSHOT VERSION 20261027;

There is no SQL clause for history_table or track_sequence_changes; use the Python call for those.

SQL: MERGE CHANGES (older grammar)#

MERGE CHANGES / MERGE SNAPSHOT is an older grammar that runs as a Delta MERGE. It still works and autocomplete still offers it, but it may be retired in a future release. Prefer APPLY CHANGES in new code.

SQL
MERGE CHANGES INTO silver.customers
FROM bronze.customers_cdc
KEYS (customer_id)
SEQUENCE BY updated_at
DELETE WHEN _op_type = 'D'
IGNORE COLUMNS (_op_type)
STORED AS SCD TYPE 1;          -- or 2 (history in __start_at / __end_at)

MERGE SNAPSHOT INTO silver.customers
FROM src_customers_today
KEYS (customer_id)
DELETE MISSING
MIN SOURCE ROWS 1000;

Things to know:

  • The target must exist first. APPLY CHANGES creates it; MERGE CHANGES does not.
  • Audit columns. MERGE CHANGES adds the DataBridge audit columns (_op_type, _cdc_seq, _source_ts, _load_ts, _batch_id, _row_hash, _is_deleted and others) to the source and adds missing ones to the target. IGNORE COLUMNS does not remove those audit columns. The DELETE WHEN rule reads the source's own marker.
  • Different column names. MERGE CHANGES history uses __start_at / __end_at only; ds.apply_changes also writes __is_current and __op.

Materialized views#

SQL
CREATE MATERIALIZED VIEW gold.mv_sales_revenue_daily
  SCHEDULE CRON '30 7 * * 1-5' TIMEZONE 'Asia/Kolkata'
AS SELECT c.region, o.order_date, SUM(o.amount) AS revenue, COUNT(*) AS orders
     FROM silver.orders o
     JOIN silver.customers c ON c.customer_id = o.customer_id AND c.__is_current
    GROUP BY c.region, o.order_date;

REFRESH MATERIALIZED VIEW gold.mv_sales_revenue_daily;
ALTER MATERIALIZED VIEW gold.mv_sales_revenue_daily SET SCHEDULE CRON '0 * * * *';
ALTER MATERIALIZED VIEW gold.mv_sales_revenue_daily DROP SCHEDULE;
DROP MATERIALIZED VIEW gold.mv_sales_revenue_daily;     -- soft drop, like a table
  • The query is one read-only SELECT or WITH. The result is a Delta table of the same name.
  • Names: <gold|silver>.mv_<snake_case>, at most 60 characters. A bad name is refused with a corrected suggestion.
  • Creating needs CREATE_TABLE; refresh and schedule changes need MODIFY or ownership; drop follows the table rules.
  • An existing table with the same name is never replaced.
  • If the first build fails (a column the query names is missing, for example), the view is still saved with last_status = 'error', and a second CREATE MATERIALIZED VIEW of the same name says it already exists. Correct the query with CREATE OR REPLACE MATERIALIZED VIEW.
  • A scheduled refresh runs as the owner and needs scheduling to be enabled on your platform (ask your administrator).
  • Freshness: information_schema.materialized_views (last_refreshed, next_refresh, last_status, row_count, refresh_ms).

Star schema and one big table#

Declaration Builds
@ds.dimension(name, keys, scd=1 or 2, sequence_by=None, track=None, surrogate_key=None, unknown=True, target="lakehouse", path=None, format=None) One row per key (Type 1) or per version (Type 2). Adds a surrogate key (gold.dim_customer → customer_key) that never changes once given, and an Unknown row with key -1. The first Type 2 version starts in 1900 so older facts still find a row.
@ds.fact(name, grain=[…], measures=[…], dimensions={fk: dim}, event_time=…, mode="overwrite", max_unknown_ratio=None) Fact rows with each foreign key swapped for the dimension's surrogate key; for Type 2 the version in force at event_time. No match gives -1 and is counted.
@ds.obt(name, fact=…, as_of="event" or "current", columns=None) The fact joined to its dimensions in one wide table.

Join checks run on every build and stop the run on anything that would make numbers wrong: one_row_per_key (dimension; a failure without sequence_by, only a warning with it, because rows sharing a key are then kept as history or as the latest), natural_key_present (warning), grain_unique (fact), join row count (a lookup must not multiply rows), known_<fk> (warning, or failure above max_unknown_ratio), rows_match_fact (OBT). Results print under the cell, come back from ds.checks(), and are stored on the Data Quality page (suite join checks), where a failure raises an alert.

Storage: target="lakehouse" (managed Delta, or Iceberg with format="iceberg") or target="lake", path=…, format="parquet"|"delta" (files at the path, registered as an external table).

Business view. After a successful ds.fact or ds.obt build, DataSimple creates or refreshes one business view for the star in Gold, for example gold.retail_sales_view: the One Big Table when there is one, otherwise the fact joined to its dimensions, without surrogate keys or SCD columns, with its meaning saved (grain, measures and whether they add up, date column, dimensions). Sarah answers from these views. Reads of a view go through its tables, so their grants, row filters and masks apply. The view is built as the person who ran the build; a build that is not allowed to change the table does not change its model. See Assistants.

REST API#

Base URL https://<your-datasimple-host>, header Authorization: Bearer <PERSONAL_TOKEN>.

Method Path Purpose
POST /api/notebooks/spark/run Run Python code ({ notebookId, cellId, code, params })
POST /api/notebooks/spark/sql Run SQL ({ notebookId, cellId, sql, previewRows })
POST /api/notebooks/spark/run-notebook Run a whole notebook

Developer examples#

1. Silver and Gold in one cell#

Python
ds.reset()

ds.apply_changes("silver.customers", "bronze.public_customers",
                 keys=["customer_id"], sequence_by="updated_at",
                 apply_as_deletes="_op_type = 'D'", except_columns=["_op_type"],
                 stored_as_scd_type=2)

@ds.table("silver.orders", mode="merge", keys=["order_id"], sequence_by="updated_at",
          expectations=[{"name": "amount_present", "rule": "amount IS NOT NULL",
                         "action": "drop", "tag": "no_amount"}])
def orders():
    return ds.read("bronze.public_orders").filter("_op_type <> 'D'").drop("_op_type")

@ds.table("gold.sales_by_region", mode="overwrite", comment="Revenue by region and day")
def sales_by_region():
    o = ds.read("silver.orders")
    c = ds.read("silver.customers").filter("__is_current").select("customer_id", "region")
    return o.join(c, "customer_id", "left").groupBy("region", "order_date") \
            .agg(F.round(F.sum("amount"), 2).alias("revenue"), F.count("*").alias("orders"))

ds.run()        # builds silver.orders, then gold.sales_by_region
ds.status()

2. Star schema#

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.fact("gold.fact_orders", grain=["order_id"], measures=["amount"],
         dimensions={"customer_id": "gold.dim_customer"}, event_time="order_ts")
def fact_orders():
    return ds.read("silver.orders")

@ds.obt("gold.obt_orders", fact="gold.fact_orders")
def obt_orders(df):
    return df

ds.run()
ds.checks()

Example result, for two customers where Ann moved from North to East between her two orders: gold.dim_customer has 4 rows (Unknown -1; Ann North from 1900 to the move date; Ann East current; Bob South); gold.fact_orders has 2 orders, each carrying the customer_key in force at the order time (the order after the move gets Ann's East key); gold.obt_orders joins order, amount, name and region. All join checks pass, a re-run changes nothing, and Lineage shows Silver → dimension and fact → OBT.

3. Type 2 with re-sends#

Python
r = ds.apply_changes("silver.customer_history", "bronze.public_customers",
                     keys=["customer_id"], sequence_by="seq", stored_as_scd_type=2)
print(r)    # {'target': 'datasimple.silver.customer_history', 'type': 2, 'versions_added': …, 'closed': …}

Example: three batches containing two same-data re-sends give 4 history rows with the default and 6 with track_sequence_changes=True.

4. Run a SQL apply through the API#

JavaScript
const res = await fetch("https://<your-datasimple-host>/api/notebooks/spark/sql", {
  method: "POST",
  headers: { "Content-Type": "application/json", Authorization: "Bearer <PERSONAL_TOKEN>" },
  body: JSON.stringify({
    notebookId: 42, cellId: "apply-orders",
    sql: `APPLY CHANGES INTO silver.orders_current FROM bronze.public_orders
          KEYS (order_id) SEQUENCE BY _load_ts
          APPLY AS DELETES WHEN _op_type = 'D'
          STORED AS SCD TYPE 1 EXCEPT COLUMNS (_op_type)`,
  }),
}).then(r => r.json());
// { success, df: { columns, rows }, rowCount, stdout, error, durationMs, trace, notes? }

Security and permissions#

  • Reads go through read_table: grants, row filters, column masks and the tenant filter apply.
  • Writes need MODIFY on the target, or CREATE_TABLE on the schema the first time.
  • Tenant column is stamped on every write; except_columns and EXCEPT COLUMNS may not remove _tenantid.
  • Definition log entries use method ds_tables or apply_changes (or scheduler in a scheduled run).
  • AI guardrail: Sarah and the Agent never run a rebuild that replaces data. An AI-made star must use new gold.* names; a person rebuilds an existing one.

Monitoring and troubleshooting#

  • Each table prints rows stored; ds.run() returns {table: rows}; ds.apply_changes prints +inserted ~updated -deleted or versions +n closed m.
  • Lineage shows the exact edges each body read. Data Quality shows join checks and quarantine counts. History shows each Delta version a run wrote.
Message Meaning
ds.apply_changes: keys is required … / sequence_by is required … Pass both.
ds.apply_changes(…): key column(s) … are not in the source Check the source columns.
… _tenantid is a protected column … (except_columns cannot remove it) Remove _tenantid from except_columns.
WARNING the single-MERGE history update … could not run … DataSimple fell back to the two-step update; the result is correct. Tell your administrator if it repeats.
Circular read error with a chain (gold.a -> silver.b -> gold.a) Break the cycle.
DELTA_MERGE_UNRESOLVED_EXPRESSION on MERGE CHANGES A column in the statement does not resolve; check the names, or switch to APPLY CHANGES.

Limits#

  • Several ways to keep history exist: DataBridge's Silver apply (__seq), ds.apply_changes / APPLY CHANGES (__sequence for Type 1, __start_at / __end_at for Type 2), MERGE CHANGES, and the Workflow SCD2 checkbox. Their bookkeeping columns differ; ds.apply_changes / APPLY CHANGES is the recommended one.
  • DataBridge Silver apply does not receive deletes found by key comparison; build Silver from the Bronze change log with ds.apply_changes for those.
  • MERGE CHANGES needs an existing target.
  • Quarantine grows when a body re-reads all of Bronze.
  • Existing Type 2 history is not rewritten when you change track_sequence_changes.
  • No SQL clause for history_table or track_sequence_changes.