Structure within staging area of data warehouse

sql database-design data-warehouse
NeedHack picture NeedHack · May 14, 2009 · Viewed 7.1k times · Source

We are working on a datawarehouse for a bank and have pretty much followed the standard Kimball model of staging tables, a star schema and an ETL to pull the data through the process.

Kimball talks about using the staging area for import, cleaning, processing and everything until you are ready to put the data into the star schema. In practice this typically means uploading data from the sources into a set of tables with little or no modification, followed by taking data optionally through intermediate tables until it is ready to go into the star schema. That's a lot of work for a single entity, no single responsibility here.

Previous systems I have worked on have made a distinction between the different sets of tables, to the extent of having:

  • Upload tables: raw source system data, unmodified
  • Staging tables: intermediate processing, typed and cleansed
  • Warehouse tables

You can stick these in separate schemas and then apply differing policies for archive/backup/security etc. One of the other guys has worked on a warehouse where there is a StagingInput and a StagingOutput, similar story. The team as a whole has a lot of experience, both datawarehouse and otherwise.

However, despite all this, looking through Kimball and the web there seems to be absolutely nothing in writing about giving any kind of structure to the staging database. One would be forgiven for believing that Mr Kimball would have us all work with staging as this big deep dark unstructured pool of data.

Whilst of course it is pretty obvious how to go about it if we want to add some more structure to the staging area, it seems very odd that there seems to be nothing written about it.

So, what is everyone else out there doing? Is staging just this big unstructured mess or do folk have some interesting designs on it?

Answer

Christian Loris picture Christian Loris · May 14, 2009

I have experienced the same problem. We have a large HR DataWarehouse and I am pulling data from systems all over the enterprise. I've got a nice collection of Fact and Dimension tables, but the staging area is a mess. I don't know of any standards for design of this. I would follow the same path you are on and come up with a standard set of names to keep things in order. Your suggestion is pretty good for the naming. I'd keep working with that.

Related questions

Database warehouse design: fact tables and dimension tables

I am building a poor man's data warehouse using a RDBMS. I have identified the key 'attributes' to be recorded as: sex (true/false) demographic classification (A, B, C etc) place of birth date of birth weight (recorded daily): The …

Read More
How to handle late arriving dimension and NULL business key in Kimball Data Mart?

I'm trying to implement Kimball data mart which uses -1 and -2 rows in dimension table for late arriving dimension and for NULL business key. I have an example code below which creates one staging table for fact and dimension …

Read More
Remove Primary Key in MySQL

I have the following table schema which maps user_customers to permissions on a live MySQL database: mysql> describe user_customer_permission; +------------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------+------+-----+---------+…

Read More