Capstone: your own pipeline

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.

  1. 1Where you left off
  2. 2One time, two formats
  3. 3Build staging
  4. 4Latest version wins
  5. 5Write the tests
  6. 6Fix the data, not the test
  7. 7What is one order?
  8. 8Dimensions
  9. 9The fact table
  10. 10Nothing lost, nothing doubled
  11. 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.