Data Transformation & Automation
What to Know
Data transformation and automation covers the pipeline from raw source to analysis-ready data: extraction, cleaning, joining, reshaping, and scheduling. Candidates should understand ETL versus ELT (transform-before-load versus load-then-transform with warehouse compute), idempotency (a job rerun produces the same result), incremental versus full refresh, deduplication with window functions, pivoting and unpivoting (long/wide reshaping), type coercion, null handling, and orchestration concepts like dependencies, retries, and backfills. Know how joins change row counts: inner, left, full outer, and cross joins, and the risk of fan-out when the right table has duplicate keys.
Traps
Question writers love ambiguous null semantics: NULL = NULL is not true, so use IS NULL or null-safe comparisons; aggregates like SUM and AVG ignore nulls, while COUNT(*) counts rows and COUNT(col) skips nulls. Watch for string-based dates sorting incorrectly, implicit casts truncating decimals, and filters placed after a left join accidentally converting it to an inner join. Another classic trap: aggregating before versus after a join produces different totals when duplicates exist. Automation traps include assuming a retry is safe when the job appends instead of upserts, and confusing scheduled time with data freshness (a job that ran at midnight may hold yesterday's data).
Worked Example
Suppose an orders table has one row per line item and you join a customer table that has two rows for one customer due to a duplicate record. A left join doubles that customer's order rows, inflating revenue. The rule of thumb: before joining, verify the right-side key is unique with COUNT(*) versus COUNT(DISTINCT key); if not, dedupe first (for example, keep the latest record per key using a row-number window ordered by update timestamp descending, filtering to row 1). Then aggregate. This one check prevents most fan-out errors in exam scenarios.