Meta Staff Data Engineer (E6) Interview Prep

Overview

The Staff Data Engineer (E6) bar at Meta is materially higher than the senior bar. Candidates are evaluated not just on technical execution, but on their ability to lead through ambiguity, make defensible architectural trade-offs, and act as a technical force multiplier for those around them. SQL is treated as a velocity filter — expect up to five problems in 30 minutes in a plain-text editor with no auto-complete or execution environment.

This guide covers six modules that mirror the actual interview rounds:

SQL Proficiency at Exabyte Scale
Python Coding & ETL Logic
Architectural Leadership — Data Modeling
Distributed Systems Design
Product Sense & Metrics
Leadership & The "Ownership" Paradigm

Module 1: SQL Proficiency at Exabyte Scale

At E6 the SQL round is a velocity filter. You must solve up to five problems in 30 minutes in a plain-text editor without auto-complete or execution capabilities. Speed, correctness, and the ability to reason about performance while writing are equally weighted.

1.1 Advanced Analytical Patterns

Window Functions: Mastery of RANK, DENSE_RANK, ROW_NUMBER, LAG, and LEAD for sessionization, retention curves, and growth metrics.
CTEs: Use Common Table Expressions to decompose complex logic into named, readable steps — interviewers penalize monolithic, nested queries.

1.2 Scale Optimization Techniques

Partition Pruning: Always filter on the partition key (e.g. ds = '2024-01-01') before any JOIN or GROUP BY. Failing to do so forces a full-table scan across trillions of rows on Hive/Presto.
Salting: Append a random suffix (CONCAT(user_id, '_', FLOOR(RAND()*10))) to high-cardinality keys to redistribute "hot partitions" during an aggregation, then sum the partial aggregates in a second pass.
Broadcast Joins: When one side of a JOIN is a small dimension table (typically <1 GB), explicitly hint the engine to broadcast it to all worker nodes, eliminating the expensive wide shuffle of the large fact table.

1.3 Production Guardrails

Use COALESCE(col, default) defensively — NULL propagation silently corrupts aggregations.
Explicit CAST() prevents silent type coercions between BIGINT and VARCHAR that cause cross-join explosions.
State your EXPLAIN reasoning out loud: tell the interviewer where you expect a full-scan vs. an index/partition scan.

1.4 Worked Examples

Challenge 1 — Sessionization with LAG 🟡 Medium

Tables: page_events(user_id, event_ts, ds)

Task: Assign a session ID to each event. A new session starts when the gap since the previous event exceeds 30 minutes.

✅ Solution

