GCS to BigQuery Explained: Designing a Reliable Batch Data Pipeline
Plan a GCS-to-BigQuery batch pipeline with explicit schemas, staging validation, duplicate handling and a clear retry strategy.
What does a GCS-to-BigQuery pipeline do?
A batch pipeline moves files from Cloud Storage into BigQuery so data can be queried and reported. Reliability comes from defining the data contract, validating each batch and making retries safe, rather than merely drawing arrows between services.
Consider a daily invoice export with invoice_id, amount and event_time. The business needs one current version of each invoice. A retry of yesterday's file must not create a second copy of every invoice.
A practical sequence
- Land the original file in a controlled Cloud Storage location.
- Record the batch identifier and source object identity in a run ledger.
- Load into a staging table with an explicit schema and understood CSV settings.
- Validate row counts, required keys, amount types and timestamp formats.
- Select the intended record per invoice and merge it into the curated table.
- Mark success only after validation and final write complete.
Cloud Storage and BigQuery dataset locations must be compatible. Loading requires appropriate permissions. Test cloud operations in an authorized practice project; this article does not deploy a job or incur cloud usage on your behalf.
How do you reason about duplicate records?
Here is a read-only GoogleSQL example using inline rows. It chooses the newest event for each invoice and makes the tie-break rule explicit.
WITH staging AS (
SELECT 'I1' AS invoice_id, 100 AS amount,
TIMESTAMP '2026-10-01 10:00:00+00' AS event_time, 1 AS sequence_no
UNION ALL
SELECT 'I1', 120, TIMESTAMP '2026-10-01 11:00:00+00', 2
UNION ALL
SELECT 'I2', 80, TIMESTAMP '2026-10-01 10:30:00+00', 1
), ranked AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY invoice_id ORDER BY event_time DESC, sequence_no DESC
) AS rn FROM staging
)
SELECT invoice_id, amount FROM ranked WHERE rn = 1 ORDER BY invoice_id;Expected result: I1 amount 120 and I2 amount 80, total 200. Simply summing staging would give 300 and answer the wrong question. sequence_no must be meaningful and deterministic in the real source; inventing an arbitrary tie-breaker can hide conflicting records.
What makes a retry idempotent?
Idempotent means repeating the same intended operation leaves the same final result. A stable batch identifier helps track retries, but it is not enough by itself. The destination write must also avoid duplicate inserts: for example, a well-defined key-based merge after the staging input is reduced to one row per key.
Do not mark a run complete before its write succeeds. Keep a record of rejected rows and explain why they were rejected. A partial failure needs a documented restart point. For deletions and late corrections, define rules explicitly rather than silently dropping historical facts.
Practice and interview discussion
Draw the failure points in this pipeline. What happens if loading succeeds but validation fails? What happens if the final write succeeds but the success marker is not saved? Explain how your key and write strategy make the second case safe.
Then add a newer correction for I2 and predict its final value. Distinguish source-file duplication from legitimate event history; the same invoice can have several valid versions.
Learning and official sources
GCP Data Engineering course.
Google Cloud CSV load guide.
AI Leaf Global Learn supports classroom learners in Mudichur/Tambaram, Chennai, and online learners across India. Confirm batches and syllabus directly. Prepared by AI Leaf Global Learn. Cloud code is a reviewed teaching example, not a claim of a completed live BigQuery job.