Explain ETL schema changes and ensure integrity
Company: Rbcroyalbank
Role: Data Engineer
Category: Behavioral & Leadership
Difficulty: easy
Interview Round: Technical Screen
## Behavioral / Data Engineering Discussion Prompts
You’re interviewing for a **Data Engineer** role. Answer the following prompts with concrete examples from your past projects (use the STAR framework where helpful):
1. **New skills from your Master’s program**
- What specific skills did you gain that made you better at building/operating data pipelines?
2. **Handling schema differences in ETL**
- In an ETL/ELT pipeline, how do you handle **schema differences** between source and target (e.g., missing/extra columns, type changes, renamed fields, nested JSON evolving over time)?
- When do you choose strict schema enforcement vs flexible evolution?
3. **Ensuring data integrity**
- How do you ensure **data integrity** end-to-end (accuracy, completeness, consistency, and timeliness) across ingestion → transformations → warehouse tables?
- What checks do you implement (and where), and how do you respond to failures?
4. **Pipeline difficulties**
- Describe one difficult pipeline problem you encountered (e.g., late-arriving data, duplicates, backfills, scaling, flaky upstreams, partitioning, idempotency, cost blowups, SLA misses).
- What was the root cause, what did you change, and what did you learn?
Quick Answer: This question evaluates proficiency in handling ETL schema evolution, maintaining end-to-end data integrity (accuracy, completeness, consistency, timeliness), pipeline reliability, and leadership in incident response, and is commonly asked to gauge experience managing schema drift, enforcing data quality, and owning production pipelines.
Solution
## How to answer (what interviewers are really testing)
They’re evaluating whether you can (1) build reliable pipelines, (2) reason about tradeoffs, (3) prevent incidents, and (4) communicate clearly with stakeholders.
Use **STAR** for each: **Situation → Task → Actions → Results**. Quantify outcomes (latency reduced, failure rate down, cost saved, data quality improved).
---
## 1) “What did your Master’s give you?” (strong DE-oriented answer)
**What to cover (pick 2–4):**
- **Data modeling**: dimensional modeling, normalization vs denormalization, slowly changing dimensions.
- **Distributed systems basics**: partitioning, shuffles, eventual consistency (if you used Spark/Beam).
- **ETL/ELT design**: incremental loads, CDC, idempotency, orchestration (Airflow/Dagster), backfills.
- **Data quality**: unit tests for transforms, Great Expectations/Deequ, anomaly detection for metrics.
- **Software engineering**: CI/CD, code review, observability, reproducible environments.
**Template**
- “Before: I could write SQL/Python. Now: I can design pipelines with SLAs, tests, and monitoring.”
- Mention one project that demonstrates production thinking.
---
## 2) Handling schema differences in ETL (a complete, practical framework)
### A. Classify the schema change
Common types and typical responses:
1. **Additive** (new nullable column) → usually safe; update mapping; ensure downstream doesn’t break.
2. **Breaking rename/remove** → add compatibility layer (alias old→new), version tables, coordinate release.
3. **Type change** (string→int, int→decimal) → explicit casting with quarantine for bad rows; backfill.
4. **Semantic change** (field meaning changes) → treat as new column/version; communicate widely.
5. **Nested/JSON evolution** → schema-on-read vs schema-on-write decision; keep raw and parsed layers.
### B. Use a layered architecture
- **Bronze / Raw**: store source payloads as-is (immutable), partitioned by ingestion time.
- **Silver / Cleaned**: parsed with controlled schema; apply validations.
- **Gold / Serving**: business-ready tables with stable contracts.
This prevents losing information when schemas evolve.
### C. Enforce a “data contract” where possible
- Define expected columns/types, nullability, uniqueness, and acceptable ranges.
- Options: schema registry (Kafka), protobuf/avro, dbt contracts, documented interfaces.
### D. Choose strict vs flexible based on blast radius
- **Strict (fail fast)** when the table powers revenue/reporting/ML features; breaking changes must be caught.
- **Flexible (best effort)** when exploratory or low-risk; route mismatches to a **quarantine table**.
### E. Implementation tactics interviewers like
- Maintain a **mapping layer** (source → canonical names).
- Use **versioned models** (e.g., `events_v1`, `events_v2`) when semantics change.
- Add automated **schema drift detection** and alerting.
- For incremental pipelines, ensure schema changes don’t break merges/upserts.
**Pitfall to call out:** silently dropping unknown columns or coercing types can hide data loss.
---
## 3) Ensuring data integrity (end-to-end checklist)
Frame data integrity as multiple dimensions:
### A. Correctness & consistency
- **Primary key uniqueness** checks (no duplicate IDs per partition/day).
- **Referential integrity** checks (FKs resolve; orphan records tracked).
- **Reconciliation**: count and sum checks between stages (source vs raw vs curated).
### B. Completeness
- Missing partitions detection (e.g., expected hourly partitions).
- Null rate thresholds on critical columns.
### C. Timeliness
- SLA monitoring: lag from source timestamp to warehouse availability.
- Late data handling: watermarking and reprocessing windows.
### D. Exactly-once / idempotency (practical version)
- Design loads so re-runs don’t duplicate:
- `MERGE` by natural key + event time
- de-dup with `ROW_NUMBER()`
- write-ahead markers/checkpoints
### E. Observability & incident response
- Data quality tests in CI (dbt tests, unit tests for transforms).
- Runtime checks + alerts (Airflow callbacks, metrics dashboards).
- **Runbooks**: what to do when checks fail (rollback, pause, backfill, stakeholder comms).
**A strong “result” statement:** “After adding dq checks + idempotent merges, we reduced duplicate records from X% to ~0 and cut on-call incidents by Y%.”
---
## 4) “Tell me about a difficult pipeline issue” (how to structure)
Pick a story with real engineering tradeoffs.
### Example topics that score well
- **Duplicate events** due to retries → fix with idempotent keys + de-dup logic.
- **Late arriving data** → implement watermark + reprocessing window and backfills.
- **Cost blowup** → partitioning/clustering + incremental models.
- **Schema drift breaking jobs** → contracts + drift detection + compatibility layer.
### What to emphasize
- Root cause analysis (logs/metrics, reproducing, isolating upstream vs downstream).
- Concrete changes (code + process): tests, monitoring, SLAs, stakeholder alignment.
- Prevention: “how we made sure it won’t happen again.”
---
## Final interviewer-ready summary (what you want to convey)
- You design pipelines with **contracts, idempotency, monitoring, and backfill strategy**.
- You treat schema changes as a **product/interface problem**, not just a coding annoyance.
- You can explain tradeoffs clearly and operate systems reliably.