Data Engineer interview questions for experienced

Work from evidence, not guesses.

1. Capture the exact query and its parameters. Run EXPLAIN (ANALYZE, BUFFERS). Compare it with an old plan if you saved one.
2. Compare estimates with actual numbers. If they are far apart, run ANALYZE on the tables. Data growth or a bulk load may have made the statistics old.
3. Look for a changed plan. A seq scan may have replaced an index scan. A bad join order may have replaced a good one.
4. Check table and index bloat (wasted dead space). Check that autovacuum keeps up.
5. Check for locks and contention in pg_stat_activity. Look for other heavy jobs running at the same time.
6. Check data growth. The table may have grown to a size where the plan changes. A new uneven value may have appeared.
7. Check recent changes: a dropped index, a schema migration, a new parameter value, or a version upgrade.

Fix the root cause. It may be statistics, an index, a query rewrite, or partitioning. Then add monitoring with pg_stat_statements so you see the problem early next time.

What they are checking: Do you have a clear, step by step way to debug slow queries?

A recursive CTE has two parts joined by UNION ALL. The anchor query gives the starting rows. The recursive part refers to the CTE itself. It repeats until the recursive part returns no new rows.

Islands are runs of consecutive values. Gaps are the breaks between them. The standard trick is to subtract a ROW_NUMBER from the value. Inside one island, the difference stays the same.

A Type 2 dimension stores one row per version, with a validity range. Filter on the range with a half-open interval so exactly one row matches.

Do two steps in one transaction. First close the changed current rows. Then insert the new versions.

EXPLAIN shows the planned tree. EXPLAIN (ANALYZE, BUFFERS) runs the query and adds real timings, row counts and block reads. Read from the innermost, most indented node outward.

Common reasons:

Mark the start of a new session when the gap from the previous event is more than 30 minutes. Then take a running sum of the marks. That sum is the session id.

Use INSERT ... ON CONFLICT. It is atomic (all or nothing) and safe when many writers run at once. It needs a unique index or constraint on the conflict column.

PostgreSQL has a short option called DISTINCT ON. It keeps the first row of each group, based on the ORDER BY.

Avoid one giant transaction. It holds locks, makes the WAL (write-ahead log) very large, and can run for hours.

Options:

• Do the work in batches of 10,000 to 100,000 rows. Commit after each batch and sleep briefly between batches:

Use conditional aggregation. It is portable and fast. PostgreSQL has no PIVOT keyword.

A B-tree composite index is sorted by the first column. Inside that, it is sorted by the second column, and so on. The index helps only when the query uses a leftmost prefix (the first columns of the index).

Partitioning splits one logical table into physical child tables by a key. Queries and maintenance then touch only the pieces they need.

Both define which rows around the current row go into the window function.

If there is exactly one row per day, use a ROWS frame:

Use the ordered-set aggregates percentile_cont and percentile_disc. The first interpolates, so it can give a value between two rows. The second returns a real value from the data.

Give each user a cohort (their signup month). Work out the months since signup for each activity. Then count distinct active users for each cohort and month offset.

Both test whether a value is in a subquery result.

• IN compares a value to the list of values from the subquery.
• EXISTS returns true as soon as the correlated subquery finds one row. It does not care about the values.

• B-tree (default): equality, ranges and sorting. It is right for most columns.
• Hash: equality only. It is rarely better than B-tree.
• GIN: many values per row, such as jsonb, arrays and full-text search.
• GiST: geometric data, ranges, nearest-neighbour search and full-text search.
• BRIN: a tiny index for huge...

• Nested loop: for each outer row, look up the matching inner rows. It is best when the outer side is small and the inner side has an index. The cost grows with the outer rows times the lookups.
• Hash join: build a hash table on the smaller input, then probe it with the other input. It is good for large unsorted...

Generate the calendar with generate_series. Then LEFT JOIN the data to it.

A view stores only a query. Each read runs the query again on current data. A materialized view stores the result on disk. You refresh it when you ask.

