Classify PII and Sensitive Columns at Scale with an LLM

Tier: Core · Workflow area: Docs, lineage & governance

LLMs close the gap because they read column names, comments, and sampled values the way a human reviewer would — at machine speed. This playbook runs an LLM classification pass over warehouse metadata, produces tag proposals with confidence and rationale, routes them through human approval, and compares the result against warehouse-native classifiers so you know what each layer catches.

When to reach for this

• •
  Seeding a data catalog or DLP program across an estate too large to tag by hand.
• •
  A compliance deadline (DPDP, GDPR, SOC 2) requires a defensible inventory of personal data.
• •
  Warehouse-native classification exists but you need cross-platform consistency or richer categories than it offers.

Don't use this as the only control for regulated data — it is a discovery accelerator feeding a human-approved tagging process, not the process itself.

The workflow

1. 1.

   Pull metadata, and sample values only where allowed. Column name + type + table context alone classifies surprisingly well; sampled values raise accuracy but mean sensitive data transits the LLM — get explicit approval first, and mask deterministic identifiers. ANSI/DuckDB-style metadata pull:

   Copy

   SELECT table_schema, table_name, column_name, data_type
   FROM information_schema.columns
   WHERE table_schema NOT IN ('information_schema')
   ORDER BY table_schema, table_name, ordinal_position;
   

2. 2.

   Define the taxonomy before prompting. Categories like DIRECT_IDENTIFIER, QUASI_IDENTIFIER, SENSITIVE_ATTRIBUTE, FINANCIAL, HEALTH, CREDENTIAL, NOT_SENSITIVE — whatever maps to your policy. A classification without an agreed taxonomy just creates a second argument.

3. 3.

   Classify with structured output so results load straight into review. Python with the anthropic SDK:

   Copy

   import anthropic
   from pydantic import BaseModel
   
   class ColumnClassification(BaseModel):
       table_name: str
       column_name: str
       category: str            # from the agreed taxonomy
       confidence: str          # high | medium | low
       rationale: str           # one sentence, cites name/type/value evidence
   
   class ClassificationBatch(BaseModel):
       results: list[ColumnClassification]
   
   client = anthropic.Anthropic()
   
   response = client.messages.parse(
       model="claude-opus-4-8",
       max_tokens=16000,
       thinking={"type": "adaptive"},
       messages=[{
           "role": "user",
           "content": f"Classify each column into exactly one category from: "
                      f"{TAXONOMY}. Use only the evidence provided; when the name is "
                      f"ambiguous and no values are given, use category NOT_SENSITIVE "
                      f"with confidence low rather than guessing sensitive.\n\n"
                      f"Columns:\n{metadata_chunk}",
       }],
       output_format=ClassificationBatch,
   )
   for row in response.parsed_output.results:
       print(row.table_name, row.column_name, row.category, row.confidence)
   

   Chunk ~100–200 columns per request, grouped by table so the model sees table context. For estates in the tens of thousands of columns, submit the same requests through the batch API for roughly half the token cost.

4. 4.

   Route through human approval, sorted by risk. Load proposals into a review sheet or your catalog's approval queue (Atlan, Collibra, Unity Catalog tags in a pending state). Review order: high-confidence sensitive first (quick confirms), then low-confidence anything (the real work). Nothing becomes an enforced tag without an approver.

5. 5.

   Cross-check against warehouse-native classifiers. Run Snowflake's sensitive-data classification (AI_CLASSIFY-family / SYSTEM$CLASSIFY) or BigQuery + Cloud DLP over the same schemas and diff the three-way result: LLM-only catches (semantic naming, free-text columns), native-only catches (value-pattern hits on misleadingly named columns), and agreements. The disagreement set is your highest-value review queue.

6. 6.

   Wire approved tags to enforcement and repeat on a schedule. Approved tags should drive masking policies and access rules, and the pass should re-run on new columns (trigger from information_schema diffs or catalog webhooks) so classification is a pipeline, not a project.

Verify before you trust it

• •
  Hand-label a stratified sample of 100 columns and score precision/recall per category before trusting estate-wide numbers; recall on sensitive categories is the number that matters.
• •
  Every high confidence sensitive proposal cites evidence in its rationale that a reviewer can see — reject rationale-free confidence.
• •
  The native-classifier diff has been reviewed — if the LLM missed things the pattern-matcher caught, your prompt or sampling is under-informing the model.
• •
  Approved tags actually appear in the enforcement layer (masking policy applied, access filtered) — test with a non-privileged role.

Pitfalls

• Name-only false negatives.col_47, payload, free-text notes columns hide PII that only value sampling reveals. Track which columns were classified without values and mark them lower-assurance.
• Over-tagging quasi-identifiers. Tagging every date and city column SENSITIVE makes the program ignorable. The taxonomy needs a quasi-identifier tier with different enforcement.
• Confidence theater. LLM confidence strings are calibrated by your prompt, not by statistics. Validate against the hand-labeled sample before letting confidence drive review order.
• One-shot classification of a living schema. New columns arrive weekly; a quarterly manual re-run means weeks of unclassified sensitive data. Automate the trigger.
• Skipping the human queue "because accuracy is high". The approval step is what makes the tags defensible in an audit. Keep it even at 98% precision.

Tools

• Claude batch API + structured outputsthe estate-scale classification pass; JSON-schema enforcement keeps results machine-loadable, batch pricing keeps it cheap.
• Snowflake Cortex AISQL / sensitive-data classificationwarehouse-native pass that never moves data out; the natural cross-check (BigQuery + Cloud DLP is the equivalent on GCP).
• Catalog approval queues (Atlan / Unity Catalog)where proposals become governed tags; use whatever catalog the org already runs.

Client-safe variant

On client engagements, default to metadata-only classification — column names and types leaving the client boundary is usually acceptable where values are not, but get that in writing. If even metadata is restricted, run the identical workflow with a self-hosted model (Ollama/vLLM with a capable open-weights model) inside the client's VPC, or use only the client's warehouse-native classifiers and reserve the LLM for synthesizing the review queue. Deliver the hand-labeled precision/recall scores with the inventory; a classification report with measured accuracy is a compliance artifact, one without it is a guess.