Upsert LarkBase data into BigQuery with n8n
Reliable LarkBase-to-BigQuery sync with staging, validation and set-based MERGE operations.
TL;DR: A reliable LarkBase-to-BigQuery sync validates records at the boundary, stages the batch, and uses a set-based MERGE keyed by a stable business identifier.
The architecture
Do not download the destination table into n8n to compare every row. Send a bounded batch to a staging table, validate required fields, then let BigQuery perform the comparison close to the data. A MERGE can update matching keys and insert new keys in one operation.
MERGE `dataset.target` AS target
USING `dataset.staging` AS source
ON target.business_id = source.business_id
WHEN MATCHED THEN UPDATE SET name = source.name, updated_at = source.updated_at
WHEN NOT MATCHED THEN INSERT (business_id, name, updated_at)
VALUES (source.business_id, source.name, source.updated_at);
Failure modes to design for
Reject duplicate business keys before the merge; otherwise a source row can match the same target more than once. Keep the business key separate from a display name, retain manually owned columns, and batch requests to respect API and query limits. Query cost also matters: partition or filter staging data where appropriate instead of scanning the whole history on every run.
Before production
Test an empty batch, an existing key, duplicate input, schema drift, a transient API error and a partially failed run. Record batch IDs and counts, alert on rejected rows, and make replay safe. Real-time behavior should not be implied unless the source and destination guarantees actually support it.