A correlated subquery refers to the outer query. So it logically runs once for each outer row.

Start with EXPLAIN ANALYZE. Then reduce the work.

PostgreSQL uses MVCC (it keeps old row versions). After an UPDATE or DELETE, the old versions stay behind. These dead tuples take space until VACUUM frees it.

An idempotent pipeline gives the same result however many times you run it for the same input. This makes retries and backfills safe.

Techniques:

• Overwrite by partition: delete and reload one date partition (or use INSERT OVERWRITE) instead of appending.
• Use upsert or MERGE on a business key, not a plain INSERT.
• Make outputs predictable: build file names and ids from the input (a hash or batch date), not from now() or random UUIDs.
• Write to a temp location and swap it in at once on success.
• Keep a run log keyed by batch id, so a finished batch is skipped.
• Avoid side effects that cannot be repeated, such as sending emails. Or guard them.

BEGIN;
DELETE FROM fact_sales WHERE sale_date = :run_date;
INSERT INTO fact_sales SELECT * FROM stg_sales WHERE sale_date = :run_date;
COMMIT;

What they are checking: Whether you design for safe reruns, the core DE skill

CDC (Change Data Capture) finds inserts, updates and deletes in a source database. It sends them to other systems as they happen.

Approaches:

• Log-based: read the database transaction log (PostgreSQL WAL, MySQL binlog). It puts little load on the source, captures deletes, and keeps order. Tools: Debezium, AWS DMS.
• Trigger-based: triggers write changes to an audit table. This adds load to the source.
• Timestamp or version column: query updated_at > last_run. It is simple, but it misses hard deletes. It can also miss rows if the timestamps are not reliable.
• Snapshot diff: compare full snapshots. This is heavy.

Log-based CDC is the usual answer for production. Plan for an initial snapshot plus an ongoing stream. Think about order and schema changes. Also decide how deletes appear (a tombstone or an op field). The target applies changes with MERGE on the primary key.

What they are checking: Whether you know log-based CDC and its trade-offs

First make things stable, then fix the cause, then prevent it from happening again.

1. Assess: read the logs. Find the failed task, the error, and what was already written. Check that the target is in a consistent state for users.
2. Communicate: tell downstream owners that the data is partial, and until when.
3. Fix the cause: bad data, schema change, timeout, credentials, or capacity.
4. Rerun safely: loads should be idempotent, so rerun the failed task and its downstream tasks for the same logical date. Do not rerun the finished ones. In Airflow, clear the failed task instance.
5. Reconcile: compare row counts and key totals between source and target.
6. Prevent: write to staging and swap or commit at once. Add retries with backoff, data quality gates, and alerts on failure and freshness.

If the pipeline is not idempotent, fix that first. Make the affected loads partition-overwrite or MERGE, so a rerun cannot create duplicates.

What they are checking: Whether you can recover calmly using idempotency and reconciliation

Measure the problem, find the source, fix it, then prevent it.

Find the duplicates:

SELECT order_id, COUNT(*) FROM fact_orders GROUP BY order_id HAVING COUNT(*) > 1;

Check the load timestamps or batch ids on the duplicate rows. If the copies came from different batches, a rerun appended instead of overwriting. If they came from the same batch, the source or a join made them. A one-to-many join can multiply rows. The source may also have sent the record twice.

Fix: keep one row per key with ROW_NUMBER on a rule such as the latest loaded_at. Then rewrite the table or partition.

Prevent:

• Make loads idempotent (MERGE on the business key or partition overwrite).
• Add a unique constraint or a dbt unique test on the key.
• Remove duplicates in staging.
• Reconcile counts with the source after each load.

Do not just delete rows without knowing the cause. The duplicates will come back.

What they are checking: Whether you look for the root cause, not only a cleanup query

First separate event time (when it happened) from processing time (when you received it). Then decide how long you will wait.

Treat it as a controlled reprocessing job.

