As a leading French agricultural cooperative, Groupe Dauphinoise places collaboration at the heart of its philosophy. Working with farmers in the Rhone-Alpes region, Groupe Dauphinoise takes on a diverse range of activities from agricultural production to research and development to running retail outlets.

As its operations expanded and strained its existing infrastructure, Groupe Dauphinoise saw the opportunity to upgrade its technology solutions and adopted G Suite, Chrome devices and ultimately Google Cloud Platform (GCP).

“We transitioned to G Suite to improve our staff’s collaboration. We quickly realized that as the company grew, we needed a new infrastructure. Based on our satisfaction with G Suite, we chose GCP. With Google, ‘any device, anytime, anywhere’ is not just a dream,” says Sylvain Claudel, Head of IT at Groupe Dauphinoise.

“We transitioned to G Suite to improve our staff’s collaboration. We quickly realized that as the company grew, we needed a new infrastructure. Based on our satisfaction with G Suite, we chose GCP. With Google, ‘any device, anytime, anywhere’ is not just a dream.”

—Sylvain Claudel, Head of IT, Groupe Dauphinoise

Flexible workforce, stable infrastructure

Many of Groupe Dauphinoise’s 1,500 employees spend their days out of the office. Five years ago, with the help of Google partner GoWizYou, the company transitioned to G Suite to improve the flexibility and collaboration of its staff.

As Groupe Dauphinoise began to expand and collect more data, the company reached the limits of its on-premise infrastructure. Adding new storage was not a simple matter. Acquiring, configuring and synchronising new servers costs Groupe Dauphinoise time as well as money. In addition, with all its servers stored in a single room, security was a concern. Groupe Dauphinoise needed a new infrastructure.

Google Cloud Platform was the only solution in mind after Groupe Dauphinoise’s experience with G Suite and Chromebooks. Disruption was kept to a minimum thanks to Google’s licensing agreements with Microsoft products, allowing the company to migrate without affecting its operations.

After migrating its infrastructure to Compute Engine, Groupe Dauphinoise can let Google look after the security and maintenance. Cloud IAM makes it easy for Groupe Dauphinoise to hand out permissions to sensitive resources across a number of sites with maximum security and minimum fuss. The company placed its archives in Cloud Storage, while Cloud SQL allows it to continue to make use of its MySQL databases without disrupting the day to day business. Meanwhile, BigQuery provides Groupe Dauphinoise with the raw power to analyse large datasets quickly.

“Our company is growing and we have more and more data to collect and analyze like sales data, weather patterns or production numbers. With GCP, we have more than a single on-premise data store, so our disaster recovery plan is much more flexible. Compute Engine allows us to add more storage quickly and easily, without having to spend days synchronizing data and installing new servers. We have improved security and maintained the stability of our infrastructure while keeping costs down,” says Sylvain.

Safeguarding the present, looking to the future

With Google Cloud Platform, Groupe Dauphinoise has expanded and secured its infrastructure without sinking costs into on-premise servers or DevOps staff. Its investment in Chrome devices and adoption of G Suite mean that its mobile workforce can fully reap the benefits of a cloud-based infrastructure while dramatically cutting the cost of hardware. Meanwhile, working with a 200 million line table of sales data in Google BigQuery, the cooperative found that queries ran ten times faster than with its previous database provider. Groupe Dauphinoise is experimenting with Google BigQuery to expand its BI capabilities. Products like Google BigQuery help Groupe Dauphinoise grow its business, safe in the knowledge that its infrastructure is stable and secure.

“The amount of data we collect is growing very quickly. We need to break our rules and evolve from the mindset that we had with on-premise infrastructure and our old databases. We can now look at collecting more customer fidelity data, or big data for our farmers. With our data and infrastructure in Google’s care, we can concentrate on growing our customer base instead of our IT department!” says Sylvain.

The Home Depot, Inc., is the world’s largest home improvement retailer with annual revenue of over $151B. Delighting our customers—whether do-it-yourselfers or professionals—by providing the home improvement products, services, and equipment rentals they need, when they need them, is key to our success.

