Capstone: Mock Loop & Self-Exam

Set a 50-minute timer and a whiteboard. The prompt: design ingestion → lakehouse → orchestration → serving for billions of Cursor events per day, plus third-party business data, on a platform that barely exists yet. That last clause is the whole role. You are not running someone's mature stack; you are drawing the first version.

The deep-dive round watches one thing above raw architecture: do you reason like the person who will own this at 3am when freshness alerts fire. Quantify before you draw, name the failure modes before someone asks and kill your own components when the tail gets expensive.

Spend the first five minutes on scale mathEarn the right to design

Most candidates jump to boxes. The strong ones price the problem first, out loud, so every later choice has a number behind it.

Walk the four layers in orderIngestion → lakehouse → orchestration → serving

The figure's nodes cover ingestion through serving; orchestration is the layer that ties them together and doesn't get its own box. Model tables as Dagster software-defined assets so lineage is the graph, not a wiki. Partition assets by date to make backfills and replays a first-class operation, not a panicked manual script. Use sensors to trigger off Kafka offsets or file arrival instead of blind cron where freshness matters.

Name the failure modes before you're askedThis is where most candidates thin out

Cost and the agent-data boundaryThe two clauses generic candidates skip

The take-home and the onsite both reward the same instinct: a finished small slice that runs and is tested, over a sprawling design that doesn't. Cursor's onsite is a paid, multi-hour build where you present trade-offs - so practice scoping like the clock is real, because on the day it is.

The promptBuild this in a 4-hour box

Build an idempotent incremental pipeline: land raw events to bronze, dedup to silver, expose a gold table with a quality check. Deliver working code, data-quality assertions and a README of trade-offs plus what I'd do next.

A 4-hour timebox that ends with something demoableSlice, don't sprawl

1. 10:00–0:20 · Scope and freeze. Write the smallest end-to-end slice on paper: one event type, bronze → silver → gold, one quality check. Cut everything else to the README's what I'd do next.
2. 20:20–1:30 · Bronze + silver. Land raw, then dedup with an idempotent MERGE. Get a re-run to produce identical row counts - that's your idempotency proof.
3. 31:30–2:30 · Gold + quality check. Build one aggregate and one assertion (row count, null rate or freshness). Make the assertion fail loudly on bad input.
4. 42:30–3:20 · Tests and a re-run. Unit-test the transform, then run the whole pipeline twice and diff the output to prove idempotency.
5. 53:20–4:00 · README. Trade-offs you made, what you cut and why and the next three things you'd build. This is graded as hard as the code.

-- silver.events: dedup bronze to the latest record per event_id.
-- Re-running this MERGE on the same bronze partition is a no-op:
-- that property is the whole point of the exercise.
MERGE INTO silver.events AS t
USING (
  SELECT *
  FROM (
    SELECT *,
      ROW_NUMBER() OVER (
        PARTITION BY event_id
        ORDER BY ingested_at DESC, _commit_version DESC
      ) AS rn
    FROM bronze.events
    WHERE event_date = :run_date   -- incremental: one partition only
  )
  WHERE rn = 1
) AS s
ON t.event_id = s.event_id
WHEN MATCHED AND s.ingested_at > t.ingested_at THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

Be ready to defend every clause. Why ROW_NUMBER over event_id and not DISTINCT. Why the event_date filter makes this incremental. Why the MATCHED AND guard protects against an out-of-order late record overwriting a newer one. If you can't justify a line, an interviewer will find it.

Cursor's first technical screen historically forbids AI beyond autocomplete. So the only honest prep is to run that constraint on yourself: blank file, agent muted, a timer and you narrating correctness, scale and cost while you type. Reasoning is graded, not just the final answer.

Treat the rubric as a gap finder, not a score to admire. Mark the weakest proof, turn it into one practice rep and keep the artifact you would show an interviewer.

Four SQL drills that map to the jobEach is a real platform pattern

SELECT
  user_id,
  event_ts,
  -- session id = cumulative count of “new session” flags per user
  SUM(is_new_session) OVER (
    PARTITION BY user_id ORDER BY event_ts
    ROWS UNBOUNDED PRECEDING
  ) AS session_id