• Avro: a binary format that stores data row by row, with the schema inside. It handles schema changes well and is compact for writes. Best for streaming (Kafka with a schema registry) and for ingesting one record at a time.
• Parquet: a column-based format with wide support in Spark, Presto/Trino, BigQuery, Snowflake and Athena. Best for analytics on a data lake.
• ORC: a column-based format that began in Hive. It has built-in indexes and ACID support in Hive. Strong in Hive and Hadoop setups.

• At-most-once: a message arrives zero or one times. There are no duplicates, but data can be lost.
• At-least-once: a message arrives one or more times. Nothing is lost, but duplicates are possible after retries.
• Exactly-once: each message changes the result only once.

Partition by the column that queries filter on most, if it has a moderate number of distinct values. Date is the usual choice for event data.

Monitor both the jobs and the data.

Job health: failures, retries, run time compared with normal, queue time, resource use, missed schedules.

Data health: freshness (the latest timestamp is recent), volume (row count in an expected range), schema changes, null rate, duplicates, and reconciliation with the source.

Keep a control table with the last successful high-water mark. Extract rows after it, upsert them, and move the mark forward only on success.

Start from the needs: freshness, volume, history, and users.

Materializations control how a model is stored:

• view: a saved query, always fresh, no storage.
• table: rebuilt fully on each run.
• incremental: after the first run, only new or changed rows are processed.
• ephemeral: inlined as a CTE, not created in the database.

They are open table formats. They add warehouse-like features on top of Parquet files in object storage.

Design for limits, retries and restarts.

Separate temporary failures from bad data.

Protect the data at every stage.

Do not depend on column position.

Test at several levels.

Measure first, then cut the biggest items.

Incremental loads based on updated_at never see a hard delete. So you need an explicit approach.

Treat it as a performance regression and find where the time went.

I compare run-time limits, data volume, cost model and effort to operate.

Both combine historical and real-time processing.

A data contract is an agreement between a data producer and its consumers. It defines the schema, meaning, quality needs, freshness, ownership, and how changes are shared.

Start with the business process (placing an order). Then declare the grain: one row per order line item.

Fact: fact_order_line

• Keys: date_key, customer_key, product_key, store_or_channel_key, promotion_key, payment_key.
• Degenerate dimension: order_number.
• Measures: quantity, unit_price, discount_amount, tax_amount, net_amount (all additive).

Dimensions:

• dim_date (day, week, month, quarter, holiday flag).
• dim_customer (SCD Type 2 for segment and city; keep the natural key).
• dim_product (name, brand, category, hierarchy).
• dim_channel, dim_promotion, and a junk dimension for flags like is_gift.

Extras: a separate fact_returns that shares the conformed dimensions. A periodic snapshot for inventory. An accumulating snapshot for order fulfilment (order, ship and delivery dates).

Use surrogate keys. Use an Unknown member (-1) for missing references. Add tests for uniqueness at the grain and for foreign key integrity.

What they are checking: Can you model from start to finish using the Kimball steps?

Kimball describes three main types, plus factless tables.

• Transaction fact: one row per event at the lowest grain (each order line, each click). This is the most common and most flexible.
• Periodic snapshot: one row per item per period (account balance at month end, daily inventory). It suits status measures that are not events.
• Accumulating snapshot: one row per process instance. It is updated as the item moves through stages (order placed, shipped, delivered), with several date columns. It is used to study pipelines and lead times.
• Factless fact: records that an event happened or a link exists, with no number to measure (student attended class, product on promotion).

Pick the type from the business process. Events lead to transaction facts. Regular status checks lead to snapshots. A workflow with milestones leads to accumulating snapshots.

What they are checking: Can you choose the right fact table type?

BigQuery is a serverless, columnar data warehouse. Storage (Capacitor format) and compute (Dremel engine) are separate. Google manages the resources.

Redshift is an MPP (massively parallel) columnar warehouse. Data is spread across the slices of the compute nodes. Two table settings change performance.

Both reduce the data scanned, but in different ways.

First check what the query does and how much data it touches.

Conformed dimensions are dimensions shared by many fact tables or data marts. They have the same keys, attribute names and meanings. Date, customer and product are typical.

