Pre-requisites¶

Before configuring a Data Intake Process (DIP) for Snowflake, you must allow Snowflake to access your Sidra DSU Stage Storage. First, verify that Snowflake supports the Azure region hosting your Sidra storage. For more information, see Snowflake Supported Cloud Regions.

Ensure that the following steps are completed:

Create a Cloud Storage Integration in Snowflake: The Snowflake Storage Integration object delegates authentication responsibility for access to the Sidra DSU Stage Storage Account to an Azure service principal.
Grant the service principal access to Sidra DSU stage storage: The service principal created by the Storage Integration needs the 'Storage Blob Data Contributor' role for the Sidra DSU Stage Storage, allowing Snowflake to write to that Storage Account.
Add network rules for Snowflake: Snowflake will provide the subnets required to add to Sidra DSU Stage Storage.

Create a Cloud Storage Integration in Snowflake and Grant Access to Sidra DSU Stage Storage¶

Refer to the Snowflake documentation, and take the following steps into consideration as you proceed:

In Step 1: Create a Cloud Storage Integration in Snowflake

The Storage Integration will be:

CREATE STORAGE INTEGRATION <integration_name>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'AZURE'
  ENABLED = TRUE
  AZURE_TENANT_ID = '<tenant_id>'
  STORAGE_ALLOWED_LOCATIONS = ('azure://<DSUStageStorageAccountName>.blob.core.windows.net/metadatastaging/', 'azure://<DSUStageStorageAccountName>.blob.core.windows.net/dataextraction/')

Where <DSUStageStorageAccountName> is in the DSU Resource Group and the name will be similar to: <prefix>stastage<suffix>. These locations for the Storage Integration will be two Sidra Storage Account Containers used for metadata creation and the intake process.

In Step 2: Grant Snowflake Access to the Storage Locations

The role to grant is 'Storage Blob Data Contributor' for the Storage Account <DSUStageStorageAccountName>.

In Step 3: Create an External Stage

This step can be skipped because ADF will be in charge of creating the stage for each operation that requires writing to the Storage Account.

Add Network Rules to Sidra DSU Stage Storage for Snowflake¶

Follow the instructions in the Snowflake documentation, while keeping these additional notes in mind:

In Step 4: Managing Virtual Network Rules The configuration cannot be done through the Azure Portal because we do not have access to the Snowflake subscription. Therefore, we will use Azure-CLI

For each subnet ID returned by the command SELECT SYSTEM$GET_SNOWFLAKE_PLATFORM_INFO();, execute the following command:

az storage account network-rule add --resource-group "<DSUResourceGroup>" --account-name "<DSUStageStorageAccountName>" --subnet $subnetid

Where <DSUResourceGroup> is the name of the resource group where the DSU is located and <DSUStageStorageAccountName> is the name of the Storage Account where the Storage Integration is configured.