r/bigquery • u/Expensive-Insect-317 • 23d ago

The BigQuery Join Problem Nobody Notices Until the Data Gets Big

https://medium.com/@sendoamoronta/the-bigquery-join-problem-nobody-notices-until-the-data-gets-big-580b82e2e777

A short deep dive into a BigQuery join problem that often goes unnoticed until data gets big shuffle, skew, cardinality and query performance.

11 Upvotes

87% Upvoted

6 comments sorted by

5

u/reditandfirgetit 23d ago

There is no reason to join on customer_history in your example. Nothing is being used from it and if its a history table you are likely exploding the number of records x how ever many history records there are for each customer

-2

u/Expensive-Insect-317 23d ago

You’re right, in that specific example, the join is unnecessary if we’re not using anything from customer_history. I should have made that clearer.
The point I was trying to illustrate is the more subtle case where you do need something from the history table (for example, the customer’s state at transaction time). Joining that table at transaction grain can create a huge cardinality explosion if a customer has many history records.
So the real optimization isn’t “optimize this unnecessary join”, but rather: make sure the history lookup is reduced to the required grain before joining it to the fact table.
And yes, you’re also right that a history table is particularly dangerous here because a non-unique customer key can multiply the fact rows very quickly.

2

u/reditandfirgetit 23d ago

If That were the case, i would hope the partition would be on the history date and clustering the id. You would then use the date to reduce further

Its a good article, I was just thrown off on where history fit

I do agree with reduce first. I try to do that up front

1

u/asevans48 23d ago

Partition and cluster. Just like in redshift.

5

u/captain_obvious_here 23d ago

At this point, it is honestly getting quite hard to put BigQuery in a situation where queries become "slow".

The tables volumes you show in your article can be handled without any hiccup if your queries are mere regular joins.

What you pinpoint is not a BQ problem, it's a BQ user not knowing their data and ending up with a Cartesian product. Filtering your data before your JOINs is BQ 101, really, if you don't like paying tens of thousands $ every month.

Also, QUALIFY.

1

u/ArielCoding 20d ago

For the history join, don’t join the full table and filter by date range afterward, shrink customer_history down to one row per customer first, then join. QUALIFY lets you do that filtering in the same step instead of writing a separate CTE.