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

# Data reference

> Query model, field type mapping, refresh cadence, and limits for the Snowflake data source in Adriel.

## Introduction

Snowflake is a cloud data warehouse. Customers store analytics, event, and business data in Snowflake tables and query it with standard SQL. The Adriel Snowflake connector (labelled **Snowflake (beta)** in the UI) binds one Adriel data source to one Snowflake table or view, discovers that object's columns from the schema at runtime, and lets those columns be used as metrics and breakdowns in widgets. When a widget loads, the connector auto-generates a SQL statement from the widget configuration, executes it against the warehouse, and returns the result.

As a database connector, the available fields are not a fixed catalog — they are derived from the schema of the bound table or view. Data types are mapped from native Snowflake types to Adriel field types: numeric columns become metrics, while every other column type surfaces as text and can serve as either a metric or a breakdown.

Because Snowflake's SQL syntax is close to PostgreSQL, schema discovery, filter operators, and default aggregation closely follow the PostgreSQL connector, with some Snowflake-specific differences noted throughout this page.

To connect this data source, see [How to connect Snowflake to Adriel](/data-sources/o-z/snowflake/how-to-connect).

## Data refresh strategy

Snowflake data is fetched **on demand**. There is no incremental sync — every dashboard, widget, or report load issues a live query against the bound table or view, so results always reflect the latest committed data in the warehouse.

**No query-result cache.** Unlike a cached warehouse path, query results are not stored between loads: each request re-runs the query against Snowflake. Every query also opens a fresh Snowflake connection and closes it immediately — there is no connection pooling.

**Field-metadata cache.** The column list (from `INFORMATION_SCHEMA.COLUMNS`) is cached through the V3 field system for **12 hours** per data source. Columns that no longer exist in Snowflake are removed on refresh (`deleteMissingFields`), and new columns become visible after the cache refreshes. The connector's legacy `deleteCache()` is a no-op.

## Architecture levels

Warehouse → Database → Schema → Table or view.

Data source setup is a four-level drill-down; each step is populated by a live query to Snowflake:

1. **Warehouse** — `SHOW WAREHOUSES` (displayed as `name (size)`).
2. **Database** — `SHOW DATABASES`.
3. **Schema** — `SHOW SCHEMAS in DATABASE {database}`.
4. **Table or view** — `SHOW VIEWS IN SCHEMA {db}.{schema}` plus `SHOW TABLES IN SCHEMA {db}.{schema}` (views listed first, then tables).

The table step is multi-select: selecting several tables creates one data source per table. One Adriel data source therefore binds to a single table or view. To expose another object, create another data source, or pre-join into a view and bind to that.

## Date range limits

There is no platform-imposed date range limit. Query bounds are set by the widget's date-range control combined with the date column configured for the data source (`dateField` / `dateFormat`) in its settings. If no date field is configured, or if the requested range has no start or end, no date filter is applied and the full object is scanned (subject to the row cap). Date bounds are always expressed as separate `>=` and `<=` conditions rather than a single `BETWEEN`.

<Note>
  **Snowflake data sources have no date breakdown**

  The connector does not expose a time/date breakdown (`hasDateBreakdown = false`). A date column can still be mapped as the data source's date field for date-range filtering and used as an ordinary breakdown, but there is no automatic day/week/month grouping.
</Note>

## Query model

SQL is auto-generated from widget configuration against the bound object using a Knex query builder configured with the PostgreSQL (`pg`) dialect, then executed as raw SQL through the Snowflake SDK. Each request produces a single statement:

```sql theme={null}
SELECT <breakdowns>, <metric aggregations>
FROM <schema>.<table>
WHERE <date range> [AND <widget filters>] [AND <connection row filter>]
GROUP BY <breakdowns>
ORDER BY <sort>
LIMIT 50000
```

Key rules applied by the query builder:

* **`{schema}.{table}` source.** The `FROM` clause targets the schema and table (or view) recorded on the asset.
* **`LIMIT 50000`.** Rows are capped at 50,000 unconditionally — the limit is hardcoded and cannot be raised per data source.
* **Breakdowns drive `GROUP BY`.** Breakdown columns become `SELECT` columns and `GROUP BY` keys; metric fields become aggregation expressions.
* **Filter fields auto-added to breakdowns.** A `WHERE` field that is not already a metric or breakdown is added to the breakdown set so the filter can be evaluated against the grouped result. Fields prefixed `RAW:` or containing `->` are excluded from this auto-injection.
* **Non-English column names.** Column names containing non-ASCII characters are quote-escaped for safe identifier handling.
* **Connection row filter applied last.** The optional connection-level pre-filter is appended after the date range and widget filters as an additional `AND` condition (see Filters).

<Note>
  **Each query runs live against the warehouse**

  Every widget load that reaches Snowflake consumes warehouse compute, and there is no query-result cache in front of it. Fewer widgets, fewer manual refreshes, and tighter filters translate directly to fewer and cheaper queries. Pairing a mapped date column with widget filters, or binding a pre-aggregated table or view, is the biggest lever for controlling cost and staying under the 50,000-row cap.