Time Travel lets you query, clone or restore data as it was at an earlier time. It works inside a retention window (1 day by default, up to 90 days on higher editions).

First decide if it is a correction or a real change.

Use a bridge table between the fact (or dimension) and the dimension that has many values.

Structure:

• fact_visit has a diagnosis_group_key.
• bridge_diagnosis_group(diagnosis_group_key, diagnosis_key, weighting_factor).
• dim_diagnosis holds the diagnoses.

Late-arriving fact: the fact shows up after its date. Load it normally. But look up the dimension version that was valid on the event date, not the current one. Then reprocess the aggregates and snapshot tables for that date.

Load dimensions first, then facts, because facts need dimension keys.

Business process: completed trips. Grain: one row per trip.

Find the cost drivers first: query history, warehouse usage and storage.

Key controls:

• Login and role-based access control with least privilege. Use roles and groups, not grants to single users.
• Column-level security and masking policies for PII (most users see a masked email).
• Row-level security, so users see only their own region or tenant.
• Encryption at rest and in transit, with...

Plan in phases and keep the risk low.

Never drop the fact silently, because the totals would be wrong. Choose a documented strategy:

Both store results that are calculated in advance. So frequent queries do not need to scan the detailed facts again.

Data Vault is a modeling method for the integration layer of an enterprise warehouse. It is built for auditing, growth and easy change.

Core parts:

• Hubs: unique business keys.
• Links: relationships between hubs.
• Satellites: descriptive attributes with history. They are loaded with timestamps and the record source.

A factless fact table has foreign keys to dimensions but no numbers to measure. It records that an event happened, or that a relationship exists.

A semantic layer defines business metrics and dimensions once, in a central place (revenue, active customer, churn rate). Every BI tool or notebook then uses those definitions.

A centralised warehouse has one central team. The team loads, models and serves data for the whole company. A data mesh spreads ownership. Each business domain owns its data and publishes it as a product.

AQE changes the plan while the job runs, using real statistics collected after each shuffle stage. It is on by default since Spark 3.2 (spark.sql.adaptive.enabled).

Skew means some keys have far more rows than others. A few tasks then run much longer and may run out of memory, while the rest sit idle.

How to spot it: on the stage page in the Spark UI, the max task time and shuffle read are far above the median.

Treat driver out-of-memory (OOM) and executor OOM as two separate problems.

Driver: collect() or toPandas() on a large result, a huge broadcast, or metadata for too many partitions. Fix: write to storage, or use take or limit. Raise spark.driver.memory only as a last step.

Use the Spark UI before you change anything.

dropDuplicates removes exact or key-based duplicates. It does not let you choose which row stays. For 'latest per key', use a window function.

First add up the revenue. Then rank the products inside each category with a window.

Spark picks one of these physical strategies:

Cache a DataFrame when it is expensive to compute and used by several actions or loops. Examples are ML loops, or a table reused by several later branches.

Catalyst is the query optimizer of Spark SQL. It uses rules and also cost estimates. It turns a query into an efficient physical plan in four phases:

Salting spreads one hot key over several partitions. Add a random number to the key on the big side. Then copy the small side once for every salt value.

partitionBy("col") writes data into one directory for each column value, such as year=2025/month=10/. Queries that filter on that column skip whole directories (partition pruning).

Aim for partitions of about 100 to 200 MB each. Also aim for at least 2 to 4 tasks per available core. This uses the whole cluster without too much scheduling work.

Small files overload the file system and slow down later reads. Fix it when writing, and with compaction.

Spark tracks lineage, which is the chain of transformations that built each partition. If a partition or executor is lost, Spark recomputes only the lost partitions from the source.

Structured Streaming treats a stream as a table that keeps growing. It runs the same DataFrame API step by step in micro-batches.

Start from the workload and the node size of the cluster. Then tune using the Spark UI.

First find out why executors die. Then reduce the pressure.

A pandas UDF (vectorized UDF) gets and returns pandas Series or DataFrames in batches, using Apache Arrow. This avoids serializing row by row.

