Memory hook: Store for the queries you need, and treat schema changes as a compatibility contract.
Must remember
- Relational OLTP favours transactional access; warehouses favour analytical scans and aggregations; data lakes retain multiple formats; lakehouse table formats add supported transactional and metadata features on object storage. Choose by query, consistency and operational requirements.
- A star schema joins fact measurements to descriptive dimensions. Normalisation reduces update anomalies; denormalisation can improve selected reads at the cost of duplication. Slowly changing dimensions may overwrite values (type 1) or preserve versioned history (type 2).
WHEREfilters rows before grouping;HAVINGfilters groups. An inner join keeps matches; a left join preserves left rows, but a right-table predicate inWHEREcan accidentally remove unmatched rows. Window functions calculate over related rows without collapsing them likeGROUP BY.- Example:
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC)can select a latest row, but equal timestamps need a deterministic tie-breaker. UseIS NULL, not= NULL; null and an empty string are different values. - Partition pruning reduces scanned partitions; Parquet/ORC and compression reduce bytes read; column selection avoids unnecessary columns. Athena workgroups control query settings and limits. Redshift distribution, sort design and query plans influence joins/scans; Spectrum queries external data.
- Glue Data Catalog stores metadata; crawlers infer supported schemas. Inference can be wrong for mixed formats or evolving columns. Glue Schema Registry supports compatibility control for supported streaming integrations. Test additions, removals and type changes against old and new consumers.
- Open table formats such as Iceberg support schema/partition evolution and transactional operations where the engine supports them. Catalog metadata, table snapshots and physical files have separate cleanup/retention lifecycles.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Preserve a customer's historical address per sale | A history-aware dimension/model, such as SCD type 2. |
| Only one date is needed from a large lake | Partition pruning plus columnar scan. |
| Keep every customer even without orders | Left join, preserving null-extended rows in later filters. |
Traps
- A crawler discovering a column does not make every consumer compatible.
- A query that returns fewer rows may still scan the same bytes.
- Deleting table metadata may leave the underlying S3 data.
Active recall
1. Which clause filters grouped aggregate results?
HAVING.
2. Why can a left join behave like an inner join accidentally?
A later WHERE condition on the right side excludes rows whose right-hand values are null.
3. What does SCD type 2 preserve?
Historical dimension versions with suitable validity/identity fields.
4. Why can a latest-row query be nondeterministic?
Tied ordering values need a defined tie-breaker.
5. Which evidence shows whether a query is scanning efficiently?
Its plan, scanned bytes, partition selection and runtime metrics, rather than output row count alone.