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:

• Creating a Snowflake database and warehouse
• Creating a Snowflake role and grant permissions
• Securing network access to Snowflake
• Creating a Snowflake user
• Creating an Amazon S3 Bucket
• Creating an AWS IAM policy
• Creating an AWS IAM user
• Adding Snowflake as a destination
• Deleting Snowflake as a destination

Resources and further reading:

• Getting Started with Snowflake
• Key Concepts & Architecture
• Understanding MRR Movements
• Custom Attributes
• Tags

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. Read more about account identifiers.
• Steps 1–5 require you to execute SQL commands (queries) using a worksheet in Snowflake. 
• ChartMogul creates its own schema chartmogul_data under the Snowflake database you’ll create.
• 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:

1. Log in to Snowflake. 
2. 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;

3. 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:

1. 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;

2. 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:

1. 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');

2. 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:

1. 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;

2. Select the commands and click the Run button.

Step 5 — Create an Amazon S3 Bucket

Next, create a new Amazon S3 bucket:

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

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.:

1. Within IAM, navigate to Policies > Create policy.
2. 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>"
                 ]
           }
       ]
   }

3. 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.

1. Within IAM, go to Access management > Users > Add users.
   
2. Enter a name for the user, e.g., ChartMogul, in the User name field.
   
3. Select Access key - Programmatic access as the AWS credential type.
   
4. 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.
   
5. Add any tags you’d like to the user, then click Next: Review.
6. If everything looks good, click Create user.
   

Step 8 — Add Snowflake as a destination

1. With ChartMogul, navigate to Data Platform > Data Output > Destinations.
2. Click ADD NEW DESTINATION and select Snowflake.
3. Enter a unique name for your destination in the Name field. Then click Next. 
4. 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).
5. Choose which datasets you'd like ChartMogul to send:
• Activities,
• Customers,
• Tags, or
• Custom attributes.
6. 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.
7. 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.
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.