The mode decides where the driver runs.

Process only new or changed data, and make the job idempotent (running it twice gives the same result).

They are open table formats. They add a transaction log and metadata on top of Parquet files in object storage.

The difference is usually the environment, the data size, or serialization.

Use the JDBC writer, with the driver jar on the classpath.

Read it in chunks. Aggregate or write out each chunk. Never hold all the data at once.

import pandas as pd
totals = {}
for chunk in pd.read_csv("big.csv", chunksize=500_000, usecols=["region", "amount"],
                         dtype={"region": "category"}):
    part = chunk.groupby("region")["amount"].sum()
    for k, v in part.items():
        totals[k] = totals.get(k, 0) + v
print(totals)

Also do these things. Load only the columns you need (usecols). Choose smaller dtypes. Parse dates explicitly. Convert the file to Parquet once. If the data keeps growing, move to DuckDB, Polars (lazy mode), Dask or Spark.

What they are checking: Practical large-file handling

Use DataFrame.to_sql with a SQLAlchemy engine. Write in chunks.

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine("sqlite:///demo.db")
df = pd.DataFrame({"id": [1, 2], "amt": [10.5, 20.0]})
df.to_sql("sales", engine, if_exists="append", index=False, chunksize=10_000, method="multi")

Notes: if_exists can be fail, replace or append. to_sql only inserts rows. For upserts (update or insert), load into a staging table. Then run INSERT ... ON CONFLICT or MERGE in SQL. For large PostgreSQL loads, COPY through psycopg is much faster. Keep credentials in environment variables or a secrets manager.

What they are checking: Loading to a database

Validate at the edges: right after reading and right before writing. Check the schema, nulls, uniqueness, ranges and links between tables (referential integrity). Fail the job, or set bad rows aside.

import pandas as pd
df = pd.DataFrame({"id": [1, 2, 2], "age": [25, -3, 40]})
errors = []
if df["id"].duplicated().any():
    errors.append("duplicate ids")
bad = df[~df["age"].between(0, 120)]
if not bad.empty:
    errors.append(f"{len(bad)} rows with invalid age")
print(errors)

Useful libraries: Pandera, Great Expectations, Pydantic (row by row) and dbt tests. Good practice: write rejected rows to a quarantine table with a reason. Track row counts in and out. Alert when the failure rate crosses a limit. Do not drop data silently.

What they are checking: Reliability mindset

Idempotent means that running a job twice leaves the same final state as running it once.

Profile first (%timeit, cProfile). Then fix the biggest cost.

Fix the data first. Then change the design so a rerun does no harm.

Use smaller dtypes and load less data.

• Make numbers smaller: pd.to_numeric(s, downcast="integer").
• Turn strings with few distinct values into category.
• Use nullable or string[pyarrow] dtypes for text.
• Read only the columns you need with usecols.
• Delete intermediate DataFrames and read in chunks.

Load the batch into a staging table. Then merge it into the target table with one SQL statement, inside a transaction.

Vectorized operations run in compiled code on whole columns. They are the fastest. map and apply call a Python function for each element or row, so they are much slower.

Because of the GIL, threads do not run Python code in parallel. So use threads for I/O-bound work and processes for CPU-bound work.

Use a paginator. It follows the continuation tokens for you.

Loop until there is no next page. Reuse one session. Respect rate limits. Retry temporary errors with backoff (wait longer after each failure).

Make both sides smaller first. Then merge. Watch out for duplicate keys.

Parse to datetime. Make the time zone explicit. Convert only at the edges.

Use the logging module with levels. Catch specific exceptions. For each error, decide whether to retry, skip or fail.

Save the highest value of a column that only grows (such as updated_at or an id) from the last successful run. Then query only the rows beyond it. This saved value is called a watermark.

A decorator wraps a function to add behavior. A context manager handles setup and cleanup around a with block.

Write transformations as pure functions. They take a DataFrame and return a DataFrame. Then test them with small hand-made inputs using pytest.

