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.

As organizations expand their use of cloud computing services, more of their sensitive data inevitably moves to and lives in the cloud. Much of this sensitive data is unstructured and can be challenging to secure. Despite this potential challenge, the usefulness of cloud for data storage and processing is too big for most organizations to ignore and has in turn led to data sprawl, where their sensitive data is spread over many resources, both in the cloud and on-premise. Addressing data sprawl requires solutions that can discover, manage, and secure sensitive data, especially unstructured data, as it spreads.

To help organizations confidently move their sensitive data to the cloud, Google Cloud works diligently to earn and maintain customer trust. Control and transparency are pillars of our approach to offering a trusted cloud. Therefore, we’ve been expanding our capabilities to act on unstructured data as sprawl increases.

Given the importance of these capabilities to our strategy, we are happy to announce today that Forrester Research has named Google Cloud a Leader in The Forrester Wave™: Unstructured Data Security Platforms, Q2 2021 report, and rated Google Cloud highest in the current offering category among the providers evaluated.

The report evaluates the 11 most significant providers with platform solutions to secure and protect unstructured data, spanning from cloud providers to data security-focused vendors. The report notes that “Google offers breadth and depth with built-in data security in the cloud. Google Cloud Platform, Google Workspace, and BeyondCorp Enterprise have underlying data security products and features for protecting customer data.”

Google Cloud tools focused on protecting unstructured data were developed and battle-tested internally at Google to alleviate some of our own data security challenges. This brings the best of Google security to the organizations utilizing Google Cloud and our security tools. The report highlights that “Google productizes capabilities originally developed to secure its own business, and brings a disciplined approach to product enhancements for enterprise requirements. It serves a wide range of enterprise and mid-market, with a focus on emphasizing data protection needs by industry. ”

Google Cloud’s data security strategy focuses on meeting customers wherever they are in their cloud migration journey. The report highlights that “Google further enables a Zero Trust approach with third-party integrations through its BeyondCorp Alliance of partners in device management, endpoint security and gateways.”

Google Cloud received the highest possible score in sixteen criteria, in total receiving the most 5 out of 5 ratings among all vendors assessed. These criteria include: Data Intelligence, Access Control, Deletion, Obfuscation-Scope, Obfuscation-Key Management, Deployment, Security and Risk, APIs and Integration, Data Security Platform Vision, Data Security Execution Roadmap, Performance, Planned Enhancements, Zero Trust Enabling Partner Ecosystem, Diversity, Equity and Inclusion, Installed Base, and Revenue. 

Notably, Google Cloud received the highest possible score in the Obfuscation criteria. Obfuscation can help protect sensitive data, like personally identifiable information (PII), which is critical to many enterprise workflows. Cloud DLP helps customers inspect and mask this sensitive data with techniques like redaction, bucketing, and tokenization, which help strike the balance between risk and utility. This is especially crucial when dealing with unstructured or free-text workloads, in which it can be challenging to know what data to redact. More than 150 detectors combine to power Cloud DLP’s masking, which can be deployed in data migrations and business workloads like real-time data collection and processing. For Obfuscation specifically, the report mentioned that Google “takes a broad view of DLP, which includes in-line redaction of sensitive elements in unstructured data and DLP APIs that extend support to additional data types like images or other media.”

We are honored to be a Leader in The Forrester Wave™ Unstructured Data Security Platforms Q2 2021 report, and look forward to continuing to innovate and partner with you on ways to make your digital transformation journey safer as we work to become your most trusted Cloud.

A copy of the full report can be viewed here.

Today at Google Cloud Next we are announcing innovations that will enable data teams to simplify how they work with data and derive value from it faster. These new solutions will help organizations build modern data architectures with real-time analytics to power innovative, mission-critical, data-driven applications. 

Too often, even the best minds in data are constrained by ineffective systems and technologies. A recent study showed that only 32% of companies surveyed gained value from their data investments. Previous approaches have resulted in difficult to access, slow, unreliable, complex, and fragmented systems. 

At Google Cloud, we are committed to changing this reality by helping customers simplify their approach to data to build their data clouds. Google Cloud’s data platform is simply unmatched for speed, scale, security, and reliability for any size organization with built-in, industry-leading machine learning (ML) and artificial intelligence (AI), and an open standards-based approach.

Vertex AI and data platform services unlock rapid ML modeling 

