Walk through every CRUD operation on a Delta table and watch the transaction log create new versions you can time-travel back to.
-- SETUP
CREATE ZONE IF NOT EXISTS demo_zone TYPE EXTERNAL
COMMENT 'External and Delta tables -- demo datasets';
CREATE SCHEMA IF NOT EXISTS demo_zone.delta_demos
COMMENT 'Delta table management tutorial demos';
CREATE DELTA TABLE IF NOT EXISTS demo_zone.delta_demos.products (
id INT,
name VARCHAR,
category VARCHAR,
price DOUBLE,
stock INT,
is_active BOOLEAN
) LOCATION '~/delta-data/products';
INSERT INTO demo_zone.delta_demos.products VALUES
(1, 'Laptop', 'Electronics', 999.99, 50, true),
(2, 'Wireless Mouse', 'Electronics', 29.99, 200, true),
(3, 'USB-C Hub', 'Electronics', 49.99, 150, true),
(4, 'Monitor 27"', 'Electronics', 349.99, 75, true),
(5, 'Keyboard', 'Electronics', 79.99, 120, true),
(6, 'Office Chair', 'Furniture', 299.99, 30, true),
(7, 'Standing Desk', 'Furniture', 599.99, 20, true),
(8, 'Bookshelf', 'Furniture', 149.99, 45, true),
(9, 'Desk Lamp', 'Furniture', 39.99, 0, true),
(10, 'Filing Cabinet', 'Furniture', 89.99, 60, true),
(11, 'Notebook A5', 'Stationery', 5.99, 500, true),
(12, 'Ballpoint Pen', 'Stationery', 1.99, 1000,true),
(13, 'Sticky Notes', 'Stationery', 3.49, 800, true),
(14, 'Highlighter Set', 'Stationery', 7.99, 300, true),
(15, 'Binder Clips', 'Stationery', 2.49, 0, true),
(16, 'Headphones', 'Audio', 149.99, 90, true),
(17, 'Bluetooth Speaker', 'Audio', 79.99, 110, true),
(18, 'Microphone', 'Audio', 199.99, 40, true),
(19, 'Earbuds', 'Audio', 59.99, 0, true),
(20, 'Sound Bar', 'Audio', 249.99, 25, true);
-- QUERIES
-- Baseline: grouped category summary
SELECT category,
COUNT(*) AS product_count,
ROUND(MIN(price), 2) AS min_price,
ROUND(MAX(price), 2) AS max_price,
SUM(stock) AS total_stock
FROM demo_zone.delta_demos.products
GROUP BY category
ORDER BY category;
-- UPDATE #1: 10% price increase for Electronics (creates V2)
UPDATE demo_zone.delta_demos.products
SET price = ROUND(price * 1.10, 2)
WHERE category = 'Electronics';
-- Compare current vs V1 using VERSION AS OF time travel
WITH old AS (
SELECT id, price AS old_price
FROM demo_zone.delta_demos.products VERSION AS OF 1
)
SELECT p.id, p.name, p.category, old.old_price, p.price AS new_price,
CASE WHEN p.price > old.old_price THEN 'Price increased 10%'
ELSE 'Price unchanged' END AS update_note
FROM demo_zone.delta_demos.products p
JOIN old ON p.id = old.id
WHERE p.category IN ('Electronics', 'Furniture')
ORDER BY p.category, p.id;
-- UPDATE #2: deactivate zero-stock products (creates V3)
UPDATE demo_zone.delta_demos.products
SET is_active = false
WHERE stock = 0;
-- DELETE: remove inactive products (creates V4)
DELETE FROM demo_zone.delta_demos.products
WHERE is_active = false;
-- Confirm deletions using VERSION AS OF 3 (pre-delete snapshot)
WITH before_delete AS (
SELECT id, name, category
FROM demo_zone.delta_demos.products VERSION AS OF 3
)
SELECT b.id, b.name, b.category,
CASE WHEN p.id IS NULL THEN 'Deleted' ELSE 'Still exists' END AS status
FROM before_delete b
LEFT JOIN demo_zone.delta_demos.products p ON b.id = p.id
WHERE p.id IS NULL
ORDER BY b.id;
-- INSERT INTO ... SELECT: add 5 new products (creates V5)
INSERT INTO demo_zone.delta_demos.products
SELECT * FROM (VALUES
(21, 'Webcam', 'Electronics', 69.99, 80, true),
(22, 'Footrest', 'Furniture', 49.99, 55, true),
(23, 'Stapler', 'Stationery', 12.99, 200, true),
(24, 'DAC Amplifier', 'Audio', 129.99, 35, true),
(25, 'Whiteboard', 'Furniture', 79.99, 40, true)
) AS t(id, name, category, price, stock, is_active);
-- Final listing: 22 rows (20 - 3 deleted + 5 inserted)
SELECT id, name, category, price, stock, is_active
FROM demo_zone.delta_demos.products
ORDER BY category, name;
-- Assert Electronics prices match old * 1.10 via time travel self-join
SELECT COUNT(*) AS electronics_price_match
FROM demo_zone.delta_demos.products p
JOIN demo_zone.delta_demos.products VERSION AS OF 1 old ON p.id = old.id
WHERE old.category = 'Electronics'
AND ROUND(p.price, 2) = ROUND(old.price * 1.10, 2);
-- CLEANUP
DROP DELTA TABLE IF EXISTS demo_zone.delta_demos.products WITH FILES;
DROP SCHEMA IF EXISTS demo_zone.delta_demos;
DROP ZONE IF EXISTS demo_zone;## When to Use Start here if Delta Lake is new to you. This demo uses a 20-row product catalog to teach the four fundamental Delta operations -- CREATE, INSERT, UPDATE, DELETE -- while highlighting what makes Delta different from plain Parquet: the _delta_log transaction log that records every change as a new version. Because each DML statement produces a commit, you can compare the current table against any previous version using VERSION AS OF without re-running the pipeline. ## What You Will Learn 1. How CREATE DELTA TABLE with LOCATION persists both data files and a _delta_log directory 2. How INSERT, UPDATE, and DELETE each produce a new numbered Delta version 3. Why UPDATE and DELETE are copy-on-write -- old Parquet files are marked removed, not rewritten in place 4. How VERSION AS OF lets you join the current state against a prior version to prove what changed 5. How to use INSERT INTO ... SELECT with a VALUES clause for bulk inserts 6. How ASSERT ROW_COUNT and ASSERT VALUE verify that every mutation produced exactly the expected effect ## Prerequisites None. This demo is fully self-contained -- the setup seeds 20 rows, and the queries section exercises every operation end to end before the cleanup drops the table with its files.
| Name | Type | Description |
|---|---|---|
TABLE_PATH | Filesystem or object-store location for the Delta table and its _delta_log. | |
ZONE_NAME | External zone the schema lives under; created if it does not exist. |