Data Cleaning Blueprint for Messy Excel Exports
Produce a field-by-field standardization plan, typing rules, joins, and QA checks to turn raw exports into analysis-ready tables.
Prompt Overview
Tips For You
- Include 50+ sample rows for realistic edge cases. - Lock a single timezone standard early. - Version the data dictionary and track changes. - Add an exceptions sheet to unblock daily runs.
From Operations TeamNexusAi TechnologyProblem It Solves
Inconsistent headers, types, and IDs cause broken joins and unreliable reports. Teams need a practical, reusable cleaning spec.
Field-level rules
Explicit typing, formats, and allowed values.
Join design
Reliable cross-file stitching with keys and fallbacks.
Validation suite
Unit tests and thresholds to catch regressions.
Exception handling
Quarantine bad rows without blocking the run.
AI Prompt Instructions
Act as a data quality lead creating a cleaning blueprint for recurring Excel exports.
Why this task matters:
Poorly standardized data breaks joins, KPIs, and trust. A repeatable blueprint cuts rework and stabilizes reporting.
Important boundaries:
- No vague advice; provide exact field rules, acceptable values, and fallback logic.
- Assume low-code or Python ETL can implement the plan.
User inputs:
- Sample export headers, 50–200 sample rows if possible.
- Known business keys, lookup tables, and target KPIs.
- Expected refresh frequency and SLA.
Objectives:
1) Normalize headers, types, and date formats.
2) Define dedupe, trim, null handling, and ID stitching.
3) Specify join logic across files (primary keys, composite keys, fuzzy rules if needed).
4) Provide validation checks and exception reporting.
Analysis workflow:
1) Create a data dictionary: Field, Source, Type (target), Allowed Values, Transform Rules, Examples.
2) Define canonical keys and surrogate keys when needed.
3) Describe dedupe rules (e.g., keep latest by UpdatedAt, tie-breaker by Status).
4) Outline unit tests: row counts, type checks, referential integrity, metric reconciliation.
Required output format:
- Data Dictionary (table)
- Transformation Rules (step list)
- Join Plan (diagram text)
- Validation Suite (checklist)
- Exceptions Handling (workflow)
Quality controls:
- Flag ambiguous fields and propose a naming standard.
- Provide at least 5 test cases with expected results.
Verification checklist:
- Are keys stable across refreshes?
- Are date/timezones consistent?
- Are lookups complete and versioned?
Final instruction:
Ask for any missing samples, then produce the full cleaning blueprint ready for implementation.
Expected Outcome
Deliverables: - Data Dictionary: CustomerID (string → UUID), OrderDate (date, ISO-8601), Amount (decimal, 2dp) - Transform Rules: trim text, uppercase country codes, coerce dates, zero-decimals to null - Join Plan: Orders.CustomerID → Customers.CustomerID; fallback via Email normalized - Validation: row count within ±5%, duplicate OrderID < 0.1%, referential integrity 100%
Implementation Journey
Draft the blueprint with ChatGPT
Paste sample headers and 50–200 rows into ChatGPT with this prompt. Request a data dictionary, transforms, join plan, and validation suite. Expect a structured blueprint.
20-30 minProbe edge cases with Gemini
Give Gemini the same samples and ask for risky fields, ambiguous keys, and counterexamples. Compare its edge cases with the draft and reconcile differences in your spreadsheet.
15-20 minFinalize implementation notes
Add final typing rules and tests to an Excel or Sheets tab. Mark columns that need coercion or lookups. Hand off the blueprint to engineering for ETL scripting.
10-15 min