Re: Philosophical question - Staging tables, name and location

On 1/28/2014 4:48 PM, Charles Wilt wrote:

Another philosophical question for you all..

I'm beginning to work with a once a week incremental data feed from an
outside source that consists of about 30 tables.

I'll end up with a set of 30 production tables and 30 staging tables.

I intend to have the production tables in a separate library from the rest
of my application (currently in 4 data libraries)

I'm trying to decide rather of not to keep the staging tables in the same
library as the production tables or in another library.

Obviously, if I keep them in the same library, I'd need need to have the
staging table named differently from the production version. Whereas if I
put the staging tables in another library I could have the same name.

I was initially leaning toward same name, different libraries. But now I'm
thinking I want different names so I can use unqualified names and depend
on the library list resolution. Primarily so I can have TEST/DEV
environments within my change management system (Aldon)

But that leaves me with deciding rather or not to have the tables in one
library or two.

Thoughts?

I can't imagine staging tables with the same layout as production
tables. By that I mean that generally speaking, inbound data isn't
typically normalised. Or free of decimal data errors (commas, decimal
points, minus signs and currency symbols in amounts, slashes or dashes
in dates, etc.) So in my case, I always use different names for the...
raw input as opposed to the final destination, production tables. I
keep them in the same library because they have similar security
requirements. If I don't want someone peeping at birth dates in
production, I probably don't want them peeping at them in a staging
table, no matter how transient that data may be.

An argument for putting staging tables outside of production is disaster
recovery / backup, especially if the staging tables are very large (say,
XML formatted?) Why back up what is essentially transient data?
Especially if restoring those extra 20GB will slow down restoring the
production tables (and you won't need it because it was work data)?
--buck