We operate more than 2,300 stores throughout the United States, Canada, and Mexico. We also have a substantial online presence through HomeDepot.com, which is one of the largest e-commerce platforms in the world in terms of revenue. The site has experienced significant growth both in traffic and revenue since the onset of Covid-19.

Because many of our customers shop at both our brick-and-mortar stores and online, we’ve embarked on a multi-year strategy to offer a shopping experience that seamlessly bridges the physical and digital worlds. To maximize value for the increasing number of online shoppers, we’ve shifted our focus from event marketing to personalized marketing, as we found it to be far more effective in improving the customer experience throughout the sales journey. This led to changing our approach to marketing content, email communications, product recommendations, and the overall website experience.

Challenge: launching a modern marketing strategy using legacy IT

For personalized marketing to be successful, we had to improve our ability to recognize a customer at the point of transaction so we could—among other things—suspend irrelevant and unnecessary advertising. Most of us have experienced the annoyance of receiving ads for something we’ve already purchased, which can degrade our perception of the brand itself. While many online retailers can identify 100% of their customer transactions due to the rich information captured during checkout, most of our transactions flow through physical stores, making this a more difficult problem to solve.

Our old legacy IT system, which ran in an on-premises data center and leveraged Hadoop, also challenged us since maintaining both the hardware and software stack required significant resources. When that system was built, personalized marketing was not a priority, so it took several days to process customer transaction data and several weeks to roll out any system changes. Further, managing and maintaining the large Hadoop cluster base presented its own set of issues in terms of quality control and reliability, as did keeping up with open-source community updates for each data processing layer.

Adopting a hybrid approach

As we worked through the challenges of our legacy system, we started thinking about what we wanted our future system to look like. Like many companies, we began with a “build vs. buy” analysis. We looked at several products on the market and determined that while each of them had their strengths, none was able to offer the complete set of features we needed.

Our project team didn’t think it made sense to build a solution from scratch, nor did we have access to the third-party data we needed. After much consideration, we decided to adopt a solution that combined a complete rewrite of the legacy system with the support of a partner to help with the customer transaction matching process.

Building the foundation on Google Cloud

We chose Google Cloud’s data platform, specifically BigQuery, Dataflow, DataProc, Cloud Storage, and Cloud Composer. Google Cloud platform empowered us to break down data silos and unify each stage of the data lifecycle from ingestion, storage, and processing to analysis and insights. Google Cloud offered best-in-class integration with open-source standards and provided the portability and extensibility we needed to make our hybrid solution work well. The open standards of BigQuery’s BQ Storage API allowed us to leverage fast BQ storage layers to be utilized with other compute platforms, e.g., DataProc.

We used BigQuery combined with Dataflow to integrate our first- and third-party data into an enterprise data and analytics data lake architecture. The system then combined previously siloed data and used BigQuery ML to create complete customer profiles spanning the entire shopping experience, both in-store and online.

Understanding the customer journey with the help of Dataflow and BigQuery

The process of developing customer profiles involves aggregating a number of first- and third-party data sources to create a 360-degree view of the customer based on both their history and intent. It starts with creating a single historical customer profile through data aggregation, deduplication, and enrichment. We used several vendors to help with customer resolution and NCOA (Change of Address) updates, which allows the profile to be house-holded and transactions to be properly reconciled to both the individual and the household. This output is then matched to different customer signals to help create an understanding of where the customer is in their journey—and how we can help.

The initial implementation used Google Dataflow, Google’s streaming analytics solution, to load data from Google Cloud Storage into BigQuery and perform all necessary transformations. The Dataflow process was converted into BQML (BigQuery Machine Learning) since this significantly reduced costs and increased visibility into data jobs. We used Google Cloud Composer, a fully managed workflow orchestration service, to help orchestrate all data operations and DataProc and Google Kubernetes Engine to enable special case data integration so we could quickly pivot and test new campaigns. The architecture diagram below shows the overall structure of our solution.

Taking full advantage of cloud-native technology

