SQL Is Still Your Superpower: Building Production Ready Data Pipelines Using Only SQL

Summary

This video demonstrates that modern data engineering on the Databricks Data and AI platform can be delivered end-to-end using SQL alone, including streaming ingestion, CDC transformations, data quality rules, governance, and AI/BI Genie configuration — without Python or external frameworks.
The medallion architecture is implemented entirely in SQL using streaming tables with CLOUD_FILES for bronze ingestion, APPLY CHANGES INTO for CDC silver layers, and materialized views with EXPECT clauses for data quality, all within Lakeflow Spark Declarative Pipelines.
Unity Catalog provides fine-grained governance across all three layers of the pipeline, and the entire solution can be promoted across three environments using Databricks Asset Bundles, making SQL a viable first-class engineering language for organizations with strong BI and SQL capability.

SQL Is Still Your Superpower: Building Production Ready Data Pipelines Using Only SQL

SQL didn’t get weaker, data platforms got better. In this video, we demonstrate how modern data engineering on Databricks can be delivered end-to-end using SQL alone on the Lakehouse, without dependency on Python or external frameworks, including SQL AI functions and Geospatial Functions. Designed for organisations with strong BI/SQL foundations, this video shows how to transition to production-grade data engineering with minimal upskilling. We showcase how pipelines are built using Lakeflow Spark Declarative Pipelines via the Pipeline Editor, with ingestion, transformation, and data quality rules all defined declaratively in SQL. This demonstrates how SQL moves beyond querying into full pipeline engineering. Governance is implemented by design using Unity Catalog, providing fine-grained permissions, lineage, and auditability across the entire pipeline. We also showcase AI/BI Genie, configured using SQL-only approaches (metrics, dimensions, instructions, and evaluation queries), enabling natural language access to trusted data. this video reframes SQL as a first-class engineering language on the Lakehouse, capable of handling streaming, transformation, data quality, governance, and AI consumption, while unlocking existing talent and accelerating adoption without increasing complexity.
Talk By: Bianca Stratulat, Chief Data Officer, Unifeye ;

Chapters

FAQs

Can you really build production data pipelines using only SQL on Databricks?

Yes — the Databricks Data and AI platform has invested heavily in making SQL a full engineering language for the lakehouse. Lakeflow Spark Declarative Pipelines supports streaming ingestion, CDC, and data quality rules in SQL; Unity Catalog provides governance; and AI/BI Genie can be configured entirely through SQL metrics, dimensions, and evaluation queries — no Python required.

How is CDC (change data capture) implemented in SQL on Databricks?

CDC is handled using the APPLY CHANGES INTO statement in Lakeflow Spark Declarative Pipelines, which tracks changes from source systems and applies inserts, updates, and deletes to silver-layer tables without requiring Python Spark code. This declarative SQL approach handles the complexity of CDC transparently within the pipeline definition.

What does data quality enforcement look like in a SQL-only pipeline?

Data quality rules are defined using EXPECT clauses within materialized view definitions in Lakeflow Spark Declarative Pipelines. These clauses specify what conditions data must meet and can be configured to quarantine bad rows, warn without failing, or block bad data from progressing through the medallion layers — all expressed declaratively in SQL.

How does a SQL-focused organization manage CI/CD for Databricks pipelines?

Databricks Asset Bundles support three-environment promotion (development, staging, production) and can deploy Lakeflow pipeline definitions, Unity Catalog objects, and other assets in a version-controlled way. This allows SQL-first organizations to adopt production-grade deployment practices without needing to adopt Python-based orchestration frameworks.

Full transcript

