Messy Dataset Audit Prompt

Audit the supplied dataset materials and determine whether they are fit for the intended analysis.

Audit inputs
- Audit goal: [Audit goal]
- Dataset materials: [Dataset materials]
- Data dictionary and schema: [Data dictionary and schema]
- Join and key definitions: [Join and key definitions]
- Constraints and policies: [Constraints and policies]
- Acceptance criteria: [Acceptance criteria]

Input requirements
The blocking minimum is readable dataset content or trustworthy profiling evidence, the intended analytical use, and enough information to identify the unit of observation or expected grain. For a multi-table audit, table relationships and expected join cardinalities are also blocking. If a blocking input is absent, ask only the questions needed to obtain it and provide an audit plan rather than a completed audit.

Useful but non-blocking context includes data dictionaries, source-system descriptions, lineage notes, prior quality reports, transformation logic, expected row counts, valid-value lists, refresh schedules, sampling rules, and known incidents. Continue with bounded analysis when these are missing, but record the resulting limitations and do not infer undocumented business rules.

ChatGPT operating boundaries
- Inspect only materials included in the conversation or otherwise accessible through the active ChatGPT interface. State which files, sheets, tables, fields, profiles, or excerpts were actually readable.
- If file analysis or code execution is available, use it only for read-only profiling and retain the executed commands, formulas, query logic, outputs, sample sizes, and errors as evidence. If it is unavailable, provide reproducible SQL, Python, spreadsheet formulas, or checks for the user to run; label their results as pending.
- Do not claim to have opened a file, scanned all rows, executed code, measured a rate, or verified a condition unless that action occurred and supporting output exists.
- Do not modify, delete, overwrite, deduplicate, impute, mask, publish, upload, or approve data. Treat every remediation as proposed until an authorized person executes and validates it.
- Minimize exposure of personal, confidential, regulated, credential, or secret data. Do not reproduce sensitive values unnecessarily. Use field names, redacted examples, aggregates, or synthetic illustrations. Stop and request a safer extract if credentials, authentication tokens, private keys, or unnecessarily exposed highly sensitive records appear.
- Preserve the source files as immutable. Recommend versioned outputs, backups, row-count reconciliation, exception retention, and rollback procedures before any future correction.

Evidence rules
Classify each material statement as one of: supplied fact, direct observation, calculated result, assumption, hypothesis, unknown, conflict, or pending test. Cite the supporting file, table, sheet, column, query, profile, or user statement whenever available. Report the population examined, sample method, denominator, null treatment, and relevant thresholds for calculated rates. Never generalize a sample result to the full dataset without stating the limitation.

When sources conflict, preserve both claims, identify the conflict, and explain what evidence would resolve it. Do not treat blanks, zeroes, sentinel values, absent rows, duplicate-looking entities, or extreme values as errors until their business meaning is established.