In our initial migration to Google Cloud, we moved most of our legacy processes in their original form. However, we quickly learned that this approach didn’t take full advantage of the cloud-native and more improved features Google Cloud offered such as auto scaling of resources, flexibility to decouple storage from the compute layer, and a wide variety of options to choose the best tool for the job. We refactored our Hadoop-based data pipelines written in Java-based Map Reduce and our Pig Latin jobs to Dataflow and BigQuery jobs. This dramatically reduced processing time and made our data pipeline code concise and efficient.

Previously, our legacy system processes ran longer than intended, and data was not used efficiently. Optimizing our code to be cloud-native and leveraging all the capabilities of Google Cloud services resulted in reduced run times. We decreased our data processing window from 3 days to 24 hours, improved resource usage by dramatically reducing the amount of compute we used to possess this data, and built a more streamlined system. This in turn reduced cloud costs and provided better insight. For example, DataFlow offers powerful native features to monitor data pipelines, enabling us to be more agile.

Leveraging the flexibility and speed of the cloud to improve outcomes

Today, using a continuous integration/continuous delivery (CI/CD) approach, we can deploy multiple system changes each week to further improve our ability to recognize in-store transactions. Leveraging the combined capabilities of various Google Cloud systems—BigQuery, DataFlow, Cloud Composer, Dataproc, and Cloud Storage–we drastically increased our ability to recognize transactions and can now connect over 75% of all transactions to an existing household. Further, the flexible Google Cloud environment coupled with our cloud-native application makes our team more nimble and better able to respond to emerging problems or new opportunities.

Increased speed has led to better outcomes in our ability to match transactions across all sales channels to a customer and thereby improve their experience. Before moving to Google Cloud, it took 48 to 72 hours to match customers to their transactions, but now we can do it in less than 24 hours.

Making marketing more personal—and more efficient

The ability to quickly match customers to transactions has huge implications for our downstream marketing efforts in terms of both cost and effectiveness. By knowing what a customer has purchased, we can turn off ads for products they’ve already bought or offer ads for things that support what they’ve bought recently. This helps us use our marketing dollars much more efficiently and offer an improved customer experience.

Additionally, we can now apply the analytical models developed using BQML and Vertex AI to sort customers into audiences. This allows us to more quickly identify a customer’s current project, such as remodeling a kitchen or finishing a basement, and then personalize their journey by offering them information on products and services that matter most at a given point through our various marketing channels. This provides customers with a more relevant and customized shopping journey that mirrors their individual needs.

Protecting a customer’s privacy

With this ability to better understand our customers, we also have the responsibility to ensure we have good oversight and maintain their data privacy. Google’s cloud solutions provide us the security needed to help protect our customers’ data, while also being flexible enough to allow us to support state and federal regulations, like the California Customer Privacy Act. This way we can provide a customer the personalized experience they desire without having to fear how their data is being used.

With flexible Google Cloud technology in place, The Home Depot is well positioned to compete in an industry where customers have many choices. By putting our customers’ needs first, we can stay top of mind whenever the next project comes up.

Database migration to a new database platform or technology can be daunting for various reasons. One of the common concerns is database performance. It is hard to evaluate database performance early in the evaluation cycle without performing actual data migration and application changes. This becomes even more important in the case of Cloud Spanner where modernization is required at both the database and application layers. 

The traditional approach to evaluate database performance is to deploy the application and database together, migrate historical data to the new database and then simulate load tests by running an application on the new database. Doing all this for an early performance evaluation of Cloud Spanner may feel like a lot of effort.

In this post, we will explore a middle ground to performance testing using JMeter. Performance test Cloud Spanner for a custom workload before making application code changes and executing data migration. More detailed step-by-step guide is published here.

Goals

• Estimate the number of Cloud Spanner nodes needed (and get a cost estimate).
• Performance test the most frequently used set of queries and transactions.
• Demonstrate the ability to scale horizontally.
• Better understand the optimizations needed for schema and sql queries.
• Determine latency of DML operations.

Limitations

• You cannot test using non-java client libraries.
• It is more complex to test non-jdbc compliant features like mutations, parallel reads etc.

Preparing for performance tests

