Data Engineer interview questions and answers

For an e-commerce company I would pull orders and customers from the PostgreSQL app database using change data capture, and marketing spend from ad platform APIs using a managed connector. Raw data lands in the warehouse, say Snowflake, in a raw schema without changes. Then dbt builds staging models that rename columns, cast types and deduplicate, followed by mart models such as fct_orders and dim_customers in a star schema. Airflow schedules the whole flow: extract, then dbt run, then dbt test. Tests check primary key uniqueness, not-null columns, accepted values and freshness. If a test fails, the dashboard refresh is blocked and the team gets a Slack alert. Finally, Power BI or Looker reads from the marts. I document models in dbt so analysts can see lineage and definitions.

OLTP stands for online transaction processing. It is the database behind an app, like PostgreSQL or MySQL, handling many small, fast operations: insert an order, update a balance, read one user. It is row-oriented, highly normalised to avoid duplicate data, and tuned for low latency and strong consistency. OLAP, online analytical processing, is for questions like "revenue by region per month for 3 years". These queries scan millions of rows but only a few columns, so warehouses such as BigQuery, Snowflake or Redshift use columnar storage, compression and massively parallel processing. OLAP models are often denormalised star schemas to make queries simpler. We usually do not run heavy analytics on the OLTP database, because it would slow down the app, so we copy data into the warehouse.

A star schema has one central fact table connected to several dimension tables, which looks like a star. The fact table stores events at a defined grain, for example one row per order line, with numeric measures like quantity and amount, plus foreign keys to dimensions. Dimension tables, such as customer, product, store and date, hold descriptive attributes used for filtering and grouping. Dimensions are usually denormalised, so product category and brand sit in the same table instead of separate lookup tables. It is popular because it is easy for analysts to understand, queries need only simple joins from fact to dimensions, and BI tools work well with it. The most important design step is choosing the grain of the fact table, because mixing grains causes double counting.

I start with basic schema tests: primary keys should be unique and not null, and foreign keys should exist in the parent table, for example every order's customer_id exists in customers. Then I add freshness checks, such as the latest event being less than two hours old, and volume checks that alert if today's row count is far outside the normal range. I add accepted value tests for columns like status, and range checks such as amount greater than zero. For critical tables I compare totals with the source system. In dbt these are simple tests, and tools like Great Expectations or Soda can do more. Most important, failures should alert the owner and, for critical models, stop downstream dashboards from refreshing with bad data instead of failing silently.

Cron can start a script at a time, but it does not know that the transform job should only run after the extract finished successfully. An orchestrator like Airflow, Dagster or Prefect defines workflows as DAGs with explicit dependencies, so tasks run in the right order and downstream tasks wait or skip when something fails. It gives retries with backoff, timeouts and alerts. It also understands logical dates, which makes backfills easy: you can rerun a month of history with the right parameters. Everything is visible in one UI with logs, run history and durations, which helps debugging and on-call. With cron, all of this would be scattered scripts and custom code. Orchestrators also integrate with warehouses, Spark and cloud services through ready-made operators.

A slowly changing dimension is a dimension whose attributes change now and then, like a customer address or a product category. The question is how to handle history. Type 1 simply overwrites the old value, which is fine for corrections such as a typo, but you lose the old value. Type 2 inserts a new row for each change with valid_from, valid_to and an is_current flag, and each version gets its own surrogate key. Facts are linked to the version that was valid when the event happened, so sales by old city stay correct. Type 3 adds a column such as previous_region, which gives limited before and after history without extra rows. I choose based on the business need: if reports must reflect the state at the time of the transaction, I use Type 2; otherwise Type 1 keeps things simple.