</Note>

## Filters

Widget filters translate into Snowflake `WHERE` conditions using a whitelisted operator set. Date-range filtering and an optional connection-level row filter are applied separately.

### Supported filter operators

The connector reuses the PostgreSQL operator set (`postgresFilterOperators`). The following operators are enabled for widget filters on Snowflake data sources:

* `EQUAL`
* `IN` (values including `Unknown` also match `IS NULL`)
* `LIKE`
* `STARTS_WITH`
* `ENDS_WITH`
* `REGEXP` (validated for safety; unsafe patterns are rejected)
* `EXIST`

The logical combinators `OR`, `AND`, and `NOT` are supported and can be nested. `GREATER_THAN` and `LESS_THAN` are defined but **not** enabled for server-side filtering; when used, they fall back to client-side post-filtering after the query returns.

<Note>
  **Snowflake uses `REGEXP_LIKE`, not the Postgres `~*` operator**

  For `LIKE`, `REGEXP`, `STARTS_WITH`, and `ENDS_WITH`, the connector emits Snowflake's `REGEXP_LIKE(column, pattern, 'i')` (case-insensitive) instead of the PostgreSQL `~*` regex operator. `LIKE` and `REGEXP` additionally run through a safety guard that rejects overly complex patterns.
</Note>

### Data-source pre-filters

Each data source can optionally carry a **connection-level row filter** defined by a `filterKey` and a `filterValue`. When both are set, a condition of the form `COALESCE(TO_VARCHAR(<filterKey>), '') ILIKE '%<filterValue>%'` is appended to every query, pre-filtering rows before aggregation. The `filterKey` supports nested paths (dot or colon notation, e.g. `payload.user.email`) that resolve to Snowflake JSON path segments, and `%`, `_`, and `=` in the value are escaped. Both `filterKey` and `filterValue` must be provided together — supplying only one raises a validation error.

### Date-range filtering

Date-range filtering is applied to the column configured as the date field in the data source settings (the `snowflake:` prefix is stripped first). The generated clause depends on the column's `dateFormat`:

| `dateFormat`          | Handling                                                                                         |
| --------------------- | ------------------------------------------------------------------------------------------------ |
| `ISO`, `TIMESTAMP_TZ` | `CAST(col AS DATE)` compared to ISO date strings                                                 |
| `TIMESTAMP_MS`        | `CAST(col AS bigint)` compared to millisecond epoch bounds (day boundaries)                      |
| `TIMESTAMP_S`         | `CAST(col AS bigint)` compared to second epoch bounds — see the note below                       |
| `EPOCH_TIMESTAMP`     | `to_timestamp(col)` compared to date strings                                                     |
| custom format         | `TO_DATE(CAST(col AS text), '<format>')`; single-letter `M`/`D` tokens are promoted to `MM`/`DD` |
| (none)                | Plain string comparison (`col >= from AND col <= to`)                                            |

<Note>
  **`TIMESTAMP_S` currently filters only the start date**

  For the `TIMESTAMP_S` format, both the lower and upper bound are computed from the range's start date, so the filter always covers exactly one day regardless of the requested range. Prefer `TIMESTAMP_MS`, `ISO`, or `TIMESTAMP_TZ` for date columns until this is addressed. *(Flagged for tech-reviewer confirmation.)*
</Note>

## Field type mapping

Snowflake has no fixed metric or breakdown catalog. The available fields are the columns of the bound table or view, resolved at query time from `INFORMATION_SCHEMA.COLUMNS` (`COLUMN_NAME`, `DATA_TYPE`, and `NUMERIC_SCALE`). All discovered columns are exposed as **both** fields and breakdowns; field names are prefixed with `snowflake:`.

<Note>
  **How to read the columns**

  The **Data type** column uses the platform's ten-value vocabulary: **Number**, **Currency**, **Percentage**, **Ratio**, **Duration**, **Date**, **Text**, **URL**, **Array**, **Boolean**. The **Field role** column indicates whether the field can serve as a metric, a breakdown, or both.
</Note>

Each native Snowflake type maps to an Adriel field type as follows. The mapping keys on the type and, for exact-numeric types, on the numeric scale.

| Native Snowflake type                                                                                                        | Condition           | Adriel field type | Data type | Field role                   |
| ---------------------------------------------------------------------------------------------------------------------------- | ------------------- | ----------------- | --------- | ---------------------------- |
| `FLOAT`, `FLOAT4`, `FLOAT8`, `DOUBLE`, `DOUBLE PRECISION`, `REAL`                                                            | —                   | `fullNumber`      | Number    | Metric or breakdown          |
| `NUMBER`, `NUMERIC`, `DECIMAL`, `INT`, `INTEGER`, `BIGINT`, `SMALLINT`, `TINYINT`, `BYTEINT`                                 | `NUMERIC_SCALE > 0` | `fullNumber`      | Number    | Metric or breakdown          |
| `NUMBER`, `NUMERIC`, `DECIMAL`, `INT`, `INTEGER`, `BIGINT`, `SMALLINT`, `TINYINT`, `BYTEINT`                                 | `NUMERIC_SCALE = 0` | `number`          | Number    | Metric or breakdown          |
| Strings, dates/timestamps, booleans, semi-structured (`VARIANT` / `OBJECT` / `ARRAY`), binary, geography, and any other type | —                   | `string`          | Text      | Metric or breakdown          |
| Any column whose `DATA_TYPE` is missing                                                                                      | —                   | —                 | —         | Excluded from the field list |