1. Identify top SQL queries, latency, frequency / hour and avg number of rows returned or updated for each. This information will also serve as a baseline for the current system.
2. Determine Cloud Spanner region / multi-region deployment. Ideally, load should be generated from the Cloud Spanner instance’s leader region for minimum latency and best performance. Read Demystifying Cloud Spanner multi-region configurations for more details on various configurations of Cloud Spanner.
3. Estimate the number of Cloud Spanner nodes required for a given workload based on (step 1). It is recommended to have a minimum of 2 nodes for linear scaling.
   Note: Peak performance numbers of regional performance and multi regional performance are published. It is based on a 1KB single row transaction with no secondary indexes.
4. Request quota for Cloud Spanner nodes on a given region / multi-region. It can take up to 1 business day. 

Setting up Cloud Spanner

Creating the schema for Cloud Spanner

You can use the following tools to generate a schema for Cloud Spanner if you are migrating from the following source databases. Alternatively, you can model the schema manually. Schema design has a huge performance impact, hence it is recommended to review the schema very carefully.

Note: Manual review and tuning of schema will be needed to optimize and mitigate potential hotspots. You will need to keep in mind schema design best practices when modeling your schema.

Populating seed data into Cloud Spanner

Performance of a database depends on the amount of data present. Existing data (and indexes) determines how much data is scanned on select queries and therefore performance. Hence, it is important to seed data into Cloud Spanner before performance testing. 

For most realistic results, data should be migrated from an existing production source. Sometimes you cannot do that due to schema changes. One way is to utilize a custom ETL job to export and transform data and then import into Cloud Spanner. Another alternative could be to mock seed data using JMeter(More details in later sections).

JMeter performance tests

Writing Tests

JMeter will interact with Cloud Spanner just like your application. It will perform DML operations the same way as your application does. Hence, tests need to simulate production like transactions. For example if a transaction is made up of insert and/or update statements in several tables within transaction boundaries then JMeter should mimic the same.

JMeter has the following hierarchy:

Test Plan > Thread Group(s) > Sampler(s) (aka test)

Test plan is a top level component. It can contain global properties and libraries (like jdbc driver etc).

Thread Group(s) are representative of a database transaction. It contains one or more sampler(s). All the samplers within a thread group execute serially.

Sampler(s) should represent a single DML call. If your transaction needs to have multiple dml calls, you should create multiple samplers. Typically you will use JDBC Sampler for database calls. You can also use JSR 223 Sampler as described here, in case you need to use mutations or parallel reads etc. Results from one sampler can be passed to the next, as in real world application.

Executing tests and collecting results

JMeter tests should be executed as physically close to Cloud Spanner instances as possible to minimize network latency. Therefore it is best to execute them from Compute Engine (via private service connect) from the same region as the Cloud Spanner instance. In-case of multi region Cloud Spanner, GCE instances should be created in the Leader Region of the Cloud Spanner instance for lowest network latency.

JMeter should be executed via command line, it is a CPU intensive application so make sure you have allocated enough resources on the VM.

Refer to the Cloud Spanner monitoring dashboard to ensure that your Cloud Spanner instance’s CPU utilization is at or below the recommended values. In addition, use introspection tools to investigate performance issues with your database. 

You might need to optimize your queries (add indexes) and re-execute tests in multiple waves to tune the performance as needed.

Try it yourself

Follow a detailed step by step tutorial on Measure Cloud Spanner performance test using JMeter to test Cloud Spanner performance yourself.

Founded in 2006, PaGaLGuY started as a forum that enabled students, typically aged between 20 and 30 to discuss and seek advice on academic issues. By 2011, PaGaLGuY had increased its traffic to about 250,000 page views per month. The business is now one of India’s largest education networks and provides an app that users can download to their Android and iOS devices.

Over the past six years, PaGaLGuY has extended its service to include video advice from experts on education topics and grown the number of views of its pages to 1.5 million per day. As Head of Technology for the business, Sandeep Kalidindi has played a key role in ensuring PaGaLGuY is as engaging as possible to users. “Because the product is advertising based, the greater the user engagement, the greater the advertising revenue,” Kalidindi explains.

Google Cloud Platform Results

• Supported growth to 1.5 million page views per day and demand spikes that see requests increase from about 90 per second to about 1,200 per second
• Reduced API latency from about 1 second to about 40 milliseconds
• Reduced system administration time from three to four days per week to 30 minutes every two weeks

