Databricks DE Pro · Domain 9 of 9

Databricks DE Pro Domain 9: Data Modeling

Data Modeling is worth 5% and has two objectives: designing scalable Delta and Iceberg table layouts for large data, and building dimensional models for analytics with materialized views and Unity Catalog metric views.

Exam weight
5%
Official objectives
2
Estimated questions estimate
~3
Suggested study time estimate
4–6 hrs

About 9 min read Reviewed October 2026

Overview

Section 9 was reduced to two objectives in the October 2026 revision, but each covers a broad design skill. The first is physical design for large Delta or Iceberg tables: matching any partitioning to the grain and size of the data, aligning clustering keys with the columns queries filter and join on, and keeping files a healthy size through compaction so readers do not pay for thousands of small files.

The second is logical design for analytics: fact and dimension tables in a star schema, surrogate keys, slowly changing dimensions, and pre-aggregated gold tables delivered as materialized views. Unity Catalog metric views add a governed semantic layer, defining measures and dimensions once in YAML so dashboards, Genie and SQL users compute metrics the same way. The section links back to Liquid Clustering in Section 5 and to SCD handling in Section 1.

How it's tested

Questions describe data volume, query patterns and consumers, then ask for a layout or model. Expect trade-offs such as partitioning against clustering, or a materialized view against a metric view, where both choices are reasonable but only one fits the stated grain, freshness or governance need.

  • Design the layout

    Table size, grain and query filters are given, and you choose partitioning, clustering keys and compaction.

  • Shape the model

    An analytics requirement is described, and you choose fact and dimension design, keys and SCD handling.

  • Publish the metric

    Teams compute a KPI differently, and you choose a metric view or materialized view to standardise it.

Reasoning it through

Three dashboards calculate net revenue with slightly different filters, and finance wants one definition used everywhere, sliced by any dimension. A materialized view would fix one aggregation level. A Unity Catalog metric view defines the measure once with its dimensions, and every tool queries it with consistent logic, so the metric view is correct.

Objectives 2

Official objectives from the Databricks outline, in outline order.

9.1 Design scalable Delta and Iceberg table layouts for large data assets, mapping partitioning to data grain, aligning clustering to access patterns, and managing compactionPhysical design that keeps big tables fast.
What it means
Layout design decides how data files are organised on storage. It covers whether to partition and on which column, which clustering keys to use, target file sizes, and how compaction merges small files written by streaming or frequent appends, for both Delta tables and Iceberg tables in Unity Catalog.
What to understand
Partition only when the table is very large and a low-cardinality column such as date matches how data is loaded and deleted. Otherwise prefer Liquid Clustering on the columns used in filters and joins. Streaming writes create small files, so schedule or automate OPTIMIZE compaction, or rely on Predictive Optimization for managed tables. Grain, meaning what one row represents, should be clear and documented before choosing keys.
How it shows up
Expect table statistics and query patterns, then a choice of partitioning, clustering keys or compaction approach.
Common confusion
Partitioning by a column queries rarely filter on, which adds small files without any pruning benefit.
9.2 Design dimensional models for analytical workloads, leveraging Materialized Views and Unity Catalog Metric Views for governed metric definitionsLogical models and governed metrics for analytics.
What it means
Dimensional modelling organises analytics data into fact tables of measurable events at a declared grain and dimension tables of descriptive attributes, joined by keys. Materialized views precompute aggregates or joined gold tables, and Unity Catalog metric views define reusable measures and dimensions for consistent business metrics.
What to understand
Use surrogate keys, for example identity columns, on dimensions and track history with SCD Type 2 where attributes change. Informational primary and foreign key constraints document relationships and can help the optimizer when marked RELY. Materialized views suit fixed aggregations that dashboards read often. Metric views suit measures that must stay consistent across many dimensions and tools, queried with the MEASURE function.
How it shows up
A reporting requirement is described, and you choose fact grain, key design, a materialized view or a metric view.
Common confusion
Using a materialized view per dashboard when the problem is inconsistent metric definitions across tools.

High-yield concepts

The terms, rules and formulas this domain turns into questions most often.

  • Grain

    What one row in a table represents.

    Remember
    Declare it before choosing keys and layout.
    Tested as
    Modelling questions.
    Trap
    Mixing grains in one fact table.

  • Partition column choice

    Low-cardinality, load-aligned column on very large tables.

    Remember
    Date is the usual candidate.
    Tested as
    Layout questions.
    Trap
    High-cardinality partitions.

  • Clustering keys

    Columns used in frequent filters and joins.

    Remember
    Liquid Clustering allows changing them.
    Tested as
    Access pattern alignment.
    Trap
    Clustering on rarely filtered columns.

  • Compaction

    Merging small files into larger ones with OPTIMIZE.

    Remember
    Streaming writes need it most.
    Tested as
    Small file questions.
    Trap
    Ignoring file counts on streaming tables.

  • Iceberg tables in Unity Catalog

    Managed Iceberg tables readable by external engines.

    Remember
    Same layout principles apply.
    Tested as
    Format design questions.
    Trap
    Assuming layout rules differ completely.

  • Star schema

    Fact table joined to dimension tables.

    Remember
    Simple joins for analytics.
    Tested as
    Dimensional design.
    Trap
    Over-normalising dimensions.

  • Surrogate keys

    System-generated keys such as identity columns.

    Remember
    Support SCD Type 2 dimensions.
    Tested as
    Key design questions.
    Trap
    Using natural keys that change.

  • Informational constraints

    PRIMARY KEY and FOREIGN KEY declarations not enforced.

    Remember
    RELY lets the optimizer use them.
    Tested as
    Modelling metadata.
    Trap
    Expecting enforcement.

  • Materialized view for gold

    Precomputed aggregate with managed refresh.

    Remember
    Fixed shape, fast reads.
    Tested as
    Gold layer questions.
    Trap
    Using it to standardise many metrics.

  • Metric view

    Unity Catalog object defining measures and dimensions in YAML.

    Remember
    Queried with MEASURE for consistent results.
    Tested as
    Governed metric questions.
    Trap
    Treating it as a stored aggregate table.

