Getting Started

Batch & Incremental Processing


This page covers two ways to move data: batch processing a whole dataset in chunks, and incremental sync — only pulling what changed since last time. The first you saw in miniature on the last page; the second is the pattern that turns a nightly full-reload into a lightweight sync that runs every few minutes.

Batch Processing

You already know this shape from the CSV import — count the work, loop over chunks, checkpoint each one. Here it's a database-to-database migration instead of a file, with an enrichment step in the middle. Same durability, new source and transform. We'll build it in three moves.

Step 1 — Page through the source

Two tasks read the source: count_source_rows for the total, and fetch_batch for one page via LIMIT/OFFSET — the cursor pagination you'd write against any large table. Each fetched page is a journaled task, so a crash costs you one page, not the whole migration.

1
@Task()
2
async countSourceRows(ctx: TaskContext, table: string): Promise<number> {
3
const result = await db.query('SELECT COUNT(*) as count FROM ' + table);
4
return result.rows[0].count;
5
}
6
7
@Task()
8
async fetchBatch(ctx: TaskContext, input: { table: string; offset: number; limit: number }): Promise<Row[]> {
9
return await db.query(
10
'SELECT * FROM ' + input.table + ' ORDER BY id LIMIT $1 OFFSET $2',
11
[input.limit, input.offset]
12
);
13
}

Note on OFFSET. LIMIT/OFFSET is the clearest way to show pagination, but on very large tables the database re-scans skipped rows, so deep offsets get slow. In production, prefer keyset pagination — WHERE id > $last_id ORDER BY id LIMIT $n — which is also the seam that leads straight into incremental sync below.

Step 2 — Enrich, then load idempotently

transform_rows does the enrichment the migration exists for — lowercasing emails, mapping a country code to a region, normalizing dates. upsert_batch writes with an UPSERT so a retried batch updates in place instead of duplicating — the idempotency rule from the last page, and it never stops mattering.

1
@Task()
2
async transformRows(ctx: TaskContext, rows: Row[]): Promise<TransformedRow[]> {
3
return rows.map(row => ({
4
...row,
5
email: row.email.toLowerCase(),
6
region: lookupRegion(row.country_code),
7
created_date: new Date(row.created_at).toISOString().split('T')[0],
8
}));
9
}
10
11
@Task()
12
async upsertBatch(ctx: TaskContext, input: { table: string; rows: TransformedRow[] }): Promise<number> {
13
return await db.upsertRows(input.table, input.rows); // ON CONFLICT DO UPDATE
14
}

Step 3 — Orchestrate the migration

The workflow is the same count-then-loop as before, now fetch → transform → upsert per batch. Nothing tracks progress explicitly — the loop index lives in the journal.

1
@Workflow({ name: 'database_migration', version: '1.0.0' })
2
class DatabaseMigration {
3
async run(ctx: WorkflowContext, input: MigrationInput): Promise<MigrationResult> {
4
const totalRows = await ctx.executeTask(tasks.countSourceRows, input.sourceTable);
5
const numBatches = Math.ceil(totalRows / input.batchSize);
6
7
for (let i = 0; i < numBatches; i++) {
8
const rows = await ctx.executeTask(tasks.fetchBatch, {
9
table: input.sourceTable, offset: i * input.batchSize, limit: input.batchSize,
10
});
11
const transformed = await ctx.executeTask(tasks.transformRows, rows);
12
await ctx.executeTask(tasks.upsertBatch, { table: input.targetTable, rows: transformed });
13
}
14
15
return { rowsMigrated: totalRows, batchesProcessed: numBatches };
16
}
17
}

The Batch Processing reference has the full assembled file plus a step-by-step crash-recovery walkthrough. With the shape familiar, let's get to the pattern this page is really about.


Incremental Sync (CDC Pattern)

Re-processing everything on every run doesn't scale — once a table has millions of rows, you want to touch only what changed since last time. That's change data capture (CDC), and you've built it before:

  • A cursor column + cron. Store last_synced_at, SELECT ... WHERE updated_at > last_synced_at, advance the cursor. Simple — until a run fails after advancing the cursor but before loading, and you've silently skipped a window forever.
  • Debezium + Kafka Connect. Robust, streaming CDC — but a broker, connectors, and a schema registry to operate for what might be a five-minute sync.

The pattern below is the cursor approach with the classic bug engineered out: the cursor (here called a watermark) advances only after the load succeeds, and because that ordering is journaled, a crash can't leave it half-advanced.

The Watermark Advances Last
1
get_watermarklast run reached 10:00:00
2
fetch_new_recordseverything since 10:00:00 — 412 rows
3
transform → loadUPSERT into the target
✗
load fails / worker dieswatermark still says 10:00:00
↻
next runre-fetches the same 412 rows — UPSERT makes the re-load harmless
✓
save_watermarkonly now advances — to the newest row loaded
Re-fetch on failure, never skip. The watermark is a promise: everything below it is loaded.

Now the code, built to match that ordering. The complete file is at the end.

Step 1 — Read the watermark

Where did the last run get to? On the very first run there's no row yet, so we fall back to an epoch start.

1
@Task()
2
async getWatermark(ctx: TaskContext, sourceName: string): Promise<string | null> {
3
const result = await db.query(
4
'SELECT watermark FROM sync_state WHERE source = $1', [sourceName]
5
);
6
return result.rows[0]?.watermark ?? null;
7
}

Step 2 — Fetch only what's new, and transform it

fetch_new_records asks the source for everything after the watermark, ordered by time. transform_records reshapes it — cents to dollars, uppercased currency, mapped status. Fetching a bounded window (limit: 1000) keeps each run small; a backlog just takes a few runs to drain.

