Skip to main content

SQL-Based ETL: Options for SQL-Only Databricks Development

Summary

  • SQL-only options: Databricks offers four paths for SQL-based ETL without requiring Python or Scala: DLT, dbt, SQLMesh, and SQL notebook jobs.
  • Engineering practices: Tools like dbt and SQLMesh enable analytics teams to apply version control, data testing, and modular design to SQL transformations.
  • DLT declarative approach: Delta Live Tables simplify batch and streaming pipelines with declarative SQL syntax, reducing complexity.

SQL-Based ETL: Options for SQL-Only Databricks Development

Watch: SQL-Based ETL: Options for SQL-Only Databricks Development
Talk by Dustin Vannoy, Sr. Specialist Solutions Architect, Databricks
Using SQL for data transformation is a powerful way for an analytics team to create their own data pipelines. However, relying on SQL often comes with tradeoffs such as limited functionality, hard-to-maintain stored procedures or skipping best practices like version control and data tests. Databricks supports building high-performing SQL ETL workloads. Attend this video to hear how Databricks supports SQL for data transformation jobs as a core part of your Data Intelligence Platform. In this video we will cover 4 options to use Databricks with SQL syntax to create Delta tables: DLT: A declarative ETL option to simplify batch and streaming pipelines dbt: An open-source framework to apply engineering best practices to SQL based data transformations SQLMesh: an open-core product to easily build high-quality and high-performance data pipelines SQL notebooks jobs: a combination of Databricks Workflows and parameterized SQL notebooks
Here's more to explore:
The best data warehouse is a lakehouse: https://www.databricks.com/product/databricks-sql
How to migrate legacy data warehouses: https://www.databricks.com/resources/ebook/migrate-your-legacy-data-warehouse-databricks
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

Chapters

FAQs

Can I build ETL pipelines with SQL only?

Yes, Databricks supports SQL-only ETL through four options: Delta Live Tables (DLT), dbt, SQLMesh, and SQL notebook jobs. This video explores each approach thoroughly.

What are the benefits of using dbt on Databricks?

dbt is an open-source framework that brings software engineering best practices to SQL transformations, including version control, testing, and modularity. This video demonstrates its integration.

What is Delta Live Tables?

Delta Live Tables (DLT) is a declarative ETL option that simplifies batch and streaming pipelines using SQL, reducing complexity. This video covers DLT implementation on the Databricks Data and AI platform.

Should I choose SQLMesh or dbt?

Both SQLMesh and dbt enable high-quality data pipelines with engineering best practices, but they have different approaches. This video compares options to help teams choose.

Full transcript

0:00 Introduction and context

Welcome everyone. I appreciate the packed room for this subject. I think it's a very interesting one. I hope that I can do justice to covering all the things involved. So we'll be talking about SQLbased ETL options for SQL only data bricks development. Now uh you hopefully have seen this by now there. This is forformational purposes. Talk about what's coming in the future is not a guarantee and other things that are in this slide.
So, make sure you read this through the end of the day and you'll know what's going on. Uh, please do complete your surveys when they pop up in the app. We'd love to get feedback on what was helpful, what was not from the session. So, here's what we're talking about today. I get this question from time to time. We are migrating to data bricks. Does that mean that I need to learn Python or Scala? And essentially the question typically is from the side of I've been writing stuff in SQL doing a lot there is that enough essentially it's typically a team of people not an individual it's a a team that has domain expertise writing a lot of logic in SQL already getting the job done adding business value like we always need to be doing and they want to own the ETL process not have to hire someone new or ship it off to a different team to write with code other than SQL code and then they want to know is this possible and so surprise yes I think so at least for the ETL sides of it right when you start getting to AI and ML um you can do some stuff with SQL there but we're going to talk about ETL data transformations and a bit about ingestion as well which is something that I think is a little more unique uh and can happen with SQL so the tools I'll be covering are data bricks SQL mesh and dbt And I just want to say like if you're part of one of these, if you're with DBT or SQL Mesh, I'd love feedback, you know, separately after the session's over, I think I'll stick okay to the overview.
But they both have booths here if I understand correctly. So definitely recommend going and talking to them to ask those follow-up questions or say, "Hey, I heard this from this guy who works for Data Bricks. Is this right?" and they can correct anything that um doesn't line up with uh the latest and greatest that they're doing because they're changing things fast just like data bricks does. So. I do have a YouTube channel.
I finally got around to getting my very first DBT related session released. I have a bunch of CI/CD and some other data engineering and other prior to data bricks, other data platform stuff out there. So, check it out for more tutorials and like hands-on stuff usually with links to code. I'm a senior specialist solutions architect and one of the things I help people with is developer experience and another is data warehousing.
And so these two kind of overlap a lot with this discussion. Not so much the deployment and CI/CD side of developer experience but the actual writing code and having an environment where you can follow good practices. Um definitely feel free to connect with me on LinkedIn and ask follow-up questions. uh keep an eye out for when I do have local events in San Diego area or um post follow-up videos um that go deeper into some of these topics probably with a lot more demo than we have time for right now.
So, I'm going to set this up with my journey. So, I started my career using ETL tools like drag and drop, you know, ETL tools, writing a lot of SQL, building really complex reports. We had a nice semantic layer where I did all kinds of crazy stuff and we got the job done. It was going pretty well from everyone else's side of it. But what I knew was that we were recalculating logic every day or sorry every query copying and pasting code.

3:17 Challenges of SQL-based ETL

A lot of lack of reuse in what we were building. Uh it was really hard to navigate the code in the dependencies. Uh for some projects we'd have hundreds of either store procedures or hundreds of SQL files to deal with and then there was some scheduler. Sometimes we honestly used crown scheduling for it that just pieced it all together. It was really hard for anyone but a couple of us to figure out what was going on. Basically the ones who built it kind of knew what was going on.
The other people did not. So then we also had this challenge deploying from dev to production. Sometimes we didn't really have a separate dev environment and it wasn't a problem for us. Uh later I met a lot of software developers that told me I was crazy for not having a lot of testing and automated deployments. So the other thing that is sort of a hint that things have changed over time is we couldn't do everything with SQL.
And here's the here's the bad part that I don't address in this. But the bad part was if it wasn't possible with our SQL language, we just said it's not possible. That's what I told the business users when I was a leader. Um, thankfully a lot of things are possible with the tools we'll talk about using SQL. I'm not going to say that no one on the team could benefit from knowing Python or Scala or some other technologies to cover some of those more advanced use cases that haven't been captured by SQL language yet.
All right. So, essentially the summary of this is SQL ETL can lead to a disorganized environment. Um, I expect a lot of you understand this. There's valuable stuff. There's good information, but it's kind of hard to figure out what's what and where it's at. When you go and build your own framework, which is something we did at one place I was at, like we did a Python framework that did a bunch of dynamic SQL. It took a lot of time to build it.
Still kind of got left with only a few of us really felt comfortable changing all that framework. And so the tools we're talking about today really exist to try to avoid this problem, solve these problems that have been around for quite a while. So new might be, you know, I'm going to call them new because I've been around a little bit and I knew a world without these. But these new frameworks are going to really help us here and we're going to talk about each of them as we go.
Now before we get into that, one of the best practices for most of the ways I'll talk about running things today, everything other than DT is to start with data brick serverless SQL as your compute platform for this code. And basically the reason is we are writing SQL code. That's what data brick SQL is all about. We are going to run multiple commands uh concurrently. the amount of concurrent commands will change over the course of this ETL job running.

5:46 Databricks compute foundations

We also often are talking about batch workloads with the things that are covered by SQL often. And for those you want compute that spins up really quickly, does its job well without you having to spend a lot of extra admin time and then turns off quickly when you're done. And C serverless SQL is a really good fit for that. Okay. It's also a bit easier to monitor the query history and see what's going on than like if you have a classic Spark cluster.
But I will say, you know, with other types of serverless compute and just the advances of the UI, maybe that's not always going to be the case. I still think it's a little bit true though that it's a little bit easier to monitor and do your query profiles, which is important. So another element before we get into the technologies in particular is that data bricks provides streaming tables and you can do these from data bricks SQL. you can kick off a command that's create streaming table and define some sort of ingestion workload.
Now this ingestion is I believe this is all we support still is from cloud storage which is the most popular and a very powerful one Kafka Kinesis event hub and so if your data is not in cloud storage yet say it's sitting in some like postgress database something like that there are other tools you bring into the picture I'm not going to get into lakeflow connect or partners like fiverran and all that today but there's other ways you can get that data to cloud storage if it's not already there but once it's there you could actually do this with SQL you can do this from dbt and um I've done it from SQL Mesh as well.
On top of that, Azure processing, we're going to look at I won't get into details on how we deal with um incremental changes that are basically a merge and insert and an update all mixed together. I'm not going to get deep into that, but um tools do it for you. Or you can use data bricks materialized views and just write the query you want create create as a materialized view and it's going to do its best to uh it'll run it on background serverless compute and it'll do its best to incrementally recomputee what it can.

