Skip to content
/Chapter 18 · Modeling and Data Quality
Lesson 18.1·garden_shop
Lesson 18.1

Profile a Table First

A profile is a single query that tells you how big a table is, where the nulls are, and what range it covers.

The fastest way to write a wrong report is to assume what a table contains.Profiling is the habit of asking first: how many rows, how many of them are missing the column you are about to filter on, and what range of dates you are actually looking at.

Most of a profile comes from one detail about COUNT. count(*) counts rows, but count(column) counts only the rows where that column is not null. Subtract one from the other and you have a null count without writing a second query.

The shape of a profile
select count(*) as rows,
       count(reorder_level) as non_null,
       count(*) - count(reorder_level) as missing,
       count(distinct category_id) as categories
from products

Ranges and repetition

Two more numbers finish the picture. count(distinct column) tells you whether a column is an identifier (distinct count equals the row count) or a category (a handful of repeated values). min and max on a date column tell you which period the table covers, which is how you catch a load that stopped three weeks ago.

When a distinct count comes back small, follow it with aGROUP BY to see the values themselves. Four order statuses is a short enough list to read in full.

Read a short category list in full
select status,
       count(*) as orders
from orders
group by status
order by orders desc, status
Schema · Garden ShopTable · orders6 columns · 24 rows
Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows
order_id intcustomer_id intorder_date dateshipped_date datestatus textcoupon_code text
Your task

Profile the orders table in one query. Returnrows, shipped (orders with a shipped date),missing_shipped_date, customers (distinct customers),statuses (distinct statuses),first_order, and last_order. The result is a single row.

SQL Workbench
query.sqlgarden_shop · SQL engine loading
⌘↵ to run
·
Expected answer
  • Columns: rows, shipped, missing_shipped_date, customers, statuses, first_order, last_order.
  • Rows: 1. A profile summarises the whole table into a single row.
  • 24 orders from 16 customers, 7 of them with no shipped date yet.

Keep the profile query. Run it before a report and again after a load, and the two results together tell you whether anything moved that you did not expect. The next lesson turns that instinct into checks that give a pass-or-fail answer.

Next · 18.2 Check data quality with SQL →

Related

Lesson
Check Data Quality with SQL

Write checks that return nothing when the data is clean.

Lesson
Write a Rerunnable Script

Pick the right guard so a setup script survives a second run.

Reference
SQL DROP Statements

Remove tables, views, schemas, and more without breakage.

Reference
SQL Table Constraints

NOT NULL, DEFAULT, CHECK, UNIQUE, and keys.