Data Engineer Top 100 Interview Questions

I would create one row per calendar day before applying a seven-row window. Seven observations are not necessarily seven days when dates are missing or repeated.

• Daily grain: aggregate transactions by the relevant business date and entity before computing the rolling statistic.
• Complete calendar: join to a continuous calendar and fill absent days with zero only when absence genuinely means no activity, rather than missing ingestion.
• Window: include the current day and six preceding days, partitioning by entity when several independent series are present.
• Boundary rule: decide whether the first six days should return partial averages or remain excluded until a complete seven-day history exists.

Use the business timezone consistently when deriving dates. A duration-based window and a calendar-day window can differ around daylight-saving transitions.

I would place CASE expressions inside aggregate functions so each metric evaluates its own condition over the same input population. This keeps the grouping grain consistent.

• Counting: sum one for matching rows and zero otherwise, or count an expression that returns NULL for nonmatches; avoid unintentionally counting both cases.
• Amounts: sum the required amount only for matching rows, with an explicit policy for missing amounts and incompatible currencies.
• Grouping: group by the reporting dimensions once, then calculate related metrics such as successful count, failed count, and successful value together.
• Interpretation: define whether categories overlap; a record can contribute to more than one conditional metric when the conditions are not mutually exclusive.

This approach avoids joining several summaries unnecessarily, but the input must already be free from unintended join multiplication.

I would deduplicate activity to one row per entity and day, then identify groups of consecutive calendar positions. Subtracting row position from day position produces a constant within each streak.

• Calendar basis: represent dates with a gap-free calendar day number, or use an equivalent date calculation supported by the SQL dialect.
• Deduplication: remove repeated activity on the same day before numbering rows, otherwise multiple events distort the streak calculation.
• Grouping: subtract the entity-specific ROW_NUMBER from the day number, then group by entity and that difference to find each island.
• Output: report the minimum day, maximum day, and count, applying the requested minimum streak length afterward.

Clarify whether consecutive means calendar days, business days, or scheduled activity days; each definition requires the corresponding calendar.

Totals inflate when the join creates more rows per measured entity than the aggregation assumes. The individual source tables can be correct while their combined grain is wrong.

1. Declare the intended output grain and measure the number of rows per join key on both sides before changing the query.
2. Inspect unmatched keys and many-to-many matches, tracing one affected entity to identify which measures are repeated.
3. Aggregate each independent detail table to the required common grain, or use an existence join when only a match is needed.
4. Recheck row counts and business totals against source-level controls, including keys with zero, one, and many matching records.

flowchart TD
A["Inspect join keys"]
B{"Both sides have multiple rows?"}
C["Aggregate to common grain"]
D["Check missing and duplicate keys"]
E["Join validated inputs"]
F["Reconcile totals"]
A --> B
B -->|yes| C
B -->|no| D
C --> E
D --> E
E --> F

Trade-offs:

• Pre-aggregation protects totals but removes detail that may need a separate analytical view.
• Adding DISTINCT after the join can hide symptoms and erase legitimate equal-valued records.

These constructs package query logic with different scopes and persistence. None is automatically faster simply because its syntax looks simpler.

When to use which:

• Use a CTE or subquery to organize one statement, selecting the form that makes dependencies and filtering easiest to understand and verify.
• Use a view when multiple consumers should share a stable query interface, while checking permissions and the underlying query cost.
• Use a materialized view when repeated expensive computation justifies storage and refresh work, and its freshness policy meets consumer needs.

CTE inlining or materialization varies by engine and query. Inspect the actual plan rather than assuming a CTE always executes once or always creates a temporary table.

An index provides an additional access path that can avoid scanning every table row. Whether it helps depends on the query, data distribution, and optimizer decisions.

• Access pattern: indexes are useful for selective filters, joins, and supported orderings; reading a large fraction of a table may still favor a scan.
• Composite design: choose column order around common equality, range, and ordering conditions, while checking the engine’s supported access strategies and actual plans.
• Coverage: an index containing required columns can reduce table lookups when the engine and row visibility rules allow it.
• Maintenance: additional indexes consume storage and add work to inserts, updates, and deletes, so evaluate benefits across the whole workload.

Stale statistics, incompatible expressions, and poor selectivity can prevent an apparently relevant index from improving performance.

I would compare the slow execution with its previous behavior and locate where work increased. Rewriting the query before identifying the bottleneck can obscure the cause.

1. Confirm the same parameters, data volume, concurrency, and execution environment; check whether the delay is waiting for locks or resources.
2. Inspect the plan for scan volume, join strategy, row estimates, actual rows, sorts, spills, and unexpectedly repeated operations.
3. Address the specific cause through corrected statistics, selective predicates, appropriate access paths, reduced columns, or corrected join cardinality.
4. Measure representative executions again and verify identical results, including edge cases; actual-execution diagnostics can run the statement and require care.

