Data Engineering SQL Interview Prep: Joins, Windows, Cron, and Airflow

Data engineering interviews are often less about obscure algorithms and more about whether you can keep data correct after the query leaves your laptop. SQL matters, but so do grain, scheduling, dependencies, retries, freshness, and failure recovery.

That can surprise candidates who prepared only for generic coding screens. A data engineering interviewer may ask you to deduplicate a landing table, explain an Airflow DAG, read a cron schedule, or decide whether a failed load can be safely rerun.

This guide gives you a practical prep map for the SQL and orchestration concepts that commonly appear in data engineering interviews.

What the Interviewer Is Really Testing

For many data engineering roles, interviewers want evidence that you can:

• Write SQL that preserves the right grain and does not inflate counts.
• Use window functions for ranking, deduplication, latest-row logic, and state changes.
• Recognize when a query is correct but too slow or fragile for production.
• Reason about scheduled jobs, retries, dependencies, freshness, and backfills.
• Communicate tradeoffs when data arrives late or a pipeline fails.

The goal is not to recite every Airflow operator or memorize every database dialect. The goal is dependable reasoning across data shape, query logic, and pipeline execution.

SQL Patterns to Practice First

Start with SQL patterns that map directly to pipeline work:

• Deduplication: keep the latest record per business key.
• Joins: avoid row multiplication from one-to-many relationships.
• Incremental loads: filter by high-water marks or updated timestamps.
• Aggregation: build fact tables and daily summaries at the correct grain.
• Window functions: rank rows, compare current and previous state, and sessionize events.
• NULL logic: avoid silent bugs in filters, anti-joins, and counts.
• Performance checks: know when to inspect an execution plan and index usage.

Useful refreshers: how to remove duplicates in SQL, SQL latest row per group, and how to read SQL EXPLAIN plans.

Deduplication Question

A common prompt is: "The landing table has multiple records per customer because events are appended. Keep the most recent record for each customer."

with ranked_customers as (
    select
        customer_id,
        email,
        plan,
        updated_at,
        ingest_id,
        row_number() over (
            partition by customer_id
            order by updated_at desc, ingest_id desc
        ) as rn
    from raw_customer_events
)
select
    customer_id,
    email,
    plan,
    updated_at
from ranked_customers
where rn = 1;

The important detail is the deterministic tie-breaker. If two records have the same updated_at, ingest_id desc gives the database a stable rule for choosing one row.

In a live interview, also say how you would validate the result: one row per customer_id, row count compared with distinct customer IDs, and spot checks for customers with duplicate source rows.

Join Question

Data engineering join questions often test grain, not syntax. Example: "Build a daily order summary with customer attributes."

The trap is joining orders to a customer history table that has multiple rows per customer. That can duplicate orders.

Reduce the dimension table to one row per customer first:

with current_customer as (
    select
        customer_id,
        region,
        segment
    from (
        select
            customer_id,
            region,
            segment,
            row_number() over (
                partition by customer_id
                order by effective_at desc, customer_snapshot_id desc
            ) as rn
        from customer_history
    ) x
    where rn = 1
)
select
    date_trunc('day', o.order_at) as order_day,
    c.region,
    c.segment,
    count(*) as orders,
    sum(o.amount) as revenue
from orders o
join current_customer c
  on c.customer_id = o.customer_id
group by
    date_trunc('day', o.order_at),
    c.region,
    c.segment;

Then explain the assumption: this uses current customer attributes, not historical attributes at the time of the order. If the business needs historical reporting, you need an effective-date join instead.

Incremental Load Question

Another common prompt is: "How would you process only new or changed rows?"

A simple pattern uses a high-water mark:

select
    order_id,
    customer_id,
    status,
    amount,
    updated_at
from source_orders
where updated_at > (
    select coalesce(max(processed_through), timestamp '1900-01-01')
    from pipeline_watermarks
    where pipeline_name = 'orders_incremental_load'
);

