Data warehouse staging architecture

Question

This is a question about data warehouse design. We are setting up a healthcare datawarehouse and starting with 2 major source systems that combine for about 20,000 tables and 2 TB of data. 1) It is highly dimensional data 2) We don't wan't to heavily effect OLTP systems

We have chosen an incremental Kimball design. My question is, should all of the data be staged, then sorted into inserts/updates and put into the data warehouse. Then the staging data would be cleared for the next incremental load.

This leaves you with 1 copy of the data.

The other method would be to incrementally load it into staging, sort it into inserts/updates and store it in the same format as the source systems. Then we would combine data from the source systems into the datawarehouse from the full copy.

This would essentially leave you 2 copies of the data, one in the form of the source systems and 1 loaded into the actual datawarehouse.

What is the best practice for this? I originally thought it would be best to only store the copy in the data warehouse and clear the source tables each load.

However, in that case if you ever have to go back to an existing dimension and add a column you would have to re-load all the dependent source tables. Plus you would lose history?

It just seems really inefficient to store it twice though....just wanted some thoughts on the design, your experiences and best practice.

Answer 1

Personally i have staging tables for extract, transform and persistent data storage.

Whether you do full exports or incremental loads will depend on what tools you have, your strategy and whether your app schema and data support it. Sometimes you cant avoid full exports.

Adding a column to a dimension isnt a big deal, but backfilling historic data could be very difficult or may not be possible at all. Trying to reconstruct how an app looked at a point in time retrospectively would be a major undertaking. You would need a very good case to justify that.

All of the things you mention are possible, but only you can decide if the cost/benefit is worth it.

Answer 2

We generally find that an ODS (Operational Data Store, a copy of the source system) is first very useful for layering your ETL process (lending itself to maintenance and troubleshooting), but then eventually becomes very useful for operational reporting.

You also have the luxury of being able to add indexes, and write crazy queries. against it.

You can also use it to troubleshoot (as you have a copy of the data that you loaded from rather than a moving target in the real source). Then if you can get your replication tools to trickle feed into the ODS every five minutes, you have a very useful architecture.

Forget about 'inefficiency' here. You'll encounter real inefficiency when you can't troubleshoot your ETL process because it's squashed up into one layer, and you don't have an ODS layer to troubleshoot from.

Answer 3

It depends heavily on your specifications. Storage space is not very expensive for a project like this (20K table will require probably a much much bigger development budget).

Keep in mind that the DWH should usually maintain more history that the Source system, so, if you think you will want to look back and add a new dimension column or a new fact, a good proposition is to build a Data Vault between your Source System and your Kimball Data Marts.

You gain a documented and detailed history and more flexibility on the Data Mart layer, which needs to be near to the user and therefore needs so much flexibility as viable.