8:10 Using dbt on Databricks

So set up the set up the stage. I'm going to be using serverless SQL for a bit of this. Let's get into dbt from my summary perspective. I'm trying to capture what it's about, like not necessarily why I love it, but what what it's about, but these are my words. So, I apologize if it doesn't quite hit the marketing lingo that's typically used. But, we'll start with marketing lingo. Analytics engineers. So, DBT really got into like I think I first heard about an analytics engineer coming out of DBT, which is people that are doing analytics can also be engineers and write powerful code and follow best practices.
Along with that comes version controlled and things that you can do that continuous deployment automated deployment with. In addition, integrated data quality testing is super important. I don't know, I won't show do show hands, but I've written my own data quality framework. Again, I knew how to use it. Maybe I'm just not good at developing things. I knew how to use it, but a lot of people struggled to fit it in versus if you have a community that tried to make it really simple, a little bit different than a couple of people taking a crack at it in their spare time.
Um it has the lineage graph is inferred as you code and you'll see that in action here and then documentation is very easily generated depending on if you're using the open source DBT core or the cloud version DBD cloud. Um you may have to host it yourself if you're using the open source version. Okay, here's an architecture slide. I don't want to get stuck on the architecture, but I do want to call out a couple things.
One is that typically the ingestion piece of this to get data into data bras or whatever data warehouse tool you're using dbt with that piece would have been some partner tool like a fiverr or many others or you're doing something like writing pispark code to read from the source these days we also have lakeflow connect as an option there because of streaming tables we can now do it from dbt with something like that um we also have some good blogs that have been around a bit about copy into statement.
I'm kind of to turning towards streaming tables now. I'm still kind of seeing I don't know where the balance is as far as copy into versus streaming tables, but you can do that ingestion if you want. It's really built for all the transformations is data is landed into a warehouse and whatever storage your platform uses. Obviously, we're talking about cloud storage. You need catalog manage tables on data bricks. That's what we'd recommend. do the processing and save it back and you can follow this incre uh this medallion architecture of incrementally improving your data as it goes from bronze to silver to gold.
Okay. So one of the key things to understand which will also help us a lot with how we talk about SQL mesh is models. So dbt model is a SQL select statement that takes data as an input and produces some sort of data warehouse object most often a table or a view in the end. So you start with your model code. Hopefully you can see there's this on the from clause there's this ref statement. There's like this ginga templating to reference some other model or some other you know thing that I've coded up in this project rather than a hard-coded place in my data warehouse already.
That gets compiled into a create a replace statement or maybe it's a merge into statement. Maybe it's even more complex than that. But that gets compiled when it's time to run and you can compile it during development. see what it looks like into something that's meaningful for the environment you're running in. That compile model will look different for your development target than it will for production because you're using a different probably catalog, maybe even separate schema.
And then once you run this, it's going to end up with some sort of table or view. Few other cool options in there, but we'll just stick with those for simplicity. All right, so we've got this uh dependency graph. I gave you a very very simple one for now and um it's going to let me see that some things can run in parallel, some things cannot. We have dependencies built in and ideally we're also able to navigate our code a little bit through this graph.
So guidance here, I kind of already went went off on serverless SQL warehouse, but guidance here is use Unity catalog. So if you're doing DBT core open source DBT fusion when it's available for you with data bicks use the dbt data bricks adapter I think that's still going to be the right thing the right name use the dbt data adapter it has a lot of stuff that data bicks is making sure is working well um from dbt including ability to use unity catalog really effectively in addition remember serless SQL warehouse the benefits are fast spin up good concurrency really built for performance of SQL statements so we think it's going to be the right place for your ETL/SQL workloads.
And I'll talk about what about those few models that don't perform well um at the end. This is all right. This is where like you need a lot more time to try and highlight DBT cloud versus core. So this don't don't get too caught up in exactly what I picked out here, but the summary I'd give you is DBT clouds to make it easier for you to develop and manage your CI/CD and to handle all the documentation in the lineage.
Yeah, we'll leave it at that for right now. What I will do though is show a demo. I forgot to check if this is all going to work properly. Let's see here. I think it is. Okay. So, here I am in dbt cloud and you can hopefully see I'm in this develop pane where I've got a specific git branch and I've got a specific SQL model open. You can tell it's a model because I've got this templating in there. I define that it's a table.
And then as we scroll down, we'll also see some other references within the model that will get converted. A lot of this really is just SQL code that works on data bricks. But then the in the from clause, we've got the HR ref. And in the um in the other statement, we've got this uh reference to a dim date table as well. So as I as I look at this, you can see at the bottom as I'm doing development, I've got this lineage graph.
If we zoom in a little bit, you'll get a sneak peek at a portion of my project. So my upstream tables, dim data and HR going to flow into dim broker and so on down the line. And I can actually navigate to different things through here. You're going to have this build. Build is one of the key terms for running things in dbt. And so you have this build option where you can pick exactly whether we need all the upstream dependencies, all the downstream, what what variation we need.
And then like I said, you can go view this compiled code. This is fairly simple example. You can probably picture what it looks like. But if you want to go run this somewhere, um there's some options to run queries like from your DBT development experience, but you also might want to copy and paste it into whatever you're typically running queries in. It kind of depends on how much time you spent figuring out your DBT development experience versus uh another.
All right. Um, as we go forward, the other thing I wanted to show you right about here is that we can set up jobs and CI/CD. So, I've got two different environments, stage and prod, two different jobs that are in this case very similar, but they're pointing to different environments. I can track my re recent runs and the actual schedule is set up here in DBT cloud and it's doing the triggering of that and running it for me. um you can see the steps, see the details about what has run with your dbt output and then when things go wrong, we typically well those of us that are helping you troubleshoot at least typically go to db de debug logs and go a lot deeper.
And then finally, this is explorer. Um, if I remember right, the mapping is explore is going to be called catalog. And so it might, you know, keep evolving as a lot of things are changing with DBT uh improvements. But essentially, it's a way to look through what models exist in my project, what exist in other teammates or people in the company's project, and be able to know what's going on beyond your one repository.
Uh, and even just know what's going on within your repository. Because this is like my full lineage graph. It's a little bit messy, but as you can interact with it and go piece by piece, it starts to make a lot more sense than 50 SQL files with nothing like this to navigate with. Um, and then finally, my dashboard gives me the top level view. Did we get out of that? Yeah. Okay. All right. This is my take. DBT Fusion was announced I think in the last two weeks and I've been I've just been listening to it as I like you know am trying to in between things and so here here's what I've gathered is DBT Fusion is like the new the new um future of DBT there is open version okay and there's also going to be the cloud version I can't recall I'm blanking on if DBT core is still a separate thing but there's definitely open I think it is and so just keep an eye on this but essentially DBT Fusion would be faster, more efficient developer experienced um giving some advanced lineage.
So some of the things we talked about with SQL mesh might actually be coming along with this to be the same with DBT or at least more similar. Um additional data bricks integrations on the way. So keep an eye out for that for the right people to announce those as they become available. And for DBT cloud customers, you'd also have uh catalog is the explorer I showed you, but you'd have copilot and then canvas and insights are these analytic user tools that sound really nice.
I haven't seen them in action enough to comment much more on them yet. So that is our intro to dbt. I will do a short live demo in a bit where I show you both of these together. Now for SQL mesh, SQL mesh is open source as well and there is a cloud offering on top of the open source and so the goals my understanding of the goals of SQL mesh being like why does this need to come into existence was really to give you like to make it easy to build high performance data pipelines.

