Data warehouse design example

Data Warehousing Concepts

Aaren Stubberfield

Data Scientist

Let's set the stage

A new startup company
Photo sharing app

An image of person photographing a scene

¹ Photo by Alex Alvarado from unsplash.com

Top-down, or bottoms up approach?

Considerations:

Vital to show business impact quickly
Top-down approach has a longer startup process

Decision:

Bottom-up approach
Sales data mart must be the priority

Kimball - select the organizational process (step 1)

Considerations:

What type of customers purchase large volumes of photos?

Decision:

Develop customer purchases

Kimball - Declare the grain (Step 2)

Considerations:

Data should be flexible to answer many questions
Selecting the lowest grain possible

Decision:

Tracking customer/photo purchases

Kimball - Identify the dimensions (Step 3)

Considerations:

How do users describe the data that results from the business process?
Customer prioritization

Decision:

Customer location (country & state)
Date customer joined
Default payment method

Kimball - Identify the facts (Step 4)

Considerations:

What are we answering?

Decision:

Time spent viewing photo before purchase
Photo cost and tax
Date of purchase

Fact and dimensions tables

A star schema set of dimensional and a fact table

On-premise or cloud implementation

Considerations:

We do not want upfront costs for hardware / software infrastructure
Small team - focus on high value activities

Decision:

Cloud implementation

ETL or ELT implementation

Considerations:

Keep all data
Cloud implementation allows us to scale compute as needed

Decision:

ELT implementation

Summary

Planning is critical
Tailor your approach based on the situation

Let's practice!

Data Warehousing Concepts

Preparing Video For Download...