Automating Semantic Model Migration to Databricks With AI and Deterministic Translation

Summary

HP developed an AI-driven migration pipeline that automates approximately 70% of the work required to migrate semantic models and dashboards from legacy BI platforms into Databricks AI/BI by separating deterministic semantic translation from AI-assisted widget generation.
The four-step pipeline uses deterministic code for semantic layer translation and publishes metric views with feedback-driven correction loops, then uses AI agents guided by a carefully designed LV-dash JSON schema to generate dashboard widgets.
The approach scales from simple dashboards to complex multi-page visualizations while maintaining semantic correctness, reducing the disruption of migrating hundreds of dashboards while simultaneously serving existing stakeholder reporting needs.

Automating Semantic Model Migration to Databricks With AI and Deterministic Translation

Migrating hundreds or thousands of dashboards from legacy BI platforms to Databricks is slow and disruptive when done manually. HP developed a scalable AI-driven approach that combines deterministic translation of semantic layers with intelligent agents, structured JSON schemas, and feedback-driven correction loops to automate 70 percent of migration work.
Jeff Mabeck walks through the architecture that solves the hallucination problem by engineering context. Rather than letting LLMs generate semantic layers directly (which failed on complex real-world data models), HP uses deterministic code for semantic translation and reserves AI agents for widget generation guided by a carefully designed LV-dash JSON schema. Learn the four-step pipeline, design principles around schema validation, and how this approach scales from simple dashboards to complex multi-page visualizations while maintaining semantic correctness and enabling rapid adaptation as Databricks AI/BI evolves.
🤝

Chapters

FAQs

Why did HP develop an automated approach to dashboard migration?

HP has multiple teams across the organization actively migrating into Databricks AI/BI, and doing those migrations manually while simultaneously serving stakeholders with critical business intelligence is slow, inconsistent, and unsustainable at scale. This video describes how the volume of hundreds of dashboards with deeply embedded business logic made automation a necessity rather than a preference.

Why does pure LLM generation fail for semantic layer migration?

This video explains that when HP initially attempted to use LLMs to generate semantic layers directly, they failed on complex real-world data models because the models hallucinated relationships and metrics that did not exist in the source schema. The solution was to handle semantic translation deterministically to guarantee correctness and reserve AI agents for widget generation guided by a structured JSON schema.

What is the LV-dash JSON schema and why is it important to HP's migration approach?

The LV-dash JSON schema is a carefully designed structure HP created to provide AI agents with precise context when generating Databricks AI/BI dashboard widgets. This video explains that engineering this schema was critical to controlling what AI agents produce: because the schema defines valid widget configurations, agents cannot generate outputs outside the supported specification, which prevents hallucinations in the widget layer.

What are the four steps in HP's dashboard migration pipeline?

The four steps are: deterministic semantic layer translation from the source BI platform, publishing metric views to Databricks with automated correction loops that fix validation errors, AI-agent-driven widget translation guided by the LV-dash JSON schema, and final validation and publishing of the completed dashboard. This video walks through each step with a live demo using a Jupyter notebook.

Full transcript

