STEPHANIE WONG: Hi.
My name is Stephanie Wong, and today, we're
going to explore how BigQuery, Google Cloud's fully
managed enterprise data warehouse,
enables you to run analytics at scale, using standard SQL
with zero operational overhead.
It provides unique functionality,
like realtime stream ingestion, query federation,
GIS functions, and built-in machine learning capabilities.
And to bring this to life, let's look
at how New Kicks, a fictional brick
and mortar and online retailer, uses advanced analytics
and BigQuery to optimize their supply chain.
The challenge started when the director of marketing
wanted to make better ad targeting decisions.
The task was given to the lead data scientist and data
engineer, and they recognized four challenges to overcome.
First up was to break down the various data silos
within their organization.
The data engineer at New Kicks built out a one-to-end mapping
of key data sources, including Google Analytics, raw click
stream, online transactional, and in-store inventory data
that all move data into BigQuery.
Store operations data is stored in the transactional database,
Cloud SQL.
These data sources were ingested via combination
of BigQuery data transfer service and Cloud Dataflow
in batch and streaming mode.
Let's take a look at this in action.
This is the Cloud Dataflow console
showing New Kicks' real-time ETL for in-store transactions,
stock levels, and click stream data.
This job ran tandem with batch and data transfer jobs
to hydrate various data sets in the data warehouse.
The click stream step was processing around 200 events
per second, with BigQuery as a target output.
This data was immediately available for querying.
For example, this query shows the most recent click stream
events.
Note the latest event is about two seconds behind current UTC
time.
With real-time access to multiple data sets,
they could perform more meaningful analysis,
like this query joining online sales with in-store sales data,
calculating ranks for the top 10 sneakers.
Accessibility across various warehouses
makes the data more actionable.
However, after reviewing current schemas in the data warehouse,
the data scientist and data engineer
realized they needed to perform some additional and possibly
heavy transformation and enrichment, in order to get
the right data sets in place.
And they were going to need some additional processing
resources to get the job done.
Now, to drive key data warehouse workloads like ingestion, ETL,
and business intelligence, New Kicks
used a combination of BigQuery pricing models--
on-demand and flat rate-- to optimize resources and cost.
For lower query volume workloads like query prototyping
and exploratory analysis, the IT manager
utilized on-demand pricing with project-level cost controls.
However, the forecasting project presented new challenges,
like unknown query volume with the need
to minimize disruption to existing production workloads.
In addition, resources were only needed for a few days.
This is where the power and value of flex slots
came into play.
Flex slots let you purchase BigQuery capacity
for short durations-- as little as 60 seconds at a time.
Using the BigQuery UI or the client libraries,
the IT manager was able to immediately allocate 500 flex
slots to a new project that the data scientists and data
engineer could work in.
The serverless nature of BigQuery
enables deploying this additional capacity
in a few seconds, and can be shut down after 60 seconds,
so it's an excellent solution for short-term and bursty
workloads.
With their additional and isolated capacity in place,
the team started to make quick work on transformation
and enrichment.
To drive the forecasting model, the team
needed to produce a filtered and enriched click stream that
had several key attributes, including
customer location, specific target action, and store
location.
This required a series of transformations,
including filtering, joining, and applying
GIS functions, all of which can be accomplished
at scale in BigQuery.
To perform these transformations,
New Kicks' data scientists executed
a series of query jobs inside of AI platform, Notebooks,
with streamlined integration with BigQuery.
The goal here was to produce a filtered click
stream that was joined to customer and in-store pickup
data.
The first query job filtered out clicks
that meet a specific requirement, including
active sessions that ended with a purchase,
and an in-store pickup.
This query reduces the analysis set from 10 terabytes
to 1 terabyte.
Notice that the results were saved back
as a new destination table.
Next, the customer table was enriched
with geocoordinates, which were joined back
to the click stream.
She then joined click stream to orders.
To obtain the geo information for a store,
she used an external query function
to execute a query into the store operations
database, located on Cloud SQL.
This type of query federation reduces data copying.
With geo information for customers and stores,
she applied one final transformation and filter.
Using built-in GIS functions, she
could calculate the distance between customer residence
and store location.
Built-in GIS functions enable you
to project analysis in-line without having
to export your data.
This query produces candidate feature inputs for the model.
If she wants to audit this process,
she can walk back through the tables created.
Over time, these queries could become materialized views
and/or stream or batch transforms,
setting the foundation for a feature store.
With the transformed data in place,
they were able to move onto modeling,
so they turned to BigQuery ML.
If you have experience with machine learning,
you know that traditional workflows
require additional pre-processing, data splitting,
model development, and deployment steps.
But with BigQuery ML, steps like pre-processing,
model development, and deployment are removed.
It lets you build machine learning models
using standard SQL, and without the need
to move your data for external training and evaluation.
First, New Kicks' data scientist and a quick sample
of the transformed click stream data.
For this model, she wanted to look at top line, all products,
per day, click stream volume, so they needed
to apply a quick transformation to group
all of the clicks by day.
No problem.
With BigQuery ML, the data scientists
can create various model types, including regression,
clustering, and XGBoost.
In this case, she used ARIMA, which
is built to forecast future time series-based values.
Here, she told the model what data
target to forecast and time series columns to use,
and provided a hint regarding US-based holidays.
Finally, she passed in the query of click streams
that looks back two years but prunes out the last 30 days.
This model trains on two years of data,
and then saves into the data set for evaluation and serving.
She then used the ML Forecast option
to forecast the next 30 days in the time series,
at a 90% confidence interval.
To verify the model estimates, she selected the actuals
from the 30-day window.
From there, she could plot the historical plus forecast
plus actual and the lower and upper bound of the confidence
intervals.
This graph passes a quick eye test.
You can see seasonality changes over quarter,
and then surges during the weekends.
Once we get into the forecast, you
can see that the model is within three of the four week windows,
and barely misses week 1, even though it saw an upward trend.
With an end-to-end workflow in place,
the data scientists can go back into additional EDA
for outliers and errors, and then persist these results back
to the warehouse and/or share the model for serving.
With a top line model in place, New Kicks
can move on to additional models like per-store and per-product
based forecasting.
Problem solved.
In this demo, we showed you how New Kicks efficiently extended
a BigQuery data warehouse to create a demand forecasting
model by utilizing various transformation patterns, flex
slots, federated queries, GIS, and machine learning functions.
Check out these links to learn more, and get started
with building your cloud native data warehouse on Google Cloud.
[MUSIC PLAYING]