Parquet is a columnar format, so a query that needs three columns out of fifty reads only those three, which cuts I/O dramatically. Because each column holds similar values, encodings like dictionary and run-length plus compression such as Snappy or Zstd shrink the files a lot, often five to ten times smaller than CSV. Parquet also stores the schema and types, so there is no guessing whether a field is a number or text, and nested data is supported. Each row group keeps min and max statistics, so engines can skip blocks that cannot match a filter. CSV and JSON are row-based text: no reliable types, quoting problems and full scans. I still accept them at the landing layer because many systems export them, but I convert to Parquet, usually inside a table format like Delta or Iceberg, for anything people query.

Change data capture means capturing every change made to a source database, inserts, updates and deletes, and streaming those changes to other systems. The most reliable form is log-based CDC, where a tool like Debezium reads the database transaction log, such as the MySQL binlog or the PostgreSQL WAL. Compared with nightly full extracts or polling on updated_at, it puts little load on the source, gives near real-time data and captures hard deletes that polling would miss. Typically the change events go into Kafka topics keyed by primary key, and a downstream job applies them to a lake or warehouse table with a MERGE, using the log position or a sequence number to keep changes in order per key. Teams use it to keep analytics tables fresh, feed caches and search indexes, and replicate data without touching application code.

Kafka is a distributed log. A topic is a named stream of messages, such as orders, kept for a retention period on brokers. Each topic is split into partitions, which are the unit of parallelism and replication. Kafka only guarantees order within a partition, so producers set a key, like order_id, and all messages with that key land in the same partition and stay in order. Every message has an offset, its position in the partition. Consumers in a consumer group divide the partitions between them, so each partition is read by one member and the group processes each message once. The group commits offsets, so after a crash or deploy it resumes where it left off, and you can reset offsets to replay. A second consumer group, say for analytics, reads the same data independently with its own offsets.

dbt is the transformation layer in an ELT stack. Loaders like Fivetran or Airbyte land raw data in the warehouse, and dbt turns it into clean, modelled tables using SQL. Each model is a SELECT statement in a file, and dbt materialises it as a view, a table or an incremental table. Models call ref() for other models and source() for raw tables, so dbt knows the dependency graph, runs things in the right order and produces lineage and documentation. You add tests such as unique, not_null and relationships in YAML, plus source freshness checks, and they run with every build. Because it is just code in Git, you get pull requests, code review, CI that builds changed models in a separate schema, and separate dev and prod targets. It brings software engineering habits to analytics work.

First I find a reliable key, ideally an event ID created by the producer. If there is none, I build one from fields that together identify the event, like user_id, event_type and timestamp, and I push the source team to add a proper ID. In batch, I keep raw data as received, then in staging I deduplicate using ROW_NUMBER over the key ordered by ingestion time and keep row 1. When loading into the final table I use MERGE on the key, so reruns and retries do not add the same row again. In streaming, engines like Spark Structured Streaming or Flink support dropDuplicates with a watermark, so state is kept only for a limited time window. I also track the duplicate rate as a metric, because a sudden jump often means a bug upstream.

I start with what changed: a recent code commit, much more data, a smaller warehouse, or other heavy jobs running at the same time. Then I open the query profile in Snowflake or BigQuery to see which step takes the time and how many rows go in and out. Common causes are a join that explodes because a key is no longer unique, a filter that stopped pruning partitions because a function wraps the date column, or memory spilling to disk. I check the join keys for duplicates and fix the grain. I make sure filters use the partition or cluster column directly. If the model rebuilds years of history every run, I change it to an incremental model that only processes new or changed rows. After the fix I add a test or alert on run time, so a slowdown is caught early.

I first ask what real-time means for them and what action they will take with the data. Often "real-time" means updated every 15 minutes, which a micro-batch pipeline can do cheaply. If a human looks at it twice a day, hourly is fine. True streaming with seconds of latency makes sense when a system reacts automatically, like fraud blocking or live inventory. Streaming has real costs: always-on infrastructure, handling late and out-of-order events, state management, exactly-once concerns and harder debugging. I also check the source: can we get CDC or events, or only nightly exports? Then I propose options, for example a 10-minute incremental load now and a streaming design later if the business case is proven, with cost and effort for each. This keeps the solution matched to the real need.