[00:08] Okay, good afternoon. Can you guys hear me? All right, I'm Jeff Mayback, uh principal analytics architect at HP. And I was originally going to be giving this talk with my colleague Utkarsh Sidhu, but unfortunately he wasn't able to be here today. So, he did send a pre-recorded video for a demo. So, I'll include that.
[00:31] Well, interesting. I went to the next slide. I'm seeing the next slide, but you guys are not. So, I don't know if the Aha! Ta-da! They did it from the back. Thanks, guys. All right, so of course the forward-looking statement disclaimer applies. And, you know, today I'm going to walk you through, you know, how we approached
[00:48] a challenge that I suspect many teams are facing right now, which is how to take semantic models and dashboards that live outside Databricks and migrate those into the Databricks platform. So, we developed an AI-driven approach that allows us to automatically create
[01:04] metric views and AI BI dashboards. Um and what I want to do today is just cover some of the key aspects of the architecture that we developed, what worked, what didn't work, and what we did to make it really a fully uh scalable solution.
[01:23] Okay, so similar to what you may be seeing at your companies, you know, multiple teams across organizations at HP are actively migrating into Databricks AI BI. Um now we're seeing this shift for a number of reasons, right? Some of those key reasons are that AI BI dashboards in Genie are
[01:39] showing rapid improvements. And we've seen a lot of that over this past year, and of course a bunch of things just in the last couple of days. Um Unity Catalog, of course, not only offers this unified governance, but also this unified semantic layer with metric views, and now domains, and then
[01:54] announced recently with glossary, um that's really important. And of course, the um you know, the cost is usage-based and doesn't require additional license costs. So, for these reasons, you know, we're seeing organizations choose to integrate more BI and analytics directly
[02:09] into Databricks. Um but the challenge is the time and effort that it takes for the analytics teams to do those migrations while they're simultaneously continuing to, you know, serve their stakeholders with critical business intelligence. Um so,
[02:25] you know, when you have hundreds or thousands of dashboards and deeply embedded business logic that's across multiple, you know, multiple layers, if you try to do these types of migrations manually, it's slow, it's inconsistent, and ultimately it's too disruptive to your ongoing business needs. So, we
[02:42] really needed a way to automate and accelerate the migrations. So, our initial approach was straightforward straightforward. Use agentic AI to create Databricks objects directly. Now, in our case, all our data
[02:57] already is in Unity Catalog tables and views. And for us, we have a lot of ThoughtSpot dashboards built on top of that data. And in ThoughtSpot, the semantic layer is primarily defined by model TML files. And the visualizations are primarily defined by Liveboard TML
[03:13] files. So, effectively, we just need to generate Unity Catalog metric views and lv-dash.json files that describe the details of that Databricks AI BI dashboard. Now, in practice, we ran into some issues um
[03:28] with more complex dashboards where, you know, the outputs weren't always um valid and the results weren't reliable enough for production. So, you know, we quickly discovered that at least for anything beyond initial test dashboards, AI generation alone really wasn't
[03:44] sufficient and we needed significantly more structure. So, we evolved the approach um so that we can engineer the context in order to get repeatable and scalable outcomes. And the key elements are shown here. You know, we pass the input, we
[04:00] parse those input artifacts, so we can control the context. We use structured schemas to define the expected outcomes. Agents generate outputs using controlled context in each step, and validation loops enforce correctness. Now, the
[04:15] LV-JSON schema that you see there is really a core part of the solution that we developed, and you'll see that in some more detail during the demo. Now, this structure does give us some, uh, key benefits. Gives us a clear way to map ThoughtSpot concepts to AIBI with
[04:31] error correction. Um, it gives us a structured approach to handle that rapid pace of new AIBI development that we talked about, and it gives us the ability to take learnings from our partners across different organizations and put that directly into the schema. So, with this kind of approach, we're
[04:46] able to get repeatability across migrations. Okay. So, if we take a look at the full pipeline, it's made up of four main steps. Step one, we translate the semantic layer. Step two, we publish the
[05:01] semantic layer with correction loops. Step three, we translate the dashboard widgets. And then in step four, we validate and publish the dashboard with correction loops. So, there are a couple of key design principles that I want to highlight here. Um, the first is using the schema as
[05:18] contract. So, every agent-generated artifact must conform before being published. And the second is using feedback-driven correction, where the system validates and iteratively fixes issues. And that's the combination that makes this pipeline reliable.
[05:37] Okay. So, I want to take a look at each step in a bit more detail. So, step one, we generate the compliant semantic layer, where we take a ThoughtSpot TML package that I described earlier that contains models, tables, SQL views, formulas, and we just translate that
[05:53] into a Unity catalog metric view. Now, to do that, we built a deterministic translator that handles a lot of the complexities. So, it allows us to resolve joint paths and model topology, translate dimensions and measures,
[06:09] preserve the calculation logic. We also make sure that any SQL views are published first, any dependencies get resolved, naming conflicts are handled. All of that stuff happens, and then we generate a migration report that shows where formulas are either high
[06:25] confidence, they're approximate, or they really require review from the analytics team. Now, when we started this project, this step one was LLM driven. And it worked fine with our initial test dashboards, but as we got into the
[06:40] complexity of real dashboards across multiple partner organizations, often those had very large and complex data models. We found that LLM driven approach was just too prone to hallucination. So, what we did is we ended up having to convert step one into a fully
[06:57] deterministic approach, and that's provided a lot more reliable performance across our teams. And that's really critical because the semantic layer really is the foundation for everything, right? It's not only the foundation for the dashboards that we're migrating, but it's also the foundation for the new
[07:12] Genie ontology we heard about yesterday for all the new Genie capabilities that are coming. So, getting the semantic layer right is absolutely mission critical. Okay. So, once we've generated the semantic layer, we need to publish that, and we use correction loops to do that.
[07:28] So, we use a publish and fix approach where we take the generated SQL definition of that metric view, we execute via API, we capture any errors back from that API, apply targeted fixes as needed, and then retry if we have to. So, we use some guardrails during this
[07:43] auto correction where allowed fixes are really only for syntax and structure updates, but we intentionally avoid auto fixing everything and we really require manual interventions for any semantic issues. And that allows us to enable the
[07:58] automation without introducing incorrect semantics. All right. So, in step three, we migrate the dashboard widgets. So, we take ThoughtSpot live boards, we translate those into Databricks AI BI
[08:14] JSON to describe the details of the dashboard. So, to do that, we've got to parse the tabs and visualizations, we map data sets to metric views, we're preserving filters, labels, axes, etc. Um and and our recent releases down in
[08:29] the migration fidelity areas where we've really been focused on making sure we can cover the full suite of the AI BI charts and tables, make sure we have the filters covered, the global filters, page level filters, widget level filters, and maybe new filter types that are coming. Um
[08:47] So, um we also have to cover on the the far side there, the layout. Make sure that we match as close as we can the layout that we had in the original dashboard to what we're going to have on the AI BI side. So, for this step, unlike in step one, the AI widget generation has worked
[09:04] really well for us. And that's because it's guided by that LV dash schema that I described and you'll see a little bit more in just a moment. Um Now, another important aspect that I want to point out here is when we we migrate these widgets iteratively. So, we're really only
[09:21] letting the LLM handle a single widget transformation at a time. And so, that approach really ensures that we have only the necessary context provided to the LLM and then that allows the process to scale from small simple dashboards that have a few widgets all the way up to
[09:36] really complex dashboards that have many pages and many visualizations per page. All right. So, now that we have generated that AIBI JSON file, we need to validate it, publish it using correction loops like we did before. So,
[09:53] it's really that schema validation that makes the structure self-correcting. So, we take that generated LV dash file, we validate against the schema, we have an auto-correct agent that will fix only the validator reported issues, and then a publish agent will publish the
[10:08] dashboard and use the API feedback like we talked about before for any final corrections. So, that gives us an automated migration pipeline that is self-correcting, repeatable, and scalable.
[10:24] All right. Well, since Utkarsh couldn't be here in person, he did record a video to show you in a bit more detail how the system works, and I think you'll find that really interesting to see.
[10:43] Hello everyone. Hope you are having a great time. So, in this demo section, we will quickly take a look at the setup that we have for migration of the dashboards. So, it is primarily a Jupiter notebook which our users will be using to do the migration. So, before starting with
[10:59] execution of the Jupiter notebook, they'll have to export the ThoughtSpot dashboard that they want to migrate, which will basically give you a package that contains ThoughtSpot DML files, which has the
[11:15] description of the live board, which is the visualization, then the model files, table files, and the manifest dot YAML. So, once this is done and put in a folder, the our users
[11:30] can just come to the notebook and then specify the folder for which the migration needs to happen. So, that is the only configuration that is needed in the notebook. The actual
[11:47] configuration of the catalog and the schema where the metric views need to be published will be captured as part of migration config.yaml which is which will be placed along with the ThoughtSpot DML export that was taken.
[12:04] So, once the migration configuration.yaml is done and the manifest folder is specified, they can hit run all cells to get the migration done. And as Jeff has mentioned, our whole migration workflow contains of four key
[12:21] steps: creation of metric view, publish metric views, migrate dashboard, and publish dashboard. So, we have done it in this way so that there's logical segregation across different steps wherein the users can intervene if at all the
[12:39] agents are unable to solve the issue by themselves. So, let's take a quick look at each of the steps and log messages. So, create first step is creation of the metric view which is purely deterministic in our case. We have a
[12:54] code written down which does it. Basically, creating one metric view for every model file. We also embed all the relevant formulas that are present as part of ThoughtSpot
[13:09] live board. So, here is the output from the first step, creation of the metric view. And we also provide a nice report that says how many of the fields were converted with high confidence and lower confidence lower confidence fields. It will be good if the users can review
[13:26] them. And they can take a look at the generated metric view file after the step one. So, this is that particular metric view. Here we can see it is a SQL file primarily. Basically, it's a SQL statement which can be executed using
[13:42] Databricks execution API to create the metric view in the specified or configured dashboard, sorry, schema and catalog. So, here we can see it has a source dimensions defined
[13:59] and measures defined as well with different aggregates that were necessary. So, once this step is verified by the user, they can move on to the step two, which is publishing of the created metric view. So, this is this is driven
[14:14] by an agent. Here we can see some of the agent messages. So, the first job it is supposed to do is try publishing it immediately without having to worry about the contents of the file. So, in this particular case, it was able to successfully publish it. So,
[14:32] it just finishes the job, gives us a summary, and exits. And And if at all there were any issues, it would flag those issues and ask user to intervene and fix those errors before moving to the next step of
[14:47] migration of the dashboards. So, the migration of the dashboard is where um the visualizations get migrated, widgets get created, pages get created, global filters get created, and all of the filters at the widget level also get
[15:04] configured. So, this is this can be a quite a long process because we are iterating at every widget level across all the pages in a given dashboard. So, once all the migrations are done for filters, tabs, pages, or tabs,
[15:21] Um, uh, the final lv-dash.json uh, will be created. Uh, we will take a quick look at that. Before that, um, let me just highlight this one step here. So, until the last validation step, everything is a uh, land graph based um,
[15:37] LLM workflow, predefined workflow, wherein um, the loops have been defined to iterate through tab and uh, uh, widgets. So, the last step is the validation, which is the super important step to making sure that structurally uh, the generated lv-dash
[15:54] is um, valid uh, when we upload it to the uh, EIBI uh, um, yeah, in Databricks. Uh, so, in this for this validation, what we have done is we have created a Databricks dashboard schema.json. So, we
[16:11] have basically created this schema. Uh, which is pretty much I I would say is the heart of this entire uh, system the underlying of it, which brings in the robustness and reliability that we are able to uh, get for migration migration
[16:27] across uh, large number of dashboards and uh, complexities. So, this one uh, we did it uh, by um, reverse engineering uh, some
[16:42] from the lv-dash that we got from uh, Databricks, uh, which you could export from Databricks. And one I would I would just highlight few things uh, that worked very well for us. So, here you can see this is not just
[16:59] uh, JSON schema that specifies what lv-dash should contain, should not contain. It also specifies in as part of description the mapping information when we have to go from hotspot to Databricks or
[17:15] that respective objects. So, that is super important when we provide the JSON to the LLM during migration process. And you could see that in multiple
[17:32] locations. So, in this particular case, the widget type mapping has been specified. So, likewise, right? I mean, enums, what is actually valid. Um So, descriptions play a huge role when it comes to
[17:48] reliability of the LLM generation. And the schema itself, with schema validation, which is a deterministic process, the LLM um in this particular in this case, it's an
[18:03] agent validation agent, it will immediately get to know the issues that are present, and it will be able to accurately fix it because of the deterministic validation. So, that ensures that we always generate an LBDash, which is structurally consistent with the with
[18:22] what database that it expects. So, yeah, that's that's pretty much all about um the creation of the LBDash file itself. Let's take a quick look at the file. So, this is the final file that has been generated which has the
[18:37] required sections of data sets, pages, and widget arrays, um everything, right? So, which has been structurally validated. And if and also the values of certain fields based on the schema.
[18:54] So, now moving on to the final step, which is publishing of the dashboard. Uh this is again done by an agent, uh which will try to fix if at all there are any minor issues. Some of the Sometimes the issues could be that, okay, there are fields um which are having this Not the fields, the widget IDs that are having
[19:10] same names. Um it could the agent could automatically fix it, but if if there are any other complex issues which Databricks is throwing, then user will be asked to intervene. In this particular case, there were no issues, so the summary doesn't contain
[19:26] any issues in related to that. But finally, and then we get a published URL link where in you can take a look at and Yeah, you can access the published
[19:41] dashboard. So, these are the four different steps, and as we could see, it's an amalgamation of deterministic code conversion of automatic view, then followed by the LLM workflow doing the widget
[19:57] conversions, and agents trying to publish and fix if at all there are any errors. And it is the solution is fully automated in the sense that the publishing of metric views, publishing
[20:13] of dashboards created is done through the official APIs from Databricks. So, in essence, there are a lot of feedback loops in the system that ensures reliability and
[20:28] consistency across different workflows of migration. And as I mentioned earlier, at the heart of the system, we have this
[20:43] JSON schema for the LB dash so that we are always structurally consistent, and the values take on the allowed um list values, right? So, yeah, that's pretty much it from the demo area. Over to you, Jeff.
[21:04] Hello everyone. Hope you are having a great time. So, in this demo section Okay. Sorry about that. I thought I had advanced to the next slide. So, we better wrap it up. Um, and I just want to summarize some of the lessons we've learned so far, right? So, in terms of
[21:19] the challenges, it's not surprising, but you know, we found a large context led to hallucinations and rule drift, and that was addressed primarily by parsing those inputs and controlling the context throughout the workflow. Right? And as soon as we moved into real-world migrations, you know, we found teams had
[21:35] really complex semantic modeling, and that was better addressed programmatically, um, rather than with an LLM-driven approach. Um, now, the other thing that is interesting to see under challenges is the evolving Databricks AI AI BI feature set, right? It's awesome that we're getting these
[21:51] new features every single week. If you guys are using the tool, I don't know if you follow the release notes, but it's exciting every week seeing the new stuff that's coming, but it's a challenge for us during these migrations because it's the tool itself is changing as teams are migrating into it. And so, um, you know, using that LV-schema that we
[22:08] had to reverse engineer out gave us a way that we could uh start to incorporate all that new capability as those release notes came out. Now, in terms of successes, what we're, you know, hearing back from the teams is about 70% of their migration work is automated. So, that leaves 30% that still needs to be done manually, but
[22:24] that 30% that's left to be done manually is getting easier and easier as Genie's getting more and more capable, right? As soon as Genie code came out, that last 30% became a lot easier. Um, the other thing maybe to highlight here is just, you know, we really found that, you know, using the deterministic
[22:40] validation and those repair loops really improved our reliability and scalability. Um, so that's really important. Uh, and, you know, I would say the biggest takeaway that we had so far is, you know, it's really critical to get that right balance of LLM AI-driven der- and
[22:56] deterministic logic to really get the right performance, right? You got to get with right mix of agentic, deterministic, validation whenever you're dealing with the real world complexities of dashboard migrations. So, yeah. Thank you very much. Um I guess since we went a little over time,
[23:11] I'm happy to take any questions um maybe after the session uh here just find me. I'll just hang out there. Um and of course, you know, we're happy to have any feedback. So, happy to have any surveys. Thanks a bunch.

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.