Data Engineer interview questions

I optimize this regression by profiling the execution plan before changing SQL.

• I compare the current profile with a known fast run and look for lost partition pruning, row explosion in a join, skew, or spilling caused by insufficient memory.
• I apply the fix indicated by the profile, such as filtering the raw partition column, pre-aggregating a large join input, correcting join order, or removing duplicate join keys.
• I also compare row counts and recent loads because a backfill or accidental duplication can explain the slowdown better than a query rewrite.

I choose partitioning and clustering from actual access patterns, then verify the result with scan metrics.

• I usually partition a large fact table by event date because it dominates filters and retention, allowing the engine to prune old data and remove periods cheaply.
• I choose clustering keys from the next most frequent filter and join columns in query history, such as tenant, customer, or product, rather than relying on intuition.
• I avoid excessive partitioning and clustering because tiny files, metadata overhead, and reclustering consume resources, and I compare bytes scanned on representative queries before and after the change.

When one worker becomes a straggler, I first suspect data skew on the join key.

• I count rows per key on both inputs to find hot values such as NULL, a default tenant, or one unusually large customer.
• I process NULL or otherwise irrelevant keys separately and broadcast the smaller table when it fits in memory, which can eliminate the shuffle.
• For unavoidable hot keys I use salting on both sides to split the work, while treating adaptive execution as helpful protection rather than a substitute for fixing the data shape.

Selecting every column defeats a major performance advantage of a columnar warehouse.

• A columnar engine reads only referenced columns, so select-star on a wide event table can turn a small logical request into terabytes scanned and substantial compute cost.
• It also weakens data contracts because downstream models silently inherit new columns and can fail far from the source after a rename.
• I require explicit column lists in pipeline models and enforce them with linting or dbt conventions, while allowing select-star only for temporary interactive exploration.

The likely cause is join fanout after a supposedly unique key became one-to-many.

• A duplicate dimension row makes each matching fact row appear multiple times, so downstream sums can double while still looking plausible.
• I add uniqueness tests for dimension join keys and fail the dbt build before consumers receive data when the expected cardinality is violated.
• I also declare model grain explicitly and compare input and output row counts whenever a transformation is expected to preserve that grain.

I choose between these options based on transformation complexity, refresh control, and governance needs.

• Engine materialized views fit simple, frequently queried aggregations over one table when automatic incremental refresh is supported and removes orchestration work.
• Pipeline-maintained summary tables fit multi-table joins, window functions, and business rules that need testing, versioning, lineage, and refreshes coordinated with upstream loads.
• As a rule, data used for business decisions goes through the tested pipeline, while materialized views remain an acceleration layer for well-understood hot queries.

I start by attributing the increase to specific workloads instead of applying platform-wide limits blindly.

• I aggregate query history by user, service account, and query fingerprint to find frequent full scans, overly frequent dbt rebuilds, forgotten development warehouses, and accidental cross joins.
• I then tune platform controls such as auto-suspend, warehouse sizing by workload, on-demand versus reserved BigQuery slots, and scanned-bytes budgets with alerts.
• I tag queries by pipeline and team so recurring costs have owners and future regressions can be detected quickly.

I preserve fact completeness by mapping missing dimension references to explicit fallback members.

• A NULL dimension key disappears from an inner join, so grouped metrics can lose fact rows and stop reconciling with source totals.
• Each dimension gets reserved surrogate rows for states such as unknown and not applicable, and the load maps NULL or unmatched natural keys to the appropriate row.
• This keeps joins complete and makes unmatched volumes measurable, while separating missing source data from failed matching because those cases need different upstream fixes.

I deduplicate once in staging, as close to ingestion as practical.

• I define duplicates with the source owner, then use row_number over the business key ordered by source timestamp and a deterministic tiebreaker to retain the newest record, using QUALIFY where supported for a clear implementation.
• The processing window covers the source's real redelivery horizon because late duplicates can make a previously clean partition incorrect again.
• I record the duplicate rate for every load and alert on spikes, which often reveal an upstream retry storm before other symptoms appear.

Surrogate keys isolate warehouse identity from unstable source identifiers and support multiple historical versions.

• Natural keys may be reused, may differ across source systems, and cannot uniquely identify several SCD2 rows for the same business entity.
• In a modern ELT pipeline I often use a deterministic hash of the source system, source identifier, and, for SCD2 rows, the version's effective timestamp, which stays stable across parallel reruns and avoids sequence lookups.
• I document the key inputs and use a strong hash because hashes are harder to inspect manually and collisions, although unlikely, remain theoretically possible.

