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) helps you understand cardinality. A distinct count near the row count can suggest an identifier-like column, while a small count suggests a category. Because COUNT(DISTINCT ...) ignores nulls and doesn't enforce a constraint, confirm nullability and uniqueness separately before treating a column as a key. 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 a GROUP 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 Shop Table · orders 6 columns · 24 rows

Table · orders

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

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Your task

Profile the orders table in one query. Return rows, 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.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: rows, shipped, missing_shipped_date, customers, statuses, first_order, last_order.
Rows: 1. A profile summarizes 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 didn't expect. The next lesson turns that instinct into checks that give a pass-or-fail answer.

← Back to all lessons

Next · 18.2 Check data quality with SQL →

Reference

SQL DROP Statements

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

Reference

SQL Table Constraints

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

Reference

SQL Schemas

Group objects into namespaces like staging and reporting.

Lesson