17:37 Introduction to SQLMesh

That's kind of a high level you know objective there. And so one of the things we see is a simplification of the deploy of data sets from dev to prod. Now, with simplify, that means there's um a bit of an opinionated way to do it. And so, I'll talk about that in a moment so you understand kind of the trade-offs and what might be better for your organization or at least have a sense of that. And then you can go talk to the different uh people at the booths to hear more.
It lets you parse SQL to infer dependencies. So, you can actually parse that SQL without running it against your compute first, which is something that DBT Fusion is going to bring along for DBT. it uh handles incremental processing with state and versions. So there's this understanding of state that we'll kind of see. Uh I'll I'll try to get a little bit more into that in the demo, but um it's it's very powerful that's available.
And then smart repair to rerun only what failed. So some key differentiators relate to that whole deploy difference in how we deploy. So we're going to use something called virtual data environments to uh avoid reprocessing data as often as we typically would with DBT. We are going to have automatic documentation as we go. Advanced COM level lineage the support for blue green deployments or I'd say it's a lot like if you've heard of write write audit publish that can be a little bit challenging to do on data bricks. um support for that kind of mode and then understanding the SQL and being able to transpile between different variations of SQL like snowflake to data bricks uh is because SQL glot also if you've heard of SQL glot is the same people to that have um kind of been the starters of SQL mesh.
So here's another architecture diagram that I don't want to get too caught up in, but just to show that there's a bunch of capabilities available open through SQL Mesh. And then there's additional improvements to make this easier for you to work with, do less work on your own by using Tobico cloud. There's this expectation that raw data is probably ingested through some tool that does ingestion that's not SQL mesh. However, you can do something like a streaming table to get from cloud storage and similar sources.
Uh, and then downstream you've got um, let me show you what you have downstream when you're going to read this data with your BI tooling or whatever else comes comes next for you. You have a view layer for the different environments. Now, I want to call out I'm still trying to figure out the right way to like write up the best practices here. I kind of need to hear from more customers about what they do. I haven't had as much of that experience with SQL Mesh yet.
But in dev, you can still have like your own dev state and do all this building of dev data completely separate. But like as you're getting into prod, you would have this let's say non-prod view of it, which is where the changes go first. When you're applying like adding a new column, that change would make it to the non-prod first. It happens physically to a table in the same uh schema as your prod data and then prod has a different set of views than non-pro.
So in this case you can see hopefully that transaction silver v2 is being pointed to by my dev environment but prod's still pointing to v1. Now, a little bit behind the-scenes glimpse to help you hopefully piece this all together. And data bricks. It's not really a V1 V2. It's, you know, a little bit more of a random number. Maybe it's not random, but to me it seems random of the version. And I have this um basically this physical table that is a little bit hidden, right?
It's not really what we're exposing everyone to. With V2, that number changed where I got the yellow box, the number changed. And then the reason it changed, I have this project column. So going back for a second, I have what? Three columns. Now I've got four columns. That was my big amazing change that I did. So front-end view, if I go and look at the whatever this view is in prod, it's pointing to a different table than the dev view.
That's your virtual data environment. Um, you might have a lot of questions on that. To be honest, I still do too. I think it's really, you know, useful at times and it also changes how I think about things. And so I'm still trying to rework what do I do with how I deploy like I know how to deploy DBT jobs because I've been doing that a lot. Um so I'm still getting used to this myself but I think it's really interesting and powerful.
Um here's kind of your breakdown. These slides will be available I'm told. I think it's going to be after the conference as a PDF. So this is a little bit more for reference. Again the booth's right there. Go talk to them. Um so let's get into a demo. And I can't do as much as I wanted once I realized how much time I have. You should I should have known when I submitted the session. I was like 40 minutes, man, that's going to go quick.

