Mercury, the Roman god of commerce, is often depicted carrying a purse, symbolic of business transactions, wearing winged sandals, illustrating his abilities to move at great speeds. Transactions power the world’s business systems today, ranging from millions of packages moving worldwide tracked in real time by logistics companies to global payments from personal loans to securities trading to intergovernmental transactions, keeping goods and services flowing worldwide. Today, we are very pleased to announce the public preview of multi-statement transactions in BigQuery.

BigQuery has long supported single-statement transactions through DML statements such as INSERT, UPDATE, DELETE, MERGE and TRUNCATE, applied to one table per transaction. Multi-statement transactions expand on this scope by supporting multiple SQL statements, including DML, spanning multiple tables in a single transaction. This means that the changes to data across multiple tables associated with all statements in a given transaction are committed atomically (all at once) if successful or all rolled back atomically in the event of a failure. These new multi-statement transaction capabilities now bridge the gap between online transaction processing (OLTP) systems and BigQuery through tighter and faster integration, just as Mercury’s speed enabled him to travel rapidly between the mortal and divine worlds.

Overview

A transaction is a set of tasks carried out against a database or a data warehouse representing a business unit of work. For example, an order expedite action may result in a series of changes to multiple tables, e.g., updating delivery dates on the order, adding new shipment lines on a shipment to create expedited delivery shipments and canceling (updating) prior normal delivery shipment lines. Correspondingly, when a batch job execute an ETL process in a data warehouse, it may perform data wrangling and data cleansing operations by transforming and saving the data in multiple tables. In both of these cases, if a failure occurs, then the entire transaction must be rolled back so that the system of record does not have partial data saved putting the dataset in an inconsistent state.

Transactions are known for their atomicity, consistency, isolation, and durability (ACID) properties. Atomicity indicates that all data changes (including metadata changes) are applied or reverted in a single operation, e.g. when a set of rows are added to a table with enrichment and the corresponding set of rows deleted, both operations happen together. Consistency refers to the consistency of data before and after the transaction has completed. When data is added to or updated in a table, the changes to data in tables are consistent with the constraints specified on the table or its columns, e.g. ensuring that the NOT NULL constraint is complied with when data gets added or modified in a table. Isolation indicates that multiple transactions can make changes to the table concurrently as long as the changes don’t conflict with each other, e.g. two transactions loading data into tables from different business units can proceed concurrently. Durability is the last and possibly the most important property of transactions which guarantees that in the event of a failure (after completing the transaction), all the changes committed by the transaction are saved for future transactions or queries.

Isolation level

All transactions in BigQuery including multi-statement transactions support snapshot isolation. At this isolation level, all statements in a transaction see a consistent snapshot of the database, as of the start of the transaction. BigQuery achieves this due to its multi-version property where it maintains the history of mutations to the database for a certain period. Transactions do not see any changes, committed or uncommitted, from other concurrent transactions while the transaction is in progress. BigQuery supports read-your-own-writes, where statements in a transaction can see all the changes applied by prior statements in the same transaction.

Example

Let’s run through an example to demonstrate how BigQuery supports the concept of multi-statement transactions with multiple sets of changes across multiple tables in one operation. In this example, we will set up this demo by creating 10 tables each with 100 partitions 

Language: SQL

  DECLARE num_tables DEFAULT 10;
DECLARE x INT64 DEFAULT 0;
# replace "demo" with your preferred dataset name
DECLARE ds STRING default "`00jathreya`";
DECLARE prefix STRING DEFAULT "mst_demo";
DECLARE table_name STRING;
DECLARE pre_table_name STRING;
/*
Sets up 10 tables, with schema (partition_id:integer,value:timestamp).
Each table has 100 partitions, with partition key 0, 10, 20, ..., 990.
*/
SET x = 0;
WHILE x < num_tables DO
 SET table_name = format("%s.%s_%d", ds, prefix, x); 
 EXECUTE IMMEDIATE format("""DROP TABLE IF EXISTS %s""", table_name); 
 EXECUTE IMMEDIATE format("""
 CREATE OR REPLACE TABLE %s (partition_id INT64, value TIMESTAMP)
 PARTITION BY RANGE_BUCKET(partition_id, GENERATE_ARRAY(0, 1000, 10))
 AS SELECT partition_id, CURRENT_TIMESTAMP() AS value FROM UNNEST(GENERATE_ARRAY(0, 990, 10)) AS partition_id
 """, table_name);
 SET x = x + 1;
