Create and Refresh a Materialized View in Databricks SQL

1.What a materialized view is, and why it is faster than a view

You query a materialized view the same way you query a table. What sets it apart is when the work happens. A standard view stores only its query, so every SELECT against it runs that query again. A materialized view stores the query's results and refreshes them on a set interval, so a reader gets results that were computed earlier. Because the results are precomputed, queries against a materialized view can run much faster than queries against a regular view.

A materialized view is more than a saved query. Databricks describes it as a declarative pipeline object made of three parts: the query that defines it, a flow that updates it, and the cached results. Unity Catalog stores the view's metadata, and the cached data itself lives in cloud storage. If you create a materialized view outside a Lakeflow pipeline (a *standalone* materialized view), Databricks creates a pipeline to update it for you. That pipeline is listed under Jobs & Pipelines with the pipeline type MV/ST. Materialized views defined inside a pipeline show the type ETL.

The documentation lists these typical uses: keeping a BI dashboard up to date with low query latency for its users, replacing complex ETL orchestration with plain SQL, building layered transformations, and any workload that needs consistent performance with up-to-date insights.

Sources12

2.Creating a materialized view with CREATE MATERIALIZED VIEW

You create a standalone materialized view with a single SQL statement. You can submit it from the SQL editor, the Databricks SQL CLI or the Databricks SQL API. Not every warehouse can run it, though: materialized views can only be created on a Pro or Serverless SQL warehouse, or inside a pipeline. A Classic SQL warehouse is not on that list.

-- This query defines the materialized view:
CREATE OR REPLACE MATERIALIZED VIEW mv1
AS SELECT
  date,
  sum(sales) AS sum_of_sales
FROM
  base_table1
GROUP BY
  date;

The CREATE is synchronous: it doesn't return until the view exists and the initial data load has finished. The user who runs it becomes the view's owner. Because the initial load and later refreshes run on a serverless pipeline, they don't use your SQL warehouse's compute.

Sources23

3.Keeping it current: schedules, triggers and manual refresh

A materialized view is only as current as its last refresh. A refresh updates the view to match the base tables as they were at the moment it ran. You have three ways to start one. A schedule, written as SCHEDULE EVERY n HOURS/DAYS/WEEKS or SCHEDULE CRON with a Quartz cron string, runs on a timer. TRIGGER ON UPDATE refreshes the view when an upstream source changes, at most once per AT MOST EVERY interval. That interval defaults to 1 minute and can't be set lower. Databricks recommends triggers for production workloads, especially when upstream data doesn't arrive on a predictable schedule. Finally, REFRESH MATERIALIZED VIEW runs a refresh on demand without restating the query. It waits for the refresh to finish unless you add ASYNC.

-- Refresh nightly at 3:30 AM UTC.
-- The cron expression uses six space-separated fields: seconds minutes hours day-of-month month day-of-week
-- Use '?' for either day-of-month or day-of-week to leave it unspecified.
CREATE OR REPLACE MATERIALIZED VIEW daily_revenue_by_region
  SCHEDULE CRON '0 30 3 * * ?' AT TIME ZONE 'UTC'
AS SELECT
  date_trunc('day', order_time) AS sales_date,
  region,
  sum(revenue) AS total_revenue,
  count(*) AS order_count
FROM
  orders
GROUP BY sales_date, region;

-- Refresh automatically when the source table is updated.
CREATE OR REPLACE MATERIALIZED VIEW mv_trigger
   ?  ON UPDATE
AS SELECT
  date,
  sum(sales) AS sum_of_sales
FROM
  base_table1
GROUP BY
  date;

Each refresh uses one of two methods. An incremental refresh finds the changes since the last update and merges only the new or modified data. A full refresh runs the whole query again and replaces the stored results. Databricks uses a full refresh when an incremental one isn't possible or wouldn't save money. In the docs' example, a view grouped by country gets 200 new customers in three countries, and only those three rows are rewritten. Either way the result is always correct, even for data that arrives late or out of order. One definition choice rules out incremental refresh entirely: if the view includes CONSTRAINT ... EXPECT expectations, it is fully refreshed on every update.

Sources21

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Creating or refreshing a materialized view from a SQL warehouse uses that warehouse's compute.Why is that wrong?

   The warehouse only submits the statement. The initial load and every later refresh run on a serverless pipeline that Databricks creates for the view.

   Covered in Creating a materialized view with CREATE MATERIALIZED VIEW

2. 2.Any SQL warehouse type can create a materialized view.Why is that wrong?

   Only a Pro or Serverless SQL warehouse, or a pipeline, can create one.

   Covered in Creating a materialized view with CREATE MATERIALIZED VIEW

3. 3.TRIGGER ON UPDATE keeps a materialized view current within milliseconds of every source change.Why is that wrong?

   A trigger refreshes at most once per AT MOST EVERY interval, which can't be less than 1 minute. Materialized views are not designed for low-latency use cases.

   Covered in Keeping it current: schedules, triggers and manual refresh