Back to Data Engineer Job Simulation

Prerequisites & resources - Data Engineer simulation

Nothing to install and nothing to upload. Both tickets are answered in the browser: five questions and a written note each. No repository is submitted and no code of yours is executed.

Everything needed is printed in the tickets - an Airflow task, the SQL it runs, the warehouse schema, and two weeks of run history.

What you should already know

You do not need warehouse experience. You need to read SQL carefully and reason about what a pipeline does on the second run.

  • SQL joins - inner vs left, and what happens to the row count when the right side has duplicates
  • Aggregation - what COUNT(*) counts after a join, and when you need COUNT(DISTINCT ...)
  • NULL semantics - three-valued logic, and why NOT IN with a NULL behaves the way it does
  • Idempotency - what makes a load safe to run twice
  • Basic warehouse modelling - fact and dimension tables, and what "grain" means
  • Partitioning - roughly why large tables are split, and how that helps a filtered query
  • Slowly changing dimensions - enough to know the problem exists

You do not need Spark, Kafka, or any specific cloud vendor.

Reference material

Looking things up is expected.

  • Join fan-out - any reputable write-up of "SQL join duplicates rows"; the mechanism matters more than the name
  • NULL and NOT IN - PostgreSQL docs → Row and Array Comparisons; the classic NOT IN vs NOT EXISTS discussion
  • Idempotent pipelines - Airflow docs → Best Practices, the section on idempotency and re-running tasks
  • Late-arriving data - any write-up of rolling reprocess windows / lookback windows
  • Slowly changing dimensions - Kimball's Type 1 / Type 2 distinction
  • Partitioning and clustering - your warehouse's docs (BigQuery, Snowflake and Redshift all document the same idea under slightly different names)
  • Data quality gates - dbt tests, or Great Expectations, as examples of checks that block a run

Ground rules

  • Answer from the SQL and the facts given. Each question points at a specific clause, a specific schema detail, or a specific numbered requirement.
  • DE-101 contains four separate bugs and one SQL trap. Fixing the join does not fix the retry. Ruth says so, and the rubric scores whether you kept them apart.
  • Read the excluded_statuses contents carefully. One of the five defects turns entirely on what is in that table.
  • Using AI assistance is allowed, as on the job. The questions turn on this specific query, and the notes are graded on reasoning you would defend to a finance lead.
  • Two free submissions per ticket. Read the feedback before resubmitting.