You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours. Most…

Monthly partitioning is too coarse for day-level queries because partition elimination cannot narrow a scan to a single day without reading the entire month's partition.

Weekly partitioning does not align with either the day-level or month-level query patterns described, so partition elimination is inefficient for both common access types.

Yearly partitioning is far too coarse and provides almost no partition elimination benefit for day-level or month-level queries, forcing near-full partition scans for every report.

Partitioning by day directly matches the stated query pattern where data is most frequently accessed at the day level, allowing SQL Server's partition elimination to skip irrelevant partitions during report and cube queries. Applying compression to each partition reduces storage I/O and improves scan speed when processing the tens of millions of weekly records. This combination provides the best query performance and storage efficiency for the described workload.