Data warehouse architectures

Data Warehousing Concepts

Aaren Stubberfield

Data Scientist

Inmon - top-down

Image of data warehouse icon

Inmon - top-down

Image of data warehouse icon

Must decide:

On all data definitions, cleaning, and business rules
Before any data enters warehouse

Inmon - top-down

image of two database icons with arrows pointing to an arrow labeled ETL which points to data warehouse icon

Inmon - top-down

image of two database icons with arrows pointing to an arrow labeled ETL which points to data warehouse icon with arrows pointing to two data mart icons with arrows pointing to an icon of a person on a computer, a reporting icon, and magnifying glass icon

Pros and cons of top-down

Advantages:

Single source of truth for organization
Normalization = less storage
Easy to change data marts to support reporting changes

Disadvantages:

More joins = slower response time
Lengthy upfront work
- Higher startup cost

Kimball - bottom-up

Image of two database icons with arrows pointing to an arrow labeled ETL and arrows pointing to two data mart icon

Denormalizes data
Focus on departmental data mart
Data moves directly from ETL to data marts

Kimball - bottom-up

Image of two database icons with arrows pointing to an arrow labeled ETL and arrows pointing to two data mart icon with arrows pointing to a data warehouse icon with arrows pointing to an icon of a person on a computer, a reporting icon, and magnifying glass icon

Pros and cons of bottom-up

Advantages:

Upfront development speed
- Lower startup cost
Denormalized = user friendly

Disadvantages:

Increased ETL processing time
Greater possibility of duplicate data
Ongoing development needed

Let's practice!

Data Warehousing Concepts