Demonstrate behavioral competencies

## How to use STAR (for Data Engineers) - Situation: 1–2 lines of context. Name systems, teams, constraints. - Task: Your responsibility/goal and success criteria (SLA, metric, date). - Action: Your specific decisions, trade-offs, and technical steps. Include tools and quantification. - Result: Measurable outcomes and what changed (latency, cost, failure rate, stakeholder impact). Add learnings. Tip: Speak in first person; quantify results; be explicit about risks and trade-offs. When possible, include these metrics: - Reliability: failure rate = bad_records / total_records, change failure rate, MTTR. - Performance: p95 latency, throughput (events/sec), data freshness (now − last_ingest_ts). - Cost: $/job = (compute_hours × $/hour) + storage + egress. - Quality: null ratio, schema drift incidents, duplication rate. --- ## 1) Handling team conflict (Data contracts and schema changes) - Situation: Analytics pipelines broke twice in a month after upstream schema changes from the app team, causing missed daily reports for Sales. - Task: Protect downstream users and reduce breakages while allowing the app team to iterate quickly. - Action: - Convened app, analytics, and platform owners. Mapped lineage for 8 critical tables and identified columns at risk. - Proposed a lightweight data contract: versioned schemas, 2-week deprecation policy, and backward-compatible changes by default. - Implemented schema registry + contract checks in CI. Added contract tests in Airflow pre-deploy. Built CDC-based backfill for deprecated fields. - Created a change request template: owner, rationale, rollout plan, impact, mitigation, and timeline. Set a weekly 15-min change review. - Result: - Schema-related breakages fell from 4/quarter to 0 in the next 2 quarters. - p95 DAG runtime variance dropped 35% due to fewer retries; on-call pages fell 60%. - App team velocity unaffected; average lead time for changes stayed at ~2 days. - Documented playbook adopted by 3 other data teams. - Likely deep follow-ups to prepare: - How did CI checks enforce contracts? Example rule and failure path. - Handling a non-backward-compatible change under urgent timelines. - Quantifying trade-off between developer friction and reliability. --- ## 2) Meeting a tight deadline (Cut scope, stabilize, then optimize) - Situation: Product marketing pulled forward a launch by 10 days. We needed a new daily ranking table feeding an API and dashboard. - Task: Deliver a minimally viable but reliable pipeline by launch day with <2h freshness and <0.5% duplication. - Action: - Ruthless scoping: shipped top 3 features (recency, CTR, recency-decayed score) and deferred 5 long-tail features. - Built an incremental dbt model with late-arriving data handling; orchestrated in Airflow with data quality checks (row count bounds, null thresholds, referential integrity). - Used a backfill window of 14 days; materialized a warm cache to keep API p95 < 200 ms. - Implemented rollback: blue/green tables (rankings_v1, rankings_v2) with a feature flag to switch consumers. - Result: - Met launch: data freshness 65–90 minutes; duplication rate 0.2%; API p95 180 ms. - Post-launch, added remaining features in 2 sprints; overall CTR improved 3.1%. - Incident-free first week; change failure rate 0% due to blue/green. - Likely deep follow-ups to prepare: - Exact quality checks and thresholds; sample SQL. - What you cut and why; quantified impact of deferrals. - Risk you didn’t mitigate and how you monitored it. --- ## 3) Leading an important project (Batch-to-streaming migration) - Situation: Daily batch pipeline produced engagement metrics with 24h lag, frustrating Ops and data consumers who needed near real-time. - Task: Lead end-to-end migration to a streaming architecture with <5-minute freshness and 99.9% delivery success. - Action: - Drafted RFC with two options: (A) Kafka + Flink + Iceberg; (B) Managed streaming + Lambda + Delta. Compared cost, operability, SLA. - Chose Kafka + Flink for low-latency joins; introduced schema registry and exactly-once semantics. - Defined SLAs: p95 end-to-end < 3 min, end-to-end loss rate < 0.1%, and backfill within 6 hours for 7-day window. - Built stateful deduplication (event_id) and watermarking for out-of-order events (allowed lateness: 10 minutes). - Established SLO-based alerting (freshness, consumer lag) and a runbook. Trained on-call and set rotation. - Result: - Freshness improved from 24h to 2–4 minutes p95; event loss reduced to 0.03%. - Reduced batch compute by 40%; net cost −18% after Kafka costs. - 5 downstream teams migrated within 2 months; support tickets dropped 50%. - Likely deep follow-ups to prepare: - How you ensured exactly-once; idempotent sinks and checkpointing details. - Out-of-order handling and its impact on correctness/latency. - Backfill strategy and reconciliation: how to detect and repair drift between batch and stream. --- ## 4) Influencing decisions with data (Build vs. buy for a feature store) - Situation: Infra team debated building an in-house feature store vs. adopting a managed solution. - Task: Provide a data-driven recommendation considering cost, time-to-value, reliability, and team skills. - Action: - Defined decision criteria and weights with stakeholders: cost (35%), reliability (25%), time-to-value (25%), maintainability (15%). - Built a TCO model: 12-month horizon, engineering hours, infra, egress, and support. $/month = compute + storage + egress + labor/12. - Ran a 3-week POC on two candidate managed solutions. Measured: - Online serving p95 latency (target < 50 ms), offline-online consistency error rate (<0.5%). - Backfill speed (rows/sec), point-in-time correctness (0% leakage on 3 labeled datasets). - Shadowed two production features for a week; compared AUC lift and consistency incidents. - Presented results and risks; recommended a managed solution with a 6-month exit strategy. - Result: - Project delivery time cut from ~6 months (build) to 6 weeks (buy); TCO −22% at 12 months, breakeven vs. build at ~18 months. - Reliability: consistency incidents dropped from ~3/month to 0 in pilot; p95 serving 38 ms. - Decision accepted; contractual SLOs negotiated (99.9% uptime, <1% monthly error budget). - Likely deep follow-ups to prepare: - Sensitivity analysis of the TCO model (e.g., labor rate ±20%, egress growth). - How you validated point-in-time correctness and prevented leakage. - Mitigation plan if vendor SLOs were missed; rollback plan. --- ## Quick formulas and checks to reference - Data freshness (minutes) = now() − max(ingest_timestamp) - Event loss rate = (produced − consumed) / produced - Duplicate rate = count(distinct id) / count(*) - Cost per run = (vCPU_hours × $/vCPU_hr) + (memory_GB_hours × $/GB_hr) + storage + egress - SLO conformance = 1 − (error_budget_burn / budget) --- ## Pitfalls to avoid - Vague results: always quantify impact (%, minutes, $). - Team-only language: clarify your unique contributions and decisions. - Overfitting to success: include 1–2 trade-offs or risks you accepted and why. - Confidential data: anonymize numbers if needed but keep orders of magnitude. --- ## Practice prompts (for deep follow-ups) - What specific dashboard/alert told you something was wrong? Show the metric. - If you had 2 more weeks, what would you improve and why? - How did you bring a skeptical stakeholder on board? What was their concern and what data persuaded them? - What did you automate to prevent recurrence? Show the test or policy you added. Use these examples as structures; swap in your own systems, metrics, and constraints. Keep each STAR story to ~90–120 seconds, and prepare a 3–5 minute deep-dive for each.