In this short demo, I'm going to show you how a data engineer can build an end-to-end production
ETL pipeline with quality, scalability, and reliability using Databricks Delta Live Tables.
To start, I'm going to read some raw sensor data that's stored in a JSON format on cloud storage,
such as AWS S3, and do some basic ETL, as well as apply data quality controls to the ETL pipeline.
First, I'll write a SQL query that ingests all the data, I'll simply write a CREATE
TABLE statement with the incremental keyword, calling the table raw data. It's important to
specify the incremental keyword because we don't want to reprocess all the data every single time.
Now I'm going to select all the sensor data and point to the location where the files are stored
using the cloud files syntax, and in this case, it's a JSON directory. This will invoke the Auto
Loader to incrementally and automatically load sensor data as it arrives into the data lakehouse.
Now that we have data ingested into the lakehouse, let's do some basic ETL.
Or create a live table called readings. And write another SELECT statement
that does some basic transformation for each of the different columns. So for example, we'll use
a timestamp function to parse the timestamp, and cast the sensor reading to a number data type. To
ensure data quality and integrity and we're going to add data expectations to this table. I'll add a
constraint in show we have a timestamp because it is difficult to parse. So any invalid timestamps
that arrive will get recorded as bad data and Delta Live Tables will let us know about that.
Additionally, I can document what we have done so that other data engineers in my organization can
understand and discover what these tables are and where they're coming from. So here I'll
add a comment to say raw sensor data from JSON. And we'll go ahead here and say, and
comment sensor data with some basic ETL. What's important to note is the live syntax reference
throughout the data pipeline, as a data engineer have defined what I need this pipeline to do.
The live syntax refers to the runtime mechanism of how Delta Live Tables understands the queries,
analyzes the pipeline to build the lineage of the data flow. Delta Live Tables then takes
care of all the environment configurations needed to run this ETL pipeline, either for
development or production. So I can focus on applying the transformation or business logic.
Now that we have built our ETL, let's go ahead to the pipeline details page. This pipeline can be
set to run continuously for your streaming use cases, or triggered to update the tables once
on a scheduled update. I'll go ahead and start the pipeline. And as you can see,
we've got all the operational information about the pipeline right here. It's currently
initializing and setting up all the different tables that we've created in that pipeline. Once
Delta Live Tables parses and understands the SQL queries, it's going to show this data flow graph.
So you can see, it's going to read the raw data, and then it'll flow into the readings table.
On the right hand side, I can see all the stats related to this ETL pipeline,
the status, the duration, the schema details, as well as the data quality statistics on
how many records were written to the target, or how many were dropped. So we can successfully say
that all the timestamps were valid. Now, all this information is not only surfaced through the UI,
but is also available in an event log and in a Delta table, which you can query using SQL.
So you have multiple ways to really understand the lifecycle of this ETL pipeline to see how things
change over time to see the performance or any errors or the reliability of this ETL pipeline.
In this short demo, you have seen how Delta Live Tables, a cloud-native service in the
Databricks Lakehouse platform, provides a reliable ETL framework to develop,
monitor, manage, and operationalize data pipelines at scale. Thank you.