Back to the index · Previous: Deployment
Every snippet from the chapters in one place, the API endpoints a developer scripts against, and the open-source references.
SQL#
-- Read the copies (chapter 3)
SELECT COUNT(*) FROM datasimple.source.dbo_Orders; -- Parquet
SELECT COUNT(*) FROM datasimple.source.dbo_Orders_DELTA; -- Delta
SELECT COUNT(*) FROM datasimple.source.dbo_Orders_ICEBERG; -- Iceberg
SELECT snapshot_id, committed_at FROM datasimple.source.dbo_Orders_ICEBERG.snapshots;
-- Create, fill, merge (chapter 7)
CREATE TABLE IF NOT EXISTS datasimple.bronze.nation_eu (
nation_id BIGINT, nation STRING, region_id BIGINT, PRIMARY KEY (nation_id)) USING DELTA;
MERGE INTO datasimple.bronze.nation_eu t
USING (SELECT N_NATIONKEY AS nation_id, N_NAME AS nation, N_REGIONKEY AS region_id
FROM datasimple.source.TPCH_SF1_NATION_DELTA WHERE N_REGIONKEY = 3) s
ON t.nation_id = s.nation_id
WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *;
SELECT * FROM datasimple.source.TPCH_SF1_NATION_DELTA WHERE N_REGIONKEY = {{region_id}}; -- a parameter
DROP TABLE datasimple.bronze.nation_eu; -- soft drop; UNDROP TABLE restores; PURGE deletes files
-- Bronze (chapter 5)
SELECT * FROM datasimple.bronze.dbo_Orders WHERE NOT _is_deleted;
SELECT OrderID, _op_type, _batch_id, _load_ts FROM datasimple.bronze.dbo_Orders WHERE _batch_id = 74;
-- Apply changes (chapter 6)
APPLY CHANGES INTO silver.orders_current
FROM bronze.dbo_Orders
KEYS (OrderID) SEQUENCE BY _load_ts
APPLY AS DELETES WHEN _op_type = 'D'
STORED AS SCD TYPE 1
EXCEPT COLUMNS (_op_type, _source_ts);
APPLY CHANGES INTO silver.orders_history
FROM (SELECT * FROM bronze.dbo_Orders WHERE Amount IS NOT NULL)
KEYS (OrderID) SEQUENCE BY _load_ts
APPLY AS DELETES WHEN _op_type = 'D'
STORED AS SCD TYPE 2
EXCEPT COLUMNS (_op_type, _source_ts)
TRACK HISTORY EXCEPT COLUMNS (Status);
APPLY CHANGES FROM SNAPSHOT INTO silver.orders_current
FROM source.dbo_Orders KEYS (OrderID) STORED AS SCD TYPE 1 SNAPSHOT VERSION 20260118;
-- Streaming tables (chapter 7b): each refresh reads only the new source rows
CREATE OR REFRESH STREAMING TABLE silver.orders_live
KEYS (OrderID) SEQUENCE BY _load_ts APPLY AS DELETES WHEN _op_type = 'D'
SCHEDULE CRON '*/15 * * * *'
AS SELECT * FROM STREAM bronze.dbo_Orders;
REFRESH STREAMING TABLE silver.orders_live; -- FULL starts again
SHOW STREAMING TABLES;
DESCRIBE STREAMING TABLE silver.orders_live;
DROP STREAMING TABLE silver.orders_live; -- UNDROP STREAMING TABLE brings it back
CREATE STREAMING TABLE bronze.orders_events -- straight from a Kafka topic
AS SELECT * FROM STREAM KAFKA('DS-20', 'orders', format => 'json', starting => 'earliest');
-- Governance (chapter 9)
GRANT SELECT ON TABLE bronze.orders TO analyst;
REVOKE SELECT ON TABLE bronze.orders FROM analyst;
-- Audit trail (chapter 10)
SELECT _op_type, COUNT(*) FROM datasimple.source.dbo_Orders_DELTA WHERE _batch_id = 74 GROUP BY _op_type;
SELECT * FROM datasimple.bronze.dbo_Orders WHERE _load_ts > TIMESTAMP '2026-10-16 18:00:00';
SELECT * FROM datasimple.source.CUSTOMER WHERE _is_duplicate;
SELECT _source_file, _file_etag, COUNT(*) FROM datasimple.source.CUSTOMER GROUP BY 1, 2;
SELECT * FROM datasimple.source.orders WHERE _kafka_error IS NOT NULL;
SELECT * FROM datasimple.silver.dbo_Orders_HISTORY
WHERE __start_at <= TIMESTAMP '2026-10-16 18:58:10' AND (__end_at IS NULL OR __end_at > TIMESTAMP '2026-10-16 18:58:10');
SELECT * FROM datasimple.silver.dbo_Orders_HISTORY WHERE __is_current;
SELECT OrderID, __seq, FLOOR(__seq / 1000000000) AS job_id, __updated_at FROM datasimple.silver.dbo_Orders;
-- Recovery (chapter 11)
DESCRIBE HISTORY datasimple.silver.orders;
SELECT * FROM datasimple.silver.orders VERSION AS OF 3;
SELECT * FROM datasimple.silver.orders TIMESTAMP AS OF '2026-10-16 18:00:00';
RESTORE TABLE datasimple.silver.orders TO VERSION AS OF 3;
SELECT snapshot_id, operation, committed_at FROM iceberg.source.dbo_Orders_ICEBERG.snapshots;
SELECT * FROM iceberg.source.dbo_Orders_ICEBERG VERSION AS OF 6318412349011;
CALL iceberg.system.rollback_to_snapshot('source.dbo_Orders_ICEBERG', 6318412349011);
-- Migrations (chapter 12, application database)
SELECT name, applied_at, duration_ms, ok, error FROM notebook_schema_migrations ORDER BY applied_at;
Database side for change-log CDC (chapter 4):
-- SQL Server
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Orders', @role_name = NULL, @supports_net_changes = 1;
-- PostgreSQL
ALTER SYSTEM SET wal_level = logical; -- then restart
ALTER ROLE <user> WITH REPLICATION;
ALTER TABLE public.orders REPLICA IDENTITY FULL; -- optional
SELECT pg_drop_replication_slot('datasimple_<db>_<schema>_<table>'); -- when you stop loading a table
Python#
# Read and write (chapter 7)
orders = read_table("datasimple.source.dbo_Orders_DELTA")
live = orders.filter("NOT _is_deleted")
save_table(live, "silver.orders_live")
merge_delta("silver.orders_live", live, on=["OrderID"])
pdf = read_table("silver.orders_live").toPandas()
# Time travel (chapters 10, 11)
list_delta_history("silver.orders_live")
old = read_delta_at("silver.orders_live", version=0) # or timestamp="2026-10-16 18:00:00"
read_table("silver.orders_live").join(old, "OrderID", "left_anti").show()
# Apply changes (chapter 6)
ds.apply_changes(target="silver.orders_current", source="bronze.dbo_Orders", keys=["OrderID"],
sequence_by="_load_ts", apply_as_deletes="_op_type = 'D'",
except_columns=["_op_type", "_source_ts"])
ds.apply_changes(target="silver.orders_history", source="bronze.dbo_Orders", keys=["OrderID"],
sequence_by="_load_ts", apply_as_deletes="_op_type = 'D'", stored_as_scd_type=2,
except_columns=["_op_type", "_source_ts"], track_history_except_columns=["Status"])
ds.apply_changes_from_snapshot(target="silver.orders_current", snapshot="source.dbo_Orders",
keys=["OrderID"], stored_as_scd_type=1, snapshot_version=20260118)
ds.create_target_table("silver.orders_current", comment="Current orders",
columns="OrderID bigint, CustomerName string, Amount decimal(18,2), Status string")
@ds.table("silver.orders_clean", mode="merge", keys=["OrderID"], sequence_by="_load_ts")
def orders_clean():
return ds.read("bronze.dbo_Orders").filter("_op_type <> 'D'")
ds.run()
# Governance and quality (chapter 9)
grant("SELECT", "silver.orders_live", "analyst")
profile_table("silver.orders_live")
check_table("silver.orders_live", lambda c: c.isComplete("OrderID").isUnique("OrderID"))
table_freshness("silver.orders_live")
read_share("partner_orders", "orders")
information_schema views#
datasimple.information_schema — read-only views over the catalog registry (no copy, no snapshot). Lower-case identifiers; rows are limited to the caller's tenant (implicit, no tenant column in results). One SELECT/WITH per query, information_schema views only. See Lakehouse governance.
| View | Columns |
|---|---|
schemata |
catalog_name, schema_name, schema_owner, comment, created |
tables |
table_catalog, table_schema, table_name, table_type (BASE TABLE / VIEW / MATERIALIZED VIEW / STREAMING TABLE), table_kind (MANAGED / EXTERNAL), data_source_format, storage_location, comment, table_owner, created, updated |
columns |
table_catalog, table_schema, table_name, column_name, ordinal_position, data_type, is_nullable, column_default, comment, is_pii, sensitivity, mask_style |
views |
table_catalog, table_schema, table_name, view_definition |
table_constraints |
constraint_catalog, constraint_schema, constraint_name, table_catalog, table_schema, table_name, constraint_type (PRIMARY KEY / FOREIGN KEY / UNIQUE / NOT NULL / CHECK), is_enforced, created |
key_column_usage |
constraint_catalog, constraint_schema, constraint_name, table_catalog, table_schema, table_name, column_name, ordinal_position |
referential_constraints |
constraint_catalog, constraint_schema, constraint_name, unique_constraint_catalog, unique_constraint_schema, unique_constraint_name, referenced_table_catalog, referenced_table_schema, referenced_table_name, referenced_columns, table_schema, table_name, referencing_columns |
check_constraints |
constraint_catalog, constraint_schema, constraint_name, table_schema, table_name, check_clause, rule_name, dimension, column_name, level, is_enforced, created (Data Quality rules that enforce on write) |
table_privileges |
grantor, grantee, table_catalog, table_schema, table_name, privilege_type, is_grantable, granted |
column_privileges |
grantor, grantee, table_catalog, table_schema, table_name, column_name, privilege_type, is_grantable, is_masked |
row_filters |
table_catalog, table_schema, table_name, filter_name, filter_expr, principals, enabled, created |
column_masks |
table_catalog, table_schema, table_name, column_name, mask_expr, principals, enabled, created |
routines |
routine_catalog, routine_schema, routine_name, routine_type, routine_definition, comment, created |
volumes |
volume_catalog, volume_schema, volume_name, volume_type, storage_location, comment, created |
materialized_views |
table_catalog, table_schema, table_name, definition, refresh_schedule, timezone, last_refreshed, next_refresh, last_status, last_error, row_count, refresh_ms, owner, created |
streaming_tables |
table_catalog, table_schema, table_name, definition, source_table, mode (append / keys), keys, scd_type, watermark_column, last_watermark, refresh_schedule, timezone, last_refreshed, next_refresh, last_status, last_error, last_new_rows, row_count, refresh_ms, owner, created |
table_lineage |
source_table, target_table, job, notebook_id, run_id, recorded_at |
table_tags |
table_catalog, table_schema, table_name, tag_name, tag_value |
API endpoints#
Log in once: POST /api/auth/login {"username","password","tenantid"} and reuse the cookie.
| Area | Endpoints |
|---|---|
| Data Source | GET/POST /api/notebooks/external-connections, PUT/DELETE /api/notebooks/external-connections/:id |
| DataBridge | POST /api/databridge/api/databases, POST /api/databridge/api/tables, POST /api/databridge/api/convert, GET /api/databridge/api/jobs[?limit=], GET /api/databridge/api/jobs/:id, GET /api/databridge/api/jobs/:id/tables, GET /api/databridge/api/jobs/:id/log, DELETE /api/databridge/api/jobs/:id/cancel, DELETE /api/databridge/api/jobs/:id/tables/cancel?table=, GET/POST /api/databridge/api/schedules, POST /api/databridge/api/schedules/:id/toggle?enabled=, POST /api/databridge/api/schedules/:id/run, DELETE /api/databridge/api/schedules/:id, GET /api/databridge/api/backup-drop/health, POST /api/databridge/api/backup-drop/prepare, GET /api/databridge/api/backup-drop/status?id= |
| Bronze | POST /api/bronze-replication/apply/:job, POST /api/bronze-replication/start-snapshot, POST /api/bronze-replication/stop, GET /api/bronze-replication/tables?source_name=, GET /api/bronze-replication/stats, GET /api/databridge/api/bronze/apply-history?limit= |
| Notebooks | POST /api/notebooks, PUT/DELETE /api/notebooks/:id, GET /api/notebooks/:id/versions, GET /api/notebooks/:id/timeline, POST /api/notebooks/spark/sql, POST /api/notebooks/spark/run, POST /api/notebooks/spark/run-notebook, GET /api/notebooks/spark/helpers (the Python helpers with signature and docstring, {helpers: [{name, signature, doc, kind}]}, for editor autocomplete) |
| Schedules | GET/POST /api/notebooks/:id/schedules, PUT/DELETE /api/notebooks/:id/schedules/:sid, POST /api/notebooks/:id/schedules/:sid/run-now, GET /api/notebooks/:id/runs, GET /api/notebooks/jobs |
| Pipelines | POST /api/pipelines, POST /api/pipelines/:id/run, GET /api/pipelines/:id/runs, GET /api/pipelines/:id/graph, PUT /api/pipelines/:id/toggle, POST /api/pipelines/:id/stop, DELETE /api/pipelines/:id |
| Dataflow | POST /api/notebooks/airflow/dags, POST /api/notebooks/airflow/dags/:dagId/trigger, GET /api/notebooks/airflow/dags/:dagId/runs (the Dataflow API lives under /api/notebooks/airflow, historic path) |
| Workflows | POST /api/workflows, POST /api/workflows/:id/run, GET /api/workflows/:id/runs, GET /api/workflows/:id/lineage, POST /api/workflows/:id/pipeline |
| Unity Catalog | POST /api/notebooks/unity-catalog/schemas, POST /api/notebooks/unity-catalog/register, POST/DELETE /api/notebooks/unity-catalog/grants, GET /api/notebooks/unity-catalog/grants?securable_type=&full_name=, GET /api/notebooks/unity-catalog/access?full_name=, POST /api/notebooks/unity-catalog/row-filters, POST /api/notebooks/unity-catalog/column-masks, POST /api/notebooks/unity-catalog/{volumes,functions,models}, GET /api/notebooks/unity-catalog/audit?limit=, GET /api/notebooks/unity-catalog/lineage?limit=, POST /api/notebooks/unity-catalog/bootstrap-managed, DELETE /api/notebooks/unity-catalog/tables/:fullName, GET /api/2.1/unity-catalog/information-schema, GET /api/2.1/unity-catalog/information-schema/:view?schema_name=&table_name=&limit=, POST /api/2.1/unity-catalog/information-schema/query {sql} |
| Lineage | GET /api/notebooks/lineage-graph, GET /api/notebooks/spark/lineage?table=, GET /api/notebooks/spark/column-lineage?table= |
| Semantics | POST /api/notebooks/semantic/terms, POST /api/notebooks/semantic/metrics, POST /api/notebooks/semantic/metrics/validate, POST /api/notebooks/semantic/metrics/:id/run, POST /api/notebooks/semantic/metrics/preview, GET /api/notebooks/semantic/lookup?q= |
| Data Quality | POST /api/notebooks/dq/validate-rule, GET/POST /api/notebooks/dq/checks, GET /api/notebooks/dq/certificates/eligibility?table=, POST /api/notebooks/dq/certificates, PUT /api/notebooks/governance/columns |
| DataShare | POST /api/notebooks/sharing/shares, POST /api/notebooks/sharing/recipients, GET /api/notebooks/sharing/access-log?limit= |
| History, Log | GET /api/notebooks/activity?group=&limit=, GET /api/notebooks/run-log?notebookId=&limit=, GET /api/notebooks/audit-log?limit= (admin) |
| Agent | POST /api/agents/run, POST /api/agents/run/stream, GET /api/agents/actions, POST /api/agents/actions/:id/approve |
| Sarah | POST /api/sarah/ask/stream (SSE: phase, query, done), GET /api/sarah/reports, GET /api/sarah/reports/:id, DELETE /api/sarah/reports/:id, GET /api/sarah/context |
| Deployment | PUT /api/deploy/environments, POST /api/deploy/environments/:name/test, GET /api/deploy/compare?target=, POST /api/deploy/promote, GET /api/deploy/history, POST /api/deploy/restore |
| Administration | GET /api/admin/roles, POST /api/admin/users, PUT /api/admin/users/:id/lock, PUT /api/admin/roles/:id/tabs, GET /api/admin/tenants |
Open-source references#
- Apache Spark SQL: https://spark.apache.org/docs/latest/sql-ref.html · PySpark: https://spark.apache.org/docs/latest/api/python/
- Delta Lake: https://docs.delta.io/latest/ ·
MERGE: https://docs.delta.io/latest/delta-update.html · time travel: https://docs.delta.io/latest/delta-batch.html#query-an-older-snapshot-of-a-table-time-travel - Apache Iceberg: https://iceberg.apache.org/docs/latest/ · Spark procedures: https://iceberg.apache.org/docs/latest/spark-procedures/
- Apache Kafka: https://kafka.apache.org/documentation/ · Debezium: https://debezium.io/documentation/
- SQL Server change data capture: https://learn.microsoft.com/sql/relational-databases/track-changes/about-change-data-capture-sql-server
- PostgreSQL logical decoding: https://www.postgresql.org/docs/current/logicaldecoding.html
- dbt: https://docs.getdbt.com/ · Apache Flink SQL: https://nightlies.apache.org/flink/flink-docs-stable/docs/dev/table/sql/overview/
- Unity Catalog OSS: https://docs.unitycatalog.io/
- Kimball slowly changing dimensions: Type 1, Type 2
Our deeper docs#
DataBridge guide · Database · File drop · Backup drop · Change-log CDC · CDC with MERGE into Silver · Kafka · Apply changes in notebooks · Migrations · Tab permissions · Environment deployment · Unity Catalog status and plan · Quick start