Building a Gemini-Powered Data Quality AI Agent

Recently, I built an AI-powered data quality agent against the Airbnb dataset .

I wanted an AI agent to do data quality checks such that it provides reasoning to a data engineer to take appropriate actions when the quality check fails or there is a data drift by generating results and inferring patterns, producing a ranked checklist and providing reasoning.

The agent checked the price column in 46,493 listing records. It found that 96.3% of those rows were malformed — meaning the value in the price field was not a price at all. It logged all 44,756 offending rows to an audit table, skipped the downstream average calculation and generated below observation:

Observation: The sample values contain amenity descriptions wrapped in 
escaped double quotes instead of numeric prices, indicating that the 
source CSV columns have shifted and amenity array data is leaking into 
the price column.

  Investigate:
    □ A shift in the source CSV column headers or layout has caused the amenities list to be loaded into the price field.
    □ The ingestion parser is improperly handling unescaped commas within the amenities field, causing trailing data to spill into adjacent columns.
    □ Review ingestion scripts to verify the schema mapping and delimiter logic.
    □ Downstream risk: Price aggregations in Gold dashboards will be completely broken because string values cannot be cast to decimals.

It did not just tell me something was wrong. It told me what was wrong, why it probably happened, and what to check first. That is the difference between a quality check and a quality agent.

In this post I want to show you how I built this. We are going to cover the architecture, the six checks and why they run in that order, and where AI agent adds value.

What Is an AI Agent and Why Is This One Built the Way It Is?

Now, “AI agent” has become a phrase that means everything and nothing, so let us be specific. In this context, an agent is code that uses a large language model (LLM) as a very well-read colleague who can reason over structured evidence — to interpret results and turn them into useful language. We give it evidence. It reasons from that evidence and writes.

For a data quality use case, there are two main patterns for wiring an LLM into a pipeline.

The first is called tool-use. The LLM decides what queries to run, calls them dynamically, and builds its understanding of the data step by step. This is powerful, but non-deterministic — the agent might run different queries on different days, which makes it hard to test and trust in production.

The second is called collect-then-interpret. Python code runs a fixed set of SQL queries, collects the results, and hands everything to the LLM at once. The LLM only touches the output layer — it interprets the results and writes.

We chose collect-then-interpret and this agent runs as an automated task after every ingestion. Every engineer on the team needs to be able to read the code and know exactly what SQL will run. If the agent produces a wrong answer, we need to know exactly which function to look at. There is no black box between our data and the output.

The call flow looks like this:

run_quality_checks("listings", "2026-06-14", token)
    → check_nulls()           # SQL → result dict
    → check_duplicates()      # SQL → result dict
    → check_datatypes()       # SQL → result dict
    → check_row_count_drop()  # SQL → result dict
    → check_value_formats()   # SQL → result dict  (also writes to audit log)
    → check_column_drift()    # SQL → result dict  (gated by format check)
    → all results + evidence → Gemini
    → Gemini returns analysis report

Each check function is independent. Each one runs SQL against the Bronze table, packages the result into a Python dictionary, and returns it. None of them call Gemini. None of them modify Bronze data. The LLM only sees the final collected output.

Six Checks — and Why Their Order Matters

Let us walk through each check and understand why they run in this exact order. The sequence is deliberate — each layer depends on the one before it, and some checks gate what comes after.

1. Null Check

Every dataset has key columns — the ones that would break downstream joins or make a record unidentifiable if they were missing. For listings, those are id, name, host_id, neighbourhood_cleansed, and price.

A null listing id means the record cannot be joined to anything downstream. A null neighbourhood means location-based aggregations silently drop records. Neither of these shows up as an error — they just produce quietly wrong results in dashboards that engineers trust.

The check uses a single SQL query with one expression per column, so we make one round trip to the warehouse instead of five.

2. Duplicate Check

Each dataset has a defined primary key: id for listings, id for reviews, and the combination of (listing_id, date) for calendar. The duplicate check looks for any group of rows sharing the same primary key within the current ingestion batch.

3. Data Type Check

What if a source system quietly changes a column type without telling us? This check compares the current table schema against a known-good baseline.

4. Row Count Drop

What if a delivery is only half the size it should be? A sudden drop of more than 30% in the row count compared to the previous ingestion batch signals that something went wrong — in the source export, the file delivery, or the load.

Importantly, the agent does not halt the pipeline when this fires. It warns. An agent that recommends stopping on every anomaly is one that gets switched off after the first week. The agent’s job is to give engineers the information they need to make decisions .

5. Value Format Check

This is where the architecture gets interesting. Rather than casting Bronze columns to their expected types — which would silently corrupt the raw data — we validate string representations against a regex pattern. A regex is simply a rule that describes what a valid value should look like.

