Data engineer at Amazon

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

Explain what a Kafka consumer group rebalance is, what triggers one, and what the practical impact is on a production streaming pipeline. What strategies do you use to minimize disruption?

Sample answer outline

A rebalance redistributes partition ownership among consumers in a group. Triggers include a consumer joining or leaving, a heartbeat timeout, or a topic partition count changing. During the rebalance all consumers stop consuming (stop-the-world by default with eager rebalancing), which causes latency spikes and can cause duplicate processing if offsets were not committed. Strong answers mention cooperative incremental rebalancing introduced in Kafka 2.4 that only moves affected partitions, static group membership to avoid rebalances on rolling restarts, tuning session.timeout.ms and max.poll.interval.ms, and idempotent consumers to handle duplicates safely.

Expect these follow-ups

• How does static group membership work, and what are its trade-offs?
• What offset commit strategies do you use to minimize data loss or duplication around a rebalance?

As asked

dbt supports multiple incremental strategies: append, delete+insert, merge, and insert_overwrite. Walk me through when you choose each one and what can go wrong with the merge strategy on large tables in BigQuery.

Sample answer outline

Append is simplest and fastest but only correct when rows are immutable (event logs). Delete+insert drops and re-inserts the affected partition, which is atomic but requires a reliable partition column. Merge is the most flexible for slowly changing records but on BigQuery performs a full table scan on the target unless the predicate includes the partition column, which makes it expensive at scale. Insert_overwrite replaces entire partitions and is good for hour or day granularity pipelines where reprocessing a whole partition is acceptable. Strong answers mention the is_incremental() macro, the unique_key configuration, and testing incremental models with dbt test to catch late-arriving data issues.

Expect these follow-ups

• How do you handle late-arriving data in a dbt incremental model?
• What is the risk of using unique_key with merge on a table that receives high-volume updates?