CertSafari
    Databricks Certified Data Analyst Associate· Lessons

    Domain 4 · Lesson 12/39

    Materialized views, streaming tables and dynamic views in Databricks SQL

    Create a materialized view, including knowing when to use Streaming Tables and Materialized Views, and differentiate between dynamic and materialized views.

    18 min read
    2.22% of exam
    8 sources
    Published 22 Oct 2026
    Docs as of 17 Oct 2026

    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?

    Sources123

    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).

    A materialized view that aggregates orders and refreshes nightly on a cron schedulesql
    -- 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.

    Ways to refresh a materialized view
    MechanismHow you set itWhen it fits
    ManualREFRESH MATERIALIZED VIEW mv1; (add ASYNC to return immediately)Ad hoc updates, or a step run by your own orchestration
    Fixed intervalSCHEDULE EVERY number HOUR(S) / DAY(S) / WEEK(S): 1–72 hours, 1–31 days, 1–8 weeksSources that load on a predictable cadence
    CronSCHEDULE CRON cron_string [AT TIME ZONE timezone_id]A specific clock time, such as before the business day starts
    On upstream changeTRIGGER 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.

    A materialized view that refreshes whenever its source table changessql
    -- 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;

    Sources23

    3.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.

    A standalone streaming table that ingests JSON files from a volume with Auto Loader, refreshed hourlysql
    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.

    The three pipeline dataset types compared
    Dataset typeHow records are processedPersisted?Readable outside the pipeline?Choose it for
    Streaming tableEach record once, assuming an append-only sourceYes, Unity Catalog managed tableYesIngesting a continuously growing feed with low latency
    Materialized viewRecomputed as needed to match the current source stateYes, Unity Catalog managed tableYesAggregates and joins over data that gets updates or deletes; results shared by several consumers
    ViewEvaluated on demandNoNo, same pipeline onlyIntermediate 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?

    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?

    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?

    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?

    Sources456

    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.

    A dynamic view that shows email addresses only to members of the auditors groupsql
    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_raw

    The 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.

    Dynamic view and materialized view side by side
    AspectDynamic viewMaterialized view
    Stores resultsNo, runs at query timeYes, precomputed and refreshed
    Main purposeFine-grained access control: row-level security and data maskingFaster, cheaper reads of expensive or frequently used queries
    Per-user logiccurrent_user() / is_account_group_member() in SELECT or WHEREROW FILTER and MASK clauses at creation
    Created withCREATE VIEWCREATE 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_raw

    Sources783

    Practise it for real

    Create a materialized view from the SQL editor, refresh it on demand, then switch it to refresh automatically.

    1. 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. 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. 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. 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. 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. 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. 2.
      “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. 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. 4.
      “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
      “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. 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. 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. 7.
      “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. 8.
      “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

    Spotted a mistake, or was something unclear? Tell us.