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. A UI made for one data team to manage a few dozen connections is a poor fit for thousands of customer-owned connections.

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 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. Truto's BigQuery client makes only two calls, so the service account needs at most two permissions:

• bigquery.tables.updateData: Required for insertRows, which calls tabledata.insertAll. Streaming inserts need only this permission.
• bigquery.datasets.get: Required only for testConnection, which reads the dataset to check the credentials.

Truto never reads table metadata, so the service account does not need bigquery.tables.get. The BigQuery Data Editor role includes both permissions, 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. Paste the key file as Google issues it. Truto requires all 11 of its fields, so a partial key returns a 400. 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",
      "private_key_id": "0123456789abcdef0123456789abcdef01234567",
      "private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n",
      "client_email": "truto-sync@acme-production-123.iam.gserviceaccount.com",
      "client_id": "123456789012345678901",
      "auth_uri": "https://accounts.google.com/o/oauth2/auth",
      "token_uri": "https://oauth2.googleapis.com/token",
      "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
      "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/truto-sync%40acme-production-123.iam.gserviceaccount.com",
      "universe_domain": "googleapis.com"
    }
  },
  "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)). A network retry of the exact same payload can be dropped, but only on a best-effort basis and only inside the deduplication window. A retry that arrives later lands as a duplicate row, so keep the SQL step.

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.

A transform node has no payload in its context. It reads the records of the request node it depends on under resources.<resource path>, so the output of a crm/contacts request is resources.crm.contacts:

resources.crm.contacts.{
  "row_key": id & "_" & updated_at,
  "id": id,
  "email": email_addresses[0].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. Its error message gives the number of skipped rows, the first error with its row position, and the number of rows inserted. The event does not carry the rejected rows themselves.

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 running a separate pipeline tool, 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 Run Starts"] --> Fetch["Request Node<br>(e.g., crm/contacts)"]
    Fetch --> Transform["Transform Node<br>JSONata: Map to BigQuery Schema"]
    Transform --> Datastore["Datastore Destination<br>insertRows"]
    Datastore --> BigQuery["BigQuery<br>tabledata.insertAll"]

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

Inside a V4 sync run you do not handle rate limits yourself. When the upstream API answers with HTTP 429, the run pauses, sends a sync_job_run:rate_limited webhook event, and resumes by itself.

The rest of this section applies only when your own code calls Truto's Unified API directly, for example for a one-off backfill. In that case 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.

These are the three nodes in the sync job's resources array:

[
  {
    "type": "request",
    "name": "list_contacts",
    "resource": "crm/contacts",
    "method": "list",
    "integrated_account_id": "{{args.integrated_account_id}}"
  },
  {
    "type": "transform",
    "name": "transform_contacts",
    "depends_on": "list_contacts",
    "config": {
      "expression": "resources.crm.contacts.{ \"row_key\": id & \"_\" & updated_at, \"id\": id, \"email\": email_addresses[0].email, \"updated_at\": updated_at }"
    }
  },
  {
    "type": "destination",
    "name": "write_to_bigquery",
    "depends_on": "transform_contacts",
    "destination_type": "datastore",
    "resources_to_persist": ["transform_contacts"],
    "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
      }
    }
  }
]

A few details matter here:

• depends_on is a single node name, not an array.
• resources_to_persist is required. It lists the nodes whose output this destination writes. Truto skips a destination without it, so nothing reaches BigQuery.
• The transform's output reaches the destination as payload.records. The {{payload.records:json}} placeholder passes that array to insertRows as rows. Each record carries the row_key field that insert_id_key points to.
• The job reads two run arguments, args.integrated_account_id and args.bigquery_datastore_id. Declare them in the sync job's args_schema with the formats integrated_account_id and datastore_id, and set "default_runtime_version": 4 on the job.

What Happens When a Write Fails

A failed datastore write does not fail the sync job run. The run still ends completed, and the watermark moves forward. Truto does not replay the failed records on the next run.

The only signal is the sync_job_run:record_error webhook event. For BigQuery, the event reports the skipped count and the first error. It does not carry the failed rows. So watch the webhook and backfill by resetting the watermark.

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 a separate ETL tool. By using BigQuery's tabledata.insertAll method, using a deterministic insertId for best-effort 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. Failed writes reach your error webhook as a count and the first error, so build the backfill step into your runbook.

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.