With the launch of Vertex AI in May 2021, we empowered data scientists and engineers to build reliable, standardized AI pipelines that take advantage of the power of Google Cloud’s data pipelines. Today, we are taking this a step further with the launch of Vertex AI Workbench, a unified user experience to build and deploy ML models faster, accelerating time-to-value for data scientists and their organizations. We’ve integrated data engineering capabilities directly into the data science environment, which lets you ingest and analyze data, and deploy and manage ML models, all from a single interface.

Data scientists can now build and train models 5X faster on Vertex AI than on traditional notebooks. This is primarily enabled by integrations across data services (like Dataproc, BigQuery, Dataplex, and Looker), which significantly reduce context switching. The unified experience of Vertex AI let’s data scientists coordinate, transform, secure and monitor Machine Learning Operations (MLOps) from within a single interface, for their long-running, self-improving, and safely-managed AI services.

“As per IDC’s AI StrategiesView 2021, model development duration, scalable deployment, and model management are three of the top five challenges in scaling AI initiatives,” said Ritu Jyoti, Group Vice President, AI and Automation Research Practice at IDC. “Vertex AI Workbench provides a collaborative development environment for the entire ML workflow – connecting data services such as BigQuery and Spark on Google Cloud, to Vertex AI and MLOps services. As such, data scientists and engineers will be able to deploy and manage more models, more easily and quickly, from within one interface.”

Ecommerce company, Wayfair, has transformed its merchandising capabilities with data and AI services. “At Wayfair, data is at the center of our business. With more than 22 million products from more than 16,000 suppliers, the process of helping customers find the exact right item for their needs across our vast ecosystem presents exciting challenges,” said Matt Ferrari, Head of Ad Tech, Customer Intelligence, and Machine Learning; Engineering and Product at Wayfair. “From managing our online catalog and inventory, to building a strong logistics network, to making it easier to share product data with suppliers, we rely on services including BigQuery to ensure that we are able to access high-performance, low-maintenance data at scale. Vertex AI Workbench and Vertex AI Training accelerate our adoption of highly scalable model development and training capabilities.”

BigQuery Omni: Breaking data silos with cross-cloud analytics and governance

Businesses across a variety of industries are choosing Google Cloud to develop their data cloud strategies and better predict business outcomes — BigQuery is a key part of that solution portfolio. To address complex data management across hybrid and multicloud environments, this month we are announcing the general availability of BigQuery Omni, which allows customers to analyze data across Google Cloud, AWS, and Azure. Healthcare provider, Johnson and Johnson was able to combine data in Google Cloud and AWS S3 with BigQuery Omni without needing data to migrate. 

This flexible, fully-managed, cross-cloud analytics solution allows you to cost-effectively and securely answer questions and share results from a single pane of glass across your datasets, wherever you are. In addition to these multicloud capabilities, Dataplex will be generally available this quarter to provide an intelligent data fabric that enables you to keep your data distributed while making it securely accessible to all your analytics tools.

Spark on Google Cloud simplifies data engineering 

To help make data engineering even easier, we are announcing the general availability of Spark on Google Cloud, the world’s first autoscaling and serverless Spark service for the Google Cloud data platform. This allows data engineers, data scientists, and data analysts to use Spark from their preferred interfaces without data replication or custom integrations. Using this capability, developers can write applications and pipelines that autoscale without any manual infrastructure provisioning or tuning. This new service makes Spark a first class citizen on Google Cloud, and enables customers to get started in seconds and scale infinitely, regardless if you start in BigQuery, Dataproc, Dataplex, or Vertex AI.

Spanner meets PostgreSQL: global, relational scale with a popular interface

We’re continuing to make Cloud Spanner, our fully managed, globally scalable, relational database, available to more customers now with a PostgreSQL interface, now in preview. With this new PostgreSQL interface, enterprises can take advantage of Spanner’s unmatched global scale, 99.999% availability, and strong consistency using skills and tools from the popular PostgreSQL ecosystem. 

This interface supports Spanner’s rich feature set that uses the most popular PostgreSQL data types and SQL features to reduce the barrier to entry for building transformational applications. Using the tools and skills they already have, developer teams gain flexibility and peace of mind because the schemas and queries they build against the PostgreSQL interface can be easily ported to another Postgres environment. Complete this form to request access to the preview.

Our commitment to the PostgreSQL ecosystem has been long standing. Customers choose Cloud SQL for the flexibility to run PostgreSQL, MySQL and SQL Server workloads. Cloud SQL provides a rich extension collection, configuration flags, and open ecosystem, without the hassle of database provisioning, storage capacity management, or other time-consuming tasks.

