Terraforming Snowflake ️
Published: 2022-07-19 19:00:36

---
title: Terraforming Snowflake ❄️
published: true
date: 2022-07-18 06:59:00 UTC
tags: 
canonical_url: https://dataroots.io/research/contributions/terraforming-snowflake
---

It should go without saying that _data_ is a critical asset for any organization. As a result, it is important that the platform handling all this data is able to do so with scalability and speed in mind.

Enter... 🥁🥁🥁 Snowflake!

<!--kg-card-begin: markdown-->

[Snowflake](https://www.snowflake.com/) is a cloud platform for data `x`, where `x = [warehousing, lakes, engineering, science, application, sharing]`. So everything that is data-related basically.

<!--kg-card-end: markdown-->

[Terraform](https://www.terraform.io/), on the other hand, is an infrastructure-as-code tool that allows you to define cloud resources in human-readable configuration files that you can version, reuse and share.  

What better combo than Snowflake ++ Terraform could there be ?

In this article, we will demonstrate how we used Terraform to set up our Snowflake resources when working on _[Weather Nowcasting - deploying a model on edge](https://dataroots.io/research/contributions/weather-nowcasting)._ On top of that, we will present how to load the content of an Azure container into a Snowflake table.

* * *

There are more than 1700 [providers](https://registry.terraform.io/) to manage lots of different resources and services. Amongst those, you can also find some providers for Snowflake, but the most used one is provided by [chanzuckerberg](https://registry.terraform.io/providers/chanzuckerberg/snowflake/latest). We have based our Terraform template on the one mentioned above and will describe step by step how you can create Snowflake resources with our template.

<!--kg-card-begin: markdown-->
## Snowflake account information
<!--kg-card-end: markdown-->

In order to connect Snowflake to the Terraform code, you will need to find out some information about your account in the first place.

> An account identifier uniquely identifies a Snowflake account within your organization, as well as throughout the global network of Snowflake-supported cloud platforms and cloud regions. [[snowflake.com](https://docs.snowflake.com/en/user-guide/admin-account-identifier.html)]

<!--kg-card-begin: markdown-->

The URL provides all the information needed: `https://app.snowflake.com/<region-id>.<cloud-provider>/<account-locator>/worksheets`

<!--kg-card-end: markdown--><!--kg-card-begin: markdown-->

`Account locator` is an identifier assigned by Snowflake when the account is created. Its value can be seen either in the URL, or by running the following command in a Worksheet: `SELECT current_account() as YOUR_ACCOUNT_LOCATOR;`

<!--kg-card-end: markdown--><!--kg-card-begin: markdown-->

`Snowflake region id` represents the cloud region where the account is located. Its value can be seen either in the URL, or by running the following command in a Worksheet: `SELECT current_region() as YOUR_SNOWFLAKE_REGION_ID;`  
The value returned must then be searched (in [this](https://docs.snowflake.com/en/user-guide/admin-account-identifier.html#region-ids) table) and its corresponding _cloud region id_ selected.

<!--kg-card-end: markdown-->

At this point, your information should be, for example:

<!--kg-card-begin: markdown-->

`SNOWFLAKE_ACCOUNT = xy12345`  
`SNOWFLAKE_REGION = north-europe.azure`

<!--kg-card-end: markdown-->
* * *
<!--kg-card-begin: markdown-->
## Connect Snowflake & Terraform
<!--kg-card-end: markdown-->

In order to establish a connection between Snowflake and Terraform, you also need to provide some credentials, namely a user and a password.

<!--kg-card-begin: markdown-->
#### Create RSA key for authentication
<!--kg-card-end: markdown-->

We will use the RSA key, but there are other ways of authenticating. You can check them out [here](https://registry.terraform.io/providers/chanzuckerberg/snowflake/latest/docs).

The following commands will generate a public/private key pair:

<!--kg-card-begin: markdown-->

`cd ~/.ssh`  
`openssl genrsa -out snowflake_key 4096`  
`openssl rsa -in snowflake_key -pubout -out snowflake_key.pub`

<!--kg-card-end: markdown-->

Now you should be able to view your public key, which should have a content similar to the following one:

<!--kg-card-begin: markdown-->

`-----BEGIN PUBLIC KEY-----`  
`<PUBLIC_KEY>`  
`-----END PUBLIC KEY-----`

<!--kg-card-end: markdown--><!--kg-card-begin: markdown-->
#### Create service user for authentication
<!--kg-card-end: markdown--><!--kg-card-begin: markdown-->

In order to create a new user, which will be used for establishing the connection to Terraform, you can run the following commands in a Worksheet. Notice how we are directly linking the user to the newly created RSA key as authentication method:

```
USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE USER "tf-snow" RSA_PUBLIC_KEY='<PUBLIC_KEY>' DEFAULT_ROLE=PUBLIC MUST_CHANGE_PASSWORD=FALSE;
GRANT ROLE SYSADMIN TO USER "tf-snow";
GRANT ROLE SECURITYADMIN TO USER "tf-snow";

```
<!--kg-card-end: markdown-->

Up until this point, you should have the following information:

<!--kg-card-begin: markdown-->

`SNOWFLAKE_USER = tf-snow`  
`SNOWFLAKE_PASSWORD = <PUBLIC_KEY>`

<!--kg-card-end: markdown-->
* * *
<!--kg-card-begin: markdown-->
## Project structure
<!--kg-card-end: markdown-->

![](https://dataroots.ghost.io/content/images/2022/05/Screenshot-2022-05-05-at-16.43.38-1.png)
_Figure 1. Structure of the Terraform folder_

<!--kg-card-begin: markdown-->

The **root** folder contains the `main.tf`, `variables.tf` and `.terraform.lock.hcl` files. The later represents the dependency lock file and you can read more about it [here](https://www.terraform.io/language/files/dependency-lock).

<!--kg-card-end: markdown--><!--kg-card-begin: markdown-->

We have chosen to create only two environments, `prod` and `staging`. Thus, you will notice that these are the names of the tfvars that we will further present.  
Moreover, in the code you will often see `var.env` or `local.env_upper`, which is the upper case version of `var.env`.

<!--kg-card-end: markdown--><!--kg-card-begin: markdown-->

In the **backend** folder we have the `prod.tfvars` and `staging.tfvars`. Each file contains a key value, which corresponds to the name of the file which will contain the state of the resources at any time, e.g. `prod.tfvars` has the content `key = "prod.tfstate"`

<!--kg-card-end: markdown-->

![](https://dataroots.ghost.io/content/images/2022/05/Screenshot-2022-05-06-at-12.57.42.png)
_Figure 2. Backend files_

<!--kg-card-begin: markdown-->

The **vars** folder contains `prod.tfvars` and `staging.tfvars`, but they contain references to the environment variables. For example, `prod.tfvars` has the content set to:

```
env = "prod"
subscription_id = env.ARM_SUBSCRIPTION_ID
tenant_id = env.ARM_TENANT_ID

```
<!--kg-card-end: markdown--><!--kg-card-begin: markdown-->

You can notice two more folders, namely `azure` and `snowflake`. These are the modules that will later be described in more detail. You can read more about Terraform modules [here](https://www.terraform.io/language/modules/develop).

<!--kg-card-end: markdown-->
* * *
<!--kg-card-begin: markdown-->
## Set up the Snowflake provider
<!--kg-card-end: markdown--><!--kg-card-begin: markdown-->

In order to define the Snowflake provider, you should include the following blocks of code in your `main.tf` file:

<!--kg-card-end: markdown--><!--kg-card-begin: markdown-->
```
terraform {
  required_version = ">= 1.1"
  required_providers {
    snowflake = {
      source = "chanzuckerberg/snowflake"
      version = "0.31.0"
    }
  }
}

provider "snowflake" {
  features {}
  alias = "terraform"
  role = "ACCOUNTADMIN"
}

module "snowflake" {
  source = "./snowflake"
  env = var.env
}

```
<!--kg-card-end: markdown--><!--kg-card-begin: markdown-->

Now we have to create a `main.tf` file in the `snowflake` module, as this is the source defined in the previous step. Its content should contain the following:

<!--kg-card-end: markdown--><!--kg-card-begin: markdown-->
```
terraform {
  required_providers {
    snowflake = {
      source = "chanzuckerberg/snowflake"
      version = "0.31.0"
    }
  }
}

provider "snowflake" {
  alias = "terraform"
  role = "ACCOUNTADMIN"
}

```
<!--kg-card-end: markdown-->

Next, we will continue to describe all the necessary steps to set up the resources properly. You can find all the code in our Github repository.

* * *
<!--kg-card-begin: markdown-->
## Initialize Snowflake
<!--kg-card-end: markdown-->

There are 3 mandatory resources that we need to create. These are: [database](https://registry.terraform.io/providers/chanzuckerberg/snowflake/latest/docs/resources/database), [schema](https://registry.terraform.io/providers/chanzuckerberg/snowflake/latest/docs/resources/schema) and [warehouse](https://registry.terraform.io/providers/chanzuckerberg/snowflake/latest/docs/resources/warehouse).

<!--kg-card-begin: markdown-->
```
resource "snowflake_database" "main" {
  provider = snowflake.terraform
  name = "WEATHER_${local.env_upper}"
}

resource "snowflake_warehouse" "weather" {
  provider = snowflake.terraform
  name = "WEATHER_WHS_${local.env_upper}"
  warehouse_size = "xsmall"
  auto_suspend = 60
}

resource "snowflake_schema" "datawarehouse" {
  provider = snowflake.terraform
  database = snowflake_database.main.name
  name = "DATAWAREHOUSE_${local.env_upper}"
  is_managed = false
}

```
<!--kg-card-end: markdown--><!--kg-card-begin: markdown-->

Our scenario included retrieving the content of a JSON file in real time from an Azure container. Thus, we only needed a [table](https://registry.terraform.io/providers/chanzuckerberg/snowflake/latest/docs/resources/table) with a single column of type `VARIANT`.

<!--kg-card-end: markdown--><!--kg-card-begin: markdown-->
```
resource "snowflake_table" "sensor" {
  provider = snowflake.terraform
  database = snowflake_database.main.name
  schema = snowflake_schema.datawarehouse.name
  name = "WEATHER_JSON_${local.env_upper}"
  column {
    name = "var"
    type = "VARIANT"
    comment = "Raw sensor data"
  }
}

```
<!--kg-card-end: markdown-->

In order to be able to customize our JSON input, we have chosen to create a custom [file format](https://registry.terraform.io/providers/chanzuckerberg/snowflake/latest/docs/resources/file_format):

<!--kg-card-begin: markdown-->
```
resource "snowflake_file_format" "json" {
  provider = snowflake.terraform
  name = "JSON_DQ_${local.env_upper}"
  database = snowflake_database.main.name
  schema = snowflake_schema.datawarehouse.name
  format_type = "JSON"
  strip_outer_array = true
  compression = "NONE"
  binary_format = "HEX"
  date_format = "AUTO"
  time_format = "AUTO"
  timestamp_format = "AUTO"
  skip_byte_order_mark = true
}

```
<!--kg-card-end: markdown-->
* * *
<!--kg-card-begin: markdown-->
## Create storage integration
<!--kg-card-end: markdown-->

> A storage integration is a Snowflake object that stores a generated identity and access management (IAM) entity for your external cloud storage, along with an optional set of allowed or blocked storage locations. [[snowflake.com](https://docs.snowflake.com/en/sql-reference/sql/create-storage-integration.html)]

We reached the part where the chosen cloud provider is becoming important; and in this case we are working with Azure. Thus, for this step, on your Azure account you should have created already a _[storage account](https://docs.microsoft.com/en-us/azure/storage/common/storage-account-overview) (Standard general-purpose v2)_ and a container. You can now link Snowflake to the container like below:

<!--kg-card-begin: markdown-->
```
  provider = snowflake.terraform
  type = "EXTERNAL_STAGE"
  storage_provider = "AZURE"
  azure_tenant_id = data.azurerm_client_config.current.tenant_id
  name = "AZURE_INTEGRATION_${local.env_upper}"
  comment = "A storage integration for Azure blobs."
  enabled = true
  storage_allowed_locations = ["azure://<storage-account-name>.blob.core.windows.net/<container-name>"]
}

```
<!--kg-card-end: markdown-->
* * *
<!--kg-card-begin: markdown-->
## Create notification integration
<!--kg-card-end: markdown-->

> A notification integration is a Snowflake object that provides an interface between Snowflake and third-party cloud message queuing services. [[snowflake.com](https://docs.snowflake.com/en/sql-reference/sql/create-notification-integration.html)]

On your Azure account, you should have already created a queue inside your storage account, which you can then also link to Snowflake.

<!--kg-card-begin: markdown-->
```
resource "snowflake_notification_integration" "azure_queue" {
  provider = snowflake.terraform
  name = "WEATHER_NOTIFICATION_${local.env_upper}"
  enabled = true
  type = "QUEUE"
  notification_provider = "AZURE_STORAGE_QUEUE"
  azure_storage_queue_primary_uri = "https://<storage-account-name>.queue.core.windows.net/<queue-name>"
  azure_tenant_id = data.azurerm_client_config.current.tenant_id
}

```
<!--kg-card-end: markdown-->
* * *
<!--kg-card-begin: markdown-->
## Create a stage
<!--kg-card-end: markdown-->

> A stage is used for loading data from files into Snowflake tables and unloading data from tables into files. [[snowflake.com](https://docs.snowflake.com/en/sql-reference/sql/create-stage.html)]

For this step is important to note that you need to generate a [SAS Token for your container](https://docs.microsoft.com/en-us/azure/cognitive-services/translator/document-translation/create-sas-tokens?tabs=Containers).

<!--kg-card-begin: markdown-->
```
resource "snowflake_stage" "snowstage" {
  provider = snowflake.terraform
  name = "SNOWPIPE_STAGE_${local.env_upper}"
  url = "azure://<storage-account-name>.blob.core.windows.net/<container-name>"
  database = snowflake_database.main.name
  schema = snowflake_schema.datawarehouse.name
  credentials = "AZURE_SAS_TOKEN='${var.sas_token}'"
  file_format = "FORMAT_NAME = ${local.qualified_file_format_name}"
}

```
<!--kg-card-end: markdown-->
* * *
<!--kg-card-begin: markdown-->
## Create a pipe
<!--kg-card-end: markdown-->

> A pipe is created for defining the [COPY INTO <table>](https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html) statement used by [Snowpipe](https://docs.snowflake.com/en/user-guide/data-load-snowpipe.html) to load data from an ingestion queue into tables. [[snowflake.com](https://docs.snowflake.com/en/sql-reference/sql/create-pipe.html)]

Now that we have successfully linked the Azure blob storage to Snowflake, we can create the pipe that will be used to load the data from the blob storage into our snowflake table.

<!--kg-card-begin: markdown-->
```
resource "snowflake_pipe" "snowpipe" {
  provider = snowflake.terraform
  database = snowflake_database.main.name
  schema = snowflake_schema.datawarehouse.name
  name = "WEATHER_PIPE_DQ_${local.env_upper}"

  copy_statement = "copy into ${local.qualified_sensor_table_name} from @${local.qualified_stage_name} file_format = (format_name = ${local.qualified_file_format_name})"
  auto_ingest = true
  integration = snowflake_notification_integration.azure_queue.name
}

```
<!--kg-card-end: markdown-->
* * *
<!--kg-card-begin: markdown-->
## Manual steps
<!--kg-card-end: markdown-->

At this point, all your resources are created and everything should be set up. But if you try to test the data flow, you will notice an annoying error similar to:

<!--kg-card-begin: markdown-->

`COULD NOT ACCESS QUEUE; THIS REQUEST IS NOT AUTHORIZED TO PERFORM THIS OPERATION USING THIS PERMISSION. (STATUS CODE:403; ERROR CODE:AUTHORIZATIONPERMISSIONMISMATCH)”`

<!--kg-card-end: markdown-->

Now it's time to move again to your Snowflake worksheets and to [grant access to your storage container](https://docs.snowflake.com/en/user-guide/data-load-azure-config.html#step-2-grant-snowflake-access-to-the-storage-locations). In order to do that, you have to follow two steps.

<!--kg-card-begin: markdown-->

The first one consists of finding the information for you storage integration. This can be done with the command: `desc STORAGE INTEGRATION <NAME OF YOUR STORAGE INTEGRATION>;`  
Now you will see a table with all the properties of your storage integration. The important ones are:

- `AZURE_CONSENT_URL`, which you should go to and allow permission
- `AZURE_MULTI_TENANT_APP_NAME`, which is the form `SnowflakePACInt1111_1234567899876` - and you should only take into account the first part `SnowflakePACInt1111`  
Moving to your Azure storage account, you should add the roles `Storage Queue Data Contributor` and `Storage Blob Data Contributor` to `SnowflakePACInt1111`
<!--kg-card-end: markdown--><!--kg-card-begin: markdown-->

The second step is to perform the same steps, but for the notification integration: `DESC NOTIFICATION INTEGRATION <NAME OF YOUR NOTIFICATION INTEGRATION>;`

<!--kg-card-end: markdown-->
* * *
<!--kg-card-begin: markdown-->
## Applying changes
<!--kg-card-end: markdown-->

As you may already know, in order to apply the changes made to your Terraform code on the Snowflake resources, you need to run some commands. We will present briefly what needs to be done at this step.

#### Terraform initialize
<button><svg id="Regular" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 24 24"><path d="M23.25,7.311,12.53,18.03a.749.749,0,0,1-1.06,0L.75,7.311"></path></svg></button>

used to initialize a working directory containing Terraform configuration files. This is the first command that should be run after writing a new Terraform configuration or cloning an existing one from version control. It is safe to run this command multiple times. [[terraform.com](https://www.terraform.io/cli/commands/init)]

e.g. terraform init -backend-config backend/staging.tfvars

#### Terraform format
<button><svg id="Regular" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 24 24"><path d="M23.25,7.311,12.53,18.03a.749.749,0,0,1-1.06,0L.75,7.311"></path></svg></button>

used to rewrite Terraform configuration files to a canonical format and style. This command applies a subset of the [Terraform language style conventions](https://www.terraform.io/language/syntax/style), along with other minor adjustments for readability. [[terraform.com](https://www.terraform.io/cli/commands/fmt)]

e.g. terraform fmt -check -recursive

#### Terraform validate
<button><svg id="Regular" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 24 24"><path d="M23.25,7.311,12.53,18.03a.749.749,0,0,1-1.06,0L.75,7.311"></path></svg></button>

validates the configuration files in a directory, referring only to the configuration and not accessing any remote services such as remote state, provider APIs, etc. [[terraform.com](https://www.terraform.io/cli/commands/validate)]

e.g. terraform validate

#### Terraform plan
<button><svg id="Regular" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 24 24"><path d="M23.25,7.311,12.53,18.03a.749.749,0,0,1-1.06,0L.75,7.311"></path></svg></button>

creates an execution plan, which lets you preview the changes that Terraform plans to make to your infrastructure. [[terraform.com](https://www.terraform.io/cli/commands/plan)]

e.g. terraform plan -var-file vars/staging.tfvars

#### Terraform apply
<button><svg id="Regular" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 24 24"><path d="M23.25,7.311,12.53,18.03a.749.749,0,0,1-1.06,0L.75,7.311"></path></svg></button>

executes the actions proposed in a Terraform plan. [[terraform.com](https://www.terraform.io/cli/commands/apply)]

e.g. terraform apply -var-file vars/staging.tfvars

* * *
<!--kg-card-begin: markdown-->
## Conclusion
<!--kg-card-end: markdown-->

Now everything should be set up and as soon as you upload a file into your container, its content should be copied into the table. For example, let's consider a JSON file with the following content:

<!--kg-card-begin: markdown-->
```
{
  "Body": {
    "ts": "2022-04-08T15:07:29.130826",
    "tz": "CEST",
    "message": {
      "temperature": 29.17,
      "humidity": 22.5,
      "pressure": 994.53,
      "light": 159.26,
      "uv": 0.04,
      "ir": 278
    }
  }
}

```
<!--kg-card-end: markdown-->

We will be able to see this row in our table, as can be observed in Figure 3.

![](https://dataroots.ghost.io/content/images/2022/05/Screenshot-2022-05-06-at-12.42.27.png)
_Figure 3. Table entry from JSON file_

* * *

In this blog post we showed how to ingest data in real time from Azure storage containers into Snowflake, while creating the resources using Terraform.

<!--kg-card-begin: markdown-->

Using `terraform plan` we verify that the changes applied to the resources are indeed what we are trying to do. Then, by `terraform apply` you actually apply those changes!

<!--kg-card-end: markdown-->

By managing our Snowflake account through Terraform we automated this error-prone process. Thus, there is no need to manually update the same resource in every environment. On top of that, by using CI/CD we simplified even more the management of resources.

Indeed, the Snowflake provider for Terraform is documented, but you will come to find out that not all the resources are updated or that some contain copy-paste bugs sometimes. Just don't take everything for granted and ask yourself if some properties make sense!

Finally, we challenge you to try and recreate this project and give an update to us about how it went!

* * *
<!--kg-card-begin: markdown-->
- **[GitHub repository](https://github.com/datarootsio/terraform-module-azure-snowflake)** where you can find the source code for this blog post
<!--kg-card-end: markdown-->