Configure and run Data Warehouse Automation for Snowflake flows

Contents

• Understand the Data Warehouse Automation for Snowflake
• Install the Data Warehouse Automation for Snowflake
•  Configure and run Data Warehouse Automation for Snowflake flows
  • Configured source application export to Snowflake
  • Configured source application to Snowflake production table – Merge & Delete
    • Add a resource from your configured source application to Data Warehouse Automation for Snowflake
    • Add a configured source application to Data Warehouse Automation for Snowflake
    • Add an unconfigured source application to Data Warehouse Automation for Snowflake

Once you have completed the installation process, integrator.io creates two flows:

• Configured source application to Snowflake – exports selected resource data from your source application into a newly created temporary table in Snowflake.
• Configured source application to Snowflake production table – Merge & Delete – exports data from the temporary table in Snowflake to the production table in Snowflake (and deletes the temporary table).

Additional record mapping is required to integrate the template’s exports and imports with your Data Warehouse Automation for Snowflake apps.

Note that all of your flows are disabled when first installed. After configuring the flows, you may enable them in your Flows dashboard or in Flow Builder with the Off/On toggle button. then run or schedule each flow.

Configured source application export to Snowflake

For configured flows, an export is automatically created for each resource you selected when configuring your source application. For example purposes, we’ve configured a Shopify connection and only selected one Shopify resource (products). An additional export is automatically added for each resource you select for a configured flow.

1. Once you have completed the installation process, open and enable the Configured source application to Snowflake production table – Merge & Delete flow.
2. Open the Configured source application export to Snowflake flow and click the gear icon in the upper right corner of Flow Builder.

Name (required): displays the name of the flow.

Description (optional): displays an optional description of the flow.

Next integration flow (optional): If you have enabled the Configured source application to Snowflake production table – Merge & Delete flow, the name of that flow displays here. You can also use the drop-down list to select any enabled flow in the Data Warehouse Automation for Snowflake integration tile.

Configured registered connection (required, uneditable): displays the name of the registered connection used to connect to the source application.

Table name (required): enter the temporary table name that integrator.io will create in Snowflake when the flow runs. Use all caps when creating a table name, and make sure that the name of the temporary table does not already exist in your Snowflake environment. You will receive an error if the temporary table name matches the name of a table that already exists in your Snowflake environment.

Resource table (optional): You can use this list to select any additional resource you would like to export from your source application (assuming that you hadn’t selected all preferred export resources during installation).

The export settings are automatically configured to send all products retrieved from Shopify to Snowflake’s temporary table. Each configured export step includes a hook that refines the exported data for import into the Snowflake temporary table.

Flow details

• Export source: Shopify
  • Export: Get Products - Exports all products Shopify products modified since the last time the flow ran.
• Import destination: Snowflake
  • Import: Import data into Snowflake’s temporary table - Adds or updates all exported data to Snowflake’s temporary table.

Configured source application to Snowflake production table – Merge & Delete

This flow exports data from the temporary table (imported during the previous flow), imports that data into the Snowflake production table, and delete the records from the temporary table.

Open the Configured source application to Snowflake production table – Merge & Delete flow and click the gear icon in the upper right corner of Flow Builder.

Name (required): displays the name of the flow.

Description (optional): displays an optional description of the flow.

Next integration flow (optional): If you would like another flow in your integration to run immediately after this flow completes, select it from this list.

Temporary table (required): Enter the name of the temporary table you want to export data from. This name should use all caps and match the Table name you defined in the Configured source application export to Snowflake flow settings.

Production table (required): Enter the name of the production table that you want to import data into with this flow. Use all caps when defining the name of tables in Snowflake.

Flow details

• Export source: Snowflake
  • Export: Check for existing records - Exports all records currently stored in the Snowflake temporary table..
• Import destination: Snowflake
  • Import: Merge temporary data with production data - Adds or updates all exported data to Snowflake’s production table.
  • Import: Delete temporary table records - removes all imported records from the temporary table.

Add a resource from your configured source application to your Data Warehouse Automation for Snowflake

Use the following steps to add an additional resource to your Snowflake Data Warehouse.

1. Open the Configured source application export to Snowflake Data Warehouse flow.
2. Click the gear icon in the upper right corner to open the Settings panel.
3. Click the Resource table drop-down menu and check the box next to the resource you want to add.

In this example, we will add a list of products as an additional export resource.

4. Check the box next to any resource you want to export from your source application and click Done.
5. Click Save or Save & Close to add the new resource to the export list.

Add a configured source application to your Data Warehouse Automation for Snowflake

You can add a configured source application to your Data Warehouse Automation for Snowflake if you have registered the connection in your environment. Use the following steps to add an additional configured source application to your Data Warehouse Automation for Snowflake.

1. Open the Data Warehouse Automation for Snowflake integration tile and click the Settings tab.

2. In the Fully generated flows section, open the Registered connections list to select the connection you want to use for the configured source application export you want to use.
3. Select each resource in the Export resources list that you want to add to your Snowflake Data Warehouse; click Done.

4. Click Save, return to the Flows tab of the Data Warehouse Automation for the Snowflake integration to see the new configured source application export to Snowflake flow.

Add an unconfigured source application to your Data Warehouse Automation for Snowflake

You can add any unconfigured source application to your Data Warehouse Automation for Snowflake if you have registered the connection in your environment. Use the following steps to add an additional unconfigured source application to your Data Warehouse Automation for Snowflake.

1. Open the Data Warehouse Automation for Snowflake integration tile and click the Settings tab.
2. In the Unconfigured flows section, click the Registered connections drop-down menu to select the connection you want to use for the unconfigured source application export you want to use.

3. Click Save, return to the Flows tab of the Data Warehouse Automation for Snowflake integration and to see the new unconfigured source application export to Snowflake flow.
4. Open the flow and click Add source to set up the connection and manually configure your export settings.
5. Enter the required JSON in the Custom settings text field.
6. Click Launch Form builder.

7. Enter the following code snippet in the Form definition section:

{
    "fieldMap": {
        "resource": {
            "id": "resource",
            "name": "resource",
            "type": "text",
            "required": true,
            "helpText": "The name of the resource as it will be stored in the Snowflake column.",
            "label": "Resource table"
        },
        "primaryKey": {
            "id": "primaryKey",
            "name": "primaryKey",
            "type": "text",
            "required": true,
            "helpText": "The primary key that integrator.io will use to perform advanced operations in Snowflake.",
            "label": "Primary key"
        }
    },
    "layout": {
        "fields": [
            "resource",
            "primaryKey"
        ]
    }
}

8. Click Save.
9. The Custom settings section of your export panel displays the following fields:

Resource table (required): Enter the name of the resource to be exported from your source application.

Primary key (required): Enter the unique identifier for the record to be exported from your source application. 

10. Click Save.
11. Open the Source application export to Snowflake flow.
12. Click the gear icon in the upper right corner of Flow Builder and enter the temporary table name to be created in Snowflake upon import.
13. Click Save.
14. Open the Source application to Snowflake production table - Merge & Delete flow.
15. Click the gear icon in the upper right corner of Flow Builder and enter the temporary table name and production table name.
16. Enable both flows, and run the Source application export to Snowflake flow.