11.1 Dimensional Modeling Fundamentals

Databricks Certified Data Analyst Associate Exam

Track your progress as you read

Finished sections get a check mark.

1 Platform Overview & Unity Catalog Foundations

1.1 Databricks Architecture & Compute Concepts 1.2 Databricks Marketplace & Data Sharing Basics 1.3 Unity Catalog Namespace & Object Hierarchy 1.4 Governed Asset Discovery & System Tables

2 Managing & Cleaning Governed Data

2.1 Dataset Discovery & Asset Tagging 2.2 Lineage Inspection & Data Traceability 2.3 SQL Data Cleaning & Transformation Techniques

3 Data Ingestion & Import Patterns

3.1 Workspace Uploads & Cloud File Ingestion 3.2 Incremental File Ingestion with Auto Loader 3.3 Ingesting Data via Delta Sharing & External Connections

4 Databricks SQL Warehouses & Core Querying

4.1 Databricks SQL Interface & SQL Assistant 4.2 SQL Warehouses Sizing & Management 4.3 Table Creation & Data Modification in Databricks SQL

5 Advanced SQL Authoring & Time Travel

5.1 Advanced Aggregations, Window Functions & Analytics 5.2 Complex Joins, CTEs & Set Operations 5.3 Views, Materialized Views & Delta Time Travel

6 Query Performance Analysis & Photon Engine

6.1 Photon Engine Architecture & Acceleration 6.2 Query History & Execution Profile Inspection 6.3 Delta Lake Audit History & Commit Logs

7 Liquid Clustering, Caching & Performance Troubleshooting

7.1 Caching Hierarchy in Databricks SQL 7.2 Data Layout Optimization: Liquid Clustering vs Partitioning & Z-Ordering 7.3 Troubleshooting Slow & Failing Queries

8 AI/BI Dashboards & Visualization Design

8.1 AI/BI Dashboards Overview & Canvas Layout 8.2 Chart Types & Visualization Selection 8.3 Notebook & SQL Editor Visualizations

9 Dashboard Interactivity, Publishing & Alerts

9.1 Dashboard Parameters & Interactive Filtering 9.2 Publishing, Sharing & Access Control 9.3 Scheduled Refresh, Email Subscriptions & Alerts

10 Developing & Maintaining AI/BI Genie Spaces

10.1 AI/BI Genie Architecture & Space Setup 10.2 Curation via Instructions, Descriptions & Sample Questions 10.3 Trusted Assets & Parameterized Functions 10.4 Monitoring, Benchmarks, Feedback & Maintenance

11 Data Modeling & Medallion Architecture

11.1 Dimensional Modeling Fundamentals 11.2 Medallion Architecture for Analytics 11.3 Constraint Enforcement & Relationships in Unity Catalog

12 Securing Data & Unity Catalog Governance

12.1 Unity Catalog Privilege Model & Grants 12.2 Ownership, Inheritance & Access Control Patterns 12.3 Fine-Grained Security: Row Filters & Column Masks

11.1 Dimensional Modeling Fundamentals

Key Takeaways Explain This Topic AI

Star schemas reduce JOIN depth by organizing data into a central fact table surrounded by single-decomposed dimension tables, yielding up to 3x to 5x query speedups compared to normalized 3NF schemas.

Fact tables capture quantitative metrics and operational events at a grain defined by business processes, storing integer foreign keys alongside additive or non-additive measures.

Data Vault uses hubs, links, and satellites for auditable multi-source historization, typically feeding star/snowflake marts used by Databricks SQL analysts.

Slowly Changing Dimension (SCD) Type 1 overwrites historical attribute values, while SCD Type 2 preserves historical changes by inserting new row versions with validity timestamps (valid_from, valid_to) and current flags.

Surrogate keys in Delta Lake tables are natively generated using BIGINT GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY, enabling fast integer-based hash joining in Databricks SQL.

Last updated: July 2026

Overview of Dimensional Modeling in Databricks SQL

