Snowflake

Learn about Stigg's native integration with Snowflake

Overview

Stigg's integration with Snowflake serves a core principal of granting access to raw data, enabling the creation of customized reporting solutions and fostering informed business decision-making.

πŸ“˜

Stigg's native integration with Snowflake is included in the Scale plan, and is also available as an optional add-on to the Growth plan. See Stigg's pricing for more details.


Available data types

Stigg's integration with Snowflake allows you to sync the following Stigg entities to Snowflake:

  1. Product catalog
    1. Products
    2. Plans
    3. Add-ons
    4. Features
  2. Customers
  3. Subscriptions

Setting up the integration

Prerequisites

To setup the integration, a Snowflake account with the ACCOUNTADMIN role. If you don’t have an account with the ACCOUNTADMIN role, contact your Snowflake administrator to set one up for you.


Create Stigg entities in Snowflake

In Snowflake, create the relevant Stigg entities (a role, user, warehouse, database and schema) with the OWNERSHIP permission.

Open a new Snowflake worksheet and paste the below script:


-- set variables (these need to be uppercase)
set export_role = 'STIGG_ROLE';
set export_warehouse = 'STIGG_WAREHOUSE';
set export_database = 'STIGG_DATABASE';
set export_schema = 'STIGG_SCHEMA';

-- set credentials
set export_username = 'STIGG_USER';
set export_password = '<PASSWORD>';

begin;

-- create role
use role securityadmin;
create role if not exists identifier ($export_role);
grant role identifier ($export_role) to role SYSADMIN;

-- create user
create user if not exists identifier ($export_username)
    password = $export_password
    default_role = $export_role
    default_warehouse = $export_warehouse;

grant role identifier ($export_role) to user identifier ($export_username);

-- change role to sysadmin for warehouse / database steps
use role sysadmin;

-- create warehouse
create warehouse if not exists identifier ($export_warehouse)
    warehouse_size = xsmall warehouse_type = standard
auto_suspend = 60
auto_resume = true
initially_suspended = true;

-- create database
create database if not exists identifier ($export_database);

-- grant warehouse access
grant USAGE
    on warehouse identifier ($export_warehouse)
    to role identifier ($export_role);

-- grant database access
grant OWNERSHIP
    on database identifier ($export_database)
    to role identifier ($export_role);

commit;

begin;

USE DATABASE identifier ($export_database);

-- create schema
CREATE SCHEMA IF NOT EXISTS identifier ($export_schema);

commit;

begin;

-- grant schema access
grant OWNERSHIP
    on schema identifier ($export_schema)
    to role identifier ($export_role);

commit;

Replace <PASSWORD> with a unique password

🚧

The defined password should only have alpha-numeric characters and no special characters


Run the script, while using the "Run All" option (⌘ + Shift + Enter shortcut in Mac or CTRL + Shift + Enter in Windows).


Grant Stigg access to your Snowflake cluster

By default, Snowflake allows users to connect to the service and internal stage from any computer or device. A security administrator (or higher) can use a network policy to allow or deny access to a request based on its origin.

In this step we'll ensure that Stigg has access to your Snowflake cluster.

In Snowflake, open a new worksheet and run the below command:

SHOW NETWORK POLICIES;

If the returned result is empty, all network access is allowed to your cluster, and specifically Stigg can access it - skip to the next section.

If the returned result is not empty, an existing network policy is in place. To ensure that Stigg can access your cluster, update the existing policy and add Stigg's IP addresses to the allowlist using the below command:

CREATE NETWORK POLICY $EXPORT_POLICY_NAME ALLOWED_IP_LIST = (
-- Depending on your data residency location, add the following IP addresses to your allowlist:
-- United States:
  '34.106.109.131/32',
  '34.106.196.165/32',
  '34.106.60.246/32',
  '34.106.229.69/32',
  '34.106.127.139/32',
  '34.106.218.58/32',
  '34.106.115.240/32',
  '34.106.225.141/32',
-- European Union:
  '13.37.4.46/32',
  '13.37.142.60/32',
  '35.181.124.238/32'
);

Connect Stigg with Snowflake

In Stigg, navigate to the Integrations > Apps > Snowflake section.

Enter the below information in the opened form:

Field
Description
Account URLThe Snowflake account URL, for example:
https://ab12345.us-east-2.aws.snowflakecomputing.com

RoleThe role you specified when creating the Stigg entities in Snowflake, for example: STIGG_ROLE
WarehouseThe warehouse you specified when creating the Stigg entities in Snowflake, for example: STIGG_WAREHOUSE
DatabaseThe database you specified when creating the Stigg entities in Snowflake, for example: STIGG_DATABASE
SchemaThe schema you specified when creating the Stigg entities in Snowflake, for example: STIGG_SCHEMA
UsernameThe username you specified when creating the Stigg entities in Snowflake, for example: STIGG_USER
PasswordThe password you specified when creating the Stigg entities in Snowflake.

Click "Connect to Snowflake".


Sync process and frequency

Upon completion of the integration setup, Stigg will perform a full sync of all of the available data types.

Subsequent syncs will be incremental; thereby, significantly reducing the amount of data synchronized on a regular basis.

πŸ“˜

Stigg syncs data to Snowflake on a daily basis at 12:00am UTC.


Removing the integration

To remove the integration, click on the dotted menu icon and select the "Remove" action.

Confirm the action by clicking on the "Remove" button in the opened modal.