In 2015, PaGaLGuY’s senior management team decided to deliver an even more relevant experience for users of the education network. “The core thing we had to do was personalise the experience for each and every student that visited PaGaLGuY,” Kalidindi says. “So we had to capture each student’s data to customise what they see when they open the site.”

The business also found traffic to the network was straining its infrastructure. During demand peaks, created by exams involving as many as 5 million students, PaGaLGuY would be inaccessible for periods of 30 minutes to one hour. Furthermore, average API latency had climbed to an unacceptable 1 second, compromising performance.

PaGaLGuY needed to access extensive compute resources to undertake its planned change. Had the business relied on a physical technology architecture to undertake the transformation, it would have had to purchase capacity equivalent to 16 new servers. “There was no way with a small team we could grow to that extent in a short time,” Kalidindi says. “This was the right moment for us to explore cloud services.”

The business established two primary requirements the selected cloud service needed to meet. First, PaGaLGuY had to be able to scale the platform with costs rising only in proportion to the increase in resources consumed. Accordingly, the business would have to minimise the number of employees required to manage the cloud environment. Second, the platform had to give PaGaLGuY easy access to student data and the ability to undertake prompt, granular analysis.

PaGaLGuY reviewed available public cloud services and determined that Google Cloud Platform (GCP) was the best fit for its business. “Google Cloud Platform was considerably more mature than the alternatives, with a high degree of automation and a suite of managed services,” Kalidindi says. PaGaLGuY management then discussed with Google how to optimise cost, performance and availability of its personalised education network on GCP.

With assistance from Google and business transformation specialists Searce, PaGaLGuY was able to deliver the platform into production on GCP in 10 months. “Searce was very proactive in ensuring the environment met our needs and allowing us to gain priority access to Google services in development,” Kalidindi says. “Their team was integral to the success of the migration.”

PaGaLGuY has been running in production in GCP for two years. The education network’s GCP architecture comprises a scalable back-end built on Google App Engine; a managed environment for its containerised applications in Google Kubernetes Engine; messaging-oriented middleware through Google Cloud Pub/Sub; a relational database in Google Cloud SQL; a managed data analytics warehouse running in Google BigQuery; stream and batch data processing through Google Cloud Dataflow; and object storage in Google Cloud Storage.

PaGaLGuY has leveraged GCP services to break down its platform application from a monolithic build to a series of microservices running in Google App Engine that enable independent deployment cycles, minimise test and quality assurance overheads and provide clearer monitoring and logging.

Running on GCP has enabled PaGaLGuY to add new personalisation features and grow fourfold without having to add any new engineers or administrators to accommodate the increased traffic. The business has also used the platform to seamlessly collect and aggregate students’ data for analysis, reporting and delivering a more targeted user experience. Furthermore, PaGaLGuY has been able to provide its management team with direct access to Google BigQuery to scrutinise data rather than require them to wait at least a day to view reports created by the product or technology teams.

Support demand peaks of 1,200 requests per second

“Thanks to Google Cloud Platform, we can easily support demand peaks that see requests per second rise from an average 90 per second to about 1,200 per second for as long as 45 minutes,” Kalidindi says. Due to GCP’s scalability, PaGaLGuY can ensure its education network remains available and performance remains consistent during those periods.

Latency cut to 40 milliseconds

The business has also reduced average API latency from 1 second to about 40 milliseconds. Furthermore, using GCP has enabled PaGaLGuY to automate most of its processes and reduce system administration requirements from three to four days a week across its team members to about half an hour per week.

The performance of GCP has transformed PaGaLGuY’s culture and processes. “Once our team was exposed to Google Cloud Platform and understood the superiority of the platform, our mindset changed from ‘let us do everything on our own’ to ‘let us do what we do best’ and delegate the remainder,” Kalidindi says. The quality of the service provided by GCP means PaGaLGuY effectively considers the cloud provider as part of its team. “We are always eager to see what new services are being launched and are extremely excited about what Google Cloud Platform can provide as part of its roadmap.” he concludes.