Why and How to Migrate to Google BigQuery
5372
Of your peers have already read this article.
6:30 Minutes
The most insightful time you'll spend today!
Over the past few decades, organizations have mastered the science of data warehousing. They have increasingly applied descriptive analytics to large quantities of stored data, gaining insight into their core business operations. Conventional Business Intelligence (BI), which focuses on querying, reporting, and Online Analytical Processing, might have been a differentiating factor in the past, either making or breaking a company, but it’s no longer sufficient.
Today, not only do organizations need to understand past events using descriptive analytics, they need predictive analytics, which often uses machine learning (ML) to extract data patterns and make probabilistic claims about the future. The ultimate goal is to develop prescriptive analytics that combine lessons from the past with predictions about the future to automatically guide real-time actions.
Traditional data warehouse practices capture raw data from various sources, which are often Online Transactional Processing (OLTP) systems. Then, a subset of data is extracted in batches, transformed based on a defined schema, and loaded into the data warehouse. Because traditional data warehouses capture a subset of data in batches and store data based on rigid schemas, they are unsuitable for handling real-time analysis or responding to spontaneous queries. Google designed BigQuery in part in response to these inherent limitations.
Innovative ideas are often slowed by the size and complexity of the IT organization that implements and maintains these traditional data warehouses. It can take years and substantial investment to build a scalable, highly available, and secure data warehouse architecture. BigQuery offers sophisticated software as a service (SaaS) technology that can be used for serverless data warehouse operations. This lets you focus on advancing your core business while delegating infrastructure maintenance and platform development to Google Cloud.
BigQuery offers access to structured data storage, processing, and analytics that’s scalable, flexible, and cost effective. These characteristics are essential when your data volumes are growing exponentially—to make storage and processing resources available as needed, as well as to get value from that data. Furthermore, for organizations that are just starting with big data analytics and machine learning, and that want to avoid the potential complexities of on-premises big data systems, BigQuery offers a pay-as-you-go way to experiment with managed services.
With BigQuery, you can find answers to previously intractable problems, apply machine learning to discover emerging data patterns, and test new hypotheses. As a result, you have timely insight into how your business is performing, which enables you to modify processes for better results. In addition, the end user’s experience is often enriched with relevant insights gleaned from big data analysis, as we explain later in this series.
The migration framework
Undertaking a migration can be a complex and lengthy endeavor. Therefore, we recommend adhering to a framework to organize and structure the migration work in phases:
- Prepare and discover: Prepare for your migration with workload and use case discovery.
- Assess and plan: Assess and prioritize use cases, define measures of success, and plan your migration.
- Execute: Iterate the following steps for each use case:
- Migrate (offload): Migrate only your data, schema, and downstream business applications.
- Migrate (full): Alternatively, migrate the use case fully end-to-end. The same as Migrate (offload), with the addition of the upstream data pipelines.
- Verify and validate: Test and validate the migration to assess return on investment.
The following diagram illustrates the recommended framework and shows how the different phases are connected:
For a deeper understanding, read Migrating data warehouses to BigQuery: Introduction and overview
5365
Of your peers have already downloaded this article
8:15 Minutes
The most insightful time you'll spend today!
To design and implement a successful machine learning (ML) project, you often need to collaborate with multiple teams, including those in business, sales, research, and engineering. Understanding the essentials of gathering and preparing your data is crucial to align teams, and getting a project off the ground.
Building an ML model that achieves your business’ goal, requires the kind of data that a model can learn from. If the goal is to predict a target output based on some input, you’ll need some data consisting of past input-output examples. For example, to detect (that is, to predict) a fraudulent credit card transaction (the target of the prediction), each example must contain information on a past credit card transaction and whether that transaction was fraudulent or not.
So what are the best practices in preparing and curating data for a machine learning initiative? The answer to this question lies within the guide. You’ll learn:
- To evaluate whether your data is suitable for ML
- What types of data you need–and which ones to eliminate
- How to prevent data leakage
- Tricks to speed up an ML project such as Google’s human labeling service, and Cloud Dataprep
- The importance of the granularity of data
5669
Of your peers have already watched this video.
56:20 Minutes
The most insightful time you'll spend today!
How Real Companies Are Innovating with AI Today—and the Benefits They’re Seeing
What do you get when you mix Target, women’s swim wear, and AI? “Joy!” says Mike McNamara, CIO and CDO, Target.
McNamara is just one of the many stories of real businesses conquering old challenges, and new disruptive industry challenges, with artificial intelligence.
McNamara, Nick Rockwell, CTO, The New York Times, Larry Colagiovanni, VP New Product Development, eBay, and Dirk John, CIO, LATAM Airlines, demonstrate how AI solutions allow businesses to find opportunity in chaos, innovation under tough conditions, and revenue in the most unlikely of places.
AI at Target
McNamara shows how AI is being applied to solve some of the most basic, yet critical, challenges that retailers like Target–and any business with large inventories—face. (start at 4:04)
AI at The New York Times
Nick Rockwell, CTO, The New York Times, shares how AI and big data tools are being used to leverage old assets and drive business ideas from them. (start at 12:18)
AI at eBay
Larry Colagiovanni, VP New Product Development, eBay, talks about how AI is helping eBay drive conversational commerce, help buyers sift through 1.1 billion items, and personalize the shopping experience.(start at 28:07)
AI at LATAM Airlines
Dirk John, CIO, LATAM Airlines, discusses how LATAM Airlines adopted advanced analytics tools in just a few weeks to accelerate the company’s understanding of their customers’ needs.(start at 35:58)
11228
Of your peers have already watched this video.
1:30 Minutes
The most insightful time you'll spend today!
Indian Retailer Figures Optimizes Hyperlocal Delivery to Increase Customer Experience
Anyone who follows the Indian e-commerce scene knows that one of the largest challenges these companies face is hyperlocal delivery.
That was a problem facing Wellness Forever, a retail chain of pharmacies with 150-plus stores across India.
“Exactly a year ago, we started our journey of hyperlocal deliveries. This optimization was a big time challenge for us to understand how to optimize this,” Palani Subbiah, CTO, Wellness Forever.
The problem in front of Wellness Forever was to identify which customer could can be sold from which store, so that a delivery could be made within 90 minutes.
“We handle a large amount of customer data and we wanted to use insights to help and improve the customer satisfaction index,” says Subbiah.
To do that Wellness Forever leveraged Google Big Query to run massive amount of data to come up with the operational insights. They also used Firebase and Google Maps.
“By 2021, we are going to have about 450 stores. Those stores are going to be not only a physical store, which is a digital store.
Blue Apron: Offering a better recipe for modern analytics
1648
Of your peers have already read this article.
5:30 Minutes
The most insightful time you'll spend today!
When Blue Apron had issues running its data warehouse on another cloud provider, it built an analytics platform using Looker and Google BigQuery to enable faster business decisions about food inventory.
Blue Apron’s mission is to make incredible home cooking accessible to everyone. Launched in 2012, Blue Apron is reimagining the way that food is produced, distributed, and consumed.
Google Cloud Results
- Enables near real-time business decisions to better manage food inventory and delivery by reducing query times exponentially
- Helps improve customer service and optimize business processes with faster insights
- Reduces costs while reclaiming up to a week per month of engineering
The popularity of meal kit delivery services has surged in recent years as consumer attitudes toward home cooking and grocery shopping have shifted. As a pioneer in the category, Blue Apron helps its customers create incredible home cooking experiences by sending culinary-driven recipes with high-quality ingredients and step-by-step instructions straight to customers’ doors. Blue Apron also offers a monthly wine subscription service and a la carte culinary tools and products through its marketplace.
If that sounds simple, it isn’t. Ingredients for the meal kits must be sourced at the right time, quality, and price. Orders must be packed efficiently and in exactly the right proportions. Most importantly, meal kits must be delivered to the customer fresh and on time.
To meet these criteria and make data meaningful and intuitive to its managers, one of the tools Blue Apron relies on is Looker, an analytics platform that lets business users explore data and ask sophisticated questions using familiar terms.
A Google Cloud partner and winner of the 2016 Google Cloud Global Partner Award for Solution Innovation, Looker integrates its solution with Google Cloud Platform to help customers modernize their analytics.
Blue Apron previously used Looker with a single database instance hosted on another cloud provider. As data volumes grew and queries became more complex, it became difficult to scale. Blue Apron’s only options were choosing ever-larger server classes and increasing storage throughput by purchasing a higher number of provisioned IOPS. To improve speed, scalability, and cost efficiency, Blue Apron moved its data warehouse to Google BigQuery.
“The combination of Looker and Google BigQuery is powerful, allowing us to get data-hungry analysts essential information much faster,” says Sam Chase, Tech Lead, Data Operations at Blue Apron. “Because we choose to pay by the query, it’s also flexible and cost effective—plus storage is cheap, so we can just put data in and query what we need.”
The analytics platform of the future
When you’re making business decisions about a customer’s dinner, speed matters. Looker takes full advantage of the power of Google BigQuery, making it easy to build a data exploration platform. Blue Apron’s applications publish event data to Kafka—approximately 140 million events per day—and data is then streamed into Google BigQuery, which performs lightning-fast queries on both streamed and static data. Now, business users and analytics teams can make decisions based on near real-time information in Looker, instead of waiting until the next business day for results.
“After we moved to Google BigQuery, query time was reduced exponentially. It’s an astonishing difference, allowing us to run 300 queries per day,” says Sam.
Previously, Blue Apron spent up to a week out of every month optimizing its data warehouse to attempt to improve query performance. With Google BigQuery, all maintenance is handled by Google, reclaiming 25% of up to two engineers’ time. Even when multiple people are using Looker concurrently, query performance never degrades and storage never runs out.
“Because Google BigQuery is architected as a giant, shared cluster, growth is smooth,” says Lloyd Tabb, Founder and CTO of Looker. “Like a race car going from 0 to 120 mph, there are no shift points, just smooth acceleration. To us, it looks like the future.”
An empowering, integrated toolset
Looker takes advantage of aggressive caching and support for date-based table partitioning in Google BigQuery to increase performance, simplify the load process, and improve data manageability. By partitioning data by time, Blue Apron can also take advantage of better long-term storage pricing without sacrificing query performance. When using Google BigQuery with Looker, analysts can easily see how much data is going to be scanned before each query is run.
Blue Apron is also using Looker for Google BigQuery Data Transfer Service to provide actionable analytics for all of the company’s Google marketing data from Google AdWords and DoubleClick by Google in one place to understand campaign performance across channels, saving its data operations team months of work. Using Looker Blocks, marketers can quickly make sense of the data with reports and dashboards, and set alerts when campaign performance hits certain thresholds.
Looker Blocks for Google AdWords and DoubleClick by Google provide all the analysis you’d get straight from the Google console, plus additional value-add analysis that’s impossible to replicate without SQL. Complex metrics such as ROI on ad spend, flexible multi-touch attribution, and predictive lifetime value empower marketers with a better understanding of their customers and where to spend their next dollar.
In addition to these turnkey dashboards and pieces of analysis, marketers can customize views to meet their unique needs and workflows. These capabilities help the Blue Apron marketing team make decisions regarding the allocation of spend to maximize customer acquisition and retention.
“Everyone at Blue Apron is excited about using Google BigQuery with Looker,” says Sam. “Business users and marketers are more empowered to look for answers, instead of waiting for analytics teams. Because users know they can get results rapidly, our business processes are evolving and improving.”
For data cleansing and transformation, Blue Apron uses Google Cloud Dataproc to run fully managed Apache Spark clusters on Google Cloud Platform. It’s also leveraging Google BigQuery integration with Google Workspace to bring data into Google Sheets for further distribution and analysis.
“Transferring data between Google tools is fast because it all happens on the Google network,” says Sam. “We can pull data from Google BigQuery, run transformations with Spark, and then write it back to Google BigQuery. That’s very helpful in providing our business users and data analysts with the richest, most current data.”
A perfect match for better insights
As Blue Apron seeks to expand its reach and deepen its engagement with customers, it is making Google BigQuery and Looker available to more users, providing a high-quality interactive analytics experience. “Our ability to pull a lot of data in and compute fast results affects everyone in our company,” says Sam. “Using Google BigQuery and Looker to iterate quickly and build new models to make our operations more efficient will directly impact our customers.”
For Looker, Google BigQuery represents the next step in data warehouse evolution. “Google BigQuery is a perfect match for Looker, combining easy setup with near infinite scale-out and elasticity,” says Lloyd. “People can make smarter decisions faster that directly benefit their business and customers.”
4328
Of your peers have already watched this video.
20:00 Minutes
The most insightful time you'll spend today!
The New and Upcoming Infrastructure for Google Cloud’s AI and ML Solutions
How does Google manage to provide its customers a differentiated compute platform experience and define ways to fully leverage its infrastructure supporting its cutting-edge AI and ML offerings? Easy-to-use, scalable and ability to create innovative products and services to end-users at low cost of ownership is the narrative behind Google Cloud’s AI and ML solutions. Explore Google Cloud’s ML infrastructure and accelerator innovation for 2021.
Watch the video to find out how Google Cloud’s leadership in AI through Google research, Deep Mind and also practical application of AI within Google Products drive innovative platforms and offerings that cater to customers’ AI and ML use cases!
More Relevant Stories for Your Company
Sainsbury’s Uses AI to Figure Out How the World Eats
Retail will forever be an industry that must constantly reinvent itself in response to, and anticipation of, ever-changing consumer demands. Digital transformation is fueling these changes and we've previously spoken about how businesses including Ulta Beauty and Kohl’s are taking advantage of Google Cloud to put data at the center of what they do
Google Cloud Tools Help U.S. Forest Department Generate Years of Insights into Earth’s Natural Resources
For 117 years, the U.S. Department of Agriculture’s Forest Service has been a steward of America's forests, grasslands, and waterways. It directly manages 193 million acres and supports sustainable management on a total of 500 million acres of private, state, and tribal lands. Its impact reaches far beyond even that,
How TeamSnap Improved Return on Ad Spend Significantly
Anyone who has ever coached or played on a sports team, or had a child involved in sports, knows how difficult scheduling and logistics can be. From game and practice schedules, to uniforms and who’s bringing the snacks, it can be a lot for coaches, administrators, parents, and players to
