185 ) What if the fact table data needs to be ingested on a weekly, monthly, or yearly basis instead of daily? How do you handle that?
Answer:
We handle this by configuring the scheduling and partitioning strategy in our ETL pipeline and Data Warehouse:
- Daily vs. Periodic Loads: While transaction fact tables (like claims) usually load daily, summary or snapshot fact tables (like monthly account balances) can be scheduled to run on the 1st of every month.
- Partitioning: We partition our large fact tables by date instead of month . When a weekly or monthly batch job runs, it only targets that specific partition block rather than scanning the entire table, which keeps the data load fast and efficient.