END WHILE;
# Verify the partitioned tables created and the timestamp
execute immediate format("""select table_schema, table_name, count(partition_id), sum(total_rows), max(last_modified_time) from %s.INFORMATION_SCHEMA.PARTITIONS
where starts_with(table_name,'%s')
group by table_schema, table_name""", ds, prefix);
# Verify data in table 9 based on the timestamp
EXECUTE IMMEDIATE format("""
SELECT * FROM %s ORDER BY partition_id
""", table_name);

Next, we will show how the new COMMIT and ROLLBACK commands in BigQuery allow you to save or reverse the changes in a transaction spanning multiple tables.

Language: SQL

  DECLARE num_tables DEFAULT 10;
DECLARE x INT64 DEFAULT 0;
# Replace `mydataset` with your preferred dataset name
DECLARE ds STRING DEFAULT "`mydataset`";
DECLARE prefix STRING DEFAULT "mst_demo";
DECLARE table_name STRING;
DECLARE pre_table_name STRING;
/*
Steps of the transaction:
1. For table 0, updates the timestamp to CURRENT_TIMESTAMP.
2. FOR table 1 to 9, merge table x using data from table x-1. All MERGE should
see the updated data in table x-1, so as a result the change in table 0 should be
propagated all the way to table 9.
3. A total of 1000 partition changes on 10 tables.
*/
BEGIN TRANSACTION;
SET x = 0;
WHILE x < num_tables DO
 SET table_name = format("%s.%s_%d", ds, prefix, x);
 IF x = 0 THEN
   EXECUTE IMMEDIATE format("""
   UPDATE %s SET value = CURRENT_TIMESTAMP WHERE true
   """, table_name);
 ELSE
   SET pre_table_name = format("%s.%s_%d", ds, prefix, x-1);
   EXECUTE IMMEDIATE format("""
   MERGE %s t2 USING %s t1 ON t2.partition_id = t1.partition_id
   WHEN MATCHED THEN UPDATE SET value = t1.value
   """, table_name, pre_table_name);
 END IF;
 SET x = x + 1;
END WHILE;
# Verify data in table 9 inside transaction shows recently updated data
SET table_name = format("%s.%s_%d", ds, prefix, num_tables-1);
EXECUTE IMMEDIATE format("""
SELECT * FROM %s ORDER BY partition_id
""", table_name);
IF x = 9 THEN
 COMMIT TRANSACTION;
ELSE
 ROLLBACK TRANSACTION;
END IF;
# Verify data in table 9 outside transaction shows timestamp of original data
EXECUTE IMMEDIATE format("""
SELECT * FROM %s ORDER BY partition_id
""", table_name);

Monitoring and Management

Unlike single-statement transactions which map to a single DML command, multi-statement transactions are complex spanning multiple DML operations across multiple tables. To monitor these complex jobs, the BigQuery JOBS INFORMATION_SCHEMA views have been expanded to add a new column, TRANSACTION_ID, which contains a unique identifier for each transaction in BigQuery. You can query transactions that are in progress or the ones that have completed by querying these views.

Language: SQL

  #Returns the list of transactions running under a parent job ID .;
SELECT
DISTINCT transaction_id
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE parent_job_id = "job_id" AND state = "RUNNING";
# Returns the active transactions and parent job id that are running and affect a named table.;
WITH 
  running_transactions AS (
  SELECT DISTINCT transaction_id 
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT 
  WHERE state = "RUNNING")
