Data Modelling
Layered modelling
The medallion pattern is the default vocabulary: bronze (raw, append-only, schema-on-read tolerant), silver (conformed, deduplicated, typed), gold (dimensional or wide aggregates serving BI). Justify each layer by consumer, not habit - an exam question may ask why silver should not simply be a copy of bronze (answer: conformance, dedup, and stable contracts).
Dimensional design
Know star schemas: fact tables at a declared grain, dimension tables with surrogate keys, and slowly changing dimensions - Type 1 overwrite, Type 2 versioned rows with effective dates and a current flag, Type 3 previous-value column. Choose SCD2 when history is queried; the cost is join complexity and row growth. Delta supports this with MERGE, generated identity columns (GENERATED ALWAYS AS IDENTITY), and CHECK constraints; generated columns (e.g. derived dates) save storage and are computed on write.
Physical design choices
Liquid clustering on high-cardinality filter columns; partitioning only for low-cardinality, stable columns; Z-ORDER is legacy alongside liquid clustering. Decide between streaming tables (incremental, append-oriented) and materialised views (full recompute, joins/aggregations) - the wrong choice is a favourite trap when a model needs cross-table joins refreshed incrementally. Schemas evolve: mergeSchema for additive changes, but renaming or type narrowing requires explicit migration, and consumers relying on column order should use names.
Rule of thumb: declare the grain first - every fact column must be true at that grain - then choose SCD type from how history is queried, not from how data arrives.