Preparing BimlFlex for Snowflake

Before working with Snowflake metadata, you should ensure that your environment is configured to use Snowflake appropriately. This section will walk you through the required software and system configurations you will need to connect BimlFlex to your Snowflake environment.

Installing and Configuring SnowSQL (SSIS Only)

When using an SSIS Integration Template, BimlFlex uses a Snowflake Custom SSIS Component that leverages SnowSQL and the SnowSQL config file to connect and move data.

This requires that SnowSQL be installed and the appropriate connection created on the server executing the SSIS packages.

Follow the below link for instructions on how to install SnowSQL.

Tip

For additional details on installing SnowSQL refer to the following Snowflake Docs guides:

Once SnowSQL is installed, a connection needs to be created in the specified config file. The location may vary from depending on the installation but the default location for Windows is %USERPROFILE%\.snowsql\.

Open the file with any text editor and insert a connection following the template below.

# BimlFlex Connection Template
[connections.<ConnectionName>]
accountname = <SnowflakeAccount>
username = <UserName>
password = <Password>
dbname = <DatabaseName>
schemaname = <SchemaName>
warehousename = <WarehouseName>
region = <Region>

BimlFlex Snowflake Custom SSIS Components

The Snowflake load process uses a custom SSIS component to provide a robust query management experience in SSIS. The required components are installed and upgraded through the BimlFlex developer installation, or by using the separate runtime installation for custom components.

Note

If the components were not installed on initial installation they can be installed at a later date by re-running the installer. For additional details on installing BimlFlex and Custom Components refer to the BimlFlex installation guide.

Installing and Configuring a Snowflake ODBC DSN

The ODBC driver is only required if using SSIS orchestration to connect to a Snowflake Source System, or using the Metadata Importer to bring Snowflake metadata into the BimlFlex framework when Snowflake data sources are part of the solution.

Tip

For additional details on installing and configuring and ODBC driver and DSN, refer to the below Snowflake Docs guides:

Configuring BimlFlex for Snowflake

BimlFlex uses Settings to adapt to specific requirements for file locations, naming conventions, data conventions etc.

Align these settings with the organizations best practices and environmental requirements.

Snowflake Settings

The following Snowflake Snowflake-specific settings are used to configure overall orchestration options for Snowflake.

These settings define the connectivity configuration from BimlFlex to Snowflake, and are used in the resulting data solution when deployed to the target environment. For example, these settings are used to define the Linked Service and file locations.

Important

Please configure and review the above settings carefully, as they are essential for correct connectivity to Snowflake in the compiled data solution.

Azure Storage Processing Settings (ADF Only)

The following Azure Settings are used to configure the blob destinations. This is mandatory when using Snowflake.

Setting Key Setting Description
Archive Container The Container Name to use for the archive process
Archive Account Name The Azure Blob Storage Account Name to use for archiving data as files in blob storage
Archive Account Key A Storage access key to use when accessing the Blob storage
Archive SAS Token A Storage access SAS Token to use when accessing the Blob storage
Error Container The Container Name to use for the Error process
Error Account Name The Azure Blob Storage Account Name to use for error files in blob storage
Error Account Key A Storage access key to use when accessing the Blob storage
Error SAS Token A Storage access SAS Token to use when accessing the Blob storage
Stage Container The Container Name to use for the staging process
Stage Account Name The Azure Blob Storage Account Name to use for staging data as files in blob storage
Stage Account Key A Storage access key to use when accessing the Blob storage
Stage SAS Token A Storage access SAS Token to use when accessing the Blob storage
Blob Storage Domain The AzCopy domain to use

Configuring a Snowflake Connection

This section outlines any specific considerations needed when configuring BimlFlex to use Snowflake across the various Integration Stages.

Field Supported Values Guide
Integration Stage Source System*, Staging Area, Persistent Staging Area, Data Vault, Data Mart Details
Connection Type ODBC*, ODBC SQL Based ELT* Details
System Type Snowflake Data Warehouse Details
Connection String Dsn={DSN Name};Uid={User Name};Pwd={Password};Database={Database Name};* Details
Linked Service Type Snowflake (ADF Only) Details

Integration Stage

BimlFlex provides support for the use of Snowflake as both a target warehouse platform and as a Source System. It is highly recommended to use a single database that when using Snowflake as a target warehouse platform.

Naming patterns and schemas can be used for separation as needed.

