15.00 - Arguments Against Denormalizing for Performance - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Arguments Against Denormalizing for Performance

Data warehousing authorities often argue that physical denormalization of the logical data model offers better performance than a physical instantiation of the normalized logical data model. This question is mitigated fully when the database engine can handle a normalized physical design and scale linearly. Teradata Database does just that (see “Born To Be Parallel” on page 25).

If further need to improve performance remains aside from scaling the database, then implement a well-planned data propagation strategy that maintains and complements the underlying normalized base table substructure.

To begin, consider propagating denormalized data within the same database instance. Weigh propagating to another environment only if there are other considerations for the target data source, such as geographic needs or the need to support proprietary data structures. In any case, the propagation is from the data warehouse and not directly from source systems.

These reasons support this strategy.

  • The fully-parallel capabilities of the data warehouse can be used to optimize the propagation of data.
  • By keeping the data in the same instance of the database, you can perform hybrid queries that take advantage of both the denormalized and normalized data. For example, large volumes of nonvolatile data from transaction detail rows can be propagated into new physical fact tables, and smaller volume, highly volatile dimensional data can be built into virtual dimension tables.
  • The resulting administration of the complete data warehousing environment is not only easier, but also less expensive.