CODEWITHSUNDEEP
azuresnowflake-cloud-data-platformazure-data-factory
Hari
Hari

Reputation: 133

Azure Data Factory- How to create a table from one snowflake account into another snowflake account using data factory?

I am trying to create a table in a target snowflake account say snowflake_acccount1 from source snowflake account snowflake_acccount2 using data factory copy activity. But that didn't work as the data set/linked services can support only respective account at a time each.

Below is the query that i want to use to create a table from a snowflake_acccount2 in snowflake_acccount1 using azure data factory

create table if not exist snowflake_acccount1_schema.table1 as select * from snowflake_acccount2_schema.table1

I tried using script activity again that only support for one dataset to connect at a time.

Due to privacy and confidential, i cannot share the pipeline diagram

is there any way to achieve this using ADF?

Upvotes: 0

Views: 591

Answers (1)

Pratik Lad
Pratik Lad

Reputation: 8392

There is no such way to query another database or Copy table from one snowflake account to another in ADF as in snowflake linked service, we supposed to give the default database to be used and copy activity cannon create the table in snowflake it will just transfer the data.

The workaround is to use staging in snowflake as below:

  • First create an external stage in snowflake on Azure Blob storage. you can follow this document to get some idea.
  • Then Load your data from source snowflake account to Azure blob storage (same for which you created staging in snowflake) in Parquet format.
  • After that based on that newly created file in Azure blob storage you can create a table and copy data into it using below query
create or replace table tablename  
using template (  
select array_agg(object_construct(*))  
from table(  
infer_schema(  
location=>’@stagedcopy/address’,  
file_format=>’my_parquet_format’  
)  
));

You can refer this document for more information.

Upvotes: 1

Related Questions