dqScan

Inspiration

During a data analyst internship, I kept running into the same data quality problem: warehouse columns that were either completely empty, or quietly filled with junk. Empty columns are easy to find. Junk that looks like data isn't. A phone number of 9999999999, an income of 0, a housing status of ANY, or a text field that still says "enter hospital name" all pass every null check. So a completeness report says the table is 100% complete, and every dashboard and model downstream treats those values as real.

What it does

dqscan scans warehouse tables for disguised missing values: placeholders, dummy entries and unreplaced form text that mean "nobody entered anything."

• It scans Snowflake tables in place: the statistics are computed in Snowflake, and only aggregates and a few example rows leave the warehouse.
• Values it's confident about are flagged with a one-sentence reason and the statistics behind it.
• Values it isn't sure about go to a review queue, where a person confirms or rejects each one in a click. Decisions are saved to Snowflake immediately.
• Confirmed values can be exported as a SQL view that turns them into NULLs, plus dbt tests so they can't come back. dqscan never modifies the source data.

How I built it

dqscan uses Gemini 3.8 Flash, but the LLM is checked at every step rather than trusted:

1. Profile: each column gets a statistical profile: common values, a sample of rare ones, their "shapes" (555-0100 → ddd-dddd), and how often a typical value appears.
2. Propose: Gemini reads the profile and proposes what a placeholder would look like in that column. Code then matches those proposals against every distinct value, which is how rare placeholders get found.
3. Review blind: the matches are mixed with ordinary "decoy" values from the same column, and Gemini reviews them against an evidence packet without knowing which values are suspects.
4. Verify: code checks that every statistic the model cites exists in the evidence and has that value. A verdict with an unverifiable reason is downgraded to "unsure" and sent to a person.

Stack: Python, pandas, Snowflake (in-place processing and results tables), Gemini with structured output and a response cache (reruns are reproducible), and Streamlit for the dashboard.

Challenges I ran into

• Proving it works. A tool like this is worthless if you can't say how accurate it is. I built a benchmark: a verified clean dataset (the Raha Hospital table) with placeholders injected in three kinds (frequent, rare, and form template text), plus an answer key. I developed against one set of injected errors and reported results from a separate held-out set that was written independently and run once.
• The "clean" data wasn't clean. The benchmark dataset already used the literal word empty for missing values, so I had to declare those as pre-existing placeholders to score fairly.
• Getting FAHES running. The public demo repository for FAHES couldn't be built: a source file was missing and its binaries were macOS-only. I found the complete source, built it in Docker, and wrapped it in a small HTTP service.
• Keeping the LLM honest. Early versions missed template text entirely, because the model only saw each column's most common values. Showing it a sample of rarer values fixed that. I also tuned the citation checks so they reject made-up numbers without rejecting correct verdicts.
• Moving the work into Snowflake while guaranteeing identical results. Warehouses don't preserve row order, so I made every step order-independent, then verified that scanning in Snowflake and scanning locally produce identical verdicts.

Accomplishments that I'm proud of

• dqscan beats FAHES, a published detector for exactly this problem (KDD 2018). On the held-out benchmark:

• The difference is in the hard cases. On rare placeholders, statistics found 0–2 of 7, while dqscan found 6. On template text, statistics found 0–1 of 3, while dqscan found all 3.
• Zero false alarms on decoys: the reviewer never flagged an ordinary value.
• I measured my own design and changed it. I originally used FAHES as dqscan's first stage. The benchmark showed that adding it changed nothing, so I took it out and kept it as the baseline to beat.
• On real, public Lending Club loan data, dqscan found values like a debt-to-income ratio of 999.0 and a housing status of ANY. It sent genuinely ambiguous cases, like an income of 0.0, to a person instead of guessing.

What I learned

• Measuring beats assuming. The component I expected to matter most (FAHES) added nothing, and I only found out because I built the benchmark first.
• LLMs are most useful when they're checked. Blind review, decoys and citation checks turned a confident-sounding model into one whose output I can trust and audit.
• "Unsure" is a feature. Letting the model defer ambiguous cases to a person made it more accurate, not less.
• Real data is messier than benchmarks: a stray space in " N/A", placeholders already inside a "clean" dataset, warehouses with no row order.

What's next for dqscan

• Placeholders that look like real values: defaults such as a dropdown's first option. This is FAHES's specialty, and my benchmark doesn't test it yet.
• Template text inside longer text, such as boilerplate at the start of a free-text field.
• Faster scans: parallel LLM calls, and pushing pattern matching into Snowflake SQL for very large tables.
• Scheduled monitoring: re-scan tables automatically and alert when confirmed placeholders reappear.
• A larger benchmark across more datasets, for broader accuracy numbers.

Built With

• ai
• fahes
• gemini
• pandas
• python
• snowflake
• statistics
• streamlit