BigQuery updates for analysts: what changed in 2025 and 2026

The table used in every example

Paste this before any query below and it runs as is:

WITH orders AS (
  SELECT 1 AS order_id, DATE '2026-10-01' AS order_date, 'anna' AS customer,
         'web' AS channel, 'paid' AS status, 120 AS amount
  UNION ALL SELECT 2, DATE '2026-10-01', 'ben',  'ads',   'refunded', 200
  UNION ALL SELECT 3, DATE '2026-10-02', 'anna', 'email', 'paid', 80
  UNION ALL SELECT 4, DATE '2026-10-02', 'cara', 'web',   'paid', 30
  UNION ALL SELECT 5, DATE '2026-10-03', 'ben',  'ads',   'paid', 50
  UNION ALL SELECT 6, DATE '2026-10-03', 'cara', 'web',   'paid', 90
)

Six orders over three days, three customers, one refund. Gross revenue is 570, paid revenue is 370.

Pipe syntax: a query that reads in the order it runs

Generally available since 1 April 2025. Each |> step takes the table from the step above, so the query reads in the order BigQuery evaluates it: filter, then aggregate, then add a column, then sort.

FROM orders
|> WHERE status = 'paid'
|> AGGREGATE SUM(amount) AS revenue, COUNT(*) AS paid_orders
   GROUP BY channel
|> EXTEND ROUND(revenue / paid_orders, 1) AS avg_order
|> ORDER BY revenue DESC

The refunded 200 is gone before the sum, and EXTEND adds a column that uses an alias from the step before, which a single standard SELECT cannot do by default. A WITH clause in front works, as above; a WITH pipe operator and named windows followed on 17 July 2025, and a DISTINCT pipe operator on 21 July 2025.

The trap: AGGREGATE puts the grouping columns first in the output, whatever order you wrote. And pipe syntax mixes with standard SQL in the same project, so a team needs one rule for which style a shared query uses, or reviews get harder, not easier.

GROUP BY ALL

Generally available since 13 May 2025, in the same release note as GROUP BY on STRUCT and ARRAY columns with SELECT DISTINCT.

SELECT order_date, status, SUM(amount) AS revenue
FROM orders
GROUP BY ALL
ORDER BY order_date, status

GROUP BY ALL groups by every column in the SELECT list that is not an aggregate.

The trap: the grain follows the SELECT list. Adding status to see it split 1 October into two rows; a report that expected one row per day now double-counts days. With an explicit GROUP BY order_date, the same edit fails with an error instead.

UNION ALL BY NAME

Generally available since 3 February 2025, along with CORRESPONDING. A plain UNION ALL matches columns by position, and if two columns have the same type, a swap goes through silently:

SELECT order_id, channel, status FROM orders WHERE order_id = 1
UNION ALL
SELECT order_id, status, channel FROM orders WHERE order_id = 2

Order 2 now has the channel “refunded”. Write UNION ALL BY NAME and the same two SELECTs line up by name:

Use it whenever you stack exports from different sources. More on set operations in the JOIN and UNION guide.

ARRAY_FIRST, ARRAY_LAST, WITH expressions and chained calls

ARRAY_FIRST, ARRAY_LAST and ARRAY_SLICE became GA on 25 September 2025. WITH expressions, which define local variables inside one expression, and chained function calls became GA on 11 August 2025.

SELECT
  customer,
  ARRAY_FIRST(ARRAY_AGG(channel ORDER BY order_id)) AS first_touch,
  ARRAY_LAST(ARRAY_AGG(channel ORDER BY order_id))  AS last_touch,
  WITH(paid AS SUM(IF(status = 'paid', amount, 0)),
       total AS SUM(amount),
       ROUND(paid / total, 2)) AS paid_share,
  (customer).UPPER().LEFT(1) AS initial
FROM orders
GROUP BY customer
ORDER BY customer

First and last channel per customer used to need [OFFSET(0)] and [ORDINAL(ARRAY_LENGTH(...))]. Ben paid 50 of his 250, so his paid share is 0.2; the WITH expression names the two sums once instead of writing them twice.

