Exploring 75 New BigQuery Capabilities for 2024: What’s Available and What’s Next

BigQuery keeps improving, offering new features that help data analysts work with their data more efficiently. These updates are handy for managing large datasets with tools that make data integration, query optimization, and reporting smoother.

Whether combining data from different sources or refining complex queries, BigQuery’s new features are built to make your job easier and more effective.

The 2024 updates to BigQuery introduce a host of powerful features that can optimize their workflows, reduce manual effort, and focus more on deriving actionable insights from their data.

In 2024, BigQuery introduced new features that significantly improve data management and analysis.

These updates are focused on four key areas:

• Lakehouse Foundation: Strengthening data integration and management across hybrid environments.
• Data + AI: Advancing AI integration and machine learning tools for more intelligent data analysis.
• Enterprise Class: Enhancing scalability, security, and enterprise-level functionality.
• Migrations & Data Pipelines: Simplifying data migrations and streamlining pipeline operations.

These updates are set to enhance efficiency and drive innovation, elevating your data strategy to the next level. Below, we break down these categories and the availability status of each feature.

BigQuery's Lakehouse Foundation represents a significant step in unifying data management and analytics. It merges the strengths of data lakes and warehouses, allowing seamless data integration, storage, and analysis.

The 2024 update rolls out several new features under the "generally available" option of the Lakehouse Foundation. These enhancements include Materialized Views, improved JSON functions, and optimized query performance, all designed to make data management smoother and more efficient.

Materialized Views for BigLake metadata cache-enabled tables can reference structured data stored in Cloud Storage and Amazon S3. These views bring significant benefits, including automatic refresh, smart tuning, and the capability to pre-aggregate, pre-filter, and pre-join data stored outside BigQuery.

The Enhanced JSON Functions in BigQuery now provide more powerful tools for parsing, querying, and manipulating JSON data. These enhancements simplify extracting specific JSON elements, managing nested structures, and converting JSON data into tabular formats, making working with complex data types within BigQuery easier.

BigQuery’s Manifest Files for External Table Integration enable seamless querying of data stored in open formats like Parquet and ORC. By using manifest files, you can define and manage data locations outside BigQuery, allowing efficient integration and querying of external datasets without importing them into BigQuery, simplifying data management.

BigQuery's Query Execution Graph is a visual tool that breaks down query execution plans. Illustrating the query in stages and showing how data flows makes it easier to spot bottlenecks and inefficiencies. This allows for targeted improvements, ensuring better resource use across complex datasets.

BigQuery’s External Query SQL Pushdown boosts performance by delegating specific operations, like filtering and column selection, directly to external data sources like Cloud SQL or Cloud Spanner. By processing only the necessary data at the source, this approach reduces data transfer back to BigQuery, reducing execution time and costs.

BigQuery’s Recursive Common Table Expressions (CTEs) simplify the construction of complex queries by allowing repeated execution of a CTE until a specific condition is met. This feature is handy for tasks like hierarchical data retrieval, graph traversal, and iterative calculations, making it easier to handle complex data relationships within a single query.

In BigQuery's Enterprise and Enterprise Plus editions, the Cross-user Result Cache speeds up query responses by allowing users with the right permissions to access cached results from other users within the same project. The cached result is stored in the querying user’s anonymous dataset for 24 hours.

BigQuery's High Cardinality Join Insights feature helps you optimize queries that involve large, complex datasets. Identifying and analyzing high-cardinality joins provides detailed insights into join patterns that might slow down performance. This allows users to refine queries and improve data relationships.

BigQuery’s Subscriber Usage Metrics offer deep insights into how subscribers use shared datasets within the Analytics Hub. These metrics allow data providers to track access patterns, query frequency, and data usage trends, helping them optimize their offerings, allocate resources more effectively, and improve overall performance.

BigQuery's Primary and Foreign Key Table Constraints are essential for maintaining data integrity by enforcing relationships between tables. Primary keys ensure each row is uniquely identified, while foreign keys link related tables. These constraints help keep data consistent and accurate across large datasets, supporting reliable database operations and preventing errors.

