Profile before you polish

Someone dumps a CSV on your desk (okay, in Slack) and says “can you clean this before the exec review?” You open it, see a few blanks, and your fingers hover over find-and-replace. That instinct is generous. It is also how you polish a surface while the foundation is a sinkhole.

Profiling is the boring superpower: counts, nulls, min and max, weird categories, duplicate rates, and a quick smell test of grain. You do it before you mutate so every cleaning step has a reason. If Part 1 gave you dimension names, Part 2 gives you the flashlight.

This is Part 2 of Data quality for people who ship numbers. We stay hands-on: SQL you can run in a warehouse notebook, Python you can run in pandas, and a mindset that refuses to clean blind.

What you’ll learn

• What a lightweight profile includes (and what it does not)
• SQL snippets for row counts, null rates, ranges, and category tails
• Python (pandas) equivalents you can reuse on any messy extract
• How to map profile findings back to the five dimensions from Part 1
• A worked example on a fake orders table that still feels painfully real

Why polishing first fails

Cleaning without a profile is like repainting a wall with a water stain. You might make the screenshot prettier. You have not learned whether the leak is a null policy, a broken join key, a late pipeline, or five spellings of the same region.

Profiling answers: What is here? How much is missing? What are the extremes? Which categories dominate? Which look like typos? How many rows claim to be unique and are not? You are not building a 40-page data catalog. You are earning the right to change values.

This pairs with habits from From spreadsheets to real data (look before you reshape) and from Python for analytics (inspect frames before groupby glory). Quality is the same discipline with trust as the output.

The lightweight profile checklist

Example profile output: null rates by column:

For any table that will feed a decision, capture at least:

• Shape: row count, column list, primary key candidate
• Completeness: null or blank rate per important column
• Uniqueness: distinct counts vs rows for key columns
• Ranges: min, max, and a few quantiles for numbers and dates
• Categories: top values and long-tail oddballs for strings
• Freshness: max timestamp and lag vs “now” or report date
• Cross-field sanity: impossible combos (shipped with null ship date, negative prices on non-refund rows)

Write the findings in plain language. “3.2% of orders missing customer_id” is a ticket. “Data is messy” is a mood.

SQL: profile without mutating

Assume a raw table orders_raw. Dialects differ slightly (FILTER, IFF, IFNULL). The ideas transfer. If you are building SQL fluency, the SQL series covers the query building blocks; here we assemble them into a quality pass.

Shape, keys, and nulls

SELECT
  COUNT(*) AS row_count,
  COUNT(DISTINCT order_id) AS distinct_order_id,
  COUNT(*) - COUNT(DISTINCT order_id) AS duplicate_order_id_rows,
  COUNT(*) - COUNT(customer_id) AS null_customer_id,
  COUNT(*) - COUNT(order_ts) AS null_order_ts,
  ROUND(100.0 * (COUNT(*) - COUNT(customer_id)) / COUNT(*), 2) AS pct_null_customer_id
FROM orders_raw;

Example output:

If duplicate_order_id_rows is positive, uniqueness is already on the table before you discuss “cleaning categories.”

Numeric and date ranges

SELECT
  MIN(amount) AS min_amount,
  MAX(amount) AS max_amount,
  AVG(amount) AS avg_amount,
  MIN(order_ts) AS min_order_ts,
  MAX(order_ts) AS max_order_ts
FROM orders_raw;

Example output:

Max amount of 9,999,999.99 might be a sentinel. Min order date in 1970 might be a Unix epoch accident. Min amount negative might be refunds (fine) or a sign error (not fine). Profiling surfaces the question; business rules answer it.

Category tails

SELECT
  status,
  COUNT(*) AS n,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct
FROM orders_raw
GROUP BY status
ORDER BY n DESC;

Example output:

Run the same pattern for region, channel, currency, or any field people group by in dashboards. Look for near-duplicates (US, usa, United States), blank strings, and catch-all buckets like Other that swallowed half the world.

Blank strings are not nulls

SELECT
  COUNT(*) FILTER (WHERE customer_email IS NULL) AS null_email,
  COUNT(*) FILTER (WHERE customer_email = '') AS empty_email,
  COUNT(*) FILTER (WHERE TRIM(customer_email) = '') AS blankish_email
FROM orders_raw;

Many tools treat empty string as “present.” Your completeness metric should decide whether empty counts as missing. Profile both so you are not surprised later.

Python: the same flashlight in pandas

When the extract is a file or you already live in notebooks, pandas is fast for a first pass. Official docs for DataFrame.describe, missing data, and value counts are worth bookmarking (see Sources).

import pandas as pd

df = pd.read_csv("orders_raw.csv")

# Shape and dtypes
print(df.shape)
print(df.dtypes)

# Completeness: nulls and empty strings
nulls = df.isna().mean().sort_values(ascending=False)
empty_str = (df.select_dtypes("object").apply(lambda s: s.fillna("").str.strip().eq(""))).mean()
print(nulls.head(15))
print(empty_str.sort_values(ascending=False).head(15))

# Uniqueness on claimed keys
print(df["order_id"].duplicated().sum())
print(df.duplicated().sum())

# Ranges
print(df["amount"].describe())
print(df["order_ts"].min(), df["order_ts"].max())

# Categories: head and suspicious tail
print(df["status"].value_counts(dropna=False).head(20))
print(df["region"].value_counts(dropna=False).tail(20))

Example:

A compact helper many analysts keep around:

