Data Engineering Interview Prep: SQL, Spark & System Design

Master joins and avoid the left join null trap. Place predicates in the on clause and test for nulls to ensure the correct rows survive.

Explore common aggregation gotchas in SQL, learn to prevent fan-out with group by keys, understand where versus having order, and fix double counting with subquery aggregation.

Explore window functions in SQL, comparing row_number, rank, and dense_rank inside each partition to handle ties, and learn top-per-group queries and running totals with frame clauses.

Use lag and lead with partition by to compute period over period growth, retention, and time to first purchase in a single pass, avoiding self joins and null edge cases.

Explore top-n per group with three approaches using window functions, including row_number, a correlated subquery, and a lateral/cross apply pattern, weighing tradeoffs for real-world SQL.

Learn to choose between subqueries, common table expressions, and temp tables to maximize readability and scope in sql, including recursive ctes for hierarchies.

Master the single pattern of islands and gaps by using row numbers to form an island key, then group and aggregate to reveal the longest consecutive streaks.

Explore sessionization with a 30-minute idle threshold, using lag, flags, and a running sum to define per-user sessions and surface complete daily counts with timezone awareness.

Read execution plans bottom-up to diagnose queries by comparing actual rows to estimates, focusing on leaves and scans. Use explain analyze in Postgres, Snowflake, and Spark to spot stale stats.

Master partition pruning, predicate pushdown, and projection pushdown to read less data and cut bytes scanned. Keep predicates raw, and rely on clustering keys to enable pruning.

Diagnose SQL performance issues by spotting anti-patterns in query plans, then apply fixes like normalize data, explicit type matching, name only the columns you need, and rewriting complex joins.

Compare OLTP and OLAP workloads to understand why normalization protects writes and denormalization speeds reads, then decide who reads the data and design with star or snowflake schemas.

Defend a star schema under pressure by naming read patterns and join costs, comparing speed and simplicity to snowflake's storage benefits and update centralization.

Declare the grain before you draw, separating facts and dimensions to prevent double counting, then write one row per declared grain and prove it with count distinct on order ID.

Use SQL with merges and as was versus as is history queries. Explore slowly changing dimensions types 1, 2, 3, and 6, and they preserve or overwrite history in changes.

Explore normalization vs denormalization trade-offs in data warehousing, balancing write safety with read speed through star schemas, joins, staging, and marts.

Compare Kimball, Inmon, and Data Vault to understand when each fits. Learn to map drivers to designs, blend approaches, and pick a clear recommendation for data warehousing.

Master pythonic idioms for data engineering interviews by using comprehensions and generators instead of manual loops. Emphasize memory efficiency with lazy evaluation, enumerate, zip, context managers, and f-strings, Spark.

Learn how spark runs jobs by mapping the driver, executors, and partitions, embracing lazy evaluation and scale-out architecture to optimize parallelism and avoid data skew.

Learn how Spark uses lazy transformations and actions. Read the DAG to identify shuffles and optimize narrow versus wide transformations for faster jobs.

Master PySpark data frame API with select and filter, using col for robust column expressions, and learn group by, joins, window ranking, and narrow versus wide shuffles impact on performance.

Compare broadcast joins, partitioning, and bucketing to minimize shuffles, and learn when each approach wins, guided by memory constraints and physical plan checks.

Learn to diagnose data skew in Spark by counting rows per key, identify the hotkey, and fix it with salting and adaptive query execution (AYQE) using the Catalyst optimizer.