Auto Trader has migrated approximately 65% of their Oracle footprint to Cloud SQL, which remains a strategic priority for the company. Using Cloud SQL, BigQuery, and Looker to facilitate access to data for their users, and with Cloud SQL’s fully managed services, Auto Trader’s release cadence has improved by over 140% (year-over-year), enabling an impressive peak of 458 releases to production in a single day.

Looker integrations make augmented analytics a reality

We are announcing a new integration between Tableau and Looker that will allow customers to operationalize analytics and more effectively scale their deployments with trusted, real-time data, and less maintenance for developers and administrators. Tableau customers will soon be able to leverage Looker’s semantic model, enabling new levels of data governance while democratizing access to data. They will also be able to pair their enterprise semantic layer with Tableau’s leading analytics platform. The future might be uncertain, but together with our partners we can help you plan for it. 

We remain committed to developing new ways to help organizations go beyond traditional business intelligence with Looker. In addition to innovating within Looker, we’re continuing to integrate within other parts of Google Cloud. Today, we are sharing new ways to help customers deliver trusted data experiences and leverage augmented analytics to take intelligent action. 

First, we’re enabling you to democratize access to trusted data in tools where you are already familiar. Connected Sheets already allows you to interactively explore BigQuery data in a familiar spreadsheet interface and will soon be able to leverage the governed data and business metrics in Looker’s semantic model. It will be available in preview by the end of this year. 

Another integration we’re announcing is Looker’s Solution for Contact Center AI, which helps you gain a deeper understanding and appreciation of your customers’ full journey by unlocking insights from all of your company’s first-party data, such as contextualizing support calls to make sure your most valuable customers receive the best service. 

We’re also sharing the new Looker Block for Healthcare NLP API, which provides simplified access to intelligent insights from unstructured medical text. Compatible with Fast Healthcare Interoperability Resources (FHIR), healthcare providers, payers, and pharma companies can quickly understand the context and relationships of medical concepts within the text, and in turn, can begin to link this to other clinical data sources for additional AI and ML actions. 

Bringing the best of Google together with Google Earth Engine and Google Cloud

We are thrilled to announce the preview of Google Earth Engine on Google Cloud. This launch makes Google Earth Engine’s 50+ petabyte catalog of satellite imagery and geospatial data sets available for planetary-scale analysis. Google Cloud customers will be able to integrate Earth Engine with BigQuery, Google Cloud’s ML technologies, and Google Maps Platform. This gives data teams a way to better understand how the world is changing and what actions they can take — from sustainable sourcing, to saving energy and materials costs, to understanding business risks, to serving new customer needs. 

For over a decade, Earth Engine has supported the work of researchers and NGOs from around the world, and this new integration brings the best of Google and Google Cloud together to empower enterprises to create a sustainable future for our planet and for your business.

At Google Cloud, we are deeply grateful to work with companies of all sizes, and across industries, to build their data clouds. Join my keynote session to hear how organizations are leveraging the full power of data, from databases to analytics that support decision making to AI and ML that predict and automate the future. We’ll also highlight our latest product innovations for BigQuery, Spanner, Looker, and Vertex AI.

I can’t wait to hear how you will turn data into intelligence and look forward to connecting with you.

May was a very busy month for data analytics product innovation. If you didn’t have the chance to attend our inaugural Data Cloud Summit, video replays of all our sessions are now available so feel free to watch them at your own pace. 

In this blog, I’d like to share some background behind the innovations we released in May, why we built them the way we did, and the type of value they can bring your company and your team.

But first, a huge thank you!

This week, we had the honor to announce that Google has been named a Leader in The Forrester Wave™: Streaming Analytics, Q2 2021 report. Forrester gave Dataflow a score of 5 out of 5 across 12 different criteria, stating: “Google Cloud Dataflow has strengths in data sequencing, advanced analytics, performance, and high-availability”. 

Google has more than a decade of experience in building real-time and internet-scale systems for its own needs, and we are excited to see that our ability to provide customers with a reliable, scalable, and performant platform is bearing fruit. 

This announcement comes on the back of the release of The Forrester Wave™: Cloud Data Warehouse, Q1 2021 report, which also named Google Cloud as a Leader.

We couldn’t be more excited about the recognition and appreciate all your feedback and trust in the work that we do to support your goal in accelerating data-powered innovation.