BigQuery’s Object Tables allow for better-structured data management by enabling the analysis of unstructured data stored in Cloud Storage. These read-only tables can support inference with BigQuery ML and allow you to join results with structured data. They also use access delegation, ensuring secure and controlled access to the underlying Cloud Storage objects.

The Preferred Tables feature in BigQuery’s BI Engine enhances performance for visual reporting tools like Looker by prioritizing certain tables for in-memory analysis. This makes query execution faster and dashboards more responsive. By designating preferred tables, you can streamline data processing and deliver real-time insights more effectively in BI workflows.

BigQuery's Case-insensitive Collation feature ensures consistent data matching by ignoring case differences in comparisons, sorting, and other operations that support collation. This feature includes the COLLATE function and options for applying collation to specific columns or entire tables, improving data accuracy across queries.

BigQuery’s Default Values on Columns feature simplifies data entry by automatically assigning predefined values to columns when no explicit value is provided during data insertion. This reduces the chances of manual errors and streamlines the data-loading process by automating the assignment of common or expected values.

The upcoming updates to Lakehouse Foundation in BigQuery are set to bring better tools, improve data integration, and boost performance, making data management smarter and more efficient.

BigQuery Studio is a unified, collaborative workspace to accelerate data-to-AI workflows by integrating SQL, Python, and Spark within a single platform. It enhances productivity through seamless data preparation, analysis, and machine learning while offering robust security, governance, and AI-powered code assistance.

Currently, in Preview, BigQuery's Partition Skew Insights tool helps identify uneven data distribution in JOIN operations, which can lead to performance bottlenecks. This feature highlights oversized partitions that are resistant to further splitting, guiding users to rebalance data distribution.

BigQuery’s new Stored Procedures for Apache Spark integration allow complex data processing tasks to be executed within Spark directly from BigQuery using SQL. This feature improves how Spark and BigQuery work together, making workflows simpler and increasing the efficiency and scalability of data processing across platforms.

The AWS Glue Federated Dataset feature in BigQuery enhances data connectivity by enabling queries on data stored in AWS without needing to move or copy it. This seamless integration supports comprehensive analytics across platforms while maintaining the original data in its AWS environment, providing users with a broader reach for their data analysis.

BigQuery’s Metadata Caching for object tables and BigLake tables, including those using Amazon S3, significantly improves query performance by storing metadata like file names and partition details. This reduces query latency by avoiding the need to repeatedly retrieve metadata from external sources, which is especially beneficial for queries involving numerous files or Hive partition filters, ensuring faster data processing.

BigQuery's Data Clean Rooms offer a secure environment for data collaboration, allowing multiple parties to analyze and share insights without exposing sensitive data. Built on the Analytics Hub platform, these clean rooms ensure privacy and compliance, enabling secure data exchanges while maintaining strict data governance standards.

BigQuery’s Quantitative LIKE Operator enhances query precision by allowing pattern matching with numeric values within JSON data. This operator enables more accurate filtering based on numeric conditions, providing query flexibility and specificity, mainly when working with complex, nested JSON structures.

The Expanded Query Support for Search Indexes in BigQuery improves data retrieval capabilities by allowing more complex queries on indexed data, including pattern matching and range queries. This enhancement boosts query performance, making it easier to search and analyze large datasets, especially in text-heavy or unstructured data environments.

BigQuery's SQL Grouping Sets, including Cube and Rollup functions, offer advanced data aggregation capabilities. These functions enable users to generate multiple grouping combinations within a single query, allowing for more comprehensive analysis and reporting.

Cube aggregates across all possible dimensions, while Rollup creates hierarchical groupings, facilitating deeper insights and more thorough data summarization.

BigQuery introduces advanced text analysis configurations to enhance search capabilities. These include options for CREATE SEARCH INDEX, LOG_ANALYZER, PATTERN_ANALYZER, and the new TEXT_ANALYZE function.

These features support advanced processing functions like ML.BAG_OF_WORDS, ML.TF_IDF and various distance measures enable more precise and sophisticated text analysis and search operations.

BigQuery’s Native Integration with Looker Studio allows seamless data visualization and reporting. Users can connect BigQuery datasets directly to Looker Studio, enabling real-time analysis and the creation of interactive dashboards.

