Building a temporal table that records changes over time

Delta Lake tables are inherently temporal: every write creates a new version in the transaction log, letting you query prior states with time travel and, when enabled, capture row-level changes via Change Data Feed (CDF). Together these features let you design tables that record and audit how data changed over time without building custom SCD logic from scratch.

1 · Learn the must-know

• 1Every INSERT, UPDATE, DELETE, or MERGE on a Delta table writes a new version to the _delta_log, and DESCRIBE HISTORY <table> shows the full audit trail (version, timestamp, operation, user, and read/write metrics).
• 2Time travel lets you query a prior version using SELECT * FROM table VERSION AS OF n or TIMESTAMP AS OF 'yyyy-MM-dd HH:mm:ss', which is useful for auditing, reproducing reports, or recovering from bad writes.
• 3Time travel depends on retained log files and data files; VACUUM removes files older than the retention threshold (default 7 days), so aggressive vacuuming will break older time-travel queries—set delta.deletedFileRetentionDuration and delta.logRetentionDuration to match your audit needs.
• 4Change Data Feed (enable with TBLPROPERTIES (delta.enableChangeDataFeed = true)) records row-level inserts, updates, and deletes with _change_type, _commit_version, and _commit_timestamp columns, queryable via table_changes(table, startVersion, endVersion).
• 5For a true history/SCD Type 2 pattern (tracking effective/expiry dates and current-row flags), you typically build it explicitly using MERGE INTO logic on top of Delta rather than relying solely on time travel, since time travel reflects table state, not a queryable history column.
• 6Time travel and CDF are Delta-specific features (not available on plain Parquet/CSV tables), so any "temporal table" design in Databricks should target Delta as the storage format.

2 · Check your understanding