Это классическая задача под секционирование.
У меня, например, аналогичная задача. Сводная БД, куда (пере)загружаются данные разных филиалов помесячно.
Таблицы поделены на, примерно, 6000 секций, с ключом по id филиала + месяц.
Заливка происходит в динамически создаваемые создаваемые таблицы в отдельной схеме, а потом секция подменяется через partition switch.
С учетом того, что данные льются не в основную структуру, чтение данных из основной структуры - практически не блокируется. Практически, потому что partition switch приводит к аварийному завершению запроса, если он работает на момент переключения секций. Но, к счастью, в 2019 появилось ленивое переключение. Оно сначала ждет задаваемый в команде таймаут, а потом - переключает.
Да!
Дозагрузка реализована несколько уродски. :-)
В автономные таблицы заливаются новые данные, а потом туда же - ранее загруженные данные из продуктовой таблицы.
Потом в этой таблице строятся индексы, а потом - секция переключается.

Могу сказать, что не смотря на постоянную заливку данных 24Х7 в 8 потоков, по продуктовым таблицам вполне можно строить оперативные отчеты. Сложные агрегаты, конечно, считаются в кубе, который строится на базе, восстановленной из бэкапа раз в сутки.

Объем всего этого дела не очень большой, чуть менее 10 Тб, но, думаю, потенциал у нее где то до 20-30 Тб дорасти, судя по нагрузке, откликам и т.д.