Data Warehouse Design Patterns

Implementation and Automation

Workshop with Roelant Vos

Register now!

"For a data warehouse, we do not have enough time."

… sounds familiar to you?

DWH-Automation enables faster delivery using agile approaches for DWH implementation. Learn the revolutionary concept of an Automated Enterprise Data Warehouse from Roelant Vos.

  • Implement a Solid Persistent Data Store.
  • Leverage proven hybrid Data Warehouse modelling techniques and patterns based on Data Vault.
  • Build a Proven Meta Data Model for process automation and virtualization.

This practical design and implementation training will discuss the techniques and patterns in great detail. It provides you with everything you need to implement an Automated Data Warehouse Solution from start to finish by choosing the right patterns.

What does Data Warehousing Automation mean?

The idea of an automated virtual Data Warehouse was conceived as a result of working on improvements for generation of Data Warehouse loading processes. It is, in a way, an evolution in ETL generation thinking. Combining Data Vault with a Persistent Historical Data Store provides additional functionality because it allows the designer to refactor parts of the Data Warehouse solution. Hybrid approaches for Data Warehousing are designed to be flexible, to be adaptable to accommodate changes in business use and interpretation. Working with data can be complex, and often the ‘right’ answer for the purpose is the result of a series of iterations where business Subject Matter Experts and data professionals collaborate.

In other words, the Data Warehouse model itself is not always something you always can get right in one go. In fact, it can take a long time for a Data Warehouse model to stabilise, and in the current fast-paced environments this may even never be the case. Choosing the right design patterns for your Data Warehouse helps maintain both the mindset and capability for a data solution to keep evolving with the business, and to reduce technical debt on an ongoing basis. This mindset also enables some truly fascinating opportunities such as the ability to maintain version control of the data model, the metadata and their relationship - to be able to represent the entire Data Warehouse as it was at a certain point in time - or to even allow different Data Models for different business domains.

To allow ideas to grow, creators need an immediate connection to what they are creating. This means that, as a creator, you need to be able to directly see what the effect of your changes are on what you are working on.

This is what the Virtual Data Warehouse as a concept and mindset intends to enable: to enable a direct connection to data to support any kind of exploration and enabling creativity while using it. Thinking of Data Warehousing in terms of virtualisation is in essence about following the guiding principle to establish a direct connection to data. It is about finding ways to seek simplification, to keep working on removing barriers to deliver data and information. It is about enabling ideas to flourish because data can be made available for any kind of discovery or assertion.

Virtual Data Warehousing is the ability to present data for consumption directly from a raw data store by leveraging data warehouse loading patterns, information models and architecture. In many Data Warehouse solutions, it is already considered a best practice to be able to ‘virtualise’ Data Marts in a similar way. The Virtual Data Warehouse takes this approach one step further by allowing the entire Data Warehouse to be refactored based on the raw transactions.

This ability requires a Persistent Historical Data Store, also known as a Persistent Staging Area where the data that is received is stored as it has been received, at the lowest level. If data is retained this way, everything you do with your data can always be repeated at any time – deterministically. In the best implementations, the Virtual Data Warehouse allows you to work at the level of simple metadata mappings, modelling and interpretation "business logic", abstracting away the more technical details.

A Virtual Data Warehouse is not the same as Data virtualisation. These two concepts are fundamentally different. Data virtualisation, by most definitions, is the provision of unified direct access to data across many "disparate" data stores. It is a way to access and combine data without having to physically move the data across environments. Data virtualisation does not however focus on loading patterns and data architecture and modelling.

The Virtual Data Warehouse on the other hand is a flexible and manageable approach towards solving data integration and time variance topics using Data Warehouse concepts, essentially providing a defined schema-on-read.

The Virtual Data Warehouse is enabled by virtue of combining the principles of ETL generation, hybrid data warehouse modelling concepts and a Persistent Historical Data Store. It is a way to create a more direct connection to the data because changes made in the metadata and models can be immediately represented in the information delivery. Persisting of data in a more traditional Data Warehouse sense is always still an option, and may be required to deliver the intended performance. The deterministic nature of a Virtual Data Warehouse allows for dynamic switching between physical and virtual structured, depending on the requirements.

In many cases, this mix of physical and virtual objects in the Data Warehouses changes over time itself, when business focus changes. A good approach is to ‘start virtual’, and persist where required.

