Profile before you model

Make profiling a routine

Before you build on a table, ask six questions: row count and grain, key uniqueness, NULL rates, orphan keys, date coverage, and categorical consistency. The project includes one reusable query for each under queries/profiling/.

Do not clean the findings yet. First quantify them. orders has 1,212 rows for 1,200 order IDs, while customers has 510 source rows for 500 customers. A duplicate is only useful information when you know whether it changes a number and whether the rows are exact repeats or conflicting versions.

Your task

Run and adapt all six profiling queries. Report: orders rows and distinct IDs; duplicated customer IDs; missing unit_cost rows; orphan product rows; calendar days with no orders; and the country-value variants. For each, say what it would do to a revenue number.

Check your understanding

• You run SELECT COUNT(*), COUNT(DISTINCT order_id) FROM orders and the two numbers differ. What have you not yet learned?
• Why does an anti-join belong in a profiling routine when the foreign key has no constraint on it?
• GROUP BY country ORDER BY COUNT(*) DESC returns France at the top. Why might that be the wrong answer?

Do it with your agent

Say next lesson, complete the six checks, then say review my work. Keep the project unchanged: this lesson establishes evidence; cleaning begins later.