DataSimplePractical GuideLog inOpen DataSimple
DataSimple
Chapter 07bTransform and model

SQL reference: the commands you can use

Back to the index · Previous: 7a. Spark · Next: 7c. Python 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#

  1. 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>.
  2. Several statements are separated by ;. They run in order; the last one's result is shown.
  3. Parameters. {{name}} is replaced by the value of the Parameters cell (or the schedule or pipeline). A parameter with no value stops the cell.
  4. Comments. -- to the end of the line and /* … */.
  5. Your access applies to every statement (7a).

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#

SQL
-- 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.

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

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

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

SQL
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.

SQL
-- 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.

SQL
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.

SQL
-- 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:

  1. A refresh reads the highest value of the watermark column in the source.
  2. It runs your query on the source rows above the value stored by the last refresh, and at most that high value.
  3. Without KEYS, the result is appended; with KEYS, it is applied by key.
  4. 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 BY column (and the columns of APPLY AS DELETES) when you use KEYS.
  • Give the STREAM source an alias (FROM STREAM bronze.dbo_Orders o) when you name its columns as o.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.tables shows it with table_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.

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:

  1. 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.
  2. 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.
  3. 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 … FULL rebuilds 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 REFRESH with another topic or format) starts a new landing table, read from starting.
  • The landing table shows in the catalog under source and 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 … FULL still rebuilds from the messages already landed, and says Kafka could not be read.
  • WATERMARK BY is not used with Kafka: the landing time _load_ts is the watermark.
  • The broker side (network reach, users, retention) is the same as for a DataBridge Kafka load: see chapter 4.

Change tables#

SQL
-- 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 COLUMN and DROP 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 larger DECIMAL). 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 with UPDATE, and drop the old one.
  • Not null on an existing column. SET NOT NULL is refused for a column that allows nulls; add a CHECK (col IS NOT NULL) constraint instead, or declare NOT NULL in CREATE TABLE.
  • Views that select * keep the columns they had when created. After you add, rename or drop a column of their table, run CREATE OR REPLACE VIEW again.
  • Dropped tables stay in Trash for the retention period; UNDROP works until then. A primary key declared with CREATE TABLE is enforced on every write: a duplicate key is refused and the write rolled back.
  • The tenant column _tenantid cannot be dropped, renamed or retyped.

History and recovery#

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

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

SQL
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
SQL
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 balance >= 100000 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.

Back to the index · Next: 7c. Python reference