The trap: a chained call on a column needs parentheses. customer.UPPER() fails with “Chained function calls require parentheses on input paths”; (customer).UPPER() runs. And ARRAY_FIRST on an ARRAY_AGG without ORDER BY returns whichever element came first, which is not defined. Array functions in general are in the array functions guide.

MATCH_RECOGNIZE: patterns across rows

Generally available since 5 November 2025. It finds sequences of rows that match a pattern, per partition. Here: any order followed by a smaller one from the same customer.

SELECT *
FROM orders
MATCH_RECOGNIZE (
  PARTITION BY customer
  ORDER BY order_id
  MEASURES
    FIRST(amount) AS first_amount,
    LAST(amount)  AS next_amount
  PATTERN (any_order smaller)
  DEFINE
    any_order AS TRUE,
    smaller AS amount < PREV(amount)
)
ORDER BY customer

Cara is not there: her second order (90) was larger than her first (30). The same question with window functions takes a LAG, a subquery and a filter; see window functions.

The trap: every pattern variable must appear in DEFINE. Leave out any_order AS TRUE and BigQuery stops with “Pattern variable any_order is not defined in the DEFINE clause”, where some other warehouses treat an undefined variable as always true. SQL ported from them needs that line added.

Filters inside aggregates (Preview)

Added on 14 September 2026, in Preview. A WHERE inside the call filters the rows that one aggregate sees:

SELECT
  SUM(amount) AS gross,
  SUM(amount WHERE status = 'paid') AS paid,
  COUNT(* WHERE status = 'refunded') AS refunds
FROM orders

It replaces SUM(IF(status = 'paid', amount, 0)) and COUNTIF, and it reads like the definition someone asked for. It ran in our project without enrolment, but Preview features can change before GA, so keep it out of a report others depend on until then. The GA aggregates are in BigQuery aggregate functions.

Multi-level aggregation (Preview)

Added on 8 July 2026, in Preview. An aggregate can take another aggregate as its argument, with its own GROUP BY:

SELECT
  channel,
  SUM(amount) AS revenue,
  AVG(SUM(amount) GROUP BY order_date) AS avg_daily_revenue
FROM orders
GROUP BY channel
ORDER BY channel

The trap: the inner GROUP BY only sees days on which the channel had an order. Email sold once, so its “average daily revenue” is 80, not 80 over three days. The query is correct; the column name is the lie. Name it avg_revenue_per_active_day, or join to a calendar first.

Reusable column aliases (GA, opt-in)

Announced on 15 October 2026 as generally available and opt-in. Once on, an alias can be used later in the same SELECT list or in the WHERE clause, as in Snowflake:

SELECT 120 * 0.2 AS tax, tax + 120 AS total

In our project, where nobody switched it on, that query fails with “Unrecognized name: tax”, which confirms the default. An administrator enables it per region by setting reusable_column_aliases_mode to on with ALTER PROJECT or ALTER ORGANIZATION (Google’s column aliases page, read in October 2026).

The trap is in that page: turning it on changes name resolution. A table column still wins over an alias, but an alias now wins over a column of the outer query, a function argument or a script variable, so an existing correlated subquery or script can return different results after the switch. The page also says the aliases do not work in pipe syntax.

AI functions in SQL

AI.IF, AI.SCORE and AI.CLASSIFY became generally available on 13 April 2026. Since 22 January 2026 they can run on your own user credentials, without creating a BigQuery connection.

SELECT
  review,
  AI.CLASSIFY(review, categories => ['delivery', 'price', 'product quality']) AS topic
FROM UNNEST([
  'The parcel arrived four days late',
  'Too expensive for what you get',
  'The zip broke after a week'
]) AS review
ORDER BY review

Three correct labels, no model to train. Two things from Google’s AI.CLASSIFY reference (read in October 2026): each call is billed by Google’s AI platform on top of the BigQuery query, and when a model call fails the function returns NULL for that row; only when failed rows pass the max_error_ratio threshold does the whole query fail, and that threshold defaults to 1.0, so by default a query with failed rows still finishes. Count the NULLs before you report a share.

