2

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)

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
4
  • Thank you for your reaction, mustaccio. I interpret your comment about single source of truth, that without a dataware house you dont have the relationships between your businessdata elements, which makes sense.
    – dotsomething
    Oct 16, 2020 at 18:33
  • Well, you do have relationship between data elements regardless of where those data are stored, because it's intrinsic to the data themselves, however, in the absence of a data warehouse, which maintains and enforces various constraints between the elements in one location, different consumers might see different interpretations of this relationship.
    – mustaccio
    Oct 16, 2020 at 18:40
  • With respect to this aspect, Marklogic -a multimodel database supplier as you probably know suggests that such database (/their product) can also replace a datawarehouse (source: marklogic.com/product/comparisons/data-hub-vs-data-warehouse, last section "..We have many customers who chose to supplement or replace their data warehouses with a MarkLogic Data Hub"). I don't understand their claim with respect to your comment. Their product 'only' adds indices to extracted data, but does provide a single version of truth usings relationsship. Any thoughts about that?
    – dotsomething
    Oct 16, 2020 at 20:48
  • 1
    Never heard of Marklogic until now, but after a cursory glance at their product blurb it seems to me they are conflating concepts and tools ("Snowflake and Redshift are popular reincarnations of traditional data warehouses like Netezza and Teradata"), which to me is not a good sign. Any combination of tools can comprise a DW, as long as it meets the requirements. "replace their data warehouses with a MarkLogic Data Hub" -- you can replace your DW with whatever you want, if what you really want is that other thing, or if that thing does what you want. Just don't call it a DW then.
    – mustaccio
    Oct 16, 2020 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.