Data engineer at Stripe

As asked

Design a change-data-capture pipeline from a production Postgres database to Snowflake, with under 5 minutes of replication lag and exactly-once semantics for analytics.

Sample answer outline

Source: Postgres logical replication via a slot, consumed by Debezium or a managed connector like Fivetran or Estuary. Stream the change events into Kafka or directly into a staging schema. Land raw events in an append-only table per source table, with the LSN and op type. A scheduled merge job materialises the current state from the raw stream. Exactly-once analytics: idempotent merges keyed on primary key, deduplicate on LSN. Watch the replication slot lag, an abandoned slot fills disk fast.

Reference implementation (sql)

-- Postgres: set up logical replication for CDC
ALTER SYSTEM SET wal_level = 'logical';
SELECT pg_create_logical_replication_slot('cdc_slot', 'pgoutput');

CREATE PUBLICATION cdc_pub FOR TABLE customers, orders, line_items;

-- Snowflake: idempotent merge from the raw CDC stream
MERGE INTO analytics.customers AS tgt
USING (
  SELECT customer_id, name, email, lsn, op
  FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY lsn DESC) AS rn
    FROM raw.customers_cdc
  )
  WHERE rn = 1
) AS src
  ON tgt.customer_id = src.customer_id
WHEN MATCHED AND src.op = 'd' THEN DELETE
WHEN MATCHED THEN UPDATE SET name = src.name, email = src.email
WHEN NOT MATCHED AND src.op <> 'd' THEN
  INSERT (customer_id, name, email) VALUES (src.customer_id, src.name, src.email);

Expect these follow-ups

• What happens when a schema changes upstream?
• How do you handle a 200GB historical backfill without blocking ongoing CDC?
• What is the failure mode if the replication slot is deleted?

As asked

Tell me about a data quality incident where a downstream consumer noticed before you did. What happened and what changed?

Sample answer outline

Pick a real example with measurable impact (wrong financial number in a dashboard, broken ML training set, misallocated marketing spend). Walk through detection (and the embarrassment of being told by a stakeholder), diagnosis, and remediation. The strong answer ends with structural fixes: tests at the contract boundary, freshness alerts, schema change detection, a data SLA with the consuming team. Show that you treat data quality as a system property, not a 'be more careful' exhortation.

Expect these follow-ups

• What test would have caught this earlier?
• How did you communicate the impact to downstream teams?
• How do you decide what to monitor vs what to alert on?

As asked

Write SQL to find each user's first and last purchase date and the days between them. Use window functions.

Sample answer outline

Use FIRST_VALUE and LAST_VALUE partitioned by user_id, ordered by purchase_date. Be careful with the LAST_VALUE default frame, which is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; you need ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the actual last. Alternative: MIN/MAX in an aggregation, then join back. Window function version is more flexible if you need other per-user attributes alongside.

Reference implementation (sql)

select distinct
  user_id,
  first_value(purchase_date) over (
    partition by user_id order by purchase_date
    rows between unbounded preceding and unbounded following
  ) as first_purchase,
  last_value(purchase_date) over (
    partition by user_id order by purchase_date
    rows between unbounded preceding and unbounded following
  ) as last_purchase,
  date_diff(
    last_value(purchase_date) over (
      partition by user_id order by purchase_date
      rows between unbounded preceding and unbounded following
    ),
    first_value(purchase_date) over (
      partition by user_id order by purchase_date
      rows between unbounded preceding and unbounded following
    ),
    day
  ) as days_between
from purchases;

Expect these follow-ups

• What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?
• How would you compute a 7-day rolling sum of revenue per user?
• When is a self-join cleaner than a window function?

As asked

The CFO asks: 'What was our revenue last month by product, and how does that compare to the same month last year?' Write the SQL.

Sample answer outline

Aggregate revenue by product and month from the orders fact table. Compute current month and same-month-last-year in one query with conditional aggregation, then derive the year-over-year delta. Watch for: time zone handling, refunds and chargebacks (do they count), product hierarchy (do we group at SKU or category), and the difference between booking date and recognition date. The strong answer asks one or two clarifying questions before writing the query.

Reference implementation (sql)

with monthly_revenue as (
  select
    date_trunc(order_date, month) as month,
    product_id,
    sum(net_revenue) as revenue
  from {{ ref('fct_orders') }}
  where order_date >= date_sub(current_date(), interval 13 month)
  group by 1, 2
)
select
  curr.product_id,
  curr.revenue as current_month_revenue,
  prev.revenue as same_month_last_year,
  safe_divide(curr.revenue - prev.revenue, prev.revenue) as yoy_change
from monthly_revenue curr
left join monthly_revenue prev
  on prev.product_id = curr.product_id
 and prev.month = date_sub(curr.month, interval 1 year)
where curr.month = date_trunc(date_sub(current_date(), interval 1 month), month)
order by current_month_revenue desc;

Expect these follow-ups

• How do you handle a product that did not exist a year ago?
• What if revenue recognition is on a multi-month schedule (subscriptions)?
• What sanity check do you run on this number before sending it to the CFO?

As asked

You need a streaming layer for event ingestion. Walk me through how you would decide between self-hosted Kafka, MSK, Kinesis, and Confluent Cloud.

Sample answer outline

Self-hosted Kafka gives full control and the lowest unit cost at scale, in return for the operational cost of running a clustered storage system. MSK takes the operational burden but you still own scaling and tuning. Confluent Cloud is fully managed including schema registry, connectors, and ksqlDB, at the highest unit cost. Kinesis is simpler and integrates well with the AWS ecosystem, but caps out on throughput per shard and has a shorter retention. Decision drivers: total throughput, retention needs, ecosystem fit (Kafka has more connectors), and how many engineers you have to operate it.

Expect these follow-ups

• What is the migration path from Kinesis to Kafka if you outgrow it?
• When is Pulsar a better choice than either?
• How do you size shards or partitions for a workload you have not seen yet?

As asked

Implement a Type 2 slowly changing dimension for a customer table where the customer address can change over time. Write the SQL.

Sample answer outline

Type 2 SCD keeps history: for each customer, multiple rows with a valid_from and valid_to, and one row with valid_to NULL or a sentinel far-future date marking 'current'. On an incoming change, close the current row by setting valid_to = today and insert a new row with valid_from = today. Add a surrogate key (customer_sk) so fact tables can join to the right point-in-time version. Mind the boundary cases: same-day changes, late-arriving updates, and the difference between the system clock and the business effective date.

Reference implementation (sql)

MERGE INTO dim_customer AS tgt
USING staging_customer AS src
  ON tgt.customer_id = src.customer_id
 AND tgt.valid_to IS NULL
WHEN MATCHED AND tgt.address <> src.address THEN
  UPDATE SET valid_to = CURRENT_DATE
WHEN NOT MATCHED BY TARGET THEN
  INSERT (customer_id, address, valid_from, valid_to)
  VALUES (src.customer_id, src.address, CURRENT_DATE, NULL);

-- Insert the new "current" row for changed customers
INSERT INTO dim_customer (customer_id, address, valid_from, valid_to)
SELECT src.customer_id, src.address, CURRENT_DATE, NULL
FROM staging_customer src
JOIN dim_customer tgt
  ON tgt.customer_id = src.customer_id
 AND tgt.valid_to = CURRENT_DATE
WHERE tgt.address <> src.address;

Expect these follow-ups

• How would you do this in dbt with snapshots?
• What if an upstream system back-dates a change by 30 days?
• When is Type 2 overkill and Type 1 the right answer?