How to Connect to Snowflake

Overview:

When you download AtScale Developer Community Edition, you get a pre-installed connection to Postgres. However, you can also connect it to other data platforms that you may want to use.

In this article, we’ll give you a step-by-step guide to connect the AtScale Design Center to Snowflake.

Prerequisite:

Ensure you have Snowflake installed on your computer and a valid license.

Download and install the AtScale Developer Community Edition following these instructions in the Quick Start Guide.

Install:

For this guide, we will use the tutorial models that come pre-installed with AtScale Community Edition as a sample.

Open the GitHub repo sml-models. There, you will find the three tutorial models in the Model Library. These are:

• Internet Sales – a simple, single-fact model derived from the fictitious AdventureWorks retail dataset.
• World Wide Importers – a more complex, multi-fact model that represents a fictional wholesale and distribution company
• TPC-DS – a complex, multi-fact model that encodes the TPC-DS benchmark model in SML.

Access Your Data Set

Open Snowflake. Make sure you’re logged in as an admin.

1. In the left menu, go to Data Products and select Marketplace.
2. Search for AtScale, bringing up the free AtScale Tutorials data set.
   
3. Open AtScale Tutorials. Details on the three models available will be displayed.
4. Click the Get button.
5. Expand the Options menu.
6. Override the database name to “atscale_tutorial_data” (all lowercase).
   Note: This catalog name will appear when you connect it to your SML.
7. Choose which roles will give access to the database.
   
8. Click Get, then Query data.

The raw data for the three models, internet-sales, ww_importers, and tpc-ds, is now loaded into the Snowflake instance.

Set Up a Data Warehouse Connection with AtScale

There are two stages to creating a connection to a cloud data warehouse.

1. Create a Data Warehouse group.
2. Establish the connection.

Data warehouse groups allow different resources for different activities, such as serving queries or building aggregates, which might require warehouses of various sizes. In this example, we will simply create a single connection.

Create a Data Warehouse Group

Go to AtScale Design Center. In the Repo Browser, you will see your three tutorial models.

1. Click the ellipsis next to the AtScale icon at the top left.
2. Choose Settings from the drop-down menu and select Data Warehouses.
   Note: AtScale Developer Community Edition has a pre-installed connection to Postgres. The tutorial models are set up to point to this Postgres connection by default. Instead, we will set up a Snowflake connection for the models to point to.
3. Click on the Snowflake iconto open the Add Data Warehouse panel.
4. Enter “Snowflake” in the Name field.
5. In the External connection ID field, also type in “Snowflake”.
   Note: This external connection ID will be the SML connection object pointing to Snowflake.
   
   
6. For this guide, enter “DEMO” in the Aggregate Catalog field.
7. Enter “AGGREGATES” in the Aggregate Schema field.
   Note: The aggregate catalog and schema will define where AtScale will write and store the aggregate tables it generates based on query activity. Ensure you have read/write access to your database and schema.
8. Click Apply.
9. When the screen refreshes, the new Snowflake warehouse will appear. However, the status will read “No connections defined.”

You have now created the connection group. The next stage is to establish a connection.

Establish the Connection

1. Click the ellipsis on the Snowflake warehouse.
2. Click + to add a new connection.
   
3. Enter “Snowflake” as the connection name.
   Note: When setting up a data warehouse group with multiple connections, use more specific or descriptive names, such as “Snowflake aggregates” or “Snowflake small queries”.
4. Enter the name of the Snowflake data warehouse you want AtScale to call up.
   Note: Ensure you choose a warehouse of an appropriate size, as AtScale will hit the aggregates very quickly.
   
   
5. In the Host field, enter the same hostname as when you logged into Snowflake.
6. Type in your username and password.
   Note: AtScale offers a range of Authentication Methods. For this tutorial, we are using Password authentication.
7. Click Test connection. A “Connection successful” notification should appear.
8. Click Apply to complete the process.
9. When the screen updates, the new Snowflake warehouse should display Pool and Connection as “UP”.

Snowflake is now connected correctly.

Change the Connection Objects in SML

You now need to refer to the new SML data warehouse connection for each tutorial data model.

1. Open the Repo Browser from the AtScale icon.
2. In the Workspace, go to models→tutorials→internet-sales→connections.
   
3. Click on Connection – Internet Sales.yml
4. In the Text Editor, change the AtScale connection object (as_connection) from “Postgres” to “Snowflake”.
   Note: This must match the external connection ID you entered when creating the data warehouse group earlier. Note also that the database is displayed with the catalog name “atscale_tutorial_data” you created when accessing the dataset from the Snowflake Marketplace.
   
   
5. Click Save file at the bottom of the page.
6. Click Deploy in the Repo browser.
7. In the Deploy menu, click Deploy.
   
8. Once it has deployed, click the Catalogs icon on the left sidebar.
9. Go to Repo: AtScale Tutorials→sml-models_main to pull down your connection strings.
10. Repeat this process for the other tutorial models: tpc_ds and ww_importers.

With the connection deployed, you can run queries on your models against Snowflake instead of Postgres.

Watch all of the Developer Community Edition demo videos on demand.