BigQuery

Connect & Ingest data from / to a BigQuery database

Setup

The following credentials keys are accepted:

• project (required) -> The GCP project ID for the project

• dataset (required) -> The default dataset (like a schema)

• gc_bucket (optional) -> The Google Cloud Storage Bucket to use for loading (Recommended)

• key_file (optional) -> The path of the Service Account JSON. If not provided, the Google Application Default Credentials will be used.

• key_body (optional) -> The Service Account JSON key content as a string. You can also provide the JSON content in env var GC_KEY_BODY.

• location (optional) -> The location of the account, such as US or EU. Default is US.

• extra_scopes (optional) -> An array of strings, which represent scopes to use in addition to https://www.googleapis.com/auth/bigquery. e.g. ["https://www.googleapis.com/auth/drive", "https://www.googleapis.com/auth/spreadsheets"]

• use_adbc (optional) -> Enable Arrow Database Connectivity (ADBC) driver for high-performance data transfer. See ADBC for setup and details. (v1.5.2+)

• adbc_uri (optional) -> Override the automatically constructed ADBC connection URI when using use_adbc=true.

If you'd like to have sling use the machine's Google Cloud Application Default Credentials (usually with cloud auth application-default login), don't specify a key_file (or the env var GC_KEY_BODY).

Using sling conns

Here are examples of setting a connection named BIGQUERY. We must provide the type=bigquery property:

$ sling conns set BIGQUERY type=bigquery project=<project> dataset=<dataset> gc_bucket=<gc_bucket> key_file=/path/to/service.account.json location=<location>

Environment Variable

See here to learn more about the .env.sling file.

export BIGQUERY='{type: bigquery, project: my-google-project, gc_bucket: my_gc_bucket, dataset: public, location: US, key_file: /path/to/service.account.json}'

You can also provide Sling the Service Account JSON in key_body as a string, or via environment variable GC_KEY_BODY, instead of a key_file.

export GC_KEY_BODY='{"type": "service_account","project_id": ...........}'

Sling Env File YAML

See here to learn more about the sling env.yaml file.

connections:
  BIGQUERY:
    type: bigquery
    project: <project>
    dataset: <dataset>
    gc_bucket: <gc_bucket>
    key_file: '<key_file>'

  # using with `key_body` instead of `key_file`
  BIGQUERY:
    type: bigquery
    project: <project>
    dataset: <dataset>
    gc_bucket: <gc_bucket>
    key_body: |
      { "type": "service_account", ... } 

BigQuery Table Partitioning

streams:
  my_schema.another_table:
    object: my_dataset.{stream_table}
    target_options:
      table_keys:
        partition: [ DATE_TRUNC(transaction_date, MONTH) ]

# OR
streams:
  my_schema.another_table:
    object: my_dataset.{stream_table}
    target_options:
      table_ddl: |
         CREATE TABLE my_dataset.{stream_table} ({col_types}) 
          PARTITION BY
            DATE_TRUNC(transaction_date, MONTH)
            OPTIONS (
              partition_expiration_days = 3,
              require_partition_filter = TRUE)

BigQuery as a target

When BigQuery is the target, Sling writes with BigQuery load jobs, not streaming inserts. The write path is:

1. Stage the data as files. Sling writes the incoming stream to files (CSV by default). If the gc_bucket credential is set, the files are staged in that Google Cloud Storage bucket; if it is not set, they are staged locally and streamed to the load job. Setting gc_bucket is recommended for anything but small loads.

2. Run a load job. Sling submits a BigQuery load job to read the staged files into a temporary table. Load jobs are batched and rate-limited, so a load is one job per batch rather than a row-by-row insert. Sling does not use BigQuery streaming inserts.

3. Finalize. For full-refresh the target is dropped and replaced. For incremental Sling runs a native BigQuery MERGE from the temporary table into the target using your primary_key and the resolved merge_strategy — all four merge strategies are supported.

source: MY_POSTGRES
target: BIGQUERY

defaults:
  object: my_dataset.{stream_table}

streams:
  public.users:
    mode: full-refresh

  public.events:
    mode: incremental
    primary_key: [id]
    update_key: updated_at

Provide gc_bucket so Sling stages through GCS. Without it Sling falls back to a local file stage, which works but is slower and less resilient for large tables. See Setup for the credential.

For BigQuery-specific incremental caveats (partitioned targets, require_partition_filter), see BigQuery Table Partitioning above and BigQuery as a source below.

BigQuery as a source

BigQuery is a standard SQL source: Sling reads it with a SELECT statement, so full-refresh, incremental, and backfill all work.

Partitioned tables

A plain full-refresh reads a partitioned table with SELECT * — partitioning by itself does not block the read.

If the source table sets require_partition_filter = TRUE, BigQuery rejects any query that does not filter on the partition column. Sling does not detect this option or inject a partition predicate automatically, so a plain SELECT * fails. Add a where source option (or --where flag) that filters the partition column to satisfy the requirement:

source: BIGQUERY
target: MY_SNOWFLAKE

streams:
  analytics.events:
    mode: full-refresh
    source_options:
      where: "event_date >= '2024-01-01'"

sling run --src-conn BIGQUERY --src-stream analytics.events \
  --tgt-conn MY_SNOWFLAKE --tgt-object public.events \
  --mode full-refresh --where "event_date >= '2024-01-01'"

Incremental from BigQuery

BigQuery works as an incremental source with primary_key + update_key like any SQL database. Sling pulls rows where update_key > max(update_key) in the target:

source: BIGQUERY
target: MY_SNOWFLAKE

streams:
  analytics.events:
    mode: incremental
    primary_key: [id]
    update_key: updated_at

If the table also has require_partition_filter = TRUE, the incremental predicate is on the update_key, which is not necessarily the partition column. Add a where on the partition column, or make the partition column your update_key, so BigQuery's filter requirement is met.

Multiple datasets to Snowflake (full-refresh, wildcards)

Use a wildcard stream to replicate every table across datasets. Reference the source dataset in the object name with the {stream_schema} runtime variable:

source: BIGQUERY
target: MY_SNOWFLAKE

defaults:
  mode: full-refresh
  object: raw.{stream_schema}_{stream_table}

streams:
  # all tables in these datasets
  analytics.*:
  marketing.*:

Source partitioning does not affect full-refresh ingestion (unless require_partition_filter is set — see above).

If you are facing issues connecting, give sling assist a try, or reach out to us at [e-mail], on discord or open a Github Issue here.