How to migrate on premises multiple SQL database tables to snowflake by creating pipeline.

I’m working on designing a Batch Macro in Alteryx to automate the migration of multiple tables from an on-premises SQL database to Snowflake. My goal is to store all table names in a single Text Input, dynamically execute a query for each table, and load the results into corresponding tables in Snowflake. Despite several attempts, I haven’t been able to get the macro to function as intended. Could someone guide me through the correct design or share best practices for implementing this workflow? and i m not able to share the sample workbook as company restrictions. Workflow Snapshot.png

Datasets

Developer

Topic of Interest

Output

Macros

Best Practices

Batch Macro

Tableau

Dynamic Processing

In Database

Interface Tools

Text Mining

Data Investigation

Database Connection

Alteryx Editions

Alteryx Copilot

Iterative Macro

In-Database

Accepted answers

Puneet13

Its done, i have achieved it using batch macro with directory and formula tool

All comments

caltang

Your concept is right, but you are building a batch macro incorrectly. You are executing an entire table's worth of paths into one dynamic input which takes in a dummy or first path, and then writes to Snowflake for that single table's worth of values.

To fix this, you need to implement the batch macro AND a different workflow to use that batch macro to then get you your data into the format you need BEFORE you load it into Snowflake.

This guide illustrates: https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309

Now, once you solve that batching process, you need to think about your Snowflake output. You can write the output and create the table / upsert etc AFTER the output of the batch macro in your other workflow as a one shot thing. So you don't really need to have the control parameter or action the final output tool per se.

Puneet13

HI Just wanted to know how this run with multiple SQL table put in Text input and call it using dynamic input and them output to the snowflake, if you can help share with me some sample.

caltang

I just told you what you were doing incorrectly and how you can do it, plus a concept guide was linked to you @Puneet13

apathetichell

Follow this thread --> https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Batch-Processing/m-p/1398528#M346810

Puneet13

can somebody share the sample workflow where multiple sql table from database put in snowflake.

Puneet13

can you please help and share the sample workflow it would be a great help

apathetichell

Hey @Puneet13 --- sorry -- no license - but try it yourself -- and let us know where you are hitting an issue. Also--- reminder - mark the solution provided by the other poster as the solution - not your own. I noticed that both times you've accepted solutions - you've accepted your own solution not the solution provided by the other poster. Hopefully unintentional because that's a jerk move.

Puneet13

Its done, i have achieved it using batch macro with directory and formula tool

apathetichell

Updating prior comment:

I've noticed that everytime you've accepted a solution - you've accepted your own solution not the solution provided by another poster. That's a jerk move.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703