{"id":1068,"date":"2015-11-10T17:53:43","date_gmt":"2015-11-10T23:53:43","guid":{"rendered":"http:\/\/www.designandexecute.com\/designs\/?p=1068"},"modified":"2026-06-23T06:04:26","modified_gmt":"2026-06-23T12:04:26","slug":"data-warehouse-design-patterns","status":"publish","type":"post","link":"https:\/\/www.designandexecute.com\/designs\/data-warehouse-design-patterns\/","title":{"rendered":"Data Warehouse Design Patterns"},"content":{"rendered":"<p>This post will not dive into each topic in detail, but serve more like a curriculum of things to research for the\u00a0 Data Journey.\u00a0 Anyone who needs to get into the Data Warehouse (DW) space should have a handle on the following Design Patterns:<\/p>\n<h2>Connection Patterns<\/h2>\n<p>There are 4 Patterns that can be used between applications in the Cloud and on-premises.\u00a0 The combinations are as follows<\/p>\n<ul>\n<li>on-premise caller to Cloud provider<\/li>\n<li>Cloud caller to on-premise provider<\/li>\n<li>Cloud caller to Cloud provider<\/li>\n<\/ul>\n<ol>\n<li><strong>Remote Procedure Calls<\/strong>\u00a0(RPC) Connection Patterns<\/li>\n<li><strong>Asynchronous<\/strong> (fire and forget) Connection Patterns using Queues<\/li>\n<li><strong>Shared Database<\/strong> in cloud or on-premise<\/li>\n<li><strong>Data\/File synchronizing<\/strong> in Copying Data (ETL) flat file loads, database to database sources to targets.<\/li>\n<\/ol>\n<h2>Integration Patterns<\/h2>\n<h3>Extract Transform Load (ETL)<\/h3>\n<p><strong style=\"color: initial;\">Truncate and Load Pattern (AKA full load):<\/strong><span style=\"color: initial;\"> It&#8217;s good for small- to medium-volume data sets and can load pretty fast.<\/span>\u00a0 It is good for staging areas and is simple.\u00a0 The key benefit is that if there are deletions in the source, then the target is updated pretty easily.\u00a0 The disadvantage is that no history is kept, and no tracking occurs. CUID, i.e., Created, Updated, Inserted, or Deleted, cannot be tracked.<\/p>\n<p><strong>Slowly Changing Dimension Type 1 Pattern: <\/strong>This pattern is simple but very slow and should not be used for anything over 1000 rows.\u00a0 See the<a href=\"http:\/\/www.designandexecute.com\/designs\/basics-of-data-warehouse-dw\/#dimension\" target=\"_blank\" rel=\"noopener noreferrer\"> dimensions definition<\/a> for type 1<\/p>\n<p><strong>Slowly Changing Dimension Type 2 Pattern: <\/strong>This pattern is simple but very slow and should not be used for anything over 1000 rows.\u00a0 See the<a href=\"http:\/\/www.designandexecute.com\/designs\/basics-of-data-warehouse-dw\/#dimension\" target=\"_blank\" rel=\"noopener noreferrer\"> dimensions definition<\/a> for type 2<strong><br \/><\/strong><\/p>\n<p><strong>A blue-green data warehouse loading pattern: <\/strong>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 <strong>blue<\/strong> is the current live one, and <strong>green<\/strong> 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.<\/p>\n<h2>Consumption Patterns<\/h2>\n<h3>Declarative\/Adhoc SQL Query<\/h3>\n<ol>\n<li><strong>Join patterns: <\/strong>directional, inner, or equijoin, left and right outer join, full outer join. A <em>theta join<\/em> allows for arbitrary comparison relationships (such as \u2265 or between).\u00a0\u00a0An\u00a0<em>equijoin<\/em> is a theta join using the equality operator.\u00a0\u00a0A\u00a0<em>natural join<\/em>\u00a0is an equijoin on attributes that have the same name in each relationship<\/li>\n<li><span style=\"box-sizing: border-box; margin: 0px; padding: 0px;\">Flattened\u00a0<strong>Hierarchies,<\/strong>\u00a0which put all the levels on one row as columns, vs Ragged\u00a0<strong>hierarchies,<\/strong> in which, like unbalanced hierarchies, the branches of the\u00a0<strong>hierarchy<\/strong> can descend to different levels.<\/span><\/li>\n<li><strong>Join Tables\/ Translation tables\/ Conformed Tables<\/strong> are usually used when putting two silo systems in the same context, so the data can be merged<\/li>\n<li><span style=\"box-sizing: border-box; margin: 0px; padding: 0px;\"><strong>Parent\/ Child Tables<\/strong> 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.<\/span><\/li>\n<li><span style=\"box-sizing: border-box; margin: 0px; padding: 0px;\"><strong>Self Joins<\/strong> (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&#8217;s info.<\/span><\/li>\n<\/ol>\n<h3>Query Performance Patterns<\/h3>\n<ol>\n<li><strong>Explain Plans, Indexing, and Partitions;\u00a0<\/strong>this is the bedrock of performance tuning in relational databases.\u00a0 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.<\/li>\n<li><strong>ETL Aggregation<\/strong> and Aggregate awareness for multiple aggregation tables<\/li>\n<li><span style=\"box-sizing: border-box; margin: 0px; padding: 0px;\"><strong>Table Constraints<\/strong> 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.<\/span><\/li>\n<\/ol>\n<h2>Interaction Patterns\u00a0<\/h2>\n<h3>Dashboard Design<\/h3>\n<ol>\n<li>Layout Patterns<\/li>\n<li>Leading Indicators Aggregation Pattern<\/li>\n<li>Drill Down Pattern<\/li>\n<li>Progressive Filtering Choice Pattern<\/li>\n<\/ol>\n<p><blockquote class=\"wp-embedded-content\" data-secret=\"zw79k3T068\"><a href=\"https:\/\/www.designandexecute.com\/designs\/holy-trinity-of-analytics\/\">Holy Trinity of Analytics<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; visibility: hidden;\" title=\"&#8220;Holy Trinity of Analytics&#8221; &#8212; Design and Execute\" src=\"https:\/\/www.designandexecute.com\/designs\/holy-trinity-of-analytics\/embed\/#?secret=sf0qehEGX8#?secret=zw79k3T068\" data-secret=\"zw79k3T068\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<h2>Security Patterns<\/h2>\n<p><span style=\"box-sizing: border-box; margin: 0px; padding: 0px;\">I have a dedicated article on\u00a0<a href=\"http:\/\/www.designandexecute.com\/designs\/security-patterns\/\" target=\"_blank\" rel=\"noopener noreferrer\">security patterns,<\/a> which are getting increasingly complex as time progresses and new regulations.<\/span><\/p>\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-design-and-execute wp-block-embed-design-and-execute\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"xtnDSKoFho\"><a href=\"https:\/\/www.designandexecute.com\/designs\/security-patterns\/\">Security Design Patterns in Data Warehousing<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; visibility: hidden;\" title=\"&#8220;Security Design Patterns in Data Warehousing&#8221; &#8212; Design and Execute\" src=\"https:\/\/www.designandexecute.com\/designs\/security-patterns\/embed\/#?secret=8GsyWTVn3J#?secret=xtnDSKoFho\" data-secret=\"xtnDSKoFho\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>This post will not dive into each topic in detail, but serve more like a curriculum of things to research for the\u00a0 Data Journey.\u00a0 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 [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":2718,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31],"tags":[],"class_list":["post-1068","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bi-data-warehouse"],"jetpack_featured_media_url":"https:\/\/www.designandexecute.com\/designs\/wp-content\/uploads\/2015\/11\/design-patterns.jpg","_links":{"self":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/1068","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/comments?post=1068"}],"version-history":[{"count":5,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/1068\/revisions"}],"predecessor-version":[{"id":25591,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/1068\/revisions\/25591"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/media\/2718"}],"wp:attachment":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/media?parent=1068"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/categories?post=1068"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/tags?post=1068"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}