lesson 74 Warehouse & reporting projects · advanced

EDA: explore before telling the story

Explore distributions and relationships before reporting.

the idea

Start with schema, dimension categories, date coverage, measures, and grain. COUNT(DISTINCT sale_key) counts sales lines in this model, not a missing order-header identifier. Define what a metric counts before putting it into a report.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT
  MIN(order_date) AS first_sale,
  MAX(order_date) AS last_sale,
  COUNT(*) AS sales_lines,
  SUM(sales) AS total_sales
FROM fact_sales;

Watch for: Sales-line count and order count are different when an order contains several lines.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Look at one sales line. Each fact_sales row is one sales line. Decide that before you count or sum.

   step-1.sql Run

   sql›

   SELECT sale_key, customer_key, quantity, sales, order_date
   FROM fact_sales
   LIMIT 8;

2. 2

   Run the reference. Start with schema, dimension categories, date coverage, measures, and grain. COUNT(DISTINCT sale_key) counts sales lines in this model, not a missing order-header identifier. Define what a metric counts before putting it into a report.

   step-2.sql Run

   sql›

   SELECT
     MIN(order_date) AS first_sale,
     MAX(order_date) AS last_sale,
     COUNT(*) AS sales_lines,
     SUM(sales) AS total_sales
   FROM fact_sales;