SELECT 
  transaction_id, parent_job_id, query 
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT, running_transactions
WHERE destination_table = "mytable" 
  AND transaction_id = running_transactions.transaction_id;
# Returns all successfully committed transactions
SELECT 
  transaction_id, parent_job_id, query 
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT 
WHERE statement_type = "COMMIT_TRANSACTION" AND error_result IS NULL;
# Returns all rolled back transactions
SELECT 
  transaction_id, parent_job_id, query 
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE statement_type = "ROLLBACK_TRANSACTION" AND error_result IS NULL;

To terminate a transaction, you will need to terminate the parent job under which the transaction is running by passing the parent job ID of the script containing the transaction to the BQ.JOBS.CANCEL system procedure.

Conclusion

As enterprises accelerate towards more real time insight-driven decisions, multi-statement transactions are a key enabler towards bringing transaction data into analytical data warehouses, such as BigQuery. With strong ACID properties, full transaction support for commit and rollback, and rich monitoring and management APIs built on proven transaction processing backbone, we are very pleased to help our BigQuery customers unlock the value of transactions through these new capabilities.

While there is uncertainty about how much the climate will change in the future, we know it won’t look like the past. Extreme weather events will increase in frequency and severity; the world will continue to warm, and the cost of climate change will increase.

Government plays a vital role in understanding and responding to these changes quickly. Achieving this improved response time will require data insights to ensure informed decision-making—from local to global scales. The challenge is not only urgent; it’s one of the world’s biggest “big data” problems.

Fortunately, new technologies to help us monitor the Earth are proliferating. Thousands of satellites take millions of images of the planet every day. Sensors generate data about temperature, precipitation, wind, soil conditions, and more—as frequently as every second. We have more information about the planet’s systems than at any other time in history. And the data will only continue to grow. The problem is not the lack of data–it is harnessing this data to drive insights for decision makers to tackle climate change. That’s why Google Cloud has partnered with Climate Engine.

How Climate Engine and Google Cloud enable greater climate resilience

Climate Engine is a scientist-led company that works with Google to accelerate and scale the use of Google Earth Engine’s world-class geospatial capacities (in addition to those of Google Cloud Storage and BigQuery, among other tools) in support of climate action in the public sector. Powered by Google Cloud’s infrastructure, Google Earth Engine (GEE) combines a multi-petabyte catalog of satellite imagery and geospatial datasets with planetary-scale analysis capabilities, enabling scientists, researchers, and developers to detect changes, map trends, and quantify differences on the Earth’s surface. 

With cloud-based technologies, we can leverage massive computing at a scale that generates actionable insights from Earth-based data. These insights help us better manage resources, understand risks, predict changes, and respond to disasters as we meet the challenge of climate change. Geospatial AI combines the power of artificial intelligence (AI) and machine learning (ML) with geospatial analysis. Google Cloud’s Geospatial AI solutions provide departments and agencies with a centralized system to collect, process, and deliver Earth-based data into decision-making contexts.

Climate Engine and Google Cloud provide specialists with the opportunity to go back in time and see how our landscapes have changed due to changes in climate and other human activities over the past few decades. Years of data can now be quantitatively analyzed and visualized in a matter of a few seconds, enabling government agencies to fulfill their mandates by drawing invaluable insights into how landscapes are changing, what physical and natural assets are at risk, and where the opportunities are for reducing emissions and increasing carbon sequestration. 

“This is game changing for natural resource managers and scientists at public institutions at all levels of government,” says Dr. Daniel McEvoy, regional climatologist, at the Desert Research Institute & Western Regional Climate Center, Nevada System of Higher Education.https://www.youtube.com/embed/aPGsi8bd_Zk?enablejsapi=1&

Use cases for geospatial climate information systems

The use cases for this technology are as varied as the climate challenges themselves. These include monitoring, predicting, and analyzing the risks of extreme weather events like floods, wildfire, drought, extreme heat, wind, and other climate hazards. Use cases also include tracking changes in ecosystems, disease vectors, water availability and quality, soil health, growing seasons, air pollution, and more. These use cases are some of the ways that Google Cloud and Climate Engine can help the public sector deliver on government mandates. These provide insights that are helpful for a wide range of departments, and that can be applied in spatial and temporal scales that are meaningful for governments to take action.

“Our planet is changing at a rate that we have never experienced,” says Forrest Melton of the NASA Western Water Applications Office. To respond to these changes, we must understand what is happening across a wide range of environmental variables and at geospatial scales that range from local to global. We now have access to more data about the planet than ever before. The big challenge is converting data into actionable insights and then rapidly integrating these insights into decision-making systems. Climate Engine and Google Cloud help resolve this problem through innovative analytical tools and effective use of cloud computing.” 

Climate change carries an existential risk to our current and future stability and security. Together, we are working to provide transformational technologies that help meet that risk and build a safer, more resilient future for all of us. 

Learn more about Google Cloud’s environmental initiatives here and here.

Building a strong brand in today’s hyper-competitive business environment takes vision. It also requires a flexible, easily managed approach to digital asset management (DAM), so marketing professionals and other stakeholders can easily share, store, track, and manipulate assets to build the brand.

Many of today’s leading companies, including JetBlue, Slack, TripAdvisor, Lyft, and HealthONE, rely on Brandfolder to deliver consistent, organized, and efficient brand experiences. Brandfolder provides an easy-to-use platform that can scale across an entire company with little end-user training, empowering customers to distribute digital assets wherever they are needed. Customers also gain much greater insight into how those assets are used, and how to use them more effectively in marketing campaigns and brand messaging.

“Google Cloud made it easy to build an ML platform to quickly iterate through different brand intelligence use cases and release data-driven product features into the Brandfolder platform.”

—Ajay Rajasekharan, Head of Data Science, Brandfolder

Brandfolder is constantly advancing its development efforts to introduce new data-driven features without complicating the user experience. Big data, artificial intelligence (AI), and machine learning (ML) are key to meeting customers’ unique business needs, and essential for Brandfolder to compete in the fast-moving DAM industry. To enhance these capabilities, Brandfolder sought a public cloud provider that could help it scale its data pipeline cost effectively while providing access to advanced AI technologies.

After graduating from the Techstars startup accelerator program in 2013, Brandfolder tried two other cloud providers before standardizing on Google Cloud Platform (GCP).

“We saw a difference with Google Cloud from the very beginning because the interactions felt like a strategic relationship,” says Jim Hanifen, Head of Product at Brandfolder. “Google gave us startup credits and a lot of face-to-face support, which we hadn’t experienced with other cloud providers. We decided to move our entire infrastructure to Google Cloud Platform.”

Building an ML platform for brand intelligence

After performing an initial lift-and-shift migration of virtual machines (VMs) onto Compute Engine, Brandfolder built an ML platform using GCP managed services to seamlessly deliver its data products. The platform leverages Cloud SQL, Cloud Storage as the data lake, Cloud Dataproc for cloud-native Apache Spark computing clusters, Cloud Composer as the batch job scheduler, Cloud Pub/Sub as the backbone data pipeline, Container Registry to store Docker images, and Google Kubernetes Engine (GKE) as the application orchestrator. Cloud Dataflow brings data into the data lake and into BigQuery for analysis.

“Google Cloud made it easy to build an ML platform to quickly iterate through different brand intelligence use cases and release data-driven product features into the Brandfolder platform,” says Ajay Rajasekharan, Head of Data Science at Brandfolder, who describes the architecture in a detailed blog. “We simply ingest raw application and event data on one end and output an ML service on the other.”

“Moving to Google Cloud Platform allows us to complete more sophisticated data analysis and ML models much faster, and at a much lower cost. We can create brand-specific ML models 12x faster and get them into production quickly to address our customers’ unique business needs.”

—Brett Nekolny, Head of Engineering, Brandfolder

