Customers do odd things

You may have read in a previous Blog that I am a great believer in the use of constraints (not null, primary key and, of course, foreign key) in a data warehouse. In an Oracle data warehouse these constraints inform the query optimiser about relationships between dimension and fact data. Without this information features such as materialized view query rewrite and star transformations would not work.
But of course there is another use for constraints and that is rule enforcement. Normally, I use RELY constraints in a DW, that is I trust my ETL process to provide referentially valid data. However, the other day I was glad that we had used a real primary key constraint on a customer table. One of our customers has a business rule that you have to have a ‘shopper’s card’ to use their stores. This gives them the ability to tie each shopping basket to an individual, a great plus point for a retailer. As we only receive a customer file once a week but sales files each day we have another rule that says ‘if a transaction is made by an unlisted customer we should assume that they are a new customer and create a minimal customer record to allow the transaction to be accepted’ The new customer record would probably arrive the following weekend and update all of the unknown information about the customer (such as their name!) This worked well for over five years until our customer made another change to their business rules – customer cards are now valid in any store. This seems a small change that is unlikely to affect the creation of a new customer unless they shop in two stores on the same day, which of course is what someone did.
We have now fixed the OWB map logic to expect that a customer can visit more than one store on their first day as a customer.