Data Transformation and Modeling Study Guide — Data Engineer Associate

Data Transformation and Modeling – DE Associate Study Guide

This domain accounts for 22% of the exam — the largest section — and covers transforming and modeling data with Spark SQL and PySpark to build the silver and gold layers.

Core Transformations

• JOIN — matches rows across tables on a key (vs. UNION, which stacks rows).
• GROUP BY with aggregates (SUM, COUNT, AVG) — collapses rows into per-group summaries.
• Window functions — compute values across a partition while keeping individual rows, e.g. ROW_NUMBER() OVER (PARTITION BY customer ORDER BY order_date).
• explode() (LATERAL VIEW explode) — turns each element of an array column into its own row, flattening nested data.
• SELECT DISTINCT / dropDuplicates — removes duplicate rows.