- My lightning talk today is about a small project
that I worked on recently, based on some feedback
that I had from the field.
And I'm going to share my screen,
and kind of show you the project that I created.
So I call this the Delta Lakehouse Data Profiler.
When I've been working with different organizations
in the field about managing their Data Lakes,
or their Delta Lakes, on databricks,
one of the very common things that you know,
I come from a data warehousing BI background,
and one of the very common tools in our toolkit has been
to have the information schema if you come
from like a SQL realm, or the schema
of your database right.
And there are many tools out there that do this,
but it's pretty simple to do you just got
to catalogue all your information.
So I kind of built something that put that together, along
with some profile information,
some data profiling statistics, very commonly,
as like a data engineer or a data analyst
or an analytics engineer, I need to know what's going on
with my data, from a statistical standpoint,
to understand how to build Delta Tables,
or how to work joins, if I have to see
to diagnose different issues with my data and my joins,
I need to understand the profiling statistics.
So what I did was I made a little notebook here
to build those constructs those tables for you,
so that you can run this in your environment,
pick a database, run it, and then I'm going
to show you some ways on how how it works with Lakehouse.
So with the new stuff that we announced this week,
SQL analytics or Lakehouse in general,
how can you use this paradigm with a project like this.
So let me show you.
I'm going to create two constructs,
there's going to be a table called Profile Data,
and there's going to be a table called Schema Information.
And that's the schema of those two tables.
And essentially, I'm going to be doing some,
some spark configs just optimizations for delta
to be able to write efficiently.
And then I'm going to this just notebook magic,
I'm going to create a widget, I'm going
to populate it with database choices.
So just show databases.
So I want to choose all the databases in my environment.
And then what I do is use some Python and SQL
to generate the information.
So if you come from like SQL realm, T-SQL
and PL SQL have like dynamic, programmatic extensibility,
I do the same thing with Python,
it's very common methodology,
but I create I use that dynamic process
to create the information,
and then I create the tables,
inside of the database that I'm using.
And then I also create temporary tables,
for an interesting trick that I found.
So essentially, what I'm going to do is I'm going
to have a process that will loop through all of my tables,
and then it will create the Schema Information.
And then it will create and it kind of puts it all
into temp tables.
And then it goes through all of the columns in each table.
And it collects statistics on those columns,
just basic data profiling information.
And it stores inside those Delta tables.
And then finally, I merged the temp tables
into the actual tables themselves,
so that we can have like the actual tables
inside the database, and then I clean up after myself
and optimize those tables.
So I have these tables now in my Delta Lake.
And this is what they look like if you just select them
inside the notebook.
Here I have my schema information,
which has the database name, table name, column name
and data type.
And then we can add any metadata and if we like.
And this is the basic profiling information,
essentially it's just the value, the number of those records
and then the length if it's a string.
So just simple data profiling statistics
so that I can do come look at my data understand it
from a high point of view as an architect analysts.
So what can I do with this data?
Now we were on Lakehouse right?
We have a new SQL analytics tool can can we use some
of the new tooling to visualize our data? Absolutely.
So what I did is I created a few queries
that use this statistical information that I created
with the data profiling task,
and then I created some dashboards with that.
If we go into dashboard, I can pull up the overview.
So what this shows is, at a table level,
it shows me my the statistics of my data profiling outputs
that I just generated in the notebook.
So anything that generate in the notebook environment,
then in my dashboarding environment,
I can use that same metadata or those databases.
So I look at the tables there.
And I can see at a high level,
what the data looks like distinct values.
So I have to look at cardinality to understand what's going
on in my data, if I have to do optimizations
for my analysts, like, do Z-order
or partitioning strategies.
So what if I want to look at a specific column,
let's say that I am... I want to look at store sales,
I have some type of situation where things are coming,
not how I expect them for a specific column,
let's say net profit.
So I can click down here in this column detail.
And what will happen is that it will,
I can look at a high level at my table,
and then that can drill into my columns.
So I can see statistical information
about columns themselves.
So if I need to understand from a high level, what's going
on with my value distribution,
or what my data is made up of,
like how many of those distinct values are there
so that I can better understand how to optimize these tables
and constructs right for the in order
to get good performance for analysts.
So essentially it allows a way for you to understand
at a high level very quickly, what's going on
with the data in your Delta Lake.
So one other thing that I built for this,
was not just statistical information,
but one thing that happens very commonly when you are kind
of dissecting performance at scale,
is that you have different join operations.
And oftentimes, you know,
you'll hear things like you have all your data skewed,
do you have a (indistinct) to manage skew join?
And, you know, I was always kind of like, Well,
how do I figure that out right?
For me, I have to kind of visualize it in order
to understand what's going on.
So this I built this joint profile, essentially,
you can pick two sides of a joint.
So if you think about, like keys in different tables,
so you can better understand, you know,
what will happen when you join these fields up,
what the data is made up with,
and then you can understand how
to optimize the join criteria.
So here I'm just going to choose reason, run that,
and then I'm going to go into one of my destination tables
for reason and see what it would look like,
when I join those tables up.
(indistinct)
So when I would join these two tables up,
and this is all based on TPC, DS data.
So it's benchmark data.
So it's meant to be homogenous.
It's synthetic data.
But essentially this gives me a good idea
of what would happen when I join these up,
how my join profile would look if I have
to kind of account for any type of optimizations.
And you can think like downstream, I can add a hint,
if I need to, or something like that, inside the query
in order to kind of better optimize,
and put a nice little graphic down here that helps you,
you know, I know everyone knows how joins work,
but you know, I'm a visual person,
so I like to make it simple for everyone.
With that, that's the project I did put it out on git,
so that you can download it and this notebook,
and create with inside of your environment.
I do have some things I'm still working on
on the Data Profiler,
for example, I have to figure out how to wait
to remove things that have been profiled previously.
I'm still figuring that part out.
So if you want to contribute, cool, if not, you know,
I'll be working on it as I see fit.
With that, I'll take any questions.