Diagnose data quality and pipeline performance issues
Company: Databricks
Role: Data Engineer
Category: Behavioral & Leadership
Difficulty: Medium
Interview Round: Technical Screen
## Scenario
You are interviewing for a **Data Solutions Architect** role. A customer is using a cloud data platform (e.g., Databricks on AWS/Azure/GCP) and reports:
- **Data quality issues** (incorrect/missing/duplicated records, inconsistent definitions)
- **Performance issues** (slow ETL/ELT pipelines, long query times, high compute cost)
They ask: “We’re struggling with data quality and performance—how would you approach this?”
## Tasks
1. **Discovery & scoping:** What questions do you ask to clarify the problem and constraints?
2. **Define success:** What *metrics* would you use for (a) data quality and (b) performance/cost? Include primary metrics and guardrails.
3. **Diagnosis plan:** Describe a step-by-step approach to identify root causes (data sources, pipeline stages, storage layer, compute, governance).
4. **Solution proposal:** Propose concrete technical and process changes to:
- Improve data quality (validation, monitoring, ownership, SLAs)
- Improve performance (storage layout, compute configuration, pipeline design)
5. **Concept check:** Explain the differences between a **data lake** and a **data warehouse**, and where a **lakehouse** fits.
6. **Cloud considerations:** What cloud concepts commonly matter in these engagements (e.g., security/IAM, networking, storage, encryption, cost)?
## Deliverable
Provide a structured plan you could present to the customer (bullets are fine), including short-term mitigations and longer-term architecture/process recommendations.
Quick Answer: This question evaluates understanding and competency in data engineering and data architecture, including diagnosing data quality problems, pipeline performance issues, metric definition, governance, and cloud platform considerations.
Solution
### 1) Discovery & scoping (what to ask first)
Treat this like incident triage + architecture review. Key is to narrow “quality” and “performance” into measurable symptoms.
**Business context**
- What decisions/products depend on this data? What is the business impact (revenue, compliance, customer trust)?
- Which datasets are critical (top 5 tables/feeds)? What is the expected freshness (hourly/daily)?
**Quality symptom details**
- What does “bad quality” mean here: duplicates, missing fields, wrong values, late-arriving data, inconsistent definitions?
- When did it start? Sudden regression vs chronic issue?
- Is there a known “gold standard” to compare against?
- Who owns each source system and each downstream dataset (RACI)?
**Pipeline & platform**
- Batch vs streaming? Any CDC? Incremental vs full refresh?
- Where is the data stored (object storage + Delta/Parquet, warehouse, external DB)?
- What are the largest tables (row counts, file counts, partition columns)?
- Current SLAs/SLOs for pipelines and dashboards.
**Performance symptoms**
- Is slowness in ingestion, transformation, or BI queries?
- What changed recently (new join, schema change, increased volume, cluster policy change)?
- What are the worst offenders (top jobs by runtime/cost; top queries by duration)?
**Constraints**
- Compliance/security (PII, HIPAA/GDPR), residency requirements.
- Cost constraints and uptime requirements.
- Team skills and operating model (who will maintain it?).
---
### 2) Define success metrics (quality + performance)
You want **one primary metric** per problem plus **diagnostics and guardrails**.
#### Data quality metrics (by dimension)
Common dimensions and example metrics:
- **Completeness:** % non-null for required fields; % records missing key attributes.
- **Validity:** % records passing domain checks (e.g., age ∈ [0,120]).
- **Uniqueness:** duplicate rate by business key.
- **Consistency:** cross-table consistency (e.g., order_total = sum(line_items)).
- **Timeliness:** lag between event time and availability in curated layer.
- **Accuracy (harder):** match rate to trusted reference; manual audit error rate.
**Primary metric example:** “% of critical tables meeting DQ SLA (all checks pass) per day.”
**Guardrails:** false positive rate of checks, volume anomaly detection (to avoid “passing” by ingesting nothing).
#### Performance / cost metrics
- **Pipeline latency:** end-to-end time from source to curated tables.
- **Job runtime distribution:** p50/p95 runtimes; failure/retry rate.
- **Query latency:** p95 dashboard/query time.
- **Throughput:** rows/sec processed.
- **Cost:** $/day, $/pipeline run, DBU-hours, cost per TB processed.
**Primary metric example:** “p95 end-to-end pipeline latency < X hours while keeping compute cost <$Y/day.”
---
### 3) Diagnosis plan (root cause workflow)
A pragmatic sequence:
1. **Reproduce and isolate**
- Identify 1–2 representative failing datasets and 1–2 slow jobs/queries.
- Confirm whether issues are tied to specific sources, partitions (dates), or downstream consumers.
2. **Map lineage and ownership**
- Document the pipeline stages (source → raw → cleaned → curated → marts).
- Identify owners per stage and establish incident channel + escalation path.
3. **Data profiling & anomaly detection**
- Profile distributions, null rates, duplicates, cardinalities.
- Compare recent period vs baseline (e.g., week-over-week) to detect drift or schema changes.
4. **Validate contracts at boundaries**
- Check source extract logic (late events, upstream dedup rules, timezone issues).
- Verify schema evolution behavior, nullability, type coercions.
5. **Performance deep dive by layer**
- **Storage layout:** file sizes (small files problem), partition strategy, skew.
- **Compute & execution:** cluster sizing, autoscaling, shuffle spill, skewed joins.
- **Query patterns:** missing predicates, non-selective partitions, excessive data scans.
6. **Operational review**
- Retries, backfills, idempotency, checkpointing (for streaming), SLA monitoring.
- CI/CD and testing: are changes deployed with regression coverage?
Deliverable from diagnosis: a ranked list of issues by impact/effort with evidence (metrics, logs, job run screenshots, sample bad records).
---
### 4) Solution proposal (quality + performance)
Split into **short-term mitigations** and **long-term architecture/process**.
#### A) Improve data quality
**Short-term**
- Implement critical checks on high-impact tables:
- Required field non-null
- Primary key uniqueness
- Referential integrity (where feasible)
- Volume and freshness checks
- Quarantine bad records (dead-letter table) instead of silently dropping.
- Create a clear “definition of done” for a dataset: schema, grain, SLAs.
**Long-term**
- **Medallion / layered modeling:**
- **Bronze (raw):** append-only, immutable, keep provenance.
- **Silver (cleaned):** standardize types, dedup, conform dimensions.
- **Gold (curated/marts):** business-ready aggregates/serving tables.
- **Data contracts & schema enforcement:** explicit schemas, controlled evolution.
- **DQ-as-code:** versioned rules, unit tests for transformations, CI checks.
- **Monitoring and alerting:** DQ dashboards, alerts routed to the owning team.
- **Governance:** dataset ownership, documentation, lineage, access controls.
Pitfall to call out: too-strict checks can block pipelines; use severity levels (warn vs fail) and staged rollout.
#### B) Improve performance (and cost)
**Storage & table optimization (common high ROI)**
- Fix **small files** (compaction) and ensure reasonable file sizes.
- Choose **partitioning** by common filter columns (often date) but avoid over-partitioning.
- Use data skipping / clustering where supported (e.g., clustering/Z-order-like approaches).
- Periodic maintenance (optimize/compaction, cleanup/vacuum where applicable).
**Pipeline design**
- Prefer **incremental processing** over full refresh (watermarks, CDC, MERGE patterns).
- Make jobs **idempotent** to support retries without duplicating data.
- Handle late data with watermarking + reprocessing window.
**Compute / execution**
- Right-size clusters; enable autoscaling where appropriate.
- Address skew (salting keys, broadcast joins for small dimensions, pre-aggregation).
- Cache strategically; avoid repeated scans.
**Cost controls**
- Separate dev/test/prod; enforce cluster policies.
- Use job clusters for batch workloads; scheduled shutdown.
- Track cost per pipeline and set budgets/alerts.
---
### 5) Data lake vs data warehouse vs lakehouse
**Data lake**
- Stores raw/semi-structured data (files) in cheap object storage.
- Flexible schema, good for large-scale storage and diverse data types.
- Historically weaker guarantees for ACID transactions and governance (depends on tech).
**Data warehouse**
- Curated, structured data optimized for SQL analytics.
- Strong governance, performance optimizations, and consistent schemas.
- Can be more expensive; less flexible for unstructured/ML workloads.
**Lakehouse**
- Aims to combine lake storage economics/flexibility with warehouse-like reliability/performance.
- Typically adds transactional tables, schema enforcement, and performance features on top of object storage.
When to recommend:
- If they need multi-modal workloads (BI + ML + streaming) with shared governance: lakehouse is often compelling.
- If they primarily need governed BI on curated data: warehouse patterns may be simplest.
---
### 6) Cloud considerations (frequent interview-ready topics)
- **IAM & least privilege:** roles, service principals, instance profiles; audit logs.
- **Networking:** VPC/VNet, private endpoints/peering, egress control.
- **Encryption:** at rest and in transit; KMS/key rotation; secrets management.
- **Storage:** object store semantics, lifecycle policies, tiering, replication.
- **Reliability:** multi-AZ, disaster recovery, backup/restore.
- **Cost:** tagging, budgets, monitoring, capacity planning.
- **Compliance:** PII handling, access reviews, data retention.
---
### Putting it together (what you’d present to the customer)
1. Align on affected datasets and SLAs; define quality + performance metrics.
2. Map lineage and owners; pick two representative failures and two slow workloads.
3. Run profiling + execution analysis; produce a prioritized issue list.
4. Implement quick fixes: critical DQ checks, compaction/partition fixes, incrementalization for biggest jobs.
5. Establish long-term operating model: layered architecture, DQ-as-code, monitoring, cost governance, security baseline.
This answer demonstrates customer-facing structure (clarify → measure → diagnose → fix → operationalize), which is what Solutions Architect screens typically look for.