Stop AI Agents from Guessing SQL with Semantic Operator on Kubernetes - Manabu McCloskey, AWS

See, my name is Manab Mlosski. I am a solutions architect at AWS. Uh today, um I am going to be talking about this project called Semantic Operator. Uh which is going to be open source soon and we are hoping to be um donated to the um KubeF community. All right. So, let's uh get started. So you know [sighs] we talk to uh our customers um a lot of things that we hear from our customers that they want to incorporate AI into their day-to-day operations. Um but they are seeing some inconsistent inconsistency in [clears throat] the agent's response. Um you know uh for example some of them are just implementing you know text to SQL and as you can get probably guess you know because of that the uh the returned answer uh differs every time you ask because the return data is different right. So what this project aims to do is to make that uh retrieval process like governed and deterministic. So if you have a you know certified model um then the same request comes in the same data is always returned and it is governed by the um policy that you define. So um let's consider the you know the question like what was the revenue last quarter right? Um this could mean a lot of things. Um you know depending on who who you ask and you know uh what you mean by that right. Um so it sounds simple but if you let the agent um run the SQL um based on the questions only uh you end up with the wrong answers many times. Right. So we actually benchmarked this against the u TPCDH um uh data set we have and you know just based on the these questions we actually saw 28 wrong answers um across 90 prompts and obviously if it's wrong this you know this much then it's really not useful right so there has to be a um you know uh standardize this and make it uh more deterministic so uh this is where the um Apache RC project comes into play. Uh Apache Apache RC is a um a open source project that attempts to uh define the the semantic model of these data sets. So these are being contributed by you know lots of the uh um data foundation teams or data platform teams uh could be like from people from data bricks uh snowflake Nvidia you know these are larger corporations that really trying to make it uh have a standard way of communicating what these data set means right so uh on the right you can kind of tell what it does right so we define some data sets and then we also define some relationships here and then we are also um you know Let's find some metrics here. Um so for this particular um demo file we are saying that um we have a single metric called total sales and this total sales uh is expressed as um single expression like so. Um we'll go into this more in detail when I do the demo. Uh but right now let's just understand that OC is a standard spec uh that is being developed under the Apache Foundation. Okay. So um you know specifying the um the Aussie spec is nice and all uh but Aussie currently isn't really aiming to go beyond spec uh meaning that you can define these specs but the you know how to use that spec is up to people that actually is using it right so this is where the um semantic operator comes into play. uh so semantic operator uh bridges that gap between the actually getting the data and defining the spec. So we take the Apache u spec and then turn that into um own custom resource within the kubernetes and then apply that meaning against the uh query engines. Right now we support Starox and Trino u but we are um uh planning on adding more and when you're using the operator um it comes with two path essentially uh one is to create that model uh so this model would be just the custom resource definition right that you I'm sure we are all familiar with that and then finally you have using the consuming the model itself so they when you're consuming a model you can use either the MCP server or the rest server that we uh provide and then run the um rest query against that and then you get the data um the same data every time. Um again I'll go through this uh in detail when you're doing a demo. uh but the when you're writing the uh model uh the first thing that uh you should be doing is to uh use the included uh CLI um utility and then you uh use that to uh run against a schema or whatever right so the model here is that we help you automate uh certain things by looking at schemas right so you know table uh information like table you know column types all that stuff but We need people uh to certify that meeting. So we can we will not do the entire uh end to end. Um so we expect people to uh use the utility to get the scaffolding working. Then uh define the meetings and how to use it. All right. And then uh the governance is a big part of this. uh we allow the you know agents and the uh rest APIs to uh get metrics columns rows uh but we can also define uh who can um access these metrics and columns and rows and because depending on the uh the role uh your organization have you don't want everyone to have the same access to the you know same metrics and columns rows right so the governance is built into the operator And we also have um you know uh integrations with commonly used um policy engines. Uh for example we have integrations with OPA ranger and all that right and so we can actually um the spark the semantic operator uh can call out to the uh the external uh decision points and uh decide if the uh request should go through or not. And uh we also have you know some um orth token integrations as well. Uh so you know depending on how your uh engine configured you might have a um you know uh integration with like ranger already in that case we can just pass in the um the you know token right through or you can also go through a token exchange. Um that way you know you have a more limited scoped tokens and then the request uh follows the same governance path meaning that the agent and rest API they all go through the same endpoint. Therefore um once you've defined the governance model and the you know metrics um that you want to expose and all that goes to the same operator same endpoint and you don't have to configure you know um specific things for MCP or specific specific things for rest. So really just simplifies the way that you're using this operator right. Um so that is the uh very over a brief overview of how the everything works. Um I think uh it really really helps to understand or look at the demo first. So I am going to see show you the demo. Um so for the demo setup I have a uh kind cluster running. Uh this has the data hub um running. Uh so data hub um acts as the um data meta metadata uh setup. So you know you can define the meanings of these data right. Um it's often used to describe a you know certain columns as a [snorts] um you know say like PII data. Uh you can also add description to you know these each individual columns and you can also define you know lineage. You can also define um uh what you call it you know the um um the uh metadata anything around that right so um so uh so I have that running in the kind of cluster and I also have a styox um execution engine running uh so this syrup is going to be responsible for actually getting the data u back into the user's hands and I also have polaris running pro is just a u you know iceber catalog API and so it just looks at the iceberg uh storage layer and then um uh it gives the starox the uh schema data and everything so that stars can actually use it and get the data that requires um so this is the the you know setup I have right now um so if I were to um open up my terminal um so So the first thing that um I said about using this operator is to uh write a um custom resource right. So if I were to run this command if I hope I can find it. There you go. So uh like I mentioned earlier uh we include certain um capabilities uh what are utility uh command lines that allows you to um get some skeleton data and about the um the data set that you want to um you know use with your uh spark semantic operator. So here um I am saying that um you know I want to run this command and I want the RC control to derive. So meaning that I want you to connect to a um this engine and the engine name is Starox and then the catalog name that I want to use is iceberg and then we also saying that the database uh name is this guy right SA uh S customer and then I'm also I am also saying that the um the model name is uh uh SSA customer health and then also the name of this uh custom resource should be this and then finally I'm also saying that you know the um the this should be written to this particular yamo file. So if I want to run this command uh it's going to just talk to the um you know starox engine and get the schema information and everything. So, if I were to look at this file here, um, see, I'm just going to this and let's look at this guy. Um, okay. So, this is the uh custom resource that was um generated um by the uh RC control uh CLI. Um, as you can see here, uh this is just standard uh custom resource, right? So API version kind all of stuff [gasps] and then um the interesting bit is this um Aussie um things right here right. So this Aussie field uh contains the entire um you know a spec here. So you know um I think earlier you saw that the you know spec is you know based on YAML and it has all the different fields right. So one of them is like the name. So name will be the name of this model and that is you know we defined that in the uh command line that uh we saw and then we also have these data sets. So the these data sets are um you know derived uh using the starox schema. So we can see that the we have the um um account data set uh this has a certain fields. So these fields in this case just corresponds to the column names um of the um um the underlying database table [gasps] and we can see that you know here has some information like you know account ID, account names you know all the things that you'd expect in an account uh table right and then uh if you look at the other um elements in this array you see that you know we also have like you know count future entitlements and all that stuff. So all these data sets were pulled uh based on the Starlock schema and I'm just going to uh minimize most of these. You can see that it pulled essentially all the tables that's available within this um u this table. Um here you see that the I have the relationship uh bits as well. um as a recall relationship just defines the um you know the relationship between the tables right so like certain columns respond to this column and all that um these are left empty because it's very difficult to infer this kind of information um so this has to be done by a human and we also have the metrics these are metrics that are u defined for agents to consume and these are again left um empty because again it's very difficult to um enforce that. Um so this is nice in a way that you know it's very um it does a lot of the work for you but it's really not enough right. So we have a uh integration with data as well. So let's see if I were to run this command instead. So the RC control has a integration with data and it can um talk to the data instance to get even more information um other than the schema information. So here the commands are similar. uh we are saying that you know use the engine uh the engine name is storeox and then use this catalog you know blah blah blah right most of it is the same but now we are saying that hey uh also look at data hub um so we want to enrich the data set and use the data hub as the source of that metadata and we are giving that data url here and then we also giving that data platform um name is iceberg and then we are also saying that the you know data data set uh prefixes this and then all the um um information that's needed uh for this u control to derive the um um you know information from the uh uh data. So if I want to run this command um it's going to write that into this file um just as scaffold and enrich. file. And if I look at the file here, um you'll see that it generated a very similar looking um file, right? But if I were to do a div between these two files um uh you see that there are um whole bunch of differences. Um big differences is the description, right? Uh those descriptions uh were pulled from data. Um you know previously on the right side uh descriptions were not available because you know without data we don't know we don't know what these columns means right so we can uh look at that uh get all this information from the um uh data so you can see that all these are pulled we also have a context I defined these are pulled from um the um datab as well and then I you can got all these different things but for the most part uh defense parts are just the descriptions themselves So, I'm just going to collapse most of these. Um, I guess I can collapse. Um, I got to scroll down. Let's see. Um, yeah. So, um, yep. So, you can see that, you know, essentially all uh changes just description, AI context, all that. Um in the future we'll probably expand the um you know integration with setup uh but right now uh it can do certain things but not all. All right. So the expectation we have uh from the um users is that you know uh we pull most of these information for you but we expect you to you know uh define these metrics and um you know governance model and all that for us. That's why the you know the relationships metrics would left uh empty as I said it before. Okay. So I am not going to fill this myself right now. Uh it's just going to take forever. So I have a field uh in version already here. Um so this is the completed version that we expect people to create. Um here we are saying that this is a SAS revenue model. uh in this uh SAS revenue we're defining certain uh data sets right most of these are the data sets that we we saw in the earlier but removed some of them because we don't really need for the revenue operations and some of them um have you know um more descriptions and all that stuff um so I'm just going to um collapse this because data sets themselves are really you know um interesting at this point Um but here we have relationships defined now right. So we are saying that um you know um this column account ID corresponds to this account ID in the um account ID table. Right? So these are like very obvious um [snorts] relationships but there might be more uh involved um relationships that you might have to define. But by defining these relationships you can um it helps the u the operator come up with the uh deterministic SQL query that we can use run against these data sets. Um let's see here. Yep you can see here here's a little bit more um you know uh complic complicated uh scenario. So we are saying that you know date them um means date SK here and then we just uh saying that this is the relationship that we want to present to the um um operator and the users. All right and then the most important bit is this metrics here. Um so metrics is filled out now right. So uh we have a u matrix named uh monthly recording revenue. Uh so just uh defines that you know current snapshot of MR. Um and then we can you know say give some description about what this metric means and uh this description everything is fed directly into the AI so that AI can reason about what metrics is needed to get that information. And then here we are also you know defining a uh you know uh expressions uh to get this information. uh it's you know these are mostly just some um expressions so they're very very easy to um you know express but uh right now we're just saying that the um MMR uh is um based on the sub monthly and current MMR and then sum everything um but yeah so these metrics that we just defined uh these metrics are going to be used um as the certified metrics meaning that these are the uh matrix that is the source of truth right uh so like any other method of trying to get this same information is invalid. Therefore we are giving the agent the you know the the source of truth when it comes to trying to understand what that the um you know users uh prompt means. Okay. Um and then finally we have the governance defined here. Um so you can define certain roles here. Uh for example the platform analyst role. Uh this role is allowed to read all metrics. So all metrics would be defined here. Right? So platform analyst can read all of the metrics but they cannot read these information. Uh for example um you know it cannot read the primary account contacts phone name, email, phone number all that stuff. Right? Uh similarly we have the uh North America customer success role. Uh it's allowed to read all metrics but it cannot look at the um negotiated discount percents and all that stuff. And in addition to that um the uh this particular role cannot look at the um the region other than the North America. So you can see that you know we can define whole different or different uh roles and then we can enforce all that uh for the um uh through the operator. So once you apply this um the um you call it the uh the yammo file um it's going to get into the ready state. So I already have this everything running. Uh let's see. So if I were to do K get semantic model um in this name space uh we see that we have the you know s adoption s revenue and s support uh semantic models available. Um as you can see that uh this has already validated and they are published and they are uh ready to be served. All right so this is how the authoring process works. Um you know there's definitely a lot of the manual steps but it's um it's designed that way because we expect the you know end users to uh uh provide their own uh business logic right okay so let me uh switch over to the other um windows share the other screen see Um, thickness this guy. All right. Um, all right. So, let's see. Yeah, this is data. I'm not going to going to go through that. Um, so this is a just a web console u that I just made. This is a throwaway web console just to show the demo. Uh, this does not come with the uh se the semantic operator itself. Uh [snorts] this just purely for demons purposes. Um I'm definitely not a um production radio or anything like that. All right. So um let's look at the uh different uh models. So right now we have these models available. So if I were to just refresh this really quick and change that to revenue, right? So um once you apply the uh the SAS revenue um JAMO file that we just generated this is what you get right you have some certain metrics or certified metrics um you know uh MR renewal um annual value and all that different things is now ready to be served. We can actually see this if I were to run the rest commands or yeah the HTTP commands I guess. Um so this is just the uh you know the console just shows you how um the actual request would look like through the um u rest endpoint. So uh what I'm going to do is run the uh the get models u or get request against the v1 models endpoint. Um and you can see that we have all these models that's available. So we have the uh SAS adoption SAS revenue SAS support all the stuff that's um you know we just applied and this is we can see because we are using the platform analyst um role but if I were to change this to say one of the other ones right so finance um finance finance uh analyst like so uh we only see one um model here now right uh Because in the um uh in the other YAML files we said that the access to the other u metrics are restricted to certain people and finance analysis is not from them. [snorts] Therefore finance analyst can only see the s revenue um model and we can look at the list metrics as well. So you know we know uh what the um uh the model name is. So su revenue and if I were to send the request again uh we see that the metrics available um that's you know ready to be served. Um again this metrics come straight from the uh custom resource we defined and same thing we can do that right we can just look at the dimensions well uh all right different ones s like so and we see that the dimensions are also available here. All right. So, um for this demo, I have created just some basic questions. Um but before I actually use the uh my little chatbot, um I'm just going to show you how this works. Um so now, um we know what the models uh metrics are, what the dimensions are like, right? we can actually run queries against it. So here I'm saying that hey uh get me the revenue uh get me the revenue uh model um but I also want to see the you know the account names contact values negotiated discount percentage all that stuff right and then we also want to order by this particular uh uh the uh the contract value here so if I have to send this request uh you'll get the uh this response back right um oh Sorry response is here. Um this response um you know the for this particular question what we are saying is that hey man what is the um the you know highest contract value that we have for this particular uh accounts or that all the accounts we have and the answer is no systems and the you know this uh value or this um response never changes right as long as the metrics and uh the order by course are the same um the we get the same response. almost every time uh that is because we are generating this um SQL query. So instead of having the agent run or create the uh SQL queries uh using the methods or the you know training data um we actually generating the set uh the SQL query um deterministically. Uh so if you have the same response going into the uh semantic operator, we get uh we generate the exact same uh SQL query. That's why we get the exact same data every time. [gasps] Um and we can also see that the let's see if I were to change the um [clears throat] question to a different thing. So here I'm I'm running a um post command right saying that hey [snorts] I want the current annual recurring revenue um based on the highest um sorry I want the current annual recurring revenue um based on the regions right here I'm saying so if you give that metrics and the dimensions I want the and the columns we get is you know North America EMA you know um APEC all that stuff right um but if I were to run this as a a um customer success row um I am only going to get the um you know data from the uh North America only uh because we defined the um this role right in a customer success role they can only look at the um uh the North American uh regions only and you can see it here right we are saying that where the account region must be NA and this was all defined in the um [clears throat] uh in the uh custom resource as we saw before. Okay. So this is how uh things work uh in a very um you know high level. Um we can also um do this against the MCP server as well. Um so let me just uh run that same command again. See that test. All right. So I am just going to ask a some canned um question here. Right. So, which account has the highest total contract value and what um discount did they negotiate? Um and you can see that the you know um you know what uh I mean it came back really quick but the um the you know the um you can see that the this value uh it lines up with what we saw earlier in the rest um console as well. Um I was hoping this would actually show me the steps. Let me see. Um create conversation maybe. Okay. I think the uh it was cached. It wasn't really um doing that. Um all right. So this agent um gave the you know same um uh what you call same answer right uh but you can also see how this was answered uh so you can see that you know we actually uh looked at the list model score so this is just another tool um that's exposed in MCP and then it also went to data hub to get the you know any relevant information that might need and then get the search metrics that's required And then finally I run the query here. Um but if I were to run this as a different row, right? So if I were to say uh do it as a um see performance I guess and then ask the same exact question. Um the agent is going to try and do the same exact steps here. Um but the um it tells you that this account um you cannot really get the information that's required because the the role that I selected uh platform analyst role is not allowed to get the information uh from certain ones. So if you look at this you know um uh the steps it took to get this information um looks fairly similar right you just do the you know list models get the metrics dimensions everything and then go to data hub so so far it looks good but the ex finally when you try to get the actual metrics here uh you get an error because this narrow is not allowed to get this uh contract value. So you know this is just a demo of um how this right operator works and um but yeah we are hoping to uh open source this and uh donate that to Google. Uh but that is it for my demos. Uh if you have any questions I'll be more than happy to answer. >> Looks like we have maybe time for one question that we need to start. uh Christian's talk. So actually do you want to answer question Christian's question which is what is the added value of storing the Aussie models as CRD in this case? As far as I understand the most important part of the tool is not really the operator but more the MCP server that the agent talks to. >> Yep. So the the the the point of having the RC spec is that we can reconcile against that and then generate and validate the u defined metrics and the um schema data, right? Um and then we can also use that data to version them. Um for example, you know, if something say a um custom resource gets updated that um you know um uh the previous version might work but the next version may not work. So by having that different uh actually having the R3 spec uh in the um uh custom resource itself we can version them uh fairly easily. >> Fantastic. Where can people find you if they any other questions or just want to connect? Um >> yeah uh just to ping me on the CNCF Slack um [snorts] and I'll be more than happy to uh answer any questions. Thank you everyone for your time.