price should look like: $120.00   (pattern: ^\$[0-9,]+(\.[0-9]{2})?$) 
minimum_nights should be:       3         (pattern: ^[1-9][0-9]*$)

For every column in scope, the check counts how many rows match the pattern and how many do not. If any row fails, the full row gets written to a separate audit table — prod_bronze.quality_logs.check_errors — using a single SQL INSERT. Bronze data is never touched.

The function also returns a column_status dictionary alongside the result:

{ "column_status": { "price": "failed",   # more than 10% of rows malformed 
  "minimum_nights": "warned"    # some rows malformed, but below 10%
    }
  }

That column_status flows directly into the next check.

6. Column Drift Check

Now, what if a column that looked fine last week suddenly looks very different this week? That is what the drift check is designed to catch. It computes statistical averages across ingestion dates — average price, average minimum nights — and flags if a metric shifts by more than a defined threshold between batches. A 50% shift in average listing price between two consecutive ingestion dates is a signal worth investigating.

In order to have a right sequence, format check runs first. If more than 10% of rows fail the format check for a given column, that column’s drift metric is skipped entirely. The report explains why:

column_drift: all drift metrics skipped — avg_price (>10% malformed in price); 
avg_min_nights (>10% malformed in minimum_nights)

If fewer than 10% fail — a small number of anomalous values in an otherwise clean column — drift runs and the report notes that the average excluded some malformed rows.

The handoff between the two checks happens through the column_status dictionary:

# In check_column_drift:
  column_status = (format_result or {}).get("column_status", {})

  for metric, cfg in metrics.items():
      status = column_status.get(cfg["col"], "ok")
      if status == "failed":
          skipped.append(f"{metric} (format check failed for {cfg['col']})") else:
          active_metrics[metric] = cfg

One check’s output becomes another check’s gate. This pattern is useful in any pipeline — we do not need an AI agent to apply it. The agent just makes the result readable.

From Alert to Action: Where the AI Actually Adds Value

So what do the six checks give us on their own? Numbers. “96.3% of price rows are malformed.” “1,360 null names.” “10 duplicate groups.” These are useful numbers. But a number alone is not the same as being able to act on it.

When an engineer sees “96.3% malformed,” here is what they still have to do: open a SQL editor, query the audit table, look at the actual bad values, form a hypothesis about what caused it, and figure out where to start. That is 20 to 30 minutes of investigation before anything has been done.

This is where the LLM adds real value — and it is worth being specific about how, because “AI adds value” is easy to say and harder to actually show.

We leveraged Gemini API’s and used gemini model gemini-3.5-flash for providing the intelligence and passed two things to Gemini. The first is the check results (the numbers). The second is what we call column_intelligence — a structured dictionary built from the audit table that contains:

- The actual bad values logged from the format check (up to 5 distinct examples)
  - How many rows each bad value appears in
  - The malformed rate from the previous ingestion date, for trend comparison
  - Today's total row count and malformed count

It looks like this:

{ "price": 
        { "today_malformed_pct": 96.3, 
          "today_malformed_count": 44756, 
          "today_total_rows": 46493, 
          "previous_source_date": null, 
          "sample_bad_values": [
                    {"value": "\"Hot water\"",                              "occurrences": 12692},
                    {"value": "\"Bed linens\"",                            "occurrences":  4135},
                    {"value": "\"Exterior security cameras on property\"", "occurrences":  3178},
                    {"value": "\"Hair dryer\"",                            "occurrences":  2525},
                    {"value": "\"Hangers\"",                               "occurrences":  2131} ]
    }
  }

This is evidence, not just a count. Gemini looks at those five sample values and does something a SQL query cannot do on its own: it recognizes a pattern and infers a cause.

Here is what Gemini actually wrote when it received this evidence:

format_check — listings.price
  Today: 96.3% malformed (44,756 / 46,493 rows)
  Previous: first run — no baseline for comparison

  Sample bad values: 
" ""Hot water"""  — 12,692 rows 
" ""Bed linens"""  — 4,135 rows 
" ""Exterior security cameras on property"""  — 3,178 rows 
" ""Hair dryer"""  — 2,525 rows 
" ""Hangers"""  — 2,131 rows

  Observation: The sample values contain amenity descriptions wrapped in escaped double quotes instead of numeric prices, indicating that the source CSV columns have shifted and amenity array data is leaking into the price column.

  Investigate:
    □ A shift in the source CSV column headers or layout has caused the amenities list to be loaded into the price field.
    □ The ingestion parser is improperly handling unescaped commas within the amenities field, causing trailing data to spill into adjacent columns.
    □ Review ingestion scripts to verify the schema mapping and delimiter logic.
    □ Downstream risk: Price aggregations in Gold dashboards will be completely broken because string values cannot be cast to decimals.