22:28 Live demo: dbt and SQLMesh

That's all right. I got you. So, let's start with except I messed up my sizing. Let's start with dbt. I'm going to do this from the cursor ID just because I like cursor quite a bit. And I'm using uh this plugin, this extension called dbt power user which is right here. Um dbt is releasing with dbt fusion. There's a VS code extension now. Um I still need to dig into that make like as it gets connectivity to data bricks and maybe I'll make a YouTube video on that.
It seems like something I would do. So um but either way what we're looking at is I have a model here dim customer SQL. I know it's a model because I have this templated code. And I can also use these extra tools. We saw DBT cloud in that video I showed and talked through. I can actually click around and see here's my customer table. It sources off of DIM customer stage. I think a double click there will open up that model.
As I scroll through, um, I can see when it gets to my join time, I'm referencing other models, and that's how it knows this lineage. You might also see, I don't think you'll see it in mine, but you might also see source. I can reference like a source I've defined in YAML, which is a table that exists outside of this DBT project. Now, the other things just to highlight really rather than than see it too much live is that I've got a build model, build the whole project, run upstream, run downstream kind of view.
And then really cool is that, and you can do this stuff with command line, of course. That's that's how I typically have been doing it until now. But I can see the compiled code and it brings it up in this split view to where I can go and see okay with the profile I'm using right now which means the target I would be running this in. Here is how this gets compiled. If I and apparently I'm running against a prod target which I think I told all of you not to like be doing this kind of stuff but I'm doing it because it's a demo.
Why not? Um, when I switch to like my dev tart, my dev profile, it's going to point to a different catalog or schema or both, depending on what you choose. All right. Um, probably a whole lot there that you're interested in that we're not going to get to. Let's take a look at SQL Mesh real quick in the IDE and then I'm going to show you talk about scheduling these from datab bricks in the event you're not using one of their cloud offerings.
So here, SQL Mesh released a VS Code extension very recently. I believe it's preview state. Um I think it's so recent that I haven't quite I I got it working. So that's good. Um so let me show you the lineage because that's probably one of the more powerful features that these kinds of tools really help us with. So here's their lineage and I can see my customer table. This is keeping it in context of this full project.
This is really the same set of models. It's just a difference in what they show in the lineage um with the DBD power user extension versus SQL meshes extension. Um and then yeah, I've got my upstream tables and so on. When I go to run, I've still I've still just been running this from the uh command line. And so they have a set of like getting they have a full walkth through video on YouTube that's really good. They also have a set of like getting started.
That's probably a good good getting started point of course. And so one of the key things you'll do is run a SQL mesh plan and optionally with that environment. So if I run SQL mesh plan on my terminal uh it's going to did I break stuff? It's going to basically say nothing has changed and the time frame you gave us for these models has not passed. So there's nothing to do. There's no data to backfill because I just ran this earlier to see if it works. um and nothing to backfill and no models have changed.
So there's not much to do. Okay. If I go and make those changes, it gives me this plan kind of if you're used to Terraform, which is a very different technology than these, it kind of gives you this plan of what's going to happen and then you can select yes, go ahead and apply this thing. Okay, let me Yes, let me go back to slides here. That was me real time wanting to demo about two more hours of content on that without having time.
Here we go. Let's talk about workflows and SQL task or SQL notebooks. And so the idea is data bricks has data bricks workflows or you might know it as data bricks jobs. We can string together mini task with dependencies get an image that actually looks a lot like the images I showed already. So why not just use that? We're going to talk about why that can be good and a few things that I'm still trying to get comfortable with to say that oh yeah, forget everything else, right?

