Snowflake Betalink
Updated 8 days ago
Snowflake is a cloud-based data warehousing platform that allows organizations to store and analyze large volumes of data in a scalable and cost-effective manner. The Fivetran Snowflake connector allows you to sync tables and views from one Snowflake database to your destination.
NOTE: Fivetran supports Snowflake as both a database connector and a destination.
We can sync any number of schemas, tables, and views from within your Snowflake database. Fivetran supports regular views.
TIP: If you want to sync multiple Snowflake databases, we recommend that you create a connector for each database.
Featureslink
| Feature Name | Supported | Notes |
|---|---|---|
| Capture deletes | check | All tables and fields |
| Custom data | check | All tables and fields |
| Data blocking | check | Column level, table level, and schema level |
| Column hashing | check | |
| Re-sync | check | Table level |
| History | check | |
| API configurable | check | |
| Priority-first sync | ||
| Fivetran data models | ||
| Private networking | check | AWS PrivateLink, Azure Private Link, and Google Cloud Private Service Connect |
Setup guidelink
Follow our step-by-step Snowflake setup guide to connect your Snowflake database with Fivetran.
Sync overviewlink
Once Fivetran is connected to your database, we pull a full dump of all selected data from your database. We then use Fivetran Teleport Sync to pull all your new and changed data at regular intervals. If data in your source database changes (for example, you add new tables or change a data type), Fivetran automatically detects and persists these changes into your destination.
Schema informationlink
Fivetran tries to replicate the exact schema, tables and views from your Snowflake source database to your destination.
We name the source schema with the dataset name you provided in the connector setup form. We replicate the source's tables and views to the destination schema. We name the destination schema with the connector name appended by the dataset name. For example, if the connector name is snowflake_db and the dataset name is fivetran_schema, the destination schema name is snowflake_db_fivetran_schema.
Type transformations and mappinglink
As we extract your data, we match Snowflake data types to types that Fivetran supports. If we don't support a data type, we automatically change that type to the closest supported type or, in some cases, don't load that data at all. Our system automatically skips columns with data types that we don't accept or transform.
The following table illustrates how we transform your Snowflake data types into Fivetran-supported types for regular and primary key columns:
| Data Type | Fivetran Type | Regular Column Support | Primary Key Support (Teleport) |
|---|---|---|---|
| NUMBER* (s = 0) | One of the following types: - SHORT (p <= 4) - INTEGER (p <= 9) - LONG (p <= 18) | Yes | Yes |
| NUMBER* (s > 0 OR p > 18) | BIGDECIMAL | Yes | No |
| FLOAT | BIGDECIMAL | Yes | No |
| TEXT (with ASCII only) | STRING | Yes | Yes (Up to a length of 256 bytes) |
| TEXT (with Unicode) | STRING | Yes | No |
| TEXT | STRING | Yes | Yes (Up to a length of 256 bytes) |
| BOOLEAN | BOOLEAN | Yes | No |
| TIME | STRING | Yes | No |
| TIMESTAMP_NTZ | LOCALDATETIME | Yes | No |
| TIMESTAMP_TZ | INSTANT | Yes | No |
| TIMESTAMP_LTZ | INSTANT | Yes | No |
| DATE | LOCALDATE | Yes | No |
| BINARY | BINARY | Yes | Yes (Length must be a multiple of 4 up to a max of 128 bytes and values must not contain leading zeros) |
| VARIANT | STRING | Yes | No |
| ARRAY | STRING | Yes | No |
| OBJECT | JSON | Yes | No |
| GEOGRAPHY | JSON | Yes | No |
| GEOMETRY | JSON | Yes | No |
*NOTE: The
NUMBERtype has a precisionpand a scales. The maximum supported values arep = 38ands = 37. If we detect a precision or scale that exceeds those maximums, we map that data to the STRING data type and update the destination column type to STRING.
Updating datalink
Fivetran performs incremental updates of any new or modified data from your source database. We use Fivetran Teleport Sync, our proprietary database replication method, to fetch only the data that has changed since our last sync. Learn more in our Fivetran Teleport Sync documentation.
Fivetran Teleport Sync's queries perform the following operations on your database:
- Do a full table scan of each synced table
- Perform calculations on all values in each synced table's rows
- Aggregate a compressed table snapshot in the database's memory
For optimum Fivetran Teleport Sync performance, we recommend that you make the following resources available in your database:
- 1 GB Free RAM
- 1 Free CPU Core
- IOPS (Teleport Sync times decrease linearly with an increase of available IOPS).
NOTE: Teleport captures deletes differently than log-based replication mechanisms. Learn more in our Changes to data between syncs documentation.
Automatic table re-syncslink
For tables with a primary key, we support ADD COLUMN DDL operations with null or default static values. All other table or column alterations trigger an automatic table re-sync. For tables without a primary key, any DDL operation triggers an automatic table re-sync.
Tables with a primary keylink
We merge changes to tables with primary keys into the corresponding tables in your destination:
- An INSERT in the source table generates a new row in the destination with
_fivetran_deleted=FALSE. - A DELETE in the source table updates the corresponding row in the destination with
_fivetran_deleted=TRUE. - An UPDATE in the source table updates the data in the corresponding row in the destination.
If any primary key column has an unsupported data type, then the table is automatically treated as primary key-less (which means create table permissions are required on the database or the workspace database).
Tables without a primary keylink
For tables without a primary key, we designate the _fivetran_id column as the primary key. We populate this column with the hash of the values of each row.
Recommendationslink
- If you want to sync tables with primary keys, we recommend that the combined size of all the tables does not exceed 100 GB.
- If you want to sync tables without primary keys, we recommend that the combined size of all the tables does not exceed 10 GB. Contact our support team for assistance with your syncs.
Deleted columnslink
If you delete a column in a source table, we replace all values in the deleted column with NULL values in the destination table.