Complete Guide to Lookup Table Fields and Settings

In this article, we'll show complete guide to lookup table fields and settings

Let’s understand what it contains and how each column is used.

Look Up Table Columns:

1. BI_CONNECTOR_ID
2. TABLE_SRC
3. SRC_TABLE_QUERY
4. TABLE_DEST
5. SCHEMA_DEST
6. PRIMARY_KEY
7. WATERMARK_COLUMN
8. WATERMARK_COLUMN_FORMAT
9. HIGH_WATERMARK
10. FREQUENCY_IN_HOURS
11. REFRESH_TYPE
12. ENABLE_FLAG
13. STARTED_ON
14. ENDED_ON
15. STATUS
16. ERROR_DESC
17. IS_BIC_TABLE
    
    

Understanding the columns in Lookup Table

• Source and Destination Information

1. BI_CONNECTOR_ID

           Auto-generated unique identifier for each row in the lookup table. Used to uniquely identify each lookup table entry.

2. TABLE_SRC
Represents the source table name configured for the pipeline. It can be either a Base Table or a BIC Table and is primarily used as a reference to identify the source table in the lookup configuration.

3. SRC_TABLE_QUERY
Stores the source query that will be executed to extract data from Fusion.

• For Base Tables:
  BI Connector automatically generates a complete SELECT query including all column names during lookup table creation.
• For BIC Tables (Custom SQL Queries):
  BI Connector automatically generates the query during lookup table creation in this format:
  SELECT * FROM <BIC_TABLE_NAME>
  This executes the custom SQL query that was defined in BI Connector for that BIC table.
  
  

4. TABLE_DEST
The name of the destination table in the target database where the data will be loaded.

5. SCHEMA_DEST
The schema in the destination database where the TABLE_DEST exists.

• Primary Key and Uniqueness

6. PRIMARY_KEY
The primary key column(s) of the source data.

• Used to uniquely identify rows and track changes.
• Can be a single column. Example: ERROR_ID
• Or multiple columns separated by commas. Example: PERSON_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE
  
  

• Watermarking (Incremental Load Logic)

7. WATERMARK_COLUMN
The column(s) used to detect new or updated records since the last run. Typically a timestamp or numeric column.

• Simply provide the name of the column used to track new or updated records
• Example: LAST_UPDATE_DATE
  
  

8. WATERMARK_COLUMN_FORMAT
Specifies the format of the datetime column of the WATERMARK_COLUMN field. It ensures that pipeLine can correctly interpret timestamp values for incremental data filtering.

• Required only if the watermark column is date/time column.
• Not required (can be left empty) if the column is not date/time column.
• Example: YYYY-MM-DD HH24:MI:SS

               Watermark Column Configuration Scenarios using example columns

          9.  HIGH_WATERMARK

• Represents the maximum watermark value processed in the previous successful run and is used to control incremental loading.
• Based on this value, the pipeline fetches new or updated records
  
  

• Execution Control
  
  10. FREQUENCY_IN_HOURS
  Indicates how often this table should run in pipeline (in hours).
  Examples:
  • 2 → Runs every 2 hours
  • 24 → Runs once daily
    
    

         11. REFRESH_TYPE
               Defines the type of data load:

• FULL_LOAD : 

1. Set the REFRESH_TYPE to FULL_LOAD in the lookup table for the tables you want to do full load.
2. To set FULL_LOAD for all rows in the lookup table, you can run the following SQL command:

   UPDATE <your_lookup_table_name>
   SET 
     REFRESH_TYPE = 'FULL_LOAD'

3. To set FULL_LOAD for specific rows in the lookup table, you can run the following SQL command:
   

   UPDATE <your_lookup_table_name>
   SET REFRESH_TYPE = 'FULL_LOAD'
   WHERE BI_CONNECTOR_ID IN (1, 2, 3)

4. This query will update rows for the tables listed in the IN clause.

• IF NOT FULL_LOAD:  

1. First , it will check for the high_watermark value.
2. If the high_watermark value is empty, then it will do full load .
3. Else, It will do an incremental refresh. It does this by comparing each source row's watermark value (like LAST_UPDATED_DATE) against the maximum value already present in the destination. Only the records with newer values — meaning data that was added or updated since the last load — are fetched and loaded.

         12. ENABLE_FLAG

• Indicates whether the table is active for execution.
• Values:

• 'Y' → Enabled

• 'N' → Disabled

• If set to N, the pipeline will skip that table.
  
  

• Execution Tracking and Monitoring 
  
  

         13. STARTED_ON
           Timestamp when pipeline execution started for that table.

          14. ENDED_ON
           Timestamp when pipeline execution successfully ended for that table.

          15. STATUS

• Stores execution status. 
• Values:

• 'Succeeded'

• 'Failed'
  
  

          16. ERROR_DESC

• Stores detailed error message if execution fails for that table.
• Used for troubleshooting and monitoring.
• Values:

• If Succeeded → Empty or NULL

• If Failed     → Error message

• BI Connector Table Indicator 
  
  

           17. IS_BIC_TABLE

• Indicates whether table is BI Connector table or not.
• Values:

• 'Y' → Yes

• 'N' → No