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()2async 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}67@Task()8async 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/OFFSETis 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()2async 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}1011@Task()12async upsertBatch(ctx: TaskContext, input: { table: string; rows: TransformedRow[] }): Promise<number> {13 return await db.upsertRows(input.table, input.rows); // ON CONFLICT DO UPDATE14}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' })2class 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);67 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 }1415 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.
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()2async 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()2async 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}78@Task()9async transformRecords(ctx: TaskContext, records: SourceRecord[]): Promise<TargetRecord[]> {10 return records.map(r => ({11 id: r.id,12 amount: r.amount / 100, // cents to dollars13 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()2async loadRecords(ctx: TaskContext, input: { table: string; records: TargetRecord[] }): Promise<number> {3 return await db.upsertRows(input.table, input.records);4}56@Task()7async 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' })2class 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';67 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 watermark10 }1112 const transformed = await ctx.executeTask(tasks.transformRecords, records);13 await ctx.executeTask(tasks.loadRecords, { table: input.targetTable, records: transformed });1415 // advance the watermark ONLY after the load succeeded16 const newWatermark = records[records.length - 1].created_at;17 await ctx.executeTask(tasks.saveWatermark, { source: input.sourceName, watermark: newWatermark });1819 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()2class 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 }1011 @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 }1718 @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 dollars23 currency: r.currency.toUpperCase(),24 status: mapStatus(r.status),25 customerId: r.customer_id,26 createdAt: r.created_at,27 }));28 }2930 @Task()31 async loadRecords(ctx: TaskContext, input: { table: string; records: TargetRecord[] }): Promise<number> {32 return await db.upsertRows(input.table, input.records);33 }3435 @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}4445const tasks = createTaskRefs(SyncTasks);4647@Workflow({ name: 'incremental_sync', version: '1.0.0' })48class 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';5253 const records = await ctx.executeTask(tasks.fetchNewRecords, { source: input.sourceName, since });54 if (records.length === 0) {55 return { recordsSynced: 0, newWatermark: since };56 }5758 const transformed = await ctx.executeTask(tasks.transformRecords, records);59 await ctx.executeTask(tasks.loadRecords, { table: input.targetTable, records: transformed });6061 const newWatermark = records[records.length - 1].created_at;62 await ctx.executeTask(tasks.saveWatermark, { source: input.sourceName, watermark: newWatermark });6364 return { recordsSynced: records.length, newWatermark };65 }66}