> ## Documentation Index
> Fetch the complete documentation index at: https://docs.get3rd.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Snowflake

> Sync your 3RD visibility, citation, competitor and sentiment data into your own Snowflake account every day.

The Snowflake integration writes 3RD's **Export API datasets** into a schema in your own Snowflake account and refreshes them every day. Your data team can join AI-visibility data with web analytics, CRM and paid-media data in SQL, dbt or any BI tool that reads Snowflake.

It runs on the same data as the [Export API](https://docs.get3rd.com/api-reference/introduction.md), so the numbers match the API and the 3RD dashboard.

> [!NOTE]
>
> The managed sync is available on plans that include the Export API. If you would rather run the load yourself, see [Load it yourself with the Export API](#load-it-yourself-with-the-export-api).

## What you need

* A Snowflake user with `ACCOUNTADMIN`, or with `SECURITYADMIN` and `SYSADMIN`, to run the setup script once.
* Your Snowflake **account identifier** in the form `ORGNAME-ACCOUNTNAME`. Find it in Snowsight under your account menu → **Account** → **View account details**.
* The owner or admin role in your 3RD organization.

## Set it up

**1. Start the connection in 3RD**

Go to **Brand → Integrations**, and under **Reporting** open **Snowflake**. Click **Connect**. Enter your account identifier. The user, role, warehouse, database and schema names are filled in with defaults; change them if your naming rules require it.

**2. Run the setup script in Snowflake**

3RD shows a setup script with a public key filled in. Copy it into a Snowflake worksheet and run it. It creates a dedicated role, warehouse, schema and service user for 3RD:

```sql theme={null}
USE ROLE ACCOUNTADMIN;

CREATE ROLE IF NOT EXISTS "THIRD_SYNC_ROLE";

CREATE WAREHOUSE IF NOT EXISTS "THIRD_SYNC_WH"
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE DATABASE IF NOT EXISTS "THIRD";
CREATE SCHEMA IF NOT EXISTS "THIRD"."AI_VISIBILITY";

GRANT USAGE ON WAREHOUSE "THIRD_SYNC_WH" TO ROLE "THIRD_SYNC_ROLE";
GRANT USAGE ON DATABASE "THIRD" TO ROLE "THIRD_SYNC_ROLE";
GRANT USAGE, CREATE TABLE ON SCHEMA "THIRD"."AI_VISIBILITY" TO ROLE "THIRD_SYNC_ROLE";

CREATE USER IF NOT EXISTS "THIRD_SYNC_USER"
  TYPE = SERVICE
  DEFAULT_ROLE = THIRD_SYNC_ROLE
  DEFAULT_WAREHOUSE = THIRD_SYNC_WH;
ALTER USER "THIRD_SYNC_USER" SET RSA_PUBLIC_KEY = '<public key shown in 3RD>';

GRANT ROLE "THIRD_SYNC_ROLE" TO USER "THIRD_SYNC_USER";
```

**3. Test the connection**

Back in 3RD, click **Test connection**. If it fails, the message tells you which step to fix. See [Troubleshooting](#troubleshooting).

**4. Choose brands and datasets**

When the test passes, 3RD starts loading your history. Under **What to sync**, untick any tables or brands you don't want. 3RD then refreshes every day.

**5. Give your team read access**

The sync role can only write to its own schema. Grant read access to the roles your analysts use:

```sql theme={null}
GRANT USAGE ON DATABASE THIRD TO ROLE ANALYST;
GRANT USAGE ON SCHEMA THIRD.AI_VISIBILITY TO ROLE ANALYST;
GRANT SELECT ON ALL TABLES IN SCHEMA THIRD.AI_VISIBILITY TO ROLE ANALYST;
GRANT SELECT ON FUTURE TABLES IN SCHEMA THIRD.AI_VISIBILITY TO ROLE ANALYST;
```

> [!TIP]
>
> The person who uses 3RD is often not the person who administers Snowflake. Send the setup script to your data team; nothing in it is secret, because the private key never leaves 3RD.

## Security

* **No password.** 3RD signs in with key-pair authentication. It generates the key pair, keeps the private key encrypted, and shows you only the public key.
* **Least privilege.** The role can use one warehouse and create tables in one schema. It cannot read anything else in your account.
* **Write-only.** 3RD never reads your data. It writes and replaces only the tables it created.
* **Your compute.** The sync runs on the `XSMALL` warehouse you created, which suspends after a minute idle. A daily run takes seconds.

If your account uses a **network policy**, 3RD can't reach it yet: 3RD has no fixed IP addresses to add to the policy. Contact support before you connect.

## Tables

Each dataset becomes one table in your schema. Column names are the Export API field names in upper case; see the [Export API reference](https://docs.get3rd.com/api-reference/introduction.md) for what each field means.

| Table | Source | One row per |
| - | - | - |
| `BRANDS` | `/brands` | Brand |
| `VISIBILITY_DAILY` | `/visibility` | Date × model × provider × market |
| `CITATIONS_DAILY` | `/citations` | Date × cited URL |
| `COMPETITORS_DAILY` | `/competitors` | Date × competitor |
| `CITATION_SHARE_DAILY` | `/citation-share` | Date × cited URL |
| `RECOMMENDED_RATE_DAILY` | `/recommended-rate` | Date × brand or competitor |
| `SENTIMENT_DAILY` | `/sentiment` | Date |
| `_3RD_SYNC_RUNS` | — | Sync run, with rows written and status |

Every dated table also has `BRAND_ID`, which joins to `BRANDS.ID`, and `_SYNCED_AT`, the time the row was written.

## How the sync works

* **Daily refresh.** Each run reloads the **last 7 days**, because AI answers for recent days can still arrive. Older days don't change.
* **Atomic.** 3RD loads new rows into a staging table, then replaces the days it reloaded in one transaction. A failed run leaves your tables as they were.
* **History.** The first sync goes back as far as your plan's Export API history allows, up to two years. See [Pagination and history](https://docs.get3rd.com/api-reference/pagination.md).
* **New columns.** When 3RD adds a field to the Export API, the column is added to your table. Existing columns are never renamed or dropped.
* **Sync now.** Click **Sync now** in settings to run a sync outside the daily schedule, or **Reload history** to load your whole history again.

Check freshness from Snowflake:

```sql theme={null}
SELECT dataset, MAX(finished_at) AS last_success
FROM THIRD.AI_VISIBILITY._3RD_SYNC_RUNS
WHERE status = 'success'
GROUP BY dataset;
```

## Example query

Weekly visibility per AI model for one brand:

```sql theme={null}
SELECT
  DATE_TRUNC('week', v.DATE) AS week,
  v.MODEL_ID,
  SUM(v.VISIBLE_COUNT) / NULLIF(SUM(v.TOTAL_PROMPTS), 0) * 100 AS visibility_rate
FROM THIRD.AI_VISIBILITY.VISIBILITY_DAILY v
JOIN THIRD.AI_VISIBILITY.BRANDS b ON b.ID = v.BRAND_ID
WHERE b.NAME = 'Acme'
GROUP BY 1, 2
ORDER BY 1, 2;
```

Sum the counts and divide, rather than averaging `VISIBILITY_RATE`: a day with more answers should weigh more.

## Manage the connection

* **Rotate key.** In settings, click **Rotate key**. 3RD shows an `ALTER USER … SET RSA_PUBLIC_KEY_2 = '…'` statement (or `RSA_PUBLIC_KEY`, whichever slot is free). Run it, then click **Confirm new key**. Once the new key signs in, 3RD switches to it and shows the statement that removes the old one.
* **Disconnect.** Click **Disconnect**. 3RD deletes its private key and stops syncing. Your tables stay. To remove the user, run `DROP USER THIRD_SYNC_USER;`.
* **Plan changes.** If your plan no longer includes the Export API, syncing stops and your tables stay as they were.

## Troubleshooting

| Message in 3RD | What to check |
| - | - |
| Account not found | The account identifier. Use `ORGNAME-ACCOUNTNAME`, not the account URL or locator. |
| Authentication failed | The setup script ran, and `RSA_PUBLIC_KEY` holds the key shown in 3RD, without the `BEGIN`/`END` lines. |
| Insufficient privileges | The `GRANT` statements ran, and the schema name in 3RD matches the one you created. |
| Blocked by network policy | 3RD has no fixed IP addresses yet. Contact support. |
| Warehouse could not run the query | A resource monitor suspended the warehouse, or the 3RD role can't use it. Raise the quota, or reconnect with another warehouse. |

The Snowflake settings page in 3RD shows the latest error. After 14 days of failed syncs, 3RD stops syncing until you click **Sync now**.

## Load it yourself with the Export API

If you prefer to run the load inside Snowflake, call the Export API from a Snowpark stored procedure. You need an Export API key; see [Authentication](https://docs.get3rd.com/api-reference/authentication.md).

**1. Allow Snowflake to call 3RD**

```sql theme={null}
CREATE OR REPLACE NETWORK RULE THIRD_API_RULE
  MODE = EGRESS TYPE = HOST_PORT VALUE_LIST = ('app.get3rd.com');

CREATE OR REPLACE SECRET THIRD_API_KEY
  TYPE = GENERIC_STRING SECRET_STRING = '<your 3rd_live_ key>';

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION THIRD_API_ACCESS
  ALLOWED_NETWORK_RULES = (THIRD_API_RULE)
  ALLOWED_AUTHENTICATION_SECRETS = (THIRD_API_KEY)
  ENABLED = TRUE;
```

**2. Create the table and the procedure**

This example loads `/visibility`. Use the same pattern for the other endpoints, with the columns from the [Export API reference](https://docs.get3rd.com/api-reference/introduction.md).

```sql theme={null}
CREATE TABLE IF NOT EXISTS VISIBILITY_DAILY (
  BRAND_ID VARCHAR, DATE DATE, MODEL_ID VARCHAR, PROVIDER VARCHAR,
  MARKET_ID VARCHAR, MARKET VARCHAR,
  TOTAL_PROMPTS NUMBER, VISIBLE_COUNT NUMBER, VISIBILITY_RATE FLOAT
);

CREATE OR REPLACE PROCEDURE LOAD_3RD_VISIBILITY(BRAND_ID STRING, DAYS INT)
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.11'
  PACKAGES = ('snowflake-snowpark-python', 'requests')
  HANDLER = 'run'
  EXTERNAL_ACCESS_INTEGRATIONS = (THIRD_API_ACCESS)
  SECRETS = ('api_key' = THIRD_API_KEY)
AS
$$
import _snowflake
import requests
from datetime import date, timedelta

BASE = "https://app.get3rd.com/api/export/v1"
COLUMNS = ["DATE", "MODEL_ID", "PROVIDER", "MARKET_ID", "MARKET",
           "TOTAL_PROMPTS", "VISIBLE_COUNT", "VISIBILITY_RATE"]

def fetch_all(path, params, key):
    """Every page, plus the window the API actually served: it clamps
    fromDate to your plan's history without an error."""
    rows, cursor, served_from = [], None, None
    while True:
        query = dict(params, **({"cursor": cursor} if cursor else {}))
        res = requests.get(f"{BASE}/{path}", params=query,
                           headers={"X-API-Key": key}, timeout=60)
        res.raise_for_status()
        body = res.json()
        served_from = served_from or body["meta"]["fromDate"][:10]
        rows.extend(body["data"])
        if not body["pagination"]["hasMore"]:
            return rows, date.fromisoformat(served_from)
        cursor = body["pagination"]["nextCursor"]

def run(session, brand_id, days):
    key = _snowflake.get_generic_secret_string("api_key")
    to_date = date.today()
    rows, from_date = fetch_all("visibility", {
        "brandId": brand_id,
        "fromDate": (to_date - timedelta(days=days)).isoformat(),
        "toDate": to_date.isoformat(),
    }, key)

    # Stage first, outside the transaction, so a failed load never
    # leaves the table with the window deleted.
    session.sql(
        "CREATE OR REPLACE TEMPORARY TABLE VISIBILITY_STAGE LIKE VISIBILITY_DAILY"
    ).collect()
    if rows:
        data = [[brand_id] + [r.get(c.lower()) for c in COLUMNS] for r in rows]
        session.create_dataframe(data, schema=["BRAND_ID"] + COLUMNS) \
            .write.mode("append") \
            .save_as_table("VISIBILITY_STAGE", column_order="name")

    # Replace only the window the API served, in one transaction.
    session.sql("BEGIN").collect()
    try:
        session.sql(
            "DELETE FROM VISIBILITY_DAILY WHERE BRAND_ID = ? AND DATE BETWEEN ? AND ?",
            params=[brand_id, from_date, to_date],
        ).collect()
        session.sql(
            f"INSERT INTO VISIBILITY_DAILY (BRAND_ID, {', '.join(COLUMNS)}) "
            f"SELECT BRAND_ID, {', '.join(COLUMNS)} FROM VISIBILITY_STAGE"
        ).collect()
        session.sql("COMMIT").collect()
    except Exception:
        session.sql("ROLLBACK").collect()
        raise
    return f"{len(rows)} rows for {from_date}..{to_date}"
$$;
```

**3. Schedule it**

Get your brand ids from `/api/export/v1/brands`, load your history once, then schedule a daily refresh of the last 7 days:

```sql theme={null}
CALL LOAD_3RD_VISIBILITY('<brand id>', 90);

CREATE OR REPLACE TASK LOAD_3RD_DAILY
  WAREHOUSE = <your warehouse>
  SCHEDULE = 'USING CRON 0 6 * * * UTC'
AS
  CALL LOAD_3RD_VISIBILITY('<brand id>', 7);

ALTER TASK LOAD_3RD_DAILY RESUME;
```

## Related

* [Export API](https://docs.get3rd.com/api-reference/introduction.md) — the endpoints and fields behind the tables
* [Authentication](https://docs.get3rd.com/api-reference/authentication.md) — Export API keys
* [Microsoft Fabric](https://docs.get3rd.com/integrations/fabric.md) — the same data in Microsoft Fabric
* [Looker Studio connector](https://docs.get3rd.com/integrations/looker-studio.md) — the same data in Looker Studio
* [Metrics at a glance](https://docs.get3rd.com/metrics/overview.md) — what each metric means


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