1
@Task()
2
async fetchNewRecords(ctx: TaskContext, input: { source: string; since: string }): Promise<SourceRecord[]> {
3
return await sourceApi.listPayments({
4
createdAfter: input.since, limit: 1000, orderBy: 'created_at',
5
});
6
}
7
8
@Task()
9
async transformRecords(ctx: TaskContext, records: SourceRecord[]): Promise<TargetRecord[]> {
10
return records.map(r => ({
11
id: r.id,
12
amount: r.amount / 100, // cents to dollars
13
currency: r.currency.toUpperCase(),
14
status: mapStatus(r.status),
15
customerId: r.customer_id,
16
createdAt: r.created_at,
17
}));
18
}

Step 3 — Load, then advance the watermark

Two tasks, and their order is the whole pattern. load_records UPSERTs the batch; save_watermark records how far we got. Load first: if it fails, the watermark is untouched and the next run re-syncs the same window safely. Save last: the watermark only ever names data that's already landed.

1
@Task()
2
async loadRecords(ctx: TaskContext, input: { table: string; records: TargetRecord[] }): Promise<number> {
3
return await db.upsertRows(input.table, input.records);
4
}
5
6
@Task()
7
async saveWatermark(ctx: TaskContext, input: { source: string; watermark: string }): Promise<void> {
8
await db.query(`
9
INSERT INTO sync_state (source, watermark, updated_at)
10
VALUES ($1, $2, NOW())
11
ON CONFLICT (source) DO UPDATE SET watermark = $2, updated_at = NOW()
12
`, [input.source, input.watermark]);
13
}

Step 4 — Orchestrate the sync

The workflow runs the five tasks in the order that makes the watermark safe, with one shortcut: if nothing's new, return early without touching the watermark.

1
@Workflow({ name: 'incremental_sync', version: '1.0.0' })
2
class IncrementalSync {
3
async run(ctx: WorkflowContext, input: SyncInput): Promise<SyncResult> {
4
const watermark = await ctx.executeTask(tasks.getWatermark, input.sourceName);
5
const since = watermark ?? '2024-01-01T00:00:00Z';
6
7
const records = await ctx.executeTask(tasks.fetchNewRecords, { source: input.sourceName, since });
8
if (records.length === 0) {
9
return { recordsSynced: 0, newWatermark: since }; // nothing new — don't move the watermark
10
}
11
12
const transformed = await ctx.executeTask(tasks.transformRecords, records);
13
await ctx.executeTask(tasks.loadRecords, { table: input.targetTable, records: transformed });
14
15
// advance the watermark ONLY after the load succeeded
16
const newWatermark = records[records.length - 1].created_at;
17
await ctx.executeTask(tasks.saveWatermark, { source: input.sourceName, watermark: newWatermark });
18
19
return { recordsSynced: records.length, newWatermark };
20
}
21
}

Run this on a durable timer (the Scheduled Sync reference shows how) and you have a CDC pipeline that survives restarts, never skips a window, and needs no broker — a sync_state table and a loop.

The complete sync

1
@Tasks()
2
class SyncTasks {
3
@Task()
4
async getWatermark(ctx: TaskContext, sourceName: string): Promise<string | null> {
5
const result = await db.query(
6
'SELECT watermark FROM sync_state WHERE source = $1', [sourceName]
7
);
8
return result.rows[0]?.watermark ?? null;
9
}
10
11
@Task()
12
async fetchNewRecords(ctx: TaskContext, input: { source: string; since: string }): Promise<SourceRecord[]> {
13
return await sourceApi.listPayments({
14
createdAfter: input.since, limit: 1000, orderBy: 'created_at',
15
});
16
}
17
18
@Task()
19
async transformRecords(ctx: TaskContext, records: SourceRecord[]): Promise<TargetRecord[]> {
20
return records.map(r => ({
21
id: r.id,
22
amount: r.amount / 100, // cents to dollars
23
currency: r.currency.toUpperCase(),
24
status: mapStatus(r.status),
25
customerId: r.customer_id,
26
createdAt: r.created_at,
27
}));
28
}
29
30
@Task()
31
async loadRecords(ctx: TaskContext, input: { table: string; records: TargetRecord[] }): Promise<number> {
32
return await db.upsertRows(input.table, input.records);
33
}
34
35
@Task()
36
async saveWatermark(ctx: TaskContext, input: { source: string; watermark: string }): Promise<void> {
37
await db.query(`
38
INSERT INTO sync_state (source, watermark, updated_at)
39
VALUES ($1, $2, NOW())
40
ON CONFLICT (source) DO UPDATE SET watermark = $2, updated_at = NOW()
41
`, [input.source, input.watermark]);
42
}
43
}
44
45
const tasks = createTaskRefs(SyncTasks);
46
47
@Workflow({ name: 'incremental_sync', version: '1.0.0' })
48
class IncrementalSync {
49
async run(ctx: WorkflowContext, input: SyncInput): Promise<SyncResult> {
50
const watermark = await ctx.executeTask(tasks.getWatermark, input.sourceName);
51
const since = watermark ?? '2024-01-01T00:00:00Z';
52
53
const records = await ctx.executeTask(tasks.fetchNewRecords, { source: input.sourceName, since });
54
if (records.length === 0) {
55
return { recordsSynced: 0, newWatermark: since };
56
}
57
58
const transformed = await ctx.executeTask(tasks.transformRecords, records);
59
await ctx.executeTask(tasks.loadRecords, { table: input.targetTable, records: transformed });
60
61
const newWatermark = records[records.length - 1].created_at;
62
await ctx.executeTask(tasks.saveWatermark, { source: input.sourceName, watermark: newWatermark });
63
64
return { recordsSynced: records.length, newWatermark };
65
}
66
}

Next Steps