Snowflake - Daily exports from AB Tasty to Snowflake

💡 Good to know

This feature is currently available in Early Adoption. Please contact your CSM to enroll into the Early Adopter program.

Snowflake is a cloud data warehouse that can store and analyse all your data records in one place. It can automatically scale up/down its compute resources to load, integrate, and analyse data. 

 

The connector

Step 1: Create the worksheet

From your SnowFlake console, execute the following steps:

  1. Create a SnowFlake account 
  2. Go into your account to create a new SQL  Snowflake worksheet

  1. Copy this below code (change the annotation field as role, username, password etc ..)
-- set variables (these need to be uppercase)
set abt_snwoflake_role = 'YOUR_ROLE'; // field to be defined by you
set abt_snowflake_username = 'YOUR_USER_NAME'; // field to be defined by you
set abt_snowflake_warehouse = 'YOUR_WAREHOUSE';// field to be defined by you
set abt_snowflake_database = 'YOUR_DATABASE';// field to be defined by you
set abt_snowflake_schema = 'YOUR_SCHEMA'; // field to be defined by you
 -- set user password
 set airbyte_password = 'your_password'; // field to be defined by you
 begin;
 -- create your role
 use role securityadmin;
create role if not exists identifier($abt_snwoflake_role);
grant role identifier($abt_snwoflake_role) to role SYSADMIN;
-- create your user
create user if not exists identifier($abt_snowflake_username)
password = $airbyte_password
default_role = $abt_snwoflake_role
default_warehouse = $abt_snowflake_warehouse;
grant role identifier($abt_snwoflake_role) to user identifier($abt_snowflake_username);
-- change role to sysadmin for warehouse / database steps
use role sysadmin;
-- create your warehouse
create warehouse if not exists identifier($abt_snowflake_warehouse)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 60
auto_resume = true
initially_suspended = true;
-- create your database
create database if not exists identifier($abt_snowflake_database);
-- grant your warehouse access
grant USAGE
on warehouse identifier($abt_snowflake_warehouse)
to role identifier($abt_snwoflake_role);
-- grant your database access
grant OWNERSHIP
 on database identifier($abt_snowflake_database)
 to role identifier($abt_snwoflake_role);
 commit;
 begin;
 USE DATABASE identifier($abt_snowflake_database);
 -- create schema for Airbyte data
 CREATE SCHEMA IF NOT EXISTS identifier($abt_snowflake_schema);
 commit;
 begin;
 -- grant Airbyte schema access
 grant OWNERSHIP
 on schema identifier($abt_snowflake_schema)
 to role identifier($abt_snwoflake_role);
 commit;
  1. Paste it inside the console and run it 

  1. Once the script is done, refresh the page. On the left part, a new line will appear with the name you gave inside the script for the database
set abt_snowflake_database = 'YOUR_DATABASE';// field to be defined by you

In the above example, the name of the worksheet is YOUR_DATABASE

 

Step 2: Get SnowFlake Host

On the Snowflake homepage, click at the bottom left of the page and copy the account URL

 

Step 3: Set up the connector 

  1. Go to the integration page > databases > Snowflake > setup connector
  2. Enter a name for the collector
  3. Host: it corresponds to the account URL you’ve already copied
  4. Role: the role we created in the previous script
  5. Warehouse: the warehouse we created in the previous script
  6. Database: the database we created in the previous script
  7. Authorization method: choose username and password
    1. Username: the username we created in the previous script
    2. Password: the password we created in the previous script
  8. Loading Method: choose internal staging

 

 

Good to know 💡

You will get an error message, if one of the fields contains an error. 
Your connector is now set up, and you can proceed to set up your Export.

 

The export

To set up your daily export, please refer to the guide: Databases integration.

Step 1: Generate your payload

Refer to the Databases article to create your payload.

Step 2: Set up the export

  1. Export name: the name of your export, give an explicit name to easily retrieve it in AB Tasty
  2. Name of the table: the name of the table we will create in your Snowflake 
  3. Data exporter query: paste here the payload of your data explorer query 
  4. Click save and create. 

The SnowFlake integration is now complete, and you will soon see the data flowing into your dedicated database (It can take up to 2–3 hours, depending on the size of your report).

 

Good to know 💡

The export is activated upon creation, and new data will be appended to the current one, daily. The following screenshot shows that the export is activated on creation:

You can only have one export per day on the free plan. When creating a new export, all previous ones will be automatically deactivated.
Please contact your KAM to upgrade your plan if you need more exports.

Was this article helpful?

/