How to use the Snowflake Destination.

This destination syncs your data to your Snowflake Data Warehouse in real time and in Relational Table format. Following the ELT way of loading data MovingLake will sync raw JSONs into tables with _raw suffixes. JSON tables are also appended with the time it the row was loaded as well as appending a unique identifier and a SHA-256 HASH of the raw JSON payload. These last two fields are quite important not just for locating rows but also when doing joins across related tables as it is not common for these to natively have an ID which relates them.

After syncing the raw JSONs, MovingLake automatically transforms the data into relational tables using our Automatic Transformer. These tables are named the same as the JSON tables but without the _raw suffix. Also one raw table might generate more than one transformed table.

Keep in mind that MovingLake will create a new Database called MLAKE_DB, a new user called MLAKE_USER and a new role called MLAKE_ROLE. Tables are stored in different schemas, one per connector.

Connector guide.

MovingLake needs its own database within a data warehouse of your account to push all the schemas and tables. For this reason please run the following script in your data warehouse. Please make note of the data warehouse where it will run and the password for the user. You will need it when creating the destination. Finally please do not use the MLAKE_USER user for any other purposes.

BEGIN;
 
  -- Variables to configure the DB. Please change the password and make sure the data warehouse name exists.
  SET role_name = 'MLAKE_ROLE';
  SET user_name = 'MLAKE_USER';
  SET user_password = 'changeme';
  SET warehouse_name = 'CHANGEME_DATAWAREHOUSE'; -- Choose an existing data warehouse.
  SET database_name = 'MLAKE_DB';

  -- Change role to securityadmin.
  USE ROLE securityadmin;

  -- Create role for MovingLake.
  CREATE ROLE IF NOT EXISTS identifier($role_name);
  GRANT ROLE identifier($role_name) TO ROLE SYSADMIN;

  -- Create a user for MovingLake.
  CREATE USER IF NOT EXISTS identifier($user_name)
  password = $user_password
  default_role = $role_name
  default_warehouse = $warehouse_name;

  GRANT ROLE identifier($role_name) TO USER identifier($user_name);

  -- Change roles for next steps.
  USE ROLE accountadmin;

  -- Create a database for MovingLake.
  CREATE DATABASE IF NOT EXISTS identifier($database_name);

  -- Grant MovingLake role access to the warehouse.
   GRANT USAGE ON WAREHOUSE identifier($warehouse_name) TO ROLE identifier($role_name);
 
  -- Grant MovingLake access to database.
  GRANT ALL ON database identifier($database_name) TO ROLE identifier($role_name);

  -- Grant MovingLake access to schema.
  GRANT ALL ON ALL SCHEMAS IN DATABASE MLAKE_DB TO ROLE MLAKE_ROLE;
  GRANT ALL ON FUTURE SCHEMAS IN DATABASE MLAKE_DB TO ROLE MLAKE_ROLE;

  -- Change role to ACCOUNTADMIN. Only needed for Snowflake on GCP.
  USE ROLE ACCOUNTADMIN;
  GRANT CREATE INTEGRATION ON account TO ROLE identifier($role_name);
  USE ROLE sysadmin;

COMMIT;

After running this script then just go ahead and create the destination on your MovingLake account. Please make sure to use input the same information from the script variables into the destination creation form.