> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cometly.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Set up a BigQuery destination
> Grant Cometly's service account access to a BigQuery dataset you own so Cometly can load export data directly into a table.
## 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
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.
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.
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.
In the dashboard, go to **Integrations → BigQuery → Add destination** and enter:
A label for this destination, for example "Production warehouse".
The GCP **project ID** (not the project display name or project number), for example
`my-company-prod`.
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](/data-warehouse/overview#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):
```sql theme={null}
SELECT *
FROM `my-project.cometly_exports.touchpoints`
QUALIFY ROW_NUMBER() OVER (PARTITION BY id) = 1;
```
Save this as a view for query-time de-duplication, or run it as a scheduled query that physically
compacts the table:
```sql theme={null}
CREATE OR REPLACE TABLE `my-project.cometly_exports.touchpoints` AS
SELECT *
FROM `my-project.cometly_exports.touchpoints`
QUALIFY ROW_NUMBER() OVER (PARTITION BY id) = 1;
```
Schedule the second query (for example, daily, after your export's run) in BigQuery's
[scheduled queries](https://cloud.google.com/bigquery/docs/scheduling-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
Choose this destination, a dataset, a target table name, and a date range.