BigQuery Introduces Fine-Grained DML for Optimized Data Mutations

October 15, 2024 at 7:05:45 AM

TL;DR Google announced a preview of fine-grained DML for BigQuery to enhance UPDATE, DELETE, and MERGE performance. Features include granular optimization, reduced data rewrites, and lower slot consumption. Available for projects with annual or three-year committed slots. Enable by enrolling in the preview and using specific options in CREATE or ALTER TABLE statements. Considerations include potential storage costs.

BigQuery Introduces Fine-Grained DML for Optimized Data Mutations

Google has announced a preview of fine-grained DML (Data Manipulation Language) for BigQuery, aiming to enhance the performance of UPDATE, DELETE, and MERGE statements.

Key Features:

  1. Granular Approach: Optimizes mutations at a more detailed level than the previous file-group approach
  2. Reduced Data Rewrites: Aims to minimize the amount of data that needs to be rewritten during operations
  3. Lower Slot Consumption: Designed to reduce overall slot usage for DML operations

Availability:

  • Available for projects assigned to reservations using annual or three-year committed slots

How to Enable:

  1. Enroll in the preview via the BigQuery fine-grained DML enrollment form
  2. Use OPTIONS(enable_fine_grained_mutations = TRUE) in CREATE TABLE or ALTER TABLE statements

Considerations:

  • May incur additional storage costs for mutation metadata
  • Processes deleted data offline, with different handling for projects with and without BACKGROUND assignments
  • Some limitations apply, including restrictions on table snapshots and replication

Best Practices:

  • Group DML operations when possible
  • Consider table partitioning for updates on older data
  • Use clustered tables for frequent updates on specific column value ranges

This preview aims to provide BigQuery users with more efficient data manipulation capabilities, potentially leading to improved performance for complex data operations.

Have more questions on this topic? Ask our AI assistant for in-depth insights.

Read more from sources πŸ‘‡

The Only Digital Marketing Feed You'll Ever Need.

Stay informed your way. Tailored updates when and how you want them. 100% Free.

10,000+ Users

500+ Sources

1000+ Tools

Or

Related Posts

Google Sheets Integrates BigQuery Saved Queries

Google Sheets Integrates BigQuery Saved Queries

Google
Google

Official Source

Official Source

Google is a Official Source. The source has been verified by Swipe Insight team.

Official Source
BigQuery Expands Search Index Capabilities with INT64 and TIMESTAMP Support

BigQuery Expands Search Index Capabilities with INT64 and TIMESTAMP Support

Google Cloud
Google Cloud

Official Source

Official Source

Google Cloud is a Official Source. The source has been verified by Swipe Insight team.

Official Source
The Ultimate Google Analytics Audit Tool

The Ultimate Google Analytics Audit Tool

Featured
Google Ends Free Access to Gemini in BigQuery, Announces Paid Plans

Google Ends Free Access to Gemini in BigQuery, Announces Paid Plans

Google Cloud
Google Cloud

Official Source

Official Source

Google Cloud is a Official Source. The source has been verified by Swipe Insight team.

Official Source
Google Cloud Enhances Looker with Major User Experience Updates

Google Cloud Enhances Looker with Major User Experience Updates

Sean Zinsmeister
Sean Zinsmeister

Official Source

Official Source

Sean Zinsmeister is a Official Source. The source has been verified by Swipe Insight team.

Official Source
BigQuery Launches Gemini-Enhanced SQL Translation Features

BigQuery Launches Gemini-Enhanced SQL Translation Features

Google Cloud
Google Cloud

Official Source

Official Source

Google Cloud is a Official Source. The source has been verified by Swipe Insight team.

Official Source
BigQuery Data Transfer Service Launches Data Source Change Log for Schema Updates

BigQuery Data Transfer Service Launches Data Source Change Log for Schema Updates

Google Cloud
Google Cloud

Official Source

Official Source

Google Cloud is a Official Source. The source has been verified by Swipe Insight team.

Official Source
BigQuery Introduces AI-Augmented Data Preparation with Gemini

BigQuery Introduces AI-Augmented Data Preparation with Gemini

Google Cloud
Google Cloud

Official Source

Official Source

Google Cloud is a Official Source. The source has been verified by Swipe Insight team.

Official Source

Related Tools

Marketing Auditor logo

Marketing Auditor

Verified Tool

Verified Tool

Marketing Auditor is a Verified Tool. Want to get this badge? Contact us.

Verified Tool

Automated audits for Google Ads and Analytics.

Get Featured Here

Showcase your tool in this list.

Contact Us
Databricks logo

Databricks

Generative AI-powered data intelligence platform

Data Engineering
GA4 SQL logo

GA4 SQL

Verified Tool

Verified Tool

GA4 SQL is a Verified Tool. Want to get this badge? Contact us.

Verified Tool

Generate GA4 BigQuery queries easily

Data Analysis
TapClicks logo

TapClicks

Automated marketing solutions powered by your data

Data Engineering
Stitch logo

Stitch

Automated cloud data pipelines, no coding needed

Data Engineering
Akkio logo

Akkio

AI-powered analytics for agencies

Data Analysis
NinjaCat logo

NinjaCat

AI-powered marketing data and analytics platform

Reporting
Funnel logo

Funnel

Aggregate and analyze marketing data seamlessly

Reporting
Fivetran logo

Fivetran

Effortlessly centralize and move data from any source

Data Engineering
Power My Analytics logo

Power My Analytics

Automate and integrate your marketing data

Reporting

Get Featured Here

Showcase your tool in this list.

Contact Us