26:58 Databricks workflows and SQL tasks

Um, let me backtrack and just say the reason you're not going to get a showdown matrix of what's better at what is because I don't think it's that straightforward. I think these are tools that you need to kind of look at. Does DBT and SQL mesh look like they're trying to do what I want with this config and this way it runs. Does this SQL task thing really fit how we're already working in an easier transition? So, there's a lot that's team dependent on not simply what thinks.
All right. So goals of workflows and tying in the task here is we have this native UI. We're not paying extra because we're using the data bricks workflow. We're just paying for the compute it runs on. Uh we have this easy to schedule trigger. It's easy to schedule and trigger and it's easy to monitor within the data bricks platform if you're already doing a lot of work there and in there every day. Not yet. A little bit more.
Uh the native SQL editor will let you build these queries independently. And so I if you if your team standardizes here here's here's the candid stuff right right now as of today and I'm going to have some conversations with whoever will listen after. Um if your team standardizes on workspace or IDE as kind of your main point to do the development you can I think have a really easy oh this is just a SQL statement with a couple of parameters I can easily run from wherever we're all working.
Um dbt I'd say it's not crazy but you have those steps of compiling code. You have to make sure you're getting those references in place. this you would I guess you still have parameters but um yeah I I'll show you a quick example hopefully it makes some sense the other piece about it is that it's pretty quick to go from we've been building this in the workspace maybe it's dev workspace hopefully uh check it into git and um we've got a job ready to go let's just kind of grab the yaml for that job and get this thing deployed so if you're already in the data bricks workspace all the time pretty easy to get started with this and then we have a for each task in data bricks workflows and so you could do some config driven and things that are really powerful.
If all your bronze tables are basically select star from whatever the source is, add some metadata fields about updated timestamp and team that owns the code. Uh you could you could use the for each to automate that and not have however many files. Now dbt and SQL mesh have macros. You can do programming like there's always ways to make these things a bit complicated um for good reasons because the world and our jobs are complicated.
But I think the 4 is not super complicated way to do that. All right. So, um, one of the things I just want to call out about data bricks workflows is it does more than just run dbt. It does more than just run SQL task or notebooks. Um, but that's kind of the focal right now. And, uh, I think I called out most of this already. The parameter support's super critical and you'll see that uh, in the example I pull up. And then also do remember that there's ways to do version control with this with SQL notebooks.
Very straightforward. Version control works like it always has. If you're creating it as a query, it'll be kind of a special data type. I think that's what everyone's seeing now. Um, so a few things to get used to there. But do use version control with your SQL code. This is literally a screenshot of data bricks um workflow. And to me, I had to go back and check because it looks like the dbt lineage I showed you from cloud, but I'm pretty sure they're both from the tools I said they're from.
So it looks pretty similar, easy to work with. If you're using the SQL task, you have a couple options. You can point to SQL files that are in your repository or you could add SQL code inline and then you can pass results from these queries as values into the next task which can be really powerful especially for that config driven framework where you're looking up some information from SQL passing it along. And then you can also use SQL scripting.
Look, I'm not the one that's going to come out here and say forget Python, write everything with SQL scripting. I think, you know, there's benefits to the way looping and conditionals happen in some of these other languages. Um, but I've had a lot of very small procedural things like looping through information that if I could just run it on this SQL warehouse that I already have running, if I could just put it right with all this other code, if I could give this junior person that's still learning uh Python but has doing SQL a while the task, like great.
The other place I've been is we've got huge store procedures that we need to migrate and that's where I think this is really going to be powerful. So anyway, there's lots of great features to it. Some good blogs came out recently because it's a pretty new feature. Uh you can find I'm sure more sessions about it here. So some challenges. The challenge really is the SE SQL scripts have to be parameterized. So all of these tools, no database workflows I would say is not trying that hard to make parameterization easy.
It's it's got a way it works. It's not crazy. DBT and SQL mesh are trying to make that parameterization, those references easy for you. So I I feel like the parameterization, we still kind of need to define some better best practices and share them out and then hear back whether or not it's uh easy or not easy for everyone, but it's very doable and we have examples. You kind of need to build your own testing framework.
Um, I think I mentioned the data quality test, the data testing, uh, that exists for the other tools. It exists for DT that I'll talk about in a moment. And then it's kind of harder to jump from the code to the DAG view. If you've got your job over here and then you're overediting the SQL um, task. I don't find it to be as seamless of a jump. Is that a reason not to use it? Not. Not really. It's just a little developer thing that you may find you you do feel more productive with the other tools.

