A terabyte a run, and not one byte in RAM.

00The one-pager

Everything below in a single screen.

01The high-level design

Start here. Everything after this section is a zoom into one box on this diagram.

The organising principle is that orchestration and execution are different jobs. The scheduler does nothing but fire. The orchestrator writes control-plane rows. Cloud Tasks owns fan-out, retry and rate limiting. Workers move exactly one partition each and know nothing about the run as a whole.

02The two-minute answer

03Scope & ownership

04Problem & requirements

A client needs their data, as files, on their own SFTP server, every day at a time they chose. The upstream team owns the BigQuery table. Everything between those two facts was ours: partitioning, export, upload, retry, reporting, and the guarantee that nothing goes missing quietly.

Functional

• Run automatically at the configured schedule, per tenant and per job.
• Read from a configured BigQuery source table.
• Create an expiring work table carrying partition_number 1…N.
• One task per partition; one deterministic GCS object per partition.
• Upload each partition to the configured client SFTP destination.
• Drive everything — table, bucket, path template, format, delimiter, compression, credentials, schedule — from tenant config.
• Produce a report showing success and failure per run and per partition.

Non-functional, and what each one forced

05The control data model

Neither BigQuery nor an SFTP server can tell you whether a run succeeded. BigQuery knows about jobs; SFTP knows about files. Only a control database knows that 1,412 partitions were planned, 1,411 are uploaded, and one failed three stages ago.

The state machine matters because it is what a retry reads. A task arriving for a partition already marked UPLOADED returns 200 and does nothing — the work is not repeated, and Cloud Tasks stops retrying because it got a success.

06Scheduler & run start

The entry point is deliberately thin. It validates config, derives an identity, records the run, and hands off. If it did real work, a scheduler retry would double it.

POST /runs/start
{ "tenant_id": "tenant_a",
  "job_name":  "daily_sftp_export",
  "scheduled_at": "2026-09-14T20:00:00+05:30" }

run_id        = tenant_id + job_name + scheduled_date_time
operation_key = RUN_START:{run_id}

The run ID is derived, not generated. Same tenant, same job, same scheduled occurrence — same ID, forever. That single choice is what makes the scheduler safe to retry.

INSERT INTO transfer_runs (run_id, tenant_id, status, created_at)
VALUES (:run_id, :tenant_id, 'PLANNING', NOW())
ON CONFLICT (run_id) DO NOTHING;

07Partitioning strategy

Partitioning is what turns one terabyte-sized problem into 1,400 independently retryable ones. The planner counts the eligible rows, divides by the tenant's partition size, and materialises a run-specific table that carries the partition number as a column.

CREATE OR REPLACE TABLE `project.transfer_work.transfer_run_20260914_203000`
OPTIONS (expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 36 HOUR)) AS
WITH numbered AS (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.stable_business_key) AS rn
  FROM `project.analytics.table1` AS t
  WHERE t.tenant_id = @tenant_id AND t.export_date = @export_date
)
SELECT * EXCEPT(rn),
       CAST(CEIL(rn / @partition_size_rows) AS INT64) AS partition_number
FROM numbered;

Why materialise a table at all

• Retries read identical content. The same run_id + partition_number always yields the same rows.
• The expensive assignment happens once. Not once per retry, and not 1,400 times.
• Workers get tiny payloads. A task carries a run ID and an integer.
• Cleanup is automatic. A 36-hour expiry means no orphaned work tables accumulate.

CEIL(ROW_NUMBER()) vs NTILE

We used the first because partition size is what maps to export duration and SFTP transfer time — the two things that had to stay predictable.

08Fan-out & bounded concurrency

With the manifest written, the planner creates one Cloud Task per partition. The queue — not the application — enforces how many run at once.

for partition_number in range(1, partition_count + 1):
    gcs_uri     = f"gs://{bucket}/{tenant_id}/{run_id}/part-{partition_number:05d}.csv.gz"
    remote_path = render_remote_path(template, run_id, partition_number)

    insert transfer_partitions(run_id, partition_number,
                               gcs_uri, remote_path, status='DISCOVERED')

    create Cloud Task → POST /partitions/export
        { "run_id": run_id,
          "partition_number": partition_number,
          "operation_key": f"BQ_EXPORT:{run_id}:{partition_number}" }

Both destinations are computed now, written to the manifest, and never recomputed. A retry three hours later writes to exactly the same GCS object and exactly the same remote path — which is what makes the retry an overwrite instead of a duplicate.

09The export worker

