Junior Data Engineer — Data engineer interview questions

What is the difference between structured, semi-structured and unstructured data?

Structured data has a fixed schema and lives in rows and columns — a database table, a CSV. Semi-structured carries its schema with it and varies row to row — JSON, XML, Avro. Unstructured has no schema at all: text, images, audio. The distinction decides where it can be stored and how it is queried, which is why a pipeline usually converts semi-structured input into something columnar before anyone analyses it.

What is the difference between GROUP BY and PARTITION BY?

GROUP BY collapses rows — you get one row per group and lose the detail. PARTITION BY, used with a window function, keeps every row and computes the aggregate alongside it. So if you want each order plus the customer's total, PARTITION BY gives you both in one pass; GROUP BY forces a join back to the detail.

What is a window function and how does it differ from an aggregate?

An aggregate returns one value per group; a window function returns a value for every row, computed over a window of related rows. ROW_NUMBER, RANK, LAG and a running SUM are the common ones. ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) numbers each customer's orders newest first, which is how you take the latest row per group without a self-join.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping, HAVING filters groups after. So a condition on a raw column belongs in WHERE and a condition on an aggregate belongs in HAVING. Putting a plain column filter in HAVING still works but makes the engine group rows it will then discard, which on a large table is real cost.

What is an index, and when does it not help?

A separate structure that lets the engine find rows without scanning the table. It does not help when the query returns most of the table, because a sequential scan is cheaper than jumping through the index; it does not help if the query's filter does not match the leading columns of a composite index; and it costs on every write, since the index must be maintained.

What is the difference between a Data Lake and a Data Warehouse?

A lake stores raw data in its original form and applies schema when reading; a warehouse stores modelled, cleaned data with a schema applied on write. The lake is cheaper and keeps everything, including things nobody has decided how to use. The warehouse is what analysts query. Most companies have both, with the warehouse fed from the lake.

What is the difference between a fact table and a dimension table?

A fact table holds the measurable events — a sale, a click — with numeric measures and foreign keys. Dimension tables hold the descriptive context those keys point at: customer, product, date. Facts grow constantly and are narrow; dimensions are wide and change slowly. Getting this backwards is the most common modelling mistake, and it shows up as queries nobody can write.