Tables, Views, Materialized Views and Time Travel

What it is

In Unity Catalog, a table stores data, a view stores only a query, a materialized view stores a query's precomputed result and refreshes it, and a streaming table appends new data incrementally.

Every Delta write creates a new version, and time travel queries or restores an earlier one.

How it works

Managed and external tables

CREATE TABLE main.sales.orders_clean AS
SELECT * FROM main.sales.orders WHERE amount > 0;

CREATE TABLE main.sales.orders_ext (order_id INT, amount DOUBLE)
LOCATION 's3://acme-lake/sales/orders_ext';

The first statement creates a managed table in the storage set by MANAGED LOCATION on the schema or, failing that, the catalog. The second creates an external table at a path you give, which needs an external location and the CREATE EXTERNAL TABLE privilege on it.

Dropping a managed table removes its data; dropping an external table leaves the files.

Unifying several sources in one table

read_files reads CSV, JSON, Parquet and other files straight from a volume or cloud path, so a single CREATE TABLE ... AS SELECT (CTAS) can join files with Delta tables.

CREATE TABLE main.sales.orders_unified AS
SELECT o.order_id, o.amount, c.segment, p.category
FROM main.sales.orders AS o
JOIN read_files('/Volumes/main/raw/files/customers.csv', format => 'csv', header => true) AS c
  ON o.customer_id = c.customer_id
JOIN read_files('/Volumes/main/raw/files/products.parquet', format => 'parquet') AS p ON o.product_id = p.product_id;

The result is one managed Delta table that dashboards and Genie Agents can read.

Views, dynamic views and materialized views

A standard view is sometimes called dynamic because it reruns its query on every read. A dynamic view in the stricter sense uses current_user() or is_account_group_member() so different users see different rows or masked columns.

Creating a materialized view

CREATE OR REPLACE MATERIALIZED VIEW main.reporting.daily_revenue
  SCHEDULE EVERY 1 HOUR
AS SELECT order_date, region, sum(amount) AS revenue
FROM main.sales.orders
GROUP BY order_date, region;

A SQL warehouse runs the CREATE, and refreshes run on a serverless pipeline Databricks creates for the view. Refresh on a schedule (SCHEDULE EVERY or SCHEDULE CRON), when sources change (TRIGGER ON UPDATE), or manually with REFRESH MATERIALIZED VIEW.

If a refresh fails, the materialized view keeps serving the last successfully refreshed data.

When to use a streaming table instead

CREATE OR REFRESH STREAMING TABLE main.raw.events AS
SELECT * FROM STREAM read_files('/Volumes/main/raw/landing/events/', format => 'json');

Choose a streaming table for append only sources, such as files or event logs, where each refresh reads only new records. Choose a materialized view for joins and aggregations that must reflect source updates and deletes.

Time travel

SELECT * FROM main.sales.orders VERSION AS OF 12;
SELECT * FROM main.sales.orders TIMESTAMP AS OF '2026-10-01';
DESCRIBE HISTORY main.sales.orders;
RESTORE TABLE main.sales.orders TO VERSION AS OF 12;

DESCRIBE HISTORY lists each version with its timestamp, operation and user. RESTORE makes an old version current again as a new commit. VACUUM deletes data files no longer referenced; once they are gone, versions that need them can no longer be queried.

How the exam asks about this

• "A dashboard aggregates two billion rows and must load in seconds; hourly freshness is fine." Create a materialized view with SCHEDULE EVERY 1 HOUR.
• "Yesterday's update overwrote prices; show the table as it was before." Find the version with DESCRIBE HISTORY, then query VERSION AS OF or RESTORE.
• "Combine a CSV file, a Parquet file and a Delta table into one governed dataset." A CTAS joining read_files with the Delta table.