Innovation galore

Your feedback and your passion is the fuel that drives our ambition to deliver more and better services to you. That’s why, this year, we didn’t want to wait until Google Cloud Next to share some great products we have been working on. On May 26, our team announced a slew of new products, services and programs. Watch a quick summary below:

https://youtube.com/watch?v=DG1mOPMXJvw%3Fenablejsapi%3D1%26

Meeting you where you are

An important design principle behind all of our services is “meeting you where you are”. This means we aim to provide you with the tools and software you need to innovate on your own terms. Here are three new services that will help you do just that:

Datastream

Datastream, our new serverless change data capture (CDC) and replication service, allows your company to synchronize data across heterogeneous databases, storage systems, and applications reliably and with minimal latency to support real-time analytics, database replication, and event-driven architectures. Datastream delivers change streams from Oracle and MySQL databases into Google Cloud services such as BigQuery, Cloud SQL, Cloud Storage, and Cloud Spanner, saving time and resources while ensuring your data is accurate and up-to-date. 

• Under the hood, Datastream reads CDC events (inserts, updates, and deletes) from source databases, and writes those events with minimal latency to a data destination. It leverages the fact that each database source has its own CDC log—binlog for MySQL and LogMiner for Oracle—which it uses for its own internal replication and consistency purposes. 
• Datastream integrates with purpose-built and extensible Dataflow templates to pull the change streams written to Cloud Storage, and create up-to-date replicated tables in BigQuery for analytics. It also leverages Dataflow templates to replicate and synchronize databases into Cloud SQL or Cloud Spanner for database migrations and hybrid cloud configurations. 
• Datastream also powers a Google-native Oracle connector in Cloud Data Fusion’s new replication feature for easy ETL/ELT pipelining. By delivering change streams directly into Cloud Storage, customers can leverage Datastream to implement modern, event-driven architectures.

Looker and BigQuery Omni on Microsoft Azure

Research on multi cloud adoption is unequivocal — 92% of businesses in 2021 report having a multi cloud strategy. We want to continue supporting your choice by providing the flexibility you need to see your strategy through. 

• This past month, we introduced Looker, hosted on Microsoft Azure. For the first time, you can now choose Azure, Google Cloud, or AWS for your Looker instance. You can also self-host your Looker instance on-premises.
• We also introduced BigQuery Omni for Azure, which along with last year’s introduction of BigQuery Omni for AWS, will help you access and securely analyze data across Google Cloud, AWS, and Azure. 

The cost of moving data between cloud providers isn’t sustainable for many, and it’s still difficult to seamlessly work across clouds. BigQuery Omni represents a new way of analyzing data stored in multiple public clouds, which is made possible by BigQuery’s separation of compute and storage. By decoupling these two, BigQuery provides scalable storage that can reside in Google Cloud or other public clouds, and stateless resilient compute that executes standard SQL queries. 

• Unlike competitors, BigQuery Omni doesn’t require you to move or copy your data from one public cloud to another, where you might incur egress costs. You also benefit from the same BigQuery interface on Google Cloud, enabling you to query data stored in Google Cloud, AWS, and Azure without any cross-cloud movement or copies of data. 
• BigQuery Omni’s query engine runs the necessary compute on clusters in the same region where your data resides. For example, you can query Google Analytics 360 Ads data stored in Google Cloud and query logs data from your ecommerce platform and applications that are stored in AWS S3 and/or Microsoft Azure. 

Then, using Looker, you can build a dashboard that allows you to visualize your audience behavior and purchases alongside your advertising spend. 

Dataplex

We understand that most organizations still struggle to make high-quality data easily discoverable and accessible for analytics, across multiple silos, to a growing number of people and tools within their organization. 

They are often forced to make tradeoffs. For instance, moving and duplicating data across silos to enable diverse analytics use cases or leaving their data distributed but limiting the agility of decisions. 

• Dataplex provides an intelligent data fabric that enables you to centrally manage, monitor, and govern your data across data lakes, data warehouses, and data marts, while also ensuring data is securely accessible to a variety of analytics and data science tools. 
• One of the core tenets of Dataplex is letting you organize and manage your data in a way that makes sense for your business, without data movement or duplication. For that, we provide logical constructs like lakes, data zones, and assets. These constructs enable you to abstract away the underlying storage systems and become the foundation for setting policies around data access, security, lifecycle management, and so on. 
• For example, you can create a lake per department within your organization (e.g. Retail, Sales, Finance, etc.) and create data zones that map to data readiness and usage (e.g. landing, raw, curated_data_analytics, curated_data_science, etc.). 