I choose the fact type according to whether the business question concerns events, states at intervals, or progress through milestones.

• Transaction facts store one row per event at the finest useful grain, such as an order, payment, or click, and are the default because other views can often be derived from them.
• Periodic snapshots store state at regular intervals, such as daily inventory or month-end balances, when historical levels are expensive or impossible to reconstruct from events.
• Accumulating snapshots keep one row per process and update milestone dates such as placed, paid, shipped, and delivered, making duration analysis easy but creating workloads that are awkward for append-oriented warehouses.

I keep the history but separate expensive historical access from common current-state queries.

• I identify attributes causing most versions, and move a volatile score or status into a mini-dimension or fact when it does not belong in the slowly changing core.
• I maintain a current-only view or table filtered by the active flag so everyday consumers join to one row per customer while full history remains available.
• For heavy point-in-time marts I materialize the effective version rather than recalculating validity-range joins in every query.

I promote frequently queried and semantically stable JSON fields to typed columns while retaining the variable long tail.

• Fields used in filters, joins, and metrics become typed staging columns so the engine can use statistics and pruning, and type errors are caught early.
• Rare or genuinely variable attributes remain semi-structured because flattening them would create hundreds of sparse columns and frequent schema migrations.
• I maintain an extraction contract listing promoted paths, data types, and null rules, and marts reference those columns rather than parsing raw JSON repeatedly.

I make timezone and calendar rules explicit data-model decisions rather than repeating them in report queries.

• I store timestamps in UTC and derive a business date through a declared reporting timezone during loading or staging, which prevents inconsistent midnight-boundary conversions.
• A maintained date dimension supplies fiscal periods, week boundaries, and holidays so models do not duplicate fragile calendar arithmetic.
• I define handling for 23-hour and 25-hour DST days, reject or repair local timestamps without offsets at ingestion, and model a separate local business date per market when one event belongs to different reporting dates.

I design the star schema around business processes and declared grains, not by translating all source tables directly.

• I identify measurable events such as order lines, payments, or resolved tickets and state the grain of each fact table explicitly.
• I gather descriptive context into denormalized customer, product, and date dimensions, reducing the deep joins required by the normalized OLTP schema.
• Facts reference dimensions through surrogate keys and contain additive or clearly documented semi-additive measures.
• I deliver one business process end to end first, then extend shared conformed dimensions as additional processes are modeled.

Conformed dimensions give different marts one shared identity and vocabulary for core entities.

• Sales and support facts use the same customer and date keys, so cross-mart measures agree on which entity and period they describe.
• Separate team-owned copies quickly diverge in deduplication, freshness, and attribute definitions, producing incompatible counts and unreliable cross-domain joins.
• I treat core dimensions as owned, versioned data products with one build path, and marts reference them instead of copying their logic.

I model this relationship with an order-promotion bridge table and explicit allocation semantics.

• The bridge contains one row for each promotion applied to an order, plus an allocation factor when a measure such as discount must be split.
• Joining facts through the bridge multiplies order rows, so unweighted revenue by promotion will overstate totals unless the metric is explicitly non-additive across promotions.
• I document the fanout and provide a pre-allocated mart for common promotion analysis so consumers do not repeatedly implement the risky join themselves.

The layers separate source mechanics, shared business meaning, and consumer-specific presentation.

• Staging standardizes names and types and handles source-level deduplication, usually with one model per source table.
• Core applies business rules once to create conformed entities and facts, while marts shape those trusted results for particular consumers.
• This structure contains source schema changes, prevents business logic from being copied into dashboards, and lets debugging proceed through small understandable steps instead of one large transformation.

A wide table wins as a serving artifact for a defined consumer, but not as the authoritative model.

• At a clear grain it gives BI users one pre-joined dataset, prevents join fanout mistakes, and performs well in columnar engines.
• As a system of record it is expensive to rebuild, fixes history semantics at build time, and encourages divergent copies for teams with slightly different needs.
• I keep shared semantics in a star-schema core and generate replaceable wide tables from it for specific tools or teams.

Delete handling must be part of the ingestion contract because timestamp-only incrementals cannot observe missing rows.

• Log-based CDC is the preferred option because it emits delete events that the warehouse can apply as audited soft deletes or physical removals.
• Without CDC, I periodically extract all source primary keys and anti-join them to warehouse keys, or negotiate soft-delete markers in the source, accepting the cost and schedule-based freshness.
• I explicitly document that updated_at extraction alone is insufficient because a deleted record has no later timestamp to enter the delta.