What are the disadadvantages of a datawarehouse with no datawarehouse tables, but only datamarts

data-warehouseetl

The ETL design pattern prescribes transformation from staging tables to a central data warehouse data model, followed by extracting data from the data warehouse data model to data marts.

Are there any limitations, and what could go wrong, with transforming data from the staging tables directly into a data mart? (e.g., skipping the data warehouse data model)

Doing that would save a lot of development time and effort in my view. It would also save time when the data warehouse model changes. Data history could be accomplished in the staging layer.

The data warehouse my organisation is currently developing (based on the ETL pattern) seems to take ages. We do need a data warehouse/business intelligence solution, but it doesn't need to be extremely advanced; we are a midsize organisation (1000+ employees).

Would using ELT instead of ETL save development time? (e.g., writing SQL queries for the transformation instead)

Best Answer

The purpose of the data warehouse is to serve as a single source of truth1 for your BI consumers. It provides you with the central place where the data and metadata for your reports and dashboards originate. If you deprive it of this role and create a multitude of data marts populated by your operational data (via staging tables or not), inevitably each of these data marts will contain its own "source of truth", not necessarily correlating with what other data marts think is the truth, thus diminishing the value of business intelligence derived from these multiple truths.

Building a data warehouse is indeed a notoriously long and failure-prone process, but developing ETL procedures is not nearly the most time-consuming part of it; it is master data management and modelling that takes the most time and effort.

Also, the size of your organization is barely relevant; it's the value that you expect to derive from your BI and, indirectly, from the data warehouse that feeds it that should drive your decision to take shortcuts.

1 - Some say it's a single version of truth, because ultimately the source of those data is elsewhere.

Related Question