Faster Data Cleansing with BigQuery Pipe Syntax

🚀 Faster Data Cleansing with BigQuery Pipe Syntax 🚀
I’ve been experimenting with BigQuery Pipe Syntax for a while now, and it’s quickly becoming one of my go-to tools for certain tasks. It’s particularly useful for workflows that follow a pipeline flow, where you’re applying transformations to your data step by step-think everyday data cleansing tasks.
Additionally, the simpler syntax makes it much easier to visualise SQL workflows, transforming something traditionally difficult to follow or interpret into a more intuitive and understandable process.
Here’s what we’ll cover in the video:
⏱️ 01:00 - Introduction to BigQuery Pipe Syntax
📋 02:30 - Tackling a Data Cleansing Task
🤔 03:45 - Challenges with Traditional SQL Syntax
💡 05:34 - Why Pipe Syntax is a Game-Changer
🎨 08:02 - How Patterns Enhance Visualisation
💻 10:22 - Demo: SQL vs Pipe Syntax
🤖 13:14 - Demo: Using LLMs to Create Visualisations
📂 18:33 - Other Use Cases
📌 21:37 - Summary, Key Takeaways
If you’ve been curious about making your SQL workflows faster and cleaner, this video is for you. Let me know if you’ve tried Pipe Syntax and how it’s helped with your data tasks!
#bigquery #pipesyntax #dataengineering #dataanalytics #datacleaning #sql #practicalgcp
----
Need to modernise your data stack? I specialise in Google Cloud solutions, including migrating your analytics workloads into BigQuery, optimising performance, and tailoring solutions to fit your business needs. With deep expertise in the Google Cloud ecosystem, I’ll help you unlock the full potential of your data. Curious about my work? Check out www.fundamenta... to see the impact I’ve made. Let’s chat! Book a call at calendly.com/r... or email [e-mail]. 🚀📊
Loved this video? I made it using VEED-an amazing online video editing tool that also has built-in recording features (which most others don’t!). It’s super easy to use, and I highly recommend giving it a try. Want to edit like a pro? Click here for an exclusive 50% off discount veed.cello.so/... and start creating today! 🎥✨

[@ChandraSekar-uk7bz]

Excellent video! Thanks for sharing. Please keep up the good work.

[@practicalgcp2780]

Thank you very much! ❤️

[@ayushmandloi1580]

Excellent video

[@practicalgcp2780]

Glad you liked it❤️

[@mileswoolgar]

Thanks for the video. How could you use this with nested GA4 data?

[@practicalgcp2780]

No worries :) on nested data, this doesn't make much of a difference, the only difference is the shorter syntax, you can still access nested using cross join like before. The following is just a dummy example, not the full set, but it is the same, FROM `bigquery_examples.ga4_dummy` -- Flatten user_properties array |> CROSS JOIN UNNEST(user_properties) AS user_properties |> AGGREGATE MAX(CASE WHEN user_properties.key = 'user_type' THEN user_properties.value.string_value END) AS user_type GROUP BY user_pseudo_id And in the results, you'll get something like this, [{ "user_pseudo_id": "1234567890abcdef", "user_type": "premium" }, { "user_pseudo_id": "abcdef1234567890", "user_type": "regular" }, { "user_pseudo_id": "a1b2c3d4e5f6g7h8", "user_type": "guest" }]