Once you have your lakes and zones setup, you can attach data to these zones as assets. You can add data from different types of storage (e.g. GCS Bucket and BigQuery dataset) under the same zone. You can also attach data across multiple projects under the same zone. You can ingest data into your lakes and zones using the tools of your choice, including services such as Dataflow, Data Fusion, Dataproc, Pub/Sub, or choose from one of our partner products. Dataplex comes with built-in 1-click templates for common data management tasks. 
To find out more about Dataplex, head to cloud.google.com/dataplex or watch the video below:

https://youtube.com/watch?v=bbFeAt7cw1g%3Fenablejsapi%3D1%26

Helping you innovate everyday

Sharing data is hard. Traditional data sharing techniques use batch data pipelines that are expensive to run, create late arriving data, and can break with any changes to the source data. These techniques also create multiple copies of data, which brings unnecessary costs and can bypass data governance processes. They also fail to offer features for data monetization, such as managing subscriptions and entitlements. Altogether, these challenges mean that organizations are unable to realize the full potential of transforming their business with shared data.

Analytics Hub

To address these limitations, we are introducing Analytics Hub, a new fully managed service that helps organizations unlock the value of data sharing, leading to new insights and increased business value. 

This new service is built on the tremendous experience and feedback we have received over the years. For example, BigQuery has had cross-organizational, in-place data sharing capabilities since its inception in 2010—and the functionality is very popular. Over a 7-day period in April, we had over 3,000 different organizations sharing over 200 petabytes of data. These numbers don’t include data sharing between departments within the same organization.

Analytics Hub takes sharing to the next level, making it easy for you to publish, discover, and subscribe to valuable datasets that you can combine with your own data to derive unique insights. 

This includes: 

• Shared datasets: As a data publisher, you create shared datasets that contain the views of data that you want to deliver to your subscribers. Data subscribers can search through the datasets that are available across all exchanges for which they have access and subscribe to relevant datasets. In addition, the publisher can track subscribers, disable subscriptions, and see aggregated usage information for the shared data.
• Curated, self-service data exchanges: Exchanges are collections used to organize and secure shared datasets. By default, exchanges are completely private, but granular roles and permissions make it easy to deliver data to the right audience—whether internal or public. 

This is just the beginning for Analytics Hub. Please sign up for the preview, which is scheduled to be available in the third quarter of 2021.

Dataflow Prime

At Google Cloud, we have the great privilege of working with some of the most innovative organizations in the world. And this work provides us with a unique perspective into the future of big data processing. Dataflow Prime is a new platform based on a serverless, no-ops, and auto-tuning architecture that brings unparalleled resource utilization and radical operational simplicity to big data processing. This new service introduces a large number of exciting capabilities but I’d like to highlight three key aspects of the product:

• Vertical Autoscaling: Dataflow Prime dynamically adjusts the compute capacity allocated to each worker based on utilization, detecting when jobs are limited by worker resources and automatically adding more resources. Vertical Autoscaling works hand in hand with Horizontal Autoscaling to seamlessly scale workers to best fit the needs of the pipeline. As a result, it no longer takes hours or days to determine the perfect worker configuration to maximize utilization. 
• Right Fitting: Each stage of a pipeline typically has a different resource requirement than the others. Until now, either all workers in the pipeline would have had the higher memory and GPU, or none of them would. Pipelines either had to waste resources or suffer slower workloads. Right Fitting solves this problem by creating stage-specific pools of resources, optimized for each stage. 
• Smart Recommendations: Smart Recommendations automatically detects problems in your pipeline and shows potential fixes. For example, if your pipeline is running into permissions issues, a Smart Recommendation will detect which IAM permissions you need to enable to unblock your job. If you are using an inefficient coder in your job, Smart Recommendations will surface more performant coder implementations that can help you save on costs.

What’s next

We’re excited to hear your thoughts and feedback about all these exciting new services. I would also highly recommend that you connect with members of the community to learn more about their story and journey. A good example to start with is the Data To Value customer panel we produced at our inaugural Data Cloud Summit with the Chief Data Officers of Keybank and Rackspace. You can watch it for free below:

https://youtube.com/watch?v=ITI2Q3MkxuA%3Fenablejsapi%3D1%26