What should I use for reliable incremental loads into BigQuery?

For reliable incremental loads into BigQuery, the best choice usually depends on your source and latency needs, but these are the most common solid options:

Best general-purpose pattern

Use a staging table + MERGE into the target table.

Why:

• Handles inserts, updates, and deletes cleanly
• Supports idempotent loads if you dedupe by a primary key / watermark
• Easy to retry safely
• Works well for batch or micro-batch pipelines

Typical flow:

1. Extract only new/changed rows from source using:
   • updated_at watermark
   • CDC stream
   • monotonically increasing ID
2. Load into a staging table
3. Run a BigQuery MERGE to upsert into the final table

---

If you want managed ELT

Use an ingestion tool that supports incremental sync well, such as:

• Fivetran
• Airbyte
• Stitch
• Matillion

Good when:

• You want less custom code
• Source connectors matter more than full control
• You can accept tool-specific behavior and cost

---

If you want orchestration and custom logic

Use:

• Cloud Composer (Airflow) or
• Dataform / dbt for transformation logic

This is useful for:

• scheduling
• dependency management
• incremental models
• SQL-based transformations

---

If the source is streaming / CDC

Use:

• Datastream → BigQuery
• Pub/Sub → Dataflow → BigQuery

Best when:

• Near-real-time ingestion is needed
• You need reliable change capture
• You want to avoid full reloads

---

Reliability tips

No matter which tool you use:

• Make loads idempotent
• Track a watermark or CDC offset
• Deduplicate in staging before merge
• Partition tables by date if appropriate
• Cluster on merge/join keys
• Use retries with backoff
• Validate row counts / checksums

---

Simple recommendation

• Batch incremental ETL: staging table + MERGE
• Managed connector: Fivetran/Airbyte
• CDC/streaming: Datastream or Dataflow

If you tell me your source system and whether you need batch or near-real-time, I can recommend the best approach more precisely.