Gold Layer Objects and Data Quality in Databricks (Data Engineer Associate)

A gold layer object is the curated, business-ready dataset that BI and analytics teams query, and in Unity Catalog it can be one of four things: a view (a stored query run at read time), a materialized view (a stored, precomputed query result kept up to date by refreshes), a streaming table (a table that incrementally processes each new source record once) or a plain managed Delta table (written by your own job code). The right choice follows from three questions: how fresh must results be, how often is the result read compared with how expensive it is to compute, and do the sources only grow or do existing rows change?

All four are registered in Unity Catalog under catalog.schema.name and are governed the same way. Granting access, row filters and column masks belong to the security section of the exam; this lesson covers choosing, building and validating the objects.

A standard view stores only its query, so it is always current and costs nothing between reads, while a materialized view stores the result, so reads are fast but the data is only as fresh as the last refresh. That trade-off decides most "view or materialized view" choices.

Choose a view when results must reflect source data committed seconds ago, when the team wants no extra storage or refresh to manage, or when the query is cheap and read rarely, such as a report run twice a day. Typical gold views select and rename columns of a silver table or apply simple filters for a BI team.

CREATE VIEW gold.orders_for_bi AS
SELECT order_id, customer_id, order_ts, status, amount AS order_amount
FROM silver.orders;

Choose a materialized view when an expensive query (a large join or aggregation) is read many times and some staleness is acceptable. The work happens once per refresh and every dashboard query reads the stored result. You can create one in Databricks SQL or define it in a Lakeflow Spark Declarative Pipeline (formerly Delta Live Tables):

CREATE MATERIALIZED VIEW gold.daily_revenue
  SCHEDULE EVERY 1 HOUR
AS SELECT o.order_date, c.region, SUM(o.amount) AS revenue
FROM silver.orders o
JOIN silver.customers c ON o.customer_id = c.customer_id
GROUP BY o.order_date, c.region;

A materialized view's result is recomputed to match the current state of its sources on each refresh, which is why it handles source updates and deletes correctly, for example a customer moving region. A pipeline CREATE TEMPORARY VIEW is neither of these: it is an intermediate step visible only inside its pipeline (see the section on pipeline views).

A materialized view is brought up to date by a refresh, which can be run on demand, on a time schedule or automatically when its source tables change. Pick the mode from how the sources are loaded.

Time-based schedules run whether or not anything changed; TRIGGER ON UPDATE runs only after a source changes, and AT MOST EVERY throttles it so a burst of small loads does not cause a burst of refreshes. All of these live in the view definition, so no separate job is needed (you can also schedule REFRESH from a Lakeflow Jobs task, or add or change a schedule later with ALTER MATERIALIZED VIEW).

Who can refresh: the owner and any principal granted the REFRESH privilege on the materialized view. Granting REFRESH lets an analyst bring the view up to date without making them the owner, and without letting them change or drop the definition. REFRESH is a privilege in its own right: read access to the sources or other privileges on the view do not include it.

What does the work: a materialized view created in Databricks SQL is refreshed by a serverless pipeline that Databricks manages for it. The SQL warehouse that issued the statement only coordinates, so refresh time and cost scale with the data processed and the serverless pipeline, not with the size or cluster count of the warehouse that started it.

An incremental refresh processes only the source rows that changed since the last refresh and merges the effect into the stored result; a full refresh recomputes the whole query. Databricks tries incremental refresh when the query and sources allow it and falls back to a full recompute otherwise.

The prerequisites for incremental refresh are:

• Serverless compute, which is how materialized views are refreshed in Databricks SQL and in serverless pipelines.
• Change tracking on the Delta sources. Databricks recommends enabling row tracking, deletion vectors and change data feed on every source table; several incremental techniques require row tracking specifically, because it lets the refresh identify which rows changed. If every refresh rebuilds everything although only a few source rows changed, row tracking on the source tables is the first thing to check. Settings on the materialized view itself, or table maintenance such as OPTIMIZE, do not supply this information.
• Supported operations: common joins, aggregations and filters can be maintained incrementally. Non-deterministic functions such as rand() block it; time functions such as current_timestamp() are allowed only inside a WHERE filter. Some aggregates can also force a full recompute, for example SUM or AVG over FLOAT/DOUBLE columns.

Checking before you build: EXPLAIN CREATE MATERIALIZED VIEW <name> AS <query> reports whether the query can be refreshed incrementally without creating the view or running a refresh. After deployment, the pipeline event log's planning_information events show which technique each refresh actually used (for example ROW_BASED or FULL_RECOMPUTE).

Controlling the fallback with a refresh policy on the view:

Use INCREMENTAL STRICT when a silent full recompute would be a problem in itself, such as a view over terabytes that must finish in a nightly window: it turns "incremental became impossible" into a visible error.

