Relational Databases & Dimensional Modeling

This section covers RDBMS fundamentals — relational theory, schema patterns, and MySQL window functions — alongside the Kimball dimensional-modeling approach to data warehousing, including star, snowflake, and galaxy schemas, conformed dimensions, and bus matrices.

Core Topics

Relational Databases

The relational model, ACID guarantees, and normalization — and where RDBMS still anchors the modern data stack alongside warehouses and lakehouses.

RDBMS Schemas

Comparison of star, snowflake, galaxy, hierarchical, network, and flat schema patterns — when each fits and the trade-offs in joins versus redundancy.

MySQL LAG Function

Window functions like LAG() for row-over-row analytics, time-series differences, and trend calculations without self-joins.

Star Schema

Central fact table surrounded by denormalized dimensions — the warehouse default for fast analytical queries and BI tooling.

Snowflake Schema

Normalized variant of the star schema; reduces storage redundancy at the cost of additional joins and query complexity.

Kimball Architecture

Bottom-up data-warehouse approach built from data marts, conformed dimensions, and a shared bus matrix spanning business processes.

Kimball Bus Matrix

Two-dimensional matrix mapping business processes to conformed dimensions — the planning artifact that anchors an enterprise data warehouse.

Real Sales Model

Worked end-to-end dimensional model for an e-commerce sales fact — grain, dimensions, surrogate keys, and slowly changing attributes.

Related on this site

Entity-Relationship Diagrams

ERDs as the conceptual layer above the schema — entities, attributes, cardinalities, and how an ERD becomes a physical design.

Logical Data Model

Bridging conceptual ERDs and physical schemas — the platform-agnostic logical model that survives database migrations.

Database Schema Design

Schema-design fundamentals: naming, keys, relationships, and how design choices ripple into query performance.

Snowflake Architecture

Cloud warehouse architecture: separation of storage and compute, virtual warehouses, and how it differs from classic on-prem RDBMS.

Medallion Architecture (Databricks)

Bronze/Silver/Gold layered design on a lakehouse — the modern dimensional-modeling parallel for streaming-first warehouses.

Delta Lake on Databricks

ACID-on-the-lake: how Delta brings RDBMS guarantees (transactions, schema enforcement, time travel) to a Parquet data lake.

About this section. Row 1 covers RDBMS basics — relational theory, schema taxonomy, and MySQL window-function patterns. Row 2 dives into the dominant warehouse schema patterns (star, snowflake) and the broader Kimball architecture they sit inside. Row 3 walks through applied dimensional modeling, from the bus-matrix planning step to a worked sales fact for an e-commerce warehouse.

Relational Databases & Modeling

Core Topics