ETL Advisors | Loading Google BigQuery from Talend: GCS Staging, Load Jobs, and MERGE That Prunes Partitions

Our engagement mix has a gap that mirrors a gap in most Talend estates: plenty of teams have landed on Google Cloud, and almost none of them built their pipelines there. The usual shape is a marketing or product group that standardized on BigQuery years ago, a finance-facing warehouse still fed by a few hundred Talend jobs, and a mandate to consolidate into BigQuery this fiscal year without rewriting a decade of extraction logic.

Good news, same as with Databricks, Microsoft Fabric and Snowflake: you do not need an exotic component set. BigQuery reads Parquet and Avro from Google Cloud Storage and speaks standard SQL. Talend does both well. What decides whether the project is boring or painful is which of BigQuery's four different write paths you pick, and whether you draw the Talend/BigQuery boundary before you build job number two.

Step 1: Pick the write path deliberately

BigQuery gives you more ingest options than the other warehouses, and the defaults in Talend's components do not always steer you to the right one.

Path	Talend side	Use it when	Cost
Load job from GCS	`tFileOutputParquet` + `tGSPut` + `tBigQueryBulkExec` (or a `tDBRow` issuing `LOAD DATA`)	Batch loads, any volume. The default choice.	Free (you pay storage)
Storage Write API	`tBigQueryOutput` in streaming/append mode on recent Studio builds	Near-real-time feeds, micro-batches from Kafka	Per GB ingested
Legacy streaming inserts	older `tBigQueryOutput` "streaming" option	Never, for new work	Per GB, plus a streaming buffer that complicates `MERGE`
Row-by-row DML	`tDBOutput` over JDBC	Never	Brutal — each statement is a query

The anti-pattern is the last row. BigQuery is not a row store; every INSERT statement is a query with planning overhead and a DML concurrency limit per table. A tDBOutput loop that smoke-tests fine on ten thousand rows will still be running the next morning on the real volume, and will start throwing Too many DML statements outstanding against table under any concurrency.

Batch-load through GCS unless a business requirement genuinely needs sub-minute latency. It is the only free path, it is the fastest per byte, and it is the easiest to make idempotent. If you do need streaming, use the Storage Write API and not the legacy insertAll streaming endpoint — the legacy path parks rows in a streaming buffer where they are queryable but not reliably mutable, which breaks MERGE and DELETE in ways that are miserable to debug. Our notes on streaming with Talend apply directly.

Step 2: Authenticate with a service account, not a JSON key on a share

Two credentials are in play and teams conflate them exactly as they do on Azure: one identity writes files to GCS, one identity runs SQL in BigQuery. Use a single dedicated service account for the pipeline and grant it both.

roles/storage.objectAdmin   on the staging bucket only
roles/bigquery.jobUser      on the project (to run load/query jobs)
roles/bigquery.dataEditor   on the target datasets only

Do not grant roles/bigquery.admin, and do not reuse a human's gcloud credentials — see the same argument against personal access tokens in the Databricks write-up.

For the key material itself, in preference order:

Workload identity federation if the Talend runtime is a Kubernetes pod or an EC2 instance — no key file exists at all, which is the only truly safe option. See containerizing Talend jobs.
Attached service account if the job runs on a GCE VM or Cloud Run job — the metadata server supplies short-lived tokens.
A JSON key file read from a path in a context variable, with the file itself delivered by your secret manager at deploy time.

What we still find on live estates and always flag: a service-account.json committed to the job's repo, or sitting on a shared drive, with the path hard-coded in the component. Keep the path in a context variable loaded per environment (multi-environment context handling) so DEV, UAT and PROD differ only in configuration.

Step 3: Stage typed files, one load ID per run

Job skeleton:

