Data Staging

The best image I could find?

In Extract, Transform, & Load ETL processes, you might save the data you are extracting to a temporary location and in an unadulterated state. This is called staging the data. Staging is an interesting word. It's a synonym for scaffolding, or a temporary support for something else, such as a career in cooking, a cancer treatment, or a rocket's trajectory. In this case, data staging is a preparation for data that has the format, structure, and purity you want. A data mart is a stage for a data warehouse.

Staging could seem like a bad idea for a number of reasons. First, you're duplicating data. Duplication is almost always a bad word when it comes to data, but staged data should not be used for anything besides staging and perhaps reporting. Still, it requires space, which can be a precious thing even when space is so cheap.

Second, staged data must be written to disk and then read from before doing the final transformations. You're effectively doubling your disk I/O. If the amount of data is significant, this could be a lot of reads and writes.

Written in 2010, a product of experience

These are real trade-offs, but there are a number of reasons to stage, some of which did not occur to me before reading Microsoft SQL Server 2008 Integration Services: Problem-Design-Solution, an excellent book on SSIS best practices by MVP's Erik Veerman, Jessica Moss, Brian Knight, and Jay Hackney.

• Data Lineage - When you run into data purity issues in production, the first question your production support team will ask is, "Where did this data come from?" A staging area provides means of tracking the import of data and may allow you to run subsequent transformations in a temporary transaction so that you can identify duplicates, null values, or other data purity issues. You will need to keep data around for enough time for such problems to be identified and addressed.


• Restartability - Jobs fail. The last thing you want to have happen at 3 AM during a conversion process is a job failure (yes, this has happened to me). A staging area should help you restart the job halfway through, after the necessary corrections have been made. This assumes you run your transformations in a transaction, which you are doing, right?


• Source Alternative - A staging area can act as a reporting environment when your source system is highly transactional or under significant strain. Just make sure you're not doing any writes to stage.


• Archive - Stage can also be an archive system, which may be useful if your destination system becomes corrupt and needs to be rebuilt. For instance, a data warehouse updated by incremental loads could be rebuilt by running through all transactions. I haven't run across this in practice, though.


• Performance - Finally, an ETL process might perform best with staged data. Though SSIS can buffer data flows, databases can often sort and aggregate data faster and with less resource load.

As always, there are no universal solutions to data architecture. Don't stage data just for the sake of staging data. You'll have to weigh the pro's and con's according to your business, technology, QA, and production support needs.

Accounting and Thermodynamics

Predator-vision

A few years ago, I rented a very cheap house in a very cold part of the country. I wanted someplace big to play my drums, but I didn't realize what kind of heating bills I would get in the winter. I ended up keeping the house at 40 degrees Fahrenheit, using space heaters, and freezing a few pipes.

Besides earning a story to tell, I also learned how to see rates of flow. I was suddenly able to see the various heat sources and sinks in my house, with vectors of various strengths showing the direction and rates of flow. Unconsciously, I had always thought of heat as being a property of a room or building, but I now saw heating the way physicists see it.

Locke-vision

Such paradigm shifts, which overlay your present view of the world with a broader experience, are not uncommon. I always enjoyed studying geology, since it allows you to see the seemingly-fixed landscape as a fluid process and to see human activity from the perspective of the Earth. For thousands of years, astrology let people interpret ordinary events through the lens of the cosmos.

One of the most natural ways of seeing the world is as a collection of things with properties. This view was best put down on paper by modern philosophers like John Locke. They went back and forth about how subjective 'secondary' qualities like color and taste could be known to be true to the 'primary' essence of a thing, but they never questioned the atomistic model of the universe. This was only natural when the physics of the day characterized the interactions of the universe by analogy to billiard balls.

I've been trying to get my head around some hard accounting problems, and I realized that my problem was thinking of accounts as things with properties. It is correct, in a sense, to describe accounts as having a dollar amount. But is is more correct to think of them as part of a system of interconnected accounts with various directions and rates of flow, much like the heat in my cold house. This is because the value of an account is constantly changing, and because its changes are the direct result of transfers from other accounts. Even the cash in your wallet is not separate from this plumbing. I've begun to see the systems I build and maintain as part of the flow of the entire monetary system.

This flow is becoming particularly interesting with the growth of currency-less transactions like ACH. If you get direct deposit, you use ACH. In the future, there will be no paper or coin currency. We'll simply transfer funds between accounts with smartphones or other devices. There are many fascinating consequences of the death of currency. For instance, if governments do not have to pay the cost of printing money, the cost of transacting will be borne by retailers in the form of transaction fees. Someone will also need to bear the cost of information theft when you lose your phone.

$0.01, spent at all places and times

But I have a really crazy thought.  If money becomes infinitely liquid, won't its velocity increase infinitely, thus increasing the money supply infinitely, and raising the cost of everything infinitely? I wonder if the the laws of thermodynamics will continue to hold as currency becomes digitized. With real-time web services and other technologies that take us away from daily batch file ETL common to financial systems, we increase the liquidity of money with consequences that are not yet clear. Instead of rates of flow, we may have currency that is in all accounts at all times, much like the Heart of Gold's Infinite Improbability Drive. But I suppose I shouldn't borrow serious thoughts from Douglas Addams.