BigQuery

Overview

The BigQuery connector runs standard SQL as jobs in a project you choose and returns typed rows to the workflow. Queries can start runs on a schedule, react when a table changes or run mid-workflow as a lookup, and every job shows up in the trace of its run.

Teams use it to update HubSpot lifecycle stages from product usage, to match Stripe charges against finance tables and to drop weekly exports into Google Drive.

Supported features

• Standard SQL in any BigQuery location, including the US and EU multi-regions

• On-demand and capacity pricing, with a bytes cap on every query

• Workload identity federation, or a service account key

How queries run

Every query Tessel runs is a normal BigQuery job with a source:tessel label, so you can find it in INFORMATION_SCHEMA.JOBS. Results are paged and checked against the expected columns. Inserts use the Storage Write API with stream offsets, so a retry never writes a row twice.

bq query \
  --use_legacy_sql=false \
  --maximum_bytes_billed=10000000000 \
  --label=source:tessel \
  'SELECT account_id, SUM(amount) AS mrr
   FROM finance.subscriptions
   GROUP BY account_id

bq query \
  --use_legacy_sql=false \
  --maximum_bytes_billed=10000000000 \
  --label=source:tessel \
  'SELECT account_id, SUM(amount) AS mrr
   FROM finance.subscriptions
   GROUP BY account_id

bq query \
  --use_legacy_sql=false \
  --maximum_bytes_billed=10000000000 \
  --label=source:tessel \
  'SELECT account_id, SUM(amount) AS mrr
   FROM finance.subscriptions
   GROUP BY account_id

Cost controls

Each query step has a bytes billed cap, 10 GB by default. Tessel runs a dry run when you save a step and shows the estimated bytes, and a query that would go over the cap fails before it costs anything.

Permissions needed

Create a service account for Tessel and grant roles at the narrowest level that works. It needs to run jobs in one project, read the datasets workflows query and write to one dataset only.

• roles/bigquery.jobUser on the project that runs queries

• roles/bigquery.dataViewer on the datasets workflows read

• roles/bigquery.dataEditor only on the dataset Tessel writes to