A streaming table is the right gold or silver object when the source is append-only and new records should be picked up within seconds or minutes without recomputing history: each record is read once, transformed and written. Click events, logs and sensor readings that are never updated are the typical fit.

CREATE STREAMING TABLE gold.readings_by_site AS
SELECT r.*, d.site_name
FROM STREAM(silver.sensor_readings) r
JOIN silver.devices d ON r.device_id = d.device_id;

Two behaviours follow from "each record once", and both point to a materialized view when the requirement is the current state:

• Non-append changes in the source. A streaming read of a Delta table fails when the source commits updates or deletes (for example after a MERGE starts updating rows of a silver table). The skipChangeCommits option stops the error by ignoring those commits, so the updates never reach gold. If gold must show current values, redefine the object as a materialized view; running a full refresh after each MERGE only patches it by hand.
• Stream-static joins do not revisit old rows. In the example above, each reading is joined with the device data as it is at processing time. If a device later moves to another site, rows already written keep the old site. Because a streaming table only ever processes records it has not seen, no change to how it reads the dimension or how often it runs makes it rewrite rows it already wrote. When historical gold rows must reflect dimension changes, use a materialized view, which recomputes the join against current inputs on every refresh.

Ingesting files into streaming tables with read_files and Auto Loader is covered in the ingestion lessons; here the point is choosing a streaming table versus a materialized view for derived data.

A plain managed Delta table, written by a job with CTAS, INSERT or MERGE, is the right gold object when you need table features that materialized views do not offer or update logic you must control yourself. Materialized views are managed by Databricks, so several Delta features are not available on them:

• Time travel. Queries with VERSION AS OF or TIMESTAMP AS OF are not supported on materialized views, nor on views; this is a property of the object type, not a retention setting. A gold table that a job updates has a normal Delta version history, so auditors can read a past version within the retention window.
• Identity columns and surrogate keys. Materialized views do not support identity columns. Computing a key with ROW_NUMBER() or monotonically_increasing_id() inside the view's query is not stable either, because values can change whenever the result is recomputed. A managed table with GENERATED ALWAYS AS IDENTITY, loaded by a MERGE that inserts only unmatched rows, assigns each key once and never reassigns it.
• Custom merge logic such as slowly changing dimension history, where you decide row by row what to update, insert or close out.

CREATE TABLE gold.dim_product (
  product_sk BIGINT GENERATED ALWAYS AS IDENTITY,
  product_id STRING,
  name STRING,
  category STRING
);

MERGE INTO gold.dim_product t
USING silver.products s ON t.product_id = s.product_id
WHEN MATCHED THEN UPDATE SET t.name = s.name, t.category = s.category
WHEN NOT MATCHED THEN INSERT (product_id, name, category)
  VALUES (s.product_id, s.name, s.category);

The cost is orchestration: you schedule the job and maintain the logic. If none of these needs apply, a materialized view is less code, because Databricks refreshes it (incrementally when possible) and keeps it consistent with its sources. Rebuilding a table each hour with CREATE OR REPLACE TABLE AS SELECT works but recomputes everything every time.

In a Lakeflow Spark Declarative Pipeline, CREATE VIEW publishes a view to Unity Catalog, while CREATE TEMPORARY VIEW defines a view that exists only inside the pipeline. Neither stores data, but they differ in visibility and in whether they can carry expectations.

Two consequences come up often. A temporary view that an analyst needs to query must become a published object: CREATE VIEW if it should stay unstored, a materialized view if a stored copy is acceptable. Its visibility is fixed by how it is declared, so only changing the declaration changes who can query it. And a published view that needs expectations cannot have them; if the rule's metrics must be recorded and BI users must query the object, make it a materialized view.

An expectation is a named data quality rule (a SQL boolean expression) attached to a pipeline dataset, plus an action that decides what happens to rows that fail it. Expectations work on streaming tables, materialized views and temporary views in Lakeflow Spark Declarative Pipelines. They do not apply to a notebook job that writes a table with MERGE outside a pipeline; use Delta constraints there.

CREATE OR REFRESH STREAMING TABLE silver.shipments (
  CONSTRAINT has_shipment_id EXPECT (shipment_id IS NOT NULL) ON VIOLATION DROP ROW,
  CONSTRAINT plausible_weight EXPECT (weight_kg <= 1000)
) AS SELECT * FROM STREAM(bronze.shipments);

Match the action to the requirement: warn for data that is suspicious but legitimate (keep it, count it, investigate a sudden rise); drop when bad rows must not reach the table but the update should keep running; fail when a publish with any invalid row is unacceptable and someone must fix the data first. A plain WHERE filter removes rows too, but records no expectation metrics.

The expect_all variants (@dp.expect_all, @dp.expect_all_or_drop, @dp.expect_all_or_fail) take a dictionary of rule names to conditions and apply them with one decorator; the single-rule decorators take one name and one condition. An expectation's condition is evaluated per row and cannot contain a subquery against another table.

