Thoughts on DWH

• No insignificant attributes, i.e. either an attribute is
• primary key (surrogate key) uniquely marking the individual record
• unique key (business key)
• surrogate key business by which delta detection join is done (if one gets snapshot data), integrates business keys of different systems making key handling simpler
• foreign key referencing the business surrogate key
• payload being considered in comparison for the delta detection
• Only attributes used for calculations are of number type, e. g. keys, codes are not
• Flags are numeric(1) as they tend to be used to be counted over. 1 means true, -1 means false, no other value but NULL is allowed
• One process, one target table: if there are several source eventually loading a target, keep the flows separate as long as the handling of the data is not the same, to ease the debugging and unite the flows with a union operation in a process on its own such that the truncating of the target remains possible
• Write all processed data (unless directly filtered from the source) into (intermediate) target table. This makes completeness checks easier.
• The use of one UUIDs/GIDs for alle the surrogate keys makes Management between different environments easier because of fewer dependencies.
• For surrogate key values use a generator as close to the data storage as possible, e. g. database fucntions and make the filling an automatic process independent from the feeder. This helps avoid messed keys. Fiddling the key values is takes more effort but this should be the case of exception anyway.
• A process step shall be able to get restarted without producing data mess, either by discarding already processed data (truncate target table) or by taking already processed data into account
• A process step should be runnable on its own for ease of (unit) testing
• Process abortion better than problem logging as logged information usually is not being monitored and not corrected
• Raw area contains data 1 : 1 as received (no constraints at all)
• Staging area contains data having been checked (cleansed)
• unique constraints
• referential constraints
• value boundary constraints
• Temporary area contains tables with intermediary values
• those tables get truncated before an individual load, i.e. no joint use of a target table
• those tables can be used between raw and staging or staging and core
• any number of tables used get from raw to staging or from staging to core is fine
• Core area contains the fully integrated data being the only source for consumers marked by either (convention, choose one and stick to it even in hell):
• position, e.g. second attribute of the table
• its index name starts with a marker, e.g. BSI
• its attribute name
• At least in the last temporary table before core business surrogate key must be known
• The last step from to core (creation surrogate key and history) is done by generated code as the creation of history is a well known process without variation. Rules can be derived from the data dictionary of the data base (see also https://sourceforge.net/p/pg-scd/wiki/Home/).
• The data copy to the database is done by generated code as it is a mere 1:1 copy. Rules can be derived from the data dictionary of the data base. Maybe external tables can be used.
• Generated and executed code is to be logged.
• Small increments in features/fixes (e. g. Scrum)
• reduce risks
• make happy because one sees improvements fairly often
• can be handled easier
• are defied by monolitc deployments (i. e. if you can deploy a specific technology - e.g. PowerCenter - only if you deploy all objects of that technology, you usually have so many time dependencies within that technology that you cannot deploy small increments)
• Fall back systems can be used to provide almost seemless service provision only if it does not take much time to migrate data (data migration can be automated and as well as the switch to the already migrated system). Database replication might help.
• Delta deliveries, i. e. only data that has been changed or "deleted" with respective marker, is preferable because the amount is significantly less and delta detection/handling is easier with historised tables (SCD2). Missing data delivery is not more grave - leading to unchanged or not closed records - than with such of snapshot deliveries - leading to wrongly closed records instead of changed or unchanged records.
• Interfaces to other systems be it sources or consumers should sport a layer which provides development indepence as far as possible. One possibility is the use of views in database Environments, another data container of which the sole purpuse is the privision of the data from sources or to consumers such that changes within a system can be unwound exclusively within that system.
• In an environment where data needs to be anonymised, it is important that at occasions that data can be traced back to individuals, one can set up a non-disclosed table to map identifying data to a purely technical surrogate key.
• To setup naming conventions you should first decide whether the ordinary development is vertical or horizontal:
• vertical: A change is being developped from the first source to the final target in "one go" before moving to the next Change.
• horizontal: Changes are being developped layer by layer, i.e. all changes affecting one layer are being implemented before changes of the next layer are being implemented
If vertical development is preferred - I have not come accross horizontal development - make the names group by the topic (usually the final target table) go to the beginning of the name only possibly preceeded of technological information like 'df' for dataflow, 'wf' for workflow, 'm' for mapping and so on. Layer information stays at the end. E. g. m_SALES_FACT_s2t. In case of horizontal development make the names group by the layer putting that information in front of the topic.