flowchart TD
A["Reproduce slow execution"]
B{"Waiting or executing?"}
C["Inspect blocking and capacity"]
D["Inspect operators and estimates"]
E["Apply targeted correction"]
F["Compare results and timing"]
A --> B
B -->|waiting| C
B -->|executing| D
C --> E
D --> E
E --> F

Trade-offs:

• An index can improve reads while increasing write overhead, so judge the workload rather than one isolated query.
• Extra memory may reduce spills but cannot repair a logically incorrect many-to-many join.

DELETE removes selected rows, TRUNCATE removes all rows through a table-level operation, and DROP removes the table object. Their exact transactional and operational behavior depends on the database.

When to use which:

• Use DELETE when only certain records should be removed or when the required row-level behavior is supported by that operation.
• Use TRUNCATE for an authorized complete reset of table contents after checking locks, foreign-key restrictions, identity behavior, and downstream dependencies.
• Use DROP when the object itself is no longer needed and its dependents, permissions, recovery options, and replacement requirements are understood.

Do not assume TRUNCATE can never roll back, always resets identifiers, or bypasses every trigger. Logging, rollback, and trigger semantics must be checked for the specific engine.

Isolation levels control which effects of concurrent transactions can become visible. Stronger isolation can prevent more anomalies but may introduce blocking, retries, or additional coordination.

When to use which:

• Use an appropriately weak level for operations whose correctness does not depend on a stable set of multiple reads, after checking the database’s actual behavior.
• Use stronger isolation or explicit coordination when a business invariant spans several reads and writes that concurrent sessions could invalidate.
• Design bounded retries for serialization failures and ensure external side effects are not duplicated when a transaction is retried.

A snapshot alone does not necessarily prevent write skew. Select isolation based on the invariant being protected rather than on the name of the level.

A deadlock occurs when transactions form a cycle of waits for resources held by one another. Long waiting alone is not sufficient to prove a deadlock.

1. Capture the deadlock report or wait graph and identify the statements, resources, transaction boundaries, and acquisition order involved.
2. Reduce transaction scope and ensure competing operations acquire shared resources in a consistent order whenever the workflow permits.
3. Improve access paths that cause unnecessarily broad locking, and keep slow network calls or user interaction outside open transactions.
4. Handle the database’s chosen deadlock victim by retrying the complete logical transaction safely, with bounded attempts and monitoring.

flowchart TD
A["Capture wait evidence"]
B{"Wait cycle present?"}
C["Analyze lock acquisition order"]
D["Investigate ordinary blocking"]
E["Shorten and reorder transactions"]
F["Retry safely and monitor"]
A --> B
B -->|yes| C
B -->|no| D
C --> E
E --> F

Trade-offs:

• Stronger locking may protect invariants but reduce concurrency, so retain only locks required for correctness.
• Retries help recover from occasional conflicts; frequent retries indicate an underlying workload or design problem.

A fact table records measurements or events at a declared grain. A dimension table supplies descriptive context used to filter, group, and interpret those facts.

When to use which:

• Use a fact table for business events or measurements whose meaning can be stated precisely, including event records that have no numeric measure.
• Use dimensions for reusable descriptions such as customer segments, product categories, or calendar attributes, with an explicit policy for historical changes.
• Connect facts to the correct dimension version and ensure the relationship does not multiply facts unexpectedly during ordinary analytical joins.

Large dimensions and small fact tables are both possible. Size alone does not determine the role of a table, and numeric identifiers are not automatically additive measures.

The grain states exactly what one fact row represents. It determines which dimensions and measurements can coexist without mixing incompatible levels of detail.

• Business statement: define the event or snapshot explicitly, such as one line item on one order, rather than describing grain only as a list of keys.
• Measure alignment: include values measured at that same level; an order-level shipping charge cannot be repeated on every line and summed without an allocation rule.
• Uniqueness: identify the key combination or event identifier expected to distinguish rows, then test it during ingestion and transformation.
• Aggregation: document which higher-level summaries can be derived and which detail would be lost if the data were aggregated prematurely.

Separate tables are often clearer when two measures naturally belong to different grains.

A star schema connects facts to relatively denormalized dimensions. A snowflake schema separates parts of dimension hierarchies into additional related tables.

When to use which:

• Use a star when analytical simplicity, discoverable dimensions, and predictable reporting joins are more valuable than avoiding every repeated descriptive value.
• Use a snowflake when shared hierarchies or independently managed attributes benefit from normalization and consumers can work correctly with the additional relationships.
• Evaluate performance on the actual engine because optimizers, compression, caching, and data volume can change the cost of each design.

Neither structure removes the need to declare grain, preserve historical meaning, or maintain consistent keys. A hybrid is reasonable when its relationships remain clear to consumers.

A natural key comes from business data, while a surrogate key is assigned to identify a stored record independently of its business attributes. Both can be useful in the same model.

When to use which:

• Retain natural keys for source traceability, deduplication, and matching updates to the business entity that produced them.
• Use surrogate keys when integrating overlapping source identifiers or distinguishing multiple historical versions of the same business entity.
• Enforce the intended uniqueness of source identifiers and version intervals separately; assigning a new number does not remove duplicate business records.

