Rapid Dataset Profiling and Data-Quality Report with an AI Assistant

Tier: Foundation · Workflow area: Data understanding & profiling

When to use

• •
  A new dataset lands (client export, warehouse table, vendor feed) and you must assess fitness before modeling or analysis.
• •
  You are inheriting a pipeline and need to document what the data actually looks like versus what the schema claims.
• •
  A stakeholder asks "can we use this data?" and needs an evidence-backed answer, not a feeling.

Skip AI for a dataset under ~10 columns you already know well — a manual df.info() pass is faster than reviewing generated code.

Prerequisites

• •
  Python environment with pandas installed; optionally ydata-profiling for a baseline HTML report.
• •
  An AI coding assistant (Claude Code, Cursor, Jupyter AI, or Databricks Assistant) with access to your notebook or files.
• •
  The dataset locally or via a query you can run. You need to execute the generated code — never accept profiling claims the assistant produces without running anything.

The workflow

1. 1.

   Ground the assistant in real schema, not guesses. Run df.dtypes.to_string() and df.head(3).to_string() yourself and paste the output. Assistants hallucinate column names when given only a description.

2. 2.

   Request a profiling plan before code. Plans are cheaper to correct than code:

   Copy

   Here is the schema and head of a pandas DataFrame `df` (~2.1M rows, order-level
   e-commerce data). Before writing any code, list the data-quality checks you
   would run, grouped by: completeness, uniqueness, validity (ranges/formats),
   consistency (cross-column rules), and timeliness. Flag which checks are
   most likely to reveal problems for a churn-modeling use case.
   

3. 3.

   Generate the profiling script. Ask for one runnable cell:

   Copy

   Write a single pandas script implementing those checks. Requirements:
   - Output a tidy DataFrame `quality_report` with columns: check_name, column,
     result, threshold, pass_fail.
   - Null share per column; duplicate-key rate on order_id; out-of-range checks
     for amount (>0) and dates (within 2019-2026); category cardinality;
     cross-field rule: shipped_date >= order_date.
   - No plotting, no sampling — full dataset, chunked if memory requires.
   

4. 4.

   Run it and interrogate anomalies. Execute the script yourself. For each failed check, paste the failing rows back and ask for hypotheses: Here are 20 rows where shipped_date < order_date. Give three plausible generating mechanisms and a follow-up query for each. Test-order artifacts, timezone bugs, and backfills each imply different fixes.

5. 5.

   Cross-check against a deterministic profiler. Run ydata_profiling.ProfileReport(df, minimal=True) (or df.describe(include="all")) and diff the headline numbers — row count, null rates, distinct counts — against the assistant's report. Discrepancies mean the generated code has a bug (common: dropping NaN before counting them).

6. 6.

   Draft the report, numbers pinned. Ask the assistant to write a one-page quality summary using only the quality_report values you paste in, with a "fit for churn modeling: yes/no/with caveats" verdict you edit and own.

Tool variants

• Agentic CLI/IDE (Claude Code, Cursor): point it at the CSV/parquet path and let it run the profiling itself; your job shifts to auditing the executed code and outputs it shows you.
• Warehouse assistant (Databricks/Snowflake/BigQuery): same workflow in SQL — null shares via COUNT(*) - COUNT(col), duplicate keys via GROUP BY ... HAVING COUNT(*) > 1. Ask for one CTE per check writing into a results table.
• Chat-only assistant: paste schema plus df.describe() output; you copy code into Jupyter manually. Slower loop, identical audit discipline.

Verification

• •
  Row count, null rates, and distinct counts in the final report match a deterministic profiler or your own one-line pandas checks.
• •
  Every pass_fail threshold was set by you or explicitly agreed, not silently invented by the model.
• •
  At least one anomaly hypothesis was confirmed or refuted with a follow-up query, not just narrated.
• •
  The report states dataset version/extract date so the profile is reproducible.

Pitfalls

• Profiling a sample without saying so. Assistants love df.sample(10000) for speed; rare-category and duplicate-rate findings from samples are wrong. Demand full-data or chunked computation.
• Plausible-but-wrong pandas. Watch for df.dropna() before counting nulls, duplicated() without subset=, and string comparisons on unparsed dates. These pass review at a glance and corrupt every downstream number.
• Accepting invented business rules. "amount should be under 10,000" is a model guess unless a domain owner confirmed it. Label thresholds as confirmed vs assumed.
• One giant prompt. Schema → plan → code → interrogation beats "profile this dataset" in one shot, which yields shallow boilerplate.

Restricted-environment variant

• •
  If data cannot leave your machine, share only schema, dtypes, and aggregate statistics with a hosted assistant — never raw rows. Generated code runs locally against the real data.
• •
  For stricter settings, run a local code model via Ollama for generation, or write checks from the assistant's plan (step 2) yourself — the check list contains no data at all.
• •
  Replace real column names with semantically faithful aliases (cust_email → contact_field) if even schema is sensitive; keep a local mapping.

Success criteria

• •
  A written quality report with pass/fail per check, produced in under an hour for a dataset new to you.
• •
  Every number in the report is traceable to code you executed.
• •
  At least one issue was found that a plain df.describe() would have missed (cross-field violation, duplicate keys, silent type coercion).
• •
  A teammate can rerun the profiling script and reproduce the report exactly.