The generated DDL does not currently support USE DATABASE or USE WAREHOUSE statements.

Deployment would require either 'Cherry Picking' the correct tables for each database or using Generate Script Options and scoping to a specific Integration Stage before each generation of the scripts.

Landing Area (ADF Only)

Snowflake is not currently supported as a Landing Area but a Landing Area is required when using ADF to orchestrate data movement.

The recommendation is for the Landing Area to be a Connection Type of Azure Blob Storage and System Type of Flat File Delimited.

In addition to the Landing Area it is also important that the *Settings for an Azure Blob Stage Container, Azure Blob Archive Container, and Azure Blob Error Container are populated correctly.

Important

Ensure that all Azure Blob Container Settings are configured properly:

Tip

Additional resources are available on the Microsoft Docs sections:

Connection Type

Currently BimlFlex only supports the Connection Type of ODBC for a Snowflake Connection when being used as the target data warehouse platform. When configuring a Snowflake Integration Stage of Source System, please set the Connection Type to ODBC.

System Type

The System Type should always be Snowflake Data Warehouse for any Snowflake Connection.

Connection String

The Snowflake Custom SSIS Component uses the connection information specified in the .SnowSQL\config file to connect. See the Snowflake SnowSQL Settings section for instructions on how to specify the file and connection to use.

BimlFlex only uses the Connection String field to generate an associated Connection Manager in SSIS. The associated Connection Manager will be created in SSIS using the inputted Connection String but will not be used during any of the generated SSIS orchestration.

Note

This guide only covers Connection String which is only used by SSIS orchestration. For details on configuring the values of a Connection String Secret see the ADF Connection String Example in Linked Services Section.

Dsn=`{DSN Name}`;Uid=`{User Name}`;Pwd=`{Password}`;Database=`{Database Name}`;
Tip

For additional details on creating a Connection refer to the below guide:

Linked Services (ADF Only)

The Azure Function Bridge will use the Linked Service configured in BimlFlex to determine the appropriate connection method to use.

When using an Azure Key Vault use the below example to assist with the creation of a connection string to be used as the the secret value.

If using a Connection String version of a Linked Service fill out the required fields.

host=`{Server Address}`;account=`{Account Name}`;user=`{User Name}`;password=`{Password}`;db=`{Database Name}`;schema=`{Schema Name}`;warehouse=`{Warehouse Name}`;
Tip

For additional details on creating a Linked Service refer to the below guides:

Deploying the target Warehouse Environment

When Snowflake is used as the target warehouse platform, BimlFlex will generate the required SnowSQL script for the deployment of all the tables, stored procedures, and the Data Vault default inserts (Ghost Keys).

Once generated the scripts can be manually deployed to the required database.

Generating SnowSQL Scripts

Using Snowflake as the target warehouse platform requires the generation of the Snowflake Table Script and Snowflake Procedure Script options when using Generate Scripts in BimlStudio. Additionally if Data Vault is being used the standard Data Vault Default Insert Script can be used to generate the required Ghost Keys.

Tip

For additional details on generating DDL refer to the BimlFlex DDL generation guide.

Deploying SnowSQL Scripts

Once BimlFlex generates the scripts they can be executed against the target database. These can be deployed through copy/paste using a Snowflake Worksheet or by a SnowSQL command if desired.

Important

Ensure you are using the appropriate WAREHOUSE and DATABASE when executing the SnowSQL scripts. The scripts do not contain a USE DATABASE or USE WAREHOUSE clause and depend on the user executing the script to have select the appropriate values.

Tip

For additional details on using the SnowSQL CLI or Worksheets refer to the below Snowflake Docs guides:

Orchestration

BimlFlex automatically generates the orchestration artifacts as part of the standard build process. The actual artifacts generated depends on the Integration Stage that is configured for the Project.

SSIS Packages

Deploying SSIS orchestration while using Snowflake as a target platform is no different from a standard SSIS deployment using BimlFlex. Please refer to the below guides for common deployment approaches.

Ensure these commonly missed steps are performed:

Tip

For additional details on generating deploying SSIS packages refer to the below guides:

ADF Environment and Orchestration

There is no difference in the process of deploying the Azure environment and ADF orchestration compared to any other target infrastructure in BimlFlex.

As a final check, please ensure the following configurations were made:

Tip

For additional details on generating deploying ADF artifacts refer to the below guides: