Japan is home to millions of vending machines installed on streets and in buildings, sports stadiums and other facilities. Vending machine owners and operators, including beverage manufacturers, stock these machines with different product combinations depending on location and demand. For example, they primarily display coffee and energy drinks in machines placed in offices and sports drinks and mineral water in machines at sports facilities. The combinations also vary by season: for example, owners and operators may display cold beverages in summer and hot beverages in winter. 

Traditionally, vending machine operators have relied on the intuition and experience of sales managers to determine the optimum product mix for each vending machine. However, in recent years, manufacturers such as Coca-Cola Bottlers Japan (CCBJ) have turned to data to analyze and make strategic decisions about when and where to locate products in machines.

CCBJ is the number one Coca-Cola bottler in Asia and vending machines comprise the bulk of its business. The organization operates about 700,000 machines across Tokyo, Osaka, Kyoto, and 35 prefectures. Minori Matsuda, Google Developer Expert and also Data Science Manager at CCBJ, says “The billions of data records collected from 700,000 physical devices are a great asset and a treasure trove we can take advantage of.”

Minori points out that when considering the mix of products in vending machines in sporting facilities, the managers naturally assume sports drinks would generally sell well. However, analysis of purchase data – including hot drinks and hot drinks plus sports drinks – found many parents purchased sweet drinks such as milk tea when they attended games or sessions involving their children.  “Analyzing data gives us new discoveries and, by using catchy storytelling techniques from exploratory data analysis, we are instilling a data culture within our company,” he says. “It’s worth creating by looking at facts rather than making assumptions!”

Minori believes that to analyze the vast amount of data collected from more than 700,000 vending machines, the business needs a powerful analytical platform. However, until recently, CCBJ had to extract data for analysis from its core systems, load this data into a warehouse it created and perform the required analyses.  The billions of records of data generated across the fleet – including transaction data – exposed some challenges for traditional analysis platforms. They could not efficiently process data at a considerable scale: it could take a day to return results and required extensive maintenance due to the size.

CCBJ considered building a machine learning (ML) platform as a layer on top of existing systems in August 2020 and opted for Google Cloud the following month.  “I feel that Google Cloud has an edge in all products and is very well thought out,“ says Minori, noting the scalability and cost of the platform allow the business to take a ‘trial and error’ approach to achieve the best outcomes from ML. Google Cloud also delivered the required visibility and flexibility to help the business deliver change every day against key performance indicators. 

MLOps platform streamlines ML pipeline development

CCBJ built its analysis platform using Vertex AI (formerly AI Platform) centered on a BigQuery analytics data warehouse, and partly using AutoML for tabular data. “We have created a prediction model of where to place vending machines, what products are lined up in the machines and at what price, how much they will sell, and implemented a mechanism that can be analyzed on a map,” says Minori, adding that building the platform with Google Cloud was not difficult. “We were able to realize it in a short period of time with a sense of speed, from platform examination to introduction, prediction model training, on-site proof of concept to rollout.”

The new data analytics platform of CCBJ consists of the following parts:

Data Sources

• The data collected from the vending machines are all stored on BigQuery.

Data Discovery and Feature Engineering

• Minori and other data scientists at CCBJ are using Vertex Notebooks, where they access the data on BigQuery by executing SQL queries directly from the Notebooks. This environment is used for the data discovery process and feature engineering. 

ML Training

• For ML training, CCBJ uses AutoML for Tabular data, Custom model training on Vertex AI, and BigQuery ML. AutoML gives model performance with AUC curves and also feature importance graphs.

ML Prediction and Serving

• For ML prediction, CCBJ uses Online Prediction for AutoML models and Online Prediction for custom models for real-time prediction when the salesperson finds the interesting point
• Batch Prediction is used for generating a large prediction map that covers the whole country
• The prediction results are distributed to sales managers’ tablets

CCBJ started constructing the platform in September 2020, and completed it within a month. The business has conducted proofs of concept at its base in Kyoto since February 2021, and since April, has rolled out the platform to sales managers in 35 prefectures in one metropolitan area. “Data analysis is built into the day-to-day routines of sales managers with 100% utilization,” says Minori. “They can utilize the prediction results on tablets that were able to achieve pretty high accuracy from the start.”