For many general use cases, Brandfolder does not need to build custom ML models, and instead relies on pre-trained API models from GCP. For example, it uses Vision API and Video Intelligence API to auto-tag creative assets on import to enable fast, intuitive searches across images and videos. When more product- and brand-specific modeling is required to address unique customer use cases, Brandfolder builds and trains custom ML models using its GCP pipeline or Cloud AutoML, a suite of products built on Google transfer learning and neural architecture search technology. For example, if a Brandfolder customer makes different types of grills, Brandfolder can use AutoML Vision to train a model to recognize the different grills.

“Moving to Google Cloud Platform allows us to complete more sophisticated data analysis and ML models much faster, and at a much lower cost,” explains Brett Nekolny, Head of Engineering at Brandfolder. “We can create brand-specific ML models 12x faster and get them into production quickly to address our customers’ unique business needs.”

Industry-leading security and performance

Google Cloud’s security model helps Brandfolder give existing and prospective customers peace of mind that their data will be protected. Cloud Identity & Access Management (Cloud IAM) provides enterprise-grade access control, while Cloud Identity-Aware Proxy (Cloud IAP) enables remote users to work more securely without the hassles of a VPN client. GCP also isolates cloud resources into projects, making it easy to assign permissions and keep data and VMs organized and segregated.

“With Google Cloud, everything begins and ends with security, which makes things very easy for us,” says Jim. “If we’re under a security review, we can submit a Google security white paper. If a potential customer has security concerns, we tell them we are hosted on GCP, and those concerns go away.”

To give customers even better application performance for accessing their brand assets, Brandfolder uses Cloud Memorystore, an in-memory data store service for Redis, to cache data and provide sub-millisecond data access for production applications.

“It was much easier for us to use Cloud Memorystore versus running Redis on our compute instances,” says Brett. “The high availability, replication across zones, and automatic failover with no data loss are big for us.”

Global private network interconnects between Google Cloud and the Fastly content delivery network (CDN) dramatically reduce latency, allowing Brandfolder’s customers to deliver and update even very large creative assets quickly around the world.

“What’s beautiful about the relationship between Google and Fastly is that if one of our customers uploads a new version of an asset, we can propagate that out to Fastly, and the new version will automatically show up in all the places where it’s referenced,” says Brett.

“The ability to quickly solve problems with AI has a substantial impact on our revenue, and that’s more apparent every quarter. Few of our competitors are doing product- or brand-specific modeling because it takes a lot of time and resources. We overcame those hurdles with Google Cloud.”

—Jim Hanifen, Head of Product, Brandfolder

Improving employee and customer productivity

Brandfolder also uses Google solutions for real-time collaboration and productivity, using G Suite to connect employees with intuitive, cloud-based apps. Teams use Gmail, Calendar, Docs, Drive, Sheets, Slides, and Hangouts Meet every day to move the business forward. Many of Brandfolder’s customers are also G Suite users, and Brandfolder offers a plug-in that allows them to view their creative assets inside of Docs and pull images in as needed. Customers can also log into Brandfolder with their G Suite credentials, making the solution even easier to use.

“We’ve been using G Suite since the beginning, and it’s helped us collaborate efficiently to build a successful, growing company,” says Jim. “Our teams expect to have that kind of close collaboration, and everyone here enjoys the G Suite experience.”

Driving 99 percent annual business growth

With automated tagging and other innovative AI-based features, Brandfolder is helping customers locate and distribute assets faster. As a result, Brandfolder is building customer loyalty and increasing sales, growing its business by 99 percent year-over-year. Since moving to GCP, Brandfolder has been able to scale its analytics and data pipeline 50x without a corresponding increase in costs and has not had to expand its development team.

“The ability to quickly solve problems with AI has a substantial impact on our revenue, and that’s more apparent every quarter,” says Jim. “Few of our competitors are doing product- or brand-specific modeling because it takes a lot of time and resources. We overcame those hurdles with Google Cloud.”

