Analyst Roadmap to Databricks: From SQL to End-to-End BI

Summary

Analyst progression path: Learn a structured roadmap from ad-hoc SQL querying through notebook development to scheduled data pipelines that produce interactive dashboards on Databricks.
Semantic modeling: Create a robust semantic layer using primary-key and foreign-key relationships, column comments, and RELY constraints to enforce data consistency and enable self-service analytics.
Genie integration: Empower business users with parameterized dashboards and Genie Spaces for conversational analytics, enabling self-service BI while maintaining governance and data quality.

Analyst Roadmap to Databricks: From SQL to End-to-End BI

Talk by Jake Duckers, Assistant Manager Business Analytics, Spencer Gifts
Analysts often begin their Databricks journey by running familiar SQL queries in the SQL Editor, but that’s just the start. In this video, I’ll share the roadmap I followed to expand beyond ad-hoc querying into SQL Editor/notebook-driven development to scheduled data pipelines producing interactive dashboards — all powered by Databricks SQL and Unity Catalog. You’ll learn how to organize tables with primary-key/foreign-key relationships along with creating table and column comments to form the semantic model, utilizing DBSQL features like RELY constraints. I’ll also show how parameterized dashboards can be set up to empower self-service analytics and feed into Genie Spaces. Attendees will walk away with best practices for starting out with building a robust BI platform on Databricks, including tips for table design and metadata enrichment. Whether you’re a data analyst or BI developer, this talk will help you unlock powerful, AI-enhanced analytics workflows.
Intelligent analytics for everyone: https://www.databricks.com/product/business-intelligence
BI meets AI: https://www.databricks.com/resources/ebook/business-intelligence-meets-ai
Explore the only unified platform for agent systems: https://www.databricks.com/product/artificial-intelligence
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

Chapters

FAQs

What is the analyst roadmap on Databricks?

The roadmap progresses from running familiar SQL queries in the SQL Editor, to SQL/notebook-driven development, to scheduled data pipelines, and finally to interactive dashboards. Each stage builds on the previous one, creating a complete BI platform on Databricks SQL.

How do you create a semantic model?

Organize tables with primary-key and foreign-key relationships and enrich them with column comments. Use DBSQL RELY constraints to enforce semantics and create a semantic layer that helps users correctly interpret and use data without needing to understand backend pipelines.

What are parameterized dashboards?

Parameterized dashboards allow users to filter and interact with data dynamically. They empower self-service analytics and can feed insights into Genie Spaces for conversational analytics while maintaining data governance.

How does this enable AI-enhanced analytics?

Genie Spaces use a well-structured semantic model to provide conversational analytics. This integration lets business users ask natural-language questions about data, with Genie understanding the relationships and semantics you've defined.

Full transcript

`0:00` Introduction and roadmap

Who am I? Most recently, I'm an assistant manager for business analytics at where I hold data brick certifications as a machine learning engineer and a data analyst. And most recently, as you can see, I'm now a member of the MVP program. Now, I chose this picture myself because I thought it represents a data analyst workflow pretty well. And plus, I think it looks pretty cool for myself. But you got the cool, you know, processes, right?
You got your gear all set up, but then where's your data? This is just one of the many questions I hope to answer today by following along on my road map. Now, to begin our road map, we first have to understand where does a data analyst fit into data bricks. And I do this by breaking their role down to three domains. Gather and interpret available data resources to build data objects that will feed business use cases.
These three domains I outline as the following and I plan to define these three domains through the lens of an analyst. However, before we can even define the uh the analyst roles, we have to first put physical properties to this idea of unity catalog. And the first place we can do that is by going to the catalog pane as this is the first place an analyst is going to be able to see and touch it. And for simplicity, we're going to assume a single catalog following the three tier medallion structure of bronze, silver, and gold where the analyst is going to be primarily concerned with what's in the gold layer.

`1:04` Unity catalog fundamentals

As these feed business uh sorry, PowerBI and key reports as they mature and data resources become available, they're going to start going into that silver layer to get more of those source tables. Now what an analyst needs to understand is that as data objects move through this medallion structure, they are transformed and created and feed business use cases. Analysts should start envisioning how these data objects are transformed and created in the rules that govern them through these processes.
Data discovery and data governance. Now I'm going to skip through this slide and let's just get right into it. All right. Data discovery and data governance essentially boils down to where's my data and what are the properties in its life cycles and we can do all this through the Unity catalog in using the UI. Data discovery and governance I boiled down into four key pillars. Freshness, who or what is updating the object and what is the cadence of these updates and refreshes?

`2:07` Data discovery and governance

