SQL SQL In Depth
0%
Guided SQL & Engine Coding Lab

Star Schema with Automated SCD Type 2 Dimension Pipeline

A Kimball dimensional warehouse pipeline implementing automated Slowly Changing Dimension (SCD Type 2) effective date range versioning, surrogate key assignment, and point-in-time historical fact-to-dimension joins.

~25 min Intermediate Lessons: Hands-On Build: Star Schema with Automated SCD Type 2 Dimension Pipeline, OLTP vs OLAP Workload Physics: Point Lookups vs Billion-Row Scans, Kimball Star Schema: Fact Table Grain, Additive Measures & Conformed Dimensions, Snowflake Schema vs Star Schema vs One Big Table (OBT) in Cloud Warehouses, Slowly Changing Dimensions: SCD Type 0, 1, 2 (Effective Dates), 3 & 6, Role-Playing Dimensions, Junk Dimensions & Many-to-Many Bridge Tables
Guided SQL & Storage Engine Lab 0/3 Steps Completed

Execute & Verify SQL & Storage Engine Invariants in the Browser SQL Studio

Complete 3 progressive relational steps with guided hints in PostgreSQL 18 WASM, SQLite 3 & Step-by-Step Relational Table Visualizer against automated result-set and query-plan assertions — zero local setup required.

01 Phase 1 · Base Relation & SARGable Predicate
Base Relation Scan & SARGable Filter (org_hr)
Establish the base relation scan in org_hr for Hands-On Build: Star Schema with Automated SCD Type 2 Dimension Pipeline with a SARGable predicate and explicit Three-Valued Logic NULL handling.
4 SQL assertions
02 Phase 2 · Relational Join, Aggregation & 3VL Guard
Relational Join, Grouping & NULL-Safe Aggregation
Combine normalized tables via explicit JOIN conditions and fold group buckets or window partitions without losing NULL boundary rows.
4 SQL assertions
03 Phase 3 · Production Query & Plan Verification
Production Query & Execution Plan Verification (Hands-On Build: Star Schema with Automated SCD Type 2 Dimension Pipeline)
Verify final column projections, ordering, and physical B+Tree access paths via EXPLAIN QUERY PLAN and automated relational assertions.
4 SQL assertions

Architectural Overview

A Kimball dimensional warehouse pipeline implementing automated Slowly Changing Dimension (SCD Type 2) effective date range versioning, surrogate key assignment, and point-in-time historical fact-to-dimension joins.

In production relational and analytical database systems—spanning PostgreSQL, SQLite, MySQL/InnoDB, and DuckDB—understanding how query clauses, schema constraints, index structures, and storage engines execute under the hood separates surface-level query authors from staff-level database engineers. This capstone build lab guides you through implementing Star Schema with Automated SCD Type 2 Dimension Pipeline from first principles with deterministic self-test invariants.

Guided Implementation Walkthrough (4 Progressive Steps)

Step 1: Initial Dimension Seed & Surrogate Key Allocation

  • Core Database Concept: Decoupling Warehouse Surrogate Keys from Operational Natural Keys
  • Target Module: index.js (ScdType2Pipeline.applyChange)
  • Engineering Specification: Implement the initial insert path in applyChange(naturalKey, attrs, changeTs) to allocate a monotonically increasing sk (surrogate key) and initialize [valid_from, "9999-12-31") with is_current: true.
  • Implementation Hint: If no current row exists for naturalKey, insert a new version with valid_from = changeTs, valid_to = "9999-12-31", is_current = true.
  • Verification Invariant: deterministic assertion verified in test.js step 1

Step 2: SCD Type 2 Attribute Diffing & Prior Row Expiration

Step 3: Point-in-Time Historical Surrogate Key Lookup

  • Core Database Concept: Half-Open Interval Containment: valid_from <= eventTs < valid_to
  • Target Module: index.js (ScdType2Pipeline.lookupSurrogateKeyAsOf)
  • Engineering Specification: Implement lookupSurrogateKeyAsOf(naturalKey, eventTs) using half-open interval logic (valid_from <= eventTs && eventTs < valid_to) so boundary timestamps match exactly one version.
  • Implementation Hint: Find the dimension version for naturalKey where row.valid_from <= eventTs && eventTs < row.valid_to.
  • Verification Invariant: deterministic assertion verified in test.js step 3

Step 4: As-Was Historical Fact Table Star Join & Aggregation

  • Core Database Concept: Attributing Fact Revenue to the Dimension Attribute Active at Event Time
  • Target Module: index.js (ScdType2Pipeline.enrichAndAggregateFacts)
  • Engineering Specification: Implement enrichAndAggregateFacts(facts) to join each fact event to the dimension version that was active at fact.eventTs and aggregate revenue by historical tier.
  • Implementation Hint: Resolve each fact’s historical dimension row via lookupSurrogateKeyAsOf and sum amount_cents by the historical tier.
  • Verification Invariant: deterministic assertion verified in test.js step 4

Relational & Storage Engine Guarantees

  • Curriculum Domain: Dimensional Modeling & Kimball OLAP
  • Difficulty & Time Budget: Advanced (~40 minutes)
  • Key Architectural Highlights:
    • Automated SCD Type 2 change detection expiring prior version rows and inserting new surrogate keys
    • Half-open [valid_from, valid_to) temporal interval invariants with is_current boolean flags
    • As-was point-in-time historical fact table enrichment resolving surrogate keys by event timestamp
  • Core Primitives & Tags: Star Schema, SCD Type 2, Surrogate Keys, Point-in-Time Join, Kimball OLAP

Related Curriculum Units

Production Database Context & Verification

This from-scratch relational engine component is engineered with zero external dependencies and deterministic verification harnesses. Every internal database invariant—from three-valued logic (TRUE / FALSE / UNKNOWN), referential integrity, and fan-out-free join pre-aggregation to B+Tree page splits, MVCC snapshot visibility (xmin / xmax), slotted-page compaction, and ARIES write-ahead log crash recovery—is asserted directly in test.js. Use the interactive browser workbench above to complete the 4 guided steps in Guided Lab mode or inspect and benchmark the complete reference architecture in Full Solution mode.