One worker, one partition. It does not query rows — it asks BigQuery to write them.

EXPORT DATA OPTIONS (
  uri             = 'gs://tenant-export-bucket/tenant_a/{run_id}/part-00042-*.csv.gz',
  format          = 'CSV',
  compression     = 'GZIP',
  overwrite       = true,
  header          = true,
  field_delimiter = ','
) AS
SELECT * EXCEPT(partition_number)
FROM `project.transfer_work.transfer_run_20260914_203000`
WHERE partition_number = 42;

The worker submits the job and polls for completion. What it holds in memory is job metadata — an ID and a status — not 700 MB of rows. BigQuery scans the partition and writes to GCS entirely server-side, which also means the bytes never cross our network.

overwrite = true is safe here precisely because of the temp table: the same partition of the same run produces byte-identical output, so a re-export replaces the object with the same content. Without the deterministic partitioning upstream, that flag would be dangerous.

On durable success the worker moves the partition to GCS_EXPORTED and — in the same transaction — inserts the SFTP_UPLOAD outbox event.

10The SFTP worker

The other half of the memory argument. The object is read from GCS and written to SFTP in bounded chunks, and it is written to a temporary name first.

def upload_partition_to_sftp(gcs_uri, remote_final_path):
    remote_tmp_path = remote_final_path + ".tmp"

    with gcs.open(gcs_uri, "rb") as src:          # a stream, not a download
        with sftp.open(remote_tmp_path, "wb") as dst:
            while True:
                chunk = src.read(32 * 1024 * 1024)   # 32 MB
                if not chunk:
                    break
                dst.write(chunk)

    sftp.rename(remote_tmp_path, remote_final_path)   # atomic-ish reveal

On a retry the worker checks first: if the final path already exists with the expected byte size and checksum, it marks the partition uploaded without re-sending a single byte. If it finds a stale .tmp, it restarts that partition cleanly.

11The memory argument

This is the part of the design most worth being able to draw from memory, because it is the part an interviewer can attack with arithmetic.

The other reason streaming wins is that memory is the one resource that fails catastrophicallyrather than gradually. A slow pipeline delivers late; a pipeline that OOMs at partition 900 of 1,400 loses its worker mid-flight, and every in-flight partition has to be reclaimed by lease expiry.

12Operation keys & leases

Cloud Tasks gives at-least-once delivery. A task can run twice; a worker can finish and die before responding; a duplicate task can be created for the same partition. So the key has to identify the logical operation, not the transport attempt.

-- claim
INSERT INTO operation_idempotency
       (operation_key, status, owner_id, lease_expires_at, attempt_count)
VALUES (:operation_key, 'IN_PROGRESS', :owner_id, NOW() + INTERVAL '10 minutes', 1)
ON CONFLICT (operation_key) DO NOTHING;

-- take over only from an owner that looks dead
UPDATE operation_idempotency
   SET owner_id = :new_owner_id,
       lease_expires_at = NOW() + INTERVAL '10 minutes',
       attempt_count = attempt_count + 1
 WHERE operation_key = :operation_key
   AND status = 'IN_PROGRESS'
   AND lease_expires_at < NOW();          -- 1 row = took over, 0 rows = someone else owns it

-- complete only if we still own it
UPDATE operation_idempotency
   SET status = 'COMPLETED', completed_at = NOW()
 WHERE operation_key = :operation_key
   AND owner_id = :owner_id;

The lease is what separates “a worker is doing this” from “a worker said it was doing this before it was killed”. Without an expiry, a crashed worker holds a partition forever and the run never completes. With one, the work returns to the pool automatically — no operator, no cleanup job.

13The outbox

Three places in this pipeline need a database change to reliably cause something outside the database: planning must create export work, a finished export must trigger an upload, and a finished run must send a report. Each is a dual-write, and each gets an outbox.

BEGIN;
  UPDATE transfer_partitions SET status = 'GCS_EXPORTED' WHERE …;
  INSERT INTO outbox_events (event_type, operation_key, run_id, payload_json, status)
  VALUES ('SFTP_UPLOAD', 'SFTP_UPLOAD:{run_id}:{n}', :run_id, :payload, 'PENDING')
  ON CONFLICT (operation_key) DO NOTHING;
COMMIT;

The relay then claims events under a lease, exactly like the stage handlers:

while True:
    events = claim_pending_events(limit=100, lease='5 minutes')
    for event in events:
        try:
            execute_event_idempotently(event)
            mark_completed(event)
        except RetryableError as e:
            release_or_extend_for_retry(event, e)
        except TerminalError as e:
            mark_failed_and_record_failure(event, e)

