I'm designing a Staging+NDS+DDS Data Warehouse system, where an ETL is going to normalize data from
[Staging] and load it into
[NDS], which will hold all history.
I've pretty much finished the T-SQL script that will create the tables and constraints in the
[NDS] database, which contains Master and Transactional tables, that will respectively feed
[DDS] Dimension and Fact tables in what I'm intending to be a star schema.
I'm given myself the following rules to follow:
[DDS]dimensions are prefixed with
[DDS]facts are prefixed with
DWD_Customerstable has control columns:
DWF_InvoiceHeaderstable has control columns:
PK_followed by the table name (including the table prefix) - e.g.
uniqueconstraint on natural keys, and those are always prefixed with
NK_followed by the table name (including the table prefix) - e.g.
DWK_followed by the name of the referenced table (without its prefix) and the word "Key" - e.g.
All prefixed columns have no business meaning and should never appear in views; this leaves me with, I find, a pretty clean and consistent design, and
create table scripts looking like this:
create table DWD_SubCategories ( DWD_Key int not null identity(1,1) ,DWD_DateInserted datetime not null ,DWD_DateUpdated datetime null ,DWK_CategoryKey int not null ,Code nvarchar(5) not null ,Name nvarchar(50) not null ,constraint PK_DWD_SubCategories primary key clustered (DWD_Key asc) ,constraint NK_DWD_SubCategories unique (Code) );
So, my question is, is there anything I should know (or unlearn) before I continue and implement the ETL to load data into this database? Would anyone inheriting this database want to chase me down and rip my head off in the future? What should I change to avoid this? The reason I'm asking about prefixes, is because I'm using
DWF, but the tables are technically not "dimension" and "fact" tables. Is that confusing?
Also, I'm unsure about the concept of natural key - am I correct to presume it should be a unique combination of columns that the source system might consider its "key" columns, that I can use in the ETL process to locate, say, a specific record to update?
There is always at least something else you should also know and almost equally, always something else you should be consciously putting a stop to. Specifically in the context of data warehousing, which is a relatively fledgling sector, leveraging relatively new technologies.
In regards to what I've seen in the real world, walking into a company for the first time and seeing what I'm understanding about your design would be genuinely tear-inducing: Tears of joy and relief. From the outset, you are well on your way to beginning what appears to be a well thought-out ( well engineered ) ETL / data warehousing system. As with the implementation of any software product, your mileage may vary as the solution grows and is consumed by the business, but fundamentally, you are on The Right Track ( and yes, you know what a natural key is ).
I've found there to be a number of challenges with these type of solutions, which I will touch upon to reinforce some of your decisions and perhaps lend some insight into the road ahead of you. Firstly, the number of times I've found myself in a predicament on account of a developer ( even fellow database administrators / data professionals ) misunderstanding the context of a control column ( using, for example running a process against the
DateInserted column, a mere time stamp of insertion, over the
DateReceived or similarly named column, intending to relate a row to a particular date of occurance ), that while I agree completely with the cautions @Aaron Bertrand raises, I feel that the prefixes for your control columns could actually be leveraged as a sort of flag to help prevent their misuse. Obvious should be obvious of course, but much like writing code in general, explicit is preferable. That said, I would almost certainly leave such prefixes out of the indexes and such ( probably even keys -
PK types can and should stay in my opinion, but unless there's a real threat of
DWF_SubCategories existing in the same schema, they really are just fluff ). I think the concern about the
DWF prefixes is valid, but they'll be living in the
[NDS] catalog and would serve to indicate intent, making it completely fine to use the nomenclature in that manner.
The second ( and perhaps most infuriating ) challenge is one of cross-training your coworkers. All of the software engineering, usage flags and design practice rules are completely for naught if your striving-for-paycheque-over-excellence colleagues get involved and do their less than very best ( or to be fair, are even just simply having a bad day ). Do keep in mind that large projects generally have many fingers in the pot, so it is imperative that those fingers are behaving well.
The last thing I'll touch on here is to always keep in mind the actual value of any ETL system to a business. Of the Extract, Transform and Load paradigm, the first and final letters have absolutely no business value, so you will want to work on making the development and maintenance of both the Extract and Load processes as minimal as possible - the "real" work will be done in the Transform phase, so you will want to automate the E and L steps as much as possible so that you can focus on making ( and keeping ) your solution valuable to the business unit by actively working on the transforms.
All of that said, I've only had the opportunity to work on a handful of different warehousing solutions so perhaps a more knowledgeable user could step in and remove my foot from my mouth if I need correcting. As I said initially, this is one of those areas where one can always learn or unlearn something, and I am absolutely no exception.
Oh, one more thing ( and probably the most important ) - Unit Test! Once your E and L are working as intended and you've had the opportunity to put a few domains through your T solution, get somebody to vet the results. If they're good, save the result set somewhere, so that when you make changes ( and you will, without a doubt ) you can ensure you haven't broken something, somewhere else. Again, automate this process as much as you possibly can ( it's another 0-value process to the business, until they go without it at least ;) ). I generally set up a separate schema or catalog for this purpose.
Hopefully some of what I've said will be useful to you!
As an update, @Aaron Bertrand's schema separation seems like it would be quite a good way to avoid unnecessary prefixing as well, so certainly consider that ( I know I will haha ).
dwfschemas, swapping "Key" for "Id" (which I prefer by far), and prefixing control fields with an underscore. — Nov 20, 2014 at 00:14
External links referenced by this document: