Description Transcript
In this video, join Retool Developer Advocate Angelik Laboy Torres to learn how to build an AI-powered anomaly detection system with Databricks and Retool. This demo covers connecting Databricks, building a real-time dashboard, automating workflows, and adding an AI agent for contextual anomaly insights.
Read more 0:02 Welcome back! My name is Angelik Laboy Torres. 0:08 I’m a developer advocate here @ Retool and today, we’re diving into the beloved tool by data teams: 0:13 Databricks! 0:19 If you're managing financial operations, procurement, or working with 0:25 large-scale data analytics, you know how critical it is to catch anomalies before they become costly problems. 0:30 In this video, I'll show you how to connect Databricks to Retool. 0:36 And build a complete anomaly detection dashboard, automate detection with Workflows, and add AI-powered insights with Agents. Let’s do this! 0:45 We will begin by connecting Databricks as a resource inside Retool. So click on Resources in the top navigation bar. 0:55 As the name points out, this is where all of your integrations live. 0:59 So all of your Salesforce, your Asana, your Notion, your REST APIs, whatever you have, right? 1:06 And this is organization-wide. So whoever is in this space that has access to my instance, angeliklaboy.retool.com, 1:12 will get to interact with these resources. 1:18 Click Create new and search for Databricks. 1:23 You'll see the Databricks SQL option, click on it to begin configuration. 1:28 First, give your resource a descriptive name like "Databricks Analytics Warehouse". 1:37 You can optionally add a description to help your team understand what this connection is for. 1:41 Something like "Financial analytics and anomaly detection data warehouse." 1:49 I'm going to leave the tags as "Data Warehouse". Awesome. 1:57 Now here's a really nice feature - see that "Autofill using connection string" link? 2:05 If you already have a JDBC connection string from your Databricks workspace, you can click that 2:10 and Retool will automatically populate all the connection fields for you. 2:14 Just paste in your connection string and click Import. Super easy! 2:15 But I'm going to show you how to configure manually. 2:18 Under Credentials, first you're going to need your Host, your Databricks workspace URL. 2:23 So for me, that would be this thing over here. 2:28 Copy it. Awesome. 2:30 And then for the Port, usually it's 443 for Databricks SQL. 2:35 Then you're going to need your HTTP path. 2:38 I'm gonna go back into Databricks. 2:40 You can find that in your warehouse's Connection Details, 2:44 looks like /sql/1.0/warehouses/id, 2:46 and then I'll paste it in. 2:47 Awesome. 2:51 For Default Catalog, I'll leave this as default, 2:54 which is the hive_metastore. 2:54 That's fine by me. 2:56 And then for my Default Schema, I'm actually gonna put "demos". 2:59 Awesome. 3:02 I'm going to click this over here. 3:05 Awesome. 3:08 Under Authentication, I'm going to select "API Key Auth". 3:11 And then enter your personal access token in the API Key field. 3:15 You can generate this token from your Databricks workspace 3:18 under User Settings > Access Tokens. 3:21 It will ask what is this for? I say Retool, Generate, Copy it, Done. 3:28 You won't have access to it again, 3:30 so make sure you copy it actually. 3:42 Otherwise you can just generate a new one. 3:44 It's not a problem. 3:45 Click Create resource. 3:47 Awesome. 3:50 Perfect. 3:51 Now let's test the connection. 3:53 You should see a success message. 3:56 Encrypted on Server. 3:57 Yours should say the same thing. 3:59 And you're all set! 4:01 AND you only need to set it up once. 4:03 Awesome. 4:05 So there you have it. 4:06 Perfect, all works well. 4:11 Now that we're here in the Retool IDE, 4:13 Retool gives you total flexibility in how you build. 4:16 Maybe you're the type who loves visual development - 4:19 dragging components, configuring properties. 4:23 Or maybe you prefer writing custom JavaScript 4:25 and building transformers from scratch. 4:27 And if you want to brainstorm and iterate quickly? 4:28 That's where AI assistance can come into place. 4:30 The point is: you build 4:32 however you feel most comfortable, 4:33 the way that makes sense for you. 4:35 But here's the thing - 4:36 all of this data lives in Databricks. 4:41 Your ML models have analyzed transactions, 4:43 flagged anomalies, 4:44 and stored everything back in your lakehouse. 4:46 Technically, you could track down everything you need in Databricks, 4:50 but here's the reality: not everyone on your finance team 4:51 knows how to navigate the platform. 4:56 And even if they do, 4:57 it's not built for operational investigation workflows. 4:59 So what we're building here is a centralized, 5:02 user-friendly dashboard 5:04 that brings all those flags and anomalies 5:07 into one place. 5:09 So your team can see every detection. 5:12 This dashboard will pull from our Databricks tables 5:15 to display flagged items with full investigative context. 5:18 Let's get started. 5:19 For that, I'm gonna start out by dragging and dropping a table. 5:24 I'm also gonna be doing a title, so a text component. 5:28 Over here, I'm gonna call this Financial Anomalies. 5:33 Now what we have to do is make a query, right? 5:35 We've made the resource already, 5:36 so that should be pretty easy. 5:38 I'm gonna go into the code area, pages, 5:41 create resource query, Databricks. 5:44 So I'm gonna select the ID, the type, the confidence score. 5:48 I'm also gonna do approve vendor, employee names, 5:53 the approval limit, the spend. 5:55 Anything that is important in order to build this anomaly detection dashboard. 6:01 From this table, I'm also going to join it from this one, which is the transactions, 6:07 the vendors, the employees, the departments. 6:09 And then I'm going to order it descending by detected and then also 6:15 descending by severity. 6:16 And I'm going to call this... 6:22 All good. 6:22 Awesome. 6:23 I can even like browse through some of the information. 6:26 Let me see, OICHB, FastPay, Global Tech Supplies. 6:32 Now I'm gonna connect that to the table. 6:34 Gonna click here. 6:36 I'm gonna click on the query that we just made, 6:38 and I'm gonna select the... 6:39 Annotate only IDs, the main one. 6:41 Easy, we've already populated something 6:44 that is 10 times easier to read. 6:46 We have 50 results even. 6:48 I wanna take out some of the columns. 6:50 So I'm gonna go into this one, 6:52 and I'm gonna hide it. 6:53 I don't want the confidence score. 6:55 Hide this one. 6:56 I also wanna hide the approved vendor. 6:59 Let's get the vendor history. 7:01 So let's make another query. 7:06 I'm also gonna have to rename the table. 7:09 I'm gonna call this table. 7:12 The getVendorHistory has stuff like the vendor name, 7:16 the first transaction date as well as the approved vendor, 7:20 yes or no from the hive_metastore_demos_vendors 7:26 where the ID is selected. 7:28 So once I select it, I'm getting that history 7:31 for each of the ones that I'm selecting. 7:33 I'm gonna do now the getRelatedTransactions. 7:35 That is something that is gonna be important 7:37 for this dashboard. 7:39 Another one. 7:44 The other one that I wanna do is getVendorOptions 7:48 is what I'm calling it. 7:49 One, duplicate, another one. 7:55 So this one is listing all the vendor names 7:59 for the populating vendor filter. 8:01 We're gonna ask for that to be made by the AI prompting, 8:05 but for now, I'll even make the query for it 8:08 so it can like, pull from it. 8:13 Good, we have all the names, right? 8:15 All of the vendor names, amazing. 8:18 I am gonna do the department options. 8:50 Now that we have these queries, I want to make a container on the right side in order 8:53 to show the information of those selected so we can pull it in. 8:57 Awesome. 8:58 So. 8:59 [Music] 9:29 [Music] 9:43 We want these tags to be mapped 9:47 and then we're gonna do a function. 9:54 I'm gonna put like a line like a divider. 9:56 there you go 9:57 the stats 10:01 the stats are added to the stats, yeah 10:03 perfect 10:06 and i'm gonna call this one monthly 10:12 budget approved fall limit 10:16 amount 10:17 for the amount i'm actually gonna 10:20 do this one for the approval limit 10:22 i'm gonna do something similar and then the last one i want to do 10:27 monthly so something that was already there as well monthly button this all seems good to me 10:50 We spoke about having like a 10:56 more general view of what we select them, right? So for that reason, 11:00 a Key Value component. 11:06 Well, you will see what this is. Okay, perfect. 11:13 This is to demonstrate everything else that is remaining. 11:19 And for that reason, I can actually select the same things that are here, but for only the one that is selected. 11:29 I'll also do transaction date to the top. I'll do detected 11:35 add those fine invoice number whereas the this one I can eliminate. 11:41 So good. I would say the vendor category to know more about them. 11:46 I also want to do a first transaction date. I want to do an Employee Name. 11:52 The monthly budget not important. I'll put a divider there actually just to make it prettier 11:57 and then we can even make some like buttons at the bottom 12:03 in order to cause some reactions for our next video where we can do like a workflow for this. 12:11 So I will do button. 12:14 Perfect. And then in terms of style, I can even make this like a green, 12:17 something very easy. 12:19 And then I can make this a red. I can even like round out like the spacing of this. 12:22 I don't like the border so let's control that a bit. 12:26 Let's make this like a 20. I feel 12:29 I feel like I'm missing an approved. 12:31 Something that is able to tell me a little bit more about like, 12:34 are they approved, are they not approved, like just by looking at it, okay? 12:36 I'm gonna do map and I'm actually gonna create a function 12:37 for this in order to detect it. 12:40 So, there you have it. 12:45 There you have it, boom, showing up, 12:47 ay, and then we set it up to this side, that looks prettier. 12:49 Awesome, okay, let's move this. 12:52 Let's move this so that way this information can fit here. 12:54 Let's make this longer. 12:55 Ooh, this looks nice. 12:57 Okay, now that this is all done, this is pretty nice. 13:01 I can actually toggle between it 13:02 and then you can see the information like changing in real time. 13:07 Now we spoke about like multi mode. 13:16 So let's prompt for them. 13:18 (upbeat music) 13:19 So right now, it's actually going ahead and thinking and making a plan, 13:21 which is part of building, right? 13:24 You don't wanna go ahead and like alter whatever it's made. 13:26 You wanna work within the guardrails. 13:29 You wanna work within what has already been created by the user. 13:30 So in this case, is adding the filter components 13:33 in the table above, vendor has already been attached 13:36 to the query that we created, right? 13:38 Let me see this one, yep, same thing. 13:40 If I wanna do this query and run it, I do, 13:45 that seems fine by me. 13:47 Something that I wanna do because I do trust it, 13:49 I'm gonna go ahead and auto-approve all the queries on this resource. 13:54 I already know what it's trying to do, 13:56 I don't wanna spend some time doing any of that. 13:59 Whoa, very cool. 14:01 Hey, it looks nice. 14:03 Let's do corporate card. 14:05 Hey, there you go. 14:07 Okay, okay, okay. 14:09 Let's do security and consulting. 14:12 Ooh, we can select one at one, you can see. 14:16 I wanna do marketing, IT. 14:21 Cheskeys, you know? 14:22 So now that you saw that I finished building, it's prompt. 14:25 I wanna go ahead and add another thing. 14:27 To go back and like switch this mode, right? 14:29 We've coded, we've like done the drag and drop components, 14:32 we've prompted it, I can even prompt more, 14:34 but I don't wanna get my hands dirty today. 14:37 So, anomalies, there you go. 14:40 The value is gonna be this one here. 14:43 This is gonna be a standard, this is not a percentage. 14:46 Secondary, I wanna suffix text 14:49 and this is gonna say like anomalies 14:51 and I want an icon in there. 14:53 Do like a warning. 14:56 Do another one here. 14:59 And this one is gonna be like the priorities of anomalies. 15:03 Total. 15:06 And then I'm gonna do standard. 15:09 I'm gonna delete this. 15:10 I'm gonna put a suffix text and I'm gonna put anomalies. 15:14 Let's do one final statistics. 15:16 This is for the weekly anomalies 15:18 and this is last seven days. 15:22 I have created a function for that. 15:24 And I'm also gonna do a warning. 15:25 Perfect. 15:26 And then I can move this again. 15:29 Okay, so we've added the main basic priorities. 15:32 Now what if I took a little bit more time, right? 15:34 The whole point of this isn't the fast development. 15:37 It is what you can keep improving over time. 15:40 The fast development is there to help you think and experiment more. 15:44 And so with more time, this is what I came up with. 15:48 Okay, so we still have the table. 15:51 I simply just change some color. 15:52 Then you have the active anomalies of priorities. 15:55 I like order them better, have better color, which was approved. 15:58 which ones haven't been approved. It's all filtering in real time. 16:04 I can select something like a medium 16:10 So congratulations we did it!!! 16:13 You have created three very powerful tools: 16:15 a dashboard, a workflow, and an agent 16:18 all powered by your Databricks data. Yet again, the intention was to demonstrate how you can intertwine each system 16:25 or how they can help complement one another. 16:29 Plus… to show how easy it is to integrate with your stack and start creating 16:34 with the mindset being: not “oh how do I connect that” but rather, “oh! What can I do?” 16:39 Now it is your turn, I want you to create a dashboard using at least one resource! 16:44 Relay it back to me by sharing it to the community at Retool Forum. 16:50 Really excited to see what you come up with 16:54 but for now, thank you so much for watching. I will see you in the next one!