Editor’s note: In honor of Mother’s Day, we look at how 1-800-FLOWERS.COM, Inc. migrated to Google Cloud as part of its digital transformation to quickly deploy seamless and convenient customer experiences across multiple brands on Mother’s Day and every day. 

As a leading provider of gifts designed to help customers express, connect, and celebrate,  1-800-FLOWERS.COM, Inc. has embraced cloud technologies to grow and transform its business through constant innovation. As part of our digital transformation, we recently completed the migration of our ecommerce platform and other services to Google Cloud. We’ve transitioned from a monolithic to a microservices platform, moved many workloads from our on-premises data centers to our Google Cloud environment, and scaled both horizontally and vertically. 

Since our migration, we’ve developed efficient processes to launch new brands, improved the customer experience across all brands, and seen significantly increased site traffic. 

Nurturing a more delightful customer journey

Customer delight is at the core of everything we do. Whether it be with a flower bouquet, a sweet treat, or a personalized keepsake, our mission is to deliver smiles. With the rise of the COVID-19 pandemic, we’ve all been challenged to find unique and safe ways to continue honoring the special connections in our lives and celebrating occasions with loved ones. Our customers have adapted by doing things such as sending gifts to isolated loved ones, sharing the same meal together virtually, or using video to engage with others through group activities like flower arranging and building charcuterie boards. 

The customer experience is a top priority for us, and we constantly look for innovative ways to enhance the customer journey across our ecommerce platform of more than a dozen brands. As a result, we’ve continued to see a rise in demand as customers enjoy the ease and convenience of our site and discover our full family of brands. 

Migrating to a cloud-first mindset

As we’ve continued to innovate and iterate on the customer experience, we knew we wanted to evolve our platform. We wanted to shift to a microservices platform, which would allow our team the opportunity to release updates to our site more often and set up the right continuous integration/continuous deployment (CI/CD) practices. 

Working with Google Cloud, we were able to move our ecommerce platform to the cloud and standardize our site and brand deployment by building one release that could then be repeated across all of our brands. We built everything in a modular fashion, including microservices and      code libraries, so that sites could be easily constructed and replicated for each brand. And because of this, we were able to launch Shari’s Berries extremely quickly after we acquired the brand in 2019.   

Moving our platform to a completely homegrown solution of microservices was a daunting task. But our team handled it beautifully through load-testing, stress-testing, and building new monitoring tools. And with Google Cloud supporting us all along the way, managing the migration process was simple and easy from start to finish. 

Arranging a better bouquet of services

Currently, we’ve migrated every customer-facing touchpoint for all of our brands to Google Cloud—whether it’s on the web or mobile, our AI bots, or our chat interfaces. 

• We run on Google Kubernetes Engine and Istio.
• We have nearly 200 microservices built to help power our entire ecommerce stack across several cloud services running on Google Cloud. 
• We’re utilizing BigQuery for our offline intelligence.

Results are coming up roses

Our new stack on Google Cloud has benefits for both us and our customers. We moved from a session-based to a token-based system, which provides enhanced security as well as a consistent, convenient experience across all our brands. Using service workers and a single-page app, we are able to download all the relevant site content to the browser in under two seconds to create an instant-click experience for each and every customer. We also use Google Analytics to measure our user interactions and provide personalized results to each customer. Our hope is that with this new system, we can learn from customer behavior to offer gift givers a more personalized shopping experience during each visit. 

The benefits of our new tech stack have not only helped us enhance the solutions we offer to customers today, they’ve also enabled us to offer new ones at lightning speed. With our legacy system, we used to release new code once a week or once a month. Now, even during our peak periods, we’re able to release 10 to 15 times a day and can deploy and pivot quickly to create new microservices and microsites on the fly—often without having to touch any code. 

Efficiencies abound     

