Data Engineer Interview Questions for AI Training Work

I design loads around upserts keyed on a natural or surrogate key rather than plain inserts, and I make each pipeline stage stateless with respect to its inputs so rerunning it with the same input produces the same output. Partitioning by a processing date and overwriting the whole partition on rerun is another reliable pattern, especially for batch pipelines.

A star schema keeps dimension tables denormalized, which trades storage and some data redundancy for simpler, faster queries. That's the right call when query performance and ease of use for analysts matter more than storage efficiency. A snowflake schema is worth the extra join complexity when dimension tables are large, change frequently, and normalization meaningfully reduces storage and update anomalies.

I treat the ingestion layer as schema-tolerant by landing raw data first, before any strict schema is enforced, and applying schema validation at a later transformation stage. This way a new or renamed field upstream doesn't break ingestion outright. I also version the schema explicitly so downstream consumers can detect and handle changes rather than silently breaking.

The deciding factor is how fresh the data needs to be for the downstream use case, not how technically interesting streaming is. If consumers only need daily or hourly updates, batch is simpler to build, debug, and operate. Streaming is worth the added complexity only when there's a real requirement for near-real-time data, like fraud detection or live dashboards.

I start with the query plan to find where time is actually spent, rather than guessing. Common fixes are adding or adjusting partitioning and clustering keys so the query scans less data, pre-aggregating if the same expensive computation runs repeatedly, and checking whether a full table scan is happening where an index or partition filter should have limited it.

I would add row-count and null-rate reconciliation checks between source and destination as a standard part of every pipeline, not just this one, since silent data loss is exactly the kind of failure that doesn't throw an error. Alerting on unexpected volume drops, rather than relying on someone eventually noticing a downstream report looks off, is the actual fix.

I'd run both pipelines against the same source data and compare outputs record by record before cutting traffic over, rather than trusting that the migration was correct based on code review alone. Keeping the legacy pipeline as the source of truth until the new one has proven consistent for a meaningful stretch reduces the risk of a bad cutover.

I would keep raw data in its region of origin to satisfy residency constraints and only move aggregated or anonymized results across regions where legally permitted. Where a true cross-region join is unavoidable, I'd push computation to the region with the strictest constraint rather than centralizing everything in one place and hoping compliance isn't an issue.

A stakeholder wanted a flat, wide table optimized for one specific report. I explained that it would make every other use case harder to support and proposed a normalized model with a view layer for that specific report instead, so we got the short-term need met without locking the warehouse into a structure that only worked for one team.

A pipeline was re-scanning an entire table on every run instead of only new partitions, which was invisible functionally but expensive at scale. I found it by reviewing the cloud billing breakdown by job rather than waiting for someone to flag it, then fixed the query to use partition pruning, which cut the job's cost by a large margin.