The hardest part was the education of sales managers in the field; having them understand the reasoning behind the ML prediction results for particular outcomes, so they could be convinced to make use of the results. “For example, regarding a new installation location predicted by the model, it seemed that there was no effective information for installation from the map information, but when I actually went there, there was a motorcycle shop and it was a place where young people who like motorcycles gathered,” says Minori. “Or there is a small meeting place where the elderly in the neighborhood are active. 

“In many cases, new discoveries that cannot be understood from map information alone can be derived from the data.”

Minori also points to a phenomenon whereby humans pursued and confirmed factors inferred by the model – meaning that once they experienced analysis and it worked effectively, they asked why the same type of analysis or prediction could not be undertaken next time. The resulting cycle of more inquiries generated, more information gathered and more data captured for analysis meant the accuracy of results was improved.

Minori describes Vertex AI as having a number of strengths in helping CCBJ build a ML data analysis platform. “One of the major merits of Vertex AI was that we were able to realize MLOps that streamlines the entire development life cycle from construction of the ML pipeline to its execution,” he says.

With near real-time data analysis through Google Cloud, CCBJ teams can spend time developing strategies rather than waiting for data requested from the IT systems department. Exploratory data analysis is also considerably easier as repeated trial and error has greatly improved the accuracy of analyses. Before we used Machine Learning, most machine placement processes were done by human senses, by looking at a map to find the suggestion points. By using Machine Learning to generate a massive number of placement point suggestions, the efficiency of routing of salespeople has been dramatically improved. 

In the future, CCBJ aims to automate the continuous training pipeline with Vertex AI. “CCBJ is a tech company that operates in the food industry,” says Minori. With the organization operating a vending machine network of 700,000 units, it would like to create new businesses based on utilization and analyzing data. Some of these businesses may be based on Sustainable Development Goals (SDGs) initiatives such as the utilization of recycled PET bottles, measures to prevent food loss and ways of using vending machines to contribute to local communities, which we have been working on for some time. It would be interesting if we could collaborate with Google Cloud on these in the future.”

When you build a data warehouse, the important question is how to ingest data from the source system to the data warehouse. If the table is small you can fully reload a table on a regular basis, however, if the table is large a common technique is to perform incremental table updates. This post demonstrates how you can enhance incremental pipeline performance when you ingest data into BigQuery.

Setting up a standard incremental data ingestion pipeline

We will use the below example to illustrate a common ingestion pipeline that incrementally updates a data warehouse table. Let’s say that you ingest data into BigQuery from a large and frequently updated table in the source system, and you have Staging and Reporting areas (datasets) in BigQuery.

The Reporting area in BigQuery stores the most recent, full data that has been ingested from the source system tables. Usually you create the base table as a full snapshot of the source system table. In our running example, we use BigQuery public data as the source system and create reporting.base_table as shown below. In our example each row is identified by a unique key which consists of two columns: block_hash and log_index.

  CREATE TABLE reporting.base_table  --156 GB processed
PARTITION BY TIMESTAMP_TRUNC(block_timestamp, DAY) AS
SELECT log_index, data, topics, block_timestamp, block_hash
FROM bigquery-public-data.crypto_ethereum.logs
WHERE block_timestamp BETWEEN TIMESTAMP '2020-01-01' AND TIMESTAMP '2020-11-30';

In data warehouses it is common to partition a large base table by a datetime column that has a business meaning. For example, it may be a transaction timestamp, or datetime when some business event happened, etc. The idea is that data analysts who use the data warehouse usually need to analyze only some range of dates and rarely need the full data. In our example, we partition the base table by block_timestamp which comes from the source system.

After ingesting the initial snapshot you need to capture changes that happen in the source system table and update the reporting base table accordingly. This is when the Staging area comes into the picture. The staging table will contain captured data changes that you will merge into the base table. Let’s say that in our source system on a regular basis we have a set of new rows and also some updated records. In our example we mock the staging data as follows: first, we create new data, than we mock the updated records:

  CREATE TABLE staging.load_delta AS --5 GB processed