tPreJob  -> tSetGlobalVar (load_id = yyyyMMddHHmmss)
tDBInput -> tMap -> tFileOutputParquet   (local staging dir)
         |
         OnSubjobOk -> tGSPut            (upload to gs://.../load_id=.../)
         OnSubjobOk -> tDBRow            (LOAD DATA INTO ..._stg)
         OnSubjobOk -> tDBRow            (MERGE into curated table)
tPostJob -> tDBRow (audit row) + tFileDelete (local cleanup)

Write Parquet, not CSV, unless something forces your hand. Parquet carries types, which stops the two BigQuery-specific drifts that cause "the numbers do not match" after a cutover:

NUMERIC vs FLOAT64. BigQuery's NUMERIC is a 38-digit decimal with 9 decimal places; BIGNUMERIC goes wider. If money lands in a FLOAT64 because it went through CSV and got auto-detected, your reconciliations will be off by pennies forever. Map Talend BigDecimal to NUMERIC explicitly in the schema.
Timestamps and time zones. BigQuery TIMESTAMP is always UTC; DATETIME has no zone. Talend Date is a java.util.Date in the JVM's default zone. Pin -Duser.timezone=UTC on the job server and decide per column which BigQuery type you mean. Getting this wrong shifts a day's worth of rows into the wrong partition and the error only shows up at month boundaries.

Put the load ID in the path, not just the filename:

gs://acme-talend-staging/orders/load_id=20260318T0210/orders-part-0001.parquet

A rerun of the same load ID then overwrites a known, bounded set of objects instead of appending duplicates. Set a lifecycle rule on the staging bucket — delete after 14 days — and you never think about cleanup again.

Step 4: Load into a staging table, never straight into the curated one

LOAD DATA OVERWRITE prod_raw.orders_stg
FROM FILES (
  format = 'PARQUET',
  uris = ['gs://acme-talend-staging/orders/load_id=20260318T0210/*.parquet']
);

LOAD DATA OVERWRITE is doing real work for you here: the staging table ends up holding exactly this run's rows, so a retried batch cannot double-count. If you would rather use tBigQueryBulkExec, set write disposition to WRITE_TRUNCATE for the same effect.

Two settings worth being explicit about. Turn schema auto-detection off and supply the schema — auto-detect happily promotes a renamed source column into a new nullable field full of NULLs, and nobody notices for a quarter. If your sources genuinely change shape, handle it as a contract, per handling schema drift in Talend. And set max_bad_records to 0; a load that quietly dropped 4,000 malformed rows is a failed load.

Step 5: MERGE for upserts, with dedup inside the source

MERGE INTO prod_core.orders AS t
USING (
  SELECT * EXCEPT(rn) FROM (
    SELECT *, ROW_NUMBER() OVER (
             PARTITION BY order_id ORDER BY source_updated_at DESC) AS rn
    FROM prod_raw.orders_stg
  )
  WHERE rn = 1
) AS s
ON  t.order_id = s.order_id
AND t.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
WHEN MATCHED AND s.source_updated_at > t.source_updated_at
  THEN UPDATE SET
    status = s.status,
    amount = s.amount,
    source_updated_at = s.source_updated_at
WHEN NOT MATCHED THEN INSERT ROW;

Three details, each of which we have seen omitted on a live pipeline:

Deduplicate inside the USING subquery. A CDC batch contains several versions of the same key, and BigQuery raises UPDATE/MERGE must match at most one source row for each target row. Same rule as everywhere else — see CDC patterns that scale.

Put a partition filter in the ON clause. This is the BigQuery-specific one. If your target is partitioned by order_date and the join predicate never mentions it, the merge scans the entire table — every run, at full cost. Adding a bounded date range to ON lets BigQuery prune partitions and turns a 2 TB merge into a 30 GB one. Cluster the table on the merge key (CLUSTER BY order_id) as well; it cuts the shuffle further.

Guard the UPDATE with a watermark. s.source_updated_at > t.source_updated_at makes a replayed batch a no-op instead of resurrecting stale values, and stops late, out-of-order events overwriting newer state. If you are keeping history rather than current state, the same staging table feeds the Type 2 pattern.

Step 6: Fail loudly and watch the bytes

tDBRow will swallow a SQL error if you let it. Wire it into tStatCatcher / tLogCatcher and assert on row counts, per our error handling and observability patterns. A merge that updated zero rows on a day the source sent 40,000 changes is a failure, not a success.

BigQuery also hands you a cheap audit source that other warehouses do not. After each statement, record what it cost:

SELECT job_id, statement_type, total_bytes_billed, total_slot_ms
FROM   `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND  job_id = @job_id;

Read that into a tFixedFlowInput and append it to your run-audit table. Now a job whose scanned bytes tripled overnight is visible the next morning instead of at the end of the billing month — the same instinct behind pipeline FinOps.

Two more habits that save real money on GCP specifically: label every BigQuery job Talend submits (talend_job, env, load_id) so cost attribution is possible at all, and decide consciously between on-demand and a small slot reservation with autoscaling. Nightly Talend batches are bursty, which is exactly the profile where a modest baseline reservation plus autoscale beats on-demand per-byte pricing.

Where this leaves the migration conversation

A BigQuery mandate does not mean discarding your Talend estate. It means demoting Talend from "the thing that does the transformation" to "the thing that lands governed, typed, idempotent batches" and letting BigQuery SQL do the set-based work it is far better at — the same division of labour we describe in Talend and dbt together and ETL vs ELT in 2026.

If you are holding a few hundred jobs and a "consolidate into BigQuery" slide, get in touch. Pinning down the landing pattern, the type mapping and the partition strategy once — before job number two — is what keeps the other several hundred boring.