Connectors → Snowflake

About Snowflake

Snowflake is a cloud data warehouse with a SQL interface. Snowflake stores both structured and semi-structured data, including JSON and XML.

Snowflake Connector Updates

This section is to explore the updates in the newer versions of the Snowflake connector available on the Incorta connectors marketplace.

In order to get the newer version of the connector, please update the connector using the marketplace.

Version	Updates
2.2.0.2	A new extra option, sql.chunks.limit, is now available to specify the maximum number of chunks that can be created by a data set when chunking is enabled. The default is 100, and the maximum is 300. If you specify a limit that exceeds 300, the connector resets it to 300.

Recommendation

Keep your connector up-to-date with the latest connector version released to get all introduced fixes and enhancements.

About the Snowflake Connector

The Incorta Snowflake connector uses the Snowflake JDBC Driver to connect to Snowflake data, which lets you access information in SQL databases using standard SQL queries.

The Snowflake connector supports the following Incorta-specific functionality:

Feature	Supported
Chunking	✔
Data Agent
Encryption at Ingest
Incremental Load	✔
Multi-Source
OAuth
Performance Optimized	✔
Remote
Single-Source	✔
Spark Extraction	✔
Webhook Callbacks	✔

Steps to connect Snowflake and Incorta

To connect Snowflake and Incorta, here are the high level steps, tools, and procedures:

Create an external data source
Create a schema with the Schema Wizard
or, Create a schema with the Schema Designer
Load the schema
Explore the schema

Create an external data source

Here are the steps to create an external data source with the Snowflake connector:

Sign in to the Incorta Direct Data Platform™.
In the Navigation bar, select Data.
In the Action bar, select + New → Add Data Source.
In the Choose a Data Source dialog, in Database, select Snowflake.
In the New Data Source dialog, specify the applicable connector properties.
To test, select Test Connection.
Select Ok to save your changes.

Snowflake connector properties

Here are the properties for the Snowflake connector:

Property	Control	Description
Data Source Name	text box	Enter the name of the data source
Username	text box	Enter the username of the Snowflake account used to authenticate to the server
Password	text box	Enter the password of the Snowflake account used to authenticate to the server
Connection Pool	text box	Enter the connection pool. The default is 30.
Connection String	text box	Enter the connection string for the Snowflake JDBC driver. The format is: `jdbc:snowflake://<HOST>:<PORT>` The `<HOST>` is `<ACCOUNT_NAME>.snowflakecomputing.com`, where `<ACCOUNT_NAME>` specifies the full name of your account provided by Snowflake. Note that your full account name might include additional segments that identify the region and cloud platform where your account is hosted. The `<PORT>` default is `443` for HTTPS. Refer to the connection properties row to definewhich database should be connected.
Set Extraction Timeout?	toggle	Enable this property to terminate the extraction job for a SQL query after a specified time.
Stop After (Minutes)	text box	Enable Set Extraction Timeout? to configure this property. Enter the maximum number of minutes that the extraction job for a SQL query can run before the system automatically terminates it.
Connection Properties	text box	Optionally enter connector properties for a custom connection to Snowflake in the format: `propertyName=propertyValue`, where each connector property is on a new line. A common example is setting a database: `db=<DATABASE_NAME>`. Refer to the Connection Parameters section of the Snowflake JDBC Driver documentation for further information.
Extra Options	text box	Enter supported extra options in the form of `key=value`.

Create a schema with the Schema Wizard

Here are the steps to create a Snowflake schema with the Schema Wizard:

Sign in to the Incorta Direct Data Platform™.
In the Navigation bar, select Schema.
In the Action bar, select + New → Schema Wizard.
In (1) Choose a Source, specify the following:
- For Enter a name, enter the schema name.
- For Select a Datasource, select the Snowflake external data source.
- Optionally create a description.
In the Schema Wizard footer, select Next.
In (2) Manage Tables, in the Data Panel, first select the name of the Data Source, and then check the Select All checkbox.
In the Schema Wizard footer, select Next.
In (3) Finalize, in the Schema Wizard footer, select Create Schema.

Create a schema with the Schema Designer

Here are the steps to create a Snowflake schema using the Schema Designer:

Sign in to the Incorta Direct Data Platform™.
In the Navigation bar, select Schema.
In the Action bar, select + New → Create Schema.
In Name, specify the schema name, and select Save.
In Start adding tables to your schema, select SQL Database.
In the Data Source dialog, specify the Snowflake table data source properties.
Select Add.
In the Table Editor, in the Table Summary section, enter the table name.
To save your changes, select Done in the Action bar.

Snowflake table data source properties

For a schema table in Incorta, you can define the following Snowflake specific data source properties as follows:

