What you will be able to do
- Create a materialized view with CREATE MATERIALIZED VIEW and keep it current with a manual REFRESH, a SCHEDULE or TRIGGER ON UPDATE
- Explain what runs a materialized view refresh and which SQL warehouse types can create one
- Choose between a streaming table, a materialized view and a pipeline view for a given workload
- Tell a dynamic view apart from a materialized view, and protect rows or columns in either one
Key concept
Materialized view — A view whose query results are computed ahead of time and stored, then brought up to date by a refresh. Readers get the stored result and do not wait for the query to run again.
1.What a materialized view stores, and what keeps it current
A materialized view saves the result of a query so readers do not pay for running that query. An ordinary view (saved query) holds only its definition and runs the query again on every read. A materialized view runs it once, keeps the output, and recalculates it when it is refreshed. You query it the same way as a table. Because the result is already computed, a dashboard reading it can respond much faster than one reading a regular view over the same base tables.
The good fits are reads that are expensive or frequent. Typical examples are serving precomputed results to BI dashboards and reports, and building layered transformations that clean, enrich and denormalize base tables. A materialized view is always correct as of its last update: all required data is processed, including data that arrives late or out of order. It is not a low-latency object. An update takes seconds or minutes, not milliseconds.
Each materialized view is backed by a pipeline. Pipelines are the declarative ETL engine described under Delta Live Tables / Lakeflow Declarative Pipelines. When you create a materialized view from Databricks SQL, outside any pipeline you wrote, Databricks creates a serverless pipeline for it, and that pipeline runs the initial load and every later refresh. You can find it under Jobs & Pipelines, where the Pipeline type column shows MV/ST for these standalone objects and ETL for objects defined inside a pipeline. Catalog Explorer shows the status of refresh operations. Streaming tables created from Databricks SQL work the same way.
Creating one has a compute requirement: it needs a Pro or Serverless SQL warehouse, or a pipeline. A Classic warehouse cannot create a materialized view. The user who runs the CREATE statement becomes the owner of the materialized view.
Checkpoint 1 of 7· Check yourself
An analyst runs CREATE MATERIALIZED VIEW from a Serverless SQL warehouse to back a sales dashboard. Later the view is refreshed on a schedule. What does the work of that refresh?
Databricks creates a serverless pipeline for each materialized view made outside a pipeline, and that pipeline runs the initial load and every refresh. Rerunning the query on every read is how an ordinary view behaves, not a materialized view.
“a serverless pipeline is created to process the create and refreshes to the materialized view”Source: docs.databricks.com
2.Creating a materialized view and choosing how it refreshes
The statement is CREATE MATERIALIZED VIEW name AS query, with optional clauses between the name and AS. OR REPLACE replaces an existing view along with its contents. IF NOT EXISTS skips the statement if the name is already in use. You can use one of these two, not both. Creation is synchronous: the command blocks until the view exists and the initial data load has finished. In the SELECT list, expressions and aggregates must have aliases, so write sum(revenue) AS total_revenue and not a bare sum(revenue).
-- 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;A refresh can happen in three ways, and you choose with a clause or a command. Note the time zone on cron schedules. If AT TIME ZONE is omitted, the session time zone is used, and if no session time zone is set either, the statement fails with an error.
| Mechanism | How you set it | When it fits |
|---|---|---|
| Manual | REFRESH MATERIALIZED VIEW mv1; (add ASYNC to return immediately) | Ad hoc updates, or a step run by your own orchestration |
| Fixed interval | SCHEDULE EVERY number HOUR(S) / DAY(S) / WEEK(S): 1–72 hours, 1–31 days, 1–8 weeks | Sources that load on a predictable cadence |
| Cron | SCHEDULE CRON cron_string [AT TIME ZONE timezone_id] | A specific clock time, such as before the business day starts |
| On upstream change | TRIGGER ON UPDATE [AT MOST EVERY trigger_interval] | Upstream Delta tables that load at unpredictable times |
For production work where upstream jobs do not run on a predictable schedule, use TRIGGER ON UPDATE. The view refreshes when an upstream source changes, at most once per minute. AT MOST EVERY sets a longer minimum gap between refreshes, and the interval cannot be under one minute. The upstream sources must be Delta tables, which includes other materialized views and streaming tables, and a single materialized view can watch no more than 10 of them.
-- Refresh automatically when the source table is updated.
CREATE OR REPLACE MATERIALIZED VIEW mv_trigger
TRIGGER ON UPDATE
AS SELECT
date,
sum(sales) AS sum_of_sales
FROM
base_table1
GROUP BY
date;The refresh runs on the view's own serverless pipeline, and the warehouse that created the view only monitors it. A refresh brings the view in line with the base tables as they stand when the refresh runs. There are two strategies. An incremental refresh finds what changed since the last update and merges only new or modified data. A full refresh reruns the whole query and replaces the stored result, which happens when an incremental refresh cannot be done or would cost more. Databricks chooses between them. Not every query can be computed incrementally, and some input changes force a full recompute that can be expensive.
Refreshing a view is not limited to its owner. The owner and any user granted the REFRESH privilege can run REFRESH MATERIALIZED VIEW. By default the command is synchronous and blocks until it finishes. Add ASYNC to get the session back right away.
Checkpoint 2 of 7· Fill the gap
Upstream jobs load base_table1 at unpredictable times. Which keyword completes this production materialized view so that it refreshes when its source changes?
-- 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;TRIGGER ON UPDATE refreshes the view when an upstream Delta table changes, at most once per minute by default. Databricks recommends it for production when upstream jobs don't run on a predictable schedule. SCHEDULE takes only EVERY or CRON, which are fixed timings, and REFRESH is a separate command for running a refresh by hand.
Source: docs.databricks.com3.Streaming table, materialized view or view: picking the dataset type
Whether you write a pipeline or create standalone objects in Databricks SQL, there are three dataset types, and their processing semantics differ. The deciding question is not whether the query aggregates. It is how the source changes over time.
A streaming table is a Delta table built for incremental processing. It reads each input row exactly once and assumes the source only appends. That matches ingestion: files arriving in cloud storage through Auto Loader, or events from Kafka, Event Hubs or Pub/Sub. A streaming table also handles high volumes and gives low latency, so it suits near-real-time feeds. In Databricks SQL you create one with CREATE OR REFRESH STREAMING TABLE, and the query has to be a streaming query that reads with the STREAM keyword.
CREATE OR REFRESH STREAMING TABLE sales
SCHEDULE EVERY 1 hour
AS SELECT * FROM STREAM read_files(
"/Volumes/my_catalog/my_schema/my_volume/path/to/data",
format => "json"
);Reading each row once is fast, and it also means rows are never revisited. After a row has been appended, later pipeline updates leave it alone. If you change the query from LOWER(name) to UPPER(name), only rows that arrive after the change come out uppercase, and fixing the older rows takes a full refresh. Joins behave the same way: a streaming table does not recompute a join when a dimension table changes. That is acceptable when speed matters more than correctness, and wrong when the result has to stay correct.
A materialized view is the type for data that changes. It recomputes as needed to reflect the current state of its sources, so updates, deletes, corrections, joins and aggregations stay consistent. Its result is a Unity Catalog table, so many dashboards, other pipelines and jobs can read one precomputed result instead of each running the query separately.
A view in a pipeline (declared with CREATE TEMPORARY VIEW) is evaluated on demand and never persisted, so it uses no storage. It is private to the pipeline that defines it and is not added to the catalog. Use it to split a long transformation into readable steps, or to validate intermediate results with expectations, when nothing outside the pipeline needs to read that step.
| Dataset type | How records are processed | Persisted? | Readable outside the pipeline? | Choose it for |
|---|---|---|---|---|
| Streaming table | Each record once, assuming an append-only source | Yes, Unity Catalog managed table | Yes | Ingesting a continuously growing feed with low latency |
| Materialized view | Recomputed as needed to match the current source state | Yes, Unity Catalog managed table | Yes | Aggregates and joins over data that gets updates or deletes; results shared by several consumers |
| View | Evaluated on demand | No | No, same pipeline only | Intermediate steps and checks that do not need publishing |
Checkpoint 3 of 7· Check yourself
A finance team's ledger table gets late corrections as UPDATEs and occasional DELETEs. Several dashboards need monthly totals from it that match the ledger as it stands now. Which dataset type should hold those totals?
A streaming table assumes an append-only source and reads each row only once, so later corrections never reach the totals it has already produced. A materialized view recomputes to match the current state of its sources, and a pipeline view is not published to the catalog, so dashboards could not read it.
“Streaming tables are designed for append-only data sources and process inputs only once.”Source: docs.databricks.com
Checkpoint 4 of 7· Exam question
A data analyst at a retail company needs to ingest a continuously growing, append-only clickstream events feed from cloud storage into Databricks, processing each new file exactly once with the lowest possible latency for downstream near-real-time dashboards. Which dataset type should the analyst use to define this in a Lakeflow Spark Declarative Pipeline?
Correct answer: A — Define the dataset as a streaming table so each new record is processed exactly once as it arrives, minimizing latency for the append-only feed.
- A. Streaming tables assume an append-only source and process each record exactly once as it lands, which delivers the millisecond-level latency this continuously growing clickstream feed needs.
- B. Materialized views recompute cached aggregates on a refresh cadence rather than incrementally processing each arriving record, so they suit periodic summaries rather than lowest-latency ingestion.
- C. A plain view is evaluated on demand and never persists data, so it adds no ingestion mechanism at all and cannot efficiently absorb a continuously arriving feed.
- D. A manually refreshed external table depends on an analyst triggering each reload, which cannot deliver exactly-once, low-latency processing of new files as they arrive.
Checkpoint 5 of 7· Exam question
An analytics team publishes a daily net revenue summary that several BI dashboards and downstream jobs all query. The source orders table receives corrections through both inserts and updates, and the team wants Databricks to precompute the aggregation once and serve cached results to every consumer instead of recalculating it separately for each dashboard. Which dataset type best fits this requirement?
Correct answer: A — A materialized view, because it caches the aggregated result and lets multiple downstream consumers read precomputed data instead of recalculating it.
- A. A materialized view persists the aggregated result and shares that single precomputed output across many consumers, which fits an update-and-delete source feeding multiple dashboards.
- B. Streaming tables expect an append-only source and are not designed to correctly reconcile inserts mixed with updates, so they are a poor fit for a table that receives corrections.
- C. A plain view recomputes the aggregation for every query rather than caching it, which forces every dashboard and job to redo the same expensive calculation.
- D. Time travel returns a fixed historical version of the table rather than a live, precomputed aggregation, so it does not serve a continuously updated shared summary.
Checkpoint 6 of 7· Exam question
While building a Lakeflow Spark Declarative Pipeline, an analyst wants to break a complex transformation into an intermediate logical step purely to make the pipeline easier to read and validate. This intermediate step should not persist any data or consume storage, and it only needs to be usable by other datasets inside the same pipeline. Which dataset type should the analyst define for this step?
Correct answer: A — A view, because it is evaluated on demand, is never persisted to storage, and is only accessible to other datasets inside the same pipeline.
- A. A view in a declarative pipeline is evaluated on demand, never materialized to storage, and is scoped to the pipeline that defines it, which matches a lightweight intermediate step used only for readability.
- B. A materialized view would persist and cache the intermediate result on disk, adding storage cost the analyst explicitly wants to avoid for a step that only exists to organize logic.
- C. A streaming table persists its output and is meant for incremental ingestion of append-only data, not for a throwaway logical step confined to a single pipeline.
- D. Registering the step as an external table would expose it workspace-wide through Unity Catalog, which is broader access and more storage than an internal readability step needs.
4.Dynamic views versus materialized views
A dynamic view is a standard view whose definition checks who is running the query. It uses identity functions such as current_user() and is_account_group_member() to decide, per user, which rows come back and which column values are shown. It stores nothing. The logic runs at query time for whoever is querying, so two analysts reading the same dynamic view can see different results.
CREATE VIEW sales_redacted AS
SELECT
user_id,
CASE WHEN
is_account_group_member('auditors') THEN email
ELSE 'REDACTED'
END AS email,
country,
product,
total
FROM sales_rawThe same pattern filters rows if you put the CASE in a WHERE clause. For example, only members of managers see transactions above 1,000,000. During query analysis, Spark replaces the CASE with either the real value or the literal, so the masking costs nothing in query performance. For Unity Catalog data, use is_account_group_member(), which checks account-level groups. The older is_member() checks only workspace-level groups and is kept for Hive metastore compatibility. A dynamic view only protects data if users cannot go around it, so do not grant them read access on the tables it references.
Dynamic views have their own compute requirements: a SQL warehouse, compute in standard access mode, or dedicated access mode on Databricks Runtime 15.4 LTS or above. Dedicated compute on 15.3 or below cannot read them.
A materialized view answers a different question. It is about storing a result to make reads faster, not about choosing what each user sees. It can still restrict data per user. Leaving sensitive columns out of the query is one option. The other is the WITH ROW FILTER and MASK clauses in CREATE MATERIALIZED VIEW, which attach functions that can check the querying user's identity or group membership. The standard example hides a tax_id column from users who are not in HumanResourcesDept.
| Aspect | Dynamic view | Materialized view |
|---|---|---|
| Stores results | No, runs at query time | Yes, precomputed and refreshed |
| Main purpose | Fine-grained access control: row-level security and data masking | Faster, cheaper reads of expensive or frequently used queries |
| Per-user logic | current_user() / is_account_group_member() in SELECT or WHERE | ROW FILTER and MASK clauses at creation |
| Created with | CREATE VIEW | CREATE MATERIALIZED VIEW on a Pro or Serverless SQL warehouse, or in a pipeline |
Checkpoint 7 of 7· Fill the gap
This dynamic view reads Unity Catalog data and must show email addresses only to members of the account-level auditors group. Which function completes it?
CREATE VIEW sales_redacted AS
SELECT
user_id,
CASE WHEN
? ('auditors') THEN email
ELSE 'REDACTED'
END AS email,
country,
product,
total
FROM sales_rawis_account_group_member() checks account-level group membership when the query runs, so each user sees either the real email or REDACTED. It is the function recommended for Unity Catalog data. is_member() checks only workspace-level groups and is kept for Hive metastore compatibility. current_user() returns the user's identity, not whether they belong to a group.
Source: docs.databricks.comPractise it for real
Create a materialized view from the SQL editor, refresh it on demand, then switch it to refresh automatically.
1.On a Pro or Serverless SQL warehouse, run CREATE OR REPLACE MATERIALIZED VIEW mv1 AS SELECT date, sum(sales) AS sum_of_sales FROM base_table1 GROUP BY date; against a table you own.
Why: Materialized views can only be created on a Pro or Serverless SQL warehouse or in a pipeline, and aggregates need aliases.
You should see: The statement blocks until the initial load finishes, then mv1 can be queried like a table, with you as its owner.
2.Open Jobs & Pipelines, add the Pipeline type column, and find the pipeline behind mv1.
Why: Every standalone materialized view gets its own serverless pipeline, which runs its refreshes.
You should see: A pipeline with type MV/ST.
3.Insert rows into base_table1, then run REFRESH MATERIALIZED VIEW mv1;
Why: A manual refresh brings the view in line with the base table as it stands at refresh time.
You should see: The command returns when the refresh completes, and the new sales appear in mv1.
4.Run REFRESH MATERIALIZED VIEW mv1 ASYNC;
Why: ASYNC returns control without waiting for the refresh.
You should see: The command returns at once, and the refresh status appears in Catalog Explorer.
5.Recreate the view as mv_trigger with TRIGGER ON UPDATE between the name and AS.
Why: Triggered refresh suits upstream tables that do not load on a predictable schedule.
You should see: Later writes to base_table1 refresh the view automatically, at most once per minute.
Stuck? Get a nudge
If CREATE fails right away, check the warehouse type. Classic warehouses cannot create materialized views.
Sources
(8)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Unlike standard views, which recompute results on every query, materialized views cache the results and refresh them on a specified interval.”
↩︎ What a materialized view stores, and what keeps it current“The latency of updating a materialized view is in the seconds or minutes, not milliseconds.”
↩︎ What a materialized view stores, and what keeps it current“Standalone materialized views have a type of MV/ST.”
↩︎ What a materialized view stores, and what keeps it current“Unlike standard views, which recompute results on every query, materialized views cache the results and refresh them on a specified interval.”
↩︎ Key concept - 2.https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-create-materialized-viewOfficial docs
“Materialized views can only be created using a Pro or Serverless SQL warehouse, or within a pipeline.”
↩︎ What a materialized view stores, and what keeps it current“You may specify at most one of IF NOT EXISTS or OR REPLACE.”
↩︎ Creating a materialized view and choosing how it refreshes“Optionally set the table to refresh when an upstream data source is updated, at most once every minute.”
↩︎ Creating a materialized view and choosing how it refreshes“If AT TIME ZONE is absent and the session time zone is not set, an error is thrown.”
↩︎ Creating a materialized view and choosing how it refreshes“Create and refresh operations on materialized views and streaming tables are powered by serverless Lakeflow pipelines.”
↩︎ Exam trap 1 - 3.
“The user who creates a materialized view is the materialized view owner.”
↩︎ What a materialized view stores, and what keeps it current“Creating a materialized view is a synchronous operation”
↩︎ Creating a materialized view and choosing how it refreshes“This does not consume SQL warehouse compute. Instead, a serverless pipeline is used for creation and subsequent refreshes.”
↩︎ Creating a materialized view and choosing how it refreshes“the system runs the entire query and replaces the existing data in the materialized view with the new results”
↩︎ Creating a materialized view and choosing how it refreshes“The owner, and any user who has been granted the REFRESH privilege on the table, can refresh the materialized view.”
↩︎ Creating a materialized view and choosing how it refreshes“The operation is synchronous by default, meaning that the command blocks until the refresh operation is complete.”
↩︎ Creating a materialized view and choosing how it refreshes“To do this, use the ROW FILTER and MASK syntax during the creation of the materialized view.”
↩︎ Dynamic views versus materialized views“To do this, use the ROW FILTER and MASK syntax during the creation of the materialized view.”
↩︎ Exam trap 5“a serverless pipeline is created to process the create and refreshes to the materialized view”
↩︎ Checkpoint - 4.https://docs.databricks.com/aws/en/ldp/conceptsOfficial docs
“Each record is processed exactly one time, assuming an append-only source.”
↩︎ Streaming table, materialized view or view: picking the dataset type“Materialized views are suited for transformations, aggregations, or pre-computing results consumed by multiple downstream datasets.”
↩︎ Streaming table, materialized view or view: picking the dataset type“Evaluated on demand, not persisted.”
↩︎ Streaming table, materialized view or view: picking the dataset type“Views are not materialized, so you can only use them within the same pipeline.”
↩︎ Streaming table, materialized view or view: picking the dataset type“Because a materialized view caches its results, downstream queries read the precomputed results instead of re-computing the query on each access.”
↩︎ Streaming table, materialized view or view: picking the dataset type“a streaming table is designed for append-only sources and processes each record a single time”
↩︎ Exam trap 2“Views are not materialized, so you can only use them within the same pipeline.”
↩︎ Exam trap 4 - 5.
“Streaming tables are also a good choice for low-latency streaming transformations”
↩︎ Streaming table, materialized view or view: picking the dataset type“Joins in streaming tables do not recompute when dimensions change.”
↩︎ Streaming table, materialized view or view: picking the dataset type“existing rows will not update to be uppercase, but new rows will be uppercase”
↩︎ Exam trap 3“Streaming tables are designed for append-only data sources and process inputs only once.”
↩︎ Checkpoint - 6.
“The query used must be a streaming query. Use the STREAM keyword to use streaming semantics to read from the source.”
↩︎ Streaming table, materialized view or view: picking the dataset type - 7.https://learn.microsoft.com/en-us/azure/databricks/data-governance/unity-catalog/abac/abac-vs-rls-cmSecondary source
“Dynamic views can also implement row-level and column-level security by embedding identity functions like current_user() and is_account_group_member() directly in the view definition”
↩︎ Dynamic views versus materialized views - 8.https://docs.databricks.com/aws/en/views/dynamicOfficial docs
“During query analysis, Apache Spark replaces the CASE statement with either the literal string REDACTED or the actual contents of the email address column.”
↩︎ Dynamic views versus materialized views“Avoid using it with views against Unity Catalog data, because it does not evaluate account-level group membership.”
↩︎ Dynamic views versus materialized views“Databricks recommends that you not grant users the ability to read the tables and views referenced in the view.”
↩︎ Dynamic views versus materialized views“To create or read dynamic views, requirements are the same as those for standard views, except for compute requirements.”
↩︎ Dynamic views versus materialized views