While the architectural design puts the correct structure in place, the data warehouse is what gives the database life.
The first step in data orchestration is setting the frequency for the data extraction from each source. The frequency is set depending on the nature of the data. Where production data may come in hourly or daily, accounting data may come in as a part of the monthly book close process. Data with multiple sources are merged, correctly compensating for any differences in the number or names of fields.
Once the data has been successfully staged, quality checks are applied to each data feed to ensure there were no issues with the extraction from the source or any new exceptions within specific fields. After the data has been checked for accuracy, all business rules are applied, and the data is transformed to mirror the format of the production table. The last step of the process is to prepare the production table to receive the new records, then promote the transformed data into the production table.
If the database is the foundation of the application, logging and error handling are the mortar of the bricks. Each step of the process is logged, and when errors do occur, the development team is notified of the exact cause, which leads to the fastest possible resolution. Only data that passes through all the gates is promoted to production. It is better to favor having out of date information in the database versus inaccurate information.
