Foundations

Data Modeling: The Visual Notebook

Facts, dimensions, star schemas and slowly changing data, drawn rather than described.

  • Handwritten-style
  • Diagrams and tables
  • Worked examples
Start reading

Who this notebook is for

Anyone who has to design tables for analytics, and candidates facing data modeling rounds who want the diagrams in their head.

What you will be able to do

  • Draw an entity model and set the right cardinality between tables
  • Normalize a design step by step and explain each normal form
  • Build a star schema and choose the grain of a fact table
  • Handle changing data with slowly changing dimensions and understand Data Vault

What's inside

Pick a chapter and the notebook opens right there.

  1. Core Philosophy & Entity Modeling FundamentalsThe ShopEasy story, spreadsheet catastrophes, Entity vs Attribute separation, Natural vs Synthetic keys, ER diagramming & Crow's Foot notationStart here
  2. Advanced Relationships, Cardinality & Junction Mechanics1:1 vs 1:N vs M:N cardinality rules, junction tables with payload attributes, self-referencing trees, and composite foreign key integrityStart here
  3. The Deep Normalization Journey (1NF to 5NF)First Normal Form (atomicity), 2NF (partial dependencies), 3NF (transitive chains), Boyce-Codd (BCNF), 4NF (multi-valued dependencies), and 5NF (join projection losslessness)Start here
  4. Relational Anomalies, ACID & Integrity InvariantsUpdate, Insert & Delete anomalies, ACID transactions, isolation levels, multi-table check constraints, soft deletes vs hard deletes, and ledger append patternsStart here
  5. Dimensional Modeling Foundations (Kimball vs Inmon)OLTP vs OLAP paradigm divide, Ralph Kimball vs Bill Inmon, 4-step dimensional design method, declaring grain, and the Enterprise Bus ArchitectureStart here
  6. Star Schema, Snowflake Schema & One Big Table (OBT)Pure Star Schema radial layout, Snowflake normalization trade-offs, modern OBT architectures, dictionary encoding benchmarks, and columnar join cost economicsStart here
  7. Deep Key Architecture & Hashing MechanicsSurrogate keys vs natural keys, durable supernatural business keys, deterministic hashing (MD5/SHA256), late-arriving dimensions with ghost records, and degenerate dimensionsStart here
  8. Slowly Changing Dimensions (SCD Type 0 to Type 7)Type 0 (retained), Type 1 (overwrite), Type 2 (effective date ranges & current flags), Type 3 (previous values), Type 4 (history tables), Type 6 (hybrid), and Type 7 (dual dimensions)Start here
  9. Advanced Fact Table ArchitecturesTransaction facts (atomic line grain), Periodic snapshots (daily balances), Accumulating snapshots (milestone lifecycles), Factless facts (event coverage), and measure classification (Additive, Semi, Non)Start here
  10. Advanced Dimension Modeling & HierarchiesConformed dimensions & enterprise bus matrix, role-playing dimensions, junk dimensions (consolidating flags), outrigger dimensions, ragged parent-child bridge tables, and weighted bridge tablesStart here
  11. Modern Lakehouse & Cloud Warehouse ModelingMedallion architecture (Bronze/Silver/Gold), BigQuery STRUCT & ARRAY nested records, semi-structured JSON vs Parquet shredding, Delta Lake & Iceberg table formats, and dbt patternsStart here
  12. Graph & Document Data ModelingParadigm shift (Relational vs Document vs Graph), embedding vs referencing rules, bucket & subset patterns, Labeled Property Graphs, index- free adjacency, and polyglot persistenceStart here
  13. Temporal & Bitemporal Data ModelingValid Time (business) vs Transaction Time (system), bitemporal 2D matrix, SQL:2011 temporal standard, retroactive interval splitting, point-in- time range joins, and regulatory audit non-repudiationStart here
  14. Data Vault 2.0 ArchitectureHubs (business keys), Links (natural transactions), Satellites (descriptive context & hash diffs), zero-dependency parallel ingestion via MD5/SHA256, Point-in-Time (PIT) tables, and Kimball-on-Vault martsStart here
  15. Physical Performance Optimization & Storage LayoutRow stores vs Columnar formats (Parquet), partitioning mechanics (Range, Hash, List), clustering & Z-Order curves, Databricks Liquid Clustering, vectorized read pruning, and salting data skewStart here
  16. Staff FAANG Data Modeling System DrillsOmnichannel retail fulfillment (Amazon), real- time ride dispatch & H3 spatial surge (Uber), double-entry financial ledger (Stripe), streaming session feature store (Netflix), and IoT fleet telemetry (Tesla)Start here
  17. Enterprise Architecture, Anti-Patterns & Master CheatsheetTop 10 deadly data modeling anti-patterns, Data Mesh & semantic layer architecture, the 5-step Staff interview defense blueprint, and the Master 100-Point Certification CheatsheetStart here
  18. Modern Semantic Layer, Activity Schema & Vector ModelingDecoupled metrics (MetricFlow), 11-column activity stream, vector embeddings (HNSW) & Data Mesh data productsStart here
  19. Gap-Fill SupplementFact constellation & anchor modeling, wide-column & time-series, event modeling, schema registry, CQRSStart here

Practice this

Put it to work on LakeBench.

Read the guide first

Short explainers that pair with this notebook.

Common questions

Is it about Kimball or Inmon?
Both. It compares the two approaches and then goes into star schemas, snowflake schemas, one big table designs and Data Vault.
Does it include interview practice?
Yes. A chapter of system-style modeling drills and a master cheat sheet close the book.
Can I read a sample before I commit?
Yes. The contents pages and the first pages of every notebook can be read online, so you can judge the style before going further.

More in Foundations

All notebooks