<Note>
  **No dedicated Date or Boolean field type**

  Like the PostgreSQL connector, the Snowflake type map classifies only numeric types as numbers; every other native type — including dates, timestamps, and booleans — falls through to **Text**. Date columns still work for date-range filtering when mapped as the data source's date field, but they are surfaced as Text rather than a Date data type.
</Note>

### Adriel-added fields

The Snowflake connector adds **no** synthetic fields — only the columns of the bound table or view are exposed. There is no synthetic row-count metric. To count rows, add a count column to a Snowflake view or pre-aggregated table. *(Inference — no synthetic field was found in the connector's query builder; confirm.)*

### Aggregation defaults

When no explicit per-field aggregation is set, the query builder chooses one from the column's type and name:

| Condition                                                    | Default aggregation                                                                        |
| ------------------------------------------------------------ | ------------------------------------------------------------------------------------------ |
| Numeric type, name does **not** end in `_id` / `ID`          | `SUM(COALESCE(CAST(col AS DOUBLE PRECISION), 0))`                                          |
| Numeric type, name ends in `_id` / `ID`                      | Truncated distinct-value list (up to 5 distinct values; `AND_MORE` appended if more exist) |
| Strings, dates, booleans, semi-structured, binary, geography | Truncated distinct-value list (same as above)                                              |
| Type in neither the summable nor the non-summable set        | No aggregation; the column is silently omitted from the result                             |

The truncated distinct-value list is built with `ARRAY_AGG(DISTINCT CAST(col AS VARCHAR))`, sliced to five elements, with `AND_MORE` appended when more than five distinct values exist. In the V3 field metadata, summable numeric types are assigned a `sum` aggregation and every other type is assigned `unique`. The `uniqueOrMultiValuesKey` and `uniqueOrNone` string-aggregation modes are **not** supported and raise an error if requested.

## Limitations

Based on the current connector (Snowflake is in **beta**):

* **50,000-row hard cap per widget query.** Every query applies `LIMIT 50000` unconditionally; the cap is not configurable and rows beyond it are silently truncated. For more granular reporting, pre-aggregate in Snowflake and bind the rolled-up object.
* **One table or view per data source.** Multi-table joins are not supported — pre-join into a Snowflake view.
* **Filter operator whitelist.** Only `EQUAL`, `IN`, `LIKE`, `STARTS_WITH`, `ENDS_WITH`, `REGEXP`, and `EXIST` (plus `OR` / `AND` / `NOT`) run server-side; `GREATER_THAN` and `LESS_THAN` are post-filtered client-side.
* **No dedicated Date or Boolean type.** Date, timestamp, and boolean columns surface as Text (see Field type mapping).
* **No date breakdown.** The connector exposes no automatic time grouping (`hasDateBreakdown = false`).
* **Columns with unmapped types are dropped.** A column whose type is in neither the summable nor the non-summable set is silently omitted from query results, and a column with a missing `DATA_TYPE` is excluded from the field list.
* **`TIMESTAMP_S` date filtering bug.** With the `TIMESTAMP_S` format, both range bounds use the start date, so only the start day is returned. *(Confirm with tech-reviewer.)*
* **No query-result cache.** Each load re-queries Snowflake; the field-metadata cache (12 hours) is the only caching layer, and `deleteCache()` is a no-op.
* **No connection pooling.** A fresh Snowflake connection is opened and closed for every query.
* **Key-pair (JWT) authentication only.** Authentication uses an account identifier, username, and PEM private key; password auth and OAuth are not supported, and there is no token refresh. The `role` field is collected in the connection form but has no effect. Re-authorization steps live in the paired how-to.

## API references (Snowflake)

* [Snowflake documentation](https://docs.snowflake.com/)
* [SQL data types](https://docs.snowflake.com/en/sql-reference/intro-summary-data-types)
* [`INFORMATION_SCHEMA.COLUMNS` view](https://docs.snowflake.com/en/sql-reference/info-schema/columns)
* [`REGEXP_LIKE` function](https://docs.snowflake.com/en/sql-reference/functions/regexp_like)
* [Key-pair authentication](https://docs.snowflake.com/en/user-guide/key-pair-auth)

## See also

* [How to connect Snowflake](/data-sources/o-z/snowflake/how-to-connect) (paired how-to)
* [Google BigQuery data reference](/data-sources/g-n/google-bigquery/data-reference) — alternative cloud data warehouse
* [Redshift data reference](/data-sources/o-z/redshift/data-reference) — alternative cloud data warehouse
* [PostgreSQL data reference](/data-sources/o-z/postgres/data-reference) — the Postgres-family SQL connector Snowflake most closely mirrors