In production, the details matter:

• Use a small lookback window if source timestamps can arrive late or be corrected.
• Make the load idempotent so reruns do not duplicate rows.
• Store the watermark only after the destination write succeeds.
• Keep enough metadata to audit which batch loaded which records.

An interview answer should mention correctness before speed. A fast incremental load that misses late-arriving updates is not reliable.

Cron Basics

Data engineers do not need to be cron historians, but you should know how to read common schedules.

Also understand the operational questions behind the schedule:

• Which timezone does the scheduler use?
• Can two runs overlap?
• What happens if a run fails?
• Should a missed run be backfilled?
• Does the upstream data arrive before the job starts?

Those answers matter more than writing a cron expression from memory.

Airflow Basics

If the role mentions Airflow, be ready to explain these concepts at a high level:

• DAG: a directed acyclic graph that defines tasks and dependencies.
• Task: a unit of work, such as running SQL, calling an API, or executing Python.
• Operator: a template for a kind of task.
• Schedule: when DAG runs should be created.
• Dependency: which task must finish before another starts.
• Retry: what should happen after a transient failure.
• Backfill: running historical intervals that were missed or need recomputation.

A concise interview explanation might be: "I would model extraction, transformation, validation, and publish steps as separate tasks. The publish step depends on validation, retries handle transient upstream failures, and the job should be idempotent so backfills and reruns are safe."

Pipeline Debugging Framework

When asked how to debug a failed pipeline, use a structured answer:

1. Identify where it failed. Scheduler, extraction, transformation, load, validation, or downstream publish.
2. Check freshness and volume. Did source data arrive? Is the row count unusually low or high?
3. Inspect the error type. Syntax, permission, schema drift, timeout, duplicate key, NULL constraint, or data quality failure.
4. Decide whether rerun is safe. Idempotent jobs can be retried; non-idempotent jobs may need cleanup or a controlled replay.
5. Communicate impact. Which dashboard, table, model, or stakeholder is affected?

This shows production judgment. The interviewer wants to know whether you can recover safely, not just whether you can find a stack trace.

Performance Questions

For SQL performance, focus on practical diagnosis:

• Look at the execution plan.
• Check whether filters are sargable.
• Verify join keys and indexes.
• Reduce data before joining when it is logically safe.
• Avoid unnecessary sorts, especially in large window functions.
• Materialize intermediate results when reuse or inspection justifies it.

Do not claim that one pattern is always faster. CTEs, temp tables, and subqueries depend on the database engine, statistics, indexes, and data volume. For a decision framework, see SQL CTE vs subquery vs temp table.

Sample Live Answer Structure

For a data engineering SQL prompt, use this structure:

1. State the target table grain.
2. Identify source table grains and possible duplicates.
3. Write the query in named steps.
4. Add deterministic ordering for window functions.
5. Explain validation checks.
6. Discuss how the query would run in a scheduled pipeline.

This is the bridge many data engineering interviews are testing: SQL correctness plus production judgment.

One Week Prep Plan

• Day 1: Joins, grain, and duplicate-row debugging.
• Day 2: Window functions for dedupe, latest row, and state changes.
• Day 3: Incremental loads, watermarks, and idempotent writes.
• Day 4: SQL performance basics: indexes, EXPLAIN, scans, and sorts.
• Day 5: Cron, Airflow DAGs, dependencies, retries, and backfills.
• Day 6: Pipeline debugging scenarios.
• Day 7: Timed practice: solve a SQL prompt, then explain how you would productionize it.

The Main Takeaway

Data engineering interview prep should connect SQL correctness with pipeline reliability. Practice window functions, joins, deduplication, incremental loads, and performance checks, but also practice explaining schedules, dependencies, retries, freshness, and safe reruns.

The strongest answers show that you can produce the right data and keep producing it reliably when the pipeline runs tomorrow.