def quick_profile(df, key_cols=None, cat_cols=None):
    key_cols = key_cols or []
    cat_cols = cat_cols or df.select_dtypes("object").columns.tolist()
    out = {
        "rows": len(df),
        "cols": df.shape[1],
        "null_pct": df.isna().mean().to_dict(),
        "dup_full_rows": int(df.duplicated().sum()),
    }
    for k in key_cols:
        out[f"dup_{k}"] = int(df.duplicated(subset=[k]).sum())
        out[f"nunique_{k}"] = int(df[k].nunique(dropna=False))
    for c in cat_cols:
        out[f"top_{c}"] = df[c].value_counts(dropna=False).head(5).to_dict()
    return out

profile = quick_profile(df, key_cols=["order_id"], cat_cols=["status", "region"])
print(profile)

Paste the printout into the ticket. Future you will thank present you.

Worked example: orders that look “fine” until they don’t

Here is a miniature dataset. Pretend leadership wants average order value by region for last week.

A blind cleaner might lowercase statuses, fill region with “Unknown,” drop null customer_ids, and delete the duplicate. A profiler slows down:

Now the cleaning plan has a spine. Example sequence after profiling:

1. Confirm grain: one paid order per order_id for AOV, refunds separate.
2. Resolve duplicate 5001 at the source or with a documented dedupe rule.
3. Standardize status case; map regions via a mapping table (Part 4).
4. Define amount rules: refunds as negative vs separate rows; cap or quarantine 999999 after business confirm.
5. Exclude or repair epoch dates with an explicit filter, not silent deletion in a one-off notebook.

That is polish with a conscience. The numbers might still move. You can explain why.

Cross-field checks beat single-column vanity

Single-column null rates are necessary and not sufficient. Add a few multi-column rules that match your domain:

SELECT
  COUNT(*) FILTER (WHERE status = 'paid' AND amount <= 0) AS paid_nonpositive,
  COUNT(*) FILTER (WHERE status = 'refunded' AND amount > 0) AS refund_positive,
  COUNT(*) FILTER (WHERE order_ts::date < DATE '2000-01-01') AS ancient_dates,
  COUNT(*) FILTER (WHERE customer_id IS NULL AND amount > 100) AS high_value_orphan
FROM orders_raw;

In pandas:

rules = {
    "paid_nonpositive": ((df["status"].str.lower() == "paid") & (df["amount"] <= 0)).sum(),
    "ancient_dates": (pd.to_datetime(df["order_ts"], errors="coerce") < "2000-01-01").sum(),
    "high_value_orphan": (df["customer_id"].isna() & (df["amount"] > 100)).sum(),
}
print(rules)

Each non-zero rule is a story, not a silent dropna.

From profile to ticket language

Translate findings into the dimensions from Part 1 so engineers and stakeholders share a vocabulary:

• “12% null customer_id on paid orders” → completeness on a required field for customer-level metrics
• “Max amount is a repeated 999999” → investigate accuracy / sentinel
• “Region has 47 variants for ~8 real markets” → consistency of categories
• “Max event time is 36 hours behind report time” → timeliness
• “1.8% duplicate primary keys” → uniqueness

This is also how you protect yourself. You are not “blocking on perfection.” You are documenting fitness for a named use, the same spirit as good-enough analytics in Analytics foundations.

Common mistakes

• Profiling only the columns you already like. The weird column is often the landmine.
• Using mean alone. Means hide spikes and sentinels. Keep min, max, and a high percentile.
• Trusting describe() on messy types. If amount is stored as text, cast carefully after you see bad tokens.
• Cleaning in the same cell as profiling. Separate “observe” and “transform” steps so you can undo.
• Skipping blank-string checks. Empty is not null in most SQL engines or pandas object columns.
• One-off profiles you never save. Stick the SQL or notebook snippet in the repo or ticket.

How to practice this week

• Pick one production table or CSV you ship from. Run the shape, null, range, and category queries above.
• Write five bullets: biggest completeness risk, biggest uniqueness risk, weirdest category, weirdest range, freshness lag.
• Add one cross-field rule that would embarrass you if leadership found it first.
• Refuse one “quick clean” request until you paste a profile summary. Notice how the request changes.
• If SQL or pandas feel rusty, use Learn to jump into the path you need, then come back to Part 3 on deduping.

Quick recap

• Profile before you polish. Observation before mutation.
• Counts, nulls, ranges, categories, freshness, and cross-field rules are enough to start.
• SQL and Python both work; use the tool closest to the data.
• Map findings to dimensions so fixes match failures.
• Next: dedupe without destroying history when uniqueness fails the profile.

Sources

• pandas documentation, “Working with missing data”: https://pandas.pydata.org/docs/user_guide/missing_data.html
• pandas API reference, DataFrame.describe: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.describe.html
• pandas API reference, Series.value_counts: https://pandas.pydata.org/docs/reference/api/pandas.Series.value_counts.html
• PostgreSQL aggregate documentation (COUNT, FILTER patterns): https://www.postgresql.org/docs/current/functions-aggregate.html
• IBM data quality dimensions (context for mapping profile metrics to dimensions): https://www.ibm.com/docs/en/ws-and-kc?topic=quality-data-dimensions

Merch on Etsy

Open store →

You're On Mute White Ceramic Coffee Mug, Data Science Mug, Data Mug, Analytics Mug, Statistics Mug, Programming Mug, Gift Mug, Coffee Mug View on Etsy → Good Cheap Fast White Ceramic Coffee Mug, Data Science Mug, Data Mug, Analytics Mug, Statistics Mug, Programming Mug, Gift Mug, Coffee Mug View on Etsy → Naive Bayes Mouse Pad, Data Science Mouse Pad, Analytics Mouse Pad, Bayesian Mouse Pad View on Etsy → You're On Mute White Ceramic Coffee Mug, Data Science Mug, Data Mug, Analytics Mug, Statistics Mug, Programming Mug, Gift Mug, Coffee Mug View on Etsy →