Getting Started

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_offset in 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.

1
interface CsvImportInput {
2
filePath: string;
3
batchSize: number;
4
}
5
6
// A @Task is a unit of work Orcher journals on completion.
7
@Task()
8
async countRows(ctx: TaskContext, filePath: string): Promise<number> {
9
const content = await fs.readFile(filePath, 'utf-8');
10
return content.split('\n').length - 1; // exclude header
11
}

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()
2
async 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
}
5
6
@Task()
7
async transformBatch(ctx: TaskContext, rows: Record<string, string>[]): Promise<CleanedRow[]> {
8
return rows
9
.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()
2
async loadBatch(ctx: TaskContext, rows: CleanedRow[]): Promise<number> {
3
// UPSERT ensures idempotency on retry
4
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 SET
8
name = EXCLUDED.name,
9
amount = EXCLUDED.amount,
10
created_at = EXCLUDED.created_at
11
`, [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' })
2
class 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;
7
8
for (let i = 0; i < numBatches; i++) {
9
// Each of these 3 steps is journaled — crash recovery resumes here
10
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
}
19
20
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.

1
import {
2
Workflow,
3
Tasks,
4
Task,
5
TaskContext,
6
WorkflowContext,
7
createTaskRefs,
8
} from '@orcher/sdk';
9
10
interface CsvImportInput {
11
filePath: string;
12
batchSize: number;
13
}
14
15
@Tasks()
16
class 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 header
21
}
22
23
@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
}
27
28
@Task()
29
async transformBatch(ctx: TaskContext, rows: Record<string, string>[]): Promise<CleanedRow[]> {
30
return rows
31
.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
}
39
40
@Task()
41
async loadBatch(ctx: TaskContext, rows: CleanedRow[]): Promise<number> {
42
// UPSERT ensures idempotency on retry
43
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 SET
47
name = EXCLUDED.name,
48
amount = EXCLUDED.amount,
49
created_at = EXCLUDED.created_at
50
`, [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
}
54
55
const tasks = createTaskRefs(EtlTasks);
56
57
@Workflow({ name: 'csv_import', version: '1.0.0' })
58
class 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;
63
64
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
}
74
75
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:

Crash After Step 2, Then Recover
✓
extractStep 1 done — result in journal
✓
validateStep 2 done — result in journal
✗
server crashesbefore enrich starts
↻
replayextract + validate return journaled results — not re-run
▶
enrichStep 3 executes fresh — the pipeline continues here
▶
loadStep 4 runs normally
Your checkpoint is always the last completed task — you never write one.

The mental shift is worth stating plainly, because it's the one that carries through the rest of this path:

What you'd reach forWhat Orcher does instead
Spark checkpoint directory, a scheduler's intermediate results, or a hand-rolled job_state tableEvery execute_task result is journaled automatically
Read state on startup, decide where to resumeReplay returns completed steps from the journal; execution continues at the first unfinished one
Dedup or "already processed?" guards so a retry doesn't double-writeCompleted 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' })
2
class MultiStagePipeline {
3
async run(ctx: WorkflowContext, source: string): Promise<PipelineResult> {
4
// Step 1: Extract (journaled)
5
const raw = await ctx.executeTask(tasks.extract, source);
6
7
// Step 2: Validate (journaled)
8
const valid = await ctx.executeTask(tasks.validate, raw);
9
10
// Step 3: Enrich (journaled)
11
const enriched = await ctx.executeTask(tasks.enrich, valid);
12
13
// Step 4: Load (journaled)
14
const result = await ctx.executeTask(tasks.load, enriched);
15
16
// 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 there
19
20
return result;
21
}
22
}

Next Steps