14The failure matrix

Every row here is a crash window that was written down before it happened in production.

The only acceptable miss is a loud one

INSERT INTO transfer_failures
       (run_id, partition_number, stage, error_code, error_message, attempts, failed_at)
VALUES (:run_id, :partition_number, :stage, :error_code, :message, :attempts, NOW());

UPDATE transfer_partitions SET status = 'FAILED_TERMINAL' WHERE …;

INSERT INTO outbox_events (event_type, operation_key, run_id, payload_json, status)
VALUES ('EMAIL_REPORT', 'EMAIL_REPORT:' || :run_id || ':FAILURE', :run_id, :payload, 'PENDING')
ON CONFLICT (operation_key) DO NOTHING;

A partition that cannot be delivered after max attempts is a business problem, not an engineering secret. It gets a row, a terminal state and a line in an email naming the run, the partition and the stage that failed.

15Throughput & capacity

partitions   = ceil(total_rows / partition_size_rows)
waves        = ceil(partitions / concurrency)
run_time     ≈ waves × avg_partition_time + orchestration_overhead

worked example
  ~1 TB run, ~700 MB partitions   → ~1,400 partitions
  concurrency                      = 50
  avg partition time               = 120 s  (export + stream + verify)
  waves                            = 28
  run_time                         ≈ 28 × 120 s ≈ 56 min

Four levers move that number, and only two of them are ours: partition size, average partition duration, concurrency, and SFTP throughput. The last one belongs to the client, and in practice it is the binding constraint — which is why the honest answer to “why not concurrency 200?” is that the destination would start refusing connections long before our workers broke a sweat.

16Tenant-generic configuration

The thing that turned this from a job into a platform: no tenant identifier appears in a code path. Everything that differs between clients is a row.

{
  "tenant_id":            "tenant_a",
  "job_name":             "daily_sftp_export",
  "source_table":         "project.analytics.table1",
  "work_dataset":         "project.transfer_work",
  "gcs_bucket":           "tenant-export-bucket",
  "gcs_prefix_template":  "{tenant_id}/{run_id}/",
  "remote_path_template": "/incoming/{tenant_id}/{export_date}/part-{partition_number:05d}.csv.gz",
  "partition_size_rows":  500000,
  "file_format":          "CSV",
  "compression":          "GZIP",
  "delimiter":            ",",
  "include_header":       true,
  "sftp_secret_id":       "projects/.../secrets/tenant-a-sftp",
  "cloud_tasks_queue":    "bq-sftp-export-prod",
  "max_concurrency":      50,
  "max_attempts":         10,
  "schedule":             "0 20 * * *"
}

Two details worth pointing at. Path templates rather than path builders — the client dictates their directory convention, and a template absorbs that without a code branch. And credentials by secret reference, never by value, so onboarding never involves a config row containing a password.

max_concurrency and max_attempts are per-tenant for the same reason: one client's SFTP box handles 50 parallel connections comfortably and another falls over at 10.

17Metrics & alerts

Mid-run, the question is never “is it running?” — it is “will it finish in time, and is anything stuck?”. The metrics are chosen to answer that.

Two of those are the early-warning pair: lease takeovers rising means workers are dying, and outbox event age rising means the relay has stopped making progress. Both show up before the run misses its window.

18Decision record

19Alternatives, argued fairly

• A managed data-transfer service. Genuinely less code. Rejected because the requirement was per-tenant partitioned files with client-dictated naming, compression and directory conventions, plus a per-partition audit trail — the shaping and reporting is most of the work, and that is the part a managed mover does not do.
• Kafka or Pub/Sub instead of Cloud Tasks. Better for continuous event streams with multiple consumers and replay. This is discrete, bounded, per-partition HTTP work with a natural completion, and Cloud Tasks gives retry semantics and a dispatch-rate limit without a consumer fleet. Trade-off accepted: a weaker replay story, which the outbox and durable state cover.
• One big export instead of partitions. BigQuery will shard a large export for you. Rejected because the unit of retry then becomes the whole terabyte — one SFTP failure at 95% and you start again. Partitions make failure cheap and progress observable.
• Spark or Dataflow. The right answer if this were a transformation problem. It is a movement problem with a shaping step that BigQuery already does, so a distributed compute framework would be operational weight bought for nothing.
• Redis for the locks. Faster, and genuinely better for high-frequency short-lived coordination. Rejected because the protected state already lives in Postgres, and a durable completion record has to exist somewhere regardless — adding Redis would mean two consistency boundaries instead of one.
• Push to client cloud storage instead of SFTP. Strictly better technically. Not available: SFTP was what the client could consume, and the design has to end where the client is.

