Memory hook: Preserve the grain before optimizing the engine.
Must remember
Raw, cleaned and curated layers often form a bronze/silver/gold pattern. These labels describe responsibilities, not mandatory product features. Delta tables add transactional metadata around supported lake files. Keep schema, partition layout and data quality explicit; simply placing Parquet files in a folder does not automatically create the intended managed Delta table.
Use PySpark for distributed code transformations, T-SQL for supported relational operations and KQL for event/time-oriented analytics. DataFrames are lazily evaluated until an action triggers work. Filter early, select needed columns and avoid collecting a large distributed dataset into a driver process. Joins can produce heavy shuffles or skew around hot keys.
Dimensional models define fact grain, dimensions and surrogate/business keys. Type 1 slowly changing dimensions overwrite the current attribute; Type 2 preserves history with new versions and effective ranges. Late facts need the dimension version valid at event time when historical correctness matters.
Deduplicate using a defensible key and ordering rule, not arbitrary row removal. Distinguish missing values from zero; standardize types/time zones; quarantine invalid records with enough context for repair. Group/aggregate only after deciding what detail can be discarded. Denormalization can improve consumption but may multiply data or complicate updates.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Need historical customer attribute at sale time | Type 2 dimension with correct effective-date join. |
| Current-only corrected spelling | Type 1 update may fit. |
| Driver out of memory | Inspect collect/to-local operations and distributed execution design. |
Traps
- Dropping duplicates without a deterministic rule can keep the wrong version.
- Aggregating early can destroy detail later analysis requires.
Active recall
1. What is a Delta transaction log for?
Tracking table versions and transactional changes around the data files.
2. Type 1 versus Type 2?
Overwrite current attributes versus preserve attribute history in versions.
3. Why can a join multiply totals?
Duplicate join keys or mismatched grain create additional matching rows.
4. What is Spark lazy evaluation?
Transformations describe work until an action triggers execution.
5. Why quarantine bad rows?
To preserve evidence and enable repair without contaminating trusted outputs.