DataSimplePractical GuideLog inOpen DataSimple
DataSimple
Chapter 13Reference

Reference

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#

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
-- 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#

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#

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

Back to the index · Next: 14. Sarah: ask for a report