Volume, what is the size of the object and how many files make it up? Lineage, what are the sources and how do they relate to the business use cases? in schema. What are the columns, data types, comments, and very importantly, the relationships? And we can address all these questions by using the catalog. This is our entry view when looking at an object in the catalog. In this case, a delta manage table. We can immediately determine if this object can be used for a business use case or is it too stale based on its refresh schedule or heavy because of the size of the data and would require further manipulation.
We can go one step deeper into understanding an object's freshness by understanding who or what is created it and what is updating it by using the details tab. And this gives ownership to the object and helps establish lines of communication with the proper teams. As an analyst grows, they can even take advantage of the properties section to understand what table properties may need to be applied. And most recently, I found this out.
Whether you want to do incremental refresh with materialized views with specific SQL functions, you got to have road tracking enabled. Moving on, using the history tab, we can see what operations are being done and want when as well as what are the jobs notebooks performing these operations. And depending on your permissions, we may even be able to see the job or the notebook that ran this. This builds on that communication I described earlier.

`3:32` Advanced catalog insights

By now, we know what team we need to talk to and we know what processes need to be reviewed before building on a business use case. Going right on over, looking at the lineage tab, we can identify what are the upstream dependencies and how do they govern how this object is created or updated as well as what other downstream business use cases does it solve. We can even further filter this to just dashboards, tables, and queries.
Now going back to that overview tab, this view looks slightly different because we're looking at a different data object. In this case, a materialized view. And what this allows us to do, which is why I love data bricks, is I can see the SQL definition behind it. And from here, we can identify what are the data types, the column comments, and primary and foreign key relationships to understand the structure that govern this object's place in the business ecosystem.
A key feature uh for in data bricks that we can leverage is the data bricks AI assistant to generate these table and column comments. And boy can I tell you that saves me a lot of time. We can also view the entity relationship diagram by using the UI feature view relationships. Let's take a look at it. Here is the same materialized view as erd with primary key and foreign key constraints established. I wanted to show this one because some examples are kind of basic.
I want to show you can make a composite primary key made up of multiple columns as well as having multiple foreign keys on this thing. And now I saved this slide for last because of the power that primary and foreign key relationships bring to Unity catalog asformational constraints. They help analysts understand the entity relationships and how to join those tables. By adding an enforced constraint and the rely option, they can be leveraged by BI tools for query optimization.
Woo, that was a lot. Sorry, I want to get through the basics before we start to get into the other stuff. So, now we understand a data object's properties and the rules that govern their structures and potential use cases. We can explore our data and validate its ability to answer the business questions and requirements. Through this process, the analyst is going to go from running ad hoc queries in the SQL editor to building data models using notebooks and workflows or as I found out today, lake flows.

`5:37` Transformation and modeling

Now I define data transformations and modeling by their requirements. Defining EDA and data mapping in this context, especially for business, means validating aggregation metrics across dimensions to reflect business processes. And the SQL editor in the notebooks provide the environment to do this in. Code can be run thanks to data bricks compute where we can attach to a SQL uh a serverless SQL warehouse and a serverless compute and the choice can depend on the environment where the code is running as well as what language you're coding in.
We can review the data and math rules by utilizing the built-in features and defining constraints on our data objects. Now the question comes all right you showed me all this where's all the stuff saved in the workspace tab. So I like to think of this as like the S drive of data bricks where everything is saved. By having a centralized file system we're able to create a hierarchical folder structure where we can organize our notebooks, our jobs and our dashboards.

`6:44` Workspace organization

And a key data bricks feature in the workspace is the ability to connect and create git folders. So we can get all that perks of you know a version controlled codebase when you guys are all ready and I like I'm just going to toot my horn. That's my organization. So I like it. Now that we know where our queries, notebooks, and dashboards are saved, let's look at our first coding environment, the SQL editor. As this is going to be where an analyst starts opening a previously saved query in the SQL editor, we can see we have four separate queries within it.

`7:16` SQL editor crash course

We can set the default catalog and schema as you guys are seeing here. So, we don't need to do the full data object reference when running the query. We have the ability to run selected queries as well as limit our results. But for now, I'm going to run them all. Since we're chaining together multiple queries, this is going to produce a multi-tabbed result. We returned each table properties as well as the sales table because I wanted to highlight one of the core features for an analyst.
The ability to build visualizations off your query results. Here we're going to make a simple line graph for let's go with sales units by date. And then for funsies, let's go ahead and group it by state so we can see all the state's historical performance. Once this thing looks pretty good, we're going to be able to save it to our SQL editor. I like to always inspect how crazy it looks. Looks good. So now we can move on to scheduling this query.
We're going to add a schedule. We can set the time zone, when we want it to run, and then when we're ready for it, we can save it. And then when I want to inspect it, and I'm ready to show it to my boss, I can then hit share, and I can set his level of permissions. All right. as the analyst now goes beyond those ad hoc queries you just saw in the SQL editor they kind of want to now take those like from experience 20 CTE makes some 10 views maybe use some PIS spark to do some you know more heavy data transformations they're going to move into notebooks notebooks have the advantage of having separate code cells that can organize and structure their analyst code for readability and orchestration purposes looking at this notebook's um table of contents we can see it's an ETL notebook that's broken down into subsections to create the fact and dimension tables for this talk.

