Configuring Snowflake as a destination

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.
  • You’ll need to decide which cloud option ChartMogul will use as a storage staging area to ingest your data: AWS S3 or Google Cloud Storage.
  • Steps 1–5 require you to execute SQL commands (queries) using a worksheet in Snowflake.
  • ChartMogul uses an S3 or GCS bucket (depending on your chosen provider, 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 or Google Cloud Storage as a destination, we recommend creating a new S3 bucket for your Snowflake destination.

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 a storage bucket

ChartMogul provides two options for bucket storage: AWS S3 or Google Cloud Storage.

To use AWS S3:

  1. Follow steps 1–3 in Configuring Amazon S3 as a destination. 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.
  2. Follow the steps to add Snowflake as a destination and choose AWS S3 when configuring storage.

 

To use Google Cloud Storage:

  1. Follow steps 1–3 in Configuring Google Cloud Storage as a destination. Note the location of your account’s private key as a JSON file, as you’ll need this in the final step.
  2. Follow the steps for Configuring an Integration for Google Cloud Storage. When creating a custom IAM role, the Snowflake Integration only requires the Data loading only permissions. 
  3. Copy and paste the following commands into the SQL editor, replacing <INTEGRATION_NAME> with the name of the integration you created in the previous step:
                GRANT USAGE ON INTEGRATION
    
                <INTEGRATION_NAME> TO ROLE
              
                CHARTMOGUL;
  4. Follow the steps to add Snowflake as a destination and choose Google Cloud Storage when configuring storage.

Step 6 — Add Snowflake as a destination

  1. With ChartMogul, navigate to Data & Config > Destinations.
  2. Click Add 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: Screenshot of the Add a Destination dialog with some of Datasets selected.
  6. Within Configure Snowflake:
    • Account: Snowflake account identifier.
    • Warehouse: The name of the warehouse you created in Step 1.
    • Username: The name of the user you created in Step 4.
    • Password: The password for the user you set in Step 4.
    • Database: The name of the database you created in Step 1.
    • Role: The name of the role you created in Step 2.
    • Schema Prefix (Optional): ChartMogul uses chartmogul_data as the schema name. Complete this field to add a prefix to the schema.
  7. Select your storage bucket and complete the following fields:
    • For AWS S3:
      • Bucket Name: The name of your bucket created in Step 5.
      • AWS Region: The region of your bucket selected in Step 5.
      • Access Key ID: The Access key ID from Step 5.
      • Secret Access Key: The Secret access key from Step 5.
    • For Google Cloud Storage:
      • GCS Integration Name: The integration name you created in Step 5.
      • Bucket Name: The name of your bucket created in Step 5.
      • JSON Key file: The private key you downloaded as a JSON file in Step 5.
  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 & Config > 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.

Was this article helpful?

We’re sorry to hear that. Would you like to share more feedback?


Thanks for your feedback!