Data Engineering Interviews: SQL and ETL Questions That Separate Senior from Mid-Level

Most data engineering interview guides throw a list of trivia questions at you — "What's a partition key?", "Explain the difference between a fact and a dimension." That stuff is table stakes. The gap between mid-level and senior is whether you can write the SQL cold and reason through an ETL pipeline on the spot, not whether you can define terms.

Here are the patterns that interviewers actually use to make that call.

SQL: three problems that reveal whether you think in sets

The trick isn't knowing ROW_NUMBER() exists. It's reaching for it without being told.

1. Window functions without being prompted

The problem: You have a table of user events. Find each user's most recent event, plus the timestamp of the event immediately before it.

-- events: user_id, event_type, event_ts

WITH ranked AS (
    SELECT
        user_id,
        event_type,
        event_ts,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts DESC) AS rn,
        LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts DESC) AS prev_ts
    FROM events
)
SELECT user_id, event_type, event_ts, prev_ts
FROM ranked
WHERE rn = 1;

A mid-level candidate writes a self-join with a subquery. A senior candidate uses ROW_NUMBER() and LAG() in one pass. The difference is whether you reach for window functions as a default tool, not a last resort.

2. Cumulative aggregation with a condition

The problem: Given daily sales per product, compute the running total of sales for each product, but only include days where the product sold at least one unit.

-- daily_sales: product_id, sale_date, units_sold, revenue

SELECT
    product_id,
    sale_date,
    units_sold,
    revenue,
    SUM(revenue) OVER (
        PARTITION BY product_id
        ORDER BY sale_date
    ) AS running_revenue,
    SUM(revenue) OVER (
        PARTITION BY product_id
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_revenue_sold_days_only
FROM daily_sales
WHERE units_sold > 0
ORDER BY product_id, sale_date;

The WHERE before the window function does the work. Candidates who try to filter inside the window function itself (which you can't do without a subquery or CTE first) reveal they haven't internalized SQL's order of operations: WHERE filters rows before the window function sees them.

3. Deduplication that preserves business logic

The problem: You have a table with duplicate rows where updated_at differs. Keep the most recent version of each row, but if two rows have the same updated_at, keep the one with the highest version field.

WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY entity_id
            ORDER BY updated_at DESC, version DESC
        ) AS rn
    FROM staging_table
)
SELECT *
FROM ranked
WHERE rn = 1;

ROW_NUMBER() with a multi-column ORDER BY is the cleanest dedup pattern in SQL. DISTINCT fails when rows differ by a column you care about but aren't "the key." GROUP BY with MAX(updated_at) loses columns you want to keep. The interviewer is testing whether you reach for ROW_NUMBER() — and whether you know to partition by the entity concept, not the table's primary key.

ETL design: the 4-step framework

When an interviewer asks "Design an ETL pipeline to ingest data from X into Y," they don't want a list of tools. They want structured thinking. Here's the framework:

Step 1: Source analysis

Answer these before touching transformation logic:

• Shape: What does the source schema look like? Is it flat or nested?
• Volume: How much data? Is it batch (nightly dump) or streaming?
• Cadence: How frequently does it arrive? Is it append-only, upsert, or full refresh?
• Quirks: Known data quality issues — nulls masquerading as 'N/A', duplicate events, timezone mismatches.

Step 2: Transformation logic

State the transformations as a sequence, not a diagram:

1. Normalize — flatten nested JSON, split multi-value columns, cast types
2. Clean — null handling, deduplication, outlier flagging
3. Enrich — join with dimension tables, derive computed columns (e.g., order_age_days)
4. Validate — constraints that must hold before loading (NOT NULL on key columns, referential integrity on FK joins)

Step 3: Error handling

The interviewer wants to hear that you don't let one bad row kill the pipeline:

• Dead-letter queue — route unparseable records to a quarantine table with the raw payload + error reason + timestamp
• Retry logic — transient failures (network, API rate limits) get exponential backoff
• Alert threshold — if >X% of records land in quarantine, page the on-call

Step 4: Scheduling and scale

Close with the operational layer:

• Orchestration — what triggers the pipeline? Cron, Airflow, event-driven (S3 bucket notification)?
• Compute — Spark for large transforms, SQL for small ones. Justify the choice with the volume from Step 1.
• Idempotency — can the pipeline run twice on the same input without corrupting the target? If not, how do you guard against it?

Data modeling: what they're actually testing

Data modeling questions in DE interviews aren't about normal forms. They're about whether you can take a vague business question and produce a schema that answers it efficiently.

The prompt: "Design a schema for a subscription business that needs to report monthly recurring revenue (MRR) by plan tier."

Good answer (star schema):

-- Fact: one row per subscription per month
CREATE TABLE fact_mrr (
    subscription_sk  BIGINT,     -- FK to dim_subscription
    date_sk          BIGINT,     -- FK to dim_date
    plan_sk          BIGINT,     -- FK to dim_plan
    customer_sk      BIGINT,     -- FK to dim_customer
    mrr_amount       DECIMAL(12,2),
    is_active        BOOLEAN
);

-- Dimensions
CREATE TABLE dim_subscription (subscription_sk, subscription_id, started_at, ...);
CREATE TABLE dim_customer     (customer_sk, customer_id, region, ...);
CREATE TABLE dim_plan         (plan_sk, plan_tier, monthly_price, ...);
CREATE TABLE dim_date         (date_sk, date, month, quarter, year, ...);

What this shows the interviewer:

• You know star schema means "fact at the center, dimensions around it"
• You understand surrogate keys (_sk) and why they're safer than natural keys in a data warehouse
• You pre-computed mrr_amount into the fact table rather than joining to dim_plan.monthly_price on every query — you understand that warehouse performance comes from denormalization at write time
• You included is_active as a flag, which means you're thinking about slowly changing dimensions (subscriptions get cancelled, but historical MRR shouldn't recalculate retroactively)

A mid-level answer is: "Subscription table, customer table, plan table, join them." A senior answer is: "Star schema with pre-computed MRR in the fact table, surrogate keys, and an active flag so historical reports don't shift."

---

The patterns above — SQL with window functions, structured ETL reasoning, and dimensional modeling with a star schema — are the consistent differentiator in DE interviews. If you want the full set of SQL challenges, ETL scenarios, data modeling exercises, and system design walkthroughs with worked solutions for each, the Data Engineering Interview Guide packages them into a structured study plan.