An inherited database, a vendor extract, a schema someone swears is "self-explanatory" — querying before profiling produces confident answers the data cannot support. This playbook runs a fast DuckDB profiling pass plus an AI interpretation: shapes, nulls, cardinalities, suspicious columns, and the honest list of what this data cannot tell you. (T-135)
Tier: Foundation · Workflow area: Data Quality & Profiling · ~30 minutes
The dangerous moment with unfamiliar data is the first business question, because SQL will return something regardless of whether the data means what the asker assumes. Profiling first inverts the risk: an hour of mechanical measurement — row counts, null rates, distinct counts, ranges, value patterns — plus an AI pass that reads those measurements the way an experienced analyst would, flagging the sentinel values, the broken date ranges, the columns whose names lie. The output is a profile document and, just as important, a "cannot answer" list that stops wrong questions before they ship as reports.
Land the data in DuckDB and get the census. From Parquet/CSV exports or via the scanners:
-- duckdb
CREATE VIEW orders AS SELECT * FROM 'extract/orders.parquet';
-- The one-command census
SUMMARIZE orders;
SUMMARIZE emits per column: type, min, max, approx distinct, null percentage, and quantiles for numerics — the 80% profile in one line. For a whole directory of files, loop it and collect results into a table.
Add the shape checks SUMMARIZE does not cover. Three families, per table:
-- Duplicate rows and key candidates
SELECT COUNT(*) AS total, COUNT(DISTINCT order_id) AS distinct_ids
FROM orders;
-- Value patterns for text columns (generalized, PII-safe)
SELECT regexp_replace(regexp_replace(regexp_replace(customer_ref,
'[A-Z]', 'A', 'g'), '[a-z]', 'a', 'g'), '[0-9]', '9', 'g')
AS pattern, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
-- Temporal sanity
SELECT MIN(order_date), MAX(order_date),
COUNT(*) FILTER (WHERE order_date > current_date) AS future_rows,
COUNT(*) FILTER (WHERE order_date < DATE '1990-01-01') AS ancient
FROM orders;
The pattern query is the workhorse: AAA-9999 appearing 380,000 times and aaa@aaa.aa appearing 4,000 times in the same column is a finding no summary statistic shows.
Hand the numbers — not the rows — to AI for interpretation.
Below: profiling output for an undocumented database (SUMMARIZE
results, key-candidate counts, generalized value patterns, temporal
checks per table). No raw values included.
Produce a profile interpretation:
1. TABLE ROLES — what each table appears to be (fact, dimension,
log, junction) and the evidence.
2. KEY MAP — likely primary/foreign keys from cardinalities and
name patterns; state confidence per link.
3. SUSPICIOUS COLUMNS — sentinel values (1900-01-01, 9999, -1,
'N/A'), null rates that contradict the column's apparent purpose,
mixed patterns in one column, distinct counts that break the
apparent grain.
4. CANNOT-ANSWER LIST — business questions this data appears unable
to support and why (e.g., no currency column with amounts; status
history absent so point-in-time questions fail).
5. QUESTIONS FOR THE OWNER — ranked, specific, referencing the
evidence.
Cite the specific measurement behind every claim. Confidence levels
throughout; prefer "unknown" to plausible invention.
Verify the key map before you believe it — joins are where profiles lie. The model proposes orders.customer_ref → customers.ref; you test it:
SELECT COUNT(*) AS orphans
FROM orders o LEFT JOIN customers c ON c.ref = o.customer_ref
WHERE c.ref IS NULL AND o.customer_ref IS NOT NULL;
orphans: 0 upgrades the link to confirmed. orphans: 84,112 is itself a finding — either the wrong key, a partial extract, or real referential rot; all three matter and the profile records which. Do this for every join the first real analysis will depend on.
Chase the top suspicious columns to ground truth. Pick the five worst flags and look at actual (in-boundary) samples. The typical harvest from a real pass: end_date = 9999-12-31 meaning "still active" (sentinel, not error), a status column with 14 live values where the app UI shows 5 (soft-deleted states), an amount column mixing two currencies distinguishable only via a sibling column that is 12% null. Each becomes a documented caveat with a number attached, not tribal knowledge.
Publish the profile as the schema's front door. One document per source: census tables, confirmed key map with orphan counts, the caveat list ("9999-12-31 means active; statuses G and H are legacy, exclude from counts"), the cannot-answer list, and open questions with owners' answers as they arrive. Anyone querying this database reads this first — that sentence, in the repo README, is the playbook's lasting effect.
postgres_scan, sqlite_scan) or a read-only replica connection let you profile without an export step; keep the same discipline about never pointing it at a busy primary.COUNT, COUNT(DISTINCT), MIN/MAX, pattern regexps) — slower to write, identical in output; AI generates the per-table SQL from your column list in seconds.read_csv with sample_size=-1 and treat every auto-detected type as a claim to check — type drift mid-file is the classic Excel-export defect.SUMMARIZE full scans or explicit full-table aggregates only.Profiling output can itself leak — min/max on a salary column brackets real people's pay, and rare-value patterns can identify individuals. Review the census before sharing beyond the team, generalize samples to patterns before any external-model pass, and classify the profile document with the data it describes.
An hour of DuckDB census plus an AI interpretation pass converts an unknown database from a source of confident wrong answers into a documented one — keys tested by orphan counts, sentinels named, and the questions the data cannot answer written down before anyone asks them.