9 Data Engineering Data Quality Interview Questions and Answers

Short answer: The classic six dimensions are completeness, accuracy, consistency, timeliness, uniqueness, and validity. Each maps to a concrete test rather than a vague "the data is good": absence of NULLs where they shouldn't exist, conformance to business rules, no duplicates on the key, and values within an allowed range.

In depth:

1. Completeness — no gaps in required fields, all expected rows/partitions arrived.
2. Accuracy — values match reality and business logic (an order total is never negative).
3. Consistency — the same value agrees across marts and systems.
4. Timeliness — data lands within the freshness SLA.
5. Uniqueness — no duplicates on the natural or surrogate key.
6. Validity — values conform to schema, types, and allowed range.

⚠️ Common mistake: saying "we check data quality" without tying it to dimensions and concrete assertions — the interviewer wants you to name a dimension and immediately give the check that guards it.

Short answer: Schema/constraint checks catch structural and type violations at the database level; dbt tests (not_null, unique, relationships, accepted_values) declaratively assert business invariants right inside the transformations; Great Expectations offers a richer set of assertions and profiling where data arrives outside dbt (raw files, ingestion, ML features).

In depth:

1. Schema/constraint tests — NOT NULL, PK/FK, CHECK at the warehouse level. Cheap, but cover structure only.
2. dbt tests — live next to the model in schema.yml, run in CI and on a schedule, great for cross-mart integrity (relationships).
3. Great Expectations / Soda — expectation suites, distribution profiling, validation at ingestion into the lake before transforms.

# schema.yml — dbt tests next to the model
models:
  - name: orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - relationships:
              to: ref('customers')
              field: customer_id
      - name: status
        tests:
          - accepted_values:
              values: ['new', 'paid', 'shipped', 'cancelled']

⚠️ Common mistake: testing only the final marts and putting no checks at ingestion — then garbage from the source rides through the entire pipeline and fails far from its cause.

Short answer: Data observability is continuous monitoring of four pillars: freshness, volume, schema, and distribution. Tests verify known invariants, while observability catches the unknown: anomalies, drift, and silent breakages that violate no explicit rule yet still mean something is wrong with the data.

In depth:

1. Freshness — when the table was last updated; lag against SLA is the first signal.
2. Volume — row/partition count against the historical baseline; a sharp drop = under-load.
3. Schema — columns appearing/disappearing/changing type upstream.
4. Distribution — NULL rate, cardinality, mean/quantiles; their drift betrays a logical breakage.

              data observability
  ┌───────────┬──────────┬─────────┬──────────────┐
  │ freshness │  volume  │  schema │ distribution │
  └─────┬─────┴────┬─────┴────┬────┴──────┬───────┘
        ▼          ▼          ▼           ▼
     lag vs   under-load/  column      NULL rate
     SLA      row spike    added/      up, cardinality
                           dropped     drift
        └──────────┴── alert BEFORE the ──┴──► stakeholder
                       business notices

⚠️ Common mistake: relying on explicit tests only and learning about a problem from an analyst's email "why are yesterday's numbers down" — without distribution and freshness monitoring, the business finds stale data first.

Short answer: A task must be idempotent — rerunning it for the same period yields the same result with no duplicates (delete+insert or MERGE on the partition key, not a blind append). Writes are atomic: you write to staging and only then swap it in with a single move (rename/partition swap), so readers never see half-written data.

In depth:

1. Idempotency — a rerun creates no duplicates: overwrite the whole partition or MERGE on the key.
2. Retries — tasks must be safe to repeat; side effects (notifications, increments) live outside the retryable path.
3. Atomic write — write→stage→rename: readers see either the old or the new version, never half.
4. Orchestration — deterministic data windows (ds/execution_date), not now(), so backfills are reproducible.

  write               stage                 swap (atomic)
  ┌──────────┐        ┌──────────────┐      ┌──────────────┐
  │ compute  │  ───►  │ _tmp/dt=... │  ──►  │ rename → dt/ │
  └──────────┘        └──────────────┘      └──────┬───────┘
                                                    ▼
                     readers see the old OR the new partition,
                     never a partially written one

⚠️ Common mistake: writing straight into the target partition and failing midway — consumers read half-empty data; plus a blind INSERT on retry doubles the rows.

