SQL has been the cornerstone of data management for over 50 years, valued for its declarative nature and robust ecosystem. However, traditional SQL has limitations, including rigid clause structures, verbose syntax, and complex nested queries that can hinder readability and maintenance. To address these challenges, Google introduced pipe syntax, an extension to GoogleSQL that reimagines how queries are written and processed. A pipe-structured data flow makes SQL more easier to read and write than ever before.
As we discussed in a previous blog post, pipe syntax introduces a linear, top-down approach to SQL queries, using the pipe operator (|>) to chain operations like filtering, aggregating, and joining in a logical sequence. This structure aligns with the natural flow of data transformations, making queries more intuitive, readable, and maintainable. Unlike standard SQL, which enforces a strict order (SELECT, FROM, WHERE, etc.), pipe syntax allows operations to be applied in any order, reducing the need for cumbersome subqueries or Common Table Expressions (CTEs).
Since pipe syntax became generally available in April, we have seen you embrace it across a myriad of use cases — from streamlining data transformations, building insightful reports to efficiently analyzing logs, and many more. In this blog, we are highlighting three different use cases from customers about how they are using pipe syntax to simplify data transformations and log data analysis.
- aside_block
- <ListValue: [StructValue([(‘title’, ‘$300 in free credit to try Google Cloud data analytics’), (‘body’, <wagtail.rich_text.RichText object at 0x3e5ddc7b83d0>), (‘btn_text’, ‘Start building for free’), (‘href’, ‘http://console.cloud.google.com/freetrial?redirectPath=/bigquery/’), (‘image’, None)])]>
1.Supercharging data analysis with a linear data flow
Imagine you want to build a recommendation report to suggest whether a logical or physical pricing model is more cost effective for long-term data storage in BigQuery. While physical storage is generally cheaper, costs can vary based on factors like data compressibility.
You might write a query like this using standard SQL:
- code_block
- <ListValue: [StructValue([(‘code’, “WITH StorageData AS (rn SELECTrn project_id,rn table_schema,rn logical_gb * logical_unit_price AS logical_price,rn physical_gb * physical_unit_price AS physical_pricern FROM `<project_id>.region.INFORMATION_SCHEMA.TABLE_STORAGE`rn WHERE table_type = ‘BASE TABLE’rn)rnSELECTrn project_id,rn table_schema AS dataset_name,rn IF(rn SUM(physical_price) < SUM(logical_price),’physical’,’logical’rn ) AS best_pricing_modelrnFROM StorageDatarnGROUP BY project_id, table_schema;”), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3e5dda6f94f0>)])]>
Using pipe syntax, the query can be written as:
- code_block
- <ListValue: [StructValue([(‘code’, “FROM `<project_id>`.`region`.`INFORMATION_SCHEMA.TABLE_STORAGE`rn|> WHERE table_type = ‘BASE TABLE’rn|> EXTEND logical_gb * logical_unit_price AS logical_price,rn|> EXTEND physical_gb * physical_unit_price AS physical_price,rn|> AGGREGATE SUM(logical_price) AS total_logical_price, SUM(physical_price) AS total_physical_price,rn GROUP BY project_id,table_schema,rn|> EXTEND IF(total_physical_price < total_logical_price, ‘physical’, ‘logical’) AS best_pricing_model”), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3e5daeb26b80>)])]>
Pipe syntax eliminates the need for CTEs, allowing users to chain operations linearly. This reduces the query’s complexity and makes it easier to follow, especially for cost analysis tasks requiring multiple transformations. Now you can build a report faster, and share the queries across teams that are easier for them to understand.
2. Simplifying data transformations in data pipelines
Suppose you are building a data pipeline for a dashboard. You want to process customer order data to calculate total revenue per customer, filter high-value customers, and then rank them.
In standard SQL:
- code_block
- <ListValue: [StructValue([(‘code’, ‘WITH aggregated_revenue AS (rn SELECT rn customer_id,rn SUM(order_amount) AS total_revenuern FROM sales.ordersrn GROUP BY customer_idrn),rnfiltered_revenue AS (rn SELECT *rn FROM aggregated_revenuern WHERE total_revenue > 1000rn),rnranked_revenue AS (rn SELECT rn customer_id,rn total_revenue,rn RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rankrn FROM filtered_revenuern)rnSELECT *rnFROM ranked_revenuernORDER BY revenue_rank;’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3e5daeb26fa0>)])]>
Using pipe syntax, the query can be written as:
- code_block
- <ListValue: [StructValue([(‘code’, ‘FROM sales.ordersrn|> AGGREGATE SUM(order_amount) AS total_revenue GROUP BY customer_idrn|> WHERE total_revenue > 1000rn|> EXTENDrn RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rankrnORDER BY revenue_rank;’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3e5daeb26580>)])]>
Building a data pipeline is a multi-step process involving data cleaning, transformation and aggregation. Pipe syntax simplifies developers’ workflows by allowing them to chain operations like aggregation and filtering in a logical and sequential way. The simplicity of the code allows developers to focus more time on solving business problems instead of writing and debugging cumbersome SQL code. Fewer lines of code and higher productivity eventually translate to faster business outcomes.
3. Simpler log analysis in Google Cloud Logging
Log Analytics in Google Cloud Logging is a powerful tool for storing, searching, analyzing, and monitoring application and system log data through SQL. When you need to perform complex analysis, aggregations, or transformations on their logs, the Log Analytics feature gives you the full strength of SQL.
When helping enterprises to migrate their workloads and logging tooling to Cloud Logging, translating pipe-like structure into standard SQL queries for typical log analysis tasks can sometimes feel cumbersome; it involves subqueries and common table expressions (CTEs) that might not feel immediately intuitive. Pipe syntax offers a more linear, pipe-based syntax, often making common log analysis patterns easier to read and write.
Let’s look at a concrete example.
The challenge: Analyzing log frequency by time, severity, and resource attribute
Imagine you want to analyze your logs to find patterns where specific log messages (identified by severity and tags) occur frequently (more than 5 times per minute), excluding certain messages (like those containing “CookieIncluded=False”).
Using standard SQL in Log Analytics, you might write a query like this:
- code_block
- <ListValue: [StructValue([(‘code’, ‘SELECTrn timestamp_trunc(timestamp, MINUTE) AS minute,rn severity,rn JSON_VALUE(labels) AS tags,rn count(*) as total_cntrnFROM `your-test-project.region._Default._Default`rnWHERE NOT SEARCH(json_payload.message, “CookieIncluded\\=False”)rnGROUP BY 1,2,3rnHAVING total_cnt > 5’), (‘language’, ‘lang-sql’), (‘caption’, <wagtail.rich_text.RichText object at 0x3e5daeb26340>)])]>
Using pipe syntax, the query can be written as:
- code_block
- <ListValue: [StructValue([(‘code’, ‘FROM `your-test-project.region._Default._Default`rn|> WHERE NOT SEARCH(json_payload.message, “CookieIncluded\\=False”)rn|> AGGREGATE COUNT(*) AS total_cntrnGROUP BYrn TIMESTAMP_TRUNC(timestamp, MINUTE) AS minute,rn severity,rn JSON_VALUE(labels) AS tags rn|> WHERE total_cnt > 5’), (‘language’, ‘lang-sql’), (‘caption’, <wagtail.rich_text.RichText object at 0x3e5daeb261f0>)])]>
Beyond looking different, pipe syntax fundamentally changes how you build and read queries for sequential log processing. It tackles two common pain points of standard SQL: nested subqueries and “inside-out” logic.
PipeSQL eliminates this nesting for common sequential tasks. Each pipe (|>) feeds the previous command’s output into the next. Aggregation results from the summary are immediately available for filtering in the subsequent WHERE clause without needing an explicit nested structure. The query remains flat and linear. By tackling nesting and promoting a linear flow, pipe syntax significantly lowers the cognitive load for many common log analysis tasks, making powerful analytics more accessible.
Customer love for pipe syntax
“I was very excited when BigQuery first introduced pipe syntax. As someone who is familiar with R, it is very natural for myself and our team of analysts to use pipe-like syntax for more streamlined data transformation via Dataform. I am happy to share that I have seen >30% reduction in my code compared to standard SQL. This eventually transferred to time saving to read, write and debug queries. I am looking forward to onboarding more analysts to use pipe syntax across my organization.” – Shanker Venkatachalam, Senior Manager, Business Strategy, Northwell.edu
“The pipe syntax has proven to be a revelation in my analytical work. Although I initially had concerns about the learning curve, it was surprisingly smooth. The linear flow facilitates step-by-step query construction, ensuring each transformation is correct before moving on. This reduces complexity and improves long-term maintainability. Notably, it also naturally encourages a ‘micro-transformations’ mindset, resulting in significantly more organized and understandable queries.”– Axel Thevenot, Google Developer Expert, Venom Engineering
“At Bindplane, we specialize in accelerating migrations to Cloud Observability with our telemetry pipeline built on OpenTelemetry. Once the data arrives, using pipe syntax makes it much easier to build key observability assets like dashboards, alerts, and queries. Once pipe syntax is available, I’m done writing regular SQL. It’s just so much easier for helping enterprises get up and running with Cloud Logging/Log Analytics.” – Keith Schmitt, Senior Software Engineer, Bindplane
What’s next for pipe syntax?
As shared at Cloud Next, BigQuery supports Gemini-powered Data Preparation Agents, which can analyze your data and auto-generate data pipelines to clean and transform your data. You will soon be able to use pipe queries to validate generated queries, which simplifies your pipelines and makes it easier to review and verify code.
In short, pipe syntax is more than a syntactic upgrade. It’s a paradigm shift that makes SQL more accessible and productive for analysts, engineers, and data teams. Its modular, pipeline-based approach aligns with modern data workflows, saving development time and improving efficiency. Whether you’re building reports, developing data pipelines, or analyzing logs, pipe syntax delivers shorter, clearer, and more efficient queries, empowering you to focus on insights rather than syntax.
Ready to simplify your SQL workflows? Learn how to use pipe syntax from this tutorial, and check out the following resources:
for the details.