I have a Data Warehouse that pulls data from two locations, the first being our modern system, the second being our legacy system. The legacy system consists of 200 databases with identical schemas that are periodically and individually being converted into our modern system. The process of converting the legacy system will take several months, and our customer needs reporting on all the data from both modern and legacy systems during this time.
My question is this: Once we remove one of the 200 legacy databases and convert into our modern system, how can its data be easily removed from the data warehouse? I know that removing large amounts of data from a data warehouse can be cumbersome and require rebuilding of indexes, etc.
Best Answer
I would suggest dropping the database once it is no longer needed.
The database space is freed for the use of other expanding databases and/or for creating further new databases.
If you are concerned about data loss you could archive the LegacyDatabases to some backup location, a file share, tape backups, whatever your choice may be.