Data warehouse design example

Concetti di Data Warehousing

Aaren Stubberfield

Data Scientist

Let's set the stage

  • A new startup company
  • Photo sharing app

An image of person photographing a scene

1 Photo by Alex Alvarado from unsplash.com
Concetti di Data Warehousing

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
Concetti di Data Warehousing

Kimball - select the organizational process (step 1)

Considerations:

  • What type of customers purchase large volumes of photos?

Decision:

  • Develop customer purchases
Concetti di Data Warehousing

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
Concetti di Data Warehousing

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
Concetti di Data Warehousing

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
Concetti di Data Warehousing

Fact and dimensions tables

A star schema set of dimensional and a fact table

Concetti di Data Warehousing

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
Concetti di Data Warehousing

ETL or ELT implementation

Considerations:

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

Decision:

  • ELT implementation
Concetti di Data Warehousing

Summary

  • Planning is critical
  • Tailor your approach based on the situation
Concetti di Data Warehousing

Let's practice!

Concetti di Data Warehousing

Preparing Video For Download...