Data Warehouse Design Patterns

This post will not dive into each topic in detail, but serve more like a curriculum of things to research for the  Data Journey.  Anyone who needs to get into the Data Warehouse (DW) space should have a handle on the following Design Patterns:

Connection Patterns

There are 4 Patterns that can be used between applications in the Cloud and on-premises.  The combinations are as follows

• on-premise caller to Cloud provider
• Cloud caller to on-premise provider
• Cloud caller to Cloud provider

1. Remote Procedure Calls (RPC) Connection Patterns
2. Asynchronous (fire and forget) Connection Patterns using Queues
3. Shared Database in cloud or on-premise
4. Data/File synchronizing in Copying Data (ETL) flat file loads, database to database sources to targets.

Integration Patterns

Extract Transform Load (ETL)

Truncate and Load Pattern (AKA full load): It’s good for small- to medium-volume data sets and can load pretty fast.  It is good for staging areas and is simple.  The key benefit is that if there are deletions in the source, then the target is updated pretty easily.  The disadvantage is that no history is kept, and no tracking occurs. CUID, i.e., Created, Updated, Inserted, or Deleted, cannot be tracked.

Slowly Changing Dimension Type 1 Pattern: This pattern is simple but very slow and should not be used for anything over 1000 rows.  See the dimensions definition for type 1

Slowly Changing Dimension Type 2 Pattern: This pattern is simple but very slow and should not be used for anything over 1000 rows.  See the dimensions definition for type 2

A blue-green data warehouse loading pattern: This is a zero-downtime way to load or refresh warehouse data by keeping two parallel versions of the data in the semantic layer. The blue is the current live one, and green is the new one being built, loaded, and validated. The pipeline is guided by a control table that contains the passive logical schema to load. Once the green side is ready, you switch consumers over in one step, which makes rollback easy if something looks wrong.

Consumption Patterns

Declarative/Adhoc SQL Query

1. Join patterns: directional, inner, or equijoin, left and right outer join, full outer join. A theta join allows for arbitrary comparison relationships (such as ≥ or between).  An equijoin is a theta join using the equality operator.  A natural join is an equijoin on attributes that have the same name in each relationship
2. Flattened Hierarchies, which put all the levels on one row as columns, vs Ragged hierarchies, in which, like unbalanced hierarchies, the branches of the hierarchy can descend to different levels.
3. Join Tables/ Translation tables/ Conformed Tables are usually used when putting two silo systems in the same context, so the data can be merged
4. Parent/ Child Tables and Cardinality (Fan traps that occur when using aggregate measures), the parent is a foreign key (FK) on the child record, so the relationship creates data clusters. We have to ensure we do not write queries that duplicate aggregated values.
5. Self Joins (aka Alias) in SQL: we can refer to the same table by another name and join to itself. For example, a manager is a type of Person, so the person table can be self-joined to get the manager’s info.

Query Performance Patterns

1. Explain Plans, Indexing, and Partitions; this is the bedrock of performance tuning in relational databases.  This topic alone deserves its own post. It would depend on table storage and data type configurations in the Data Definition Language (DDL) setup. It will also need knowledge of data cardinality to create balanced trees vs bitmap indexes, user query patterns to create covering indexes, and to get more index range scans if the query does not uniquely select the index, or to hit partitions to use much smaller data sets for faster queries.
2. ETL Aggregation and Aggregate awareness for multiple aggregation tables
3. Table Constraints in Data quality, including PK, FK, and additional functions or regular expressions that can be put on columns to ensure accurate data, and that NOT nulls are stored as needed.

Interaction Patterns 

Dashboard Design

1. Layout Patterns
2. Leading Indicators Aggregation Pattern
3. Drill Down Pattern
4. Progressive Filtering Choice Pattern

Holy Trinity of Analytics

Security Patterns

I have a dedicated article on security patterns, which are getting increasingly complex as time progresses and new regulations.