Building AI Agents That Answer Why: dbt, Databricks, and MCP

Summary

Standard BI copilots fail on 'why' questions because they see only dashboard values and filters and have no access to the transformation logic, business rules, or upstream data that explain discrepancies.
Integral Ad Science built an AI agent connecting dbt, Looker, and Databricks through MCP servers, giving the agent direct access to column-level lineage, compiled SQL, and the Unity Catalog to enable root-cause analysis within the BI layer.
By choosing MCP over direct APIs for its richer, semantically structured interface, the agent can trace discrepancies back through dbt transformation models and validate findings in seconds rather than hours of manual investigation.

Building AI Agents That Answer Why: dbt, Databricks, and MCP

BI copilots can answer what-about-the-data questions but fail on why questions. They see dashboard values and filters but cannot access the transformation logic, business rules, or upstream data that explain discrepancies. Analysts spend hours tracing through DBT models, SQL, and DAGs manually.
This talk shows how Integral Ad Science built an AI agent that solves this by connecting dbt, Looker, and Databricks through MCP servers. The agent gains direct access to column-level lineage, compiled SQL, and the Unity Catalog, enabling both analysts and business users to trace root causes and validate findings, all within the BI layer and in seconds.
🤝

Chapters

FAQs

Why can't standard BI copilots answer 'why' questions about data?

BI copilots see only the data exposed in dashboards—values, filters, and aggregates—but have no access to the transformation layer that produced those values. Without visibility into dbt model logic, compiled SQL, and upstream lineage, they cannot explain why a metric looks the way it does or trace discrepancies to their root cause.

How does MCP connect dbt, Looker, and Databricks in this agent architecture?

The agent uses MCP servers as a structured interface to each tool, calling dbt for compiled SQL and column-level lineage, Looker for dashboard context, and Databricks for data access and Unity Catalog governance. MCP was chosen over direct API integration because it provides a more semantically consistent interface that the LLM can reason over effectively.

What is the Tangaroo example and what does it demonstrate about the agent?

Tangaroo is a product in the Jaffle Shop demo project that was categorized as food rather than as a beverage, inflating food revenue metrics. The AI agent traced the misclassification back through dbt transformation logic to identify the business rule causing the error, demonstrating root-cause analysis that would otherwise require hours of manual investigation through SQL and DAGs.

How does Unity Catalog provide governance for this AI agent?

Unity Catalog controls what data the agent can access in Databricks, ensuring that sensitive datasets are protected even when the agent queries them on behalf of users. This means the agent respects existing access policies and every query it executes is auditable, satisfying enterprise security requirements.

Full transcript

