Solved: Migrate SQL tables to Snowflake

[Puneet13]

Hello Alteryx Community,

I’d like to share a solution I’ve developed for migrating SQL tables to Snowflake using a Batch Macro in Alteryx. I’ve attached a snapshot of the workflow and outlined the steps below for your reference:

Steps to Migrate SQL Tables to Snowflake Using Batch Macro

1. Directory Tool

   • Set the shared drive path.
   • In the File Specification, use *.SQL to target all SQL files.

2. Dynamic Input Tool

   • Add your SQL table in CSV format.
   • Under Read a List of Data Sources, select Full Path.
   • In the Action setting, choose Change Entire File Path.

3. Summarize Tool

   • Group by FileName.
   • Concatenate Field_1.

4. Formula Tool

   • Select FileName from the dropdown.
   • Create a formula to define the SQL ODBC connection path, e.g.:
     'odbc:DSN=Connection|||' + [FileName]

5. Create a Batch Macro

6. Inside the Batch Macro

   • Add an Input Tool and connect it to your SQL Server.
   • Use the SQL Editor to select the desired table.
   • From the Interface menu, add a Control Parameter and connect it to the Input Tool via an Action Tool.
   • In the Action Tool, choose Replace a Specific String and insert a query similar to the one used in the Input Tool.

7. Formula Tool in Macro

   • Use the outputfilename field to define the output path, e.g.:
     'odbc:DSN=Connection|||Test'
   • Add another Control Parameter and connect it to the Formula Tool via an Action Tool.
   • Again, use Replace a Specific String to insert the appropriate query.

8. Output Tool

   • Connect to your Snowflake database.
   • In the configuration window, enable Take File/Table Name from Field and select Change Entire File Path.
   • Choose the outputfilename field from the Formula Tool.