Profile Source Data into Requirements Before It Breaks Your Project

Workflow area: Data & Evidence · Tier: Core · Hands-on time: ~35 minutes

Every migration and integration project carries the same buried risk: requirements written against what stakeholders believe the data looks like, not what it actually contains. The system owner swears customer IDs are unique; the extract says 3% are duplicated. Profiling used to demand a data engineer and a week; with an LLM writing the pandas or Power Query for you, it demands an afternoon and the discipline to turn findings into numbered requirements rather than a shrug.

When to use — and when not to

• Use it at the start of any migration, integration, or reporting projectbefore requirements are baselined, so data reality shapes scope instead of ambushing it.
• Use it to test stakeholder claims about dataevery "that field is mandatory" and "those codes are standardized" is a hypothesis your profile confirms or demolishes with counts.
• Use it when a vendor or downstream team needs a data contractprofiling output is the evidence base for field-level quality clauses.
• Skip it on production databases directlyprofile governed extracts or read-replicas; an analyst's exploratory script has no business touching live systems.
• Skip the AI route when the extract is too sensitive to moveif the data cannot enter an approved tool even pseudonymized, have the AI write the script and run it inside the governed environment instead of uploading rows.
• Skip conclusions from a stale or partial extracta profile of last year's snapshot describes last year; get extraction date and completeness in writing first.

The recipe

1. 1.

   Get the extract and its paperwork. Request the source extract plus three facts in writing: extraction date, row count at source, and any filters applied. Verify the file's row count matches. Without this, every finding is contestable.

2. 2.

   Generate the profiling script. Attach the file (or just its column headers and ten pseudonymized sample rows, if the data must stay put) to Claude or ChatGPT:

   Copy

   You are helping a business analyst profile a source extract
   ahead of a migration. File: [e.g. "vendors.csv, 48,112 rows,
   expected one row per vendor"].
   
   Write a Python/pandas script that reports, per column:
   1. Null and blank-string counts and percentages.
   2. Distinct value count; top 10 values with frequencies for
      any column under 50 distinct values.
   3. Format consistency for dates, phones, postal codes, and
      IDs — list every distinct pattern found with counts.
   4. Duplicate analysis on [KEY COLUMN(S)]: exact duplicates
      and same-key-different-values conflicts, with examples.
   5. Referential check: values in [FK COLUMN] with no match in
      [REFERENCE FILE/LIST].
   6. Range checks: dates outside [PLAUSIBLE RANGE], negative
      amounts, zero-length strings.
   Output a findings table: column, check, result, severity
   (INFO/WARN/CRITICAL). Flag anything that would break a
   migration mapping as CRITICAL. Do not modify the data.
   

3. 3.

   Run it and read the findings like an analyst, not an auditor. Typical output:

   Copy

   FINDINGS — vendors.csv (48,112 rows, extracted 2026-07-31)
   | Column      | Check        | Result                  | Sev      |
   | vendor_id   | duplicates   | 1,447 ids on 2+ rows;   | CRITICAL |
   |             |              | 212 with conflicting    |          |
   |             |              | bank details            |          |
   | tax_id      | nulls        | 9.4% null (4,522 rows)  | CRITICAL |
   | country     | formats      | "DE", "Germany",        | WARN     |
   |             |              | "GERMANY" all present   |          |
   | created_on  | range        | 38 dates in 1900-01-01  | WARN     |
   | pay_terms   | distinct     | 61 values incl. "NET30",|          |
   |             |              | "Net 30", "30 days"     | WARN     |
   

   For each CRITICAL, pull five example rows and look at them. Duplicated vendor IDs with conflicting bank details is not a data note — it is a fraud-control conversation.

4. 4.

   Take findings to the data owner before writing anything. Every anomaly has one of three explanations: known-and-intentional (the 1900 dates are a legacy "unknown" convention), known-and-tolerated (duplicates from an old merger, cleanup never funded), or news. The owner's answers decide which findings become requirements and which become documented conventions. Log who said what.

5. 5.

   Convert findings into numbered requirements. One finding, one testable requirement, with the profile as evidence:

   Copy

   DQ-04: Prior to migration, the 1,447 duplicate vendor_id
   groups shall be resolved to a single surviving record per
   vendor, per the dedup rules in Appendix C. Verification:
   post-migration profile shows zero duplicate vendor_ids.
   Evidence: profile 2026-07-31, finding F-01.
   
   DQ-07: The migration mapping shall normalize country to
   ISO 3166-1 alpha-2; unmappable values route to the
   exception queue, not to a default. Evidence: F-03.
   

   Ask the AI to draft these from the findings table — then edit severity, ownership, and verification method yourself, because those are judgment calls.

6. 6.

   Version the profile and schedule the re-run. Save script, findings, and owner responses with the extract date. Re-run on the cutover extract — the delta between profiles is your late-breaking risk report.

Make it yours

• Power Query + Copilot for the Excel-native team: column-quality and distribution views cover checks 1–3 without code; use the AI for the duplicate and referential logic Power Query makes tedious.
• Claude/ChatGPT data analysis for one-shot profiling: upload the extract and have it run the script in-session — fastest path when the data is cleared for the tool.
• Script-only mode for restricted data: the AI writes the script from headers and fake sample rows; a data engineer runs it inside the governed environment and returns only the findings table. You never touch the rows; the discipline survives.
• Data-catalog integration at scale: file findings against the catalog entry (Collibra/Purview-class) so the next project inherits your profile instead of rediscovering the duplicates.

Quality check

• Extraction provenance is documenteddate, source row count, filters — and the file reconciles to it.
• Every stakeholder data claim was testedeach "always populated" and "unique key" has a measured result beside it.
• CRITICALs were eyeballedyou looked at real example rows, not just counts, before escalating.
• The data owner adjudicatedfindings are classified intentional / tolerated / news, with names and dates.
• Findings became testable requirementsnumbered, with verification methods and profile evidence links, not a prose lament about data quality.
• The profile is versioned and re-runnablescript plus findings saved; a cutover re-run is in the plan.