I open the Spark UI and find which stage and tasks fail. If a few tasks process far more data than others, it is skew, and I fix it with AQE skew join handling or salting. If all tasks are big, partitions are too large, so I increase shuffle partitions or repartition so each partition is roughly 100 to 200 MB. I check for collect() or toPandas() on large data, which overload the driver. I check whether a broadcast join is broadcasting a table that is actually large. I reduce data early by filtering rows and selecting only needed columns before joins, and replace Python UDFs with built-in functions where possible. Caching large DataFrames that are not reused also wastes memory. Only after these fixes would I increase executor memory or memory overhead, because more memory often hides the real problem.

I need a reliable way to find changed rows. Best is change data capture from the database log, which includes inserts, updates and deletes. If that is not available, I use an updated_at column that the app always sets on change. Each run selects rows where updated_at is greater than the max already loaded, minus a lookback window of, say, two hours to catch transactions that committed late. Then I MERGE into the target on order_id, updating existing rows and inserting new ones, so overlap from the lookback is safe. For deletes I prefer soft deletes in the source; otherwise CDC is needed. Because incremental logic can drift, I schedule a weekly reconciliation that compares counts and sums with the source, and support a full refresh when the logic changes. In dbt this is an incremental model with unique_key and a merge strategy.

First I open the failed task in the Airflow UI and read its logs to find the real cause: a source outage, a schema change, bad data, credentials or resources. If the 8 a.m. deadline is at risk, I post early in the data channel saying which dashboards are affected and when I expect an update. Then I fix the cause, for example by adjusting a query for a renamed column or waiting for the source to recover, and clear the failed task so it and its downstream tasks rerun. Our tasks overwrite their date partition or MERGE on a key, so reruns are safe. Before saying it is fixed, I check row counts, freshness and key metrics against previous days. Afterwards I write a brief post-mortem and add whatever was missing, like a contract test, a source freshness alert or retries for transient errors.

I land the daily extract in staging, then compute a hash of the attributes I track, such as address and segment. I compare it with the current row in the dimension for each business key. Where the hash differs, a MERGE or two statements in one transaction close the old row by setting valid_to to the change time and is_current to false, then insert a new row with a new surrogate key, valid_from equal to the change time and valid_to set to a far-future date. Brand-new keys are inserted as current rows, and I agree with the business whether deletes close the row or set a deleted flag. I use half-open ranges so joins never match two versions, and I make the load rerunnable for the same date. Tests check exactly one current row per key and no overlapping ranges. In dbt, snapshots implement this pattern for me.

A managed warehouse is the fastest way to give analysts reliable SQL with little operational work: storage, tuning, security and concurrency are handled for you. I would pick it for a small team focused on BI. A lakehouse keeps data as Parquet with a table format like Iceberg or Delta on object storage, adding ACID transactions, time travel and schema evolution. Its strengths are cheap storage at large volume, one open copy that Spark, Trino, Flink and even warehouses can read, and a natural fit for machine learning and semi-structured data. The cost is more engineering for compaction, catalogs and access control. In practice in 2026 many teams use both: raw and large data in Iceberg tables in the lake, and the warehouse querying those tables or holding curated marts. I decide based on data volume, use cases, team skills and how much vendor lock-in matters.

I start by asking what changed: input size, a code deploy, Spark version, cluster size or upstream file layout. Then I open the Spark UI and find the stage that got slow. In that stage I look at task durations: if a few tasks run much longer than the median, it is skew on a join or group key, which I fix with AQE skew handling, salting or filtering hot keys. If I see large spill to disk, partitions are too big or memory is short, so I tune shuffle partitions or executor memory. I check the SQL plan with explain to see whether a join stopped broadcasting or a filter is no longer pushed down. I also check the input: thousands of tiny files suddenly appearing would slow file listing and scheduling, so compaction would help. I change one thing at a time and compare the runs.