`8:40` Notebook development

Another advantage of notebooks are the ability to access different types of compute such as serverless as you can see here which allows code cells to be written in different languages but still be able to run together. Here I create the calendar dimension table using the SQL commands create or replace table and the insert into. But when we scroll a few lines down, you're going to be able to see that I use these temp I actually create temp views using Pispark, which are then put into a SQL code block to finally make my sales table.
Because I'm crazy, I wanted to show you. Now, just like the SQL editor, we can create visualizations. We can organize our notebooks in the workspace, and we can set a schedule for our notebooks. Now, we're start going to start getting into the picture slides. So this is the basic notebook structure I use to develop the core analytical tables that feed PowerBI and our data bricks dashboards. Each notebook is broken down into three main parts.

`9:54` Data model workflow

The first is you're going to make that table definition. It's your column list and you're going to put your column names, the data types, enforce constraints, and primary key columns and comments. Enforced constraints ensure that the data that is added is automatically verified and if it breaks that enforce constraint, the transaction will fail. very helpful. Next, I encode the primary and foreign key relationships, but these are very very important to remember is they are only forformational purposes at this point and they are not enforced on their own.
The next thing I'd like to add is the rely constraint option for a primary key because when it's valid, it can be leveraged by downstream BI tools as well as improve query performance. And finally, you want to use automatic liquid clustering by cluster by auto. Second, I'm going to create the temp views referencing my source tables in the silver schema to build my fact and dimension tables. Once I have all those temp views created, it's as simple as just using that SQL command, insert into, and I'm going to insert all those temp views into it.
This modular code structure allows for user readability, easy debugging, and future modification. Here's probably a slide we all want to see. This is the simplified example to create that sales fact table. The enforce constraint not null is set on the columns that are going to be used to make that composite primary key. That's that enforce constraint I've been talking about. When the primary key is valid, meaning it's unique, you can set that rely constraint on it.
That rely constraint option allows the query engine to skip over the unnecessary joins when the join won't change the final result of that table or what it's going to pop out. Next, I do automatic liquid clustering by using cluster by auto. And finally, use insert into to insert all that data into our defined table earlier. And if you want to validate everything that you've done in here, it's as simple as going back to that SQL editor and just typing in describe table extended and that table name orchestration.

`12:07` Workflow orchestration

Now, we know we have our notebooks created, that former data model. We need to ensure that our data is fresh and up to date for our business users. So, we got two options here. You can either schedule all those individual notebooks. No. Or what we could do is use our workflow lakeflow free features. Now, here's the workflow I set up originally for this talk. Using the data bricks workflow, I chain together multiple task types to orchestrate these notebooks that create my key reporting tables.
Notebooks can be run in any spec uh can be run in a specified order by setting it your task level dependencies. I included here an if else condition because I wanted to demonstrate you can create complex workflows within data bricks. Now the reason I put that in there is because I don't want to refresh my calendar table every single day when I'm refreshing my tables. I only want to do that once a year. That's what that if then condition is meant to be used for.
I just want to highlight here two things you might want to check out is the task type the PowerBI refresh and then the data uh data bricks dashboard refresh. Those I'm testing right now and I'm loving them. From that view, if you guys back out in that workflow, you can see the overview of it. And this is where we can review past runs. We can modify triggers and schedules. And we can ensure proper monitoring by simply just applying those tags because this will help with that cost monitoring and observability feature.

`13:13` Workflow monitoring

Um, a key feature here we can do from this view is set task level parameters that can be referenced by those individual tasks. So, if you look over here, I have on the job parameters job run date. That's what's getting passed into that if then condition and checking it against what is the current date. Now we're at our final domain guys. Now that we have our data models built and we ensure their freshness, we're ready to build data products to deliver business value and insight.
There are many different BI tools and last stops for data when delivering to the business. But I want to focus specifically on dashboarding. The first is PowerBI. What can PowerBI do with these column constraints and how they can be used to automatically generate that semantic model? And finally, I want to show off a data bricks dashboard. First, we got to set up our connection to PowerBI and data bricks. We go to the workspace.

`13:58` Power BI integration

We're going to go to the compute pane and we're going to grab that SQL warehouse host name and path. Then, we're going to go on over to PowerBI. We're going to go to get data, navigate, search for Azure data bricks and paste in that host name and path. We're going to select our tables, load and transform the data. And then what you're going to see here is finally once those tables load into PowerBI, if we navigate over to the model view tab, by establishing those primary and foreign key relationships, those are automatically generated for us.

