Most companies using Oracle Fusion Cloud have a pressing need to push the Fusion data to their Snowflake to maintain a single source of truth. These companies typically come across any of the following combinations of Data Migration/ETL needs for each table they want to pull from Oracle Fusion:

1. Type of data pull from source table - Full data pull or Incremental data pull
2. Data insertion in target table - Insert new rows or update existing rows or both
3. Frequency of automated refresh - daily/weekly/bi-weekly or once in x days

In some cases, they might have a composite primary key (a set of concatenated values from different columns of the table to uniquely identify each row). Some tables may not have time-based columns to facilitate incremental loads, for which the primary key is used as a reference to perform the incremental load.

This article will help you to handle all of the above scenarios with a single Azure Data Factory (ADF) pipeline (that refers to a Lookup table). 

Need a visual guide? Watch the full step-by-step setup video here: Click to watch 

The lookup table acts as a configuration controller for ADF pipelines. Each row in the table represents a data load job, including source/destination table details, load type, execution frequency, and watermark column information for incremental loads.

Prerequisites:
1. Required Setup:

1. Windows Server with Microsoft Integration Runtime and BI Connector
   
   1. Install and configure Microsoft Self-hosted Integration Runtime (SHIR) from Microsoft
      Note: Its possible to use an existing SHIR as well
   2. Install BI Connector Server edition and activate it using a license key.
2. Admin Access to Gateway Server
   
   1. Admin access is required to set up System DSNs in ODBC.
      

2. Recommended Configurations:

1. Windows Server 2016 or above (can also be set up on a VM on the cloud)
2. .NET Framework 4.7.2 or above installed
3. Minimum of 8 CPU cores, 8 GB RAM, and 80 GB hard disk space
4. Strongly recommended to disable hibernate/sleep on this server.
   Note: The SHIR wouldn't work when the server enters hibernate/sleep mode

Step 1: Install and Configure Snowflake ODBC Driver: 

1a. Installing Snowflake ODBC Driver:

• Download Snowflake ODBC Driver [link to download](choose 64-bit or 32-bit version according to your system architecture)
• Run the installer as Administrator
• Follow the installation wizard to complete the setup

1b. Create Snowflake ODBC DSN :

• Open ODBC Data Source Administrator (64-bit) from Windows Start Menu 
• Go to System DSN tab  and Click "Add" 

• Select "SnowflakeDSIIDriver" from the list and Click "Finish

1c. Configure Snowflake DSN :

• Data Source Name: Enter descriptive name (e.g. Snowflake_Prod) 
• Server: Your Snowflake account URL (e.g. abc123.us-east-1.snowflakecomputing.com) 
• Database: Your database name 
• Warehouse: Your warehouse name
• Schema: Your default schema 
• Click "Test" to verify connection 
• Click "OK" to save DSN

Note: Remember the DSN name as you'll need it for creating the Snowflake ODBC connection in ADF.

Step 2: Importing the Pipeline Files

Please Contact us or email support@biconnector.com to get our ready-to-deploy ADF pipeline template.

Prerequisites

• Ensure the BI Connector DSN is configured and the required BIC tables (source tables created in BI Connector) are already available. These are essential for the BI Connector ODBC connection. 
• For further assistance on creating the DSN and BIC tables, refer this article.  

⚠️ CRITICAL: Timestamp Column Formatting 

When creating BIC tables, you MUST use proper timestamp formatting like: TO_CHAR(CREATION_DATE,'YYYY-MM-DD HH24:MI:SS') AS CREATION_DATE. This prevents timestamp conflicts. 

SELECT
    PERSON_ID,
    PERSON_NUMBER,
    DISPLAY_NAME,
    TO_CHAR(CREATION_DATE,'YYYY-MM-DD HH24:MI:SS') AS CREATION_DATE,
    TO_CHAR(LAST_UPDATE_DATE,'YYYY-MM-DD HH24:MI:SS') AS LAST_UPDATE_DATE,
    CREATED_BY
FROM
    per_persons_f
ORDER BY LAST_UPDATE_DATE ASC;

2a. Import the Pipeline Template

• Click the + (plus) icon under Factory Resources.
• Select Pipeline, then choose Import from pipeline template from the options shown.
• Add the ZIP folder containing your pipeline template 

2b. Map the services 

After importing the pipeline template, you need to map the linked services.

• Snowflake Linked Service
  Either:
  • Select an existing Snowflake Linked Service configured to point to your destination Snowflake database.
    OR
  • Click + New to create a new one:
    
    • Give Name to Snowflake Linked Service
    • Under Account name: Enter the account name (for e.g. yours.snowflakecomputing.com) 
    • Under Database : Enter the database name
    • Under Warehouse: Enter the warehouse name
    • Under Authentication type: Choose the appropriate method ( Basic ).
    • Enter database username and password that has write access to destination schema.
    • Under Host: Enter the host name (which would be similar to the account name)
    • Click Test Connection to ensure successful connectivity.
    • Click Create.

