Lynn Pettis (4/19/2010)
Here is a guess at why you see a larger growth from stage to production than from source to stage: indexes on the production table(s). Do you currently drop and recreate the indexes or are they in place during the transfer?
Ah ha! It all make sense now. I keep the indexes online. I had considered drop/recreate, but up until now could see no specific reason to do so. The process itself didn't take that long, so I didn't think I'd really get much of a performance boost from dropping/recreating indexes. There are about twenty indexes on the fact table. Most are INTs, but still the index space for the table is nearly as much as the data space.
On the other hand, there are virtually no indexes on the intermediate table. Only a identity key added today.