`14:25` Databricks dashboards and genie

So no more do we have to go to the juniors and see many many to relationships that are blowing up the models. Data bricks dashboards are evolving at an exponential rate. And I want to highlight three features here. data bricks dashboards or sorry data bricks parameters calculated uh dimensions and metrics and an autogenerated genie space. This is a published data bicks dashboard using the Walmart data set from the M5 forecasting competition.
At the top are our key filters by picking different business defined date periods. We can see how the visualizations are either rerunning or reading from cache by the spinning wheel that shows in the visualization. Next, we can see dynamic cascade filtering in action by changing the state filter to California. Also filters down that chain to filter the stores. And we can also reverse back up that chain. As you can see, when we filter to just a specific store in California, filters the state.
Now, what I want to do here once I finish showing this demonstration, there we go. Let's look at an autogenerated genie space or sorry yes let's look at autogenerated genie space from here from the highlighted section of my prompt we can see genie uses the metadata from the table or column comments to translate last complete week into lcw which is one of our business defined periods and genie not only provides us a result as a table but it's using the data visualizations within the workspace to provide me the type of visual I'm expecting finally We can provide feedback.
And it is noted that when you want to provide feedback to Genie, you don't just want to provide a rating. You also want to be able to provide specific corrections and context in order to improve future conversations. The first step to creating this dashboard is to create the data sets that power our visualizations using the create from SQL option in the dashboard. Visualizations needing their uh needing their own level of granularity are going to have to have their own data sets.

`16:35` Setting up datasets

And do not quote me, but I think you can have about a hundred separate SQL queries in there. So if you reach it, let me know cuz I want to know what you're doing. The second step is you can uh use that same star schema or a one big table from our visualization data sets to get your distinct choices for your filters. So you want to have in here data sets for your visualizations and then data sets for your vis uh your filters. the dynamic cascade filtering I modify I have to modify the wear clause to be broken up into separate conditional arrays for each filter.

`17:13` Interactive filtering

This allows for that default catch when you're not doing any filtering and it's just like all the way you reference them is now it is the colon parameter name notation. And if you look at the bottom of that of my image right there, if you want to change the parameter type, you're going to go to the parameter section. And what you have to do for each of those array conditions is you want to make sure it's multi select the date single select and then you can also as you can see here is a date range as you can choose as well.

`17:51` Metrics and performance

Returning to the canvas view and selecting the state filter we can choose the uh the state filter data set for the field section. So we can actually see how we can interact with this thing. When we select each data set's corresponding uh sorry when we set this thing we're going to pick the fields which comes from our filter data set. Then remember how in that wear clause we modified each of our things we want to filter.
That's what you're going to choose for each of those parameters. That's how you enable that dynamic filtering that's going on. To create custom calculations as well as their comments, we're going to click back onto the schema tab and we're uh sorry, we're going to click on the schema tab in the data pane. Custom calculations let you define dynamic metrics and transformations. And I want to highlight two main things about this stuff.
Calculated measures are aggregated aggregated values. The example here is going to be revenue percent change. The second one is going to be a calculated measure which gives you an aggregated result and that's going to be your overall revenue percent change for that date period. The one thing I want to highlight here is these improve your dashboard's responsiveness because if the data set small that you're going to be using for the visualization 100 megabytes or less than 100,000 rows, it happens in the browser.
If it's got to go back to the SQL warehouse, you're going to see that spinning wheel. The last step in this process is publishing the dashboard. This is what I really want to see uh for a lot of these talks is how are now these businesses actually using Genie? Are they creating their own Genie space based on dashboards or building it right off the top? Here we're building an autogenerated Genie space from a dashboard.

`19:09` Publishing and resources

So our first choice is we're going to be uh sorry, our first choice is autogenerated genie space. The second one is going to be using that u URL um and we can post it into a previously created genie space. Now I want to talk about the limitations here and that's pretty much going to be it for it is that if you use an autogenerated genie space from what I found is you can't modify the instructions in the UI using genie but if you if you create a genie space in its own environment you're able to modify the instructions.
So what I've seen is I will use a dashboard to kind of create a template of how I want this genace to be re uh to be built and the SQL queries I'm going to need and then I transfer that into its own private genie space. So then I can kind of get more particular with that tuning of those instructions. Lastly, I just wanted to provide some resources. I'm really loving this book. I think it's really good for every business analyst for uh talking about the gold schema.
And I just found out from that may be an old QR code, but if you're doing anything with data bricks and PowerBI, that is going to be the GitHub to go to. It has all performance optimizations, how to kind of set things up. I highly recommend that. So, if that QR code doesn't work, I'm sorry. Lastly, please complete the survey for my session and thank you for coming, guys. I appreciate it.

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.