Skip to main content

​
Overview

A BigQuery destination is a reusable connection to a BigQuery dataset you own. Like a Snowflake destination — and unlike an S3 or GCS destination — Cometly loads the data directly into a table in your warehouse on each run. Unlike Snowflake, you paste no credentials at all. Instead, you grant Cometly’s service account access on your dataset and prove you control it with a label. Cometly’s load jobs run against your project, but the jobs themselves are free — you only pay for storage.

​
Prerequisites

  • A Google Cloud project with a BigQuery dataset you own (or permission to create one), in the US or EU multi-region.
  • A space administrator role in Cometly.

​
Connect Cometly to BigQuery

1

Create or choose a dataset

In BigQuery, create a dataset (or choose an existing one) for Cometly exports.
The dataset’s location must be the US or EU multi-region. Cometly copies each run to a transfer bucket colocated with your dataset before loading — a BigQuery requirement — and only those two multi-regions are supported today. A dataset in any other location (including single regions like us-central1) is rejected when you add the destination.
2

Grant Cometly's service account access

In BigQuery, go to your dataset → Sharing → Permissions → Add principal, and grant the BigQuery Data Editor role to Cometly’s loader service account. The exact service account email is shown in the Add destination dialog in Cometly, with a copy button.
3

Label the dataset to prove control

Add a label to the dataset with key cometly-verify and the value shown in the Add destination dialog in Cometly (also copyable). This is a per-space token — it proves that the person connecting the destination actually controls the dataset, so Cometly refuses to load into a dataset you don’t own.
4

Add the destination in Cometly

In the dashboard, go to Integrations → BigQuery → Add destination and enter:
​
string
required
A label for this destination, for example “Production warehouse”.
​
string
required
The GCP project ID (not the project display name or project number), for example my-company-prod.
​
string
required
The BigQuery dataset you granted access to and labeled above, for example cometly_exports.
Cometly runs a connection check on save, and it’s strict — the destination isn’t created until it passes.
The connection check confirms the dataset exists, that the service account has access, that the cometly-verify label matches, and that the dataset’s location is supported — each failure returns a specific, actionable error rather than a generic one.

​
Target table

When you create an export against this destination, Cometly creates the target table for you from your column selection — an explicit schema with one column per selected field, all NULLABLE, typed from the dataset’s source: numeric columns become INT64, NUMERIC, or FLOAT64, dates become DATE, and text becomes STRING. Time columns become TIMESTAMP (UTC) or, for zone-naive source columns, DATETIME (values still UTC). JSON-array columns (for example the contacts identity-graph columns) are stored as STRING containing raw JSON text; use JSON_EXTRACT or PARSE_JSON on your side to work with them. Table names must start with a letter or underscore, followed by letters, digits, or underscores — the $ character allowed in Snowflake table names isn’t valid here. If a table with that name already exists in the dataset, creation fails and you’re asked to choose a different name. Cometly never creates a table by inference from a load, and never drops a table.

​
Loads are append-only

Each run is a BigQuery batch load job (WRITE_APPEND) — free on your side; you only pay for the storage the loaded rows occupy. Cometly does not de-duplicate on your behalf: like every recurring export, a run re-exports its entire trailing window on each firing (see Cadence and windows), so rows from a previous run’s overlapping window land again in the table. The number of rows loaded by each run is recorded on the export’s run history.

​
De-duplicating loaded rows

Because runs append rather than upsert, collapse repeated rows with a ROW_NUMBER() window function partitioned on the dataset’s row-identity column (see the dataset’s page — usually id, or id plus metric_date for ad-analytics datasets):
Save this as a view for query-time de-duplication, or run it as a scheduled query that physically compacts the table:
Schedule the second query (for example, daily, after your export’s run) in BigQuery’s scheduled queries so the table stays compact between reads.
For datasets whose rows can change between runs (ad analytics metrics, conversions updated within the window), the copies of a row are not identical and the query above keeps an arbitrary one. If you need strict latest-wins semantics for those datasets, load into an intermediate table and MERGE on the identity column after each run, updating every non-key column.

​
Next step

Create an export

Choose this destination, a dataset, a target table name, and a date range.