Rob's high performance data warehousing rule #3
September 26, 2023

Rob's high performance data warehousing rule #3: Strong operational data store ensures high performance

There is no more powerful structure to ensure a high performance data warehouse, than a strong Operational Data Store.

I've yet to find any DW design that is more successful than one that depends on a highly structured ODS.

The ODS is how we evade expensive data quality processes. It's the formalization of all of the data contracts we have with operational systems. If it's well constrained and all inclusive, you can sleep at night. If some source system breaks the load into the ODS, we kill the ingestion for that subject matter and throw an alert.

The ODS consumes all of the incoming data from individual upstream systems. It's generally highly normalized, at least as much as the up stream systems. If the constraints defined in data contracts are enforced, it will defend the data warehouse itself.

The primary goal is to centralize the enterprise's data, nothing more. It's a "now" representation of all of the artifacts we need to support the data warehouse, but we ignore time variance, integration and subject orientation. Now, you may add more data than exists in your source systems, lookup tables for identity management so you can map Jeff from sales in the HR table to Jeff from sales in the CRM system, but we're not worried much about turning Jeff from both systems into a singular entity here, that happens in the warehouse itself.

More importantly, the ODS provides a starting schema for the warehouse itself. Once implemented and constrained, the schema and ELT for the warehouse can be generated through reflection from the ODS, reducing manpower spend.

Given modern platforms, we have options that we didn't before. Today's ODS schemas don't need to live on specialized or isolated hardware, they'll happily live in the same database as the data warehouse itself.

Build and trust your ODS.

Read all the posts

Intrigued? Want to read some more?

Curious to learn more?