5.3 Views, Materialized Views & Delta Time Travel

Databricks Certified Data Analyst Associate Exam

Track your progress as you read

Finished sections get a check mark.

1 Platform Overview & Unity Catalog Foundations

1.1 Databricks Architecture & Compute Concepts 1.2 Databricks Marketplace & Data Sharing Basics 1.3 Unity Catalog Namespace & Object Hierarchy 1.4 Governed Asset Discovery & System Tables

2 Managing & Cleaning Governed Data

2.1 Dataset Discovery & Asset Tagging 2.2 Lineage Inspection & Data Traceability 2.3 SQL Data Cleaning & Transformation Techniques

3 Data Ingestion & Import Patterns

3.1 Workspace Uploads & Cloud File Ingestion 3.2 Incremental File Ingestion with Auto Loader 3.3 Ingesting Data via Delta Sharing & External Connections

4 Databricks SQL Warehouses & Core Querying

4.1 Databricks SQL Interface & SQL Assistant 4.2 SQL Warehouses Sizing & Management 4.3 Table Creation & Data Modification in Databricks SQL

5 Advanced SQL Authoring & Time Travel

5.1 Advanced Aggregations, Window Functions & Analytics 5.2 Complex Joins, CTEs & Set Operations 5.3 Views, Materialized Views & Delta Time Travel

6 Query Performance Analysis & Photon Engine

6.1 Photon Engine Architecture & Acceleration 6.2 Query History & Execution Profile Inspection 6.3 Delta Lake Audit History & Commit Logs

7 Liquid Clustering, Caching & Performance Troubleshooting

7.1 Caching Hierarchy in Databricks SQL 7.2 Data Layout Optimization: Liquid Clustering vs Partitioning & Z-Ordering 7.3 Troubleshooting Slow & Failing Queries

8 AI/BI Dashboards & Visualization Design

8.1 AI/BI Dashboards Overview & Canvas Layout 8.2 Chart Types & Visualization Selection 8.3 Notebook & SQL Editor Visualizations

9 Dashboard Interactivity, Publishing & Alerts

9.1 Dashboard Parameters & Interactive Filtering 9.2 Publishing, Sharing & Access Control 9.3 Scheduled Refresh, Email Subscriptions & Alerts

10 Developing & Maintaining AI/BI Genie Spaces

10.1 AI/BI Genie Architecture & Space Setup 10.2 Curation via Instructions, Descriptions & Sample Questions 10.3 Trusted Assets & Parameterized Functions 10.4 Monitoring, Benchmarks, Feedback & Maintenance

11 Data Modeling & Medallion Architecture

11.1 Dimensional Modeling Fundamentals 11.2 Medallion Architecture for Analytics 11.3 Constraint Enforcement & Relationships in Unity Catalog

12 Securing Data & Unity Catalog Governance

12.1 Unity Catalog Privilege Model & Grants 12.2 Ownership, Inheritance & Access Control Patterns 12.3 Fine-Grained Security: Row Filters & Column Masks

5.3 Views, Materialized Views & Delta Time Travel

Key Takeaways Explain This Topic AI

Standard Views in Databricks SQL store only the defining query logic in Unity Catalog metadata without persisting physical data, re-executing the underlying SQL query upon every user reference.

Choose Streaming Tables for continuous arrivals, Materialized Views for repeated complex BI queries on curated data, and standard views when you need non-persisted dynamic logic.

Materialized Views (MVs) in Databricks SQL SQL Warehouses compute and persist query results physically, enabling incremental refresh via Delta Live Tables engine background processing without full table scans.

Delta Lake Time Travel allows users to query historic snapshots of a Delta table using TIMESTAMP AS OF or VERSION AS OF syntax for up to the retention window defined by delta.logRetentionDuration (default 30 days).

The VACUUM command permanently removes data files no longer referenced by a Delta table log that are older than the retention threshold (retention default of 7 days / 168 hours), disabling Time Travel prior to that commit.

Last updated: July 2026

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.

View Type Scope / Governance Physical Storage Compute Strategy on Query Refresh Mechanism

Standard View Unity Catalog (Global) Metadata Only (0 Bytes) Re-evaluates SQL query dynamically Real-time on every query

Temporary View Session / Connection Only Metadata Only Re-evaluates SQL query in session Session lifespan only

Materialized View Unity Catalog (Global) Persisted Delta Table Reads pre-computed stored results Incremental background refresh -- 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:

Need Prefer Why

Continuously arriving event/sensor data that must stay near real time Streaming Table Incrementally ingests append-style streams and keeps results fresh for ongoing arrivals

Frequent, expensive queries over mostly-static or slowly changing curated data (BI dashboards) Materialized View Stores precomputed results and refreshes on a schedule/trigger; ideal for repeated complex aggregations

Always-current logic with no stored result set; light transforms and governance abstractions Standard / dynamic view Recomputes on each query; no physical persistence

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.