Skip to main content

How do I create Snowflake data feed metrics?

Updated

Visualize, discover, and act on your data with metric visualizations and dashboards.

Ready to get the most from the data that's stored in your Snowflake database? Get started by adding some data feeds. When you're finished adding Snowflake data feeds, you'll use them to power your metrics.

This article includes:

Should I create data feeds or connect directly to my Snowflake warehouse?

In PowerMetrics you can connect to your Snowflake warehouse and create either data warehouse metrics or data feed metrics. Which solution is better for you and your data?

  • Data warehouse metrics are the recommended solution for database and data warehouse infrastructures. They may be right for you if you have a lot of data and want scalability (the amount of data that can be handled is only limited by the capacity of your warehouse), have stringent security policies (data is stored and managed in your data warehouse) and demand higher performance (performance can be optimized by tuning your warehouse).
     
  • Data feed metrics are primarily used to connect to services using REST API to get data. They may be right for you if you process small amounts of data (the maximum data feed size is 10 MB. Each time the raw data is queried it needs to fit within this limit after the transformations are defined in the feed) and want a mixed storage model (metric history and data are stored in the metric layer in PowerMetrics and in the warehouse).

Adding a Snowflake data feed

If you’re using Network Policies to restrict IP access, before adding a data feed, make sure you've added our PowerMetrics IP addresses to your allow list. See the full list of PowerMetrics' Allow list IP addresses.

To add a Snowflake data feed:

  1. Add a new data feed by clicking the + button beside Data Feeds in the left navigation bar. (See below.)
    add data feeds button
  2. Click Select data.
  3. On the Where is your data? page, click Snowflake. (See below.)
    snowflake tile
  4. On the Configure data service page, fill in the following fields:
    • Account name: Enter the number that displays under LOCATOR (see the example below).
      snowflake locator example
    • Snowflake Region: Choose the appropriate region from the drop-down list.
    • Username: Enter the username that’s associated with the appropriate role. Learn more about user roles.
    • Private key: Add a private key file. Note: You must copy and paste the entire contents of the key file, including the header and footer text.
    • Private key passphrase: Enter the private key passphrase if the key file is encrypted. Note: This information is only required when the key file is uploaded or changed.
    • Role: Enter the role that’s associated with the selected account name. PUBLIC is inserted into this field as the default setting but you can replace it if needed.
    • Warehouse: Enter the name of the warehouse you want to use to access your data. Warehouses are listed in the Admin section in Snowflake. Note: Make sure the role you entered above has access to the warehouse.
    • Database: Enter the name of the database where the data you want to retrieve is stored.
    • Schema: Enter the name of the schema within the database that you want to access. Schemas are listed under Databases > <Name of the selected database>.
    • SQL Query: Enter an SQL query to retrieve the desired data. Tip: You can test your SQL queries in Snowflake by adding a SQL worksheet. Learn more here. Go here for more information on querying data in Snowflake.
  5. Select whether to Include column headers.
    (See below for an example of configuration settings for a Snowflake data feed).
    configuration settings example
  6. Click Get data.
  7. Ensure this is the data you're looking for, then click Continue.
  8. You're taken to the data feed editor, where you can make changes to the data feed or save it as-is. If you want to modify the data feed, go to this article for detailed editing information.
  9. Give the data feed a name and, optionally, enter a description.
  10. Click Save data feed.
    The data feed is added to your account and is ready to use for custom metrics. To see a list of all your data feeds, click Data Feeds in the left navigation sidebar.

Creating Snowflake metrics

Now that you've created one or more Snowflake data feeds, you can return to them and use them to make metrics. A single data feed can be used to create a single or multiple metrics.

To create Snowflake metrics:

  1. In the left navigation sidebar, click Data Feeds to open your list of data feeds.
  2. Select the Snowflake data feed you want to use for your metric.
  3. Click either the +Add metric button in the top right corner of the window or open the Metrics tab and click +Add metric. (See below.)
    options for adding metrics
  4. Choose settings for your metric and click Save metric. If you need help, go here to learn more.
    The metric is added to your list of metrics, accessed by clicking Metrics in the left navigation sidebar.

Troubleshooting Snowflake queries

Here are a few troubleshooting ideas you can try if you're having issues with your Snowflake query.

  • Confirm that the SQL connection is working by using a query of Select 1. This should return the result of 1.
  • Make sure your query isn’t trying to bring in too much data. Klipfolio supports a maximum file size of 10 MB. If you’re returning more than 10 MB of data, your query may not work. To see if this is the issue, try adding a LIMIT to your query (e.g., LIMIT 1000) to reduce the amount of data being returned.
  • Confirm that it isn't taking too long to bring in your data. Klipfolio includes a global setting of 80 seconds within which to bring in data. If you think this might be the issue, try reducing the amount of your data so you stay within this range.

Related articles