• Snowflake ODBC Linked Service
  Either:
  • Select an existing ODBC Linked Service that is already configured with the Snowflake ODBC DSN.
    OR
  • Click + New to create a new one:
    • In the New Linked Service window:
      • Give Name to the ODBC Linked service.
      • Select Self Hosted Integration Runtime under Connect via Integration Runtime.
    • Under Connection String:
      • Add the DSN name that was created in step 1 for Snowflake in ODBC, in the format: 
        dsn=<<your_dsn_name>>
    • Under Authentication type: Choose the appropriate method (Basic)
    • Provide the username and password of your Oracle Fusion Account.
    • Click Test Connection to verify.
    • Click Create.
• BI Connector ODBC Linked Service
  Either:
  • Select an existing ODBC Linked Service that is already configured with the BI Connector DSN.
    OR
  • Click + New to create a new one:
    • In the New Linked Service window:
      • Give Name to the ODBC Linked service.
      • Select Self Hosted Integration Runtime under Connect via Integration Runtime.
    • Under Connection String:
      • Add the DSN name that was created using BI Connector, in the format: 
        dsn=<<your_dsn_name>>
    • Under Authentication type: Choose the appropriate method (Basic)
    • Provide the username and password of your Oracle Fusion Account.
    • Click Test Connection to verify.
    • Click Create.
• 
  

Step 3: Create the Lookup Table in the Snowflake Database

3a. Run the Table Creation Script:

CREATE OR REPLACE TABLE <your_schema_name>.<your_lookup_table_name>
(   TABLE_SRC               VARCHAR(255),  -- Source table name from which data is extracted   
TABLE_DEST              VARCHAR(255),  -- Destination table name where data is loaded   
PRIMARY_KEY             VARCHAR(255),  -- Primary key column(s) used to uniquely identify records   
SCHEMA_DEST             VARCHAR(255),  -- Target schema for the destination table   
DATABASE_DEST           VARCHAR(255),  -- Target database for the destination table   
WATERMARK_COLUMN        VARCHAR(255),  -- Column used for incremental loading   
WATERMARK_COLUMN_FORMAT VARCHAR(255),  -- Format of the watermark column   
LAST_RUN_DATE           DATE,          -- Date when the data pipeline last ran successfully   
FREQUENCY_IN_DAYS       NUMBER(38,0),  -- Number of days between scheduled data refreshes   
REFRESH_TYPE            VARCHAR(50)    -- Type of data refresh: FULL_LOAD, INCR_LOAD
);

INSERT INTO <your_schema_name>.<your_lookup_table_name>
(    TABLE_SRC,    TABLE_DEST,    PRIMARY_KEY,    SCHEMA_DEST,    DATABASE_DEST,    WATERMARK_COLUMN,    WATERMARK_COLUMN_FORMAT,    LAST_RUN_DATE,    FREQUENCY_IN_DAYS,    REFRESH_TYPE
)
VALUES (    'BIC_TABLE_NAME',                    -- Source table name    'SNOWFLAKE_TABLE_NAME',              -- Destination table name    'ID_COLUMN',                         -- Primary key column    'TARGET_SCHEMA',                     -- Target schema    'TARGET_DATABASE',                   -- Target database    'LAST_UPDATE_DATE',                  -- Watermark column    'YYYY-MM-DD"T"HH24:MI:SS',            -- Watermark format    NULL,                                -- Last run date (NULL for first run)    1,                                   -- Frequency (1 = daily)    'INCR_LOAD'                          -- Refresh type
);

3c. (Optional) Updating and Deleting Rows in the Lookup Table 

This step is required only if you want to make any changes to the Lookup table. If not, you can move to the next step.

To Update a Row:

Add the Update statement in the sql editor and execute it .

UPDATE <your_schema_name>.<your_lookup_table_name>
SET FREQUENCY_IN_DAYS = 1
WHERE TABLE_SRC = 'BIC_TABLE_NAME';

• FREQUENCY_IN_DAYS = 1 → sets the pipeline to run every 1 day

To Delete a Row:
Use the DELETE statement with a proper condition:

DELETE FROM <your_schema_name>.<your_lookup_table_name>
WHERE TABLE_SRC = 'BIC_TABLE_NAME';

• This condition ensures that only the row(s) where the TABLE_SRC column has the value 'BIC_TABLE_Name' will be deleted.

3d. Set the Lookup Table Name in ADF Pipeline

• In the TO_ORACLE_USING_BIC pipeline (e.g., FUSION_TO_SNOWFLAKE_USING_BIC) in ADF, replace the existing default value of the table_name and schema parameter with the name of the lookup table and schema.  
• Once done, Click Publish All in the ADF to save and deploy your changes

To learn more about lookup table, please refer this article.

Step 4: Create Trigger in ADF Pipeline

4a. Add the Trigger

To add the trigger in ADF pipeline

• Click the Add Trigger
• Click New/Edit

4b. Create the Trigger

• Click Choose trigger
• Click + New 

4c. Configure the Trigger

• Under Name: Enter trigger name
• Under Type: Choose Schedule
• Under Start date: Choose appropriate date with time
• Under Time zone: Choose appropriate time zone
• Under Recurrence: Choose Every 1 day(s)
• Click OK

4d. Enter the table_name

• Under table_name: Enter the name of the lookup table you created 
• Click OK

4e. Publish the Pipeline

    After creating the trigger, click Publish All

To see more on frequently asked question, please refer this article.