BigQuery’s Subscription Management in Analytics Hub streamlines service oversight by allowing users to manage data subscriptions efficiently. This feature offers a centralized platform to monitor, update, and cancel subscriptions, ensuring easy access to shared datasets and better control over data usage across projects.

Routines in Analytics Hub are designed to optimize workflows by enabling the sharing and execution of reusable SQL scripts, functions, and stored procedures across different projects and organizations. This feature enhances collaboration and streamlines complex data operations, allowing for more efficient data analysis and consistent task execution.

BigQuery’s Analytics Hub includes the ability to restrict data egress, which enhances security by controlling how shared datasets are accessed and transferred. This feature ensures that sensitive data remains within authorized environments, preventing unauthorized export or misuse while enabling collaborative data analysis and sharing across projects.

BigQuery’s Non-incremental Materialized Views allow for a complete data refresh by fully rebuilding the view rather than updating only the changed data. This approach is ideal for scenarios requiring comprehensive data recalculations, ensuring accuracy and consistency in complex datasets.

BigQuery’s Unicode Column Naming feature enhances universal data compatibility by supporting the use of Unicode characters in column names. This update accommodates global languages and symbols, enabling more intuitive and culturally relevant data schemas and improving accessibility and usability across diverse datasets and international data environments.

BigQuery’s latest Data and AI capabilities are designed to transform data analysis and machine learning. These new features empower organizations to leverage advanced AI tools, streamline data processing, and enhance predictive modeling.

Beyond AI, BigQuery's Generally Available Data offers a powerful suite of tools for real-time predictions, advanced data analysis, and seamless machine learning integration. These capabilities make it easier for organizations to process and analyze large datasets with ready-to-use AI solutions.

BigQuery ML's Custom Holiday Modeling feature enhances time-series forecasting by letting users define and incorporate custom holidays into their models. This capability improves prediction accuracy by accounting for specific events not covered in standard holiday regions, enabling tailored forecasting that meets unique business needs.

The BigQuery ML Inference Engine enables real-time predictions by allowing you to run machine learning inferences directly within BigQuery. You can use imported custom models, remotely hosted models on Vertex AI, and Google’s pre-trained Cloud AI models.

This integration reduces data movement, cuts costs, and simplifies machine learning deployment using familiar SQL, all while enhancing security and scalability.

The ML.TRANSFORM function in BigQuery ML optimizes model training by preprocessing feature data based on transformations defined during the model’s training phase. This ensures consistency and accuracy in the training and prediction stages, maintaining transparency throughout the process.

BigQuery ML now supports text embeddings, allowing for advanced text analysis directly within SQL. You can generate embeddings using models like BERT, NNLM, SWIVEL, and text embedding-gecko for sentiment analysis and semantic search tasks.

These embeddings integrate seamlessly with BigQuery ML models.

BigQuery ML’s Multivariate Time Series Forecasting feature enables the prediction of complex data patterns by simultaneously modeling multiple time-dependent variables. This feature uses ARIMA_PLUS with external regressors (XREG) to enhance forecasting accuracy by incorporating additional factors. It is ideal for complex scenarios such as sales forecasting and financial planning.

BigQuery ML's new Explainable AI (XAI) capabilities bring transparency to machine learning by offering insights into how models arrive at their predictions. These tools help identify the key factors influencing outcomes, allowing users to understand better and refine their models. XAI supports compliance, improves decision-making, and ensures ethical AI practices in data-driven environments.

BigQuery's Preview Data and AI Enhancements bring cutting-edge features that significantly expand the platform's data processing and machine learning capabilities. These upcoming tools promise advanced AI integration, improved data analysis, and enhanced predictive modeling.

Gemini in BigQuery is set to revolutionize data preparation and analytics. It introduces AI-driven tools that simplify data cleansing, wrangling, and pipeline management. With natural language prompts for SQL and Python code assistance, it reimagines analytics workflows through a visual data canvas and offers performance optimization recommendations.

BigQuery's integration with DocAI using Object Tables allows for transparent machine learning. This feature extracts and organizes document insights directly within BigQuery, making it easier to manage, query, and analyze unstructured data. It ensures a seamless process while maintaining transparency and traceability in machine learning workflows.

With BigQuery’s ML.TRANSCRIBE function combined with Object Tables, you can seamlessly transcribe audio files directly within BigQuery. This feature enhances data accessibility by converting audio content into searchable text, allowing for efficient analysis and querying, all while integrating smoothly with your existing BigQuery workflows.

The integration of Vertex AI PaLM with BigQuery ML takes natural language processing to the next level. Using the ML.GENERATE_TEXT function, users can perform tasks like content generation, text summarization, rephrasing, sentiment analysis, and critical information extraction from large text datasets - all without needing complex external tools.

Vertex AI Managed Pipelines enhance BigQuery ML by automating ML workflow management. This includes online model serving, allowing real-time predictions by deploying BigQuery ML models to Vertex AI endpoints.

Additionally, robust MLOps capabilities automate model monitoring and retraining, ensuring your machine-learning processes remain accurate and efficient.

BigQuery now integrates with Colab Notebooks, enabling seamless collaboration for data analysis. This integration lets users run SQL queries, visualize results, and build machine learning models directly within Colab.

Combining BigQuery's powerful data processing with Colab's interactive environment it fosters teamwork, streamlines workflows, and enhances productivity across data teams.

The BigQuery DataFrames API allows for seamless data manipulation, enabling users to work with BigQuery data directly in Python, similar to Pandas DataFrames.

This integration simplifies data processing tasks like filtering, aggregating, and joining datasets, while maintaining BigQuery's scalability and performance – making it an ideal tool for data scientists and analysts.

BigQuery ML’s Point-in-time Lookup functions are essential for accurate historical analysis, as they enable the retrieval of data as it existed at a specific time. This capability is crucial for precise time-series forecasting. It ensures that models are trained on accurate historical data, which in turn improves the reliability and accuracy of predictions.

BigQuery's new enterprise features strengthen data security, management, and performance. They provide customized solutions for real-time updates, secure operations, and smoother workflows, helping organizations improve their data strategies on a larger scale.

BigQuery’s ready-to-use enterprise-class features are crafted to meet the complex needs of modern, data-driven organizations. These features provide tailored solutions for data management, security, and performance.

BigQuery offers three editions - Standard, Enterprise, and Enterprise Plus, each tailored to support different workloads and features at varying price points. These editions can be enabled during BigQuery capacity reservation, allowing organizations to optimize costs and performance.

With the Storage API, BigQuery’s Change Data Capture (CDC) enables real-time updates by directly capturing and replicating changes from your data sources into BigQuery. This feature ensures your data is always up-to-date, facilitating timely analysis and decision-making, while supporting seamless data integration across platforms.

BigQuery's Storage Write API Multiplexing enables efficient data streaming by allowing multiple streams to write data concurrently into BigQuery tables. This feature improves throughput and reduces latency, making it ideal for high-velocity data environments where real-time data ingestion is crucial for maintaining up-to-date analytics and supporting dynamic business needs.

BigQuery’s Query Inspector offers deep insights into query performance and execution. It helps you identify bottlenecks, optimize resource usage, and troubleshoot inefficiencies. With visualizations and comprehensive metrics, Query Inspector ensures your queries run efficiently, improving overall data processing.

BigQuery’s Table Clones feature provides a lightweight, writable copy of an existing table, known as the base table. These clones allow you to duplicate data without incurring additional storage costs unless the cloned data differs from the base table. This feature is ideal for developing, testing, and creating sandboxes, offering data manipulation and analytics flexibility while ensuring efficient storage usage.

BigQuery’s Dynamic Data Masking strengthens data security by allowing you to obscure sensitive column data for specific user groups. This feature integrates with column-level access control, enabling fine-grained control over who can view or manipulate sensitive information, ensuring that data privacy is maintained across different teams and use cases.

BigQuery's Query Queues feature optimizes data workflows by dynamically managing the number of queries that can run concurrently based on available compute resources.

