Data Modeling

Conceptual, logical, and physical levels, and the real, compounding cost of a model that was never designed on purpose.

Read and draw ER diagrams: entities, attributes, crow’s-foot notation, 1:1 / 1:M / M:N, and bridge tables.

Natural, surrogate, and composite keys, foreign keys, and referential integrity, where most real data bugs begin.

Declare the grain before you build, and avoid the mixed-grain disasters that silently corrupt every metric downstream.

Functional dependencies, 1NF through BCNF, update anomalies, and when to deliberately denormalize for reads.

Turn requirements into a normalized operational schema, with the why: write integrity, transactions, and constraints.

Why operational and analytical models differ, how to decide between them, and the reasoning behind each structure.

The Kimball core of analytics modeling: facts measure events, dimensions give them context, built around a clear grain.

The classic tradeoff between a flat star and a normalized snowflake, and exactly when each one is the right call.

Additive, semi-additive, and non-additive measures; transaction, snapshot, and accumulating facts; factless and degenerate.

Track dimension history correctly, from overwrite to history rows to hybrids, the number-one modeling interview topic.

The three enterprise modeling philosophies compared, with a clear map of when each approach actually fits.

Hubs, links, and satellites: an auditable, scalable, parallel-loadable pattern for enterprise data warehouses.

Medallion (bronze/silver/gold), wide tables / One Big Table, partitioning & clustering as the physical model, dbt models, and the semantic / metrics layer.

Effective dating, bitemporal models, snapshots, event sourcing, and SQL:2011 temporal tables — ask what was true, what we believed, and query the past natively.

Document, key-value, and wide-column stores: access-pattern-first design, DynamoDB single-table with GSIs, and Cassandra hard rules.

Labeled property graphs and RDF triple stores: Cypher traversals, fraud-ring patterns, and the LPG vs RDF paradigm choice.

Golden records, entity resolution, and reference data: one trusted version of customers, products, and accounts.

Domain-oriented data products, DDD bounded-context decomposition, and federated governance at organizational scale.

Model features for machine learning with point-in-time correctness, avoiding leakage and training/serving skew.

Four-layer contracts (schema, semantics, SLOs, ownership), semantic breaking changes, expand-and-contract, and governance enforcement.

A repeatable requirements-to-model method, OLTP vs OLAP decisions with the why, a four-domain interview cheat sheet, and interview narration.

Partitioning, scalability under traffic, insert/update/history load mechanics, CDC as a load pattern, late-arriving data, and tradeoffs with sample queries.