Task 9.2: Design dimensional models for analytical workloads, leveraging Materialized Views for pre-computed aggregation and Unity Catalog Metric Views for governed, reusable metric definitions, to ensure efficient querying and aggregation.

• AMaterialization works, and each manager's queries are filtered from the materialized data

  Incorrect

  Materialized results are precomputed as the owner, so the per-user filter could not be applied to them.

• BMaterialization works if the metric view is owned by a service principal

  Incorrect

  The owner's identity is exactly why materialization is blocked with row-level security.

• CMaterialization is blocked while the source table has a row filter

  Correct

  A materialization is computed once with the owner's identity, so serving it would bypass per-user filters; Databricks blocks it.

• DMaterialization works after the row filter is converted into an ABAC policy

  Incorrect

  ABAC row filter policies also block materialization.

A metric view cannot be materialized when the view or any source table uses row-level security, column masks or ABAC policies, because the pre-computed results would be shared regardless of each user's access.

• ASELECT region, total_revenue FROM sales_metrics

  Incorrect

  Measures are not plain columns; they are referenced with MEASURE.

• BSELECT region, SUM(total_revenue) FROM sales_metrics GROUP BY region

  Incorrect

  A measure takes its aggregation from the metric view definition and is evaluated with MEASURE, not wrapped in a regular aggregate such as SUM.

• CSELECT * FROM sales_metrics

  Incorrect

  This does not evaluate the measure per region.

• DSELECT region, MEASURE(total_revenue) FROM sales_metrics GROUP BY region

  Correct

  Measures of a metric view are evaluated with the MEASURE function.

Metric views separate measures from fields. Queries group by fields and evaluate measures with MEASURE(), so the engine generates the right computation for any grouping.

• AThe metric view sales_core itself

  Correct

  A metric view can use another metric view as its source.

• BOnly a managed Delta table

  Incorrect

  Views, materialized views, foreign tables and SQL queries also work.

• COnly a materialized view

  Incorrect

  Many table-like assets are allowed.

• DA notebook that returns a DataFrame

  Incorrect

  The source is a table-like asset or SQL query, not a notebook.

A metric view's source is any table-like Unity Catalog asset, such as a table, view, materialized view, streaming table, foreign table, system table or metric view, or a SQL query.

• AAdd CHECK constraints on every column of both tables

  Incorrect

  CHECK constraints validate values; they do not inform join planning.

• BPartition both tables by customer_id, the join key

  Incorrect

  Partitioning by a high-cardinality key is not the recommendation.

• CPrimary and foreign key constraints with the RELY option

  Correct

  RELY lets the optimizer use informational keys for better plans, such as removing unnecessary joins.

• DConvert both tables to views over the raw files

  Incorrect

  Views do not give the optimizer key information.

When a metric view's SQL source includes joins, set primary and foreign key constraints on the underlying tables and use RELY. Databricks does not enforce them, but the optimizer can use them.

• AThe query fails because no materialization matches

  Incorrect

  Queries still run through the fallback path.

• BThe query falls back to reading the source data

  Correct

  When no suitable materialization exists, the optimizer reads from the source directly.

• CThe query returns results only for region and month

  Incorrect

  Results are returned for the requested grouping.

• DThe pipeline refreshes a new materialization first

  Incorrect

  Materializations are defined in the YAML, not created per query.

With materialization, queries on the metric view use aggregate-aware rewriting: a fast path through a suitable materialized view, or a fallback path that reads the source data.