DEDP 4.5 — Materialized View vs. OBT vs. dbt Table vs. OLAP Cube vs. DWA
Five approaches to the same problem: persist complex SQL so users get fast answers. All are convergent evolutions of “cache the business logic.”
The Five Approaches
Materialized Views (1998+): Database-native cached query results. Oracle first (1998), BigQuery added support in 2020. Fixed granularity, zero software engineering overhead, but no lineage, testing, or documentation. The simplest cache.
Traditional OLAP Cubes (late 1990s): Pre-aggregated multidimensional structures (SSAS, MDX). Sub-second slice-and-dice across dimensions. The killer feature was dynamic drill-down; the killer problem was cardinality explosion — each new dimension multiplies the pre-computation space. See 06-reference/2026-04-03-the-data-warehouse-toolkit for the dimensional modeling foundation that OLAP cubes operationalized.
dbt Tables (2016+): SQL + software engineering. Jinja templating, lineage graphs, unit tests, documentation, version control. Emerged from RJMetrics (2016), popularized by Fishtown Analytics (2018+). The key innovation is not the SQL — it is applying DRY principles, testing, and governance to transformations that previously lived as unmanaged scripts.
One Big Table / OBT (2021+): Denormalize everything into wide tables. Eliminates joins at query time by paying the cost at build time. Leverages cheap cloud storage and columnar compression. Trade-off: dimension changes require full backfill. Works best when dimensions are stable and query patterns are predictable.
Data Warehouse Automation / DWA (2011+): Metadata-driven code generation for the entire warehouse lifecycle. Tools like BiGenius, Qlik Compose, BimlFlex, TimeXtender. Generates the SQL rather than writing it. Risk: vendor lock-in and emotional resistance from engineers who feel automated out.
Three Shared Patterns
- Cache Pattern — all five persist data to disk for fast retrieval. Short-term state storage that trades freshness for speed.
- Business Transformation — complex domain logic (finance calculations, supply chain rules) encoded and persisted. This is where the actual business value lives.
- Reusability — templating, modular design, stacking transformations. dbt makes this explicit; the others do it implicitly.
Decision Framework
| Factor | Best Fit |
|---|---|
| Simple caching, no governance needs | Materialized Views |
| Multi-dimensional drill-down, stable dimensions | OLAP Cubes |
| Team-based development, testing, lineage | dbt |
| Predictable queries, stable dimensions, max speed | OBT |
| Large-scale warehouse, metadata-driven | DWA |
The chapter’s most useful insight: the choice depends on organizational maturity, not technical superiority. A team without engineering discipline will not succeed with dbt. A team drowning in manual SQL will not succeed without DWA or dbt. Match the approach to the team.
What Matters for Consulting
dbt governance is a real problem. The chapter flags that without governance, dbt projects balloon into thousands of unmaintained tables. This is the #1 issue we see in 01-projects/phdata/index dbt engagements. The pattern is: team adopts dbt for reusability, skips governance, ends up with worse sprawl than before.
OBT is underrated for specific use cases. When dimensions are stable (geographic hierarchies, product catalogs that change quarterly), OBT eliminates join complexity and dramatically simplifies BI tool configuration. The trade-off (backfill on dimension change) is acceptable when changes are infrequent. Relevant for 06-reference/2026-04-03-headless-bi semantic layer discussions — OBT can simplify the physical layer that the semantic layer sits on.
DWA is the pattern behind “declarative data stacks.” The chapter connects DWA to the broader declarative movement. This maps to the 06-reference/2026-04-03-data-products-taxonomy vision of data products as configuration rather than code.
The cache pattern is universal. Every materialization strategy is a caching decision. Frame it that way for clients: “Where in your pipeline should you cache, and what freshness trade-off is acceptable?” This makes the conversation about business requirements, not technology preferences. 06-reference/concepts/analytics-as-craft — choosing the right materialization is a craft decision, not a formula.
What’s Academic
The detailed history of each tool’s release dates is reference material, not insight. The DWA section is the thinnest — it reads like a vendor category overview rather than a pattern analysis.
Key Takeaway
SQL is the constant. Everything else — MVs, OLAP, dbt, OBT, DWA — is a different answer to “how do we make SQL results fast and reusable?” The maturity of your team and the stability of your dimensions determine which answer fits.