The root problem is that the data team depends on internal tables nobody promised to keep stable. I would pick the most critical datasets and agree a data contract with each owning team: the fields, types, meaning, freshness and who owns it. Ideally producers publish a stable interface, such as outbox events with a schema in a schema registry or a dedicated view, rather than us reading their raw tables. Then I make the contract executable: a check in their CI fails the pull request if a contracted field is removed or changes type, and breaking changes ship as a new version with a deprecation window. On our side, pipelines validate schemas on ingest and fail fast with clear alerts instead of loading bad data. I also show product teams which dashboards and models broke, so they see the impact and treat data as a product.

With a small team, I would minimise what we must operate ourselves. Ingestion uses managed connectors like Fivetran or Airbyte Cloud for SaaS sources, and CDC for the main databases. Everything lands raw in one cloud warehouse such as Snowflake or BigQuery, or a lakehouse if we expect heavy ML work. Transformations live in dbt with three layers: staging for cleaning, intermediate for business logic, and marts for BI, all in Git with code review and CI that runs tests on changed models. A managed orchestrator, or dbt Cloud plus a simple Airflow, schedules runs. I add data tests, freshness checks and alerts from day one. For governance, role-based access, masking of PII columns, and a data catalog from dbt docs. I set budgets and warehouse auto-suspend to control cost. Streaming waits until a real use case appears.

At-most-once means a message is processed zero or one time: you commit the offset before processing, so a crash loses it. At-least-once commits after processing, so a crash causes a replay and possible duplicates. Exactly-once means each message affects the result once, even with failures. Inside Kafka, idempotent producers and transactions let a consume-transform-produce job commit output and offsets atomically. Engines like Flink and Spark Structured Streaming use checkpoints that store offsets and state together and replay from the last checkpoint. But end-to-end exactly-once also needs the sink to cooperate: either a transactional sink that commits with the checkpoint, like Flink two-phase commit, or an idempotent sink, such as upserting by a unique event ID. In practice, I design for at-least-once delivery plus idempotent writes, which gives effectively-once results and is simpler to reason about.

First, you need to know where personal data lives, so a catalog with PII tags and lineage is essential. Deletion requests go into a queue with the user ID and a deadline, and a job processes them across systems. In the warehouse, a DELETE or MERGE removes rows. In the lake, plain Parquet files are immutable, so table formats like Delta Lake or Iceberg help: they support row-level deletes, but old snapshots still hold the data until you vacuum or expire them, so that step is mandatory. A good design keeps PII in a few tables keyed by a pseudonymous ID, so most tables never store names or emails. Crypto-shredding goes further: encrypt each user's data with their own key and delete the key. Backups follow a fixed retention, after which deleted data ages out. Every request is logged and verified, and laws like GDPR or India's DPDP Act set the deadlines.

I start from how the table is queried: the filters and joins that appear in most queries and in the most expensive ones. Usually that is a date, so I partition by day or month. The partition key should have a moderate number of values; partitioning by user_id or by hour over many years creates millions of tiny partitions, which slows planning and creates small files. For high-cardinality filters like customer_id, I use clustering in BigQuery or Snowflake, Z-ordering or liquid clustering in Delta, or sort keys in Redshift, so the engine can skip data blocks within partitions. Things that go wrong: skewed partitions where one day is huge, queries that wrap the partition column in a function and lose pruning, and choosing a key nobody filters on. I check pruning in query profiles and revisit the design as query patterns change.

First I would fix the data: find the bug, correct it, backfill the two days, and tell stakeholders clearly what was wrong, which numbers were affected and when it was fixed. Then a blameless post-mortem to find the root cause, for example a source change that doubled refunds, and why our checks missed it. Prevention has several layers. Add tests on business logic, not just schemas, such as revenue matching the payment system within 0.5% each day. Add anomaly detection on key metrics and row counts, plus freshness SLAs. Make critical models a circuit breaker: if tests fail, the dashboard keeps showing yesterday's data with a warning instead of wrong numbers. Assign clear owners to critical tables, with alerts routed to on-call rather than a channel nobody reads. Finally, agree on a data contract with the source team so schema or meaning changes are announced.

