Snowflake

Section header image mobile

The Coefficient Snowflake connector comes with a visual query builder where you can select tables, columns, and filters, which we automatically translate into SQL query that is run on your Snowflake instance. In addition, you can supply custom SQL as well if you prefer to set up your import based on a Snowflake SQL query.

Connecting to Snowflake

Connect using Account Credentials

Connect using OAuth

Configure Snowflake OAuth (For Snowflake Admins)

Connect using Snowflake OAuth

Import from Snowflake

Import from Tables & Columns

Import using a Custom SQL Query

Import from GPT SQL Builder

Schedule your Import, Snapshots, and Add Automations

FAQs for Snowflake Integration

Connecting to Snowflake

When you begin a Snowflake import for the first time, you will need to go through a few steps to connect Snowflake as a data source for Coefficient.

Let’s discuss each of them! 💡

Connect using Account Credentials

When connecting to Snowflake using your account credentials, Coefficient will need the following information: Snowflake Account Name, Database Name, Username, Password, and/or Warehouse Name.

1. Open the Coefficient Sidebar and click on the Menu.

MenuIconUpdated.png

2. Select “Connected Sources”.

Connected_Sources.png

3. Select “Add Connection” at the bottom and then “Connect” to Snowflake.

Screenshot 2023-11-24 at 8.36.51 PM.png

4. Enter your Snowflake Account Name and Database Name.

ℹ️ NOTE: When entering your Account Name, you may need to include the region ID and/or cloud platform (AWS, Azure, GCP).

For details, see Account Locator in a Region (Snowflake Documentation).

SnowflakeDBCreds1.png

5. Enter your credentials. If your Snowflake instance has network policies configured with IP restrictions, you will need to whitelist (ALL 3) Coefficient's server IP addresses.  (34.217.184.131, 44.234.233.60, 52.32.132.51)

Screen Shot 2022-05-27 at 10.23.23 AM (1).png

For details, see Network Policies (Snowflake Documentation).

 

6. If you would like to use a different Snowflake Warehouse or you don’t have a default warehouse assigned to your account, you can manually enter it by clicking the “Advanced Settings” link.

ℹ️ NOTE: You can leave this section blank and Coefficient automatically connects to your default warehouse.

Screenshot 2023-11-24 at 8.43.25 PM.png

7. ℹ️ OPTIONAL - Once the connection is made, you will see an optional setting that will allow you to share your Snowflake connection with your team. Your password will never be visible to your team members.

Screen_Shot_2021-07-14_at_11.54.55_AM.png

Connect using OAuth

ℹ️ NOTE: Your Snowflake Admin needs to configure the Snowflake OAuth for your domain first to be able to use this feature.

Configure Snowflake OAuth (For Snowflake Admins)

1. Open the Coefficient Sidebar and click on the Menu.

MenuIconUpdated.png

2. Select “Connected Sources”.

Connected_Sources.png

3. Select “Add Connection” at the bottom and then “Connect” to Snowflake.

Screenshot 2023-11-24 at 8.36.51 PM.png

4. Click the “connect using Snowflake OAuth” link.

Screenshot 2023-11-24 at 10.14.15 PM.png

5. Click “Configure”.

Screenshot 2023-11-24 at 10.15.49 PM.png

6. You will then be redirected to the Snowflake OAuth Configuration page. Follow the instructions below and save the configuration.

Screenshot 2023-11-24 at 10.22.59 PM.png

Screenshot 2023-11-24 at 10.21.12 PM.png

ℹ️  Click here to learn more about configuring Snowflake OAuth.

Connect using Snowflake OAuth

1. Open the Coefficient Sidebar and click on the Menu.

MenuIconUpdated.png

2. Select “Connected Sources”.

Connected_Sources.png

3. Select “Add Connection” at the bottom and then “Connect” to Snowflake.

Screenshot 2023-11-24 at 8.36.51 PM.png

4. If your domain’s Snowflake OAuth is properly configured, the Account is automatically populated and you only need to enter the Database name and/or Warehouse name (Optional). Click “Authorize” to proceed.

Screenshot 2023-11-24 at 10.42.43 PM.png

5. Sign in to your Snowflake account. If successful, you can now proceed with creating Snowflake imports using Coefficient. 😎

Screenshot 2023-12-01 at 7.47.55 PM.png

Import from Snowflake

There are several ways to import data from Snowflake, let's go through each one!

  • Import from Tables & Columns
  • Import using a Custom SQL Query
  • Import from GPT SQL Builder

Import from Tables & Columns

1. Open the Coefficient Sidebar and click on the “Import from…” button.

ImportFrom.png

2. Select “Snowflake” from the list.

ImportfromSnowflake.png

3. Select "From Tables & Columns".

SnowflakeTablesandColumns.png

4. The Import Preview window opens showing all the table schemas from your MySQL database. Select the table for your import. (i.e. ”call_center”)SnowflakeImportPreview2.png

5. Select/Unselect the fields you need for your import on the left side of the Import Preview window. The data preview section updates as you make changes to your settings (the first 50 rows of data are shown). You can drag and drop column headers to change their order in your import.SnowflakeImporPreviewWindow.png

6. Customize your import by adding filters, and row limits as needed. When done click “Import”.SnowflakeFilterSortRowLimits.png

7. Congratulations on your successful import from a Snowflake table. 🎉SnowflakeImportSuccessful.png

Import using a Custom SQL Query

1. Open the Coefficient Sidebar and click on the “Import from…” button.

ImportFrom.png

2. Select “Snowflake” from the list.

ImportfromSnowflake.png

3. Select "Custom SQL Query".

SnowflakeCustomSQLQuery.png

