4.

One approach when building a new data repository is to extract data from your source systems, transform it into the format of the target system and then load it to the new system (ETL). This is exceptionally risky when building a data warehouse for financial use cases.

Successful data repositories are those that can tolerate multiple different views of the same underlying source data. This is because each department has its own specific definitions of the same core concepts such as market value, customer, security, product and price. Without the correct definitions, your stakeholders will be unable to perform their roles. And if they cannot perform their roles using your data repository, they will simply ignore it and continue to use their original systems and processes.

Adopting an ETL approach means applying a single data definition to your raw data, and then storing the results of that calculation on the new data repository. However, as each department uses different definitions, this approach will lead to irreconcilable discrepancies when attempting to roll up data from across all of your departments. As a result, your data warehouse will be unable to deliver a single source of truth for operational and regulatory reporting—one of the key project objectives.

As a result, your data warehouse will be unable to deliver a single source of truth for operational and regulatory reporting—one of the key project objectives.

To solve the challenge, it is essential to extract and load the raw data from your legacy systems, and then transform the data dynamically inside the new data warehouse—an approach called ELT or schema-on-read. By preserving the raw data, this method will enable you to design business logic that applies different data definitions for each department. This gives every stakeholder the correct view of the data to meet their operational reporting needs, while preserving a single source of truth for accurate cross-departmental reporting.