Data Engineering interview questions: pipeline quality, observability & data validation

Schema validation confirms that incoming data conforms to a predefined schema: expected column names, data types, and constraints. This catches structurally incompatible records early so they don't propagate downstream and cause silent errors in dependent tables or analytics.

Data validation means running explicit checks on incoming records against expected rules (schema, null constraints, value ranges, uniqueness) before loading, so invalid or unexpected data is caught early instead of propagating to tables, dashboards, or models. A strong answer names both the purpose (catching bad data before it propagates) and concrete rule types.

Data validation checks verify that ingested data conforms to expected quality rules before it reaches downstream systems, so bad records are caught before they corrupt consumers. The typical rules are structural and content-level: column names and schema, data types, nullability, allowed value ranges, and uniqueness. Encrypting sensitive columns is a security concern, scheduling runs is orchestration, and aggregating raw events into dashboards is transformation, so none of those count as validation.

A uniqueness test asserts that every value in a column or set of columns is distinct. If any value appears more than once, the test fails — protecting primary-key integrity and preventing double-counted or ambiguous records in downstream models.

A not-null test asserts that every row in the target column has a non-null value, so it directly catches missing values. A uniqueness test checks for duplicate values, a schema test checks column types and structure, and a range test checks that values fall within bounds. None of those specifically guard against missing values.

The built-in expectation expect_column_values_to_not_be_null checks that no value in the specified column is null, which is exactly the constraint required for order_id. Uniqueness checks such as expect_column_values_to_be_unique detect duplicates rather than nulls, while expect_column_values_to_be_in_set and expect_column_values_to_match_regex validate membership and pattern conformance and do not address null detection.