Where the numbers go: for warn and drop, per-expectation passed and failed counts appear on the dataset's data quality tab in the pipeline UI and in the pipeline event log (flow_progress events, queryable from SQL with the event_log() table-valued function, which suits dashboards). They are not in Delta DESCRIBE HISTORY, the Spark UI, driver logs or information_schema.

How far a failure reaches: in a continuous pipeline, a fail action stops the failing flow and the flows downstream of it; independent flows keep running. In a triggered pipeline, a failing flow does not fail other parallel flows, so a validation dataset does not block a gold table that does not read from it. To make gold wait for validation, put validation and downstream processing in separate pipelines and link them with task dependencies in a Lakeflow Jobs job.

Dropped rows are gone: they are never written to the target, so time travel cannot recover them, and the event log and data quality tab store only counts. When stewards need to review invalid rows, quarantine them instead of dropping them: flag each row, record metrics with a warn expectation, and split the data.

from pyspark import pipelines as dp
from pyspark.sql.functions import expr

rules = {"valid_id": "sensor_id IS NOT NULL", "valid_temp": "temp_c BETWEEN -50 AND 150"}
quarantine_rule = "NOT({0})".format(" AND ".join(rules.values()))

@dp.table
@dp.expect_all(rules)          # warn: keep every row, record counts
def readings_flagged():
    return (spark.readStream.table("bronze.readings")
            .withColumn("is_quarantined", expr(quarantine_rule)))

@dp.temporary_view
def valid_readings():
    return spark.readStream.table("readings_flagged").filter("is_quarantined = false")

@dp.temporary_view
def invalid_readings():
    return spark.readStream.table("readings_flagged").filter("is_quarantined = true")

Downstream silver and gold datasets read only the valid rows, and stewards review the invalid ones.

Reusable rules. When many tables share rules, store them outside the code, for example in a Delta table with name, constraint and tag columns, and load them in a Python helper: @dp.expect_all_or_drop(get_rules("validity")). Stewards then add or change rules by editing rows, with no pipeline code change. SQL pipelines cannot load expectations dynamically from a file or table; copying decorators or adding per-table CHECK constraints means a code or DDL change for every rule edit.

Uniqueness. An expectation sees one row at a time, so it cannot detect a duplicate key by itself. Add a dataset that groups by the key and counts, and put a fail expectation on that count:

CREATE OR REFRESH MATERIALIZED VIEW store_key_check (
  CONSTRAINT one_row_per_store EXPECT (row_count = 1) ON VIOLATION FAIL UPDATE
) AS SELECT store_id, COUNT(*) AS row_count
FROM gold.dim_store GROUP BY store_id;

The same pattern compares row counts between layers. The principle: a property of the whole dataset (uniqueness, totals) needs a dataset-level check, and a check that fails visibly is different from logic that quietly reshapes the data so the problem never shows.

For tables written by jobs and notebooks rather than a pipeline, Delta table constraints are the enforced quality check: NOT NULL and CHECK constraints are verified on every write, by every writer, and a write containing a violating row fails as a whole transaction with nothing applied.

ALTER TABLE silver.refunds ADD CONSTRAINT refund_nonnegative
  CHECK (refund_amount >= 0);
ALTER TABLE gold.shipping_charges ADD CONSTRAINT charge_adds_up
  CHECK (charged = base_rate + fuel_surcharge);
ALTER TABLE silver.refunds ALTER COLUMN refund_id SET NOT NULL;

• Because the rule sits on the table, it also covers jobs that other teams add later, unlike a WHERE filter in each job (which silently drops rows and depends on every writer remembering it) or a filtering view (which does not stop the data being written).
• ADD CONSTRAINT first checks every existing row. If historical data violates the rule, the statement fails; correct or delete those rows, then add the constraint.
• Primary and foreign keys in Unity Catalog are informational. They document the model and, with RELY, let the optimizer use them, but they are not enforced: a duplicate key inserts successfully. Guarantee uniqueness in the load logic or validate it with a check like the one above.

Data quality monitoring watches tables over time rather than testing rows as they are written. It has two parts:

• Anomaly detection is enabled once for a schema and learns each table's normal behaviour to flag freshness problems (a table not updated on its usual cadence) and completeness problems (far fewer rows than usual), with no per-table rules. It catches a stalled upstream job, which row-level checks never can: if no rows arrive, no expectation or constraint is evaluated, so the data quality tabs look clean.
• Data profiling (formerly Lakehouse Monitoring) is configured per table, as a snapshot, time series or inference profile. It computes summary statistics for each column and drift metrics between windows, stored in metric tables with a dashboard, and you can alert on those metrics, for example when the distribution of one column shifts.