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 using an account locator in the format
account_locator.cloud_region_id
or account name in the formatorganization-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:
- Log in to Snowflake.
- 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.
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.
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.