Comparison of Deisgn and ETL patterns for Fact Table, depending on its usage, and performance requirements:
|Criteria||Full Re-flush||Snapshot with Insert then Archive||Differential Facts||Periodic Snapshots|
|ETL Structure||Simplest and may be required when using any other pattern, once an error happens.||2 Snapshots: one new and one old snapshot data.||Requires differential calculations on the the source DB, as well as Full Re-flush if error happens.||Requires Log Table and updates to it.|
|ETL DML – For Locking and Page Splits||Using synonyms and indirections to avoid DML on table while querying.
Quick truncate then Quick Select Into with minimum logging.
|Logged Inserts then Logged Deletes||Logged Inserts||Logged Inserts|
|Query Requirements||Simple query on Counts table by calling a synonym||A view selecting the latest snapshot data.
Archiving Historics in separate (Archive) table.
|Full Aggregate required.
Indexing or partitioning required after time, on the fact table keys, other than the date key.
|Tiny ETL Log table: Logging ETL ID, ETL Date, ETL Status (Success/Not).
A view selecting the latest snapshot data.
|Transactional Query Performance||Quick. No historics in main table. Only latest snapshot.||Quick. No historics in main table. Only latest snapshot.||Full Aggregate required||View querying the fact table joining the Log table with latest ETL date or ETL period ID.|
|Reporting Query Performance||Backing and querying up Historics in separate (Archive) table||Querying Historics in separate (Archive) table||Full Aggregate required across ETL timestamps, up to N-day times at least (if ETL is only once a day), for each combination of fact table keys other than date key.||Depending on query.|
|Instant/Hourly Model Updates||Separate table or Incremental.||Separate table for adding differentials, or Incremental.||Incremental. These increments are to deleted once next differential is loaded from source OLTP Server.||Incremental- These increments are to deleted once next snapshot is loaded.|
|Convert to Memory-Optimized and Column Store table||Easy: Small table||Easy: Small table||Harder: Large table||Harder: Large table|