# Solution Alignment The improved prompt asks for a structured answer that states assumptions, covers edge cases, and explains trade-offs. The answer below preserves the original solution content while making the expected interview coverage explicit. ## Interview Framing - Start by restating the goal and the assumptions you need. - Work through the main approach in the same order as the prompt. - Call out trade-offs, edge cases, and validation steps before finalizing the recommendation. ## Detailed Answer ### 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. ## Checks and Follow-ups - Verify that the answer addresses every requested part of the prompt. - Identify the highest-risk assumption and explain how you would validate it. - Be ready to discuss an alternative approach and why you did not choose it first.