32:44 Declarative ETL with Delta Live Tables

Okay. DT. uh you hopefully have a bit of background. You hopefully will hear a lot more about it uh over the next couple days, but this is declarative ETL. It's it really came from this background of can we make it easier for more like the data engineers to do what they're doing with code a little bit less of a can we make the analytics engineers more empowered, but it can work from both sides. So, it's a simple declarative way to build these data pipelines.
It's meant to take pretty simple development to do less simple things, more complex things that we would write with Spark structured streaming or Spark uh batch mode. It can automatically handle complexity of batch and streaming ETL. Uh so when you're going streaming and you're trying to do streaming a long way through the flow, I I I see DBT runs that are like every 15 minutes, which is, you know, pretty pretty close to streaming, right?
It meets some of those use cases. I don't see a lot of that. I don't start with that as my recommendation, but I I see it and they're being successful with it. Um, this is meant like if you need to run this continuous stream, you can. You're paying for the compute all the time, but this can run continuous streaming for you. Uh, and you can do that with SQL language as well. So, uh, that's most of it. Hopefully, maintainability and observability is good.
The developer experience has recently been revamped. And so I'd say if you haven't checked out in the last couple weeks, um check out the new Lakeflow editor and take take a look at how that works if you have tried it and we're struggling with the developer experience and let us know what you think. And then it's pretty easy to version control. It fits well with data bricks typical CI/CD. Here's a quick view of kind of as you're doing medallion architecture, you would do streaming views, streaming tables, um, and eventually you would get to more complex objects and code.
Data quality and monitoring, we have expectations that we can use. There's some data quality that's just kind of there for you to see or at least some uh, aggregations and metrics that are just kind of there for you when you run a DT pipeline. Uh, note that this is running on a different type of compute. DT each DT pipeline runs its own cluster and that can be a serverless cluster serverless compute or it can be a cluster where you kind of configure the nodes or take the default and so it's different it's not running on the SQL warehouse so it scales horizontally um it has enhanced autoscaling capabilities classic gives you more control if you want to size the nodes make sure you know exactly how much room it has to scale up and down and uh but serverless has some addition V b b b b b b b b b b b b b b b b b b b b b b b b b b b b b b b b b b b b b b b bs like that incremental recmp compute.
Uh LakeFlow connect I believe is is serverless and streaming capabilities. I'm not sure if that's if there's still streaming features that aren't available outside of serverless, but I think what I wanted to call out was that when you're running those streaming tables from DBSQL I showed you at the very beginning or early on, those are going to run on background DT serverless compute. Um, so when you get started with that, learn, see some of our blogs about how do we monitor the cost of these things.
So you know, because it's a little bit more hidden for most of us than if we set up a cluster with max number of nodes and we're watching how long it runs for. All right, let us do demo. Demo might be too strong a word for what I'm about to show you, but I do want to show what these look like out in the wild in the workspace. So here is This is not what I'm proposing you do. This is a Oh, and then I'll show you the DBT task at the end.