SELECT log_index, data, topics, block_timestamp, block_hash
FROM bigquery-public-data.crypto_ethereum.logs
WHERE block_timestamp BETWEEN TIMESTAMP '2020-12-01' AND TIMESTAMP '2020-12-07';
 
INSERT INTO staging.load_delta --2 GB processed
SELECT log_index, CONCAT(data, RAND()), topics, block_timestamp, block_hash
FROM bigquery-public-data.crypto_ethereum.logs TABLESAMPLE SYSTEM (5 PERCENT)
WHERE block_timestamp BETWEEN TIMESTAMP '2020-10-01' AND TIMESTAMP '2020-11-30';

Next, the pipeline merges the staging data into the base table. It joins two tables by unique key and than updates the changed value or inserts a new row

  MERGE INTO reporting.base_table T --161 GB processed
USING staging.load_delta S
ON T.block_hash = S.block_hash
 AND T.log_index = S.log_index
WHEN MATCHED THEN UPDATE SET 
  T.data = S.data
WHEN NOT MATCHED THEN INSERT (log_index, data, topics, block_timestamp, block_hash)
VALUES (log_index, data, topics, block_timestamp, block_hash);

It is often the case that the staging table contains keys from various partitions but the number of those partitions are relatively small. It holds, for instance, because in the source system the recently added data may get changed due to some initial errors or ongoing processes but older records are rarely updated. However, when the above MERGE gets executed, BigQuery scans all partitions in the base table and processes 161 GB of data. You might add additional join condition on block_timestamp:

  MERGE INTO reporting.base_table T --161 GB processed
USING staging.load_delta S
ON T.block_hash = S.block_hash
 AND T.log_index = S.log_index
 AND T.block_timestamp = S.block_timestamp
WHEN MATCHED THEN UPDATE SET 
  T.data = S.data
WHEN NOT MATCHED THEN INSERT (log_index, data, topics, block_timestamp, block_hash)
VALUES (log_index, data, topics, block_timestamp, block_hash);

But BigQuery would still scan all partitions in the base table because condition T.block_timestamp = S.block_timestamp is a dynamic predicate and BigQuery doesn’t automatically push such predicates down from one table to another in MERGE.

Can you improve the MERGE efficiency by making it scan less data? The answer is Yes. 

As described in the MERGE documentation, pruning conditions may be located in a subquery filter, a merge_condition filter, or a search_condition filter. In this post we show how you can leverage the first two. The main idea is to turn a dynamic predicate into a static predicate.

Steps to enhance your ingestion pipeline

The initial step is to compute the range of partitions that will be updated during the MERGE and store it in a variable. As was mentioned above, in data ingestion pipelines, staging tables are usually small so the cost of the computation is relatively low.

  DECLARE src_range STRUCT<date_min TIMESTAMP, date_max TIMESTAMP> --115 MB processed
DEFAULT(SELECT STRUCT(
  MIN(block_timestamp) AS date_min,  
  MAX(block_timestamp) AS date_max) FROM staging.load_delta);

Based on your existing ETL/ELT pipeline, you can add the above code as-is to your pipeline or you can compute date_min, data_max as part of some already existing transformation step. Alternatively, date_min, data_max can be computed on the Source System side while capturing the next ingestion data batch.

After computing date_min, date_max you pass those values to the MERGE statement as static predicates. There are several ways to enhance the MERGE and prune partitions in the base table based on precomputed date_min, data_max. 

If your initial MERGE statement uses a subquery, you can incorporate a new filter into it:

  BEGIN 
DECLARE src_range STRUCT<date_min TIMESTAMP, date_max TIMESTAMP> --115 MB processed
DEFAULT(SELECT STRUCT(
  MIN(block_timestamp) AS date_min,  
  MAX(block_timestamp) AS date_max) FROM staging.load_delta);

