Databricks ML — Study Notes
Module 2: Delta Lake
1. What is Delta Lake
Delta Lake is an open source storage layer that adds reliability, ACID transactions, and versioning to
your data lake. It sits on top of cloud storage (S3/ADLS/GCS) and adds a transaction log to plain
Parquet files.
Think of it as:
Plain Parquet files + Transaction log (_delta_log/) = Delta Lake table
Problems Delta Lake solves:
→ Two jobs writing simultaneously → data corruption → Delta uses ACID
→ Job fails halfway → partial data lands → Delta rolls back automatically
→ No history → overwrite = data gone forever → Delta keeps all versions
→ Can't query with SQL → Delta tables are fully SQL queryable
→ No schema enforcement → anyone writes anything → Delta enforces schema
2. The Transaction Log (_delta_log/)
Every Delta table has a hidden _delta_log/ folder — this is what makes it Delta.
my_table/
├── [Link] ← actual data
├── [Link] ← actual data
└── _delta_log/
├── [Link] ← table created, inserted 1000 rows
├── [Link] ← inserted 500 more rows
├── [Link] ← deleted 100 rows
└── [Link] ← updated 50 rows
Every change is recorded as a JSON entry — like Git commits for your data. Each entry records what
files were added, removed, and what statistics exist per file.
3. ACID Properties
Delta Lake guarantees all four ACID properties:
A — Atomicity
Either ALL changes succeed or NONE do. No partial writes ever land.
→ Job writes 1M rows, crashes at row 500K → automatic rollback
→ Table stays at previous version, completely unaffected
→ Delta uses two-phase write: write files first, then commit to log
C — Consistency
Data always stays in a valid state. Schema is enforced on every write.
→ Write DataFrame with extra column → REJECTED immediately
→ Write wrong data type → REJECTED
→ Table schema never silently corrupted
Example:
# This is rejected — extra_col not in table schema
bad_df.[Link]('delta').mode('append').saveAsTable('employees')
→ AnalysisException: schema mismatch
I — Isolation
Multiple jobs reading/writing simultaneously each see a consistent snapshot. No dirty reads.
→ Dirty read = reading data another job hasn't committed yet
→ Delta gives each reader a snapshot of last committed version
→ Job B never sees Job A's half-finished write
Example:
Job A: DELETE 500 rows, INSERT 600 rows (takes 30 seconds)
Job B: SELECT COUNT(*) at second 15
→ Job B sees last committed version (before Job A started)
→ NOT the half-deleted state
D — Durability
Once committed, data survives crashes. Transaction log on cloud storage ensures nothing is lost.
→ Crash before commit → files ignored, table unaffected
→ Crash after commit → data permanent, survives any crash
→ Cloud storage (S3/ADLS/GCS) has 99.999999999% durability
4. Key Delta Lake Commands
Create a Delta Table
[Link]('delta').mode('overwrite').saveAsTable('[Link]')
mode What it does
overwrite Delete existing data, write fresh
append Add to existing data, keep old rows
ignore If table exists, do nothing
error If table exists, throw error (default)
DESCRIBE HISTORY — View all versions
DESCRIBE HISTORY [Link];
Shows every operation: CREATE, INSERT, UPDATE, DELETE, OPTIMIZE, RESTORE etc.
Time Travel — Query previous versions
# By version number
df = [Link]('delta').option('versionAsOf', 0).table('employees')
# By timestamp
df = [Link]('delta').option('timestampAsOf', '2026-01-
01').table('employees')
# SQL syntax
SELECT * FROM employees VERSION AS OF 0;
SELECT * FROM employees TIMESTAMP AS OF '2026-01-01';
Schema Enforcement vs Schema Evolution
Schema Enforcement (default) — rejects writes that don't match schema:
[Link]('delta').mode('append').saveAsTable('employees')
→ extra columns → REJECTED
Schema Evolution (opt-in) — allows adding new columns:
[Link]('delta').mode('append')\
.option('mergeSchema', 'true')\
.saveAsTable('employees')
→ new column added to table
→ existing rows get NULL for new column
MERGE — Upsert (Update + Insert)
MERGE INTO target_table AS target
USING source_table AS source
ON [Link] = [Link]
WHEN MATCHED THEN
UPDATE SET [Link] = [Link]
WHEN NOT MATCHED THEN
INSERT (id, name, salary) VALUES ([Link], [Link], [Link]);
Three MERGE clauses:
→ WHEN MATCHED THEN UPDATE SET * → update existing rows
→ WHEN MATCHED THEN DELETE → delete matching rows
→ WHEN NOT MATCHED THEN INSERT * → insert new rows
OPTIMIZE — Compact small files
OPTIMIZE [Link];
Combines many small files into fewer ~128MB files. Reduces file overhead and speeds up queries.
→ Target file size: ~128MB per file
→ About size of files, not number of records
→ Run periodically as maintenance
ZORDER — Co-locate related data
OPTIMIZE [Link] ZORDER BY (department);
Reorders data so related rows are in the same files. Enables data skipping.
→ Pick columns you frequently filter by in WHERE clauses
→ Spark records min/max statistics per file per column
→ Queries can skip entire files that can't contain matching rows
→ Always combine with OPTIMIZE: OPTIMIZE ... ZORDER BY (col)
VACUUM — Remove old files
VACUUM [Link] RETAIN 168 HOURS;
Deletes files no longer needed for time travel. Reduces storage costs.
→ Default retention: 168 hours (7 days)
→ RETAIN 0 HOURS → dangerous, breaks time travel for old versions
→ Run after OPTIMIZE to free up space from compacted files
RESTORE — Undo changes
RESTORE TABLE [Link] TO VERSION AS OF 0;
Goes back to a previous version. Does NOT delete history — adds a new version that looks like the
old one.
5. Delta vs Other Formats
Feature Delta Parquet CSV JSON
ACID transactions ✅ ❌ ❌ ❌
Time travel ✅ ❌ ❌ ❌
Schema ✅ ❌ ❌ ❌
enforcement
MERGE support ✅ ❌ ❌ ❌
OPTIMIZE/ ✅ ❌ ❌ ❌
ZORDER
SQL queryable ✅ ✅ ✅ ✅
Default in ✅ ❌ ❌ ❌
Databricks
6. Data Skipping
When ZORDER runs, Delta records statistics (min/max values) per column per file in the transaction
log. Spark uses these to skip files that can't contain matching rows.
[Link]: department min='Engineering', max='Engineering'
[Link]: department min='Finance', max='Marketing'
Query: WHERE department = 'Engineering'
→ File-001: might have Engineering ✅ read it
→ File-002: no Engineering possible ❌ SKIP entirely
Result: Spark reads far less data → much faster queries.
7. Exam Questions & Answers
Q: What is Delta Lake?
A: An open source storage layer that adds ACID transactions, time travel, schema enforcement, and
versioning to cloud storage. Built on Parquet + transaction log.
Q: What is the transaction log?
A: A hidden _delta_log/ folder that records every change to the table as JSON entries. Like Git
commits for data.
Q: What happens if a job fails halfway through writing to a Delta table?
A: Atomicity kicks in — partial write is automatically rolled back. Table stays at previous version
with no corruption.
Q: What does DESCRIBE HISTORY do?
A: Shows all versions of a Delta table — every operation (CREATE, INSERT, UPDATE,
DELETE, OPTIMIZE, RESTORE) with timestamp and user.
Q: How do you query a Delta table at a previous version?
A: Time travel: .option('versionAsOf', 0) for version number, or .option('timestampAsOf', '2026-01-
01') for timestamp.
Q: What is schema enforcement?
A: Delta rejects writes that don't match the table schema. Extra columns, wrong data types are
rejected. Table always stays in valid state.
Q: How do you allow adding new columns to a Delta table?
A: Use .option('mergeSchema', 'true'). Existing rows get NULL for the new column.
Q: What does MERGE do?
A: Combines UPDATE and INSERT in one command. Updates existing rows (WHEN
MATCHED), inserts new rows (WHEN NOT MATCHED). Also supports WHEN MATCHED
THEN DELETE.
Q: What does OPTIMIZE do?
A: Compacts many small files into fewer ~128MB files. Reduces file overhead and speeds up
queries. Target is file size, not row count.
Q: What does ZORDER do?
A: Co-locates related data in the same files. Enables data skipping — Spark skips files that can't
contain matching rows using min/max statistics.
Q: Which columns should you ZORDER by?
A: Columns you frequently use in WHERE clause filters. Don't ZORDER by columns you never
filter on.
Q: What does VACUUM do?
A: Removes old files no longer needed for time travel. Default retention is 168 hours (7 days).
Reduces storage costs.
Q: What is the risk of VACUUM RETAIN 0 HOURS?
A: All old files deleted permanently. Time travel only works for current version. Cannot go back to
previous versions.
Q: What does RESTORE do?
A: Returns the table to a previous version. Does NOT delete history — adds a new version. Can
still access all previous versions after restore.
Q: What is a dirty read?
A: Reading data that another job hasn't committed yet. Delta prevents this with snapshot isolation
— each reader sees last committed version.
Q: What is the difference between .save() and .saveAsTable()?
A: save() writes files to a path, not registered in catalog. saveAsTable() writes AND registers in
Unity Catalog — queryable by name by anyone with access.
Q: What is data skipping?
A: Spark uses file-level min/max statistics recorded by Delta to skip entire files that can't contain
matching rows for a WHERE clause filter.
Q: What is the default write mode in Spark?
A: error — throws an error if the table already exists. Use mode('overwrite') to replace,
mode('append') to add rows.
8. Quick Reference — All Commands
Command Purpose
[Link]('delta').mode('overwrite').saveAsTabl Create/overwrite Delta table
e('...')
[Link]('delta').mode('append').saveAsTable(' Append to Delta table
...')
DESCRIBE HISTORY table View all versions and operations
.option('versionAsOf', N) Time travel by version number
.option('timestampAsOf', 'date') Time travel by timestamp
.option('mergeSchema', 'true') Allow schema evolution
MERGE INTO ... USING ... ON ... Upsert — update existing + insert new
OPTIMIZE table Compact small files into ~128MB
OPTIMIZE table ZORDER BY (col) Compact + co-locate related data
VACUUM table RETAIN 168 HOURS Remove old files (keep 7 days)
RESTORE TABLE ... TO VERSION AS OF N Restore to previous version
DESCRIBE DETAIL table View table details — location, size, format
Databricks ML Associate Exam Prep — Module 2: Delta Lake