Download PDF

Your Trainer

Roelant Vos has been active in Data Warehousing and BI for more than 20 years and is well known as experienced expert in the Data Vault community. Whenever there is some time, he shares his ideas and thoughts on his blog roelantvos.com.

Roelant is General Manager - Enterprise Data Management at Allianz Worldwide Partners in Brisbane, Australia. In a role that is highly focused on analytics, he is working on collecting, integrating, improving and interpreting data to support various business improvement initiatives. Passionate about improving quality and speed of delivery through model-driven design and development automation, he has been at the forefront of contemporary modelling and development techniques for many years.

You want to ...

  • Deeply understand the concepts behind data loading patterns and how to implement them.
  • Leverage ETL generation techniques and spend more time on higher value-adding work such as improving the delivery of your data.
  • Work on a Do-It-Yourself (DIY) solution or have adopted any of the available Data Warehouse Automation (DWA) platforms and seek understanding how these use the patterns and modelling approaches.

As advanced modelling and implementation techniques are also covered, this applies to a wide range of data professionals including BI and Data Warehouse professionals, data modelers and architects as well as DBAs and ETL specialists.


  • Sufficient knowledge of English - Course language is English
  • Understanding of Data Warehouse and ETL development
  • Knowledge of SQL
  • Some scripting / programming experience
    Develop a data solution that can evolve with the business
  • Data Vault Modeling Experience

Optional hands-on sessions only: pre-installed environment with SQL Server 2012, 2014 or 2016, Integration Services and Visual Studio with SQL Server Data Tools.

Is this for me?

By adopting the Data Vault patterns on top of a Persistent Historical Data Store, we can reduce the repetitive aspects of data preparation and maintain consistency in development. These patterns are seemingly straightforward – almost deceptively so. In fact, every pattern needs far-reaching considerations to evaluate both at a technical and conceptual level to truly match the business expectations.

The Data Vault Modelling provides elegant handles to manage complexities, but success depends on correct modelling of the information. Ultimately, leveraging ETL generation and virtualisation techniques allows for a great degree of flexibility because you can quickly refactor and test different modelling approaches to understand which one fits best for your use-case. This enables you to spend more time on higher value-adding work such as improving the data models and delivery of data.

This advanced training is relevant for anyone seeking to understand how to leverage ‘model-driven-design’ and ‘pattern-based code-generation’ techniques to accelerate development. As advanced modelling and implementation techniques are also covered, this applies to a wide range of data professionals including BI and Data Warehouse professionals, data modellers and architects as well as DBAs and ETL specialists.

Flexible design and implementation

The intent of the training is to achieve implementation and advanced techniques as quickly as possible. The training discusses the implementation of the main Data Vault modelling concepts including their various edge-cases and considerations. The mechanisms to deliver information for consumption by business users (i.e. ‘marts’) will also be covered, including details on how to produce the ‘right’ information by implementing business logic and managing multiple timelines for reporting.

The training provides tools and configurations which you can adopt to get started automating your own development – or understand the approaches used in commercial ‘off-the-shelf’ software to be able to fully utilise these.

Training content and schedule

Day 1

  • Model Driven Design overview
  • Solution Design & Architecture
  • Solution pre-requisites and components. What needs to be in place? What concepts should be supported?
  • Data Staging concepts, implementation and approaches
  • Collaborative group modelling workshop
  • Overview of loading patterns and their metadata requirements
  • In-depth Hub pattern considerations and implementation approach (key distribution)

Day 2

  • In-depth Link pattern considerations and implementation approach (relationships)
  • In-depth Satellite & Link-Satellite pattern considerations and implementation approach (handling time-variant data)
  • Technical considerations (indexing, partitioning, joining)
  • Managing scheduling, workflows and parallelism
  • Exception handling
  • Historisation and continuous loading

Day 3

  • Base tables and Derived tables
  • Application of business logic
  • Helper constructs (PIT, Bridge)
  • Dimensions and Facts
  • Flexibility in development (scale-up and scale-out)

Overview PDF

Dates & Prices

postponed until further notice
  • If you are interested please use the registration form, we will contact you
  • EUR 3.094 incl. VAT
EUR 2.600 plus VAT
Price on request


If you have any further questions, please contact us:


Copyright: Roelant Vos

Imprint | Privacy Policy | Image Sources