35:49 Practical workflow implementation

Um, this is a simple SQL task just to kind of highlight what a single task without all the dependencies looks like. I create it. I pick the type of SQL. Notice we have a bunch of other options here, including notebook, which is a popular way to do SQL or orchestrate these. I give it I pointed it to a query that the workspace knows about and um you can there it is um you could also point it to a file like I mentioned earlier set the warehouse I set some job level parameters that'll get pushed into my query and then I can keep building from there to build something more meaningful and this is the monitoring view of workflows uh much much broad deeper topic than we'll get into.
But you can see easily that I failed at this until I got it working and I said enough for a demo. I'm out. Uh if we go to a more advanced uh more complex one, this is it's another variation of this TPCDI process. None of this that I did for this demo is benchmarking, but I use the benchmarking examples because colleagues have already made that code public and I could just grab it and tweak it quite a bit apparently, but get it to work.
So, um, I've got this pretty complex lineage, but I can go in and see that in this case, we are using a notebook task. We are setting up the dependencies. We are driving some parameterization. Maybe we won't see that. Um, but that's what you would see if that opens up. Mostly I wanted to show you that complex lineage becomes visible. I can navigate through it. Um, and then hopefully you are aware already that you could export that YAML, put it in data bricks asset bundles and have a deployable process. pretty pretty easily.
Now, I mentioned this for each task. This is an idea of the for each task. Query a metadata table with a SQL task or a notebook. Take some of those values and pass them along to the next phase and then loop through a certain set of code with the input being the rows that were output from my earlier one. And you can run mini in parallel this way as well. on the DLT side. What I want to show this is kind of a quick glimpse at the new editor.
And so again, I have a lineage. I can work with it. Maybe this thing's important because everyone's building it this way. I can have my code and lineage side by side. Once I've run it, my cl my compute is not running, I don't think, so I won't click around. But I can do data previews. I can see metrics from when I did it. I can run a single table at a time uh with that kind of button. And when I look at this code, we've got things that look more or less like what I had in the slide.
Create a refresh of materialized view is basically what that is. But we also have some really advanced stuff like slow changing dimensions with history tracking slowed type 2 built into the product. And if it loads up, what we're going to see is that it's this apply changes command. Apply changes into set of parameters you provide. it'll handle either the type one or type two slow changing dimensions. So really powerful stuff and I this is in SQL is the main reason I wanted it to load up so you can see this isn't Python code in these examples.
All right, the other piece here and then we'll take just a couple minutes talk best practice and I'll get you out of here. Uh DBT core. I can run a DBT task by choosing the DBT task type and picking either my git provider or a workspace location. put any of the DVT commands I need in these command sections. Point it to that SQL warehouse that'll run it really well for me. Set the catalog and things here and then again export.
I need some sort of CLI compute that I'm going to run on. This is really small. It's basically just running DBT command. So I usually use serverless here and then that's going to do its thing and I can obviously build up dependencies and things within the same workflow. So to wrap us up uh when you if you are working with these some or if you're doing anything with data bricks remember this one remember your table optimizations take a look at predictive optimization and either liquid clustering or pitching and zorder to get these tables working well use serverless SQL warehouse and remember that DT MVs uh materialized views and streaming tables do not use serless SQL warehouse and then optimize ylars this is the important part is I see these mig migrations.

40:21 Best practices and conclusion

And when you migrate from some other system to data bricks with these tools, what you tend to find is there's like a small number of things that are taking longer than they used to or at least aren't meeting the benefits we wanted from moving. Um, that's a good time to learn about reading the query profile. Uh, first thing you'll do is learn, do I need to go back and do some table optimizations, even if it's within the process of the ETL pipeline?
Do I need to simplify the way these models are set up to make sure the query plan's the best it can be? Do I need to say, "Hey, data bricks, I'm struggling. Bring in a specialist." That is it. So, these are my contact info. You're welcome to reach out with follow-up questions. I'll hang here and then move to the hall for some short ones. I'll take you I'll take that right here. I think I'm over time already. So, all right.
Thank you all. 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.