In the OLTP (online transaction processing systems – usually operational systems) world we measure the quality of a database by its ability to pass the ACID test. That is Atomicity, Consistency, Isolation and Durability – ACID. For more details see: http://en.wikipedia.org/wiki/ACID. As we make our data warehouse and data marts more near-real time and active, more extreme if you like, the need for those database to pass the ACID test becomes paramount. And maybe we need to consider in ETL or ELT (Standard definition: E – Extract , T – Transform, L – Load) that the "T" really needs to stand for TRANSACTION and not transformation.
Where does that leave all of those companies who have invested so much time in developing disparate data marts that don’t maintain any level of ACID? Where there is replications there is no Atomicity. Where there is data in different levels of consistency there is no Consistency. Isolation, well oh yeah, data marts are isolated alright but not within the context of a transaction. And Durability, if you’ve ever had someone ask you to roll back some historic information because it was loaded incorrectly the concept of durability just went out the window.
In the Data Warehouse (DW) world the concepts of data quality, integrity and reliability continue to come up. The ACID test solves this for us in the OLTP world. It need to do the same in the OLAP world. Of course even in the OLTP world with ACID, the integrity of the database cannot be guaranteed, and in practice, these properties are relaxed to provide better performance (see my blog on Performance is Always an Issue). The compromises we make for performance reasons always come back to roost. And that’s where the DW world finds itself right now. We gave up some data integrity and quality in order to provide performance to our users. And our users were okay with that because they were so desperate to get their hands on the data. Now that they’ve had the data for a while they are starting to ask pointed questions about the data’s viability as a tool in which they can entrust their business decisions. If your DW passes the ACID test the answer gets a little closer to Yes.
