> ## 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

**1. Create or choose a dataset**

In BigQuery, create a dataset (or choose an existing one) for Cometly exports.

> [!WARNING]
>
> 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:

**Name** `string` *required*

A label for this destination, for example "Production warehouse".

**Project ID** `string` *required*

The GCP **project ID** (not the project display name or project number), for example
`my-company-prod`.

**Dataset ID** `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.

> [!NOTE]
>
> 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](https://docs.cometly.com/data-warehouse/overview.md#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.

> [!NOTE]
>
> 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

> [card] [Create an export](https://docs.cometly.com/data-warehouse/create-export.md)
>
> Choose this destination, a dataset, a target table name, and a date range.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.