dbt ships with four built-in generic tests: unique (all values in a column are distinct), not_null (no nulls), accepted_values (values belong to an allowed set), and relationships (referential integrity, where each value in the child column exists in the parent model's column). The unique test covers the distinctness constraint, and relationships covers the foreign-key constraint.

The buggy line is the if len(nulls) == 0: condition on line 5. The filter on line 4 correctly selects the rows where user_id is null, so nulls holds those rows. The condition len(nulls) == 0 is True only when no nulls were found, so the ValueError is raised exactly when the data is clean, and never when nulls are present. Changing the comparison to len(nulls) > 0 (or if not nulls.empty:) makes the error fire when nulls exist.

Freshness monitoring tracks how recent the data in a table is, typically by comparing the maximum load-time or event-time timestamp against the current time and against historical arrival patterns. If no new data lands within the expected window, the tool alerts that an upstream pipeline may be broken. The option about NULL values describes completeness checks, the option about repeated primary-key values describes uniqueness checks, and the option about value distributions describes distribution monitoring. Those are separate data-quality dimensions, so none of them is the purpose of freshness monitoring.

Great Expectations distinguishes its components precisely. An Expectation Suite is a named, reusable collection of individual Expectations, each such as a not-null check or a range check, that together define the expected properties of a dataset. It is the core artifact that captures data-quality rules, and it is what a validation run evaluates a batch of data against.

Schema validation verifies structural conformance: column names, data types, and nullability. It does not enforce business-rule constraints such as value ranges, referential integrity, uniqueness, or cross-column relationships. Those require additional column-level and multi-column data-quality checks layered on top of schema validation, so a passing schema check alone cannot guarantee contract compliance.

A data-quality assertion must encode each business rule as a testable condition on the dataset. The rule that order_id is a unique primary key becomes a uniqueness check, the rule restricting status to pending, shipped, or delivered becomes a membership check against that allowed set, and the rule that created_at is never null becomes a NOT NULL check. Only the assertion combining these three conditions covers all three stated rules. The other options each contradict one of the rules: allowing arbitrary status strings ignores the allowed set, allowing null created_at ignores the NOT NULL rule, and allowing duplicate order_id values breaks the primary key uniqueness.

A silent row shortfall is a volume anomaly: the pipeline finishes but delivers fewer rows than the source provided. A row count reconciliation check at the ingestion boundary, comparing received rows to an expected count obtained independently from the source, fails the run when the gap exceeds a threshold. Running it immediately after receipt and before transformation means a shortfall introduced during extraction is exposed before it reaches the warehouse. The baseline must not be derived from prior pipeline outputs, since a sustained drop would progressively lower a rolling average and mask itself; an independently sourced count avoids that feedback loop. Null checks, schema validation, and latency monitoring do not measure whether the expected number of rows arrived.

The call passes actual=95000, expected=100000, tolerance_pct=5. Because expected is non-zero, the early return is skipped. The bounds are computed as lower = 100000 - 5000.0 = 95000.0 and upper = 100000 + 5000.0 = 105000.0; these multiplications and divisions are exact in floating point. The first check, 95000 < 95000.0, is False because the comparison is strict, so the lower boundary is accepted. The second check, 95000 > 105000.0, is also False. Neither failure branch runs, so the function returns "PASS", which the print statement outputs. The boundary is inclusive because both checks use strict inequalities.

A type change from INTEGER to VARCHAR is a schema drift event, so it must be detected by comparing the incoming column names and types against a known contract at the ingestion boundary; the pipeline then fails before any rows are written. Row-count reconciliation, statistical outlier detection, and primary-key uniqueness checks all run after the data is already in the warehouse, and none of them inspects the declared column types, so none of them catches this change before loading.

The function loops over the four records. Record 1 has every required field non-None, so missing stays at 0; id 1 is not yet in seen_ids, so it is added. Record 2 has name set to None, so missing becomes 1; id 2 is new and is added. Record 3 has all fields present, so missing is unchanged; its id 1 is already in seen_ids, so duplicates becomes 1 and the set is not changed. Record 4 has email set to None, so missing becomes 2; id 3 is new and is added. The function returns a dict with missing equal to 2 and duplicates equal to 1, and print displays it as {'missing': 2, 'duplicates': 1}.

Expectation suites turn business rules and schema constraints into executable assertions, which is the core mechanism all three frameworks share. Storing validation results as metrics over time lets teams track quality across runs and spot regressions, which is standard observability practice. Running validation only in production is an anti-pattern because checks should shift left and run at multiple stages so bad data is caught before it reaches production or downstream consumers. Muting failures so runs never surface quality problems removes the feedback signal that observability exists to provide, so it is not a standard practice.

Schema validation and row-count reconciliation both pass, so the problem is not structural change or row-level loss; it is value-level corruption inside existing rows. Distribution checks on the event_value column detect shifts in null rate and percentiles, and a SUM(event_value) reconciliation between source and target detects aggregate value divergence that row counts cannot show. Freshness and file-size checks monitor pipeline health and storage, not content. Primary-key uniqueness targets duplicate rows, which matching row counts already rule out. Dashboard latency and Kafka consumer lag address performance and delivery throughput, and neither detects wrong values when row counts are correct.

The three well-established strategies are enforcing schema compatibility at the topic level with a schema registry (rejects malformed or incompatible records at ingestion), computing windowed column-level statistics such as null rate and distinct-count ratio against baselines to detect anomalies (a standard streaming observability pattern), and routing failing records to a dead-letter queue with volume-based alerting (a standard error-handling pattern in stream processors). Full-table reconciliation after every micro-batch is not practical because the source is a changelog rather than a static snapshot, the destination is continuously appended to, and full scans at micro-batch cadence are prohibitively expensive; incremental or watermark-based checks are the streaming-appropriate alternative. Silently dropping records with null keys without logging or metrics removes all visibility into data loss, so it is the opposite of observability.

There are 4 records. For the name field, only record 3 has None (1 null). For the email field, records 2 and 4 have None (2 nulls). The loop keeps the worst null count, so max_nulls = max(1, 2) = 2. Then completeness = (4 - 2) / 4 = 0.5. Since 0.5 is less than 0.95, the passed flag is False. The printed dict is {'passed': False, 'null_count': 2, 'completeness_ratio': 0.5}.

Consumer-lag monitoring only tracks ingestion throughput; it does not validate data content or detect silent drops between the processor and the lake. Schema validation at the Kafka boundary (typically via a schema registry) catches breaking schema changes and malformed CDC records before they enter processing, preventing downstream corruption from bad input. Output completeness reconciliation compares committed lake partitions against upstream Kafka offsets or stream-processor checkpoint metadata to detect silent data drops, duplicate writes, or stalled partitions, failure modes that lag metrics alone cannot surface. Together the two strategies cover content integrity and delivery integrity.

This is an open design question, so it stays a short answer graded on coverage rather than a single key. A strong answer covers all four areas named in the prompt: schema and contract validation at ingestion to stop vendor structure drift before it reaches the dimensional model; layered data quality checks (nulls, ranges, uniqueness, referential integrity) at each stage with quarantining of bad rows; freshness monitoring against vendor delivery SLAs so missing or late files are caught rather than silently serving stale data; and observability through test pass/fail metrics, volume anomaly detection, lineage, and stakeholder-facing dashboards and alerts. Answers that name concrete mechanisms for each area, rather than generic statements about quality, demonstrate the depth expected at this level.

Tracing all 20 rule evaluations: completeness checks value is not None (3 of 5 pass → 0.6), validity checks status membership (all 5 pass → 1.0), freshness uses id % 2 == 0 (2 of 5 pass → 0.4), uniqueness flags id == 3 (4 of 5 pass → 0.8). Three rules fail their thresholds: completeness (0.6 < 0.7), freshness (0.4 < 0.5), and uniqueness (0.8 < 0.9). Validity does NOT fail because the comparison is strict < and its score equals its threshold exactly at 1.0. The weighted score is (2×0.6 + 1×1.0 + 1×0.4 + 1×0.8) / 5 = 3.4 / 5 = 0.68, which rounds to 0.68. Since 0.68 < 0.8 AND there are failed rules, the classify function returns 'critical'.

The failure is semantic: the field name, type, nullability, positivity, and row count are all unchanged, so schema enforcement, NOT NULL checks, range checks, and row-count reconciliation all pass. Schema registry compatibility rules govern structural evolution, not the meaning of values, so they cannot see a unit change. Tightening a lower bound does not help, because dividing by 100 keeps values positive and well above any reasonable floor. Statistical distribution monitoring does catch it: every percentile, the mean, and the standard deviation shift by about 100x relative to the historical baseline, far beyond a threshold such as 3 standard deviations, so the drift is flagged immediately.