Databricks Data Engineer Professional
Section 9 · 5%Data Modeling

Data modeling

Table layouts, dimensional models, materialized views and Unity Catalog Metric Views.

This section tests whether you can lay out big tables so files stay well sized and queries skip most data, and whether you can serve reports from pre-computed or governed definitions. The biggest way people lose marks is choosing a layout by habit (partition by date) instead of by the grain of the data and the columns queries actually use.

PracticeS9 practice bank →

3 items, practice or timed exam mode.

Walk the guide, line by line

Each row is one line of the official guide, in its own words on the left. Read across: plain words, what a question looks like, the answer, and the trap.

Guide lineIn plain wordsA question looks likeThe answerThe trap
9.1 Design scalable Delta/Iceberg table layouts for large data assets by mapping partitioning to data grain, aligning clustering to relationship access patterns, and maintaining balanced file sizes through compaction.Table layout means how rows are grouped into files. Partitioning should follow the grain (the level of detail of one row, such as one order line or one daily snapshot). Clustering should follow the columns that queries join and filter on. Compaction means merging many small files into fewer well-sized ones so reads stay fast.A very large order-lines table is mostly filtered by customer and joined to a customer table, and streaming writes keep leaving small files. The question asks how to lay it out.Cluster on the customer key with Liquid Clustering, because that matches the relationship queries use, and let compaction keep files balanced: Predictive Optimization runs OPTIMIZE on managed tables, or you schedule OPTIMIZE yourself. Partition only on a low-cardinality column that matches the data's grain and that is always filtered. Clustering works for Delta and for managed Iceberg tables, with Iceberg support depending on version, so check the docs.Partitioning by the customer key because it is the main filter. Too many distinct values make tiny files and slow metadata operations.
Runner-up wins when: Partitioning by load date wins when downstream jobs and retention rules always work on whole days and the table is very large.
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.A dimensional model arranges analytics data as fact tables (events and amounts) joined to dimension tables (descriptions). A materialized view stores a query result and refreshes it, so repeated aggregations are pre-computed. A Unity Catalog metric view defines measures and dimensions once, with governance, so every tool computes a metric the same way.Many dashboards compute revenue per region and month from a large fact table, and different teams define revenue differently. The question asks how to make it fast and consistent.Use a materialized view for the heavy shared aggregation so it is computed once and refreshed incrementally when the query allows it, and a Unity Catalog metric view to hold the single governed definition of revenue and its dimensions. A metric view lets users group by any dimension, and can itself be materialized so eligible queries are rewritten to use it.Making a standard view for the aggregation. A standard view computes on every query, so it gives a shared definition but no speed-up.
Runner-up wins when: A standard view wins when the data is small and always needs to be fresh, because nothing needs refreshing.

Mind map: Layout and serving choices for analytics tables

People assume one technique (partition by date, or a plain view) fits every model, when grain, query columns and freshness decide.

What you observeWhat it points toWhat to do (and when the runner-up wins)
Very large table, filtered by a high-cardinality keyLiquid Clustering on that keyCluster, do not partition.
Always filtered by one low-cardinality column, tied to retentionPartitioningPartition; here the usual answer flips.
Same aggregation recomputed by many dashboardsMaterialized viewPre-compute it. A standard view wins only when data is small and must be live.
Teams disagree on a metric's definitionMetric viewDefine measures and dimensions once in Unity Catalog.