Data Engineer interview questions

A SELECT query is written in one order but evaluated in a different logical order.

• The written order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT.
• Logical evaluation starts with FROM and joins, then applies WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT.
• Because WHERE runs before SELECT and aggregation, a SELECT alias is normally unavailable in WHERE, and aggregate results belong in HAVING rather than WHERE.

WHERE and HAVING filter data at different stages of a grouped query.

• WHERE filters individual rows before grouping, such as keeping only rows where country = 'DE'.
• HAVING filters groups after aggregation, such as keeping groups where SUM(amount) > 1000.
• A condition that can be applied in WHERE should usually go there because reducing rows before aggregation lowers the amount of work.

The appropriate join depends on which unmatched rows the result must retain.

• INNER JOIN keeps only rows that have a match on both sides, which suits records that must exist in both tables.
• LEFT JOIN keeps every row from the left table and fills right-side columns with NULL when no match exists, which suits a complete customer list with or without orders.
• FULL JOIN keeps unmatched rows from both sides, which is useful for reconciling a source and a target.

A join multiplies rows when its key is not unique on one or both sides.

• Every matching pair produces an output row, so a one-to-many or many-to-many join can silently inflate counts and totals.
• Before the join, compare COUNT with COUNT(DISTINCT key) or run an equivalent uniqueness check on the expected unique side.
• After the join, compare row counts with the expected relationship and automate these uniqueness and count checks so the pipeline fails before publishing doubled data.

GROUP BY defines the result's granularity, while aggregate functions calculate a value for each group.

• Rows with the same values in the listed grouping columns become one result group.
• SUM, COUNT, AVG, MIN, and MAX calculate a separate result for each group.
• Each selected column must normally be either listed in GROUP BY or wrapped in an aggregate, so the grouping columns should match the requested report granularity.

COUNT variants and other aggregates treat NULL values differently.

• COUNT(*) counts all rows, while COUNT(column) counts only rows where that column is not NULL.
• SUM, AVG, MIN, and MAX ignore NULL values, so AVG uses only filled values and may describe a different population than expected.
• Use COALESCE(column, 0) only when a missing value truly means zero, and inspect the share of NULL values before aggregating.

COUNT(DISTINCT column) counts unique non-NULL values rather than rows.

• COUNT(DISTINCT customer_id) separates the number of unique customers from the number of orders.
• NULL is excluded, and exact distinct counting can be expensive on very large tables, so an approximate function may be suitable when an estimate is enough.
• After joins, row multiplication may leave the distinct count unchanged and hide a fan-out error, so also validate row counts and key relationships.

Subqueries and CTEs both create intermediate results, so the main choice is clarity and structure.

• A short subquery in WHERE or FROM works well for a single local task, such as comparing a value with an average.
• A CTE declared with WITH gives each step a name and makes a multi-step transformation readable from top to bottom.
• CTEs are common in dbt models and analytics SQL because named steps are easier to review and can be referenced again when the database supports that use.

A window function calculates across related rows without collapsing the original rows.

• Every input row remains in the result and receives an additional calculated value.
• PARTITION BY defines the related group, while ORDER BY inside OVER defines its sequence, as in ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at).
• GROUP BY returns one row per group, while window functions can show each row's rank, previous value, or cumulative value within its group.

A running total of daily revenue combines daily aggregation with a windowed sum.

• One solution is SELECT order_date, SUM(amount) AS daily_revenue, SUM(SUM(amount)) OVER (ORDER BY order_date) AS running_total FROM orders GROUP BY order_date ORDER BY order_date.
• The inner SUM calculates revenue per day, and the outer windowed SUM accumulates those daily values from the first date through the current date.
• Adding PARTITION BY region to the window restarts the running total for each region, provided region is also part of the daily grouping.

The usual deduplication pattern ranks rows by recency within each business key and keeps rank one.

• Build a CTE such as WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn FROM customers_raw).
• Select from that CTE with WHERE rn = 1, adding a stable tie-breaker to ORDER BY if two rows can share the same timestamp.
• Unlike DISTINCT, this method deduplicates by the chosen business key and explicitly controls which duplicate wins.

UNION removes duplicate result rows, while UNION ALL simply appends all rows.

• Removing duplicates requires extra work, so UNION is usually slower than UNION ALL.
• Pipelines should normally prefer UNION ALL when inputs cannot overlap or when duplicates are meaningful.
• If unwanted duplicates are possible, an explicit rule based on a business key is clearer and safer than UNION's silent full-row deduplication.

Conditional aggregation uses CASE WHEN inside an aggregate to calculate segmented metrics in one query.

• SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_revenue sums only paid amounts.
• COUNT(CASE WHEN channel = 'mobile' THEN 1 END) AS mobile_orders counts rows where the condition returns a non-NULL value.
• Several such expressions can calculate multiple report segments in one table scan instead of separate filtered queries.

NULL represents an unknown value and requires SQL's dedicated NULL operators and functions.

• Comparisons such as column = NULL and NULL = NULL evaluate to UNKNOWN rather than TRUE, so use IS NULL or IS NOT NULL.
• NOT IN can return no rows when its subquery contains NULL, so NOT EXISTS is often the safer anti-join pattern.
• COALESCE supplies an explicit fallback value, while NULLIF can turn a zero divisor into NULL and prevent a division-by-zero error.

Monthly event aggregation should group by a real month boundary that includes the year and the intended time zone.

• A typical query uses DATE_TRUNC('month', created_at) AS month, groups by that value, and orders by it.
• Grouping only by month number merges the same month across different years, while grouping by a formatted label can break chronological sorting.
• Convert timestamps to the business time zone before truncation when needed, because UTC and local time can place a boundary event in different months.

The latest record per entity is usually selected with ROW_NUMBER in a window.

• Calculate ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY event_time DESC) in a CTE or subquery and keep rn = 1 outside it.
• Add a stable tie-breaker column to ORDER BY so two events with the same timestamp still produce one deterministic winner.
• A join to MAX(event_time) is an alternative, but without extra tie handling it can return multiple rows for the same entity.

Common SQL string functions can normalize messy text before it is compared, joined, or published.

• TRIM removes surrounding whitespace, while LOWER and UPPER normalize letter case.
• REPLACE removes or substitutes unwanted characters, and SUBSTRING with POSITION or SPLIT_PART extracts parts of composite values.
• A value such as an email can be normalized once during ingestion with LOWER(TRIM(email)) instead of repeating the same cleanup in downstream queries.

A dataset should be checked against trusted references, transformation expectations, and representative records before publication.

• Compare totals with the source system or an established report, and track row counts at each step to locate unexpected losses or inflation.
• Test edge cases such as NULL keys, duplicates, and date boundaries.
• Trace two or three records from source to final output by hand, and investigate unusually good or bad numbers instead of assuming they are correct.

The first step with a slow query is to identify where the database spends time and resources rather than guess.

• Use EXPLAIN to inspect full table scans, index or partition use, join strategy, and the number of rows processed at each stage.
• Reduce data early with appropriate filters and select only required columns instead of SELECT *.
• Check whether join and filter columns have useful indexes in a row database or whether WHERE uses the partition column in a partitioned system.

DDL defines database structures, while DML works with the data stored in those structures.

• DDL includes CREATE, ALTER, and DROP for objects such as tables, schemas, and indexes.
• DML includes INSERT, UPDATE, DELETE, and MERGE, while SELECT reads data and is sometimes classified separately as DQL.
• Destructive DDL such as DROP or TRUNCATE needs extra care because rollback behavior varies by database and recovery may be difficult or impossible.