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, 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.

{
  "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"]
}

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:

{
  "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. 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.

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:

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

flowchart TD
    Start["Sync Job Triggered"] --> Auth["Resolve OAuth Token<br>(Mutex Locked)"]
    Auth --> Fetch["Fetch Unified Resource<br>(e.g., crm/contacts)"]
    Fetch --> Transform["JSONata Transform<br>Map to BigQuery Schema"]
    Transform --> Datastore["Datastore Destination<br>tabledata.insertAll"]
    Datastore --> Watermark["Update State<br>(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.

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.

{
  "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.