I85PREP
⌘ K
Databricks Data Analyst study materials
Databricks Data Analyst study resources

Databricks Data Analyst: Data Modeling with Databricks SQL

Databricks Data Analyst · Work through your exam, one topic at a time.

All guide sections

Data Modeling with Databricks SQL

Dimensional modeling still rules

The expected pattern for analytics is a star schema: central fact tables at the grain of a business event (one row per order line, per session), surrounded by conformed dimension tables (customer, product, date). Keep facts narrow and numeric; keep dimensions descriptive. Snowflaking (dimensions referencing dimensions) is acceptable but adds joins.

Objects available in Databricks SQL

  • Views - stored SQL, always current, no storage cost, recomputed on read.
  • Materialized views - precomputed and incrementally refreshed; good for expensive aggregations consumed repeatedly.
  • Streaming tables - incrementally updated from streaming sources.
  • Metric views - a semantic layer defining measures, dimensions, and joins once so dashboards and Genie compute consistent numbers.
  • Constraints - NOT NULL and CHECK are enforced; PRIMARY KEY/FOREIGN KEY are informational and help the optimizer and lineage, not uniqueness.

Physical design choices

Delta handles layout. Choose liquid clustering on columns used in frequent filters; avoid deep partitioning. Denormalization is cheap in a lakehouse - a wide gold table often beats a complex join graph for BI.

Traps

  1. Assuming primary keys enforce uniqueness - they do not; duplicates require MERGE logic.
  2. Using a view where a materialized view is needed, forcing recomputation on every dashboard load.
  3. Partitioning by a high-cardinality column such as order_id, creating massive file counts.
  4. Redefining a metric differently in three dashboards instead of centralizing it in a metric view.

Rule of thumb

Grain first. Before writing any DDL, state the grain in one sentence ("one row per order line per day"). Every key, aggregation, and join follows from that sentence. If two facts have different grains, they are two tables.

Worked example

Orders and order lines: fact_order_line at order-line grain, dim_customer and dim_product as Type-2 slowly changing dimensions with surrogate keys, and dim_date for fiscal calendars. Gold aggregates are materialized views over the fact.