How To Setup BimlFlex for Snowflake
Getting Started
To get started, you can download SnowSQL from the Snowflake UI after logging into your Snowflake account.
To be able to build Windows 64-bit BimlFlex solutions for Snowflake, you need to:
Open the Snowflake repository of https://sfc-repo.snowflakecomputing.com/index.html
From here you will be able to download the latest ODBC driver and the SnowSQL executable installation files.
Install the ODBC drivers
- Open the Snowflake repository of https://sfc-repo.snowflakecomputing.com/index.html
- Click on the obdc folder;
- Select either the win32 or win64 folder to install the 32-bit or 64-bit driver;
- Click on the latest folder
- Click on the available *.msi
This will download the driver. Run the downloaded file, follow the instructions until finished.
Create the ODBC connection in the driver management app by clicking on Start, and typing ODBC. Select either the 32-bit or 64-bit depending on which driver you installed.
The following screen opens:
Select SnowflakeDSIIDriver and click Finish.
The following screen then opens:
Please ensure to enter the correct Server: by replacing the “accountname” with your Snowflake account name and “region” with the region for your Snowflake account followed by .snowflakecomputing.com .
Set the Data Source name and change the Database, Schema and Warehouse fields to the respective names you wish to connect to.
Install SnowSQL
- Open the Snowflake repository of https://sfc-repo.snowflakecomputing.com/index.html
- Click on the snowsql folder
- Click on the bootstrap folder
- Click on the version available
- Select the windows_x86_64 folder
- Select the latest snowsql*.msi file
- Run the downloaded installation file, follow the wizard until you Finish.
Setup the Snowflake config file
Browse to the following directory %USERPROFILE%\.snowsql\config , open with Notepad or any text reader and below the rows starting with # insert the following:
[connections.bimlflex_db]
accountname = enteryouraccountname
username = enteryourusername
password = enteryourpassword
schemaname = enteryourschemaname
dbname = enteryourdatabasename
warehousename = enteryourwarehousename
region = enteryourregion
Setup BimlFlex
Open BimlFlex, in the Getting Started block in the bottom right of the screen, open Load Defaults.
Select the Snowflake DataVault SSIS Solution option and ensure the correct Target Version, click Ok.
This will load all the default settings required for Snowflake using SSIS.
For all the databases that will be residing in Snowflake, in this example the _STG, ODS and DM will be in Snowflake, therefore we need to change these database settings in BimlFlex to allow for this connectivity.
For each connection that needs to be adjusted, you will need to:
Change the CONNECTION TYPE to ODBC SQL Based ELT; change the SYSTEM TYPE to Snowflake Data Warehouse; enter the CONNECTION STRING username, password and DSN name (ODBC Driver name setup earlier).
Now that we have setup the connections we need to configure the Snowflake settings in BimlFlex. If you open the Settings in BimlFlex, filter to show the Snowflake settings, you will need to change the:
- SnowflakeAccount – in the SETTING VALUE enter the Snowflake account to be connected to
- SnowflakeDatabase - in the SETTING VALUE enter the Snowflake database to be connected to
- SnowflakePassword - in the SETTING VALUE enter the Snowflake password for the above account
- SnowflakeRegion - in the SETTING VALUE enter the Snowflake region for the above account
- SnowflakeSchema - in the SETTING VALUE enter the Snowflake schema to connect to
- SnowflakeUser - in the SETTING VALUE enter the Snowflake user to connect to
- SnowflakeWarehouse - in the SETTING VALUE enter the Snowflake warehouse to connect to
- SnowSqlConnection - in the SETTING VALUE enter the Snowflake connection to connect to.
Please note that all these settings are the same settings that were made in the %USERPROFILE%\.snowsql\config file.
All of the above that I have written can be viewed in the following video: https://www.youtube.com/watch?v=yPWKs65JSFo
Please note though that as this video was made in an earlier version of BimlFlex, some of the links and icons may be different to the newer versions.
Once all of this is done, you are now able to build Snowpipes from BimlFlex.