A composite business key may include source, tenant, and local identifier. Avoid assuming an integer is a surrogate or that a business key is permanently stable merely because it is currently unique.

Type 1 overwrites an attribute’s previous value. Type 2 preserves history by creating a new dimension version with an effective interval and its own record identity.

When to use which:

• Use type 1 for corrections or attributes where consumers deliberately want the latest value applied without preserving the previous description.
• Use type 2 when historical analysis depends on what was true at the time, such as a customer segment associated with an earlier transaction.
• Define the policy per attribute where needed, and distinguish a correction of a mistake from a genuine business change before updating history.

Type 2 needs reliable effective times, nonoverlapping intervals, and correct fact-to-version lookup. Merely adding an active flag does not establish accurate historical relationships.

I would compare incoming tracked attributes with the correct existing version and publish a consistent set of historical intervals. Replaying the same change must not create another version.

1. Identify the entity, effective business time, tracked attributes, and source ordering; deduplicate changes and distinguish corrections from genuine new versions.
2. For an ordinary new change, expire the previous version at the new effective time and insert a new surrogate-keyed version with a half-open interval.
3. Commit the interval update and insertion atomically where supported; otherwise publish a validated replacement snapshot through a controlled commit boundary.
4. Handle out-of-order changes by repairing the affected interval history and related lookups; test uniqueness, overlaps, gaps, and repeat processing.

flowchart TD
A["Read ordered entity change"]
B{"Tracked attributes changed?"}
C["Keep existing version"]
D["Build corrected intervals"]
E["Validate version history"]
F["Commit complete result"]
A --> B
B -->|no| C
B -->|yes| D
D --> E
E --> F

Trade-offs:

• Preserving every attribute change increases storage and lookup work, so track attributes that consumers actually need historically.
• Business-effective time improves historical meaning but requires a clear correction policy for changes received late.

These fact types represent different views of a business process. Select the grain according to whether consumers need individual events, regular states, or milestone progress.

When to use which:

• Use transaction facts for detailed activity such as order lines or inventory movements, preserving the events needed for flexible later analysis.
• Use periodic snapshots for balances or status at regular intervals, including inactive entities when their zero or unchanged state matters.
• Use accumulating snapshots for processes with recognizable stages, such as an application moving from submission through approval and completion.

An accumulating snapshot is convenient for cycle-time reporting but may not preserve every intermediate transition. Retain an event history separately when repeated stages or detailed auditing matter.

These categories describe the dimensions across which a measure can be summed meaningfully. Correct arithmetic alone does not guarantee a meaningful business result.

• Additive: a measure such as quantity sold can be summed across compatible products, locations, and periods when units and the fact grain are consistent.
• Semi-additive: a balance may be summed across accounts at the same instant but should not normally be summed across daily snapshots to obtain a closing balance.
• Non-additive: ratios, percentages, and many distinct counts cannot be summed across groups to produce the overall result.
• Correct aggregation: retain underlying components such as revenue and units, then recompute a weighted average or ratio from those components at the requested grain.

Document currency, units, time interpretation, and population alongside the measure so consumers know which aggregations are valid.

I would preserve the fact and track the unresolved relationship rather than silently discarding it. The eventual dimension lookup must use the fact’s business time when history matters.

1. Validate the source business key and determine whether the missing dimension is genuinely delayed, invalid, or absent because of an ingestion failure.
2. Apply the agreed policy: hold the fact temporarily or use an explicitly identified unknown or inferred member that keeps the missing relationship visible.
3. When dimension information arrives, resolve the correct historical version and update the relationship through a controlled, repeatable repair.
4. Measure unresolved counts and age, alert on overdue resolutions, and reconcile reports that may have changed after the repair.

flowchart TD
A["Receive fact"]
B{"Dimension version available?"}
C["Attach correct version"]
D["Hold or assign unresolved member"]
E["Receive missing dimension"]
F["Repair relationship and reconcile"]
A --> B
B -->|yes| C
B -->|no| D
D --> E
E --> F

Trade-offs:

• Holding facts preserves fully resolved reporting but delays visibility of real business activity.
• Publishing unresolved facts improves timeliness while requiring clear flags and reliable repair ownership.

A bridge table represents a many-to-many relationship explicitly, such as one account having several owners. It makes the relationship visible but does not automatically prevent double-counting.

• Relationship grain: store one row per valid association, including effective dates when membership changes over time.
• Allocation: use weights only when the business has an agreed attribution rule, and validate that relevant weights sum to the intended total.
• Query semantics: distinguish questions about membership from questions about allocated measures; counting an account for every owner differs from splitting its balance.
• Validation: test duplicate relationships, missing members, overlapping validity intervals, and joins that accidentally multiply the original fact population.

Use a semi-join when the question only asks whether an association exists. That can avoid expanding facts unnecessarily while preserving the intended population.