5.3 Views, Materialized Views & Delta Time Travel

In Databricks SQL and Unity Catalog, views provide essential abstraction layers for data governance, access control, and query simplification. Meanwhile, Delta Lake's underlying ACID log architecture unlocks powerful historical auditing and recovery features through Delta Time Travel. Mastering the distinctions between view types and understanding time travel operations is critical for any Databricks Data Analyst.

Views Architecture in Unity Catalog & Databricks SQL

Databricks SQL supports three distinct categories of view objects, each tailored to specific operational scoping, performance, and persistence requirements.

1. Standard Views (CREATE VIEW)

A standard view is a logical query definition saved inside Unity Catalog metadata. Standard views store no physical data on disk. When a user queries a standard view, Databricks SQL dynamically evaluates the underlying query logic against the source tables in real time.

• Governance Benefit: Standard views allow data teams to implement row-level and column-level security filters without duplicating physical data.
• Performance Consideration: Since data is not pre-computed, querying a complex standard view incurs the full compute cost of executing the underlying query every time.

-- Standard view created in Unity Catalog
CREATE VIEW main.sales.v_active_us_customers AS
SELECT customer_id, customer_name, state, email
FROM main.crm.customers
WHERE country = 'US' AND status = 'ACTIVE';

2. Temporary Views (CREATE TEMP VIEW)

Temporary views are session-scoped abstractions. They exist only within the active Databricks SQL warehouse connection or notebook session and are automatically dropped when the session ends.

CREATE TEMPORARY VIEW temp_monthly_summary AS
SELECT DATE_TRUNC('month', order_date) AS mth, SUM(amount) AS total_amount
FROM main.sales.orders
GROUP BY DATE_TRUNC('month', order_date);

3. Materialized Views (CREATE MATERIALIZED VIEW)

Materialized Views (MVs) combine the simplicity of views with the performance of pre-computed physical tables. Unlike standard views, Materialized Views compute and persist query results physically to storage. Databricks SQL utilizes the Delta Live Tables (DLT) serverless background engine to incrementally refresh MVs when source table data changes.

-- Materialized View in Databricks SQL Warehouse
CREATE MATERIALIZED VIEW main.sales.mv_daily_regional_performance AS
SELECT 
    region, 
    CAST(order_date AS DATE) AS sale_date, 
    COUNT(order_id) AS total_orders, 
    SUM(amount) AS total_revenue
FROM main.sales.orders
GROUP BY region, CAST(order_date AS DATE);

---

Streaming Tables vs Materialized Views vs Dynamic Views

Databricks sample questions emphasize choosing the right declarative table type. Use this decision framework:

Practical distinctions

• Streaming Tables shine when the business question is “what is happening now as files/events land?” — often fed by Auto Loader / declarative pipeline patterns.
• Materialized Views shine when the business question is “run this heavy join/aggregate quickly for many dashboard viewers.”
• Dynamic/standard views shine when freshness of source tables matters more than precomputation, or when you want a governed projection without owning storage.

-- Materialized view for dashboard-ready aggregates
CREATE MATERIALIZED VIEW main.gold.daily_revenue_mv AS
SELECT order_date, SUM(amount) AS revenue
FROM main.silver.orders
GROUP BY order_date;

-- Streaming table pattern (conceptual): keep results current as new events arrive
-- CREATE STREAMING TABLE main.silver.sensor_readings AS SELECT ... FROM STREAM(...);

Exam trap: do not pick a Materialized View for continuous sensor arrival when the prompt stresses real-time ingestion, and do not pick a Streaming Table when the prompt stresses repeated complex BI queries on relatively static curated data.

Delta Lake Transaction Log & Time Travel Mechanics

Delta Lake tables record all mutations (inserts, updates, deletes, overwrites) as atomic commits in an underlying JSON transaction log stored inside the _delta_log/ directory. Each commit creates a new numerical version file (00000000000000000000.json, 00000000000000000001.json, etc.).

Because Delta Lake retains historical log commits and underlying Parquet data files, analysts can query historical snapshots of a table at any prior point in time—a feature known as Delta Time Travel.

Time Travel Query Syntax

Databricks SQL provides two equivalent SQL syntaxes for querying historical snapshots:

-- 1. Querying by Timestamp using TIMESTAMP AS OF
SELECT * FROM main.sales.orders 
TIMESTAMP AS OF '2026-04-15 00:00:00';

-- 2. Querying by Version Number using VERSION AS OF
SELECT * FROM main.sales.orders 
VERSION AS OF 42;

-- 3. Inline @ Syntax shortcut
SELECT * FROM main.sales.orders@v42;
SELECT * FROM main.sales.orders@20260415000000;

---

Practical Applications of Delta Time Travel

1. Data Auditing & Reproducible Reporting

Analysts can verify historical financial reports or reproduce machine learning training datasets by pinning queries to exact table versions, ensuring identical results even as source tables continue to ingest live data.

2. Rollback & Disaster Recovery with RESTORE

If an erroneous UPDATE or DELETE statement corrupts a Delta table, analysts can restore the table to a known good historical state in a single atomic transaction using the RESTORE command:

-- Roll back a corrupted table to version 15
RESTORE TABLE main.sales.orders TO VERSION AS OF 15;

-- Roll back to a specific timestamp
RESTORE TABLE main.sales.orders TO TIMESTAMP AS OF '2026-04-30 12:00:00';

3. Delta Table History Inspection

To view all historical commits, user identities, timestamps, and operations performed on a table, use DESCRIBE HISTORY:

DESCRIBE HISTORY main.sales.orders;

---

Data Retention, VACUUM, and Time Travel Limits

Delta Lake tables maintain historical versions based on table property retention configurations:

• delta.logRetentionDuration (Default: 30 days): Controls how long transaction log history is preserved before commit log files are cleaned up.
• delta.deletedFileRetentionDuration (Default: 7 days / 168 hours): Controls how long unreferenced data files (marked for deletion by updates or deletes) are retained on storage.

The VACUUM Command

To reclaim cloud storage space and remove stale data files no longer referenced by the current Delta log, administrators execute VACUUM.

-- Purges unreferenced data files older than 168 hours (7 days)
VACUUM main.sales.orders RETAIN 168 HOURS;

Critical Exam Rule: Executing VACUUM permanently deletes underlying data files older than the retention threshold from storage. Once a table is vacuumed, Time Travel queries targeting table versions prior to the vacuum threshold will fail with a file not found error.