Quick start: Use DataStage to load enterprise data into Snowflake

With existing IBMid

If you have an existing IBMid, watch this short video.

With new IBMid

If you don't have an existing IBMid, watch this short video.

Add the Source connector node

1. In the node palette, expand the Connectors section.

2. Drag the Asset browser connector and drop it anywhere on the empty canvas.

   

3. When you drop the Asset Browser connector on the canvas, you are prompted to select the asset.

   1. To locate the asset, select Connection > Data Fabric Trial - Db2 Warehouse > BANKING > MORTGAGE_APPLICATION.

      Tip: To expand the connection and schema, click the connection or schema name instead of the checkbox.

   2. Click Add to drop the Db2 Warehouse data source onto the DataStage canvas.

Add the Target connector node

1. In the Node palette, expand the Connectors section.

2. Drag the Asset browser connector and drop it onto the canvas so it is positioned as the second node.

   

   1. To locate the asset, select Connection > Snowflake > MORTGAGE.

      Tip: Click the checkbox to select the MORTGAGE schema name.

   2. Click Add to drop the Snowflake connection onto the DataStage canvas.

3. To link the nodes together, hover over the Mortgage_Application_1 node until you see an arrow. Drag the arrow to the Snowflake connection to connect the two nodes.

   

Configure the source and target nodes

1. Double-click the MORTGAGE_APPLICATION node to see its settings.

   1. Click the Output tab.

   2. Check the Runtime column propagation option. DataStage is flexible about metadata. It can handle situations where the metadata is not fully defined. In this case, you select Runtime column propagation to ensure that if the DataStage job encounters extra columns that are not defined in the metadata when the job actually runs, it adopts these extra columns and propagates them through the rest of the job. This feature allows your flow design to be flexible for schema drift.

   3. Click Save.

      Because you are reading data from Db2 Warehouse into Snowflake, the Db2 Warehouse connector is positioned first in the flow. Your goal is to load the Db2 Warehouse data into Snowflake. Next, you add a Snowflake connector that reads the data from the Db2 Warehouse connector. Thus, the Snowflake connector is positioned second in the flow.

2. Double-click the MORTGAGE_DATA connector to see its settings.

   1. Change the node name to Snowflake_mortgage_data

   2. In the settings side panel, click the Input tab.

   3. Expand the Usage section.

   4. For Write mode, select Insert.

   5. For the Table name, add APPLICATION after the schema name, so the full table name reads MORTGAGE.APPLICATION.

   6. For the Table action, select Create. This setting creates the table in the specified database and schema in Snowflake, and then loads the enterprise data into that table.

   7. Accept the default values for all other fields in the Actions section.

   8. Click Save to update the changes, and return to the DataStage flow.

Add another Asset connector node

1. In the node palette, expand the Connectors section.

2. Drag the Asset browser connector on to the canvas close to the MORTGAGE_APPLICATION node.

3. When you drop the Asset Browser connector on the canvas, you are prompted to select the asset.

   1. To locate the asset, select Connection > Data Fabric Trial - Db2 Warehouse > BANKING > MORTGAGE_APPLICANT.

      Tip: To expand the connection and schema, click the connection or schema name instead of the checkbox.

   2. Click Add to drop the Db2 Warehouse data source onto the DataStage canvas.

Add the Join stage node

1. In the Node palette, expand the Stages section.

2. In the Node palette, drag the Join stage on to the canvas, and drop the node on the link line between the MORTGAGE_APPLICATION and Snowflake_mortgage_data nodes. This action maintains links from the MORTGAGE_APPLICATION node to the JOIN node to the Snowflake_mortgage_data node.

3. Hover over the MORTGAGE_APPLICANT connector to see the arrow. Connect the arrow to the Join stage.

4. Double-click the MORTGAGE_APPLICANT node to see its settings.

   1. Click the Output tab.

   2. Check the Runtime column propagation option. As mentioned previously, this option accommodates schema drift.

   3. Click Save.

5. Double-click the Join_1 node to edit the settings.

   1. Expand the Properties section.

   2. Click Add key.

      1. Click Add key again.

      2. Select ID from the list of possible keys.

      3. Click Apply.

      4. Click Apply and return to return to the Join_1 node settings.

   3. Change the Join_1 node name to Join_on_ID.

   4. Click the Output tab.

   5. Check the Runtime column propagation option to accommodate schema drift.

   6. Click Save to save the Join_on_ID node settings.

Add the Filter stage node

1. In the Node palette, in the Stages section, drag the Filter node to the canvas, and drop the node on the link line between the Join_on_ID and Snowflake_mortgage_data nodes.

2. Double-click the Filter_1 node to edit the settings.

   1. Expand the Properties section.

   2. Under Predicates, click Edit.

      1. Click the Edit icon in the Where clause column, and type STATE_CODE='CA'. This clause filters mortgage applications to only California applicants.

      2. Click Apply and return.

   3. Click the Output tab.

      1. Check the Runtime column propagation option to accommodate schema drift.

   4. Click Save to save the Filter node settings.