Migrating Azure Data Factory’s Snowflake Connector from Legacy to latest V2

We recently announced a new updated V2 of our popular Snowflake connector in Azure Data Factory. The new connector promises better performance and security. The new V2 connector supports pipelines and data flows and comes with the new Go-based Snowflake driver.

Differences between legacy connectors and V2 connectors

The V2 connector offers improvements in performance and security. The main differences between legacy connectors and V2 connectors are:

The V2 version offers several enhancements over the legacy version, including future features such as autoscaling, multi-availability, static IPs, improved storage consolidation support, and Iceberg support. For more information connector documentation.

Deprecation of legacy connector support

Snowflake Legacy Connector ends support on October 31st^castleWhen support ends in 2024, the connector will be considered deprecated and will no longer be supported. If you want to continue using your connector and experience new enhancements, we recommend upgrading your connector to the new V2 connector. read official announcement To know more.

Migrate from Legacy to V2 Connector

The steps required to update from Legacy to V2 connector include the following process:

• Check whether script parameters are being used.
  • If so, modify it to use dynamic expressions.
• Check if additional connection parameters are being used
  • If so, those parameters cannot be applied to the new connector.
• Update services connected to V2 connectors
• Update dataset to V2 dataset

Connected service updates:

Below is a typical example of a linked service using parameters and basic authentication.

legacyV2

• You need to change the connector version to V2.
• In your connection string, you need to update the type attribute to include the following:

"typeProperties": {
    "authenticationType": "Basic",
    "accountIdentifier": "@{linkedService().accountIdentifier}",
    "user": "@{linkedService().user}",
    "database": "@{linkedService().database}",
    "warehouse": "@{linkedService().warehouse}",
    "encryptedCredential": ""
}

Dataset update:

You will need to update your dataset to include V2 of the connector. Update property type from snowflake table to Snowflake V2 Table.

Update definition file:

Linked services and datasets can be updated through Git. To update linked services, you can use the following steps:

• Create a new branch from the main branch
• Update definition files
• Connection and pipeline testing
• Give a pull-request and merge it with the main branch.

All other branches must be pulled from this branch, or the branches must be manually changed to match the definition.

Collect the data factory list using the legacy connector:

If you have a large environment and need to find a list of data factories that use legacy connectors, you can use PowerShell to find them. This program will run for a long time depending on the number of resources.

You can access this code: gist.

Verification with Snowflake query history:

Queries generated by Data Factory users can be validated in Snowflake to determine if they are generated by a legacy or V2 connector.

SELECT DISTINCT CLIENT_APPLICATION_ID, USER_NAME
FROM SNOWFLAKE.ACCOUNT_USAGE.SESSIONS
WHERE CREATED_ON > DATEADD(DAY, -1, CURRENT_TIMESTAMP) // OPTIONAL: FILTER BY TIME
AND USER_NAME = '' // THE USER THAT IS CONFIGURED IN DATA FACTORY
ORDER BY CLIENT_APPLICATION_ID;

Legacy connectors have their versions after “ODBC”, and V2 connectors have their versions after “GO”. If you only want to see connections coming from ODBC, you can filter the client_application_id column.

SELECT DISTINCT CLIENT_APPLICATION_ID, USER_NAME
FROM SNOWFLAKE.ACCOUNT_USAGE.SESSIONS
WHERE CREATED_ON > DATEADD(DAY, -1, CURRENT_TIMESTAMP) // OPTIONAL: FILTER BY TIME
AND USER_NAME = '' // THE USER THAT IS CONFIGURED IN DATA FACTORY
AND CLIENT_APPLICATION_ID LIKE '%ODBC%' // FILTER BY ODBC FOR LEGACY CONNECTOR
ORDER BY CLIENT_APPLICATION_ID;

Best practices:

You can apply the following best practices to Data Factory to make migrating your connectors easier.

• Using Git for version control in Data Factory
• Create a separate branch to merge with the main branch and other branches to manage only connected services without pipelines/artifacts.
• Updated username/password based authentication to KeyPair authentication for increased security
• Store password/key pair keys using Key Vault
• Test your connector on a specific development environment/branch before pushing it to the main branch.

We encourage you to try our new and improved connector. If you’re having trouble migrating or need help with: