Pro lesson Expert
Clean and model it
Turn raw orders into tested, deduplicated fact and dimension tables.
25 min11 hands-on steps
In this lesson you will
- Build staging tables: real types, one spelling per value, no duplicates, newest customer version
- Run a test suite, watch a test fail, and fix the data the right way
- Build the star schema: dimensions with surrogate keys and an unknown member, then the facts
Words you'll meet
- Staging
- Tables built from raw with types fixed, text normalized and duplicates removed. Raw stays untouched.
- try_strptime
- Reads text as a timestamp using a format you give, like %m/%d/%Y. Text that does not fit becomes NULL.
- Test suite
- Several tests run together, one row per test with its count of bad rows. All zeros means pass.
The steps
Each step teaches one idea, then has you try it hands-on in your browser, and checks your work.
- 1Where you left off
- 2One time, two formats
- 3Build staging
- 4Latest version wins
- 5Write the tests
- 6Fix the data, not the test
- 7What is one order?
- 8Dimensions
- 9The fact table
- 10Nothing lost, nothing doubled
- 11Challenge
This lesson is part of LearnDE Pro
$12 a month or $100 a year: every lesson, spaced-repetition flashcards, and Learn by teaching. Refund on request within 14 days.