No query told Gemini this was a column shift. No query told it the problem was unescaped commas in an amenities field. It looked at five strings — ””Hot water””, ””Bed linens””, ””Exterior security cameras on property”” — and reasoned that these are list-like amenity values, not prices, which means the CSV parser misaligned columns during the load.

The checks measured something. The LLM interpreted it. The engineer who reads this report knows exactly what to look for. The agent compressed 20 minutes of debugging into a 30-second read.

The system prompt is how we get Gemini to behave this way. Think of a system prompt as the job description we hand to the LLM before it sees any data. Without clear instructions, an LLM defaults to restating the numbers we already have in the terminal. With the right instructions, it produces the analysis above.

We gave Gemini three core rules:

1. For every column in column_intelligence, produce a trend block, the sample bad values, a one-sentence observation, and an ordered investigate checklist with the most likely cause first.

  2. If column_intelligence is absent for a dataset, produce one plain-English line per check — nothing more. Do not invent evidence that was not given.

  3. Never suggest halting the pipeline. The audience is a data engineer who makes that decision. The agent's job is to give them information.

Rule 2 is the one that is easy to overlook but critical to get right. Gemini is explicitly told to reason only about evidence that is present. This prevents the agent from hallucinating a trend comparison when no historical data was collected, or inventing a checklist when no format check was defined.

What the Agent Found on Real Data

Let us look at what the agent reported when run across all three datasets — listings (46,493 rows), reviews (1,866,743 rows), and calendar (16,570,636 rows) — after ingesting data for two cities.

Check               listings    reviews    calendar
  ──────────────────────────────────────────────────
  null_check              FAIL        PASS       PASS
  duplicate_check     FAIL        FAIL         FAIL
  datatype_check     PASS      PASS       PASS
  row_count_drop     PASS      PASS       PASS
  format_check         FAIL        PASS       PASS
  column_drift           PASS        PASS       PASS
  ──────────────────────────────────────────────────
  OVERALL              FAIL        FAIL       FAIL

A few observations from this output worth pausing on.

Reviews and calendar had no format check failures — those datasets do not have numeric columns in scope for format validation in Phase 1. But duplicate_check failed across all three datasets. This happened because two cities (San Francisco and New York) were both ingested under the same source_date partition, so primary keys that were unique within one city collided across cities in the same table. The agent surfaced a real ingestion design issue — not a value quality problem in the traditional sense. That is the kind of finding we only catch if we run a primary key check on every batch, every time.

The format check logged 88,871 malformed rows to the audit table for listings. Every single one of them, with the exact value that failed, the column it came from, and the timestamp of the run. Bronze was never touched.

One more thing worth knowing: during this run, Gemini hit a rate limit on the reviews dataset. The free tier allows 20 API calls per day — three datasets across multiple runs exhausted that quickly. The check results for all three datasets still printed correctly. The LLM failure never blocked the detection layer. The checks and the interpretation are two independent layers. If Gemini goes down, we still know our data failed. We get the written analysis on the next run.

This is what production Bronze data looks like. Not the sanitized sample datasets from tutorials. Real Airbnb listing data, freshly downloaded, with column shifts, raw amenity strings in numeric fields, and missing host information. The agent did not make the data worse. It made the problems visible — which is exactly what it was built to do.

Conclusion

The collect-then-interpret pattern is the right starting point for anyone adding an LLM into a data pipeline. Keep the SQL in Python functions. Keep the LLM at the interpretation layer. When something goes wrong, we know exactly which function to look at.

The format check gating the drift check is the single architectural decision worth taking from this article. Any time we have an analytical check that depends on data being in a certain shape, we should run a format check upstream and gate on the result. Computing an average over malformed data is not just wrong — it is confidently wrong, and confidently wrong answers in a pipeline are the hardest kind to catch.

Real Bronze data is messier than any tutorial will prepare us for. The column shift that landed amenity strings in a price column is not an edge case — it is exactly the kind of thing that happens when we ingest CSV files from a third-party source that changes its format without telling us. The agent did not fix this. It found it, named it, and gave an engineer a ranked checklist before they opened a single file.

In essence: the AI is not doing magic here. It is doing pattern recognition on structured evidence and translating findings into plain English. The interesting engineering work is in building that evidence — the sample bad values, the historical rates, the column_intelligence dictionary — and giving it to the LLM in a shape it can actually reason about. Get that right, and the analysis takes care of itself.

There is no one-size-fits-all approach to adding AI into a data pipeline. But if we are just getting started, collect-then-interpret gives us the right combination of predictability, testability, and LLM value — without sacrificing the ability to understand exactly what our agent is doing on any given run.