> For the complete documentation index, see [llms.txt](https://docs.sumble.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.sumble.com/system-setup-and-configuration/data-destinations/bigquery.md).

# BigQuery

Sumble can read your account list from BigQuery and write enriched data back to BigQuery as tables.

## How it works

The two directions are independent — set up either one, or both.

**Pull** — Sumble reads your accounts (and optionally your contacts) from a table or view you specify, e.g. `my-project.sumble.accounts`. Column mappings translate your field names to Sumble's schema, so you can use whatever column names are natural for your system. Each run reads the current contents of the table; accounts that are no longer present are marked as removed on the Sumble side.

**Push** — After enrichment, Sumble loads the results into a BigQuery dataset you nominate, as one table per output table. Each table's contents are replaced in full when its load job commits, so you never read a partly-loaded table.

Sumble runs every BigQuery query and load job in **its own Google Cloud project**, so all BigQuery compute and storage-load costs are billed to Sumble, not to you. You never need to grant Sumble the BigQuery Job User (`roles/bigquery.jobUser`) role.

## Where the dataset lives

There are two ways to set this up. Pick whichever fits your data governance model — the data delivered is identical.

**Your dataset.** You create a dataset in your own Google Cloud project and grant Sumble's service account access to it. Your data never leaves your project, and you control retention and downstream access.

**Sumble's dataset.** Sumble creates a dataset in its project and grants a principal you nominate read access to it. Nothing to provision on your side — you just tell us which user, group, or service account should be able to read it.

### Access to grant

Sumble's service account is:

```
customer-data-share-sa@sumble-358418.iam.gserviceaccount.com
```

For a dataset in **your** project, grant that service account:

| Direction                          | Role                                               | Granted on                                               |
| ---------------------------------- | -------------------------------------------------- | -------------------------------------------------------- |
| Pull (Sumble reads your accounts)  | BigQuery Data Viewer (`roles/bigquery.dataViewer`) | The dataset holding your input table, or just that table |
| Push (Sumble writes enriched data) | BigQuery Data Editor (`roles/bigquery.dataEditor`) | The output dataset                                       |

For a dataset **hosted by Sumble**, you don't grant anything — send us the principal that should read it (a user, group, or service account email) and we'll grant it BigQuery Data Viewer on our side.

## Setup

BigQuery integration requires an enterprise plan.

### 1. Prepare your input table (pull only)

Identify or create the table or view holding the accounts you want Sumble to enrich. Sumble uses the following fields for matching:

<table><thead><tr><th width="232">Field</th><th width="90" data-type="checkbox">Required</th><th>Description</th></tr></thead><tbody><tr><td>Account ID</td><td>true</td><td>unique identifier for your account</td></tr><tr><td>Account Name</td><td>true</td><td>account's organization name</td></tr><tr><td>Account URL</td><td>true</td><td>primary URL or domain associated with the account</td></tr><tr><td>Account LinkedIn URL</td><td>false</td><td>URL of the LinkedIn page associated with the account</td></tr><tr><td>Country</td><td>false</td><td>account's organization headquarter country</td></tr><tr><td>Address</td><td>false</td><td>account's organization headquarter address</td></tr><tr><td>Parent Account ID</td><td>false</td><td>unique identifier of the account's parent organization</td></tr><tr><td>Ultimate Parent Account ID</td><td>false</td><td>unique identifier of the account's topmost parent organization</td></tr></tbody></table>

Use whatever column names are natural for your system — we'll handle the column matching from there. Any additional columns you include can be carried through and used as context when matching your accounts to Sumble organizations.

Sumble reads whichever object you name, so this can be a table or a view. If the accounts you want enriched span several tables, or you only want to send us a subset, create a view in your own dataset and give us that instead.

### 2. Create the output dataset (push only)

Create a dataset in the region you want the enriched data to live in:

```bash
bq --location=US mk --dataset my-project:sumble
```

Skip this step if you'd rather Sumble host the dataset.

### 3. Grant access

In the Google Cloud console: open **BigQuery**, select the dataset, click **Sharing → Permissions → Add principal**, enter Sumble's service account, and pick the role from the table above.

Or run the equivalent DCL statements from the BigQuery console (or `bq query --use_legacy_sql=false`):

```sql
-- Pull: let Sumble read your input dataset
GRANT `roles/bigquery.dataViewer`
ON SCHEMA `my-project.crm`
TO "serviceAccount:customer-data-share-sa@sumble-358418.iam.gserviceaccount.com";

-- Push: let Sumble write to your output dataset
GRANT `roles/bigquery.dataEditor`
ON SCHEMA `my-project.sumble`
TO "serviceAccount:customer-data-share-sa@sumble-358418.iam.gserviceaccount.com";
```

To keep read access as narrow as possible, you can grant Data Viewer on the single input table instead of the whole dataset:

```sql
GRANT `roles/bigquery.dataViewer`
ON TABLE `my-project.crm.accounts`
TO "serviceAccount:customer-data-share-sa@sumble-358418.iam.gserviceaccount.com";
```

### 4. Send Sumble the identifiers

To finish configuration we need:

* **For pull** — the fully-qualified input table or view, `project.dataset.table` (and the same for contacts, if you're using champion tracking).
* **For push** — the fully-qualified output dataset, `project.dataset`. If you'd like Sumble to host it, send the principal that should be granted read access instead.

Sumble then configures the column mappings for your account fields, you choose which enrichments to include, and syncs begin.

## What gets delivered

Each pipeline run writes these tables into the output dataset:

* `enriched_organizations` — your accounts matched to Sumble organizations, with all configured enrichment columns
* `alerts_feed` — intent signals generated for your accounts
* `enrichments` — a description of each enrichment column in the share
* `organizations` — the underlying Sumble organization records
* `top_whitespace` — high-potential organizations that aren't in your CRM (if enabled)

The exact set depends on what's configured for your account — any additional lookup or reference tables in your share land in the same dataset. Table names are stable across runs, and each table is replaced in full on every run.

Table and column names are lowercase. BigQuery table names are case-sensitive, so query `enriched_organizations`, not `ENRICHED_ORGANIZATIONS`.

Each table is replaced independently, so if a run fails partway through, some tables can briefly hold the previous run's data while others hold the current run's. This clears on the next successful run. If you join across tables and need them to agree, the `FOR SYSTEM_TIME AS OF` queries below let you read every table as of the same timestamp.

### Reading JSON columns

Columns that carry JSON — enrichment evidence columns, and fields such as `extra_content` and `match_details` where they're included in your share — are loaded as `STRING`, not BigQuery's native `JSON` type. Use `PARSE_JSON()` or `JSON_VALUE()` to read them:

```sql
SELECT
  account_id,
  JSON_VALUE(my_enrichment_evidence, '$.job_title') AS job_title
FROM `my-project.sumble.enriched_organizations`;
```

(If you also receive a Snowflake share, note that those same columns arrive there as `VARIANT`.)

### Reading a previous run

Replacing a table doesn't discard its previous contents. BigQuery keeps them for the dataset's time travel window — seven days by default — so you can read any table as it stood at an earlier point:

```sql
SELECT *
FROM `my-project.sumble.enriched_organizations`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY);
```

This is useful for comparing runs, or for reading several tables as of one timestamp. To keep a copy for longer than the time travel window, take a [table snapshot](https://cloud.google.com/bigquery/docs/table-snapshots).

## Update cadence

Data is refreshed daily, after the Sumble data pipeline completes.

<a href="https://calendly.com/d/cnzk-sjk-q38/sumble" class="button primary">Book a time to chat with us</a>