Additional queries are queued until resources become available, ensuring efficient resource allocation and fair project scheduling. Users can control the maximum concurrency target within reservations, guaranteeing a minimum slot capacity for each query.

While BigQuery's Query Queues focus on optimizing query execution by managing concurrent queries based on available resources, tools like OWOX Transformation and Dataform address the challenge of data transformation itself. Both OWOX Transformation and Dataform help teams streamline the process of turning raw data into structured, meaningful datasets, but they differ in their approach.

Here is a comparison table between Dataform and OWOX Transformation

BigQuery's Physical Bytes storage billing model allows customers to reduce costs by leveraging data compression, charging based on the actual physical storage rather than logical bytes. This option offers significant cost savings while maintaining data accessibility and query performance, providing a more transparent and efficient billing method.

BigQuery's Time Travel feature allows you to configure data retention windows, enabling recovery of changed or deleted data for up to seven days. You can set the time travel window from a minimum of two to a maximum of seven days, with longer windows offering extended recovery periods and shorter windows reducing storage costs, particularly under the physical storage billing model.

BigQuery's Authorized Stored Procedures enhance data security by allowing users to execute SQL code with elevated permissions, independent of the caller's access rights. This ensures that sensitive operations are performed securely, enabling controlled access to data while maintaining strict governance and compliance standards across your organization's data workflows.

BigQuery enhances governance with features like Data Lineage, Profiles, and Data Quality. Data Lineage tracks data movement and transformations across systems, Data Profiles provide insights into data structure and content, and Data Quality tools ensure data accuracy and reliability. Together, these features enable robust data management and compliance.

BigQuery's Dataset Tag Support enhances data management by allowing users to organize and categorize datasets, tables, and views with custom tags. These tags enable easier data discovery and improved access control in large data environments, simplifying the application of consistent governance and policy enforcement across your organization.

BigQuery's Future Enterprise Solutions in Preview offers a sneak peek into advanced tools designed to elevate data management, security, and performance. These upcoming features promise to equip organizations with cutting-edge capabilities for improved data governance, auditing, and cross-region data handling.

BigQuery's Table Change History feature takes data auditing to the next level by tracking and recording changes to tables. It allows you to review historical data states, understand modifications, and ensure compliance with auditing requirements. A detailed log of alterations makes monitoring and maintaining data integrity much easier.

The Table Storage Information Schema in BigQuery provides a clear snapshot of table storage usage and materialized views. It details logical, physical, and time travel bytes, helping you monitor storage consumption, plan for future growth, and understand update patterns. This transparency is key to efficient storage management and cost optimization.

BigQuery's Cross-Region Table Copy feature enables smooth data movement between regions, allowing you to replicate tables across different geographic locations. This capability ensures data availability, supports disaster recovery, and enhances data accessibility for global teams, making it easier to manage and safeguard your data in a distributed environment.

The Cross-Region Dataset Replication in BigQuery enhances data protection by creating a secondary replica of your dataset in a specified region. This replica is updated asynchronously, ensuring redundancy across multiple zones and regions, offering an additional layer of security.

BigQuery’s DML (Data Manipulation Language) now supports modifying recently streamed data, providing greater flexibility in managing your datasets. This feature lets you update or delete rows streamed into BigQuery within the last seven days, enabling timely corrections and adjustments that enhance accuracy and operational efficiency.

BigQuery’s Partitioning and Clustering Recommender improves data organization by analyzing workload execution over 30 days to identify suboptimal table scans. Using machine learning, it estimates potential performance improvements and cost savings, offering tailored recommendations for partitioning and clustering based on your specific read-and-write patterns.

BigQuery’s Custom Data Masking Functions provide tailored data protection by allowing user-defined functions (UDFs) to mask sensitive data in columns. Supporting most data types, this feature offers flexibility in how data is masked while ensuring security.

These custom routines are ideal for implementing specialized data protection strategies that meet your organization’s unique needs.

BigQuery’s Differential Privacy feature boosts data security by adding controlled noise to query results, safeguarding individual data points from being identified. This ensures that sensitive information remains confidential while allowing meaningful aggregate analysis, making it essential to maintain privacy in datasets containing personally identifiable information (PII).

