Hi, this is Richard from Databricks. And today I want to show you how Delta Live tables
makes it really easy to build declarative ETL pipelines on batch and streaming data using SQL.
The pipeline will build powers this dashboard, showing near real time bike availability and live
weather information for every Divi bike station in the city of Chicago. I speeded up the video here,
so you can actually see the data moving over the period of an hour or so.
The fact that this data is current makes the analytics incredibly actionable.
You can react instantly to changes in the data and reallocate bikes to stations with
low availability. You can also see the impact of changing weather conditions.
And you can travel back in time, and look at any date and time in the past. Here we can see what
happened on February 2 at 9:30am during one of the biggest snowstorms we had in Chicago this year.
But turning simple SQL queries into continuous and production ready ETL pipelines is really hard,
as it involves setting up a lot of complicated operational components.
Even at a small scale, the majority of data
practitioners time is spent on tooling and infrastructure, not on transforming data.
Delta Live Tables is the first ETL framework to use a simple declarative approach to building
reliable data pipelines. It understands data dependencies live, while automating away virtually
all of the inherent operational complexity. This allows SQL savvy data analysts and
Python centric data engineers to worry less about operations and focus on getting value from data.
So this demo our data sources of Chicago Devi bikes and open weather rest API's.
were capturing this data using simple Python scripts scheduled as Databricks jobs. Our scripts
run every minute to get the real time availability of bikes. And the current weather conditions
are the 900 or so stations across the city. The raw data is captured as JSON and stored
in our cloud data lake. We started collecting data about three months ago in early January,
and have already captured over 1.5 million files. So let's take a look at how Delta
Live Tables helps me turn all these files into a production ready data pipeline.
This is the notebook I use to write my code. SQL is my skill set. But you can also use Python
with Delta live tables if that's your thing. First, let's look at the station status stream.
Best practice is to implement a medallion architecture, going from bronze to silver
to gold tables. So I'll start with a bronze table called Raw station status.
Since data is continually arriving, I've created this as a streaming live table,
which will process new files incrementally as soon as they arrive.
But documentation governance and discoverability purposes, I've added a comment describing what
the table is for and added a property tagging the table as bronze quality.
Next, I simply select star from Cloud Files, passing the location of the files in cloud
storage, and the expected format, which is JSON. This invokes Databricks auto loader,
which is an optimized streaming pattern to efficiently ingest data into the lake house
without triggers or manual scheduling. It's perfect for this type of use case.
Now, this raw data is ingested into the lake house, I need to clean it and transform it so it's
ready for analytics downstream. So I'll create my silver level table. I'll call it clean to station
status and define it as a streaming live table to so new data just flows right in.
I want this table to contain high quality data. So this is where I define a quality constraint.
It expects station ID to be not now if it is now the record is dropped.
The second constraint checks for stale data. I can define as many constraints as I want
to fail the pipeline completely drop records, or just flag the issue and move on with all this
information being captured in an event log for detailed monitoring. Here I've partitioned the
table and created some additional documentation next define how and where to get the data from.
My SQL logic refers to the previous streaming Live table, we created raw station status. It then
explodes the JSON into multiple rows, and prepares the columns exactly how we want. This is where
we start to see the power of declarative data pipelines. The syntax enables Delta Live Tables to
understand the queries and automatically build a lineage of the entire data flow.
Next, we follow the same process for the real time weather stream. First, we create a bronze
level streaming live table called Raw weather information that invokes auto loader to read
the data from Cloud Storage. Another reason auto loader is so perfect for this use case is that
it automatically infers the schema of any JSON payload, so I don't have to define it in advance.
It also detects changes in the schema over time, and captures any unexpected data in a
rescue data column, then restarts the pipeline for me, so I can start using the new data right away.
This capability is essential for the open weather API, as the response payload varies over time.
For example, if it's not snowing, you don't get a snow column in the payload.
Here, we transform our raw data into a silver streaming live table called cleaned weather
information. Reading from the bronze table. Once again, we create quality constraints
documentation, and define all the transforms we need.
Finally, we create another bronze streaming table called Raw station information. This data
set is different from the last two, in that it's more like a slowly changing dimension. The data
describes each of the 900 or so stations in terms of dimensions like station name, station type,
geospatial location, and so on. After exploding the JSON structure into a temporary live table,
we then use the Apply changes into syntax to merge or upset records into the silver level table
called Clean station information. This feature either overwrites an existing station,
or inserts information about new stations that appear in the data based on the station ID key. In
reality, this data doesn't change much. But this process ensures that any new station information
is reflected on the dataset instantly, keeping our analytics as fresh and accurate
as possible. I've gone about as far as I need to in terms of transformation logic,
the silver tables I created, give me all the flexibility I need to build my Dashboard. Once
I understand usage and query performance, I might build some gold level aggregate tables. But for
now, I'm good to go. Next comes the headache of trying to figure out how to turn all this into
a production ready data pipeline. To do this, I head over to jobs and click on Delta Live Tables.
You can see I already created a pipeline which we'll look at in a minute.
But for now I'll create a new one. I give the pipeline a name and point it to my SQL notebook.
Next, I enter the name of a target database to create all the tables.
Now I can easily run my pipeline in different environments. Maybe I have a database on my
laptop for development before switching to a staging or production environment. This essential
enterprise capability is made easy by Delta Live Tables. Next, I specify a location to store all
the table metadata for the pipeline. Again, I'll leave it blank and let the system decide for me.
Since the requirement is for a near real time dashboard, I'll make this a continuous pipeline.
If I wanted to sacrifice real time analytics and schedule the pipeline to refresh periodically,
I could pick the triggered option allowing me to shut down my cluster in between runs
to keep costs down. I'll enable auto scaling. So my cluster dynamically resizes up and down
based on the load. And here I'll set the minimum or maximum number of workers for my cluster
and click Create. Now all of the wonderful Delta live tables automation kicks in.
It first waits for the cluster resources, then initializes the pipeline and sets up the tables.
Finally our graph is running and the data is flowing. Notice how Delta lives Tables
automatically understood the lineage of the data pipeline from the declarative SQL I created.
It starts by backfilling all of the data from the files already captured in Cloud Storage.
Once all of the history is loaded, the pipeline instantly processes any new arriving data,
streaming it through the tables in the order we specified. Let's quickly take a
look at how to manage an existing pipeline by looking at the one I created earlier.
Over on the right, I can see some basic metadata about the pipeline, such as the name,
the notebooks that uses when it was created, how long it's been running for, and so on.
Clicking on any of the nodes, I can see information about the specific table, including
its name, type, some runtime information, and the comments we created when we define the table.
We can also see the table schema at the top of the page I have some controls. This drop
down allows me to look at the current run or any run in the past to see what was going on.
I can toggle between development and production modes here
to affect the underlying infrastructure the pipeline runs on. in development mode,
I can avoid automatic retries and cluster restarts. But switch these on for production.
If I click here, I can assign different pipeline permissions to users and groups.
I can edit existing pipeline settings here and schedule my pipeline here. Since this
pipeline is continuous, will not create a schedule and just leave it running.
Finally, if I click start, my pipeline will stop processing data, I can then decide to restart it
and either reprocess all historical data. Or we can just start where we stopped the pipeline last.
Below the graph, we can see all the information about the pipeline captured in the event log.
This is stored as a Delta table to so we can do any type of analysis on it in real time
or in the past. We can see all of the actions on the pipeline, like when it was initialized,
when the tables were created, when the data flow started, and so on. Literally
every event is captured here. So let's head over to Databricks SQL, and take a look at
how we can explore our event data to take a look at things like pipeline health and data quality.
This dashboard is built on top of the event log via a Delta table. There are literally
hundreds of different ways I can slice and dice this data. And these are just a few examples.
I'll click Refresh to get the most up to date view. I can quickly see metrics such
as the number of records processed since the pipeline was created, the percentage of failing
records based on our data quality checks, and the average records process per day.
Below, I can look at data quality trends over time to see exactly when problems started.
I can also look at different pipeline actions over time, like create, edit,
start and stop operations and see who perform these operations. No surprises here it was me.
Finally, I can look at data quality by data set
and by expectation to see exactly where problems occur, allowing me to fix the issues upstream.
Delta Live Tables is the first ETL framework that uses a simple declarative approach to building
reliable data pipelines and automatically manages your infrastructure at scale
enables you to accelerate ETL development using declarative languages in Python and SQL and apply
modern software engineering best practices. It automatically manages your infrastructure
and removes overhead by automating complex and time consuming activities like task orchestration,
error handling and recovery, auto scaling and performance optimization It gives you
confidence in your data with built in quality controls, testing, monitoring and enforcement
to ensure accurate and useful data for business intelligence, data science and machine learning.
And it simplifies batch and streaming workloads so you can provide the freshest data to your apps.
If you'd like to learn more about Delta Live tables, please visit the link shown here.
We'd love to talk to you. Thanks for watching.