Property	Control	Description
Type	drop down list	The default is SQL Database
Data Source	drop down list	Select the Snowflake external data source
Incremental	toggle	Enable the incremental load configuration for the schema table
Fetch Size	text box	Used for performance improvement, fetch size defines the number of records that will be retrieved from the database in each batch until all records are retrieved. The default is 5000.
Incremental Extract Using	drop down list	Enable Incremental to configure this property. Select one of the following options: Last Successful Extract Time Maximum Value of a Column
Incremental Column	drop down list	Enable Incremental and select Maximum Value of a Column for Incremental Extract Using to configure this property. Select the column to be used for Maximum Value of a Column. The Loader will track and use the greatest value or most recent timestamp for each load operation.
Set Extraction Timeout?	toggle	Enable Incremental to configure this property. Enable this property to terminate the extraction job for an incremental SQL query after a specified time. This configuration will override the data source configuration.
Stop After (Minutes)	text box	Enable Incremental and Set Extraction Timeout? to configure this property. Enter the maximum number of minutes that the extraction job for an incremental SQL query can run before the system automatically terminates it. This configuration will override the data source configuration.
Query	text box	Enter the query to retrieve data from the Snowflake database
Update Query	text box	Enable Incremental to configure this property. Enter the query to retrieve data updates from the Snowflake database.
Incremental Field Type	drop down list	Enable Incremental to configure this property. Select the format of the incremental field: Timestamp Unix Epoch (seconds) Unix Epoch (milliseconds)
Chunking Method	drop down list	Chunking methods allow for parallel extraction of large tables. The default is No Chunking. There are two chunking methods: By Size of Chunk (Single Table) By Timestamp Chunking is available only when you do not Enable Spark Based Extraction.
Chunk Size	text box	Select By Size of Chunking for Chunking Method to configure this property. Enter the number of records to extract in each chunk in relation to the Fetch Size. The default is three times the Fetch Size.
Order Column	drop down list	Select a Chunking Method to configure this property. Select a column in the source table you want to order by before chunking. For a Chunking Method of By Size of Chunking, it is typically an ID column and must be numeric. For a Chunking Method of By Timestamp, it is a timestamp column.
Chunk Period	drop down list	Select the chunk period to use in dividing the chunks: Daily Weekly (default) Monthly Yearly Custom
Number of Days	text box	Select Custom for Chunk Period to configure this property. Enter the chunking period in days.
Upper Bound for Order Column	text box	Optional. Enter the maximum value for the order column.
Lower Bound for Order Column	text box	Optional. Enter the minimum value for the order column.
Enable Spark Based Extraction	toggle	Enable a Spark job to parallelize the data ingest. Spark Based Extraction is available only when the Chunking Method is No Chunking.
Max Number of Parallel Queries	text box	Enable Spark Based Extraction to configure this property. Enter the maximum number of parallel queries to run at a time.
Column to Parallelize Queries on	drop down list	Enable Spark Based Extraction to configure this property. Select a numerical column in the source table that you want Spark to parallelize the extraction queries on.
Memory Per Extractor	text box	Enable Spark Based Extraction to configure this property. Enter the numerical amount of memory to use per extractor in gigabytes (GB).
Callback	toggle	Enable this option to call back on the source data set
Callback URL	text box	Enable Callback to configure this property. Specify the URL.

View the schema diagram with the Schema Diagram Viewer

Here are the steps to view the schema diagram using the Schema Diagram Viewer:

Sign in to the Incorta Direct Data Platform™.
In the Navigation bar, select Schema.
In the list of schemas, select the Snowflake schema.
In the Schema Designer, in the Action bar, select Diagram.

Load the schema

Here are the steps to perform a Full Load of the Snowflake schema using the Schema Designer:

Sign in to the Incorta Direct Data Platform™.
In the Navigation bar, select Schema.
In the list of schemas, select the Snowflake schema.
In the Schema Designer, in the Action bar, select Load → Full Load.
To review the load status, in Last Load Status, select the date.

Explore the schema

With the full load of the Snowflake schema complete, you can use the Analyzer to explore the schema, create your first insight, and save the insight to a new dashboard.

To open the Analyzer from the schema, follow these steps:

In the Navigation bar, select Schema.
In the Schema Manager, in the List view, select the Snowflake schema.
In the Schema Designer, in the Action bar, select Explore Data.

Additional Considerations

Types of Incremental Loads

You can enable Incremental Load for a Snowflake data source. There are two types of incremental extracts:

Last Successful Extract Time

Fetch updates since the last time the tables were loaded. It is determined by the difference between the current time and the database timestamp.

Maximum Value of a Column

The column-based strategy depends on an extra column called Incremental Column in each table. The Snowflake connector supports both timestamp and numeric columns. A timestamp column is of the type date or timestamp. A numeric column is of the type int or long.

Note

Changing the incremental load strategy requires a full load to ensure data integrity.

Incremental Load Example

In this example, the invoices table must contain a column of the type Date or Timestamp in order to load the table incrementally with a last successful extract time strategy. In this case, the name of the date column is ModifiedDate and the format of the column is Timestamp.

Here are the data source property values for this example:

Incremental is enabled
Query contains SELECT * FROM invoices
Update Query contains SELECT * FROM invoices WHERE ModifiedDate > ?
Incremental Field Type = Timestamp

Note

? is a variable in the update query that contains the last schema refresh date. When running an update query for an incremental load, the ? character will be replaced with the last incremental reference to construct a valid query to the database. The ? reference character is not valid in a standard query.