--- title: "Sync SaaS Data to BigQuery: Streaming Inserts & Deduplication" slug: sync-saas-data-to-bigquery-streaming-inserts-deduplication date: 2026-11-01 author: Sidharth Verma categories: [Product Updates, Guides] excerpt: "Stream SaaS data into BigQuery with Truto sync jobs. Service-account setup, insertAll streaming inserts, insertId deduplication, and invalid rows." tldr: "Truto sync jobs can write SaaS data straight into BigQuery. The BigQuery datastore's insertRows method streams rows through tabledata.insertAll, uses insert_id_key for best-effort deduplication, and can skip invalid rows." canonical: https://truto.one/blog/sync-saas-data-to-bigquery-streaming-inserts-deduplication/ --- # Sync SaaS Data to BigQuery: Streaming Inserts & Deduplication To stream third-party SaaS data into BigQuery without a traditional ETL pipeline, you need three things: BigQuery's `tabledata.insertAll` streaming API, an `insertId` per row for best-effort deduplication, and a plan for rows that do not match your schema. Truto's BigQuery datastore wraps all three in one sync job method, `insertRows`. This guide breaks down exactly how to architect a multi-tenant data sync using Truto's Sync Job V4 runtime to write normalized API payloads directly to your customers' BigQuery instances. If you are building B2B SaaS, your product's value is directly tied to the data it can ingest. Your customers expect you to pull millions of records from their existing CRMs, HRIS platforms, applicant tracking systems, and accounting tools natively into your application. However, building and maintaining 50 custom data pipelines is a massive engineering drain. Worse, forcing customer-facing integrations through internal ELT tools designed for offline analytics fundamentally breaks down at scale. ## The Challenge of Syncing SaaS Data to BigQuery Bulk data extraction in a B2B SaaS context is fundamentally different from internal data warehousing. When you pull data for internal business intelligence, you control the source, the credentials, and the destination. When you extract data on behalf of your customers, you control none of those things. **The Multi-Tenant Multiplier Effect** If you have 1,000 customers and offer 50 integrations, you do not have 50 data pipelines. You have 50,000 unique integration states. Each of those connections has its own OAuth token lifecycle, its own API rate limits, and its own schema customizations. Many engineering teams attempt to solve this by deploying traditional ETL tools. However, these tools are built for single-tenant internal data movement. Fivetran positions itself as a fully managed data integration for BigQuery, but it charges based on consumption and monthly active rows. When you are syncing millions of rows across thousands of customer accounts, consumption-based pricing becomes prohibitively expensive. Airbyte positions itself as the open-source alternative, but self-hosting requires heavy infrastructure maintenance, and managing 50,000 separate connection syncs in a UI designed for internal data engineers is operationally unviable. You need a programmatic, API-first way to handle [bulk data extraction and ETL workflows through unified APIs](https://truto.one/handling-bulk-data-extraction-and-etl-workflows-through-unified-apis/), bypassing the need for a standalone pipeline tool entirely. ## Why BigQuery? Streaming Without a Staging Bucket BigQuery is the default warehouse for many GCP-native teams, because it is serverless and needs no cluster management. BigQuery is no longer just for offline analytics. Modern SaaS applications are increasingly using BigQuery as a backend for customer-facing analytics, machine learning model training, and real-time reporting dashboards. To power these features, your application needs to stream data from third-party APIs into BigQuery as quickly and reliably as possible. Unlike traditional data warehouses that require you to stage data in cloud storage (like Amazon S3 or Google Cloud Storage) before running a batch load job, BigQuery offers a robust streaming API. This allows you to bypass intermediate storage and write records directly into tables, which is critical for real-time or high-frequency multi-tenant syncs. ## Setting Up the BigQuery Service Account To write data to a customer's BigQuery instance (or your own multi-tenant instance), you need to authenticate securely. BigQuery uses Google Cloud Service Accounts for server-to-server authentication. **Required IAM Permissions** Do not grant the `BigQuery Admin` role. Follow the principle of least privilege. Your service account only needs the following permissions to perform streaming inserts and verify schemas: * `bigquery.tables.updateData`: Required to insert data into the table. * `bigquery.tables.get`: Required to fetch table metadata and schema information. * `bigquery.datasets.get`: Required to verify dataset existence and location. These permissions are typically bundled in the `BigQuery Data Editor` role, but you can create a custom IAM role for tighter security. **Managing Credentials in Truto** In Truto, BigQuery is a first-class datastore. A datastore holds the connection configuration (credentials, dataset, project), while the sync job holds the logic (what data to write). A BigQuery datastore takes three config fields: - `credentials`: the full service account JSON key, the same shape Google Cloud Storage datastores use. Truto encrypts it at rest and never returns it in API responses. - `dataset`: the BigQuery dataset the sync job writes to. - `project_id` (optional): defaults to the project in the credentials. Datastores are scoped to a Truto environment, so one environment cannot use another environment's credentials. ```json { "type": "bigquery", "label": "analytics-warehouse", "config": { "dataset": "saas_sync", "project_id": "acme-production-123", "credentials": { "type": "service_account", "project_id": "acme-production-123", "client_email": "truto-sync@acme-production-123.iam.gserviceaccount.com", "private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n" } }, "validation_methods": ["testConnection"] } ``` > [!TIP] > **Validating Credentials** > Pass `validation_methods: ["testConnection"]` when you create the datastore. For BigQuery, `testConnection` reads the dataset with the service account. The check writes nothing to your tables. ## Streaming Inserts with tabledata.insertAll The BigQuery API documentation specifies the `tabledata.insertAll` method for streaming data directly into tables. This REST API endpoint accepts an array of rows and writes them to the table immediately. **Batch Loading vs. Streaming Inserts** Traditional batch loading requires writing a CSV, JSON, or Parquet file to Google Cloud Storage, then triggering a BigQuery load job. This is efficient for massive, infrequent historical backfills. However, for continuous SaaS integrations - where a CRM might emit a webhook every time a contact is updated, or a sync job runs every 15 minutes to fetch incremental changes - batch loading introduces unnecessary latency, storage costs, and architectural complexity. The `tabledata.insertAll` method lets you stream rows one at a time or in batches. Google recommends about 500 rows per request, and Truto's `insertRows` sends 500 rows per request by default (`batch_size` changes it). ### The insertAll API Payload When making a request to `https://bigquery.googleapis.com/bigquery/v2/projects/{projectId}/datasets/{datasetId}/tables/{tableId}/insertAll`, the payload structure looks like this: ```json { "kind": "bigquery#tableDataInsertAllRequest", "skipInvalidRows": true, "ignoreUnknownValues": true, "rows": [ { "insertId": "contact_987654321_20261012T143000Z", "json": { "id": "987654321", "first_name": "Jane", "last_name": "Doe", "email": "jane.doe@example.com", "updated_at": "2026-10-12T14:30:00Z" } } ] } ``` This architecture fundamentally changes how you design [declarative data sync pipelines without ETL tools](https://truto.one/declarative-data-sync-pipelines-without-etl-tools-for-saas/). Instead of managing a complex ELT orchestrator, you can map normalized API responses directly to the BigQuery streaming endpoint. ## Handling Deduplication with insertId Distributed systems fail. Network requests timeout, API gateways drop connections, and sync jobs must retry. When streaming data into BigQuery, a naive retry mechanism will result in duplicate rows. If an `insertAll` request succeeds on BigQuery's end, but the HTTP response is dropped before reaching your server, your retry logic will insert the exact same rows a second time. To solve this, BigQuery relies on best-effort deduplication using the `insertId` property. **How insertId Works** BigQuery uses the `insertId` property to detect duplicate rows on a best-effort basis. Google documents the window as at least one minute, and does not guarantee deduplication. If BigQuery receives another row with the same `insertId` inside that window, it can drop the duplicate. For exact results, deduplicate again in SQL. **Generating Deterministic insertIds** You must generate a deterministic `insertId` based on the source data. Do not use random UUIDs for `insertId`, because a retry will generate a new UUID, defeating the deduplication mechanism. The best practice for syncing SaaS data is to construct the `insertId` using a combination of the source record's ID and its `updated_at` timestamp. This ensures that updates to the same record are treated as new versions (which you can later deduplicate in your SQL views using `ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC)`), while network retries of the exact same payload are dropped. In Truto you do not build the `insertId` wrapper yourself. Put the deterministic ID in a row field, for example `row_key`, and name that field in `insert_id_key`. Truto sets it as each row's `insertId`. A row whose `row_key` is missing or `null` is sent without an `insertId`. The field also stays in the row itself, so either add a `row_key` column to the table or set `ignore_unknown_values`. ```jsonata payload.records.{ "row_key": id & "_" & updated_at, "id": id, "email": email, "updated_at": updated_at } ``` ## Managing Schema Mismatches: skipInvalidRows When extracting data from third-party SaaS APIs, you are at the mercy of the vendor's schema. Upstream APIs frequently introduce undocumented fields, change data types, or return malformed JSON. If you are syncing data from 50 different CRMs, schema drift is not a possibility - it is a daily reality. By default, `tabledata.insertAll` is all-or-nothing at the batch level. If one row in the request does not match the table schema, BigQuery inserts none of the rows. The response is still HTTP 200, but it carries an `insertErrors` array, and the valid rows are marked `stopped`. In a multi-tenant sync environment, one malformed record from a custom CRM field can halt the entire data pipeline for that customer. **Using skipInvalidRows** The `skipInvalidRows` flag tells BigQuery to insert the valid rows in a request even when some rows are invalid. In Truto the flag is `skip_invalid_rows`, and it changes what the sync job does on a bad row: | Setting | What BigQuery does | What Truto does | |---|---|---| | `skip_invalid_rows` off (default) | Refuses the whole batch. Valid rows come back as `stopped`. | Stops at that batch and reports how many rows earlier batches inserted. | | `skip_invalid_rows` on | Keeps the valid rows and lists the invalid ones in `insertErrors`. | Sends every batch, then reports the skipped and inserted counts. | In both cases the call counts as a failure. A 200 response that carries `insertErrors` is still an error to Truto. The sync job sends a `sync_job_run:record_error` webhook event, so the skipped rows reach your error handling instead of vanishing. **Using ignoreUnknownValues** Similarly, third-party APIs often add new fields to their payloads without warning. If you send a field that does not exist in the BigQuery table schema, the insert will fail. Setting `ignore_unknown_values` (BigQuery's `ignoreUnknownValues`) to `true` tells BigQuery to silently drop any fields in the JSON payload that do not map to the table schema, ensuring the core data continues to flow uninterrupted. ## ELT Without a Pipeline Tool: Truto's Sync Job V4 Truto's Sync Job V4 runtime is designed specifically to handle these complexities natively. A V4 sync job is a JSON document that defines an explicit, typed Directed Acyclic Graph (DAG) for data extraction, transformation, and loading. Instead of writing custom Python scripts or maintaining an Airbyte cluster, you declare how data flows from the Unified API to the BigQuery datastore. ### The Architecture of a V4 Sync Job ```mermaid flowchart TD Start["Sync Job Triggered"] --> Auth["Resolve OAuth Token
(Mutex Locked)"] Auth --> Fetch["Fetch Unified Resource
(e.g., crm/contacts)"] Fetch --> Transform["JSONata Transform
Map to BigQuery Schema"] Transform --> Datastore["Datastore Destination
tabledata.insertAll"] Datastore --> Watermark["Update State
(last_synced_at)"] ``` The V4 runtime replaces implicit resource graphs with explicit nodes. You define a `request` node to pull incremental data from the unified API, a `transform` node using JSONata to reshape the payload and generate the `insertId`, and a `destination` node pointing to your BigQuery datastore. ### A Note on Rate Limits If your own code also calls Truto's Unified API directly, for example for a one-off backfill, Truto passes upstream HTTP 429 errors back to you. It normalizes the limits into the IETF `ratelimit-limit`, `ratelimit-remaining`, and `ratelimit-reset` headers, so your code owns the backoff. Read more in our guide on [handling API rate limits and webhooks from dozens of integrations](https://truto.one/handling-api-rate-limits-and-webhooks-from-dozens-of-integrations/). ### Declarative Destination Configuration In Truto, the BigQuery destination node calls the datastore's `insertRows` method. Placeholders inject the transformed records into the call. The dataset comes from the datastore, so the node names only the table. ```json { "type": "destination", "name": "write_to_bigquery", "depends_on": ["transform_contacts"], "destination_type": "datastore", "method": "insertRows", "config": { "id": "{{args.bigquery_datastore_id}}", "config": { "table": "contacts", "rows": "{{payload.records:json}}", "insert_id_key": "row_key", "skip_invalid_rows": true, "ignore_unknown_values": true, "batch_size": 500 } } } ``` The preceding JSONata transform node builds the `payload.records` array from the unified API response. Each record carries the `row_key` field that `insert_id_key` points to. ### Memory Caching and Performance Since a sync job run processes many records - often paginating through tens of thousands of contacts - fetching the BigQuery datastore credentials from the database on every record would be highly inefficient. Truto's Sync Job V4 runtime caches each datastore in memory for the life of the run. The datastore config, including the decrypted credentials, is fetched once and reused for every record. Google access tokens are cached too, so the run does not sign a new token for each batch. ## Strategic Next Steps Building multi-tenant data pipelines does not require deploying heavy, consumption-priced ETL tools. By using BigQuery's `tabledata.insertAll` method, utilizing `insertId` for deterministic deduplication, and configuring `skipInvalidRows` to survive schema drift, you can stream SaaS data directly into your analytical backend. Truto's Sync Job V4 runtime provides the declarative infrastructure to execute these syncs reliably at scale, handling OAuth token refreshes, pagination, and unified data mapping, and reporting every rejected row to your error webhook. Stop writing custom Python scripts for every new integration. Abstract the integration layer, unify the data models, and stream the results directly to where they belong. > Ready to replace your brittle ETL pipelines with declarative unified API syncs? Schedule a technical deep dive with our engineering team. > > [Talk to us](https://cal.com/truto/partner-with-truto)