-- Step 1: flag session starts
WITH lagged AS (
  SELECT
    user_id,
    event_ts,
    LAG(event_ts) OVER (
      PARTITION BY user_id
      ORDER BY event_ts
    ) AS prev_ts
  FROM page_events
  WHERE ds = '2024-01-01'          -- partition pruning
),
flagged AS (
  SELECT
    user_id,
    event_ts,
    CASE
      WHEN prev_ts IS NULL
        OR DATEDIFF('minute', prev_ts, event_ts) > 30
      THEN 1 ELSE 0
    END AS is_new_session
  FROM lagged
)
-- Step 2: cumulative sum produces a session counter per user
SELECT
  user_id,
  event_ts,
  SUM(is_new_session) OVER (
    PARTITION BY user_id
    ORDER BY event_ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS session_id
FROM flagged
ORDER BY user_id, event_ts;

Key insight: SUM(is_new_session) OVER (...) acts as a running counter — it increments only when a new session boundary is crossed, giving each session a stable integer ID.

Challenge 2 — Salted Aggregation for a Skewed Key 🔴 Hard

Context: events(creator_id, action, ds) where a handful of viral creators account for 90% of rows.

Task: Count actions per creator without hitting a hot-partition OOM error.

✅ Solution

-- Pass 1: add salt, partial aggregate
WITH salted AS (
  SELECT
    CONCAT(CAST(creator_id AS VARCHAR), '_',
           CAST(FLOOR(RAND() * 10) AS VARCHAR)) AS salted_key,
    creator_id,
    action,
    COUNT(*) AS partial_count
  FROM events
  WHERE ds = '2024-01-01'
  GROUP BY 1, 2, 3
)
-- Pass 2: strip salt, final aggregate
SELECT
  creator_id,
  action,
  SUM(partial_count) AS total_count
FROM salted
GROUP BY creator_id, action
ORDER BY total_count DESC;

Key insight: Salting spreads the hot creator across 10 reducers in Pass 1. Pass 2 re-aggregates the 10 partial sums — cheap because there are at most 10 × distinct_creators × distinct_actions rows.

Challenge 3 — Week-over-Week Retention with LEAD 🔴 Hard

Table: weekly_active(user_id, week_start, ds)

Task: For each week W, compute the fraction of W's active users who were also active in W+1 (the immediately following 7-day period).

✅ Solution

WITH next_week AS (
  SELECT
    user_id,
    week_start,
    LEAD(week_start) OVER (
      PARTITION BY user_id
      ORDER BY week_start
    ) AS next_active_week
  FROM weekly_active
  WHERE ds = '2024-01-01'
),
retention_flags AS (
  SELECT
    week_start,
    COUNT(DISTINCT user_id)                                  AS wau,
    COUNT(DISTINCT CASE
      WHEN DATEDIFF('day', week_start, next_active_week) = 7
      THEN user_id END)                                      AS retained_next_week
  FROM next_week
  GROUP BY week_start
)
SELECT
  week_start,
  wau,
  retained_next_week,
  ROUND(100.0 * retained_next_week / wau, 2) AS w1_retention_pct
FROM retention_flags
ORDER BY week_start;

Module 2: Python Coding & ETL Logic

Python evaluation focuses on practical productionalization — not academic algorithms. Interviewers want to see that you write code that would survive a code review in a real data pipeline.

2.1 High-Performance Data Structures

Dictionaries (hashmaps): Default to dict/collections.defaultdict for O(1) lookups; avoid repeated linear scans of lists.
collections.Counter: One-liner for frequency counts; supports arithmetic (+, -, &, |) between counters.
collections.defaultdict: Eliminates KeyError guards and is faster than dict.setdefault in tight loops.

2.2 Complex Transformation Techniques

Log Parsing: Use re.compile once outside the loop, then .match() / .search() per line. Name your capture groups ((?P<name>...)) to make downstream code self-documenting.
JSON Flattening: Use recursion with a prefix accumulator to produce column names like user.address.city. Guard against cycles with a max_depth parameter.
State Machine / Event Validation: Use an explicit state variable and a transitions dict ({current_state: valid_next_states}) to validate alternating event sequences (e.g. checkout → payment → confirmation).

2.3 Scalability Discussion

Proactively address what changes at 100× scale:

Single-machine loop → distributed map with Spark/Beam
In-memory dict → external KV store (Redis, DynamoDB) for state
Batch file read → streaming consumer (Kafka) for real-time updates
Always name the bottleneck: CPU, memory, I/O, or network

2.4 Worked Examples

Challenge 1 — Parse Malformed Server Logs 🟡 Medium

Input sample:

2024-01-15T08:32:01Z | user=12345 | action=view | item_id=abc99 | latency_ms=142
2024-01-15T08:32:05Z | user=12345 | action=purchase | item_id=abc99 | amount=29.99
2024-01-15T08:32:07Z | MALFORMED LINE NO PIPE
2024-01-15T08:32:10Z | user=67890 | action=view | item_id=xyz01 | latency_ms=88

Task: Parse into a list of dicts; skip and count malformed lines.

✅ Solution

import re
from typing import Iterator

LOG_PATTERN = re.compile(
    r"(?P<ts>\S+)\s+\|\s+user=(?P<user_id>\d+)\s+\|"
    r"\s+action=(?P<action>\w+)\s+\|(?P<rest>.*)"
)
KV_PATTERN = re.compile(r"(\w+)=(\S+)")


def parse_logs(lines: Iterator[str]) -> tuple[list[dict], int]:
    records, malformed = [], 0
    for line in lines:
        line = line.strip()
        if not line:
            continue
        m = LOG_PATTERN.match(line)
        if not m:
            malformed += 1
            continue
        record = {
            "ts": m.group("ts"),
            "user_id": int(m.group("user_id")),
            "action": m.group("action"),
        }
        # parse remaining key=value pairs
        record.update(
            {k: v for k, v in KV_PATTERN.findall(m.group("rest"))}
        )
        records.append(record)
    return records, malformed


# --- scalability note ---
# At 100x scale: stream lines from GCS/S3 via a generator (no full load),
# or distribute across Spark executors with sc.textFile().mapPartitions(parse_logs).

Challenge 2 — Recursive JSON Flattener 🟡 Medium

Task: Flatten nested JSON events into a single-level dict suitable for relational ingestion.

✅ Solution

from typing import Any


def flatten(obj: Any, prefix: str = "", sep: str = ".", max_depth: int = 10) -> dict:
    """
    Flatten a nested dict/list into {dotted.key: value} pairs.
    Lists are indexed: parent.0, parent.1, ...
    """
    if max_depth == 0:
        return {prefix: str(obj)}  # treat deep nodes as opaque strings

    items: dict = {}
    if isinstance(obj, dict):
        for k, v in obj.items():
            new_key = f"{prefix}{sep}{k}" if prefix else k
            items.update(flatten(v, new_key, sep, max_depth - 1))
    elif isinstance(obj, list):
        for i, v in enumerate(obj):
            new_key = f"{prefix}{sep}{i}" if prefix else str(i)
            items.update(flatten(v, new_key, sep, max_depth - 1))
    else:
        items[prefix] = obj
    return items


# Example
event = {
    "user": {"id": 123, "address": {"city": "NYC", "zip": "10001"}},
    "items": [{"sku": "A1", "qty": 2}, {"sku": "B3", "qty": 1}],
    "total": 49.99,
}
print(flatten(event))
# {
#   'user.id': 123, 'user.address.city': 'NYC', 'user.address.zip': '10001',
#   'items.0.sku': 'A1', 'items.0.qty': 2, 'items.1.sku': 'B3', 'items.1.qty': 1,
#   'total': 49.99
# }

Challenge 3 — Event Sequence State Validator 🔴 Hard

Task: Validate a stream of checkout events per user. Valid sequence: cart_add → checkout_start → payment_submitted → order_confirmed. Detect missing or out-of-order steps.

✅ Solution

from collections import defaultdict
from typing import NamedTuple

TRANSITIONS = {
    None:                 {"cart_add"},
    "cart_add":           {"cart_add", "checkout_start"},
    "checkout_start":     {"payment_submitted"},
    "payment_submitted":  {"order_confirmed"},
    "order_confirmed":    set(),  # terminal
}


class ValidationResult(NamedTuple):
    user_id: int
    invalid_transitions: list[tuple[str, str]]
    final_state: str | None


def validate_sequences(events: list[dict]) -> list[ValidationResult]:
    """events: sorted list of {user_id, action, ts}"""
    user_states: dict[int, str | None] = defaultdict(lambda: None)
    user_errors: dict[int, list] = defaultdict(list)

    for e in events:
        uid, action = e["user_id"], e["action"]
        current = user_states[uid]
        if action not in TRANSITIONS.get(current, set()):
            user_errors[uid].append((current, action))
        else:
            user_states[uid] = action

    all_users = set(user_states) | set(user_errors)
    return [
        ValidationResult(
            user_id=uid,
            invalid_transitions=user_errors[uid],
            final_state=user_states[uid],
        )
        for uid in sorted(all_users)
    ]


# --- scalability note ---
# At 100x scale: maintain state in Redis (HSET user:{id} state {action})
# so workers can be stateless and horizontally scaled.

Module 3: Architectural Leadership — Data Modeling

The modeling round is the defining round for Staff leveling. It assesses whether you can translate ambiguous business goals into durable technical systems that remain maintainable at Meta scale.

3.1 Dimensional Modeling Mastery

Grain Definition

Before touching schema, always state the grain explicitly: "One row represents one line item per order per day." Getting grain wrong invalidates every downstream metric.

Fact Table Specialization

Type	Use When	Example
Transactional	Discrete business events	Ad clicks, purchases, messages sent
Periodic Snapshot	State captured at a regular cadence	Daily account balances, weekly DAU
Accumulating Snapshot	Progress through a well-defined funnel	Loan application stages, onboarding steps

SCD Logic

SCD Type 1 (Overwrite): Simple, no history. Use when history is irrelevant (e.g. phone number correction).
SCD Type 2 (New Row): Preserves full history via effective_date / expiry_date and a is_current flag. Required when historical accuracy is a business requirement (e.g. a customer's geographic region at time of purchase).
Defending your choice: State the business question that requires history. If no business question requires historical accuracy for that attribute, Type 1 is simpler and cheaper.

3.2 Advanced Schema Design

Star Schema vs. One Big Table (OBT)

	Star Schema	OBT
Reads	Requires JOINs, slower for BI tools	Single scan, fastest for BI tools
Storage	Compact (dimensions stored once)	Denormalized — dimension columns duplicated
Maintenance	Update dimension once, propagated automatically	Must rewrite entire OBT on dimension changes
Best for	Complex, flexible queries; large cardinality dimensions	High-concurrency, fixed-query BI dashboards

Mini-Dimensions

When a small subset of a large dimension (e.g. dim_customer with 500M rows) changes frequently (e.g. loyalty tier, credit score band), extract those attributes into a mini-dimension keyed by a hash of their values. This prevents the main dimension from "exploding" with SCD Type 2 rows on every status change.

Bridge Tables

Resolve many-to-many relationships (one patient → multiple diagnoses; one ad → multiple audience segments) with a bridge table that has a group_key linking rows in the fact table to a set of dimension members. Always define a weighting factor column (e.g. 1/count_of_diagnoses) to prevent metric inflation when aggregating.

3.3 Schema Design Walkthrough

Example — Ad Delivery Data Model at Meta Scale

-- Grain: one ad impression per user per ad per hour (partitioned by ds + hour)
CREATE TABLE fact_ad_impressions (
  impression_id   BIGINT,
  user_key        BIGINT,           -- FK to dim_user
  ad_key          BIGINT,           -- FK to dim_ad
  placement_key   INT,              -- FK to dim_placement
  user_demo_key   INT,              -- FK to mini_dim_user_demographics
  impression_ts   BIGINT,           -- epoch ms
  is_click        BOOLEAN,
  revenue_usd     DECIMAL(12,6)
)
PARTITIONED BY (ds DATE, hour TINYINT)
STORED AS ORC TBLPROPERTIES ('orc.compress'='ZSTD');

-- SCD Type 2 dimension
CREATE TABLE dim_user (
  user_key        BIGINT,           -- surrogate key
  user_id         BIGINT,           -- natural key
  country_code    CHAR(2),
  age_band        VARCHAR(10),
  effective_date  DATE,
  expiry_date     DATE,             -- NULL means current
  is_current      BOOLEAN
);

-- Mini-dimension: rapidly changing demographics
CREATE TABLE mini_dim_user_demographics (
  user_demo_key   INT,
  income_band     VARCHAR(20),
  loyalty_tier    VARCHAR(10),
  valid_from      DATE,
  valid_to        DATE
);

Interviewer follow-up you should anticipate:

"What changes if we need to support cross-device attribution?" — Add a device_key and a bridge table mapping device_id → user_id resolved via identity graph.
"How do you handle late-arriving data?" — UPSERT (MERGE) on the surrogate key with a watermark column; re-partition the affected ds slices.

Module 4: Distributed Systems Design

At E6 the system design round tests throughput, reliability, and correctness — not request/response latency. You are designing pipelines that process petabytes per day, not APIs that serve millisecond responses.

4.1 Architecture Comparison

Pattern	Core Idea	When to Choose	Key Trade-off
Lambda	Separate speed layer (stream) + batch layer; serving layer merges both	When you need sub-minute latency AND historical accuracy	Two codebases to maintain; data consistency at merge point
Kappa	Stream-only; replay from immutable log (Kafka) to reprocess history	When stream semantics can handle all business questions	Reprocessing at scale is expensive; requires durable log retention
Medallion (Bronze/Silver/Gold)	Tiered storage: raw → cleansed → aggregated	Data lakehouse (Delta Lake / Iceberg); incremental refinement	Storage amplification; staleness between tiers

4.2 Reliability Techniques

Exactly-Once Processing

True exactly-once delivery is impractical at Meta scale. The production pattern is:

At-least-once delivery from the message broker (Kafka/Scribe) — messages may be duplicated on retry.
Idempotent writes at the sink — use MERGE INTO ... WHEN MATCHED THEN UPDATE (Delta/Iceberg) or INSERT OR REPLACE keyed on a deterministic event_id.

Log-Based Change Data Capture (CDC)

Log-based CDC (Debezium): Reads the database binary log (MySQL binlog, Postgres WAL) — zero additional load on the source database.
Query-based polling: SELECT * WHERE updated_at > last_watermark — simpler but adds read pressure and misses hard-deletes.
Prefer log-based at Staff level; polling is only acceptable for read-heavy sources that don't support CDC.

Operational Maturity: The Three Hard Problems

Problem	Definition	Mitigation
Backpressure	A slow consumer lets the producer queue grow unboundedly	Auto-scale consumers; expose consumer lag as a metric; use bounded queues with drop/DLQ policies
Schema Drift	Upstream adds/renames/removes fields without notice	Schema registry (Confluent / AWS Glue) with compatibility rules; automated validation job that alerts on schema delta before writing to Silver
Small-File Problem	High-frequency streaming writes create millions of tiny Parquet/ORC files, crushing NameNode and slowing reads	Micro-batch compaction job (e.g. Spark `OPTIMIZE` / Delta `ZORDER`) scheduled every 15 min; target 128–512 MB files

4.3 System Design Walkthrough

Example — Design a Real-Time Ad Spend Pipeline

Requirements:

Ingest 5M impression events/second from 200 countries
Serve advertiser spend dashboards with <5 min latency
Guarantee spend figures are never double-counted
Support 90-day historical reprocessing within 4 hours

Reference Architecture (Kappa on Medallion):

Producers (mobile/web/server)
    │
    ▼
[Scribe / Kafka] — retention: 90 days, partitioned by advertiser_id
    │
    ├── Flink Job (streaming)
    │     • watermark: event_time + 2 min allowed lateness
    │     • aggregate spend per (advertiser, campaign, country, minute)
    │     • MERGE INTO delta_bronze.ad_spend_1min  (idempotent on event_minute + advertiser_id)
    │
    ▼
[Bronze — raw, immutable, Parquet/ORC on S3/GCS]
    │
[Silver — cleansed: deduped on impression_id, invalid creatives filtered out]
    │
[Gold — aggregated: daily/weekly rollups, pre-joined with dim_advertiser]
    │
    ▼
Advertiser Dashboard (Presto / internal BI tool reads Gold)

How you handle double-counting: Flink assigns a deterministic impression_id = SHA256(user_id + ad_id + event_ts_ms). The MERGE INTO on Bronze deduplicates on impression_id — replay is safe.

How you handle reprocessing: Replay 90 days of Kafka into the same Flink job with a historical mode flag. Bronze is append-only with partitioning on event_date; reprocessed partitions are overwritten atomically.

Module 5: Product Sense & Metrics

Meta Data Engineers at E6 are expected to act as product partners who understand the "why" behind the data. The metrics round assesses whether you can design a measurement framework and debug anomalies independently.

5.1 Metric Engineering Framework

Tier	Purpose	Example (Marketplace)
North Star	Single metric capturing the core product objective	Weekly Gross Merchandise Value (GMV)
Leading Metrics	Early indicators that predict North Star movement	Listing creation rate, search-to-detail-page CTR
Guardrail Metrics	Monitor for cannibalization or negative side effects	Seller churn rate, buyer dispute rate, p50 latency

Staff signal: Proactively name the guardrail metrics before the interviewer asks. This shows you think about second-order effects.

5.2 Structured Root Cause Analysis (RCA)

When asked "Comments dropped 10% week-over-week — investigate," work through four layers in order:

Technical Integrity: Is the data trustworthy? Check for logging gaps, pipeline delays, instrumentation changes, or schema drift before drawing any business conclusions.
Population Specificity: Is the drop global or localized? Segment by platform (iOS/Android/web), geography, user cohort, and feature surface. A drop only on iOS 17.4 is an instrumentation bug, not a product regression.
External / Internal Events: Did a competitor launch? Was there a news event? Did Meta ship a related feature (e.g. Reacts) that may have substituted for comments? Check the launch calendar.
Metric Characteristics: Is this metric inherently seasonal (e.g. lower on weekends)? Did the denominator change (e.g. DAU grew, making per-user comment rate look flat even if total volume grew)?

Output format the interviewer expects:

State your hypothesis first, then describe the query you'd run to confirm or refute it.
Give a recommendation: "If hypothesis A is confirmed, the action is X. If B, the action is Y."

5.3 RCA Practice

Scenario — "DAU dropped 8% on Tuesday. Investigate."

Layer 1 — Technical Integrity

-- Check: did the logging pipeline have a gap?
SELECT ds, COUNT(DISTINCT user_id) AS dau
FROM fact_daily_active
WHERE ds BETWEEN '2024-01-08' AND '2024-01-16'
GROUP BY ds
ORDER BY ds;

If the gap is Tuesday-only, check whether a pipeline incident affected that partition.

Layer 2 — Population Specificity

-- Segment by platform
SELECT ds, platform, COUNT(DISTINCT user_id) AS dau
FROM fact_daily_active
WHERE ds IN ('2024-01-08', '2024-01-09')  -- Mon vs Tue
GROUP BY ds, platform;

If the drop is on Android only → likely an app crash or push notification failure.

Layer 3 — External Events

Check the release calendar: was there an Android app update on Monday night? Was there a Meta-wide outage reported by Downdetector?

Layer 4 — Metric Characteristics

-- Check YoY: is Tuesday structurally lower?
SELECT DAYOFWEEK(ds) AS dow,
       AVG(dau) AS avg_dau
FROM fact_daily_active
WHERE ds BETWEEN '2023-01-01' AND '2024-01-01'
GROUP BY 1
ORDER BY 1;

Module 6: Leadership & The "Ownership" Paradigm

At E6, the behavioral round assesses your ability to act as a technical force multiplier — someone who raises the output of every engineer around them through influence, mentorship, and clarity, not command authority.

6.1 Storytelling Frameworks

STAR (Situation, Task, Action, Result)

The standard behavioral framework. At Staff level, the Action section must demonstrate cross-team influence, architectural decision-making, or mentorship — not individual execution.

SPSIL (Situation, Problem, Solution, Impact, Lessons)

Better suited for system design retrospectives because it surfaces why a technical choice was made, not just what was done. Use this when the interviewer asks: "Walk me through a complex project you led."

Component	Focus	Time
Situation	Business context and scale (brief)	15 sec
Problem	Technical root cause — be precise	20 sec
Solution	Your architectural decision AND the alternatives you rejected	60–90 sec
Impact	Quantified business or engineering outcome	20 sec
Lessons	What you would do differently — shows intellectual honesty	15 sec

6.2 Staff Signal Techniques

Leadership Without Authority

Prepare a story where you achieved a significant technical outcome without any direct reports. Strong signals include:

Writing a design doc that became the standard adopted by multiple teams
Running a cross-functional "data quality council" you initiated
Convincing a partner team to change their schema by making the business case with data

Conflict Resolution

When disagreeing with a PM or Data Scientist, use the de-risk and commit pattern:

Quantify the risk of the disagreed-upon approach in business terms ($, latency, error rate).
Propose a middle ground that lets the business move forward while mitigating the risk.
Commit to a timeline and deliver on it — credibility is built in execution, not debate.

Architectural Defense (Project Retrospective)

Interviewers will probe your past systems looking for weaknesses. Prepare a granular retrospective for your most complex project:

What were the three alternatives you considered for the critical architectural choice?
What data did you use to make the final decision?
What broke in production that your design did not anticipate?
What would you change if you started today with the same constraints?

Why this matters: Shallow answers like "we chose Kafka because it's scalable" are E4 answers. E6 answers cite specific throughput numbers, specific failure modes observed, and specific trade-offs that informed the choice.

6.3 Example SPSIL Story

"Walk me through a complex data pipeline you led"

Situation (15 sec):

I led the migration of our ads attribution pipeline from a daily Hive batch job to a near-real-time Flink pipeline processing 2M events/second. The pipeline was the source of truth for $800M/year in advertiser billing.

Problem (20 sec):

The batch pipeline produced billing figures 18–24 hours late, causing advertisers to pause campaigns mid-day when they couldn't see real spend. This was driving 15% of advertiser support tickets.

Solution (90 sec):

I considered three options:

Reduce batch frequency to hourly: Lowest risk, but still 60 min latency and didn't scale past 5× event volume growth.
Lambda architecture (stream + batch): Gave <5 min latency, but two codebases to maintain and a complex merge layer. I prototyped it and found the merge logic introduced its own correctness bugs.
Kappa on Medallion (chosen): Single Flink codebase, immutable Bronze layer in Delta, idempotent MERGE on impression_id. Replay from Kafka for historical corrections.

I wrote the design doc, got sign-off from three partner teams (Ads Infra, Billing Eng, Data Governance), and staffed a two-engineer sub-team for the 12-week build. I personally owned the idempotency guarantee and the schema migration playbook.

Impact (20 sec):

Latency dropped from 18 hours to 4 minutes. Advertiser support tickets related to spend visibility fell 60%. The pipeline handles 4× the original event volume without hardware changes.

Lessons (15 sec):

I underestimated the schema governance work — we had 12 upstream producers with inconsistent field naming. Next time I would invest in a schema contract and compatibility checks at the producer level before starting the pipeline build.

6.4 Leadership Checklist

Before your behavioral round, confirm you can answer these with a concrete story:

☐ A time you influenced a major technical decision without formal authority
☐ A time you mentored a junior engineer and can quantify the outcome
☐ A time you pushed back on a PM or leadership using data, and what happened
☐ A project post-mortem where you can name three things you'd change
☐ A time you drove alignment across >3 teams on a shared technical standard

Interview-Day Strategy for E6

Round	First 2 Minutes	Staff Signal
SQL	State the partition key you'll filter on before writing a single line	Proactively mention salting or broadcast join if the schema implies skew
Python/ETL	Ask: "What's the expected input size and update frequency?"	Conclude with a 100× scale discussion even if not prompted
Data Modeling	State the grain explicitly before drawing any table	Name SCD type, fact type, and one alternative you rejected
System Design	Clarify SLA: latency, throughput, durability, and reprocessing requirements	Proactively address exactly-once semantics and operational runbook
Product/Metrics	Define North Star and guardrail metrics before any analysis	Structure RCA as four layers; state hypothesis before running queries
Behavioral	Confirm the competency the question is testing before answering	Use SPSIL for technical stories; name alternatives you rejected