4. The Import Preview window pops up, you can add your Snowflake query in the blue text area provided. You can also use a SQL Parameter for your query or use the GPT SQL Builder if needed. Only the first 50 rows of data are shown in the data preview section.

ℹ️ Click here to learn more about Snowflake Query Syntax.Screenshot 2023-12-22 at 7.57.43 AM.png

5. Make sure to give your import a name then hit "Import".Screenshot 2023-12-22 at 8.06.09 AM.png

6. Hooray! Congratulations on your Custom SQL Query import from Snowflake! 🎉SnowflakeSuccessImport.png

Import from GPT SQL Builder

1. Open the Coefficient Sidebar and click on the “Import from…” button.

ImportFrom.png

2. Select “Snowflake” from the list.

ImportfromSnowflake.png

3. Select "GPT SQL Builder"

SnowflakeImportFromGPT.png

4. Enter the prompt/query you would like the AI to build for you. Once you are done, click on "Generate SQL".

Example: Show all information from the tpcds_sf100tcl.inventory table where quantity on hand is greater than 500."

 ℹ️ PRO TIP: Be specific when adding your prompts. It will help the AI to easily understand your requirements and provide accurate results.Screenshot 2023-12-22 at 8.07.45 AM.png

5. The AI automatically generates the SQL query which you can modify if necessary. Click the "Refresh Preview" button to display a sample of the data results (only 50 rows are shown). Click "Import" when you are done. And... VOILA!🪄 Your Snowflake import assisted by the GPT SQL Builder is now ready. 🎉SnowflakeGPTResults.png

Schedule your Import, Snapshots, and Add Automations

Once you have pulled your data into Sheets using Coefficient, you can set up the following:

1. Schedule your ImportScheduled_Refreshes.png

2. Turn on SnapshotsSnapshot.png

3. Set Up Automations

AutomationswithGPT.png

FAQs for Snowflake Integration

Do I need a Username and Password to connect to Snowflake? What if my company uses an SSO (Single Sign-On) like Okta?

You now have the option to create your Snowflake connection and log in using your account credentials (username and password) or sign in through SSO. Please make sure to reach out to your Snowflake Admin to configure SSO with Coefficient (see the steps on configuring your Snowflake OAuth here).

How long does Coefficient connect to my Snowflake instance?

When Coefficient needs to run a query, we establish a connection to your database, run the query on your behalf, and terminate the connection once the query completes.

Why is the initial connection to Snowflake so slow?

When initially connecting to Snowflake, it may take a few minutes for Coefficient to fetch your database schema: table/view definitions, column names, etc. We cache this information so setting up subsequent Snowflake imports should feel much snappier.

I keep getting an error “No default warehouse for Snowflake user” when I try to connect to my server, what should I do?

  • Make sure that your user account has a default warehouse assigned to it. Run the script SHOW WAREHOUSES; on your DB to list all your available warehouses. To assign a default warehouse to your user account, run the following command: ALTER USER <user_name> SET DEFAULT_WAREHOUSE = <warehouse_name>;
  • There may be instances where your account is assigned to a specific role and it needs to be granted usage to the default warehouse. To do that, run the below script: GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE <role_name>;
  • In other cases where your connection is still not working even after running the above scripts, please ask your DB Admin to run the scripts above using their own credentials and make sure that your account and role have a default warehouse assigned to them.

What Role/s in Snowflake does Coefficient use to create the data connection?

Each time our systems establish a connection to your Snowflake instance, the underlying SQL connection is made using your Snowflake user’s default primary role and default secondary role. If you need to activate a non-default primary or secondary role, you can create your Coefficient Snowflake import using the “Custom SQL Query” option, and include the appropriate USE ROLE/USE SECONDARY ROLES statements in your SQL query.

I added a table (or column) in my Snowflake database; why is it not showing up in Coefficient?

To deliver a snappy experience when you set up imports from Snowflake, we cache your database schema for up to 24 hours. If you recently changed your database schema (e.g. added a table/column, renamed a table/column, etc.), and you don't see the change reflected in Coefficient, you can force a schema reload:

  1. Open the Coefficient sidebar in Google Sheets
  2. Click on the menu icon in the top right of the sidebar, then click on “Connected Sources”
  3. Click on your  Snowflake connection to see its Connection Settings page
  4. Click on the︙button near the top right, choose “Reload Schema”, and click “Reload” on the confirmation dialog.

My custom SQL script seems to run longer than expected and sometimes, I see a "SQL Error - canceling statement due to statement timeout" error when I refresh my import, what should I do?

The error message you're seeing indicates that the SQL query you're trying to execute is being canceled due to a statement timeout. This means that the query is taking too long to execute, and your database server is configured to cancel any query that exceeds a certain execution time threshold.
Here are some steps you can take to understand and fix this issue:

  1. Examine the Execution Plan: Use the EXPLAIN command to get the execution plan for your query. This will show you where the query might be inefficient, such as performing full table scans or using nested loops that could be optimized. (Click here to learn more about the EXPLAIN command with Snowflake).
  2. Optimize the Query: Look for ways to make the query more efficient. This could involve adding indexes to the columns used in the WHERE clause and the ILIKE conditions, rewriting the query to reduce complexity, or breaking it into smaller parts.
  3. Reduce the Dataset: If possible, limit the scope of the query. For example, if you're querying a large date range or a large number of rows, see if you can reduce that range with LIMIT.
  4. Increase the Statement Timeout (not recommended): If you have control over the database server settings, you can increase the statement timeout value. This is a temporary solution and may not be ideal if the query is inherently inefficient.
Was this article helpful? 0 out of 0 found this helpful