Short answer: Lineage is the dependency graph from source through transforms to marts and dashboards. It exists for impact analysis: when a source changes, you immediately see which downstream models and reports will break and whom to warn. A catalog adds descriptions, owners, and semantics on top.

In depth:

1. Upstream lineage — from a suspicious number back to the source: incident triage and root cause.
2. Downstream lineage — from source to consumers: who a schema change or backfill will affect.
3. Catalog — owners, descriptions, PII tags, table trust level.
4. Automation — dbt builds the graph from ref(); OpenLineage/Marquez capture job-level lineage.

  source.orders ─┐
                 ├─► stg_orders ─► fct_orders ─► dash_revenue
  source.users ──┘                     │
                                       └────────► ml_churn_features
  source.orders.status changes →
  impact: stg_orders, fct_orders, dash_revenue, ml_churn_features

⚠️ Common mistake: changing a source schema without checking downstream lineage — and learning about the broken dashboard from Finance rather than from impact analysis.

Short answer: An SLA is a promise to consumers (e.g. "the sales mart is ready by 8:00 for yesterday"), an SLO is the measurable internal target behind it (freshness < 3 hours on 99% of days). A freshness guarantee is expressed as a threshold on max(loaded_at); the alert fires when data is late or stale, before the business notices.

In depth:

1. SLA — a consumer agreement in business terms: by what time and with what completeness.
2. SLO — a numeric target (freshness, completeness, run success rate) with a measurement window.
3. Error budget — how many breaches are tolerable; once spent, priority shifts to reliability.
4. Freshness alert — check the threshold, not just "the job failed": data can be late even with a green pipeline.

-- freshness assertion: the dataset is stale if the last load is older than 3 hours
SELECT
  max(loaded_at)                      AS last_load,
  now() - max(loaded_at)              AS lag,
  (now() - max(loaded_at)) > interval '3 hours' AS is_stale  -- → alert
FROM analytics.fct_orders;

⚠️ Common mistake: alerting only on task failure. A job can finish "successfully" yet bring no fresh data — without a max(loaded_at) check the SLA is breached silently.

Short answer: You need a data contract between source and consumption plus compatibility rules: backward-compatible changes (adding a nullable column) are safe, breaking ones (dropping a column, changing a type) require versioning and coordination. When the contract is violated, the pipeline must fail loudly rather than silently drop data.

In depth:

1. Data contract — explicit schema, types, nullability, and owner; changes go through review.
2. Backward compatibility — new producer, old consumers: add nullable fields, don't drop or rename.
3. Forward compatibility — old producer, new consumers: ignore unknown fields.
4. Fail loudly — on an incompatible change, quarantine rows and alert, don't discard quietly.

⚠️ Common mistake: catching a schema change with try/except and silently dropping mismatched rows — nobody sees the loss until the numbers diverge. Failing loudly beats losing quietly.

Short answer: Alert on what needs a human to act: task failures, freshness SLA breaches, and row-count anomalies. Everything else goes to dashboards and logs. Every alert must be actionable, routed to an owner, and tuned with thresholds/deduplication — otherwise the team stops reacting to them.

In depth:

1. Actionable, not FYI — if nobody acts on an alert, it's noise, not an alert.
2. Thresholds and baselines — compare against history (± deviation), not a hard "< 1000 rows".
3. Severity and routing — critical pages on-call, the rest goes to a channel/digest.
4. Deduplication and grouping — one incident = one alert, not a hundred emails.

⚠️ Common mistake: alerting on every little thing. Alert fatigue sets in — the team mutes the channel and misses the real incident amid the noise.

Short answer: The engineer is responsible for PII being tagged, masked, or encrypted, access granted on least-privilege (RBAC/row/column-level), and data stored and deleted per a retention policy. For GDPR/deletion you need a mechanism to erase a specific subject from the lake and the warehouse.

In depth:

1. PII classification — tag sensitive columns (email, phone, personal data) in the catalog.
2. Masking/encryption — tokenization, hashing, dynamic masking; the analyst sees the masked value.
3. Access control — RBAC, column- and row-level security, access auditing.
4. Retention and deletion — partition TTL; right to be forgotten — erase the subject across all layers.

⚠️ Common mistake: carrying raw PII through every layer without masking and without a map of where it lives — then a GDPR deletion request is impossible to fulfill, and a leak is only a matter of time.