Downloads wait on the network (I/O-bound), so use a thread pool.

The executor decides how tasks are run.

Celery starts tasks fast, but you must manage the workers all the time. Kubernetes gives clean isolation and autoscaling. But each task pays the pod start-up time. There is also CeleryKubernetes, which uses both. Recent versions can also use several executors at once.

What they are checking: Can you choose the right way to scale?

A DAG run covers one data interval. It starts only after that interval has ended.

Example: a @daily DAG has the data interval 2025-10-01 00:00 to 2025-10-02 00:00. It starts at 2025-10-02 00:00. Its logical date (ds) is 2025-10-01, the start of the interval. This is because the run processes the data of that interval.

• start_date: the start of the first interval. Nothing runs before it.
• schedule: a cron string, a preset (@daily) or a timetable.
• Templated values such as {{ data_interval_start }} and {{ ds }} help tasks fetch the right slice of data.

So a new DAG with today's start_date seems not to run. The first interval has not finished yet.

What they are checking: This is one of the most confusing Airflow ideas. Can you explain it clearly?

An idempotent task gives the same result if you run it once or many times for the same logical date. So retries and backfills are safe.

• Use {{ ds }} or the data interval as a parameter. Never use datetime.now().
• Overwrite the partition for that date, or use upsert/MERGE. Do not just append.
• Write to a temporary place, then swap it in one step (atomic).
• Delete and insert the date's data in one transaction.
• Avoid side effects that cannot be repeated, or protect them with a check.

DELETE FROM fact_sales WHERE load_date = '{{ ds }}';
INSERT INTO fact_sales SELECT * FROM stage WHERE load_date = '{{ ds }}';

What they are checking: Do you know the key design rule?

Work from the file toward the scheduler.

Check these in order:

Queued means the scheduler gave the task to the executor, but no worker has started it.

• Keep top-level DAG code light. No API calls, DB queries or heavy imports at parse time.
• Make tasks idempotent and atomic. Use logical dates and templates.
• Use small tasks with clear limits, not one giant function.
• Keep Airflow as the orchestrator. Push heavy work to Spark, warehouses or containers.
• Keep...

Use four idempotent tasks, keyed on the logical date. Airflow orchestrates them, and other systems do the work.

Use a backfill, or clear the past runs, with limits, on idempotent tasks.

A trigger rule decides when a task runs, based on the states of its upstream tasks. The default is all_success.

Dynamic task mapping (Airflow 2.3+) creates a changing number of task instances at runtime. The number depends on the output of an upstream task. You use expand.

The task process may have died without telling Airflow. Or it may be waiting on something forever.

Test in layers:

Parse time is the time spent running your DAG files. So cut the work done at module level.

• parallelism (core config): the most task instances that can run at once in the whole installation.
• max_active_tasks (per DAG): the most task instances that can run at once for one DAG, across its runs.
• max_active_runs (per DAG): the most DAG runs at once. It helps with backfills and stops runs from overlapping.
• max_active_tis_per_dag (per task): the limit for one task across runs.
• worker_concurrency (Celery): tasks for each worker.
• Pools: a limit for each resource.

A hook is a reusable way to talk to an outside system (connection handling, API calls). Examples are S3Hook and PostgresHook. An operator is a task template. It usually uses a hook to do its job.

Deferrable operators give a long wait to the triggerer. This is a background (async) process that runs many waiting triggers in one event loop. While the task is deferred, it frees its worker slot. So thousands of waits cost almost nothing.

The scheduler and workers are separate. The state lives in the metadata database. So a restart continues the work.

It stores DAGs, runs, task instances, XComs, connections, variables and references to logs. It is the single source of truth, so everything depends on it.

schedule_interval was the older argument. It took a cron string, a preset or a timedelta. In Airflow 2.4+, the single schedule argument replaces it. schedule accepts a cron expression, a preset like @daily, a timedelta, a list of datasets, or a Timetable object. Custom timetables handle rules that cron cannot write, such as 'every business day' or odd calendars.