Snowflake Connector Guide

Snowflake Connector Guide

Snowflake Mapping Example

Snowflake Mapping Example

An enterprise application uses the Oracle database to store the product transaction details. You use Snowflake data warehouse to analyze the completed transactions, pending transactions and availability of stocks. You read the product transaction details from an Oracle source and apply lookup condition on the PRODUCTDET table in Snowflake which stores details of product and its availability. Based on availability and requirement, you write the transactions to the PENDINGTRANSACTION and COMPLETEDTRANSACTION tables in Snowflake and update the INSTOCK field in PRODUCTDET table based on the completed transactions. You use the following objects in the Snowflake mapping:
Source Object
The source object for the mapping task is OracleSrc table in Oracle. Use an Oracle connection to connect to Oracle and read data from the OracleSrc object.
The following image shows the transaction details stored in the OracleSrc table:
Lookup Object
The lookup object for the mapping task is PRODUCTDET table in Snowflake, which has details of product and its availability.
The following image shows the data stored in the PRODUCTDET table:
Target Object
The mapping task has the following target objects:
COMPLETEDTRANSACTION
The COMPLETEDTRANSACTION table includes the TRANSACTIONID, PRODUCTID, QUANTITY, ORDERPLACEDON, and ORDERCOMPLETEDON fields.
The following image shows the data stored in the COMPLETEDTRANSACTION table:
PENDINGTRANSACTION
The PENDINGTRANSACTION table includes the PRODUCTID, TRANSACTIONID, REQUIREDQUANTITY, and ORDERPLACEDON fields.
The following image shows the data stored in the PENDINGTRANSACTION table:
PRODUCTDET
The PRODUCTDET table includes the PRODUCTID, INSTOCK, PRODUCTDET, and PRICE fields. Based on the completed transactions, the INSTOCK field is updated.
The following image shows the data stored in the PRODUCTDET table:
Mapping
The following image shows the Snowflake mapping:
When you run the mapping, the agent reads the transaction details from source, fetches fields from the lookup, and based on the conditions applied write the available quantity and transaction details to the target tables.

Updated March 07, 2019


Explore Informatica Network