Clients send events to a collector service that validates them against a schema from a schema registry and writes to Kafka, keyed by user or session so related events stay ordered, with enough partitions for peak load and growth. Each event carries an event_id and an event timestamp. A Flink or Spark streaming job reads the topic, drops duplicates by event_id within a time-bounded state, and computes windowed metrics on event time with a watermark that tolerates a few minutes of lateness. Those results go to a low-latency store such as ClickHouse or Druid for the live dashboard. A separate sink writes raw events into an Iceberg or Delta table partitioned by event date, with regular compaction to fix small files. Daily dbt or Spark models build sessions and funnels from that table and correct anything that arrived after the stream watermark. I monitor consumer lag, invalid event rate and freshness, and tune retention and storage tiers for cost.

I start with an inventory using query logs and lineage: which tables, jobs and reports exist and which are actually used. Usually a big share is dead and can be dropped instead of migrated. Then I migrate in phases by business domain, beginning with one that is valuable but not too risky. For each phase I load history, rebuild pipelines in the new platform, translating SQL dialect differences and taking the chance to fix bad patterns, and then run old and new in parallel. Automated reconciliation compares row counts, sums and key metrics per table and per day, and differences must be explained before cutover. I agree a cutover date with report owners, repoint dashboards, and keep the old system read-only for a rollback window before decommissioning it. From day one I set up role-based access, cost monitors and tagging, so the new platform does not become an expensive surprise.

I would not guess; I would measure. Using query history and billing data, I attribute spend to warehouses, service accounts, dbt models and dashboards, and usually a handful of jobs drive most of the cost. Quick wins come from waste: warehouses that never suspend, jobs running hourly when daily is enough, full-refresh models that could be incremental, and tables or dev copies nobody reads. Next I tune the heaviest queries: filter on partition and cluster columns so pruning works, stop SELECT * on wide tables, pre-aggregate dashboard sources, and fix exploding joins. I right-size compute, separate workloads so a big batch job does not require a huge shared warehouse, and set aggressive auto-suspend. For storage I review time travel and retention and move cold data to cheaper tiers. Finally I add budgets, alerts and per-team cost dashboards so the savings last.

First I make sure the transformation takes a date range as a parameter and overwrites whole partitions, so any chunk can be rerun safely. Rather than rewriting the live table in place, I build the new history into a shadow table, or a new version of the model, so dashboards keep working during the backfill. I run it in batches, for example a month at a time, with an Airflow backfill or a dedicated job, limiting concurrency so I do not overload source systems or blow the compute budget. Meanwhile the daily job writes to both tables, or I simply finish by reprocessing the final few days. Before switching, I compare old and new totals per month and explain the expected differences. Then I swap the tables atomically with a rename or view change, keep the old version for rollback, and tell analysts that history changed and why.

I would run Debezium against PostgreSQL logical replication. It takes a consistent initial snapshot and then streams WAL changes into Kafka topics keyed by primary key, so changes for one row stay in order. A Spark or Flink job reads micro-batches, keeps only the latest change per key within each batch using the LSN, and MERGEs into an Iceberg or Delta table: updates overwrite, inserts add, and delete events remove rows or set a soft-delete flag. Using the LSN in the merge condition makes replays idempotent and stops an older event from overwriting a newer one. Schema changes flow through a schema registry, and I allow additive changes automatically but alert on breaking ones. Frequent merges create many small files and delete files, so I schedule compaction and snapshot expiry. Finally I monitor replication slot lag, because a stuck slot fills the source disk, and I run periodic count and checksum reconciliation.