Datawarehouse bad practice?

In our Datawarehouse context, we have to update some record in fact tables inside our ETL.

One thing we did is to create nonclustered indexes just before the heaviest queries, and drop them afterwards. This lead us to much less time spent scanning tables in queries, and the time spent in building indexes has very low impact (1-2 minutes).

Is this a bad practice?

Note: We cannot partition tables right now.

Building temporary indexes for ETL jobs is not necessarily bad practice, as the index builds are fairly quick. Where it might not be so efficient is if you have relatively small incremental updates on very large tables, but it sounds like this is not the case here.

The only caveat is if you expect the tables to grow substantially with time. If they are just work tables for the ETL then it may well be oK. If the tables are fact tables that will accumulate large data volumes over the next 5 years then the index rebuilds may get slower with time.

For staging data, dropping indexes then loading will make bulk loads to staging much quicker, and you may well need to add indexes to the staging tables in order to support queries supplying the ETL process.

StackExchange Database Administrators Q#89676, answer score: 7