Configuring Snowflake as a destination – Help Center

You’ll need to be a Staff or Admin user in ChartMogul and use the ACCOUNTADMIN role in Snowflake to complete the steps outlined in this article. Read more about user roles and permissions.

Snowflake’s Data Cloud is powered by an advanced data platform provided as Software-as-a-Service (SaaS). ChartMogul allows you to export MRR movements, customers, custom attributes, and tags to Snowflake tables. From there, you can create your own views and combine datasets for further analysis.

Here’s what we cover in this article:

Resources and further reading:

Before you begin

Please review the following details before completing the steps outlined in this article.

You’ll need to identify your Snowflake account identifier using an account locator in the format account_locator.cloud_region_id or account name in the format organization-account_name.
Steps 1–5 require you to execute SQL commands (queries) using a worksheet in Snowflake.
ChartMogul uses an S3 bucket (that you’ll set up) as a staging area to send data to Snowflake. ChartMogul first uploads data using the parquet data storage format to the bucket, then moves the data into Snowflake tables. ChartMogul refreshes (overwrites) the data in these tables as part of its daily sync.
If you have already configured Amazon S3 as a destination, we recommend creating a new S3 bucket for your Snowflake destination.
You’ll need to choose a name for the S3 bucket. Please review Amazon’s bucket naming rules as part of this.

Step 1 — Create a Snowflake database and warehouse

Create a new Snowflake database and warehouse for ChartMogul:

Within Worksheets, click + Worksheet. Copy and paste the following commands into the SQL editor:

CREATE DATABASE CHARTMOGUL_DATA;
 CREATE WAREHOUSE CHARTMOGUL_WAREHOUSE
   WITH
     WAREHOUSE_SIZE = 'XSMALL'
     WAREHOUSE_TYPE = 'STANDARD
     AUTO_SUSPEND = 60
     INITIALLY_SUSPENDED = true;

Select the commands and click the Run button.

Step 2 — Create a Snowflake role and grant permissions

Create a new role with the permissions required to load your data into the newly-created warehouse:

Copy and paste the following commands into the SQL editor:

CREATE ROLE CHARTMOGUL;
GRANT USAGE ON WAREHOUSE CHARTMOGUL_WAREHOUSE TO ROLE CHARTMOGUL;
GRANT USAGE ON DATABASE CHARTMOGUL_DATA TO ROLE CHARTMOGUL;
GRANT CREATE SCHEMA ON DATABASE CHARTMOGUL_DATA TO ROLE CHARTMOGUL;
GRANT ALL ON ALL SCHEMAS IN DATABASE CHARTMOGUL_DATA TO ROLE CHARTMOGUL;

Select the commands and click the Run button.

Step 3 — Secure network access to Snowflake

We recommend creating a network policy to limit access to your Snowflake instance to ChartMogul IP addresses:

Copy and paste the following command into the SQL editor:

CREATE NETWORK POLICY CHARTMOGUL_POLICY
ALLOWED_IP_LIST=('3.248.174.47/32','99.80.87.208/32','34.248.167.65/32');

Select the command and click the Run button.

Step 4 — Create a Snowflake user

Create a dedicated Snowflake user for ChartMogul and grant database access:

Copy and paste the following commands into the SQL editor, replacing <PASSWORD> with the appropriate value:

CREATE USER CHARTMOGUL_USER
  MUST_CHANGE_PASSWORD = FALSE
  DEFAULT_ROLE = CHARTMOGUL
  PASSWORD = "<PASSWORD>"
  NETWORK_POLICY = “CHARTMOGUL_POLICY”;
GRANT ROLE CHARTMOGUL TO USER CHARTMOGUL_USER;

Select the commands and click the Run button.

Step 5 — Create an Amazon S3 Bucket

Next, create a new Amazon S3 bucket:

Within S3, navigate to Buckets > Create bucket.
Within General configuration, enter the name you’ve chosen for your S3 bucket in the Bucket name field.
Select the AWS Region you’ve chosen from the AWS Region drop-down.
Complete any additional setup option you may need, then click Create bucket.

Note the Bucket name and AWS Region, as you’ll need these in the final step.

Step 6 — Create an AWS IAM policy

Next, create an AWS Identity and Access Management (IAM) policy that allows ChartMogul to access the S3 bucket you just created.:

Within IAM, navigate to Policies > Create policy.

Click the JSON tab and add the following code, replacing <BUCKET_NAME> with the name you used to create the bucket in the previous step. Then click Next: Tags.

{
    "Version": "2012-10-17",
    "Statement": [
        {
              "Effect": "Allow",
              "Action":
              [
                  "s3:GetObject",
                  "s3:PutObject",
                  "s3:PutObjectAcl",
                  "s3:ListBucket"
              ],
              "Resource":
              [
                  "arn:aws:s3:::<BUCKET_NAME>/*",
                  "arn:aws:s3:::<BUCKET_NAME>"
              ]
        }
    ]
}

Finally, name your policy, e.g., ChartMogul, and click Create policy.

Step 7 — Create an AWS IAM user

Now, create an AWS Identity and Access Management (IAM) user that will have permission to access the S3 bucket. After creating the user, they’ll have an Access key ID and a Secret access key, which you’ll use to create the destination in ChartMogul.

Within IAM, go to Access management > Users > Add users.
Enter a name for the user, e.g., ChartMogul, in the User name field.
Select Access key - Programmatic access as the AWS credential type.
Within Set permissions, click Attach existing policies directly, then search for the IAM policy you created in the previous step using its name and tick the box to select it. Finally, click Next.
Add any tags you’d like to the user, then click Next: Review.
If everything looks good, click Create user.

Note the Access key ID and Secret access key, as you’ll need these in the final step. Alternatively, click Download.csv to save this information.

Step 8 — Add Snowflake as a destination

With ChartMogul, navigate to Data Platform > Destinations.
Click ADD DESTINATION and select Snowflake.
Enter a unique name for your destination in the Name field. Then click Next.
Choose how often you’d like ChartMogul to export data to Snowflake, either:

Daily (09:00 UTC each day),
Weekly (09:00 UTC each Sunday), or
Monthly (09:00 UTC on the first day of each month).

Choose which datasets you'd like ChartMogul to send:

Activities,
Customers,
Tags,
Custom attributes, or
Currency rates

Within Snowflake configuration:

Account: Snowflake account identifier.
Warehouse: The name of the warehouse you created in Step 2.
User: The name of the user you created in Step 5.
Password: The password for the user you set in Step 3.
Database: The name of the database you created in Step 1.
Role: The name of the role you created in Step 3.
Schema Prefix (Optional): If your organization has a schema naming convention, add it here. If left blank, ChartMogul creates a schema on your behalf.

Complete the following fields for S3 Storage configuration:

Bucket Name: The name of your bucket created in Step 6.
AWS Region: The region of your bucket selected in Step 6.
Access Key ID: The Access key ID from Step 8.
Secret Access Key: The Secret access key from Step 8.

Click SAVE AND TEST CONNECTION.

ChartMogul will perform a test connection and, if successful, activate Snowflake as a destination.

Deleting Snowflake as a destination

To delete your Snowflake destination, navigate to Data Platform > Data Output > Destinations. Find your destination in the list and click the Settings icon. From there, click DELETE.

After deleting your Snowflake destination in ChartMogul, the data that has been synced up to the point of deletion will remain in the database.