If your tables currently don't fit the criteria for using the faster, more efficient Incremental Replication Methods, don't despair! You may be able to make some little changes to allow the use of incremental replication.
Modifications for Modified At
The Modified At method - which is the most ideal replication method - uses a datetime column to detect new and/or updated data. As a refresher, the datetime column in tables using this method must be indexed and can't contain null values at any time.
If your table doesn't quite fit the bill, you can add an index modified_at column. Remember that null values are a no-go - check that the column is populated for every row.
If you currently delete rows from the table, the solution isn't to actually delete the row - mark it as invalid instead. How, exactly?
You could add a deleted column to the table. This column will return a 1 if the row is invalid and 0 otherwise. You can then use this column to filter out invalid rows when you're building metrics and reports. Voila!
Modifications for Single Auto Incrementing Primary Key
If the Modified At method cannot be enabled, then Single Auto Incrementing Primary Key is the next best option. New data is discovered in tables using this method by searching for primary key values that are higher than the current highest value in the Data Warehouse.
As a refresher, tables using this method are single column with integer auto incrementing primary keys. To use this method in your database, try making the following modifications:
- if the primary key is either a composite key or a non-integer, change the primary key to an auto incrementing integer
- if the primary key is a single integer column but keys can be assigned non-sequentially, change the primary key to auto increment
While we hope you can put these changes into place and take advantage of the faster, more efficient Incremental Replication Methods, we know it may not always be possible. You can still take other steps to reduce your update time and optimize your database.