Shape the data

Every BI horror story starts the same way: someone charted a table nobody had profiled. Duplicated keys double the revenue, a text column refuses to sum, a mystery NULL silently drops rows from a join. This session is the vaccine. You will profile every Daybreak table, meet the four kinds of dirt, and learn the six transforms that BI tools - Power Query, Tableau Prep, dbt - all implement under different names.

Profiling is asking each column four questions: how many rows, how many distinct values, how many nulls, and what is the range? Power BI shows this in Power Query's column quality bar; Tableau shows it in the Data Interpreter and profile pane. We will compute it with SQL so you see exactly what those panes are doing. While profiling, you are hunting four dirt types: wrong types (a date stored as text), missing values, duplicates, and inconsistent categories ("Coffee" vs "coffee" vs "COFFEE").

SELECT COUNT(*)                                        AS row_count,
       COUNT(DISTINCT product_id)                      AS distinct_ids,
       COUNT(DISTINCT category)                        AS distinct_categories,
       SUM(CASE WHEN roast IS NULL THEN 1 ELSE 0 END)  AS null_roasts,
       MIN(price)                                      AS min_price,
       MAX(price)                                      AS max_price
FROM products;

Every prep tool ships hundreds of buttons, but real work is six moves: change type (text to date, text to number), split or derive a column (year out of a date, domain out of an email), filter rows, group and aggregate, pivot or unpivot (reshape wide to tall and back), and merge or append (combine two tables). The last pair is the most-confused pair in all of BI, so let us nail it: merge is a join - it adds COLUMNS by matching keys. Append is a union - it adds ROWS by stacking same-shaped tables.

SELECT SUM(CASE WHEN strftime('%m', order_date) = '01' THEN 1 ELSE 0 END) AS jan_orders,
       SUM(CASE WHEN strftime('%m', order_date) = '02' THEN 1 ELSE 0 END) AS feb_orders,
       SUM(CASE WHEN strftime('%m', order_date) = '03' THEN 1 ELSE 0 END) AS mar_orders
FROM orders;

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

Next session you will relate tables into a star schema, and every relationship needs a key on both sides: unique on the one side, repeating on the many side. The rule that saves you: never trust a key you have not tested. A "unique" ID that quietly duplicates will fan out your joins and double your revenue - the ugliest bug in BI because every individual row still looks right.

SELECT customer_id, COUNT(*) AS rows_per_id
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;

Run the routine across the whole warehouse. Three passes: how big is everything, what are the real category values, and which nulls mean something. By the end you will know this database better than most teams know their production warehouse.

SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL SELECT 'subscriptions', COUNT(*) FROM subscriptions
UNION ALL SELECT 'events', COUNT(*) FROM events;

SELECT 'status' AS field, status AS value, COUNT(*) AS orders
FROM orders GROUP BY status
UNION ALL
SELECT 'channel', channel, COUNT(*)
FROM orders GROUP BY channel
ORDER BY field, orders DESC;

SELECT category,
       COUNT(*) AS products,
       SUM(CASE WHEN roast IS NULL THEN 1 ELSE 0 END) AS null_roast
FROM products
GROUP BY category;

SELECT SUM(CASE WHEN cancel_date IS NULL THEN 1 ELSE 0 END)     AS active_subs,
       SUM(CASE WHEN cancel_date IS NOT NULL THEN 1 ELSE 0 END) AS cancelled_subs
FROM subscriptions;

Three suspicious smells, three investigations. Each box is runnable as-is, but read the result and decide: is this dirt to fix, or a fact to keep? That judgment call is the actual skill.

SELECT p.product_id, p.name, p.category
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.order_id IS NULL;

SELECT p.name,
       p.price AS catalog_price,
       oi.unit_price AS price_at_order,
       COUNT(*) AS order_lines
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
WHERE oi.unit_price != p.price
GROUP BY p.name, p.price, oi.unit_price
ORDER BY p.name;

SELECT strftime('%Y-%m', order_date) AS month,
       status,
       COUNT(*) AS orders
FROM orders
GROUP BY 1, 2
ORDER BY 1, 2;

• Run the 10-minute profile routine on one real table at work: row count, distinct count per interesting column, null counts, min/max on numerics. Write down one thing that surprised you.
• Write down that table's keys - and actually run the GROUP BY ... HAVING COUNT(*) > 1 test before declaring anything unique.
• Find one merge case in your own data (two tables that share a key and need combining side to side) and one append case (two same-shaped tables that need stacking). Naming them cements the distinction.
• Spot one NULL in your data and classify it: correct absence, business logic (like cancel_date), or genuinely missing. Decide keep, flag, or fix.
• Bring to b3: your profiled table. We will decide whether it is a fact or a dimension.

The "Prepare the data" domain is 25-30% of the PL-300 exam - the largest slice alongside modeling. This page covers its concepts tool-agnostically; the Power Query and Tableau Prep click-paths stay with the vendors.