Symptoms
Records with ID = -1 are not read into Business Unit table while using the following incremental load selection rule: ID > (Last max. value) - 1000000 and where the ID field is also the table’s Primary Key
Cause
The incremental selection rule in the data source filters out records with ID = -1, preventing them from being read into the Business Unit table.
Resolution
To include records with ID = -1 in the Business Unit table while using incremental load, follow these steps:
-
Create a Query table:
- Right-click on the data source and click Data Source Settings
- Select the tab Query tables
- Write a query to select records with ID = -1, e.g.,
SELECT * FROM schema.table WHERE ID = -1
.
-
Merge the Query Results:
- Use the query table to pull the records with ID = -1.
- Merge these records into the same table in the prepare (DWH) instance.
- This will ensure that the table contains the missing rows with ID = -1.
-
Handle Incremental Changes:
- If the records with ID = -1 eventually change to a valid number, the incremental load will add them as new rows.
-
Use Brackets for Table Names:
- If you encounter errors with table names containing special characters (e.g.,
-
), enclose the table name in brackets, e.g.,[schema].[table-name]
.
Reference Ticket
32229