MERGE INTO reporting.base_table T --41 GB processed
USING (
  SELECT *
  FROM staging.load_delta
  WHERE block_timestamp BETWEEN src_range.date_min AND src_range.date_max) S 
ON T.block_hash = S.block_hash
 AND T.log_index = S.log_index
 AND T.block_timestamp = S.block_timestamp
WHEN MATCHED THEN UPDATE SET 
  T.data = S.data
WHEN NOT MATCHED THEN INSERT (log_index, data, topics, block_timestamp, block_hash)
VALUES (log_index, data, topics, block_timestamp, block_hash);
END;

Note that you add the static filter to the staging table and keep T.block_timestamp = S.block_timestamp to convey to BigQuery that it can push that filter to the base table. This MERGE processes 41 GB of data in contrast to the initial 161 GB. You can see in the query plan that BigQuery pushes the partition filter from the staging table to the base table:

This type of optimization, when a pruning condition is pushed from a subquery to a large partitioned or clustered table, is not unique for MERGE. It also works for other types of queries. For instance:

  SELECT * -- 41 GB processed
FROM reporting.base_table T
INNER JOIN staging.load_delta S
ON T.block_hash = S.block_hash
 AND T.log_index = S.log_index
 AND T.block_timestamp = S.block_timestamp
WHERE S.block_timestamp BETWEEN TIMESTAMP '2020-10-05' AND TIMESTAMP '2020-12-07'

And you can check the query plan to verify that BigQuery pushed down the partition filter from one table to another.

Moreover, for SELECT statements, BigQuery can automatically infer a filter predicate on a join column and push it down from one table to another if your query meets the following criteria:

• The target table must be clustered or partitioned. 
• The result size of the other table, i.e. after applying all filters, must qualify for broadcast join. Namly, the result set must be relatively small, less than ~100MB.

In our running example, reporting.base_table is partitioned by block_timestamp. If you define a selective filter on staging.load_delta and join two tables, you can see an inferred filter on the join key pushed to the target table

  SELECT * 
FROM reporting.base_table T
INNER JOIN staging.load_delta S
ON T.block_timestamp = S.block_timestamp
WHERE S.block_hash = '0x0c1caa16b34d94843aabfebc0d5a961db358135988f7498a6fdc450ad55f0870'

There is no requirement to join tables by partitioning or clustering key to kick off this type of optimization. However, in this case the pruning effect on the target table would be less significant.

But let us get back to the pipeline optimizations. Another way to enhance MERGE is to modify the merge_condition filter by adding static predicate on the base table:

  BEGIN 
DECLARE src_range STRUCT<date_min TIMESTAMP, date_max TIMESTAMP> --115 MB processed
DEFAULT(SELECT STRUCT(
  MIN(block_timestamp) AS date_min,  
  MAX(block_timestamp) AS date_max) FROM staging.load_delta);

MERGE INTO reporting.base_table T --41 GB processed
USING staging.load_delta S
ON T.block_hash = S.block_hash
 AND T.log_index = S.log_index
 AND T.block_timestamp BETWEEN src_range.date_min AND src_range.date_max
WHEN MATCHED THEN UPDATE SET 
  T.data = S.data
WHEN NOT MATCHED THEN INSERT (log_index, data, topics, block_timestamp, block_hash)
VALUES (log_index, data, topics, block_timestamp, block_hash);
END;

To summarize, here are the steps that you can perform to enhance incremental ingestion pipelines in BigQuery. First you compute the range of updated partitions based on the small staging table. Next, you tweak the MERGE statement a bit to let BigQuery know to prune data in the base table.

All the enhanced MERGE statements scanned 41 GB of data, and setting up the src_range variable took 115 MB.  Compare it with the initial 161 GB scan. Moreover, given that computing src_range may be incorporated into some existing transformation in your ETL/ELT, it results in a good performance improvement which you can leverage in your pipelines. 

In this post we described how to enhance data ingestion pipelines by turning dynamic filter predicates into static predicates and letting BiQuery prune data for us. You can find more tips on BigQuery DML tuning here.

Special thanks to Daniel De Leo, who helped with examples and provided valuable feedback on this content.