Data Preparation and Engineering: From Raw Sources to Analysis-Ready Data
Domain scope
Data Preparation and Engineering tests whether you can turn raw, messy sources into an analysis-ready table without corrupting the signal. The exam emphasizes data quality, integration, transformation, feature construction, sampling, and reproducible pipelines.
Core rules and concepts
- Data quality dimensions: completeness, accuracy, consistency, timeliness, uniqueness, validity. A defect should be measured before and after cleaning.
- ETL extracts, transforms, then loads into a warehouse; ELT loads raw data first and transforms inside the target system. Cloud warehouses favor ELT because storage and compute scale separately.
- Joins must be validated on cardinality. A one-to-many join can multiply rows; check row counts and key uniqueness before and after.
- Missing data mechanisms: MCAR, MAR, MNAR. Mean imputation is safe only under strong assumptions; MNAR needs a missing-indicator or explicit model.
- Normalization (min-max) changes scale; standardization (z-score) centers and scales. Fit scalers on training data only.
- Feature engineering should be time-aware: rolling windows, lags, and recency must use only data available at the prediction timestamp.
- Sampling: random, stratified, cluster, systematic. Stratify when a rare class or subgroup matters.
Worked example
A retailer wants a monthly churn model from orders. Raw orders have duplicate order IDs, three time zones, and missing campaign codes. Steps: deduplicate by order ID keeping the latest status; convert timestamps to UTC; join customer dimension on a validated one-to-one key; create features per customer-month: order count, spend, days since last order, and support tickets in the prior 90 days; split by month so the validation period is later than training; fit median imputation and scaling on training only, then apply to validation. Reasoning: the time split mimics deployment, and training-only fitting prevents leakage. A missing campaign code is retained as “unknown” because missingness may itself be informative.
Traps
- Imputing or scaling before the train/test split.
- Removing outliers without checking whether they are the business event of interest.
- Joining on non-unique keys and silently inflating counts.
- Using a random split for time-dependent data.
- Resampling class imbalance before splitting.