The benefits of moving our platform to Google Cloud have extended to our internal teams as well. Before the migration, we had only two environments for developing and testing, which made it time-consuming to test updates before they went into production. Now with Google Cloud, we have several different journey teams—which are made up of developers, product owners, and technical owners—all working in several different environments, solving problems, and creating new solutions together. 

Everyone is now empowered to be self-sufficient, developing and releasing microservices on their own when they’re ready. This has given our developers more time to take part in continued development and learning opportunities. For example, we offer lunch-and-learn sessions as well as other resources for everyone to take advantage of so they can continue to learn and refine their skills.

Planting the seeds for future growth

As we look to the future and think about how we help our customers express, connect, and celebrate, we’ll continue to collaborate across teams to deliver solutions that spread smiles. Specifically, we’re exploring additional use of AI to help us better serve our customers across all our brands. 

We’ve enjoyed the ongoing support we’ve received from the Google Cloud team as they help us build new solutions and design a road map for the future. Their support has helped the 1-800-FLOWERS.COM, Inc. team to realize the power of the cloud and bring the very best experience to our customers.

Learn more about 1-800-FLOWERS.COM, Inc., or check out our recent blog about cloud migration for the real world.

Statsig is a modern feature management and experimentation platform used by hundreds of organizations. Statsig’s end-to-end product analytics platform simplifies and accelerates experimentation with integrated feature gates (flags), custom metrics, real-time analytics tools, and more, enabling data-driven decision-making and confident feature releases. Companies send their real-time event-stream data to Statsig’s data platform, which, on average, adds up to over 30B events a day and has been growing at 30-40% month over month. 

With these fast-growing event volumes, our Spark-based data processing regularly ran into performance issues, pipeline bottlenecks, and storage limits. In turn, this rapid data growth negatively impacted the team’s ability to deliver product insights on time. The sustained Spark tuning efforts that we were dealing with made it challenging to keep up with our feature backlog. Instead of spending time building new features for our customers, our engineering team was controlling significant increases in Spark’s runtime and cloud costs. 

Adopting BigQuery pulled us out of Spark’s recurring spiral of re-architecture and into a Data Cloud, enabling us to focus on our customers and develop new features to help them run scalable experimentation programs.

The growing data dilemma

At Statsig, our processing volumes were growing rapidly. The assumptions and optimizations we madea month ago would become irrelevant the next month. While our team was knowledgeable in Spark performance tuning, the benefits of each change were short lived and became obsolete almost as quickly as it was implemented. As the months passed, our data teams were dedicating moretime to optimizing and tuning Spark clusters instead of building new data products and features. We knew we needed to change our data cloud strategy.

We started to source advice from companies and startups who had faced similar data scaling challenges – the resounding recommendation was “Go with BigQuery.” Initially, our team was reluctant. For us, a BigQuery migration would require an entirely new data warehouse and a cross-cloud migration to GCP.

However, during a company-wide hackathon, a pair of engineers decided to test BigQuery with our most resource-intensive job. Shockingly, this unoptimized job finished much faster and at a lower cost than our current finely-tuned setup. This outcome made it impossible for us to ignore a BigQuery migration any longer. We set out to learn more. 

Investigating BigQuery

When we started our BigQuery journey, the first and obvious thing we had to understand was how to actually run jobs. With Spark, we were accustomed to daunting configurations, mapping concepts like executors back to virtual machines, and creating orchestration pipelines for all the tracking and variations required. 

When we approached running jobs on BigQuery, we were surprised to learn that we only needed to configure the number of slots allocated to the project. Suddenly, with BigQuery, there were no longer hundreds of settings affecting query performance. Even before running our first migration test, BigQuery had already eliminated an expensive and tedious task list of optimizations our team would typically need to complete.

Digging into BigQuery, we learned about additional serverless optimizations that BigQuery offered out of the box that addressed many of the issues we had with Spark. For example, we often had a single task get stuck with Spark because virtual machines would be lost or the right VM shape needed to be attainable. With BigQuery’s autoscaling, SQL jobs are much more granularly defined and can move resources as needed between multiple jobs. As another example, we sometimes encountered a storage issue with Spark due to the shuffled data overwhelming a machine’s disk. On BigQuery, there is a separate in-memory shuffle service that eliminates the need for our team to worry about predicting and sizing shuffle disk sizes. 