Dimensional modeling remains the cornerstone of modern data warehousing and business intelligence. Pioneered by Ralph Kimball, dimensional modeling optimizes relational data for fast querying, intuitive reporting, and efficient ad-hoc analytics. In the context of the Databricks Data Intelligence Platform and Databricks SQL, dimensional schemas provide structured semantic structures that allow analytical engines like Photon to execute high-speed join algorithms, leverage cache vectorization, and streamline aggregation processing. While traditional operational database design prioritizes normalized schemas (such as Third Normal Form, or 3NF) to minimize redundancy and prevent write anomalies during transaction processing (OLTP), analytical systems (OLAP) favor denormalized structures that minimize multi-table join chains and simplify query authoring for data analysts.

Star Schema vs. Snowflake Schema Architecture

In dimensional design, schemas are broadly categorized into two structural patterns: Star Schemas and Snowflake Schemas .

A Star Schema consists of a single central fact table surrounded by non-normalized dimension tables. Each dimension connects directly to the central fact table via a single join path, creating a star-like visual shape. This structure minimizes join complexity—queries typically require joining the fact table to one or two dimension tables.

A Snowflake Schema extends the star schema by normalizing dimension tables into sub-dimensions (for instance, splitting a dim_product table into dim_subcategory and dim_category ). While snowflaking eliminates redundant text storage, it introduces multi-level join hierarchies (e.g., joining fact to product, subcategory, and category). On modern lakehouses powered by Delta Lake and Databricks SQL, Star Schemas are strongly preferred over Snowflake Schemas. Denormalized dimensions significantly reduce shuffle overhead during query execution, allowing Photon to perform fast broadcast hash joins.

• Architectural Feature Star Schema Snowflake Schema
• Dimension Structure Denormalized into single flat tables Normalized into hierarchical sub-dimensions

Join Complexity Low (Single-level JOIN from Fact to Dimension) High (Multi-level recursive JOINs across sub-dimensions)

Query Execution Performance Optimized for Databricks SQL & Photon broadcast joins Higher shuffle overhead and query latency

Storage Efficiency Slight redundancy in attribute strings High normalization reduces string redundancy

BI Usability Highly intuitive for analysts and AI/BI tools Complex entity relationships for non-technical users

Fact Tables, Grain, and Measures

At the core of any dimensional model is the Fact Table . A fact table records numeric measurements, metrics, or events resulting from business operations (e.g., retail sales transactions, website clickstreams, or inventory snapshots).

Defining the Grain of a fact table is the single most critical decision in dimensional modeling. The grain represents the exact real-world atomic detail stored in a single row. For example, a fact table grain could be "one row per item sold on a customer checkout receipt" or "one row per daily summary of account balances." Establishing the atomic (lowest level) grain provides maximum analytical flexibility, enabling analysts to slice and dice metrics across any dimension attribute without losing detail.

Fact tables contain two types of columns:

Foreign Keys : Integer keys linking each fact record to its corresponding dimension tables (e.g., customer_id , product_id , store_id , date_id ).

Measures (Numeric Metrics) : Quantitative values that can be aggregated. Measures fall into three operational categories:

Additive Measures : Values that can be meaningfully summed across all dimensions (e.g., sales_amount , quantity_sold ).

Semi-Additive Measures : Values that can be summed across some dimensions but not time (e.g., account_balance or inventory_count where summing across days produces incorrect totals).

Non-Additive Measures : Ratios, percentages, or unit prices that cannot be added directly (e.g., margin_percentage or unit_price ); these must be computed dynamically using underlying additive metrics (e.g., SUM(margin) / SUM(revenue) ).

Dimension Tables and Slowly Changing Dimensions (SCD)

Dimension Tables contain descriptive context surrounding business events. They store contextual attributes (such as customer names, product categories, store regions, or promo details) that analysts filter, group, and slice by in SQL queries. Dimensions typically contain fewer rows than fact tables but feature many wide text attributes.

In real-world enterprise environments, dimension attributes change over time—customers change addresses, products switch categories, and stores are rebranded. Handling these updates requires Slowly Changing Dimension (SCD) strategies:

