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

# Snowflake

> Connect Snowflake to SegmentStream and import arbitrary tables or query results into your project's BigQuery dataset.

Snowflake is a general-purpose data source — instead of pulling from an ad platform API, SegmentStream runs a SQL query you write against a Snowflake warehouse and loads the results into a BigQuery table you choose. Use this to bring CRM tables, offline conversions, product catalogues, or any other Snowflake-hosted data alongside the rest of your SegmentStream data.

## Before you begin

You will need:

* A Snowflake **user** authenticated via key-pair (not password). Follow Snowflake's [key-pair authentication guide](https://docs.snowflake.com/en/user-guide/key-pair-auth) to generate an RSA key pair and register the public key on the user with `ALTER USER <user> SET RSA_PUBLIC_KEY='...'`.
* The **private key** in PEM format (starts with `-----BEGIN PRIVATE KEY-----`). Keep it safe — you'll paste it into SegmentStream during setup.
* A Snowflake **warehouse** the user can use, and privileges to `USAGE` on the warehouse, `USAGE` on the database and schema, and `SELECT` on the tables the query touches.
* (Optional) A dedicated **role** to scope permissions.

<Tip>
  We recommend creating a service user in Snowflake specifically for
  SegmentStream, with only the privileges required for the queries you plan
  to import. Rotating credentials is easier when the account is not shared.
</Tip>

## Example Snowflake setup

If you're setting up SegmentStream access from scratch, the pattern below is
a good starting point. It creates a dedicated role and service user, wraps
the source data in a secure view so only what you deliberately expose is
readable, and grants the minimum privileges needed.

Run as `ACCOUNTADMIN`:

```sql theme={null}
-- 1. Dedicated role for SegmentStream.
CREATE OR REPLACE ROLE SEGMENTSTREAM_ROLE;

-- 2. Service user, registering the public half of your RSA key pair.
--    Paste the key body between the quotes with no BEGIN/END lines
--    and no whitespace.
CREATE OR REPLACE USER SEGMENTSTREAM_SERVICE_USER
  TYPE = SERVICE
  RSA_PUBLIC_KEY = '<PUBLIC_KEY_BODY>'
  DEFAULT_ROLE = SEGMENTSTREAM_ROLE;

-- 3. (Optional) Proxy database + secure view so SegmentStream reads only
--    what you deliberately expose. Replace SOURCE_DB.SOURCE_SCHEMA.SOURCE_TABLE
--    with the table you want to import.
CREATE OR REPLACE DATABASE SEGMENTSTREAM_PROXY_DB;
CREATE OR REPLACE SCHEMA   SEGMENTSTREAM_PROXY_DB.ACCESS;

CREATE OR REPLACE SECURE VIEW SEGMENTSTREAM_PROXY_DB.ACCESS.MY_FEED AS
SELECT * FROM SOURCE_DB.SOURCE_SCHEMA.SOURCE_TABLE;

-- 4. Grant privileges. Replace COMPUTE_WH with the warehouse
--    SegmentStream should use.
GRANT USAGE  ON WAREHOUSE COMPUTE_WH                            TO ROLE SEGMENTSTREAM_ROLE;
GRANT USAGE  ON DATABASE  SEGMENTSTREAM_PROXY_DB                TO ROLE SEGMENTSTREAM_ROLE;
GRANT USAGE  ON SCHEMA    SEGMENTSTREAM_PROXY_DB.ACCESS         TO ROLE SEGMENTSTREAM_ROLE;
GRANT SELECT ON VIEW      SEGMENTSTREAM_PROXY_DB.ACCESS.MY_FEED TO ROLE SEGMENTSTREAM_ROLE;

-- 5. Assign the role to the service user.
GRANT ROLE SEGMENTSTREAM_ROLE TO USER SEGMENTSTREAM_SERVICE_USER;
```

<Tip>
  To rotate the RSA key later, run
  `ALTER USER SEGMENTSTREAM_SERVICE_USER SET RSA_PUBLIC_KEY='<NEW_KEY_BODY>'`
  as `ACCOUNTADMIN`. Update the corresponding private key in the SegmentStream
  data source configuration afterwards.
</Tip>

## Getting started

