ETL Pipelines & Checkpointing
You already know the shape of an ETL job: pull data from a source, clean it, write it to a destination. And you know the part that actually hurts — what happens when it dies at row 847,392 of a million? Everything in this section is about that question, so let's start there.
The problem you're used to solving
Restartable pipelines are a solved problem, and you've solved it before. The catch is how much machinery it took:
- Re-run the whole job. Simple, and fine at small scale — but re-reading a million rows and re-hitting every API to recover from a failure at 90% is expensive and slow.
- A manual state table. You track
last_processed_offsetin Postgres yourself, read it on startup, update it as you go. Now you own a second source of truth that can drift from the actual work. - Scheduler or Spark checkpoints. A scheduler that passes intermediate results between steps, or Spark's checkpoint directories. Real checkpointing — but it's infrastructure you run, tune, and debug.
All three are answers to one question: where did I get to, and how do I not redo it? Orcher's answer is that you don't implement the answer at all — the runtime already knows, because it journals every step.
Your First ETL Pipeline
We'll build a pipeline that imports a CSV of customers into a database, in batches, with crash recovery — piece by piece. Each piece is a task: a unit of work Orcher journals when it completes. Read it in whichever language you'll use; the tabs stay in sync as you go, and the complete file is at the end to copy.
Step 1 — Count the work
First, how many rows are we importing? Counting is its own task, so the total lands in the journal once. On a restart, the pipeline doesn't re-read the file to recount — it reads the number back from where it left off.
1interface CsvImportInput {2 filePath: string;3 batchSize: number;4}56// A @Task is a unit of work Orcher journals on completion.7@Task()8async countRows(ctx: TaskContext, filePath: string): Promise<number> {9 const content = await fs.readFile(filePath, 'utf-8');10 return content.split('\n').length - 1; // exclude header11}Step 2 — Extract and transform one batch
Now the per-batch work. extract_batch pulls a slice using offset/limit pagination — a familiar cursor over the source — and transform_batch cleans it: drop rows without a valid email, normalize the rest. Keep transforms pure (same input → same output): a deterministic transform replays safely, a random or clock-dependent one does not.
1@Task()2async extractBatch(ctx: TaskContext, input: { filePath: string; offset: number; limit: number }): Promise<Record<string, string>[]> {3 return await csvParser.readBatch(input.filePath, input.offset, input.limit);4}56@Task()7async transformBatch(ctx: TaskContext, rows: Record<string, string>[]): Promise<CleanedRow[]> {8 return rows9 .filter(row => row.email && row.email.includes('@'))10 .map(row => ({11 email: row.email.toLowerCase().trim(),12 name: row.name?.trim() ?? '',13 amount: parseFloat(row.amount) || 0,14 createdAt: new Date(row.created_at).toISOString(),15 }));16}Step 3 — Load without duplicates
This is the step that makes durability safe. A task can be retried after a failure — so if load_batch writes rows and then the worker dies before the result is journaled, the retry runs the write again. A plain INSERT would double-load that batch. An UPSERT (INSERT ... ON CONFLICT DO UPDATE) makes re-running the same batch a no-op: the rows already there are updated in place, not duplicated. This is the single most important habit in durable data work — writes must be idempotent.
1@Task()2async loadBatch(ctx: TaskContext, rows: CleanedRow[]): Promise<number> {3 // UPSERT ensures idempotency on retry4 const result = await db.query(`5 INSERT INTO customers (email, name, amount, created_at)6 SELECT * FROM UNNEST($1::text[], $2::text[], $3::numeric[], $4::timestamptz[])7 ON CONFLICT (email) DO UPDATE SET8 name = EXCLUDED.name,9 amount = EXCLUDED.amount,10 created_at = EXCLUDED.created_at11 `, [rows.map(r => r.email), rows.map(r => r.name), rows.map(r => r.amount), rows.map(r => r.createdAt)]);12 return result.rowCount;13}Step 4 — Orchestrate the batches
The workflow ties the tasks together: count once, then loop over batches running extract → transform → load. Every execute_task call is a journaled checkpoint, so the loop index is effectively durable state — there's no offset table because the journal is the offset. Crash at batch 500 of 1,000, and recovery replays the 499 finished batches instantly and resumes at 500.
1@Workflow({ name: 'csv_import', version: '1.0.0' })2class CsvImport {3 async run(ctx: WorkflowContext, input: CsvImportInput): Promise<number> {4 const totalRows = await ctx.executeTask(tasks.countRows, input.filePath);5 const numBatches = Math.ceil(totalRows / input.batchSize);6 let totalLoaded = 0;78 for (let i = 0; i < numBatches; i++) {9 // Each of these 3 steps is journaled — crash recovery resumes here10 const rows = await ctx.executeTask(tasks.extractBatch, {11 filePath: input.filePath,12 offset: i * input.batchSize,13 limit: input.batchSize,14 });15 const cleaned = await ctx.executeTask(tasks.transformBatch, rows);16 const loaded = await ctx.executeTask(tasks.loadBatch, cleaned);17 totalLoaded += loaded;18 }1920 return totalLoaded;21 }22}Key principle: every execute_task call is journaled. On crash recovery, completed steps return cached results instantly — no re-execution, no duplicate API calls, no duplicate database inserts. You wrote the pipeline; Orcher supplied the durability.
The complete pipeline
Here's everything above assembled into one file.
1import {2 Workflow,3 Tasks,4 Task,5 TaskContext,6 WorkflowContext,7 createTaskRefs,8} from '@orcher/sdk';910interface CsvImportInput {11 filePath: string;12 batchSize: number;13}1415@Tasks()16class EtlTasks {17 @Task()18 async countRows(ctx: TaskContext, filePath: string): Promise<number> {19 const content = await fs.readFile(filePath, 'utf-8');20 return content.split('\n').length - 1; // exclude header21 }2223 @Task()24 async extractBatch(ctx: TaskContext, input: { filePath: string; offset: number; limit: number }): Promise<Record<string, string>[]> {25 return await csvParser.readBatch(input.filePath, input.offset, input.limit);26 }2728 @Task()29 async transformBatch(ctx: TaskContext, rows: Record<string, string>[]): Promise<CleanedRow[]> {30 return rows31 .filter(row => row.email && row.email.includes('@'))32 .map(row => ({33 email: row.email.toLowerCase().trim(),34 name: row.name?.trim() ?? '',35 amount: parseFloat(row.amount) || 0,36 createdAt: new Date(row.created_at).toISOString(),37 }));38 }3940 @Task()41 async loadBatch(ctx: TaskContext, rows: CleanedRow[]): Promise<number> {42 // UPSERT ensures idempotency on retry43 const result = await db.query(`44 INSERT INTO customers (email, name, amount, created_at)45 SELECT * FROM UNNEST($1::text[], $2::text[], $3::numeric[], $4::timestamptz[])46 ON CONFLICT (email) DO UPDATE SET47 name = EXCLUDED.name,48 amount = EXCLUDED.amount,49 created_at = EXCLUDED.created_at50 `, [rows.map(r => r.email), rows.map(r => r.name), rows.map(r => r.amount), rows.map(r => r.createdAt)]);51 return result.rowCount;52 }53}5455const tasks = createTaskRefs(EtlTasks);5657@Workflow({ name: 'csv_import', version: '1.0.0' })58class CsvImport {59 async run(ctx: WorkflowContext, input: CsvImportInput): Promise<number> {60 const totalRows = await ctx.executeTask(tasks.countRows, input.filePath);61 const numBatches = Math.ceil(totalRows / input.batchSize);62 let totalLoaded = 0;6364 for (let i = 0; i < numBatches; i++) {65 const rows = await ctx.executeTask(tasks.extractBatch, {66 filePath: input.filePath,67 offset: i * input.batchSize,68 limit: input.batchSize,69 });70 const cleaned = await ctx.executeTask(tasks.transformBatch, rows);71 const loaded = await ctx.executeTask(tasks.loadBatch, cleaned);72 totalLoaded += loaded;73 }7475 return totalLoaded;76 }77}Automatic Checkpointing
Traditional pipelines require manual checkpoint management. Orcher journals every task result automatically, so your checkpoint is always the last completed task. Picture a four-stage pipeline that crashes after validation — here's exactly what runs, and what doesn't, on recovery:
The mental shift is worth stating plainly, because it's the one that carries through the rest of this path:
| What you'd reach for | What Orcher does instead |
|---|---|
Spark checkpoint directory, a scheduler's intermediate results, or a hand-rolled job_state table | Every execute_task result is journaled automatically |
| Read state on startup, decide where to resume | Replay returns completed steps from the journal; execution continues at the first unfinished one |
| Dedup or "already processed?" guards so a retry doesn't double-write | Completed tasks are never re-executed — the guard is the journal |
You still design the pipeline; Orcher owns the bookkeeping. That trade — you keep the logic, the runtime keeps the durability — is the through-line of everything below.
1@Workflow({ name: 'multi_stage_pipeline', version: '1.0.0' })2class MultiStagePipeline {3 async run(ctx: WorkflowContext, source: string): Promise<PipelineResult> {4 // Step 1: Extract (journaled)5 const raw = await ctx.executeTask(tasks.extract, source);67 // Step 2: Validate (journaled)8 const valid = await ctx.executeTask(tasks.validate, raw);910 // Step 3: Enrich (journaled)11 const enriched = await ctx.executeTask(tasks.enrich, valid);1213 // Step 4: Load (journaled)14 const result = await ctx.executeTask(tasks.load, enriched);1516 // If crash happens after step 2 but before step 3:17 // → Steps 1-2 return cached results instantly (no re-execution)18 // → Step 3 executes fresh and continues from there1920 return result;21 }22}