Database Replication Methods

This article discusses three methods of database replication and highlights the benefits and drawbacks of each approach. The first method, daily snapshots, involves a total re-sync of data from source databases to a destination warehouse on a schedule. While this is easy to implement, it can lead to delays in updating large tables and loss of changes in data. The second method, incremental updates using 'last modified' columns, requires scheduling queries that collect records after a last-modified date cursor. This approach is faster than re-syncing the entire database but still requires scanning the entire table and can lead to silent errors if the user picks the wrong column. The third method, incremental updates using change logs, captures every version of every change, including deletes, and provides close to real-time updates. This approach is more challenging to set up but offers better data integrity and efficiency. Fivetran recommends replication based on change logs for building database connectors due to its positive impact on the rest of the data stack.