# Snowflake Destination Reference

Snowflake is a SQL data warehouse built from the ground up for the cloud, designed with a patented new architecture to handle today’s and tomorrow’s data and analytics.

A fully-managed SaaS data warehouse solution, Snowflake runs on [Amazon Web Services](http://aws.amazon.com/) cloud infrastructure: AWS EC2 virtual compute instances are used for compute needs, while S3 is utilized for persistent data storage.

This guide serves as a reference for version 1 of Stitch’s Snowflake destination.

## Details and features

* [Stitch features](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#stitch-features)

* [Destination details](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#destination-details)

* [Snowflake pricing](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#pricing)

### Stitch features

High-level details about Stitch’s implementation of Snowflake, such as supported connection methods, availability on Stitch plans, etc.

| **Release status<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | Released |
| --- | --- |
| **Stitch plan availability<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | All Stitch plans |
| **Stitch supported regions<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | <ul><li>North America (AWS us-east-1)</li><li>Europe (AWS eu-central-1)</li></ul><br><br>Operating regions determine the location of the resources Stitch uses to process your data. [Learn more](https://help.qlik.com/en-US/stitch/security/supported-data-pipeline-regions). |
| **Supported versions<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | Not applicable |
| **Connect API availability<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | **Supported<br>the Success iconSupported**<br><br>This version of the Snowflake destination can be created and managed using Stitch’s Connect API. [Learn more](https://help.qlik.com/en-US/stitch/developers/stitch-connect/api#destination-form-properties). |
| **SSH connections<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | **Unsupported<br>the Remove iconNot supported**<br><br>Stitch does not support using SSH tunnels to connect to Snowflake destinations. |
| **SSL connections<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | **Supported<br>the Success iconSupported**<br><br>Stitch will attempt to use SSL to connect by default. No additional configuration is needed. |
| **VPN connections<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | **Unsupported<br>the Remove iconNot supported**<br><br>Virtual Private Network (VPN) connections may be implemented as part of a Premium plan. Contact [Stitch Sales](https://www.stitchdata.com/contact/) for more info. |
| **Static IP addresses<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | **Supported<br>the Success iconSupported**<br><br>This version of the Snowflake destination has static IP addresses that can be whitelisted. |
| **Default loading behavior<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | **Upsert**<br><br>**Note**: Append-Only loading will be used if all conditions for Upsert are not met. [Learn more](https://help.qlik.com/en-US/stitch/replication/loading/understanding-loading-behavior). |
| **Nested structure support<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | **Supported**<br><br>the Success icon<br><br>Supported<br><br><br>Nested data structures will be loaded intact into `VARIANT` columns. [Learn more](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#transformations--json-structures). |

### Destination details

Details about the destination, including object names, table and column limits, reserved keywords, etc.

**Note**: Exceeding the limits noted below will result in loading errors or rejected data.

| **Maximum record size<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | 20MB |
| --- | --- |
| **Table name length<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | 255 characters |
| **Column name length<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | 251 characters |
| **Maximum columns per table<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | None |
| **Maximum table size<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | None |
| **Maximum tables per database<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | 100,000 |
| **Case sensitivity<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | Insensitive |
| **Reserved keywords<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference)** | Refer to the [Reserved keywords documentation](https://help.qlik.com/en-US/stitch/replication/loading/reserved-keywords#snowflake-v1-reserved-keywords). |

### Snowflake pricing

Snowflake pricing is based on two factors: The volume or data stored in your Snowflake destination and the amount of compute usage (the time the server runs) in seconds.

Snowflake offers two types of plans, each with varying levels of access and features. There are On Demand plans which are commitment-free and usage-based. The alternative is a Capacity option, which guarantees secure price discounts. [Learn more about Snowflake plans and pricing here](https://www.snowflake.net/product/pricing/).

#### Snowflake warehouse sizes

Snowflake data warehouses can be different sizes - X-Small, Large, and 3X-Large, for example - which defines how many servers will comprise each cluster in a warehouse.

While the size of a warehouse can impact the time required to execute queries, bigger doesn’t always mean better. Warehouse size is directly tied to the number of credits used, which will directly impact your Snowflake costs. [Learn more about Snowflake warehouse sizes here](https://docs.snowflake.net/manuals/user-guide/warehouses-overview.html).

To help you select the warehouse size that fits your needs and budget, check out [Snowflake’s Warehouse Considerations guide](https://docs.snowflake.net/manuals/user-guide/warehouses-considerations.html) before getting started.

#### Automated warehouse management

To reduce usage, you can elect to automate the management of your Snowflake warehouse. This means that you can elect to suspend the warehouse when there’s no activity after a specified period of time, and then automatically resume when there is. Note that these settings apply to the entire warehouse and not individual clusters.

Enabling these settings depends on your workload and availability needs. [Learn more about the Auto Suspend and Auto Resume features here](https://docs.snowflake.net/manuals/user-guide/warehouses-considerations.html#automating-warehouse-management).

**Note**: Stitch will only ever impact your Snowflake usage when loading data.

## Replication

* [Replication process overview](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#replication-process-overview)

* [Loading behavior](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#loading-behavior)

* [Primary Keys](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#replication--primary-keys)

* [Incompatible sources](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#replication--incompatible-sources)

### Replication process overview

A Stitch replication job consists of three stages:

* [Step 1: Data extraction](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#replication--data-extraction)

* [Step 2: Preparation](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#replication--stitch-internal-pipeline)

* [Step 3: Loading](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#replication--loading)

#### Step 1: Data extraction

Stitch requests and extracts data from a data source. Refer to the [System overview guide](https://help.qlik.com/en-US/stitch/getting-started/basic-concepts-system-overview#system-architecture--extraction) for a more detailed explanation of the Extraction phase.

#### Step 2: Preparation

During this phase, the extracted data is buffered in Stitch’s durable, highly available internal data pipeline and readied for loading. Refer to the [System overview guide](https://help.qlik.com/en-US/stitch/getting-started/basic-concepts-system-overview#system-architecture--preparing) for a more detailed explanation of the Preparation phase.

#### Step 3: Loading

Stitch loads the data into Snowflake.

### Loading behavior

By default, Stitch will use **Upsert loading** when loading data into Snowflake.

If the conditions for Upsert loading aren’t met, data will be loaded using Append-Only loading.

Refer to the [Understanding loading behavior guide](https://help.qlik.com/en-US/stitch/replication/loading/understanding-loading-behavior) for more info and examples.

### Primary Keys

Stitch requires Primary Keys to de-dupe incrementally replicated data. When tables are created in the destination, Stitch will apply [Primary Key constraints](https://docs.snowflake.net/manuals/sql-reference/constraints-overview.html) to columns used as Primary Keys. Primary Key constraints require that column values be unique and not null.

**Note**: Primary Keys cannot be changed in the destination.

### Incompatible sources

No compatibility issues have been discovered between Snowflake and Stitch's integration offerings.

See all [destination and integration incompatibilities.](https://help.qlik.com/en-US/stitch/destinations/destination-integration-compatibility)

## Transformations

* [System tables and columns](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#transformations--system-tables-columns)

* [Data typing](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#transformations--data-typing)

* [JSON structures](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#transformations--json-structures)

* [Column names](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#transformations--column-naming)

* [Timezones](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference#transformations--timezones)

### System tables and columns

Stitch will create the following tables in each integration’s dataset:

* [_sdc_rejected](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table)

Additionally, Stitch will insert [system columns](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns) (prepended with `_sdc`) into each table.

### Data typing

Stitch converts data types only where needed to ensure the data is accepted by Snowflake. In the table below are the data types Stitch supports for Snowflake destinations, and the Stitch types they map to.

* **Stitch type:** The Stitch data type the source type was mapped to. During the Extraction and Preparing phases, Stitch identifies the data type in the source and then maps it to a common Stitch data type.
* **Destination type:** The destination-compatible data type the Stitch type maps to. This is the data type Stitch will use to store data in Snowflake.
* **Notes:** Details about the data type and/or its allowed values in the destination, if available. If a range is available, values that exceed the noted range will be [rejected by Snowflake](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table).

| **Stitch type** | **Destination type** | **Notes** |
| --- | --- | --- |
|  | [](https://help.qlik.com/en-US/stitch/destinations/snowflake/reference) |  |
| BIGINT | [NUMBER](https://docs.snowflake.net/manuals/sql-reference/data-types-numeric.html#int-integer-bigint-smallint-tinyint-byteint) | <ul> <li> <p><strong>Description</strong>: Stored as <code>NUMBER(38,0)</code></p> </li> <li> <p><strong>Range </strong></p><div><strong><button>the Warning 2 icon</button><div>Values that exceed this range will be rejected by the destination and logged in the &lt;strong&gt;_sdc_rejected&lt;/strong&gt; table. &lt;a href='/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table'&gt;Learn more&lt;/a&gt;.</div></strong></div><strong>:</strong>Limited to 38 digits of scale<p></p> </li> </ul> |
| BOOLEAN | [BOOLEAN](https://docs.snowflake.net/manuals/sql-reference/data-types-logical.html#boolean) |  |
| DATE | [TIMESTAMP_TZ](https://docs.snowflake.net/manuals/sql-reference/data-types-datetime.html#timestamp-ltz-timestamp-ntz-timestamp-tz) | <ul> <li> <p><strong>Description</strong>: Stored in UTC as <code>TIMESTAMP_TZ(9)</code></p> </li> </ul> |
| DOUBLE | [FLOAT](https://docs.snowflake.net/manuals/sql-reference/data-types-numeric.html#float-float4-float8) |  |
| FLOAT | [FLOAT](https://docs.snowflake.net/manuals/sql-reference/data-types-numeric.html#float-float4-float8) |  |
| INTEGER | [NUMBER](https://docs.snowflake.net/manuals/sql-reference/data-types-numeric.html#int-integer-bigint-smallint-tinyint-byteint) | <ul> <li> <p><strong>Description</strong>: Stored as <code>NUMBER(38,0)</code></p> </li> <li> <p><strong>Range </strong></p><div><strong><button>the Warning 2 icon</button><div>Values that exceed this range will be rejected by the destination and logged in the &lt;strong&gt;_sdc_rejected&lt;/strong&gt; table. &lt;a href='/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table'&gt;Learn more&lt;/a&gt;.</div></strong></div><strong>:</strong>Limited to 38 digits of scale<p></p> </li> </ul> |
| NUMBER | [DECIMAL](https://docs.snowflake.net/manuals/sql-reference/data-types-numeric.html#decimal-numeric) | <ul> <li> <p><strong>Range </strong></p><div><strong><button>the Warning 2 icon</button><div>Values that exceed this range will be rejected by the destination and logged in the &lt;strong&gt;_sdc_rejected&lt;/strong&gt; table. &lt;a href='/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table'&gt;Learn more&lt;/a&gt;.</div></strong></div><strong>:</strong>Precision must be between 1 and 38; scale must be between 0 and the precision value<p></p> </li> </ul> |
| STRING | [VARCHAR](https://docs.snowflake.net/manuals/sql-reference/data-types-text.html#varchar) | <ul> <li> <p><strong>Description</strong>: Stored as <code>VARCHAR(16777216)</code>, which is the maximum length supported by Snowflake. No widening will occur if the string length changes.</p> </li> </ul> |

### JSON structures

When Stitch replicates source data containing objects or arrays, Stitch will load the data intact into a [`VARIANT` column](https://docs.snowflake.net/manuals/sql-reference/data-types-semistructured.html#variant). This is a Snowflake data type that can contain semi-structured data like JSON arrays and objects.

You can then use Snowflake’s functions for semi-structured data to parse the data. Refer to [Snowflake’s documentation](https://docs.snowflake.net/manuals/sql-reference/functions-semistructured.html) for more info.

### Column names

Column names in Snowflake:

* Must contain only letters (a-z, A-Z), numbers (0-9), or underscores (`_`)

* Must begin with a letter or underscore

* Must be less than the maximum length of 251 characters. Columns that exceed this limit will be [rejected by Snowflake](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table).

* Must not be prefixed or suffixed with any of [Snowflake’s or Stitch’s reserved keyword prefixes or suffixes](https://help.qlik.com/en-US/stitch/replication/loading/reserved-keywords#snowflake-v1-reserved-keywords)

Stitch will perform the following transformations to ensure column names adhere to the rules imposed by Snowflake:

| **Transformation** | **Source column** | **Destination column** |
| --- | --- | --- |
| Convert lowercase and mixed case to uppercase | `CuStOmErId` or `customerid` | `CUSTOMERID` |
| Replace spaces with underscores | `CUSTOMER ID` | `CUSTOMER_ID` |
| Remove special characters | `CUSTOMER#ID` or `!CUSTOMERID` | `CUSTOMERID` and `CUSTOMERID` |
| Remove non-letter leading characters | `4CUSTOMERID` or `_CUSTOMERID` | `CUSTOMERID` |

### Timezones

Snowflake will store the value as `TIMESTAMP_TZ(9)` and express it as UTC.

## Compare destinations

**Not sure if Snowflake is the data warehouse for you?** Check out the [Choosing a Stitch Destination](https://help.qlik.com/en-US/stitch/destinations/choosing-a-stitch-destination) guide to compare each of Stitch’s destination offerings.