FROM (
  SELECT
    user_id,
    event_ts,
    CASE
      WHEN event_ts - LAG(event_ts) OVER (
             PARTITION BY user_id ORDER BY event_ts
           ) > INTERVAL '30' MINUTE
        OR LAG(event_ts) OVER (
             PARTITION BY user_id ORDER BY event_ts
           ) IS NULL
      THEN 1 ELSE 0
    END AS is_new_session
  FROM events
);

Three Python drillsIdempotent transform, schema validation, backfill loop

• Idempotent transform. A function that, given a partition path, writes deduped output and is safe to re-run - overwrite by partition, never append-then-hope.
• Schema validation. Validate an incoming record against an expected contract; accept new nullable fields, reject a changed type, route the bad record to a dead-letter sink with a reason.
• Backfill / partition loop. Iterate a date range, process one partition at a time, log progress and be resumable after a crash so you don't reprocess what already landed.

from datetime import date, timedelta

def backfill(start: date, end: date, sink) -> None:
    """Process one date partition at a time; skip what's done so a
    crash mid-run is safe to restart without double-processing."""
    day = start
    while day <= end:
        if sink.partition_complete(day):     # idempotency checkpoint
            day += timedelta(days=1)
            continue
        rows = transform_partition(day)       # pure, deterministic
        sink.overwrite_partition(day, rows)   # atomic per partition
        sink.mark_complete(day)
        print(f"backfilled {day}: {len(rows)} rows")
        day += timedelta(days=1)

For senior data hires the values round is often informal, sometimes over a meal and it filters hard for mission belief and intensity. Charm won't carry it. What lands is a small set of quantified stories and a why Cursor that names the founding-platform and agent-data angle specifically.

Rehearse five stories - quantified, in this shapeMap them to the behavioral themes the loop tests

Keep each to about two minutes: situation in 20 seconds, your action in the first person for a minute, a quantified result, then one line of reflection. Past two minutes you're narrating backstory, not the decision.

The 60-second 'why Cursor'Three beats, no filler

1. 1The founding angle. You want to be one of the first data platform engineers, defining primitives rather than maintaining someone else's - enormous scale on a deliberately early-stage platform is the draw, not the deterrent.
2. 2The agent-data angle. Name that the data here directly feeds agent and model capabilities, so privacy, security and ML-data fluency matter more than at an analytics shop - and that's exactly the work you want.
3. 3The mission. Genuine belief that AI-assisted coding is where software is going, said plainly. Intensity reads as real only when the mission belief underneath it is real.

Have a crisp first-90-days answerA common senior probe

• Weeks 1-3: map what exists - current ingestion, the Dagster asset graph, where freshness and cost already hurt. Listen before you rebuild.
• Weeks 4-8: ship one self-serve primitive that removes a recurring pain (a standard ingestion pattern or freshness SLAs on the top gold assets).
• Weeks 9-12: harden the agent-data boundary - masking, access controls and audit - and write the operational standards the next hires inherit.

Prep without a scorecard drifts toward whatever's comfortable. Score each loop stage 1-5 against what it actually tests, map every weak score to a deep-dive module, then build a final-week plan that attacks one gap per day and ends in a full dry-run.

Treat the rubric as a gap finder, not a score to admire. Mark the weakest proof, turn it into one practice rep and keep the artifact you would show an interviewer.

Score each stage against what it testsBe honest - a 3 you can defend beats a hopeful 5

Map weak scores to the deep divesEach gap has a home

The readiness barTwo pass/fail questions

Confirm logistics with your recruiterCheap certainty

• Exact stage order and how many technical screens to expect.
• The AI-tool policy per round - confirm whether the first screen is autocomplete-only.
• Onsite location (NY or SF), format and that it's a paid build day.

A final-week planOne weak area a day, then a full dry-run

1. 1Mon-Wed. One lowest-scoring deep dive per day: re-read it, then do a timed drill or a 50-minute design rep on that exact gap.
2. 2Thu. Behavioral day - record the five stories and the why-Cursor, grade for specificity and ownership, re-record the weakest.
3. 3Fri. Full mock loop dry-run end to end: a no-AI coding rep, a 50-minute design, a scoped build and the values round, scored against this rubric.
4. 4Weekend. Light review of flashcards only; rest beats cramming the night before intensity is being measured.