Blog Post

Azure Data Factory Blog
1 MIN READ

ADF now supports data integration with Snowflake

Linda_Wang's avatar
Linda_Wang
Icon for Microsoft rankMicrosoft
Jun 08, 2020

Azure Data Factory provides 90+ built-in connectors allowing you to easily integrate with various data stores regardless of variety of volume, whether they are on premises or in the cloud.

 

We are glad to share that ADF newly added support for Snowflake connector with the following capabilities to fulfill your Snowflake data integration need:

  • Ingest data from Snowflake into any supported sinks (e.g. data lake) using Copy activity, which utilizes Snowflake’s COPY into <location> command to achieve the best performance;
  • Look up data from Snowflake for ADF control flow consumption using Lookup activity;
  • Load data into Snowflake from 90 plus supported sources using Copy activity, which takes advantage of Snowflake’s COPY into <table> command to achieve the best performance.

You can now find Snowflake connector from ADF connector gallery UI. Learn more from Snowflake connector documentation.

 

Upcoming:

Next, we plan to enable the following to further enrich the ADF-Snowflake integration capabilities. Stay tuned!

  • Load semi-structured data from JSON files into Snowflake VARIANT column using Copy activity, for data processing in Snowflake subsequently. [Update 2020/7/12: now data factory supports direct copy between JSON files and Snowflake semi-structured data types, learn more from direct copy from Snowflake and direct copy to Snowflake.]
  • Support Snowflake in Mapping Data Flow as source and sink transformations to provide seamless ETL. [Update 2020/8/31: ADF enables Snowflake connector in Mapping Data Flow, learn more from this blog.]
Updated Aug 31, 2020
Version 4.0
azure data factory
Azure Data Integration

69 Comments

Show More
  • adfguy's avatar
    adfguy
    Copper Contributor
    Jun 10, 2020

    Linda_Wang 

    so we need to wait till Stored Procedure Activity rolls out the support for Snowflake it seems. 

     

  • Linda_Wang's avatar
    Linda_Wang
    Icon for Microsoft rankMicrosoft
    Jun 10, 2020

    adfguy thanks for sharing the details. Executing stored proc on source/lookup is not supported now. The way ADF submits query to Snowflake doesn't handle SP as you observed. I was trying to understand more on your workflow logic w/ lookup + SP to see what scenario is blocked by lacking of such capability, or if you can use query instead. Hope it clarifies.

  • adfguy's avatar
    adfguy
    Copper Contributor
    Jun 10, 2020

    Linda_Wang   Hi Linda. Hope you're doing good.

     

    I created a schemaless dataset against the Snowflake linked service- then i used that dataset in a lookup activity and called a stored procedure from it.

    Surprisingly- Stored proc ran just fine in the database but ADF doesn't seem to capture the return value and it fails.

     

    Please see the attached screenshots for more understanding.Query log at Snowflake levelADF execution logLookup activity configured to call stored procedure of snowflakeSchemaless dataset

     

     
     

     

     

     
     

     

     

  • Linda_Wang's avatar
    Linda_Wang
    Icon for Microsoft rankMicrosoft
    Jun 10, 2020

    Srini2020 Snowflake stored procedure execution is on the radar though we don't have timeline at the moment. To understand more, what kind of Snowflake DML/DDL operations you want to execute in the workflow?

  • Linda_Wang's avatar
    Linda_Wang
    Icon for Microsoft rankMicrosoft
    Jun 10, 2020

    adfguy currently ADF Snowflake connector doesn't support executing stored proc. We will track the ask. Do you mind sharing your scenario with lookup + sp?

  • Srini2020's avatar
    Srini2020
    Copper Contributor
    Jun 09, 2020

    Thank you for the update Linda_Wang 

     

    Please let us know if Snowflake stored procedure execution is going to be available using this connector in the future? Looks like this would only cover ingesting data into Snowflake but not execute any DML or DDL statements including stored procedures.

  • adfguy's avatar
    adfguy
    Copper Contributor
    Jun 09, 2020

    Great update Linda_Wang !!!

     

    I tried executing Snowflake stored procedure from Lookup activity and it succeeds at the database level but errors out in the UI - 

     

    { "errorCode": "2100", "message": "Failure happened on 'Source' side. ErrorCode=UserErrorOdbcInvalidQueryString,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The following ODBC Query is not valid: 'call CDM.test1('c')'.,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,'", "failureType": "UserError", "target": "Lookup1", "details": [] }

     

     

    a known issue ?

  • Linda_Wang's avatar
    Linda_Wang
    Icon for Microsoft rankMicrosoft
    Jun 09, 2020

    avixorld this Snowflake connector utilizes Snowflake's most recommended and performant way to load data and can scale for large volume of data. Not sure what exact mechanism your client used earlier. If any perf issue going forward, please keep raising support ticket and we'd love to look into the specific case.

  • avixorld's avatar
    avixorld
    Copper Contributor
    Jun 08, 2020

    In one of our energy client(can't take the name for confidentiality) we found the performance of ADF when loading data into Snowflake was not great. Then raised a ticket to Microsoft and MS support team advised to use Matillion inside Azure. After that we found performance of data load been increased. It's around 3/4 months old story. Does this still true for large volume of data load?