Visit Matillion AI Playground at Snowflake Data Cloud Summit 24

Find out more

Best Practices For Connecting Matillion ETL To Azure Blob Storage

Matillion makes data work more productive by empowering the entire data team – coders and non-coders alike – to move, transform, and orchestrate data pipelines faster. It has lots of great ways to maximize the power of your Azure Blob Storage by orchestrating your data into and unloading from your Data Platform, or simply using it for staging. 

Components include:

  • Azure Load: loading your data into your data warehouse
  • Azure Load Generator Wizard: this generates the table schema and Azure Load configuration for you in an easy-to-follow wizard 
  • Azure Unload: unloading your data from your data warehouse into your chosen blob


It can be technically challenging in Azure to set up the initial connection to your chosen storage account. Here are some common reasons:

  1. Cross Subscription / Tenant access 
  2. Incorrect role permissions 
  3. Storage account firewall blocking access 
  4. Outbound Network access restricted

You will also need to repeat if you switch to a new virtual machine (for example during a migration or when restoring from a backup).

Best Practices

When setting up permissions in Azure, it can be easy to grant these on a virtual instance basis. However, this can come back to haunt you later when you set up additional virtual machines or when you have to migrate to a new version. Here are some key best practices when connecting to your Azure Blob Container:  

  • Use app credentials. These can then be re-used on multiple Matillion ETL instances
  • Keep one app for one specific subscription. Matillion ETL locks each environment connection down to one subscription, so by using one app, you won’t end up causing problems by locking into the wrong subscription
  • Create one environment connection in Matillion ETL, for access to one subscription. This is the number one cause for issues connecting to cross-subscription resources
  • Follow the least privilege rule and assign the Storage Account Contributor Role. Other access roles are not required
  • If you restrict network access on the Storage Account, you can only do this if you are using Matillion ETL for Snowflake. This is not supported for Matillion ETL for Synapse or Databricks

Azure RBAC Implementation

When connecting to your Azure Blob Container, follow these simple steps to create a service principal and grant it access to your blob storage. 


Setting up the App
  1. Login into the Azure portal - https://portal.azure.com
  2. Select the Tenant / Subscription you wish to create a connection in
  3. Search for “App Registrations”
  4. Click on the “App Registrations”
  5. Click “New Registration”
  6. Give it a memorable name
  7. Select the “Supported account types”: “Accounts in this organizational directory only (Matillion Limited only - Single tenant)”
  8. Click Register

 

Granting permissions to the Blob

  1. Go to the Storage Account you wish to give access 
  2. Click “Access Control (IAM)”
  3. Click Add > “Add role assignment”
  4. Select “Storage Account Contributor”
  5. Add the app you just created as a Member by clicking on “Select Members”
  6. Click “Review + assign”

 

How to Test a Managed Identity

In Matillion ETL you can now add your app to finish your setup: 

  1. For each connection to a specific subscription, create a new environment by clicking on the Project Menu > Add Environment
  2. Click “Manage” next to the “Azure Credentials:” to add your new app the this environment
  3. Click the + under the “User Defined Credentials” and add all the correct credentials taken from your app registration
  4. When you click on “Test” it should now bring back “Blob Storage: success”
  5. Click OK and make sure you select your app instead of “Instance Credentials, from the drop-down next to Azure Credentials

 

You are now ready to access your Azure Storage Account Blob! For additional resources and information on Azure Blog storage, check out the following articles: 

To get started with a free trial of Matillion, sign up for free!

Adam Smith
Adam Smith

Senior Solution Architect - DevOps