[00:09] All right, how's it going everyone? Thanks for joining me today. Uh, I'm just going to jump right into it, tell you about this idea. So, every BI tool, all of them, I think, all the major ones, all the small ones that in some way, shape, or form in the past few years, they've shipped some sort of AI
[00:24] chatbot or co-pilot right on top of the dashboard. And in my experience, it's very good at summarizing data, doing text to SQL, creating reports. But you know what I find they're not very good at is those why questions. We have
[00:40] entire Slack channels at my company dedicated to why questions and analytics. Why is this product categorized this way? Why is revenue declined for this customer? And that's not a knock on AI. It's definitely not a knock on any of the BI platforms. The chat bots are doing what they were built
[00:57] to do given the data we expose them to. They just have no semblance of the data behind the data, the transformation layer. They stumble on these questions because they don't have access to the answers. My name is Mars Dower. I lead enterprise data and AI at Integral Ad
[01:12] Science. And for this talk, I just want to show you how we enhanced and brought those answers to the dashboard. All right. So, we're gonna adopt the persona. We're someone working probably in finance and the cursor is still not moving. What's going on?
[01:33] There we go. And if you're unfamiliar with what we're looking at, this is Jaffel Shop. It's the Hello World, the go-to demo project of DBT. It's basically a sandwich and smoothie shop. So, right now we're working as finance guy. finance guy is looking at this dashboard and he's like,
[01:49] "Hey, food revenue seems a bit high." Maybe he's comparing it to his local source of truth spreadsheet and he's like, "Okay, something's going on." So, what does he do? He's going to drill into this explorer. He's going to he's going to see if, you know, anything passes his gut checks. If anything
[02:04] smells off, maybe he adds a filter. We're probably going to go ahead and add a filter. Let's see a breakdown by product. Run that. See if anything sticks out.
[02:22] And right away he's like, "Okay, all right. Now I have a product breakdown. Things are a little bit more obvious." And there's this product, Tangaroo. Funny name. Uh he's like, "Hey, Tangaroo is categorized as a food. I I think it's a smoothie. Shouldn't that be a beverage?" So he's like, "Okay, this might be what's throwing everything off." So what do we do? We jump into AI.
[02:39] Maybe AI has the answer, right? So, we're going to go ahead and say, "Hey, why is kangaroo considered a food? I think it might be a beverage." And what does AI spit out? Let's see any anything profound. It says kangaroo is a food because the product category is a food.
[02:55] That's basically it. Brilliant. So, product category is food. All the flag fields say it's food. Therefore, tangaroo is a food. So, the co-pilot, it describes it on the screen. It summarizes the chart. It lists the filters, but it doesn't have a real understanding of the business logic or upstream transformations that created
[03:13] tangaroos of food. So, here's what we're going to discuss today. Uh, we're going to go through the BIY problem, why co-pilots can't answer this. We're going to dive into MCP in two minutes. I know this is a technical crowd, so I'll just keep it light. Uh, then we're just jumping into the live demo,
[03:29] architecture, design decisions, governance, and eval. Finally, some lessons learned and where we're headed with this. So, yeah, let's talk about that BIY problem. So, the BIY problem, why they can't answer those why questions is that
[03:45] LM on dashboards are only can see can only see what's exposed in the BI layer. This is usually your MARS layer and DBT or if you're doing medallion architecture, it's your gold layer. Maybe you have a great semantic layer, but that's that's the very thin lens that the BI layer has access to. What it
[04:02] doesn't have access to is all that intermediate model, all that business logic, all the source data that's buried in DBT. And there's a spoiler up there too for that case statement. So let's go back to our investigation with finance guy. What's he doing? So maybe he pings
[04:17] an analyst or if he's savvy enough, you know, he's going to dive in himself. He's like, "Hey, this number looks wrong." The analyst might open the explorer. They're going to check the look ML. They're going to see what fact table is this pointing to. You find the DBT model. You trace it through the DAG compiled SQL. You might have six tabs
[04:35] open at that point. The analyst might lose the plot and then maybe they find the answer in 20 minutes, maybe an hour, maybe sometimes it takes all afternoon. So that's what this agent is uh that we created is to fill that gap. So our stack is DBT for modeling, data bricks
[04:51] for warehouse, and looker for BI. Each one exposes an MCP server. So we built one agent that uses all three. So MCP is what made this possible. Let's do just a quick detour and explain what that is for anyone who's unfamiliar. Like I said, it's a technical audience, so
[05:07] we'll keep it light. MCP is just the standard interface that agents interact with tools and systems. It's basically an API specifically built for AI. It's a translation layer for any tools AI system to talk to AI. It's plug-and-play. I like to say it's the
[05:22] USB for LLM tools. An agent has one port. any tool that uh can plug in will instantly understand the tools exposed. So before MCP every AI had to like be manually written into scripts and all these LLM tools uh new error routing,
[05:39] bespoke schemas, discovery. Now with this project we just combined three MCP servers. DBT cloud for lineage and model details, data brick SQL which is read only against Unity catalog and Looker for the BI access and SQL parsing.
[05:57] The key point here is the agent doesn't need to know any of these tools. It just speaks MCP. It's plug-andplay. So now let's jump in right to the demo. Hopefully the video plays this time. I'll set it off manually.
[06:13] So, back to this Tangaroo uh investigation. Instead, though, we're going to fire up our embedded agent that we created that combined these MCP tools. First, we're going to show something off that it still retains the Looker conversational analytics. So,
[06:29] that's available through the Looker MCP. We're going to validate our hypothesis. We're going to say, "All right, what's food revenue minus Tangaroo? Let's just make sure that, you know, we're on the right track." And lo and behold, we are. So yeah, now now we definitely know something's up. So we're going to say finally the crux of our situation. Yep,
[06:46] we've validated. We know the revenue matches. But we're going to ask why is Tangaroo food? I typed slow. Sorry. So three things to watch here. We're streaming every tool call. uh it's going
[07:03] to call the dbt analyst first run through the entire DAG validate everything in data bricks and spit back the answer and we like to format things in too long didn't read right at the top is called a food because it contains the word smoothie and that overrides all the other business logic we're like all
[07:20] right cool let's go a step further let's say hey what's the SQL and now we know the exact SQL that's causing this discrepancy whether it's correct or Not that we'll leave that to the analytics engineer. But before we
[07:35] pass it off, let's say, hey, if we change this, is this going to break anything like any downstream models? Any surprises here? Probably not. No, it's only affecting order items and orders. We're like, all right, you know
[07:51] what? Let's just submit a JUR ticket. Let's hand this off to an engineer to take a look at. So what you just saw is basically an investigation that could have taken you know an hour or two hours gone through five DBT models validated in data bricks done in seconds and all
[08:07] without leaving the BI layer. And I do have a couple more demos I just want to show you to really hammer this home.
[08:23] This one we're again we're still finance guy. We're saying, "Okay, we're looking at our total lifetime spend for our customers, and we're we're we're noticing an anomaly. There's a lot of customers that have relatively low order quantities and have really high lifetime spend." And so, again, we're just going to ask the native AI what's going on.
[08:40] And this is the interesting part. It's kind of contextualizing the data it's seeing. It's not wrong, but it's contextualizing it. Actually, it says Kelly Wells with that 183k lifetime value. She's platinum tier. So, it's almost creating a story about the data that it's seeing. And the scary part is,
[08:56] could you imagine if like we spent campaign dollars on building a segment around someone who looks like Kelly Wells? Luckily, we have this agent where we can just kind of answer the same question. Switching it to analyst mode. And I'm going to go deeper into what those personas are in a couple slides.
[09:14] See what it finds this time. And this time, the culprit is a join fan out. Again, we've formatted it too long, didn't read. Right at the top, we have a join fan out, a root cause, and I believe if past me scrolls down, I even
[09:30] have a validation query that now we can pass off to that analytics engineer. They can replicate the problem and potentially fix it. So, we gave two different AIS the same data. One one contextualized and made up a story about it, and the
[09:46] other found a bug. And for a data team only that second part is the one we can actually take action on. And then finally our for our last demo.
[10:03] This one hits close to home. So we have a revenue question. Uh this person Joy Lamb her uh her total lifetime spend is way less than the total lifetime spend pre-tax. So, this is usually when someone gets pinged and said, "Hey, the data is wrong. Figure it out." Um, and that's that's exactly the
[10:20] workflow we're trying to avoid. So, we're just going to go ahead ask the agent what's going on with uh with Joy Lamb's revenue. Where's that missing revenue?
[10:35] And this time it's going to explain hopefully. There we go. It explains that Joy Lamb had billy credits, billing credits. And not to worry. So, don't worry about it. We just saved an analyst's life. So, there it is. Three investigations in six minutes. Things that used to take hours to figure out.
[10:51] Finance guy didn't have to bother anyone. No one got any issues escalated. He just had his answers and everyone's happy. Everyone has their time back. And to me, it's kind of wonderful. Saves a lot of time on my team because we can now answer those why questions directly in the BI layer. So, you saw me switch
[11:07] to analyst mode. I'm going to talk about that in a couple slides, but I do want to talk about the architecture first and what the agent does. So, what does our agent actually do? Uh, the best way to answer that was just ask the agent.
[11:23] You could see right here it just says column level lineage, business logic, data validation, impact analysis, and pipeline health. More to come. But I kind of bucket bucket that into three things. Lineage and logic, validation, and action. And really what I'm just trying to hammer home is the power of
[11:39] simply pointing an LLM at your business logic at your data model and at your warehouse. uh sorry on those personas. So we have two modes analyst and
[11:54] business. Here we have a business uh mode answer where the answer is going to be less technical. There's no SQL statements. There's no technical jargon. of course not playing the video. There we go. However, if you switch to analyst
[12:09] mode, it's going to give you the if you paste the exact same question, it's just going to go a little deeper, it's going to give you the same root cause, but it's going to give you those validation queries, those DBT model names, everything that an analyst need with to uh solve or get to a root cause of a
[12:25] problem. And that was just a little tweak in the output prompt. we didn't need an entirely separate agent to to serve both sides of the data teams. So this is our endtoend architecture. Um this is very specific to our use case.
[12:41] Yours is obviously going to dinner differ, but at a high level it's the same. You're going to have a front end, you're going to have a BI layer. It doesn't matter which BI tool you guys have. I'm sure they all offer some way to host external web content embedded in
[12:56] the front end somewhere. I believe I found every solution across the board. Um, looker at a couple options and I'll go through those. For hosting, we just, you know, you have to host that web content, your agent somewhere. For us, it's just cloudr run and expose it via
[13:11] an API. In our case, fast API. Um, our agent framework is just Google ADK. We're Gemini's enterprise shop, so it just fits our ecosystem. Um, and then but our LLM tooling, this is the cool part, uh, we're using a library called light LLM, which allows you with a
[13:28] single line of code to just swap out the endpoints. So for like our data bricks agent, we could just swap it for a datab bricks foundational endpoint, keep all our data in a secure environment. And then of course our MCP tools, which are interfacing with our source systems, our DBT project, our BI layer, and our Unity
[13:44] catalog. Let's go over some design decisions real quick because again this is a journey. This is just how we created this agent. Uh you can learn from our mistakes and you know oh one more thing about MCP. I almost cut this slide. Uh then my friend
[14:01] he's a CMO at a pretty large company. He asked me hey what's so special about MCP? Every tool has an API. Why not just plug into that? Uh there's four main reasons. Speed. Every new tool we want to every new tool we want to interface with. We don't need to write a custom script. We point it to the MCP server,
[14:18] the tool is there with instructions ready to go. We don't need to maintain any sort of API client. Uh decoupling uh when the API changes upstream on the host side, our code again doesn't move. That integration boundary is the MCP server. Same thing with governance. The
[14:34] MCP enforces whatever built-in government governance and access controls they already have. And then the composability, which is what I like to say, it's like the more modularity of it. If we swap the LLM out, the tools stay the same. Again, there's no rewiring, there's no code rewrites. And
[14:49] then just a little more on the DBTMCP, it's basically the ground truth for uh that compiled SQL. It almost acts like a pseudo semantic layer telling the agent exactly what tables and what data to go through in order to uh get to our
[15:05] answers. Um some architecture trade-offs we went through. So multi-agent, single agent. We actually started with single agent. Uh as the tool surface grew, the prompts grew. Uh now we moved on to multi- aent. Now we have like four specialist agents
[15:21] under one routing agent. Uh the win we get we get better answers that way. Uh the trade-off is latency. Answers might take longer, but obviously not as long as an analyst would take to dig through DAXs. Um model mixing. So because now we have multi- aent, we're able to choose
[15:38] which LLM model we want to apply to each agent. So for things like SQL parsing or the Looker parser, we can choose a lighter model that's cheaper, save on tokens. And then for something more complex like the DBT analyst, we can choose a foundational model. Um,
[15:53] finally, just embedded versus standalone chat. Um, we actually started with a standalone chat, but it was really hard to provide context. Uh, and we found that, you know, there's multiple ways to skin a cat. Look into the docs for your BI layer. See where you can embed, where
[16:10] you cannot, see what options there are. We found out we can embed right in the explore, right in that tile. And that way, it was really easy to inherit the context just through a query ID in the Looker URL, and we would have our entire mapping of SQL that DBT would then just find the models for.
[16:30] Uh, just some quick quick more design decisions. Uh these are more production readiness decisions. This is like the unglamorous stuff nobody thinks about until it inevitably does come up. The first two are the same idea. When uh you're interfacing with these MCP tools, the responses can be very robust, very long. That can be tokenheavy. We just
[16:47] slim them slim them down a bit to only the information we need for the use case. Saves a lot on tokens in the long run. Um this one, streaming, it's a big one that's obvious and also overlooked a lot in V1 agents. What I mean by that is when you enter an agent prompt, just
[17:04] stream the tool calls. Uh, a lot of times people don't think about that in their V1, you you ask the agent something and then you know you everything's happening behind the scenes and the user has no idea what's going on. So streaming that streaming that through the SSSE events, best way to go. Trust comes from visibility, not from
[17:20] speed. Let's talk a little bit about governance and eval. uh governance was actually the easy part thanks to Unity catalog. So the agent actually inherits user permissions via the MCP via the data bricks SQL MCP. So
[17:38] all the arbback the rowle security if a user can't see it in the database or in the BI layer then the agent's not going to expose it either. That's basically the scope of governance. Uh MCP made it easy. Um so and it's also read only. All that nightmare stuff you read on
[17:54] LinkedIn AI deleted my database. uh it's not even an option here. It's it's readon inherently. Uh so you'll never face that kind of scenario. And then finally also the same audit trail. So since everything is done through data bricks and unity catalog, if you ever
[18:09] want to see what the agent actually executed, it's going to be in the same audit log within Unity Catalog. So how do we know the agent actually works? And this is this especially comes in handy when we're making quick
[18:24] changes. Um, if you're unfamiliar with eval, it's basically unit tests for LMS and non-deter deterministic systems, uh, our methodology, uh, we basically have a suite of questions. I think it's about 22, uh, easy, medium, hard, and we've
[18:40] kind of hardcoded what the ground truth is. Uh, every time we make a pretty substantial change, we just run the agent against those questions. And then we have a judge agent, usually a big agent, smarter than the agent, more robust, that's acting as a judge. Uh,
[18:56] and it's grading the answers on accuracy, structure, and content. So, LLM as a judge isn't perfect. It's more directional, and it's just easy to test if we're if we see any regressions based on a change, we can then dive in and kind of see what happened.
[19:17] So full disclosure, I originally pitched this talk for DBT Summit which is in September. So we've only just started exploring uh the capabilities of this agent. We're only using about seven of the 37 tools on the DBT MCP. Uh we are still need to explore the semantic layer
[19:33] tooling. Uh code generation like that Jura ticket we submitted as part of the demo. Why not have an AI spin up a PR that the analytics engineer can just go in and review directly? So, I think the most interesting parts haven't even been built yet. That's kind of the exciting
[19:48] part is that we've only scratched the surface. Um, let me know what you guys come up with. Thank you. That's the talk. Sorry I talk a little fast, but thanks for sticking with me. Thank you.

Learn more about the Databricks Data and AI platform.

The information provided herein is for general informational purposes only and may not reflect the most current product capabilities or configurations.