At this point, it was clear that the migration away from the DevOps of Spark and into the serverless BigQuery architecture would be worth the effort. 

Spark to BigQuery migration 

When migrating our pipelines over, we ran into situations where we had to rewrite large blocks of code, making it easy to introduce new bugs. We needed a way to simultaneously stage this migration without committing to huge rewrites . Dataproc is a very useful tool for this purpose. Dataproc provides us with a simple yet flexible API to spin up Spark clusters and gives us access to the full swath of configurations and optimizations that we’re accustomed to from our previous Spark deployments.

Additionally, BigQuery offers a direct Spark integration through stored procedures with Apache Spark, which provides a fully managed and serverless Spark experience native to BigQuery and allows you to call Spark code directly from BigQuery SQL. It can be configured as part of the BigQuery autoscaler and called from any orchestration tool that can execute SQL, such as dbt. 

This ability to mix and match BigQuery SQL and multiple options for Spark gave us the flexibility to move to BigQuery immediately but roll out the entire migration on our timeline.

With BigQuery, we’re back to building features 

With BigQuery, we could to tap into performance improvements, direct cost savings, and experienced a reduction in our data pipeline error rates. However, BigQuery really changed our business by unlocking new real-time features that we didn’t have before. A couple of examples are:

1. Fresh, fast data results

On our Spark cluster, we needed to pre-compute tens of thousands of possible results each day if a customer wanted to look at a specific detail. While only a small percentage of results would get viewed each day, we couldn’t predict which results would be needed, so we had to pre-compute it all. With BigQuery, the queries run much faster, so we now compute specific results when customers need them. We benefit from avoiding expensive jobs. To our customers, this translates into fresher data.

2. Real-time decision features

Since our migration to BigQuery began, we have rolled out several new features powered by BigQuery’s ability to compute things in near real-time, enhancing our customers’ ability to make real-time decisions.

1) A metrics explorer that lets our customers query their metric data in real-time.

2) A deep dive experience that lets our customers instantly dig into a specific user’s details instead of waiting on a 15-minute Spark job to process. 

3) A warehouse-native solution that lets our customers use their own BigQuery project to run analysis. 

Migrating from Spark to BigQuery has simplified many of our workflows and saved us significant money. But equally importantly, it has made it easier to work with massive data, reduced the strain on our perpetually stretched-thin data team, and allowed us to build awesome products faster for our customers.

Getting started with BigQuery

There are a few ways to get started with BigQuery. New customers get $300 in free credits to spend on BigQuery. All customers get 10GB storage and up to 1TB queries free per month, not charged against their credits. You can get these credits by signing up for the BigQuery free trial. Not ready yet? You can use the BigQuery sandbox without a credit card to see how it works. 

The Built with BigQuery advantage for ISVs 

Google is helping tech companies like Statsig build innovative applications on Google’s data cloud with simplified access to technology, helpful and dedicated engineering support, and joint go-to-market programs through the Built with BigQuery initiative. 

More on Statsig

Companies want to understand the impact of their new features on the business, test different options, and identify optimal configurations that balance customer success with business impact. But running these experiments is hard. Simple mistakes can cause you to make wrong decisions, and lack of standardization can make results hard to compare, and reduce user trust. When implemented poorly, an experimentation program can become a bottleneck, causing feature releases to slow down or, in some cases, causing teams to skip experimentation altogether. Statsig provides an end-to-end product experimentation and analytics platform that makes it simple for companies to leverage best-in-class experimentation tooling.

If you’re looking to improve your company’s experimentation process, visit statsig.com and explore what our platform can offer. We have a generous free tier and a community of experts on Slack to help ensure your efforts are successful. Boost your business’s growth with Statsig and unlock the full potential of your data.