Audit procedure
1. Inventory the evidence. List every supplied artifact, whether it was readable, its apparent format, row and column counts when observed, date coverage, source, refresh time, and material access limitations. Detect truncated exports, parsing failures, encoding issues, malformed rows, hidden sheets, inconsistent delimiters, and partial samples where possible.
2. Establish scope and grain. State the intended decision or analysis, the expected unit of observation, candidate primary keys, time period, population, exclusions, and tables in scope. Compare stated grain with observed key uniqueness. Flag mixed grains, repeated snapshots, aggregation mismatches, and ambiguous entity definitions.
3. Reconcile structure. Compare observed fields and types with the supplied schema or dictionary. Check missing or unexpected columns, duplicate column names, type drift, mixed types, precision loss, units, formats, encodings, timezone handling, date parsing, impossible dates, and schema variation across partitions or files.
4. Profile completeness. For each important field, calculate or request null counts and rates, blank-string rates, sentinel-value rates, and completeness by relevant cohort, source, or time period. Distinguish expected, conditionally applicable, structurally missing, and unexplained missingness. Flag sudden changes and missingness patterns that could bias analysis.
5. Test identity and duplication. Measure candidate-key uniqueness, exact duplicate rows, duplicate business keys, and conflicting records sharing a key. Separate legitimate repeated events or snapshots from probable duplicates. Specify survivorship or deduplication rules only when supported by business evidence.
6. Test validity and consistency. Check allowed values, ranges, formats, units, cross-field rules, chronological order, mutually dependent fields, totals versus components, and impossible combinations. Normalize values only in proposed tests; preserve raw values and report how normalization affects counts.
7. Examine distributions and outliers. Summarize numeric and temporal distributions with appropriate counts, quantiles, spread, and cohort comparisons. Use transparent methods such as domain bounds, interquartile ranges, median absolute deviation, or temporal change checks. Treat statistical outliers as review candidates, not automatic errors, and distinguish genuine rare events from likely entry, unit, or parsing defects.
8. Audit categories and text. Identify inconsistent case, whitespace, spelling, encoding, label aliases, excessive cardinality, placeholder text, and category drift. Quantify each issue and avoid merging categories without an approved mapping.
9. Audit relationships and joins. For each proposed relationship, state expected cardinality and test null keys, key type or format mismatches, orphan rates, unmatched rows on both sides, duplicate dimension keys, many-to-many expansion, and pre- versus post-join row counts. Calculate join coverage and fan-out where evidence permits. Treat unexplained row multiplication or loss as a readiness blocker.
10. Assess time and refresh integrity. Check gaps, overlaps, duplicate periods, stale extracts, future timestamps, timezone inconsistencies, late-arriving data, uneven reporting intervals, and changes around pipeline or policy transitions.
11. Assess analytical and operational risk. Identify target leakage, post-outcome fields, selection bias, survivorship bias, nonrepresentative samples, class imbalance, unstable definitions, inconsistent historical logic, personal or sensitive fields, and data retention concerns when relevant to the stated goal. Do not make legal or compliance determinations; identify matters requiring qualified review.
12. Prioritize findings. Assign severity using explicit impact and likelihood: critical for unsafe use or a result-invalidating defect, high for a likely material distortion, medium for a bounded quality problem, and low for a minor or cosmetic issue. Distinguish confirmed defects from suspected defects and quantify affected records where possible.
13. Design remediation without applying it. For each finding, propose an owner, correction rule, exception policy, dependencies, approval point, validation query, rollback or recovery approach, and expected downstream effect. Discuss trade-offs such as dropping versus retaining records, imputation versus explicit missingness, strict rejection versus quarantine, and source correction versus downstream patching.
14. Verify readiness. Evaluate every supplied acceptance criterion and the default checks below. Record expected condition, actual observation, evidence, and status as pass, fail, blocked, or not applicable. A check passes only when observed evidence supports it.

Default acceptance checks
- Scope, grain, population, and time window are unambiguous.
- Required fields exist and conform to documented types, units, formats, and valid domains.
- Primary or business keys meet the agreed uniqueness rule.
- Missingness is within agreed thresholds and unexplained cohort differences are resolved or accepted.
- Duplicate, validity, consistency, and outlier exceptions are quantified and dispositioned.
- Every required join meets its cardinality, coverage, fan-out, and row-reconciliation expectations.
- Temporal coverage and refresh recency meet the intended use.
- Leakage, bias, privacy, and sensitive-data concerns have an owner and an approved disposition where applicable.
- Critical and high-severity defects are resolved, formally accepted by an authorized owner, or explicitly block use.
- All reported calculations are reproducible from recorded logic and evidence.

Required deliverable
1. Audit scope and evidence inventory: intended use, population, grain, period, artifacts inspected, access status, execution capability, and limitations.
2. Dataset structure map: one row per table or file with source, grain, observed size, candidate key, time coverage, schema status, and relationships.
3. Evidence ledger: evidence ID, classification, source location, method or query, population or sample, observation, uncertainty, and reproducibility status.
4. Field-quality profile: field, role, observed type, expected type, null and blank counts or rates, distinctness, validity rule, notable distribution issue, and status. Use unavailable where no measurement exists.
5. Duplicate and key assessment: key definition, expected uniqueness, observed duplicate count or pending test, duplicate class, likely cause, and impact.
6. Join-integrity matrix: left and right datasets, join keys, expected cardinality, unmatched counts and rates on each side, fan-out, row counts before and after, status, and evidence.
7. Findings register: finding ID, dimension, affected artifact and fields, evidence ID, evidence classification, affected count and denominator, severity, analytical impact, confidence, and status.
8. Remediation plan: finding ID, proposed correction, source-versus-downstream location, owner, dependencies, approval required, exception handling, validation check, rollback or recovery control, and priority. Do not represent proposals as completed changes.
9. Verification matrix: criterion, threshold or expected result, actual observed result, evidence ID, status, unresolved gap, and person or team responsible for acceptance.
10. Analysis-readiness decision: choose ready, ready with documented limitations, not ready, or undetermined. Explain the decision, permitted uses, prohibited or unsafe uses, blockers, accepted exceptions, and residual risks. Use undetermined when evidence is insufficient.
11. Handoff: list the smallest safe next actions, who must authorize consequential changes, tests still to run, evidence still needed, and the conditions for reassessment.

Completion language
Keep proposed, pending, executed, observed, verified, accepted, and blocked states distinct. A remediation is not fixed until an authorized change has occurred and its validation evidence passes. A dataset is not approved merely because this audit recommends use; final acceptance belongs to the designated data owner or accountable reviewer.