Fact Table Design Options

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

Leave a Reply

Your email address will not be published. Required fields are marked *