20Cross-functional work

• Upstream analytics team. The contract was which table, which filter columns, and — critically — which column is a stable business key. The partitioning correctness argument depends entirely on that answer, so it had to be theirs explicitly rather than my assumption.
• The client. Directory convention, file naming, compression, how many parallel connections their box tolerates, and what they do with a .tmp file. Concurrency 50 is a negotiated number, not a computed one.
• Platform / DevOps. Cloud Tasks queue configuration, GCS lifecycle rules, service-account IAM, and Secret Manager entries per tenant.
• PM. Turning “daily” into a delivery window, which is what made partition size and concurrency answerable numbers rather than guesses.

21Mentorship

This project has an unusually good teaching surface: the failure matrix is a ready-made curriculum in distributed-systems reasoning, and each row is a self-contained lesson.

• “Where can this crash?” as a review question. Asking it of every handler teaches the dual-write problem far better than explaining the dual-write problem.
• Deterministic identity as a transferable idea. Once someone sees why run_id is derived rather than generated, they apply it everywhere — it is the same lesson as the batch-ID bug on the allocation platform.
• Hand over a stage, not a ticket. A whole stage — the SFTP worker, say — comes with its own failure modes, its own idempotency argument and its own on-call surface.

22Self-initiated work

The delivery requirement was assigned. Two things about how it was built were not.

• Generalising it. The ask was one tenant's export. Building it config-driven — path templates, per-tenant partition size and concurrency, secrets by reference — cost more up front and meant the second and third tenants were config rows. Nobody asked for that; it was the difference between a job and a platform.
• The failure matrix. Nobody writes a ticket saying “a crash between the SFTP upload and the status update will re-send the file”. That came from sitting down and enumerating what happens if each stage dies between its side effect and its acknowledgement — which is where the outbox, the leases and the temp-rename all came from.

For a purely self-initiated project with an external adoption story, the stronger answer is mtptrace — that one started as nobody's project at all.

23Learning curve

• Do the arithmetic before the architecture. The 35 GB number is the entire justification for streaming. Getting it wrong by 10× would have made a broken design look acceptable — and the bug would have surfaced in production, at partition 900, at 2am.
• Determinism is a feature you design in, not a property you hope for. Derived run IDs, row-number partitioning on a stable key, computed GCS URIs, computed remote paths. Every one exists so that a retry can find its own prior work instead of creating new work.
• The constraint you do not control shapes the design most. The client's SFTP box set the concurrency, which set the wave count, which set the run time, which fed back into partition size. Designing around an unscalable dependency is a different discipline from designing for scale.
• “Generic” is cheap early and expensive late. Config-driving it from the start cost maybe a week. Retrofitting it after three tenants had bespoke code paths would have cost far more.

24Outcome & metrics

25The hardest part

Making retries safe across four systems that share no transaction

Postgres, BigQuery, GCS and a remote SFTP server. No two of them can commit together, and every gap between a side effect and its acknowledgement is a distinct failure with a distinct fix. Enumerating those gaps produced the failure matrix; the matrix produced the design.

The subtlest one is the SFTP upload. It is the only step where a partial result is visible to someone else — the client's ingestion job can pick up a half-written file and process it as real data before we even notice the transfer died. Postgres can be rolled back and a GCS object can be overwritten, but a file the client has already consumed cannot be recalled. That asymmetry is why the temp-then-rename pattern is not a nicety.

The runner-up: a correctness bug that no system reports

If the partitioning ORDER BY is not stable, a retried partition exports different rows than the original attempt. No error is raised anywhere — BigQuery succeeds, GCS succeeds, SFTP succeeds, every status turns green, and the client receives files with duplicated and missing rows. It is the only bug in this system that the system itself cannot detect, which is why the stable-key question had to be answered by the team that owns the data rather than assumed by me.

26Question drill

Read the question, answer it out loud, then open the card and compare.

The design

Correctness

Scale & operations

27What not to say

28Gaps to fill before the interview

• Duration and team. Months to first production run, who else contributed, what you led.
• Tenants live. How many onboarded without a code change — that is what proves “generic”.
• Production numbers. Real run sizes, real durations, delivery success rate.
• One incident. A failure the platform absorbed without a human — the best possible evidence the reliability design was real.
• Mentorship story. One engineer: gap, coaching, what they owned afterwards.