Recommended Architecture

The recommended architecture for data analytics is to use a reporting database otherwise known as a Data Lake. The only time a source database should be used for reporting is when live data is required.

In the instances when live data for reporting is required, the associated queries that run on the source database should be highly optimised and specific, taking less than 10 seconds to run.

When updating the reporting database from the source database, try to schedule the data transfer jobs during off-peak times when the source system is the least likely to be used.