<Steps>
  <Step title="Navigate to Data Sources">
    Inside the admin panel navigate to the **Data Sources** page and click **+ NEW** on the top right corner.
  </Step>

  <Step title="Select Snowflake">
    Choose **Snowflake** from the list.
  </Step>

  <Step title="Provide the private key">
    Paste the **Private Key (PEM)** into the input field. If the key is encrypted, also fill in the **Private Key Passphrase**. Click **Save**.
  </Step>

  <Step title="Configure the connection">
    Fill in the connection and query fields (see [Configuration fields](#configuration-fields) below), then click **Save**.
  </Step>

  <Step title="Activate">
    Once the source is saved, click **Activate** in the paused banner (or the toolbar) to start daily imports.
  </Step>
</Steps>

## Configuration fields

<ResponseField name="Account" type="string" required>
  Your Snowflake account identifier, including the region and cloud
  (e.g. `xy12345.us-east-1` or `xy12345.eu-central-1.aws`). You can find it
  in the URL you use to log in to Snowflake — everything between `https://`
  and `.snowflakecomputing.com`.
</ResponseField>

<ResponseField name="User" type="string" required>
  The Snowflake username the private key is registered against.
</ResponseField>

<ResponseField name="Warehouse" type="string" required>
  The Snowflake warehouse used to run the query. The user must have `USAGE`
  privilege on this warehouse.
</ResponseField>

<ResponseField name="Role" type="string">
  Optional. The role Snowflake assumes when running the query. Leave empty
  to use the user's default role.
</ResponseField>

<ResponseField name="Database" type="string" required>
  The database that contains the tables you want to query.
</ResponseField>

<ResponseField name="Schema" type="string" required>
  The schema inside the database. Combined with `Database`, this is the
  search path Snowflake uses for unqualified table names in your query.
</ResponseField>

<ResponseField name="SQL Query" type="string" required>
  The `SELECT` statement executed on every daily run. Use `{{date_from}}`
  and `{{date_to}}` placeholders to filter to the daily window — see
  [Writing the query](#writing-the-query).
</ResponseField>

<ResponseField name="Destination table name" type="string" required>
  Name of the BigQuery table (inside your project's SegmentStream dataset)
  that the query result is loaded into. The table is created on the first
  successful run and re-populated on every subsequent run.
</ResponseField>

<ResponseField name="Partition table by date" type="boolean">
  When enabled, the destination table is date-partitioned by the daily
  window, and each run replaces the day's partition with the query
  results. When disabled, each run replaces the entire table.
</ResponseField>

## Writing the query

SegmentStream runs your query once per day, substituting the placeholders
`{{date_from}}` and `{{date_to}}` with the daily window as `YYYY-MM-DD`
strings. Wrap the placeholders in single quotes so Snowflake interprets them
as dates:

```sql theme={null}
SELECT
  order_id,
  customer_id,
  order_total,
  order_created_at
FROM analytics.crm.orders
WHERE order_created_at >= '{{date_from}}'
  AND order_created_at <  '{{date_to}}'
```

Keep the query deterministic — the same window should always return the
same rows, otherwise late-arriving updates will not be reflected in
BigQuery unless you turn on **Partition table by date** and let re-runs
replace older partitions.

<Warning>
  The query must return columns with names and types Snowflake can serialize
  to JSON. Complex types (`OBJECT`, `ARRAY`, `VARIANT`) are supported and
  land in BigQuery as `STRING` — cast to JSON strings if you need to preserve
  nested structure downstream.
</Warning>

## Destination table

The destination table lives in the SegmentStream-managed BigQuery dataset
for your project. The schema is inferred from the query output on the first
run and enforced on subsequent runs; adding a new column to the query
requires deleting the destination table and letting SegmentStream re-create
it.

When **Partition table by date** is enabled the table has a date partition
column matching the daily window, and each run replaces the partition for
its own window only — safe for backfills and re-runs. When it's disabled
the whole table is truncated and reloaded on every run.

## Troubleshooting

<AccordionGroup>
  <Accordion title="Authentication failed / JWT signature error">
    Check that the private key you pasted matches the public key registered
    on the Snowflake user with `DESC USER <user>`. The `RSA_PUBLIC_KEY_FP`
    fingerprint on Snowflake side should match the fingerprint of the
    private key you provided.
  </Accordion>

  <Accordion title="Warehouse suspended or user has no privilege">
    The Snowflake user must have `USAGE` on the warehouse and privileges to
    read from the database, schema, and tables the query references. Run
    `SHOW GRANTS TO USER <user>` in Snowflake to audit.
  </Accordion>

  <Accordion title="Query returned zero rows">
    Confirm that `{{date_from}}` and `{{date_to}}` cover the range you
    expect — the default is a one-day window ending at your project's most
    recent active date. If you use `>= date_from AND < date_to`
    (recommended), the window excludes the upper bound; adjust filters
    accordingly.
  </Accordion>

  <Accordion title="Data source paused after re-authorization">
    Re-authorizing an existing Snowflake source (for example when you
    rotate the private key) pauses the source as a safety step. Click
    **Activate** in the paused banner on the data source configuration
    form to resume imports.
  </Accordion>
</AccordionGroup>