ETL engineer databases interview questions

As asked

An upstream team changes a JSON field from a string to an object without warning. Your ETL job starts failing before the finance dashboard refresh. What do you do?

Sample answer outline

Start by preserving raw data so the pipeline can be replayed after the parser is fixed. Triage whether the change is additive, compatible, or breaking, then decide whether to quarantine bad records, roll back the upstream producer, or ship a parser that supports both versions. Protect downstream tables with contracts and tests so finance does not receive silently coerced values. Communicate impact and expected refresh time to stakeholders rather than hiding behind the batch job. Weak answers focus only on patching the transform and ignore lineage, replay, and stakeholder trust.

Expect these follow-ups

• When would you fail closed versus quarantine records?
• How do you detect this class of change before a dashboard breaks?
• What contract would you ask the upstream team to adopt?

As asked

You need to implement a Slowly Changing Dimension Type 2 on a customer table in Snowflake. The source sends a full snapshot daily. Walk me through the SQL or dbt logic you would write, including how you detect changes and manage effective_from and effective_to dates.

Sample answer outline

Strong answers describe hashing non-key columns to detect row changes efficiently, using a MERGE statement or INSERT plus UPDATE to close open records before inserting new ones, and setting effective_to to a far-future sentinel date rather than NULL. Candidates should also mention how dbt snapshots automate this and what the check_cols and strategy configs do.

Expect these follow-ups

• How does your approach change if the source sends only deltas rather than a full snapshot?
• What do you store in effective_to when a record is still current?

As asked

Explain how partition pruning works in Snowflake's micro-partitions versus Redshift's distribution and sort keys. If a query on a 10-billion-row fact table is doing a full scan, what are the first things you check on each platform?

Sample answer outline

Snowflake uses automatic micro-partitioning with min/max metadata; pruning requires that the filter column has low overlap across partitions, which clustering keys improve. Redshift requires the SORTKEY to match the WHERE clause for zone map pruning and the DISTKEY to match join columns to avoid redistribution. Candidates should know how to read EXPLAIN plans and query system tables like SVV_TABLE_INFO on Redshift or QUERY_HISTORY on Snowflake.

Expect these follow-ups

• When would you add a clustering key to a Snowflake table and how do you decide which column to cluster on?

As asked

You need to compute each transaction's value as a percentage of that customer's total spend for the month. Show me how you write this with a window function versus with a subquery or CTE using GROUP BY, and explain which one runs faster in a columnar warehouse and why.

Sample answer outline

The window function approach (SUM(amount) OVER (PARTITION BY customer_id, month)) reads the fact table once, whereas the GROUP BY subquery reads it twice. In a columnar warehouse the window function is usually faster because it avoids the second scan and the join back to the original table. However, very large partition sizes can cause memory pressure with window functions. Candidates should also mention that the GROUP BY approach is more portable to older SQL dialects.

Expect these follow-ups

• How would you handle this if you also needed a running total within the month, not just the monthly total?

As asked

A dbt model computing a cumulative sum over a 2-year fact table using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is taking 45 minutes in Redshift. Walk me through how you diagnose this and what you change to make it faster.

Sample answer outline

First check the EXPLAIN plan for DS_DIST_ALL_NONE or DS_BCAST_INNER which indicate redistribution. The window function requires all rows for a partition to be on the same slice, so if the PARTITION BY column does not match the DISTKEY, Redshift must redistribute data. The fix is to align DISTKEY with the PARTITION BY column and ensure SORTKEY matches the ORDER BY column to avoid a sort step. Also consider pre-aggregating to a smaller granularity before the window function.

Expect these follow-ups

• Would adding more warehouse nodes solve this or just shift the bottleneck?

As asked

You run large nightly DELETEs on a Postgres source table as part of cleanup, and the next morning your extraction query runs 10x slower. Explain what is happening and how you fix it.

Sample answer outline

Large DELETEs create dead tuples that autovacuum may not have fully cleared before the extraction query runs. Dead tuples bloat the table, cause index bloat, and force sequential scans even on indexed columns. The fix is either to run VACUUM ANALYZE explicitly after the DELETE or to schedule the extraction after autovacuum has had time to complete. Candidates should also mention that TRUNCATE is faster than DELETE for full-table removal and does not leave dead tuples.

Expect these follow-ups

• How would you monitor autovacuum progress on this table to confirm it is the cause?