65 Data Engineer interview questions with detailed answers, sorted from basic to advanced. Free to practice, with progress saved automatically.
Data engineers build and run the pipelines that move data from source systems into storage for analysis. This section covers the role compared with analysts and scientists, SQL for data work, ETL and ELT, warehouses and data modelling, Spark batch jobs, streaming, Airflow orchestration, data quality checks, OLTP versus OLAP, and lakehouses.
and 13 more topics.
Kafka Connect is a framework that ships with Kafka for moving data between Kafka and other systems (databases, Elasticsearch, S3…). Instead of writing a producer/consumer for each system, you declare a connector in a config file and submit it to Connect through its REST API.…
At-most-once (fire-and-forget): the producer sets acks=0 and the consumer commits the offset before processing. Messages can be lost but never duplicated. Fine for metrics and logs, where absolute accuracy is not the point. At-least-once: the producer sets acks=all with retries and the consumer commits after…
Amazon Redshift is AWS's data warehouse. It stores data by column and spreads it across nodes that run in parallel to serve analytical (OLAP) queries on very large tables. RDS is a row-oriented database for OLTP: small reads and writes per record, many concurrent transactions.…
Normalization: organize data to remove duplication — split into related tables joined by keys, each fact stored once (per the normal forms 1NF/2NF/3NF). - Pros: compact writes/updates, avoids contradictory data, saves space. - Cons: reads must JOIN many tables → more complex and potentially slower…
- View: a stored query saved under a name — it stores no data. Each time you query the view, the DB re-runs the underlying SQL on the current data → always fresh, no storage cost, but heavy if the query is complex and called…
Two data workload types, optimized for different goals: OLTP (Online Transaction Processing) — the transactional system running the app: - Many small, fast transactions: read/write a few rows (place an order, update a profile). - Needs ACID, low latency, high concurrency. - Normalized data, indexes…
A window function computes over a set of rows related to the current row but without collapsing rows — each original row is kept, with the computed value added. You recognize it by the OVER (...) clause. Versus GROUP BY: - GROUP BY collapses many…
Two warm-up problems that appear in almost every SQL round: Second-highest salary (must handle duplicate salaries and the missing case): Use DENSERANK (not ROWNUMBER/RANK) because tied salaries must share one rank with no gaps. Since several people tied at rank 2 still produce several rows,…
A SELECT is written as SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY, but is logically evaluated in nearly the reverse order: 1. FROM + JOIN — build the source row set. 2. WHERE — filter individual raw rows. 3.…
Use declarative range partitioning on the timestamp column, one partition per month (or per week under heavy write volume). The main win is not faster queries but data lifecycle: dropping old data with drop table requestlogs202601 is instant and creates no dead tuples, unlike DELETE-ing…
A Data Engineer builds and runs the paths data travels through: collecting data from source systems into one place, cleaning it, and organising it so others can use it. Data Analysts and Data Scientists are the people who use that data. Data Engineer Data Analyst…
Big data is data that ordinary tools (a single database, an Excel file) can no longer handle because it is too large, arrives too fast, or comes in too many shapes. It is usually described with the 5 Vs: - Volume — size: terabytes or…
Parquet stores data by column (columnar), while CSV and Avro store data by row. Analytical queries usually read a few columns across millions of rows, so columnar storage reads far less data. CSV Avro Parquet ------------ Layout Row-based, text Row-based, binary Column-based, binary Schema None,…
ETL is the three steps that move data from sources into an analytics store: Extract, Transform, Load. - Extract — read data from sources: application databases, APIs, CSV files from partners, logs. Usually only new or changed data since the previous run, to reduce load…
They differ in where the transformation happens: ETL transforms before loading into the warehouse, ELT loads raw data first and then transforms inside the warehouse with SQL. ETL ELT --------- Where it transforms A separate engine (Spark, Informatica, Python scripts) Inside the warehouse (BigQuery, Snowflake,…
A lakehouse is a data lake (Parquet files on S3/HDFS, cheap storage) with a table format layer such as Delta Lake, Apache Iceberg or Apache Hudi on top, giving it features that used to exist only in warehouses. An old data lake is just a…
Medallion is a way of splitting lakehouse data into three layers, each cleaner and closer to the business than the previous one. Layer Contents Example --------- Bronze Raw data, kept as in the source, plus load time JSON events from Kafka, a dump of the…
A surrogate key is a key the warehouse generates itself (usually an incrementing integer or a hash) with no business meaning. A natural key comes from the source system, such as customer code KH00123 or an email. Dimensions use surrogate keys because: - History tracking:…
The grain answers "what does one row in the fact table represent". For example: one row is one product line in an order, or one row is total revenue for one store on one day. The grain must be fixed before choosing dimensions and measures.…
An SCD is how you handle dimension attributes that change over time, for example a customer changing address or a product changing category. The core question: should old reports show the old value or the new one? Type 1 — overwrite: update the old row…
First define what a duplicate is: identical in every column, or the same business key (orderid) with different versions. The two cases are handled differently. Identical in every column: SELECT DISTINCT is enough. Same key, several versions: number the rows within each key group with…
A full load rereads the entire source table on every run. An incremental load fetches only rows new or changed since the previous run, based on a marker called the watermark (usually the highest updatedat already loaded). Full loads are simple and always correct, fine…
Spark follows a one driver coordinates, many executors do the work model. The driver holds the execution plan; executors are processes on worker nodes that actually read data and run tasks. - Driver — the process that runs main() and creates the SparkSession. It builds…
A transformation creates a new DataFrame from an existing one (filter, select, join, groupBy) and does not run immediately — Spark only records it in the plan. An action (count, collect, show, write) is what forces Spark to execute the whole chain before it. Why…
They differ in how many input partitions one output partition needs data from. - Narrow (filter, select, map, union): each output partition depends on a single input partition, so it is processed on the executor that already holds the data, with no network transfer. -…
Both do distributed processing on the map → group by key → reduce idea. The biggest difference is where intermediate results live. Hadoop MapReduce Spark --------- Intermediate results Written to HDFS after every job Kept in memory, written to disk only on shuffle or memory…
Batch processes an accumulated chunk of data on a schedule (hourly, nightly); stream processes each event, or each small micro-batch, continuously as data arrives. Batch Stream --------- Latency Minutes to hours Milliseconds to seconds Input Bounded (yesterday's data) Unbounded Complexity Low: easy to rerun and…
Both pull only the changes instead of copying the whole table each time. They differ in where the changes come from. Query-based (polling) — periodically runs a query like this: - Simple: it only needs read access and an indexed updatedat column. - Cannot see…
Airflow is a pipeline orchestration tool: you define in Python which steps run, in what order and on what schedule; the scheduler runs them on time and retries failures, and the UI shows which step broke. Airflow does not process heavy data itself — it…
Use a sensor — an operator with one job: check a condition periodically, move to success when it holds so downstream tasks run, and fail once timeout is exceeded. Common sensors: - S3KeySensor, GCSObjectExistenceSensor — wait for a partner to drop a file in a…
The data interval is the range of data a DAG run is responsible for: the daily run for Sep 24 covers data from 00:00 Sep 24 to 00:00 Sep 25. The logical date is the point in time that identifies the run. The two are…
Data quality checks are automated assertions about data that run as a pipeline step: if they pass the pipeline continues, if not it alerts or stops before wrong numbers reach a dashboard. The groups of checks, by the question each one answers: - Completeness —…
A data contract is a machine-checkable agreement between the data producer (usually the backend team that owns the database or events) and its consumers: which columns the schema has, their types and constraints, who owns it, and which changes require advance notice. The problem it…
Start from what changed, and only then move to tuning. Most of the time the cause is the data or the code, not the configuration. 1. Narrow it down: did the job slow down gradually (data growth) or suddenly from one day (new deploy, source…
An RDD (Resilient Distributed Dataset) is the low-level API: a distributed collection of objects whose columns Spark knows nothing about. A DataFrame is tabular data with a schema (column names and types), so Spark SQL can optimise it. A Dataset is a statically typed DataFrame,…
Both keep the computed result of a DataFrame so later actions can reuse it instead of recomputing from the source. cache() is simply persist() with the default storage level, while persist() lets you choose the storage level. - DataFrame: defaults to MEMORYANDDISKDESER, kept in RAM…
Both change the number of partitions of a DataFrame. repartition(n) always shuffles all the data to spread it evenly, and can increase or decrease the partition count. coalesce(n) can only decrease the count, by merging existing partitions without a shuffle. repartition(n) coalesce(n) --------- Shuffle Yes,…
pandas processes data in one machine's RAM and runs each statement immediately (eager). PySpark splits data into partitions, processes them in parallel across executors and runs lazily, executing only when an action is called. pandas PySpark --------- Scale Fits in one machine's RAM Hundreds of…
The principle is to never load the whole file into memory: read a piece, process it, write it out or fold it into a running result, then drop it. Memory use then depends on the size of one piece, not on the size of the…
For equality joins (equi-joins), Spark mainly chooses between the following, based on the estimated size of both sides: - Broadcast hash join: the small table is sent in full to every executor and each partition of the large table joins locally. The large table is…
The small files problem is when a table consists of a huge number of small files (a few KB to a few MB) instead of fewer files of 128MB–1GB. The total data is the same, but every read gets slower, because listing, opening and reading…
The trick is to subtract each date's row number from the date itself: within a run of consecutive dates, the date grows by 1 and ROWNUMBER() grows by 1, so the difference stays constant. When the run breaks, the difference jumps to a new value.…
They are three ways to organise a warehouse: Kimball builds bottom-up with dimensional models (star schemas) per business process, Inmon builds top-down a central 3NF-normalised warehouse, and Data Vault separates keys, relationships and attributes so new sources are easy to load and everything is auditable.…
A data catalog is where you look up what data exists: which tables, which columns, who owns them, descriptions, sensitivity level. Data lineage is the graph of where data comes from and where it goes: which tables and jobs produce gold.revenuedaily, and which dashboards read…
A pipeline has two things to verify: is the transformation code logically correct, and does the real data flowing through meet expectations. Unit tests run on small fake data before merging; data tests run on real data every time the pipeline runs. The two complement…
A data mart is a slice of warehouse data organised for one department or business area (marketing, finance, sales). The warehouse integrates data for the whole company; a data mart is like a shop counter that only displays what one team needs. Data warehouse Data…
The approach is to split the pipeline into steps with clear boundaries, record what data goes in and out of each step, then walk back from where the number looks wrong towards the source. The first step where the numbers diverge is where the bug…
They do not sit at the same layer. dbt is a framework for writing transformations in SQL: it computes nothing itself, it compiles models into SQL and pushes them down to the warehouse (BigQuery, Snowflake, Postgres, Databricks) to run. Spark is a distributed compute engine:…
