Alooma has 3 different options for how to replicate transactional databases: full dump and load, incremental dump and load, and change data capture. These replication methods result in a set of tables in your target data warehouse. Each one works best for different use cases, so choose the replication method that's best for your specific data.
A full dump of the database tables is performed and loaded periodically into the data warehouse, replacing the last dump and load.
Advantage: Setup is simple and works best for small tables.
Disadvantage: Tables only update every few hours (configurable). This process is resource heavy, which may slow down the database during dumps.
The database tables are queried periodically for updates since the last query. Updates are then loaded into the target database. Requires a monotonically increasing "update indicator" column such as an "updated_at" timestamp or increasing numerical column.
Advantage: Setup is simple, has low resource load, and is low latency.
Disadvantage: Only captures inserts and updates, not deletes, and requires an update indicator column.
After a dump of the initial state of the database, the log of each database operation is continuously streamed and loaded into the data warehouse, and consolidated on a schedule (configurable).
Advantage: Best approach when available – enables near real-time replication, has very low query impact, supports both deletions and table alterations and only requires a primary key.
Disadvantage: Setup requires creating a replica for the log.
The replication of transactional databases results in a set of tables in the target data warehouse which support replication.
For each table in your replicated database, a log table (suffix _log) is created as a temporary buffer that contains all the latest changes originating from that table (for full dump/load this is the entire contents of the table).
Alooma runs a consolidation process that updates the final replicated table (the consolidated table) with all the events in this log table every eight hours (configurable by Alooma support). Alooma then empties this log table. Therefore, this log table only contains the latest changes that have not yet been updated to the consolidated table.
The consolidated table is a result of running the consolidation queries on your log table, and it contains all the historical data loaded into the target database from the input table. Query this table when query speed is the most important. This table is updated with the latest changes from your input table as a batch process approximately every eight hours (by default). You may contact Alooma support if you want to change this frequency.
Near Realtime View (Incremental Dump/Load and CDC replication only)
Alooma generates a near realtime consolidated view (suffix _nrt) by querying the consolidated table and the log table. Query this table when data freshness is the most important. This view provides the latest snapshot of your database at your fingertips!
If you have any questions about the replication method that's best for your data, feel free to reach out at firstname.lastname@example.org.