BigQuery’s Shared Encryption Functions, in collaboration with Cloud Sensitive Data Protection (SDP), offer unified security by enabling the encryption and decryption of sensitive data directly within queries. This feature simplifies the management of encrypted data, ensuring it remains secure during processing without compromising performance.

BigQuery’s IAM Conditional Policies offer precise control over who can access what and when. Permissions are granted based on specific conditions, like time constraints or operational contexts. This enhances security by allowing suitable access rules for projects, datasets, tables, and other resources, ensuring users have access exactly when needed.

BigQuery’s IAM Deny Policies strengthen security by explicitly blocking access to specific actions or resources, regardless of other permissions that may have been granted. This feature allows organizations to enforce stricter security measures, ensuring critical resources are protected by preventing unauthorized actions, even if users have broad permissions in other system areas.

BigQuery’s latest updates introduce powerful new migrations and data pipeline features designed to simplify cross-platform data integration and streamline data transformations. These capabilities enhance the efficiency of data transfers, support seamless migration from other platforms, and optimize pipeline management.

BigQuery offers robust tools designed to make data transformation and integration more seamless. These tools are essential for efficient data ingestion, streamlined pipeline management, and smoother platform transitions.

Dataform is a powerful tool that simplifies data transformation within the ELT (Extract, Load, Transform) process by enabling declarative SQL workflows in BigQuery. It supports version control, automated quality testing, and Git integration, which helps you manage dependencies and schedule executions efficiently.

BigQuery facilitates secure data ingestion from Azure Blob Storage, enabling seamless platform integration. By utilizing Azure federated identity, administrators can grant a Google service account access to Azure applications, ensuring secure and efficient data access.

This feature significantly enhances the ability of data analysts to query and manage data across different cloud environments.

BigQuery’s Interactive SQL Translator has advanced debugging tools that make migrations smoother by translating legacy SQL code into BigQuery SQL. It offers step-by-step guidance, helping you identify and resolve potential issues, simplifying the migration process, and reducing errors.

BigQuery is rolling out preview features to enhance data migrations and pipelines. It offers advanced tools that improve media integration, streamline advertising analytics, and support scalable storage solutions.

The Display & Video 360 Transfers feature in BigQuery automates the integration of campaign data from Display & Video 360 into BigQuery. This seamless process makes it easier to analyze advertising performance, offering deeper insights and enabling more effective optimization of digital media campaigns.

BigQuery’s Search Ads 360 Transfers feature simplifies the integration of search campaign data from Search Ads 360 into BigQuery. Automating this process allows for a more comprehensive analysis of search ad performance, providing deeper insights and enabling the fine-tuning of advertising strategies directly within BigQuery.

With the Export to Bigtable feature, BigQuery makes it easy to move large datasets from BigQuery to Bigtable. This capability supports scalable storage solutions, making it ideal for managing high-throughput, low-latency workloads where fast access to large volumes of structured data is crucial across multiple applications.

BigQuery’s Migration Assessment for Apache Hive helps you plan and evaluate the transition of your existing Hive data warehouse to BigQuery. This tool generates a detailed report that assesses storage costs, identifies cost-saving opportunities, and provides a comprehensive migration plan, ensuring a smooth and efficient migration of your workloads to BigQuery.

Similarly, BigQuery’s Migration Assessment for Snowflake assists in planning and executing the migration of your Snowflake data warehouse to BigQuery. The assessment produces reports estimating storage costs, identifying potential savings, and outlining a detailed migration plan, facilitating a seamless and efficient transfer of your data workloads to BigQuery.

The OWOX Reports Extension for Google Sheets is a handy tool that integrates BigQuery data into your spreadsheets, making data analysis more accessible and efficient. With this extension, data analysts can pull data directly from BigQuery into Google Sheets, allowing for real-time analysis, reporting, and visualization - all without the need to switch between platforms.

The OWOX Reports Extension also facilitates collaboration by allowing multiple team members to work on the same Google Sheet simultaneously, boosting transparency and teamwork. Its intuitive interface lets users easily manipulate and analyze BigQuery data, even if they are unfamiliar with SQL.