Data Engineer Professional · Domain 1: Developing Code for Data Processing using Python and SQL · 23% of the exam
Task 1.6: Choose between a streaming table and a materialized view for a given latency, cost, and refresh requirement.
Append-only incremental processing against full-result correctness, incremental refresh of materialized views, refresh schedules and triggers, and what each costs.
Study it
Streaming table or materialized view: latency, cost and refresh
Lesson coming
Sample questions
Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.
Question 1 · choose 1
The silver table events gets a nightly DELETE of rows older than 90 days to meet a retention rule. A downstream table in a Lakeflow pipeline must keep every event it has ever seen for seven years, enriched with a few derived columns, and should not reprocess history on each update. Which definition fits?
AA materialized view over events, refreshed on a schedule after the nightly delete
BA streaming table that reads events directly, with no extra read options
CA view over events that adds the derived columns at query time
DA streaming table that reads events with the skipChangeCommits option set to true
Show the answer and why
AA materialized view over events, refreshed on a schedule after the nightly delete
Incorrect
A materialized view matches its source; rows deleted from events disappear from it on refresh, and a full refresh cannot restore them.
BA streaming table that reads events directly, with no extra read options
Incorrect
A streaming read expects an append-only source; the nightly DELETE commits make the stream fail.
CA view over events that adds the derived columns at query time
Incorrect
A view stores nothing, so it shows only the 90 days still in events.
DA streaming table that reads events with the skipChangeCommits option set to true
Correct
A streaming table processes each appended row once and keeps it, and skipChangeCommits ignores the retention deletes, which would otherwise make the stream fail.
Do not define a materialized view on a source where records are deleted after processing but must be kept downstream; use a streaming table. Because a streaming source must be append-only, read it with skipChangeCommits so that the delete commits are ignored.
A materialized view in a Lakeflow pipeline on classic compute is fully recomputed on every update, although its query is supported for incremental refresh. What explains this?
AThe pipeline must run in continuous mode for incremental refresh
BThe materialized view must be declared as a streaming table instead
Only materialized views updated with serverless pipelines can be incrementally refreshed.
DIncremental refresh works only for views made in Databricks SQL
Incorrect
Pipeline materialized views on serverless also refresh incrementally.
Materialized views that are not updated by serverless pipelines are always fully recomputed. On serverless, Databricks chooses between incremental refresh and full recompute by cost and query support.
Before choosing between a materialized view and a streaming table for a gold aggregate, a data engineer wants to know whether the proposed query can be incrementally refreshed. What can they run?
ADESCRIBE HISTORY on each source table of the query
BANALYZE TABLE on the source tables before creating it
CSHOW CREATE TABLE on a similar existing view
DEXPLAIN CREATE MATERIALIZED VIEW on the query
Show the answer and why
ADESCRIBE HISTORY on each source table of the query
Incorrect
History lists table commits, not query support for incremental refresh.
BANALYZE TABLE on the source tables before creating it
Incorrect
That collects statistics; it does not report incrementalizability.
CSHOW CREATE TABLE on a similar existing view
Incorrect
That prints a definition; it does not evaluate refresh support.
DEXPLAIN CREATE MATERIALIZED VIEW on the query
Correct
EXPLAIN CREATE MATERIALIZED VIEW tests a query for incremental refresh support.
Support for incremental refresh depends on the operators in the query. To test a specific query, use EXPLAIN CREATE MATERIALIZED VIEW.
A serverless materialized view sums a DOUBLE column amount and is fully recomputed on each refresh. The rest of its query supports incremental refresh. What change can let it refresh incrementally?
ACast amount to DECIMAL in the aggregate expression
BCast amount to FLOAT in the aggregate expression
CReplace SUM with a Python UDF that adds the values
DSchedule the refresh more often so less data changes
Show the answer and why
ACast amount to DECIMAL in the aggregate expression
Correct
Aggregations that sum floating-point values can be non-deterministic and force a full refresh; DECIMAL avoids that.
BCast amount to FLOAT in the aggregate expression
Incorrect
FLOAT is still floating point, with the same problem.
CReplace SUM with a Python UDF that adds the values
Incorrect
A UDF does not make floating-point sums deterministic.
DSchedule the refresh more often so less data changes
Incorrect
The aggregate type, not the schedule, forces the full refresh.
Sums, averages and similar aggregations over FLOAT or DOUBLE can produce non-deterministic results and force a full refresh. Casting these columns to DECIMAL in the expression avoids this.
Analysts must be able to query an earlier version of a pipeline dataset with VERSION AS OF while investigating incidents. Which dataset type supports this?
AA materialized view
BA streaming table
CA temporary view
DA view in Unity Catalog
Show the answer and why
AA materialized view
Incorrect
Time travel is not supported with materialized views.
BA streaming table
Correct
In pipelines, Delta time travel queries are supported only with streaming tables.
CA temporary view
Incorrect
A temporary view stores no data and has no versions.
DA view in Unity Catalog
Incorrect
A view stores no data of its own to travel back in.
Pipeline limitations note that Delta Lake time travel queries are supported only with streaming tables, not with materialized views.