Also in this group: AI.KEY_DRIVERS, GA on 29 September 2026, finds the segments behind a change in a summable metric; and AI.FORECAST gained the TimesFM 3.0 model with multivariate forecasting on 7 October 2026, in Preview. Model-based work beyond these is in BigQuery ML, and the wider picture of asking BigQuery questions in plain language is in AI analytics on BigQuery.

Global queries (Preview)

Added on 17 February 2026, in Preview. One query can read datasets in more than one region, for example an EU dataset joined with a US one. It is not on by default: the project that runs the query needs enable_global_queries_execution, the project that holds the data needs enable_global_queries_data_access, and the user needs the bigquery.jobs.createGlobalQuery permission, which among predefined roles only BigQuery Admin has.

What it costs, from Google’s global queries page (read in October 2026): compute in each remote region, compute for the final query, the copy between regions at data replication prices, and storage of the copied data for 24 hours. If the copy succeeds and the query then fails, the copy is still billed. We did not run one: our test data sits in one region.

Changes that affect cost and safety

• A new daily query limit (8 October 2025). For on-demand pricing, the default QueryUsagePerDay limit for new projects is 200 TiB; existing projects got a limit based on their last 30 days. A heavy month-end run can now hit a quota that did not exist before. Pricing itself is in the BigQuery pricing guide.
• History-based optimizations on by default (29 September 2025). BigQuery reuses what it learned from earlier runs of similar queries. Timings of a repeated query can change with no change to the SQL.
• Optional job creation mode, GA (27 May 2025). Small dashboard-style queries can run without a full job, with caching, for lower latency.
• GoogleSQL is the default dialect for the CLI and API since 1 August 2025. A script that relied on legacy SQL now needs to ask for it explicitly.
• Undelete a dataset, GA (23 February 2026). A deleted dataset can be recovered while it is inside its time travel window. Table-level recovery is in the time travel guide.

Fail-safe: the old line on this page was wrong

This page used to say of fail-safe storage: “There will be no billing for this period until July 17, 2023.” That date is long gone, and the rule is not one date for everyone. Google’s time travel page (read in October 2026) says it depends on the dataset’s storage billing model:

• Logical storage billing: time travel and fail-safe storage are included in the base rate.
• Physical storage billing: they are charged at the active physical storage rate.

Fail-safe lasts seven days after the time travel window ends, cannot be configured, and cannot be queried. Recovery from it needs Cloud Customer Care to grant temporary access first. The time travel window itself is seven days by default and can be set between two and seven. Recovery steps are in recover deleted data in BigQuery.

Names that changed

On 22 April 2026 Looker Studio was renamed Data Studio, at datastudio.google.com; existing reports redirect and keep working. In spring 2025 Analytics Hub was renamed BigQuery sharing. Update the old names in your runbooks.

Following updates yourself

The release notes page is the source, and everything above links back to it by date. For BigQuery news on the day it ships, subscribe to its feed (https://docs.cloud.google.com/feeds/bigquery-release-notes.xml) in any RSS reader. Read each item for one word first: “Preview” or “generally available”. Preview features can change and have limited support, so a query that a stakeholder’s report depends on should use GA features only. Then test on a table you can check by hand, like the six rows above, before you trust the new feature on production data.

Where this meets reporting

New syntax makes a query shorter. It does not make two analysts’ queries agree. When one report uses GROUP BY ALL and another an explicit GROUP BY, or one adopts SUM(amount WHERE status = 'paid') while another still sums everything, the same metric drifts between dashboards.

In OWOX Data Marts the query that defines a metric is published once as a Data Mart, in whichever syntax you prefer, and every report reads that one data model: in Google Sheets, in Data Studio dashboards, in Excel or in an AI assistant. OWOX Data Marts runs your SQL as written, in your own BigQuery project, and does not transform the data, so pipe syntax, MATCH_RECOGNIZE and Preview features behave exactly as they do in the console.

Google BigQueryBigQuery for Marketing Analytics: What Changed in 2026September 24, 2026→Google BigQueryIs BigQuery a Data Warehouse or a Data Lake? A Marketing GuideOctober 6, 2026→Google BigQueryGoogle Search Console to BigQuery: Bulk Export Setup and SQLOctober 6, 2026→

See all articles →