Exploring AI-Assisted QA for Snowflake and dbt
Data quality problems are usually easy to spot. Working out why they happened is the slow part.
When a dbt test fails, you know something is off. A column has nulls, a key isn’t unique, a relationship test broke. What you don’t know is what caused it. Did an upstream source change? Did a load run twice? Is this new, or has it always been like this and nobody tested for it before? Answering that means digging through lineage, checking run history, querying the data, and often asking around.
I’ve been experimenting with using an agent on AWS Bedrock to do some of that digging. The idea is that a failed test shouldn’t be the end of the pipeline’s output. The agent takes the test result, pulls whatever metadata is relevant, has a look at the data, and comes back with a few likely explanations. An engineer still has to confirm them. The agent just gets you to a shorter list faster.
What that looks like in practice
Say the unique test on orderid in fctorders fails. Yesterday it passed. Today there are 412 duplicate rows.
Here’s what I’d normally do. Query the duplicates to see if they’re exact copies or differ in some column. Check the model SQL for a join that could fan out. Look at the staging model underneath it. Look at the raw source table to see if the duplicates are already there. Check whether a load ran twice. Depending on what I find, this is ten minutes or it’s most of the morning.
Almost every step of that is mechanical, which is exactly why I think an agent can do the same walk.
Pull the failing rows and group by orderid. Are the duplicates identical, or do they differ in updatedat, status, something else? Identical rows usually mean a double load. Rows that differ by a timestamp usually mean a missing dedup step or a slowly changing dimension that wasn’t handled.
Read the compiled SQL for fctorders and the models it depends on. Look for joins to tables where the join key isn’t unique. If fctorders joins stgorderitems on order_id and that’s a one-to-many, there’s your fan-out.
Check the raw source. If raw.orders already has 412 duplicate order_ids, the problem isn’t in dbt at all. It’s upstream in whatever is landing the data.
Check run history. If the source table was loaded twice in the last 24 hours, or a backfill overlapped with the scheduled run, that’s a strong candidate.
Then come back with something like: “412 duplicate orderids. All duplicates are identical except for loadedat, which differs by roughly 15 minutes. raw.orders also contains these duplicates. Two load jobs completed for raw.orders between 02:00 and 02:30. Most likely cause: the source load ran twice. Less likely: a join fan-out in stgorders, but the join key there is unique in the current data.”
That’s not a diagnosis I’d act on blindly. It’s a diagnosis I’d check in two minutes instead of thirty. And if it’s wrong, I want it to be wrong in a way I can see, because it showed me the evidence it used rather than just the conclusion.
The catch
The obvious objection is that a model will make things up, and that’s fair. Without the right context it will give you a confident, plausible, wrong answer, and in a production pipeline that’s a liability rather than a help. So the real work is less about the model and more about what you feed it, what it’s allowed to touch, and how you check whether it’s actually reasoning from evidence or just pattern-matching on the error message. The example above only works if the agent can read lineage, query the warehouse with read-only access, and see load history. Take any of those away and it’s guessing.
Where this is
I’m still at the prototype stage. The pieces aren’t connected to Snowflake and dbt yet, so the walkthrough above is what I want it to do, not what I’ve seen it do. I don’t have results to share and I’m not going to claim it works until I’ve tested it against real failures.
That’s the next step. Wire it up, point it at actual broken runs, and find out whether it earns its place.
The goal isn’t to replace the tests. It’s to make the hour after a test fails less manual.