Notebook basics (cell types, the first-line options %sql, %python, %md, Markdown) are in Notebooks; the engine is in Spark engine.
SQL reference#
SQL cells speak Spark SQL with the Delta Lake and Apache Iceberg commands, plus a few DataSimple commands (APPLY CHANGES, materialized views, UNDROP, COMMENT ON for business meaning). This chapter lists them by task, with an example of each. Every example runs in a SQL cell; replace the table names with your own. Every command in the examples was run on DataSimple's Spark 4.0 on 3 November 2026 (on small demo tables silver.sql_demo_* and silver.st_demo_*, kept as references).
Rules for every SQL cell#
- Names.
datasimple.<layer>.<table>,<layer>.<table>, or a bare table name. Layers:source(read-only copies of your systems),bronze,silver,gold. Catalog views:datasimple.information_schema.<view>. - Several statements are separated by
;. They run in order; the last one's result is shown. - Parameters.
{{name}}is replaced by the value of the Parameters cell (or the schedule or pipeline). A parameter with no value stops the cell. - Comments.
-- to the end of the lineand/* … */. - Your access applies to every statement (7a).
SQL contents#
| Task | Commands |
|---|---|
| Query data | SELECT, WITH, joins, GROUP BY, window functions, PIVOT, UNION, TABLESAMPLE |
| Look at objects | SHOW, DESCRIBE, information_schema, EXPLAIN |
| Create objects | CREATE SCHEMA, CREATE TABLE, CREATE TABLE … AS SELECT, CREATE VIEW, CREATE TEMPORARY VIEW |
| Change data | INSERT, INSERT OVERWRITE, UPDATE, DELETE, MERGE, TRUNCATE |
| Changes from a source: APPLY CHANGES | Type 1, Type 2, from a snapshot |
| Materialized views | CREATE, REFRESH, ALTER … SCHEDULE, SHOW, DROP, UNDROP |
| Streaming tables | CREATE STREAMING TABLE … FROM STREAM <table> or STREAM KAFKA('<Data Source>', '<topic>'), REFRESH [FULL], ALTER … SCHEDULE, SHOW, DESCRIBE, DROP, UNDROP |
| Change tables | ALTER TABLE, COMMENT ON, DROP, UNDROP |
| History and recovery | DESCRIBE HISTORY, VERSION AS OF, TIMESTAMP AS OF, RESTORE, Iceberg snapshots |
| Maintenance | OPTIMIZE, VACUUM |
| Access | GRANT, REVOKE |
| Functions | The built-in functions you will use most |
| Not available | What other platforms have, and the DataSimple way |
Commands at a glance#
The everyday commands for each kind of object. Every one is explained with an example further down.
| Do | Table | View | Materialized view | Streaming table |
|---|---|---|---|---|
| Create | CREATE TABLE t (…), CREATE TABLE t AS SELECT … |
CREATE VIEW v AS SELECT … |
CREATE MATERIALIZED VIEW gold.mv_x AS SELECT … |
CREATE STREAMING TABLE t AS SELECT … FROM STREAM src |
| Change the definition | ALTER TABLE t ADD COLUMNS / RENAME COLUMN / DROP COLUMN / ALTER COLUMN |
CREATE OR REPLACE VIEW v AS … |
CREATE OR REPLACE MATERIALIZED VIEW … |
CREATE OR REFRESH STREAMING TABLE … |
| List | SHOW TABLES IN silver |
SHOW VIEWS IN gold |
SHOW MATERIALIZED VIEWS |
SHOW STREAMING TABLES |
| Describe | DESCRIBE TABLE [EXTENDED] t, DESCRIBE DETAIL t |
DESCRIBE TABLE v |
DESCRIBE TABLE gold.mv_x |
DESCRIBE STREAMING TABLE t |
| Read | SELECT … FROM t |
SELECT … FROM v |
SELECT … FROM gold.mv_x |
SELECT … FROM t |
| Add rows | INSERT INTO t VALUES … / SELECT … |
(through its tables) | REFRESH MATERIALIZED VIEW |
REFRESH STREAMING TABLE t |
| Change rows | UPDATE t SET … WHERE … |
|||
| Remove rows | DELETE FROM t WHERE …, TRUNCATE TABLE t |
|||
| Insert or update by key | MERGE INTO t USING s ON …, APPLY CHANGES INTO t … |
KEYS (…) in its definition |
||
| Rename | ALTER TABLE t RENAME TO t2 |
ALTER VIEW v RENAME TO v2 |
||
| Describe its meaning | COMMENT ON TABLE t IS '…', COMMENT ON COLUMN t.c IS '…' |
COMMENT ON TABLE v IS '…' |
COMMENT '…' in its definition |
|
| History | DESCRIBE HISTORY t, SELECT … VERSION AS OF n |
DESCRIBE HISTORY gold.mv_x |
DESCRIBE HISTORY t |
|
| Drop (to Trash) | DROP TABLE t |
DROP VIEW v |
DROP MATERIALIZED VIEW gold.mv_x |
DROP STREAMING TABLE t |
| Bring back | UNDROP TABLE t |
UNDROP MATERIALIZED VIEW gold.mv_x |
UNDROP STREAMING TABLE t |
|
| Delete for good | DROP TABLE t PURGE |
Query data#
-- Filter, sort, limit
SELECT OrderID, CustomerName, Amount
FROM bronze.dbo_Orders
WHERE NOT _is_deleted AND Amount > 1000
ORDER BY Amount DESC
LIMIT 20;
-- Aggregate
SELECT Status, COUNT(*) AS orders, SUM(Amount) AS total, AVG(Amount) AS average
FROM bronze.dbo_Orders
GROUP BY Status
HAVING COUNT(*) > 10;
-- A named sub-query (CTE) and a join
WITH big_customers AS (
SELECT CustomerID, SUM(Amount) AS spend
FROM silver.orders GROUP BY CustomerID HAVING SUM(Amount) > 50000)
SELECT c.CustomerName, b.spend
FROM big_customers b
JOIN silver.customers c ON c.CustomerID = b.CustomerID;
Joins: JOIN (inner), LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN, LEFT SEMI JOIN (rows that have a match), LEFT ANTI JOIN (rows that have none). JOIN … USING (col) when the column has the same name on both sides.
-- Window functions: rank, running total, previous value
SELECT branch_code, account_number, balance,
RANK() OVER (PARTITION BY branch_code ORDER BY balance DESC) AS rank_in_branch,
SUM(balance) OVER (PARTITION BY branch_code ORDER BY account_number) AS running_total,
LAG(balance) OVER (PARTITION BY branch_code ORDER BY account_number) AS previous
FROM silver.accounts;
-- Filter on a window function: put it in a sub-query (QUALIFY is not available)
-- The top 3 accounts per branch:
SELECT branch_code, account_number, balance
FROM (SELECT branch_code, account_number, balance,
RANK() OVER (PARTITION BY branch_code ORDER BY balance DESC) AS rank_in_branch
FROM silver.accounts)
WHERE rank_in_branch <= 3;
-- The latest row per key (a pattern you will use often on Bronze)
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY _load_ts DESC) AS rn
FROM bronze.dbo_Orders)
WHERE rn = 1;
-- PIVOT: one column per value (values are case-sensitive)
SELECT * FROM (SELECT customer_id, phone_type FROM bronze.fin_customer_phones)
PIVOT (COUNT(*) FOR phone_type IN ('Mobile', 'Home', 'Work'));
-- Combine results
SELECT CustomerID FROM silver.orders_2025
UNION ALL -- UNION removes duplicates, and there are also INTERSECT and EXCEPT
SELECT CustomerID FROM silver.orders_2026;
-- A sample of a large table
SELECT * FROM silver.events TABLESAMPLE (1 PERCENT);
Look at objects#
SHOW SCHEMAS; -- source, bronze, silver, gold, …
SHOW TABLES IN silver; -- your tables in a schema
SHOW COLUMNS IN silver.orders;
DESCRIBE TABLE silver.orders; -- columns and types
DESCRIBE TABLE EXTENDED silver.orders; -- plus location, format, properties
DESCRIBE DETAIL silver.orders; -- Delta: files, size, partitions
EXPLAIN SELECT COUNT(*) FROM silver.orders; -- how Spark will run the query
-- The catalog as tables (only your tenant's objects)
SELECT table_schema, table_name, table_type, data_source_format
FROM datasimple.information_schema.tables
WHERE table_schema = 'gold';
SELECT column_name, data_type, comment, is_pii
FROM datasimple.information_schema.columns
WHERE table_schema = 'silver' AND table_name = 'orders';
Other information_schema views: schemata, views, table_constraints, key_column_usage, table_privileges, column_privileges, row_filters, column_masks, table_lineage, table_tags, materialized_views, streaming_tables, object_history. The full column list is in chapter 13.
Create objects#
CREATE SCHEMA IF NOT EXISTS sandbox; -- needs CREATE_SCHEMA
-- A table with columns and a primary key (Delta by default)
CREATE TABLE IF NOT EXISTS silver.customers (
customer_id BIGINT,
customer_name STRING,
segment STRING,
created_at TIMESTAMP,
PRIMARY KEY (customer_id)
) USING DELTA
COMMENT 'One row per customer';
-- A table from a query
CREATE OR REPLACE TABLE gold.sales_by_region AS
SELECT region, DATE_TRUNC('month', order_date) AS month, SUM(net_amount) AS net_sales
FROM silver.sales
GROUP BY region, DATE_TRUNC('month', order_date);
-- Partitioned by a column you filter on
CREATE TABLE silver.events (event_id BIGINT, event_type STRING, event_date DATE)
USING DELTA PARTITIONED BY (event_date);
-- Iceberg instead of Delta (when Iceberg is enabled)
CREATE TABLE silver.orders_ice (OrderID BIGINT, Amount DECIMAL(18,2)) USING ICEBERG;
-- A view: a saved query, read through its tables' access rules
CREATE OR REPLACE VIEW gold.open_orders AS
SELECT * FROM silver.orders WHERE Status = 'Open';
-- A temporary view: only in your session, gone when it closes
CREATE OR REPLACE TEMPORARY VIEW recent AS
SELECT * FROM silver.orders WHERE order_date >= DATE_SUB(CURRENT_DATE(), 7);
Every table you create is registered in the catalog and appears in the Lakehouse tree. Column types: STRING, INT, BIGINT, DOUBLE, DECIMAL(p,s), BOOLEAN, DATE, TIMESTAMP, BINARY, ARRAY<…>, MAP<…>, STRUCT<…>.
Change data#
INSERT INTO silver.customers VALUES (1001, 'Ana Lopez', 'Retail', current_timestamp());
INSERT INTO silver.customers
SELECT customer_id, customer_name, segment, created_at FROM bronze.customers WHERE NOT _is_deleted;
INSERT OVERWRITE gold.sales_by_region -- replace all rows
SELECT region, DATE_TRUNC('month', order_date), SUM(net_amount) FROM silver.sales GROUP BY 1, 2;
UPDATE silver.customers SET segment = 'Premier' WHERE customer_id = 1001;
DELETE FROM silver.customers WHERE segment IS NULL;
TRUNCATE TABLE sandbox.scratch; -- remove every row, keep the table
-- MERGE: insert new keys, update changed ones, delete removed ones
MERGE INTO silver.customers t
USING (SELECT * FROM bronze.customers) s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s._is_deleted THEN DELETE
WHEN MATCHED THEN UPDATE SET t.customer_name = s.customer_name, t.segment = s.segment
WHEN NOT MATCHED AND NOT s._is_deleted THEN INSERT (customer_id, customer_name, segment, created_at)
VALUES (s.customer_id, s.customer_name, s.segment, s.created_at);
UPDATE SET * and INSERT * copy every column with the same name. A declared primary key is checked on every write; a duplicate is rejected and the write rolled back.
APPLY CHANGES#
The simplest way to keep a Silver table in step with Bronze or a change feed. DataSimple orders the changes by the sequence column, applies only newer ones, and handles deletes. Details and the bookkeeping columns: chapter 6.
-- Type 1: the current row per key
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);
-- Type 2: every version, with __start_at, __end_at, __is_current
APPLY CHANGES INTO silver.orders_history
FROM bronze.dbo_Orders
KEYS (OrderID) SEQUENCE BY _load_ts
APPLY AS DELETES WHEN _op_type = 'D'
STORED AS SCD TYPE 2
TRACK HISTORY EXCEPT COLUMNS (Status);
-- From a full copy: keys missing from the copy are deleted (Type 1) or closed (Type 2)
APPLY CHANGES FROM SNAPSHOT INTO silver.orders_current
FROM source.dbo_Orders KEYS (OrderID) STORED AS SCD TYPE 1;
Run it again any time: it applies only what is new. Put it in a scheduled notebook or a pipeline step to keep the table current.
Materialized views#
A stored query whose result is kept as a table and refreshed on demand or on a schedule, so heavy Gold reports read a ready result. Names: gold.mv_<subject> or silver.mv_<subject>, lower case, at most 60 characters.
CREATE MATERIALIZED VIEW gold.mv_sales_revenue_by_region_daily
SCHEDULE CRON '0 6 * * *' TIMEZONE 'America/New_York'
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; -- now
ALTER MATERIALIZED VIEW gold.mv_sales_revenue_by_region_daily SET SCHEDULE CRON '0 * * * *';
ALTER MATERIALIZED VIEW gold.mv_sales_revenue_by_region_daily DROP SCHEDULE;
DROP MATERIALIZED VIEW IF EXISTS gold.mv_sales_revenue_by_region_daily;
SELECT * FROM datasimple.information_schema.materialized_views; -- freshness
Streaming tables#
A table that keeps up with a source table: each refresh reads only the rows that arrived since the last refresh. Name the source once as STREAM <table> (or STREAM(<table>)) after FROM or JOIN; join other tables by their plain name.
-- Append: every new source row is added once
CREATE STREAMING TABLE silver.orders_log
COMMENT 'Every order change, as it arrived'
AS SELECT OrderID, Status, Amount, _op_type, _load_ts
FROM STREAM bronze.dbo_Orders;
-- By key: the current row per order, updates replace and deletes remove
CREATE OR REFRESH STREAMING TABLE silver.orders_live
KEYS (OrderID) SEQUENCE BY _load_ts
APPLY AS DELETES WHEN _op_type = 'D'
STORED AS SCD TYPE 1
SCHEDULE CRON '*/15 * * * *' TIMEZONE 'UTC'
AS SELECT o.OrderID, o.Status, o.Amount, c.CustomerName, o._op_type, o._load_ts
FROM STREAM bronze.dbo_Orders o
LEFT JOIN silver.customers c ON c.CustomerID = o.CustomerID;
REFRESH STREAMING TABLE silver.orders_live; -- only the new rows
REFRESH STREAMING TABLE silver.orders_live FULL; -- start again from the first row
ALTER STREAMING TABLE silver.orders_live SET SCHEDULE CRON '0 * * * *';
ALTER STREAMING TABLE silver.orders_live DROP SCHEDULE;
SHOW STREAMING TABLES; -- or SHOW STREAMING TABLES IN silver
DESCRIBE STREAMING TABLE silver.orders_live; -- source, keys, watermark, schedule, last refresh, query
DROP STREAMING TABLE silver.orders_live; -- soft drop, the schedule stops
UNDROP STREAMING TABLE silver.orders_live; -- back, with its data, definition and schedule
SELECT * FROM datasimple.information_schema.streaming_tables;
| Clause | Meaning | Default |
|---|---|---|
OR REFRESH |
Create, or change the definition of an existing one. An unchanged definition only loads the new rows; a changed one starts again | Without it, an existing name is refused |
OR REPLACE |
Change the definition and start again from the first row | |
IF NOT EXISTS |
Do nothing when it exists | |
KEYS (…) |
Apply new rows by key, like APPLY CHANGES; a repeat changes nothing |
Append every new row |
SEQUENCE BY col |
Which change of a key is the newest | The watermark column |
APPLY AS DELETES WHEN … |
Rows that delete the key (Type 1) or close it (Type 2) | No deletes |
STORED AS SCD TYPE 1 | 2 |
Current row only, or every version with __start_at, __end_at, __is_current |
1 |
WATERMARK BY col |
The source column that grows with every new row | _load_ts (every Bronze and DataBridge table has it) |
SCHEDULE CRON '…' [TIMEZONE '…'] |
Refresh on a 5-field cron | Refresh with REFRESH STREAMING TABLE, or from a pipeline step |
COMMENT '…' |
The table's meaning in the catalog |
How it works:
- A refresh reads the highest value of the watermark column in the source.
- It runs your query on the source rows above the value stored by the last refresh, and at most that high value.
- Without
KEYS, the result is appended; withKEYS, it is applied by key. - The new high value is stored only when the refresh succeeds, so a failed refresh is simply run again.
Good to know:
- The query must keep the
SEQUENCE BYcolumn (and the columns ofAPPLY AS DELETES) when you useKEYS. - Give the
STREAMsource an alias (FROM STREAM bronze.dbo_Orders o) when you name its columns aso.col. - Only rows with a higher watermark are read. A source that is rewritten with older load times (a full reload) is followed with
REFRESH … FULL. - Refreshing needs MODIFY on the streaming table (or to own it); creating needs CREATE_TABLE on the schema and SELECT on the source.
information_schema.tablesshows it withtable_type=STREAMING TABLE.
Reading a Kafka topic directly
Tested on 4 November 2026 with Apache Kafka 3.9 (topic with 2 partitions; inserts, an update, a delete and a message that is not JSON): the append table held every message once, the keyed table the current row per order, a repeat read nothing new, and FULL gave the same result from the landing table.
Name the topic with STREAM KAFKA('<Data Source>', '<topic>'). The Data Source is a saved Kafka connection (DS-20, or its name); its brokers and password never appear in the SQL.
-- Every message, as it arrived
CREATE STREAMING TABLE bronze.orders_events
AS SELECT * FROM STREAM KAFKA('DS-20', 'orders', format => 'json');
-- The current row per order, from JSON messages with an op field
CREATE OR REFRESH STREAMING TABLE silver.orders_live
KEYS (order_id) SEQUENCE BY _source_ts
APPLY AS DELETES WHEN _is_deleted
SCHEDULE CRON '*/5 * * * *'
AS SELECT k.order_id, k.customer, k.amount, k.status, k._source_ts, k._is_deleted
FROM STREAM KAFKA('DS-20', 'orders', op_field => 'op', timestamp_field => 'updated_at') k
WHERE k._kafka_error IS NULL;
-- Change events from a database (Debezium envelopes)
CREATE STREAMING TABLE silver.customers_live
KEYS (customer_id) SEQUENCE BY _source_ts APPLY AS DELETES WHEN _is_deleted
AS SELECT * FROM STREAM KAFKA('DS-20', 'dbz.crm.customers', format => 'debezium');
| Option | Meaning | Default |
|---|---|---|
format => '…' |
json, debezium (change envelopes), csv or raw |
json |
op_field => '…' |
JSON / CSV field holding the operation; with delete_values (default D,DELETE) it sets _op_type and _is_deleted |
none: every message is an insert |
timestamp_field => '…' |
The change time, in _source_ts: a field of the message, kafka_timestamp or ts_ms |
The message time (Debezium: ts_ms) |
starting => '…' |
earliest (the whole topic on the first refresh) or latest (only messages after it) |
earliest |
max_messages => '…' |
At most this many messages per refresh; the next refresh continues | 1,000,000 |
csv_columns => 'a,b,c', csv_delimiter => ';' |
CSV: the column names and the separator | c1..cN, , |
Each message becomes a row: its fields as columns (nested objects as JSON text), plus _op_type (I / U / D), _is_deleted, _source_ts, _kafka_topic, _kafka_partition, _kafka_offset, _kafka_timestamp, _kafka_key and _kafka_error (set when a message could not be decoded; its data columns are empty).
How it works:
- A refresh reads the messages from the offsets the last refresh reached to the end of each partition, with the same reader as a DataBridge Kafka load.
- They are added to the streaming table's landing table,
source.kafka_<schema>_<table>_<code>, which keeps every message once. The offsets are stored with the streaming table, so it never shares a position with a DataBridge load or another streaming table of the same topic. - Your query then runs on the landing rows that are new, as for any streaming table.
Good to know:
- No message is lost or doubled. The offsets move only after the messages are in the landing table; a refresh cut off half way reads the same messages again and the landing table keeps them once.
REFRESH … FULLrebuilds the streaming table from the landing table; Kafka is not read again from the start, so messages that Kafka has since deleted (retention) are not lost.- A different topic or reader options (
CREATE OR REFRESHwith another topic orformat) starts a new landing table, read fromstarting. - The landing table shows in the catalog under
sourceand can be read like any table. Dropping the streaming table keeps it. - One refresh reads for at most about two minutes, or
max_messages; the message says how many are still waiting, and the next refresh continues. - A broker that does not answer is tried once more after a few seconds; then the refresh fails with the reason and can be run again.
REFRESH … FULLstill rebuilds from the messages already landed, and says Kafka could not be read. WATERMARK BYis not used with Kafka: the landing time_load_tsis the watermark.- The broker side (network reach, users, retention) is the same as for a DataBridge Kafka load: see chapter 4.
Change tables#
-- Columns
ALTER TABLE silver.customers ADD COLUMNS (email STRING, phone STRING, visits INT, fax STRING, telex STRING);
ALTER TABLE silver.customers ADD COLUMNS (loyalty_tier STRING COMMENT 'Bronze, Silver or Gold' AFTER segment);
ALTER TABLE silver.customers RENAME COLUMN phone TO phone_number;
ALTER TABLE silver.customers DROP COLUMN email;
ALTER TABLE silver.customers DROP COLUMNS (fax, telex);
ALTER TABLE silver.customers ALTER COLUMN segment COMMENT 'Retail, Business or Premier';
ALTER TABLE silver.customers ALTER COLUMN visits TYPE BIGINT; -- widen: SMALLINT → INT → BIGINT, FLOAT → DOUBLE
ALTER TABLE silver.customers ALTER COLUMN customer_name DROP NOT NULL;
-- Rules on every write: a row that breaks one is refused
ALTER TABLE silver.customers ADD CONSTRAINT name_present CHECK (customer_name IS NOT NULL);
ALTER TABLE silver.customers ADD CONSTRAINT valid_segment CHECK (segment IN ('Retail', 'Business', 'Premier'));
ALTER TABLE silver.customers DROP CONSTRAINT valid_segment;
-- The table
ALTER TABLE silver.customers SET TBLPROPERTIES ('delta.logRetentionDuration' = 'interval 30 days');
SHOW TBLPROPERTIES silver.customers;
-- Business meaning: kept in the catalog, shown in the Lakehouse tree and used by Sarah
COMMENT ON TABLE silver.customers IS 'One row per active customer, from the CRM';
COMMENT ON COLUMN silver.customers.segment IS 'Retail, Business or Premier';
-- Rename the table: from here on it is silver.customer_master
ALTER TABLE silver.customers RENAME TO silver.customer_master;
-- Views: change one by creating it again
CREATE OR REPLACE VIEW gold.open_orders AS SELECT * FROM silver.orders WHERE Status IN ('Open', 'Held');
ALTER VIEW gold.open_orders RENAME TO gold.orders_open;
-- Drop and bring back
DROP TABLE silver.customer_master; -- soft drop: hidden, files kept
UNDROP TABLE silver.customer_master; -- brings it back, with its history
DROP TABLE sandbox.scratch PURGE; -- deletes the files: cannot be undone
DROP VIEW gold.orders_open;
DROP TABLE IF EXISTS sandbox.scratch; -- no error when it is not there
- Renaming and dropping columns. A Delta table needs column mapping for
RENAME COLUMNandDROP COLUMN. DataSimple turns it on for that table the first time you run one; the data is not rewritten. Iceberg tables rename and drop columns without it. - Changing a column's type works for widening (
SMALLINT→INT→BIGINT,FLOAT→DOUBLE, a largerDECIMAL). DataSimple turns type widening on for that table the first time. For other changes (a number to text, say), add a new column, fill it withUPDATE, and drop the old one. - Not null on an existing column.
SET NOT NULLis refused for a column that allows nulls; add aCHECK (col IS NOT NULL)constraint instead, or declareNOT NULLinCREATE TABLE. - Views that select
*keep the columns they had when created. After you add, rename or drop a column of their table, runCREATE OR REPLACE VIEWagain. - Dropped tables stay in Trash for the retention period;
UNDROPworks until then. A primary key declared withCREATE TABLEis enforced on every write: a duplicate key is refused and the write rolled back. - The tenant column
_tenantidcannot be dropped, renamed or retyped.
History and recovery#
DESCRIBE HISTORY silver.orders; -- every version: when, who, operation
SELECT * FROM silver.orders VERSION AS OF 3; -- the table as it was at version 3
SELECT * FROM silver.orders TIMESTAMP AS OF '2026-10-16 18:00:00';
-- What changed since version 3
SELECT * FROM silver.orders EXCEPT SELECT * FROM silver.orders VERSION AS OF 3;
RESTORE TABLE silver.orders TO VERSION AS OF 3; -- administrators only
-- Iceberg tables
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);
Maintenance#
OPTIMIZE silver.orders; -- compact small files
OPTIMIZE silver.orders ZORDER BY (CustomerID); -- and cluster by a column you filter on
VACUUM silver.orders RETAIN 168 HOURS; -- remove files older than 7 days that no version uses
VACUUM limits how far back time travel can go: keep at least 7 days. Both need MODIFY.
Access#
GRANT SELECT ON TABLE gold.sales_by_region TO analyst;
GRANT MODIFY ON TABLE silver.orders TO data_engineer;
GRANT CREATE_TABLE ON SCHEMA silver TO data_engineer;
REVOKE SELECT ON TABLE gold.sales_by_region FROM analyst;
SELECT * FROM datasimple.information_schema.table_privileges WHERE table_name = 'sales_by_region';
Grants are recorded in the catalog and apply everywhere (notebooks, Sarah, the API, DataShare). They need an administrator or the MANAGE privilege. Row filters and column masks are set in Lakehouse (chapter 9).
Functions#
Spark SQL has several hundred built-in functions; autocomplete in a SQL cell lists them with their signature. The ones you will use most:
| Kind | Functions |
|---|---|
| Text | CONCAT, CONCAT_WS, UPPER, LOWER, TRIM, SUBSTRING, REPLACE, REGEXP_REPLACE, REGEXP_EXTRACT, SPLIT, LPAD, LENGTH |
| Numbers | ROUND, FLOOR, CEIL, ABS, MOD, POWER, GREATEST, LEAST |
| Dates | CURRENT_DATE, CURRENT_TIMESTAMP, DATE_TRUNC, DATE_ADD, DATE_SUB, DATEDIFF, MONTHS_BETWEEN, YEAR, MONTH, DAYOFWEEK, TO_DATE, TO_TIMESTAMP, DATE_FORMAT |
| Nulls and conditions | COALESCE, NULLIF, IFNULL, NVL, CASE WHEN … THEN … ELSE … END, IF |
| Types | CAST(x AS type), TRY_CAST (null instead of an error) |
| Aggregates | COUNT, COUNT(DISTINCT …), SUM, AVG, MIN, MAX, APPROX_COUNT_DISTINCT, PERCENTILE_APPROX, COLLECT_LIST, COLLECT_SET |
| Windows | ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE |
| Hashes and keys | SHA2(x, 256), MD5, UUID, MONOTONICALLY_INCREASING_ID |
| JSON and arrays | FROM_JSON, TO_JSON, GET_JSON_OBJECT, EXPLODE, ARRAY_CONTAINS, SIZE |
SELECT customer_id,
UPPER(TRIM(customer_name)) AS name,
COALESCE(segment, 'Unknown') AS segment,
DATEDIFF(CURRENT_DATE(), TO_DATE(created_at)) AS days_as_customer,
CASE WHEN segment = 'Premier' THEN 'High' ELSE 'Standard' END AS tier,
SHA2(CONCAT_WS('||', customer_id, 'CRM'), 256) AS customer_hash
FROM silver.customers;
Not available, and what to use instead#
| You may know | In DataSimple |
|---|---|
| A streaming table reading files directly | A DataBridge File drop loads the files into Bronze; a streaming table reads Bronze as STREAM. Kafka topics are read directly: STREAM KAFKA |
COPY INTO a table from files |
A DataBridge File drop (chapter 4) |
| Tasks and streams on a schedule | A schedule on a notebook, or a pipeline (chapter 8) |
CREATE STAGE, PUT, GET |
Files go through DataBridge or the lake target; read them as tables |
USE CATALOG other catalogs |
One catalog per tenant, datasimple |
QUALIFY |
A sub-query with the window function, then WHERE (example) |
SHOW CREATE TABLE on a Delta table |
DESCRIBE TABLE EXTENDED and DESCRIBE DETAIL |
| Stored procedures | A notebook with parameters, run by a schedule or a pipeline |
Queries on a storage path (delta.`s3://…` ) |
Administrators only; read by table name |
Read more#
- Apache Spark SQL reference, Delta Lake and Apache Iceberg documentation (the open-source projects).
- Chapter 13 · Reference: every snippet from the guide in one place.
Python reference#
A Python cell runs PySpark in your Spark session, with the DataSimple helpers loaded. Use the helpers to read and write tables: they find the table in the catalog, apply your access rules, register what you write and record lineage.
Ready in every cell#
No import needed:
| Name | What it is |
|---|---|
spark |
Your Spark session |
F |
pyspark.sql.functions (F.col, F.sum, F.when …) |
T |
pyspark.sql.types (T.StringType() …) |
Window |
pyspark.sql.window.Window |
ds |
DS Tables, apply changes, star schemas (chapter 15) |
read_table, save_table, … |
The helpers below |
What shows under the cell: print(...) output, then the cell's last expression. A DataFrame as the last line shows as a result table (up to 200 rows); df.show() prints it as text.
Parameters from the Parameters cell are Python variables: a parameter region is the variable region.
Packages. pandas, NumPy and the standard library are there. Other packages are installed by your administrator; %pip is not available.
Read and write: the five you need#
orders = read_table("bronze.dbo_Orders") # governed read, any format
live = orders.filter("NOT _is_deleted")
new_rows = live.filter("OrderID > 1000") # the rows to add
changes = live.filter("Status = 'Open'") # the rows to update or insert
save_table(live, "silver.orders_live") # create or replace a Delta table
save_table(new_rows, "silver.orders_live", mode="append") # add rows
merge_delta("silver.orders_live", changes, on=["OrderID"]) # update matching keys, insert the rest
old = read_delta_at("silver.orders_live", version=0) # time travel
PySpark DataFrames in short#
df = read_table("silver.sales")
## Choose and rename columns
df.select("region", "order_date", "net_amount")
df.withColumnRenamed("net_amount", "net")
## New columns
df = (df.withColumn("month", F.date_trunc("month", "order_date"))
.withColumn("tier", F.when(F.col("net_amount") >= 1000, "High").otherwise("Standard"))
.withColumn("region", F.upper(F.trim("region"))))
## Filter
df.filter(F.col("net_amount") > 0)
df.filter("region = 'WEST' AND net_amount > 0") # a SQL condition also works
## Aggregate
by_region = (df.groupBy("region", "month")
.agg(F.sum("net_amount").alias("net_sales"),
F.countDistinct("customer_id").alias("customers")))
## Join
customers = read_table("silver.customers")
joined = df.join(customers, on="customer_id", how="left") # inner, left, right, full, left_anti, left_semi
## Window: the latest row per key
w = Window.partitionBy("OrderID").orderBy(F.col("_load_ts").desc())
latest = (read_table("bronze.dbo_Orders")
.withColumn("rn", F.row_number().over(w))
.filter("rn = 1").drop("rn"))
## Sort, de-duplicate, count
by_region.orderBy(F.desc("net_sales"))
df.dropDuplicates(["order_id"])
df.count()
## SQL from Python (use schema.table, not datasimple.schema.table)
spark.sql("SELECT region, SUM(net_amount) AS net FROM silver.sales GROUP BY region")
## To pandas (filter and aggregate first: pandas loads everything into memory)
pdf = by_region.toPandas()
All helpers#
Read
| Helper | Purpose |
|---|---|
read_table(name) |
Governed read. Finds the format in the catalog (Parquet, Delta or Iceberg), checks SELECT, applies row filters and column masks |
read_delta(name), read_parquet(name), read_iceberg(name) |
The same for a catalog name |
read_delta_at(name, version=None, timestamp=None) |
Delta time travel |
list_delta_history(name, limit=20) |
Delta history as rows |
delta_file_count(name) |
Number of files of a Delta table |
read_share(share, table) |
A DataShare table you may read |
read_staged(connection_id, table, workflow_id=None) |
The DataBridge raw copy behind a Workflow box |
read_jdbc(engine, host, port, database, table, user, password, **opts) |
Read a PostgreSQL or MySQL table directly |
Write
| Helper | Purpose |
|---|---|
save_table(df, name, mode="overwrite", partition_by=None, merge_schema=False, fmt=None) |
Delta (or fmt="iceberg"); registers the table, applies quality rules that enforce on write, records lineage. Modes: overwrite, append |
write_delta(df, name, mode="append", partition_by=None, replace_where=None, merge_schema=False, expect=None) |
Delta write; replace_where replaces only the matching rows; expect checks rules first |
merge_delta(target, source_df, on, when_matched="update_all", when_not_matched="insert_all") |
Delta MERGE by key |
create_managed_table(df, name, mode="overwrite", partition_by=None, fmt=None) |
A managed Delta, Parquet or Iceberg table |
create_temp_table(df, name) |
A temporary view, only in your session |
quarantine_write(df, target, rules, mode="append") |
Good rows to the target, bad rows to <target>_errors |
read_errors(target), replay_errors(target, fix=None), purge_errors(target, older_than_days=90) |
Work with the error table |
optimize_table(name), optimize_tables(names) |
Compact small files |
drop_table(name, purge=True) |
Drop a table (files deleted only under the managed area) |
Changes from a source (CDC)
| Helper | Purpose |
|---|---|
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) |
A change feed to a Type 1 (current) or Type 2 (history) table |
ds.apply_changes_from_snapshot(target, snapshot, keys, stored_as_scd_type=1, snapshot_version=None) |
The same from a full copy; missing keys are deleted or closed |
ds.create_target_table(name, comment=None, columns=None, format="delta") |
An empty registered target |
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"])
Declared tables, star schemas and One Big Tables
| Helper | Purpose |
|---|---|
@ds.table(name, mode="merge", keys=None, sequence_by=None, expectations=None, comment=None) |
Declare a table; the function returns its DataFrame |
ds.expect(df, name, rule, action="fail") |
A rule: fail, warn or drop |
@ds.dimension(name, keys, scd=1, …), @ds.fact(name, grain, dimensions=…), @ds.obt(name, fact, …) |
Star schema and One Big Table |
ds.read(name) |
A declared table (built first) or any catalog table |
ds.run() |
Build every declared table in dependency order; returns {table: rows} |
ds.status(), ds.reset(), ds.checks(), ds.models() |
Show or clear the declarations, see the join checks |
@ds.table("silver.orders_clean", mode="merge", keys=["OrderID"], sequence_by="_load_ts",
expectations=[{"name": "amount_positive", "rule": "Amount > 0", "action": "fail"}])
def orders_clean():
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_clean").groupBy("Status").count()
ds.run() # builds silver.orders_clean first, then gold.orders_by_status
Full example: chapter 15.
Data quality
| Helper | Purpose |
|---|---|
profile_table(name) |
Profile a table: counts, nulls, distinct values, ranges |
suggest_checks(name) |
Suggested checks from the data |
check_table(name, checks, level="Warning", fail_on_error=True) |
Run checks; the results show in Data Quality |
table_freshness(name) |
When the table last changed |
expect(df, rules) |
Check rules on a DataFrame |
enforce_pk(...), enforce_fk(...), enforce_not_null(...), enforce_unique(...) |
Validate keys and constraints |
check_table("silver.orders_clean", lambda c: c.isComplete("OrderID").isUnique("OrderID"))
Access
| Helper | Purpose |
|---|---|
grant(privilege, on, principal), revoke(privilege, on, principal) |
Grant or revoke on a table (administrators or MANAGE) |
apply_row_filter(df, expr), apply_column_mask(df, column, expr) |
Apply a filter or mask by hand |
Workflows
A notebook behind a Workflow box gets WF_TEST_MODE, WF_TARGET_DATABASE, WF_SAMPLE_ROWS and workflow_table(table), which returns the box's target table.
Not available, and what to use instead#
| You may know | In DataSimple |
|---|---|
display(df) |
Make the DataFrame the cell's last line |
dbutils.widgets |
A Parameters cell |
dbutils.notebook.run |
A pipeline with one step per notebook |
dbutils.fs |
Read files as tables (DataBridge), or read_parquet / read_delta by name |
%pip install |
Ask your administrator |
spark.readStream / writeStream |
A streaming table: spark.sql("REFRESH STREAMING TABLE silver.orders_live") is not supported from Python; create and refresh it in a SQL cell (7b, 7d) |
spark.read.load("s3://…") |
read_table("<layer>.<table>"): a path bypasses your access rules |
The editor's autocomplete lists every helper with its signature and description; GET /api/notebooks/spark/helpers returns the same list.
Streaming, materialized views and incremental tables#
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;
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.