What you will be able to do
- Create a streaming table with CREATE OR REFRESH STREAMING TABLE and the STREAM keyword
- Choose a streaming table or a materialized view for a given workload
- Explain how a dynamic view differs from a materialized view and write one with is_account_group_member()
1.Streaming tables: process each row once
A streaming table is a Delta table with extra support for streaming, or incremental, data processing. It is built for append-only sources and reads each input row only once. That makes it a good fit for ingestion, where data keeps arriving and must be captured without reprocessing what is already loaded. Sources include cloud object storage (through Auto Loader) and message buses such as Apache Kafka. If you create one outside a pipeline, Databricks creates and manages a serverless pipeline for it, the same way it does for a standalone materialized view.
The statement is CREATE OR REFRESH STREAMING TABLE, not CREATE OR REPLACE. The query must be a streaming query, so you read the source through the STREAM keyword. As with a materialized view, the initial load starts right away and runs on the serverless pipeline, not on your warehouse.
Checkpoint 1 of 5· Fill the gap
Which keyword gives this query the streaming semantics a streaming table requires?
CREATE OR REFRESH STREAMING TABLE sales
SCHEDULE EVERY 1 hour
AS SELECT product, price FROM ? raw_data;A streaming table's query must be a streaming query, and STREAM is the keyword that reads the source with streaming semantics.
Source: docs.databricks.comThat is the cost of processing each row once. A row already appended isn't read again by later updates, so after you change the query, the table holds rows produced by different versions of it. A full refresh re-reads all earlier data from the source and rewrites every row. Streaming tables also expect streams that are bounded, either naturally or by a watermark. An unbounded stream without a watermark can make the pipeline fail from memory pressure.
2.Choosing between a streaming table and a materialized view
The choice comes down to whether you need each row handled once, quickly, or need results that are always correct. Streaming tables are the right choice for data ingestion and low-latency streaming transformations. Materialized views are the right choice for complex transformations and analytical queries. Joins show the difference most clearly. A join inside a streaming table isn't recomputed when a dimension table changes, which the docs call a fit for "fast-but-wrong" scenarios. A materialized view recomputes its joins when dimensions change, so it stays correct.
| Aspect | Streaming table | Materialized view |
|---|---|---|
| Create statement | CREATE OR REFRESH STREAMING TABLE ... FROM STREAM source | CREATE OR REPLACE MATERIALIZED VIEW ... AS query |
| Typical source | Append-only data: cloud storage via Auto Loader, Kafka | Existing tables to join, aggregate or clean |
| How rows are processed | Each input row is handled only once | Recomputed incrementally or in full to stay correct |
| When a dimension in a join changes | Join is not recomputed | Join is recomputed |
| Latency | Low-latency streaming; real-time mode for sub-second | Seconds or minutes, not milliseconds |
| Best fit | Ingestion and low-latency streaming transformations | Complex transformations, aggregations, BI dashboards |
Checkpoint 2 of 5· Check yourself
A sales report joins transactions to a customer dimension. Customers' regions are often corrected after the fact, and the report must always show the corrected region. Which dataset type fits?
Joins in a streaming table aren't recomputed when a dimension changes. A materialized view recomputes them, so the report stays correct.
“Materialized views are always correct because they automatically recompute joins when dimensions change.”Source: docs.databricks.com
Checkpoint 3 of 5· 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.
3.Dynamic views versus materialized views
The names sound alike, but the two solve different problems. A dynamic view is a standard view you create with CREATE VIEW. It uses Unity Catalog functions so that what you see depends on who you are. Its purpose is fine-grained access control: security at the column or row level, and data masking. A materialized view is a Unity Catalog managed table that physically stores query results so they are fast to read. A dynamic view stores no results. Like any standard view, it recomputes on each query.
| Function | Returns | Guidance |
|---|---|---|
| session_user() | The current user's email address | Use when the logic depends on the individual user |
| is_account_group_member() | TRUE if the user belongs to an account-level group | Recommended for dynamic views on Unity Catalog data |
| is_member() | TRUE if the user belongs to a workspace-level group | Kept for Hive metastore compatibility; avoid it for Unity Catalog data |
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_rawWhen a query runs, Spark replaces that CASE expression with either the literal 'REDACTED' or the real email column. That's why the same view returns different results to different users. Putting a CASE in the WHERE clause filters rows the same way. Databricks recommends that users not be granted read access to the tables the view references. Otherwise they could skip the view and read the raw data. To create or read dynamic views you need a SQL warehouse, compute in standard access mode, or dedicated access mode on Databricks Runtime 15.4 LTS or above.
Not necessarily. A materialized view can carry its own access control: add WITH ROW FILTER and MASK clauses when you create it. For example, you can hide a tax_id column from users outside the HumanResourcesDept group.
Checkpoint 4 of 5· Check yourself
A dynamic view on Unity Catalog data should show salary only to an account-level group called finance. Which function should the CASE expression use?
is_account_group_member() checks account-level group membership and is the recommended function for Unity Catalog dynamic views. is_member() checks only workspace-level groups.
“Avoid using it with views against Unity Catalog data, because it does not evaluate account-level group membership.”Source: docs.databricks.com
Checkpoint 5 of 5· 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.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.After you change a streaming table's query, the next update rewrites every existing row with the new logic.Why is that wrong?
Rows already appended aren't processed again. Only new rows get the new logic, and you need a full refresh to rewrite the old ones.
Covered in Streaming tables: process each row once
2.A streaming table that joins to a dimension table updates past results when that dimension changes.Why is that wrong?
Joins in streaming tables aren't recomputed when a dimension changes. Use a materialized view when the joined result must always be correct.
Covered in Choosing between a streaming table and a materialized view
3.is_member() is the right function for group checks in a dynamic view on Unity Catalog data.Why is that wrong?
is_account_group_member() is the recommended function. is_member() checks only workspace-level groups and exists for Hive metastore compatibility.
Covered in Dynamic views versus materialized views
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“A streaming table is a Delta table with additional support for streaming or incremental data processing.”
↩︎ Streaming tables: process each row once“Streaming tables are designed for append-only data sources and process inputs only once.”
↩︎ Streaming tables: process each row once“Joins in streaming tables do not recompute when dimensions change.”
↩︎ Choosing between a streaming table and a materialized view“You can trigger a full refresh to requery all previous data from the source table to update all rows in the streaming table.”
↩︎ Exam trap 1“Joins in streaming tables do not recompute when dimensions change.”
↩︎ Exam trap 2“existing rows will not update to be uppercase, but new rows will be uppercase.”
↩︎ Prediction“Materialized views are always correct because they automatically recompute joins when dimensions change.”
↩︎ Checkpoint - 2.
“The query used must be a streaming query. Use the STREAM keyword to use streaming semantics to read from the source.”
↩︎ Streaming tables: process each row once - 3.
“Streaming tables are the right choice for data ingestion and low-latency streaming transformations.”
↩︎ Choosing between a streaming table and a materialized view“Materialized views are the right choice for complex transformations and analytical queries.”
↩︎ Choosing between a streaming table and a materialized view - 4.https://docs.databricks.com/aws/en/views/dynamicOfficial docs
“you can use dynamic views to configure fine-grained access control”
↩︎ Dynamic views versus materialized views“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“Databricks recommends that you not grant users the ability to read the tables and views referenced in the view.”
↩︎ Dynamic views versus materialized views“Recommended for use in dynamic views against Unity Catalog data.”
↩︎ Exam trap 3“Avoid using it with views against Unity Catalog data, because it does not evaluate account-level group membership.”
↩︎ Checkpoint - 5.
“To do this, use the ROW FILTER and MASK syntax during the creation of the materialized view.”
↩︎ Dynamic views versus materialized views