SCD Type 1 (Overwrite) : Old attribute values are directly overwritten with new values. Historical context is lost; past transactions appear as if they occurred under the new attribute value. This strategy is ideal when correcting errors or when historical tracking is irrelevant.

SCD Type 2 (Add New Row) : A new record is inserted into the dimension table whenever an attribute changes, preserving complete historical context. The existing record is marked as expired by setting a valid_to timestamp and an is_current = FALSE flag, while the new record receives a valid_from timestamp and is_current = TRUE . All historical fact transactions retain foreign key links to the dimension version active when the event took place.

Dimension Strategy Operation Mechanism Historical Context Primary Use Case

SCD Type 1 Overwrites existing column values in place Lost (Past transactions reflect current attribute state) Correcting typos or non-critical attribute updates

SCD Type 2 Inserts a new versioned row with validity timestamps Preserved (Full point-in-time point of view) Compliance, accurate historical sales tracking, auditability

Surrogate Keys and Identity Columns in Delta Lake

To support SCD Type 2 and insulate dimensional models from operational source changes, dimensional tables utilize Surrogate Keys —synthetic integer primary keys generated specifically for the data warehouse, distinct from natural business keys (e.g., Social Security Number or operational SKU).

Databricks SQL provides native identity column syntax for automatic surrogate key generation in Delta Lake tables using BIGINT GENERATED ALWAYS AS IDENTITY or BIGINT GENERATED BY DEFAULT AS IDENTITY . -- Creating a Dimension Table with Native Identity Surrogate Keys

• CREATE TABLE main.analytics.dim_customer (
• customer_sk BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
• customer_id STRING NOT NULL, -- Natural business key from source system
• customer_name STRING,
• email STRING,
• city STRING,
• state STRING,
• valid_from TIMESTAMP NOT NULL,
• valid_to TIMESTAMP,
• is_current BOOLEAN NOT NULL
• ) USING DELTA;

When inserting new records into dim_customer , Databricks SQL automatically assigns monotonically increasing integer values to customer_sk . Fact tables store customer_sk as a foreign key, allowing queries to join facts with exact historical dimension versions efficiently.

Data Vault Modeling for Analytical Workloads

Alongside star and snowflake designs, the exam outline expects familiarity with Data Vault modeling. Data Vault is a hub-and-spoke historical modeling approach often used in enterprise integration layers before dimensional marts are published.

• Component Role Typical contents
• Hub Unique business keys Customer hub keyed by customer_bk
• Link Relationships between hubs Orders link connecting customer and product hubs
• Satellite Descriptive/historical attributes Address history, status changes, effective dates
• When analysts encounter Data Vault on Databricks

Bronze/silver integration zones may store hubs, links, and satellites as Delta tables under Unity Catalog.

Analysts rarely query raw vault tables for dashboards. Instead, curated gold star schemas or metric views are built on top of vault structures.

Compared with star schemas: Data Vault optimizes for auditability and source integration; star schemas optimize for BI query simplicity and aggregation performance on Databricks SQL / Photon. Source systems → Data Vault (Hubs/Links/Satellites) → Dimensional mart (Star/Snowflake) → Dashboards/Genie

Exam tip: if a scenario emphasizes long-term historization and multi-source integration with minimal redesign, Data Vault is the modeling family; if it emphasizes fast BI aggregations and clear fact/dimension grain, choose star (or snowflake when dimensions are normalized).

Best Practices for Databricks SQL Data Modeling

Prefer Star Schemas : Keep dimensions wide and denormalized to maximize Photon broadcast join speed.

Choose Integer Keys : Use integer surrogate keys ( BIGINT ) rather than long string GUIDs for join keys to reduce memory usage and speed up hash joins.

Establish Clear Grain : Avoid mixing aggregated and atomic data within the same fact table.

Pre-compute Additive Base Metrics : Store raw additive sums in facts so non-additive ratios can be calculated dynamically during query time.