Side by side

Concepts candidates mix up, compared on the point that decides the answer.

Materialized view vs. metric view

ConceptMeaningKey differenceCommon trap
Materialized view Stored result of a query One fixed aggregation, fast reads Expected to adapt to any slicing
Metric view Semantic definition of measures and dimensions Consistent metrics across tools and slices Thought to store precomputed data

Layout options for large tables

ConceptMeaningKey differenceCommon trap
Partition by date Directories per day Helps very large tables loaded and deleted by day Applied to small tables
Liquid Clustering Clustered files by changeable keys Default choice for most tables Combined with partitions
Compaction Rewrite small files into larger ones Fixes streaming and append fragmentation Assumed unnecessary on managed tables without Predictive Optimization

Fact vs. dimension tables

ConceptMeaningKey differenceCommon trap
Fact table Measurable events at a declared grain Many rows, numeric measures Storing descriptive attributes repeatedly
Dimension table Descriptive attributes for slicing Fewer rows, may keep history Using changing natural keys as joins

Exam traps

Each trap is an answer that looks right. Learn the clue that should change your mind.

  1. Partition by ID

    What candidates pick
    Partition a large table by customer_id.
    Why it looks right
    Queries filter on customer_id.
    Why it's wrong
    Millions of partitions create tiny files; cluster on the column instead.

    Clue to watch for The partition column has very high cardinality.

  2. Metric view stores data

    What candidates pick
    Pick a metric view to make a heavy aggregate faster.
    Why it looks right
    It sounds like an aggregate table.
    Why it's wrong
    A metric view defines semantics; a materialized view stores precomputed results for speed.

    Clue to watch for The requirement is read latency, not consistent definitions.

  3. MV for consistency

    What candidates pick
    Build a materialized view per team to align revenue numbers.
    Why it looks right
    Materialized views are familiar.
    Why it's wrong
    Separate views can still diverge; a single metric view defines the measure once for all tools.

    Clue to watch for Several teams disagree on a KPI.

  4. Enforced foreign keys

    What candidates pick
    Rely on a FOREIGN KEY to reject orphan rows.
    Why it looks right
    Other databases enforce it.
    Why it's wrong
    Unity Catalog primary and foreign keys are informational and not enforced.

    Clue to watch for The question expects bad rows to be rejected.

  5. Skip compaction

    What candidates pick
    Leave a streaming table to accumulate small files.
    Why it looks right
    Writes succeed without it.
    Why it's wrong
    Small files slow every read; compaction or Predictive Optimization is needed.

    Clue to watch for A streaming table has many small files.

Answer framework

Follow these steps when a Section 9 question asks how to design a table or model.

  1. State the grain of each table and the main query patterns, including filter columns, join keys and the consumers who read the data.
  2. Choose layout: Liquid Clustering on filter and join columns by default, and partitioning only for very large tables on a low-cardinality load column.
  3. Plan compaction for tables written by streams or frequent appends, using Predictive Optimization on managed tables where available.
  4. Model analytics as facts at one grain and dimensions with surrogate keys, using SCD Type 2 where attribute history matters.
  5. Publish fixed fast aggregates as materialized views, and shared business measures as Unity Catalog metric views for consistency.

Practice question

A 25 TB event table is loaded daily and old days are deleted after two years. Most queries filter by event_date and device_id. Which layout fits best?

Want more like this? The Databricks DE Pro practice test has questions for every domain.

Checklist

0 of 10

Tick each one you can do without notes. Your ticks stay in this browser.

Study strategy

Keep this section short and practical, and tie it to work you have done for Sections 1 and 5. Take one realistic dataset, such as orders with customers and products, and write down the grain of each table before creating anything. Build a star schema with identity-column surrogate keys and an SCD Type 2 customer dimension fed by AUTO CDC.

For layout, apply Liquid Clustering on the order table's main filters and check file pruning in a query profile. Write a short rule for when partitioning still applies, using table size and load pattern, and practise it on two or three imagined tables.

Then create a materialized view for a daily revenue summary and a metric view that defines revenue and order count with a few dimensions. Query the metric view with MEASURE at different slices and compare it with the fixed materialized view.

Finish by reviewing informational constraints and RELY, and by reading how Iceberg managed tables appear in Unity Catalog, since the objective names both formats.

FAQ

How much of the exam is Data Modeling?

Data Modeling is 5% of the scored content, about three questions, the same share as Data Governance.

What are Unity Catalog metric views?

Objects that define business measures and dimensions in YAML so every query and tool computes the metric the same way, using the MEASURE function.

Should I partition large tables?

Only very large tables with a low-cardinality column, such as date, that matches loading and deletion. Otherwise use Liquid Clustering.

Are primary and foreign keys enforced?

No. They are informational in Unity Catalog. With RELY they can help the optimizer, but they do not reject bad rows.

Does the exam cover Iceberg?

Yes. The layout objective names both Delta and Iceberg tables, and the same partitioning, clustering and compaction principles apply.

How did this section change in October 2026?

It dropped from four objectives to two and added metric views, focusing on layout and dimensional modelling for analytics.

Other Data & Analytics certifications

Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.

Test your Domain 9 knowledge

You have covered table layout, compaction, dimensional models, materialized views and metric views. Check your design choices on short modelling scenarios.

Take the Databricks DE Pro practice test Check your Databricks DE Pro readiness

Keep going