Data Engineer (Data Warehouse Focus)

How do you decide between a star schema and a more normalized model when designing a data warehouse?

I lean toward a star schema when the primary use case is reporting and analytics, since the denormalized structure keeps queries simpler and faster for end users. I only introduce more normalization where storage costs or update anomalies genuinely outweigh the query performance benefit.

What's your approach to building an ETL pipeline that needs to handle both historical backfills and ongoing incremental loads?

I design the pipeline so incremental loads and backfills share the same transformation logic, just with different source windows, rather than maintaining two separate code paths that can drift out of sync. I also make sure the incremental logic is idempotent so a rerun doesn't duplicate data.

How do you approach optimizing a query that's become slow as the underlying table has grown?

I start by checking the query plan to see where time is actually being spent, rather than guessing at the fix, and I look at whether partitioning or indexing aligned with the common filter columns would help before considering a full redesign of the table.

What's your process for validating that a new ETL pipeline is producing correct data before it goes live?

I reconcile totals and row counts against the source system for a representative sample period, and I check edge cases like nulls and duplicate keys specifically, rather than only spot checking a few rows that look fine. I keep the validation checks running as part of the pipeline going forward, not just as a one time gate.