[00:10] Welcome and thank you for being here today. I am Bianca Stratulat. I'm the Chief Data Officer at Unify. Unify is a pure play Databricks partner based in UK. I'm also a Databricks Champion and a Databricks MVP.
[00:22] So, this session is particularly aimed at organizations with strong BI and SQL capability who want to modernize without heavy retraining.
[00:34] So, today I do want to challenge an assumption that organizations have adopted over the last few years that modern data engineering automatically means Python
[00:47] heavy stacks, complex orchestrations, frameworks, and highly specialized engineering teams. SQL is often treated as legacy querying tool, but the reality is
[01:01] it's actually very simple. SQL didn't get weaker. Data platforms got better. And what I will be showing today is how organizations with strong SQL capability can build fully governed
[01:16] production-ready data pipelines just by using the Lakehouse with SQL alone. So, no Python, no DBT, no external frameworks, just SQL. By the way, this is a very short session. We
[01:29] are now We are having around 18 minutes. There will be a follow-up and webinar organized by Unify on how to. So, it's an follow-up because it's a short session. Okay.
[01:41] Setting up the the agenda, we are going to understand why SQL is the right foundation, the business case and the technical case, the architecture worked through the bronze, silver, the
[01:53] medallion architecture essentially with finishing with the gold layer, but also the Unity Catalog governance and BI Genie. And we are going to see also how we can map this to a production grade CICD
[02:08] pattern. So, many organizations have hundreds of SQL developers. If we are talking about the big organization, especially onto the public sector, who understands the
[02:20] data, understands the business, and they are ready to build. The problem is no skill. The problem is the perception. There is a misbelief that modern data
[02:32] engineering, as I said, requires Python, Spark, and specialist engineering knowledge. The belief is quite wrong, at least in my opinion. Or rather, it used to be
[02:45] true, but it's no longer as we are into this modern data landscape. Databricks has invested heavily into the SQL, making it a full
[02:57] engineering language onto the lakehouse. The Lake Flow declarative pipelines, the Unity Catalog, the BI Genie, the Databricks SQL endpoints, all these are actually SQL first
[03:09] products. So, let's look at what that is in practice. So, um the conversations that we are having
[03:21] with the organizations, so they have the BI teams, they have the reporting teams, SQL analysts who deeply understand the data, but when it comes to modernizing lakehouse, the conversations oftenly is blocked that we
[03:35] need to hire software engineers, we need to hire Python engineers, we need to hire an application engineer to do the front end. Or we need to retrain everyone, and it's
[03:47] very expensive, and of course, they are being blocked by the stakeholders. So, this clearly creates a friction point that is slowing the adoption.
[03:59] So, Databricks allows you to start exactly where teams are. SQL teams can write streaming tables, transformation pipelines, data quality rules, and governance all in
[04:11] SQL. So, we're going to go through the um medallion architecture, the bronze, the silver, the gold using um
[04:24] uh the concepts of course from from Databricks, which every single year being defined in SQL. The pattern that I'm going to show can work for any data set, so you can give it a try
[04:38] after this. Saying that, um for those that are very new to to the Databricks, the bronze is the raw ingestion using streaming tables in SQL.
[04:50] Auto seam the CDC handles change data capture schema. It is going to inference. It's going to be inferred, so we don't need to define every column up front. The pipeline can be incremental
[05:03] by default. By the way, I am presenting only one way. That doesn't mean that is the single way of doing things. I'm just trying to provide you a pattern that can be put into production. Uh the silver,
[05:16] this is where the data engineering happens. The SQL um defined transformations. You can do joins, deduplication, standardization. Data quality expectations are declared in line using expect clauses. Uh you choose
[05:29] to drop, to fail, or warn on constraint violations. Uh and lineage is tracked automatically because of the Unity Catalog. Uh the gold is the business ready
[05:41] uh tables. There are tables your analysts and the um genie will uh, query. These are fully optimized, pre-aggregated, semantic ready, um, and, um, ready to be used.
[05:53] Onto the governance, you have the Unity Catalog that sits across all the three, uh, layers. Every table, column, and row is governed. Lineage is visible across the full pipeline. No additional
[06:05] configuration is being required. Uh, onto the orchestration, Databricks workflows, uh, can handle the scheduling and the sequencing, uh, and, uh, you have the Databricks assets and bundles
[06:18] to, uh, define the full, uh, deployment. Uh, so, using just the SQL code base, you can move from dev to test to prod.
[06:30] So, we are going to start at the very beginning, uh, getting raw data in. The ingestion layer is often, uh, where data, um, teams reach first for, uh, for Python, uh, but, uh, today I'm going to show you that, uh, is not, uh,
[06:43] the case. So, uh, let's walk through, uh, the syntax because it's, uh, actually very simple in SQL. You, of course, you are going to start with, uh, the create, uh, statement. Uh, streaming table, uh, is
[06:57] here, uh, the key point. This is a native, uh, SQL in, uh, the Lake Flow declarative, uh, pipeline. So, you can see that there is no Spark API, no Python. Read um, files is a SQL function that is handling the file ingestion, the
[07:10] schema inference, and the incremental processing. Uh, the pipeline is going to handle, uh, checkpointing, uh, automatically, so you don't need to, uh, manage, uh, offsets or, uh, state.
[07:23] The auto, uh, CDC can be added with apply changes, uh, into for change data capture patterns. So, really is about SQL and SQL only.
[07:35] Um, now the raw data is in. Uh, now, uh, we need to make it, uh, trustworthy. Silver is, uh where the data quality rules uh, live. Again, only on SQL can be done.
[07:47] So, um you are going to use again the create. I haven't put in here the syntax, but it's a very similar, but this time you're going to create a refreshed materialized um uh view, uh which is um again standard
[08:00] uh SQL. Any analyst who knows uh select where join can actually write um uh write this. Uh and the most important thing, the data quality is not a separate tool or
[08:13] uh or process. Uh it is uh declared in line um in the pipeline uh definition. The data uh the pipeline it is going to enforce it. And of course, the Unity Catalog is uh going to track all the
[08:26] violations. Lineage is going to show which rows passed or uh or failed. And most importantly, this is governance by design. Uh governance here is not an afterthought.
[08:43] Okay, now that the data is clean and trusted, um we can serve it to to the business. And the gold layer um is where the SQL actually shines as an analytics ready uh layer. Um so,
[08:56] um you have um here what it means the the gold uh layer, which I said actually from the beginning. But, as a gold layer, you can have average property prices uh by
[09:09] county, by year. Uh you can name it. So, it's uh you can see actually a pattern emerging onto the SQL side. It can be only a group by, no? So,
[09:21] um this is going to become your business metric. And you can have multiple business metrics that are being uh written in um in SQL. Most importantly, the table it is going to be exposed um
[09:33] or queried using uh database SQL uh warehouse warehouse endpoint, which is very performant in database uh and specially if you are using serverless option. Uh analysts can query it
[09:46] directly. The Genie, it is going to be able to use it. The Power BI, Tableau can connect to it. Uh more than that, Databricks now offers um
[09:58] uh AI SQL functions that can be uh used. The AI generate um uh text, but you also have the geospatial such as
[10:10] ST_Distance, um and you can properly um uh analyze locations. And there is no need for another framework. Um if you are more curious about uh the geospatial uh
[10:25] functions, um we as Unify have a dedicated webinar for that, and you can find it recorded on on YouTube. Just by searching it.
[10:41] Okay, so uh now that we have the data at the gold layer and we can query it, the data is governed, it's trusted, we need to make it accessible to the uh whole business through uh natural language. It's the most uh common
[10:54] request nowadays from the businesses. So, uh Genie uh is configured entirely in in SQL, so you have no custom code, no data science skills required. It's an out-of-box solution presented by
[11:07] Databricks to all of us. And we saw today that actually it is Genie Series being elevated. Um and we have now three versions of Genie.
[11:21] So, um let's understand more about um Genie. So, Genie is all about defining your metrics uh in in SQL. Uh you do need these definitions to exist.
[11:35] Uh, you need also dimensions to to specify such as county, property type, year of sale, and you are going to define this as sequel references. You also need evaluation queries. Uh, these
[11:49] are test questions with expected sequel outputs. You write the question and the current sequel answer. Genie will learn from this. So, uh, you also need to leave instructions. Uh, these are plain text
[12:02] instructions that will tell uh, Genie how to interpret the business terminology. So, uh, for example, when uh, business user is asking for um, the most recent data uh, to use the
[12:16] last 12 months or maybe 3 months as per the business requirements, of course. Um, what is the key message uh, in here? So, businesses uses um, the
[12:30] trusted data through the natural language. The analyst uh, team uh, it is not going to need to build the full dashboards. That is the main um, uh,
[12:42] gain. And uh, the IT or the infrastructure team or the governance team in the organization has the confidence because the Unity Catalog it's governing the underlying data.
[12:58] So, uh, going back to the pipeline, uh, the pipeline is being built, but how do we make this production ready? A common concern from engineering team is is sequel good enough or is based is is
[13:11] sequel based development compatible with a modern CI/CD practice? And the answer is yes, for sure, and it's 100% uh, achievable. So, um,
[13:23] we have um, the Databricks assets bundles that is going to make that possible. Um, Let's walk through the three-stage promotion pattern that is available in Databricks. You have the
[13:35] development, the SQL written pipelines into the pipeline editor. This is where you are going to start your development as engineer. You will have this parameterized for the environment. You are going to have catalog names, schema
[13:48] names injected via job parameters. And you are going to have your bundle defined into YAML files. Where you here you are going to describe all your pipelines, jobs, clusters. And
[14:01] the developer it is going to manually deploy this against the dev workspace. For testing, this is where the Databricks workflow triggers into the pipeline into
[14:13] the test environment is coming in. The data quality expectations act as automated test gates. And of course in here you are going to do also the evaluation for
[14:26] the genie space. For production, we are going to follow the same pattern. Promotion happens via the Databricks asset bundle deployment. You are going to use the same SQL code
[14:39] base but with different parameters. And again, the Unity Catalog is behind everything ensuring that everything is being audited trailed. And the data isolation
[14:51] between the environments exists because of how you are going to define the CICD pipeline. So, everything is one SQL code base. You have three environments, full governance. And you don't need a
[15:04] specialist DevOps skill beyond what Databricks asset bundles require. So, I would like us to
[15:16] summarize some of the takeaways from these sessions Uh, because we are approaching now the end. Um, SQL is production grade. Everything that was showed today from stringing, ingestion,
[15:30] the pattern, the transformation, the data quality, the governance, uh, was by just using SQL. So, literally, you just you need just to use the existing
[15:43] team, and that will be your starting point. And not to be, uh, blocked because the skills don't, uh, exist. Utilize what you have, and work with your existing team.
[15:55] Uh, Databricks, uh, it's offering you most of the capabilities. Yeah, you don't need DBT, uh, you don't need Airflow, no external, uh, tools. Lakehouse, Unity Catalog,
[16:08] Databricks SQL, uh, use the jobs, the Ascent, uh, Ascent Bundles. All are Databricks, uh, products, all SQL compatible. So, if your SQL, um, if your organization has, uh, SQL
[16:21] expertise, uh, you, uh, you have a pointing, uh, start. You just need to, uh, take the decision to start the development. The, uh, barrier is actually lower than what you think. So,
[16:35] the time to value is faster than the traditional approaches. And to, uh, leave you with, uh, an after thought, I would want to say
[16:48] that modernization doesn't need to begin with replacing your people. Sometimes is, um, uh, it begins by finally giving SQL the platform it always, uh, deserved. So,
[17:01] please do not, uh, start by, uh, firing your people. Just work with them. Thank you.

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.