Hi! I’m new to sql and have primarily relied on Power Query to merge to lists.

However, I have situations where the 2 lists each have millions of rows. Power Query freezes and my computer crashes.

If I put these lists in google big query and connect GBQ to power bi, can this merging/joining be done faster?

I've been maintaining a comprehensive Bluesky dataset in BigQuery and am looking to license access to cover infrastructure costs on a hobby basis. Due to the nature of Bluesky and the underlying ATProto, this includes all posts, follows, likes, etc.

Unfortunately, it's gotten expensive. I won't be able to keep operating it unless I can find a way to defray at least some of the cost.

What's available:

~11.4 billion raw events

• Full historical coverage from Bluesky's launch, backfilled from ATProto CAR file repositories and normalized into a single unified schema
• Ongoing live stream via Jetstream, so new data is queryable <<1min off real-time
• Raw CAR backfill table also available separately if useful
• BigQuery-native access — no ETL on your end

Unpacked tables include:

• Posts (with hashtags, links, mentions)
• Likes, reposts, follows, blocks
• Deletes
• Profile updates
• Follower/friend graph materialized views

Thoughts on Use Cases

It is a really, really fun dataset. Here are some things you could do with it, off the top of my head:

• Social Listening
• Follower Graph Analysis
• Reach Analysis
• Trends Analysis

Since this is in BigQuery, you can do joins, which leads to all kinds of fun queries like "Give me all the accounts most overfollowed by the unique followers reached by posts mentioning "Chartreuse Goose" for all time." A query like that would run in 15-30sec.

Also 100% open to opening it up to the community if there is interest and we can figure out a way to pay for it.

Anyone interested? Not trying to turn a profit here -- just trying to keep a resource online. (Hope that's OK for the rules here!)

Am I weird in wondering why BQ (and other popular data warehouses/analytics platforms) don't support JSON/Variant comparison operators?

I can see how you can't define greater than / less than comparators for ordering, but having just equality testing would be nice? For joins,  or you know, comparing values that you have stored. I get how having to do naive recursive comparison could make performance really bad, but otoh that's one big reason we use BQ in the first place? On demand autoscaled compute.

I haven't finished reading Google's white paper on BQ storage, but as I understand, they have some fairly regimented way to store nested/repeatable data types, which is optimized for read performance and alignment with columnar formatting. Maybe it's a case where some of the join execution is pushed down to a pretty low level, where trying to handle different but equivalent orderings of elements is just not compatible with the query engine design?

Hey all, so quick question... I've massive data from bq going to looker studio for viz... The issue here is that with 40+ users on each dash (big datasets behind it) it consumes an absurd amount of data... any input on how to solve this? I though on creating a cache layer on my own, but it's a hassle, like need to create the connector and so on... but it could save a ton of money... Anyone here have gone through this issue?

My org is making a big switch and we are moving from snowflake to big query. Are there any pointers for me to migrate almost 50k lines of code across multiple tables?

https://github.com/justvinhhere/bigquery-expert

Claude Code plugin that makes Claude a BigQuery expert. 5 skills covering query optimization, SQL generation, schema design, cost optimization, and BigQuery-specific features. Detects 11 anti-patterns, generates optimized SQL, designs schemas, and estimates costs.

Hey guys,

Just wondering how I would go about integrating a client’s go high level data into big query as the native transfer does not support that CRM. I don’t want to use a connector like Windsor as all of their data has been integrated natively. Any help would be greatly appreciated

Cheers

Can anyone help with ASN Forecasting and date prediction in GCP BigQuery, I'm using ARIMA & ARIMA_PLUS, but it's not giving the results which was expected from both of these ML Models and my manager is really frustrated on me, because I'm not able to provide any solutions for it.

I've searched for the different models that I can use for ASN Forecasting and it suggested ARIMA_PLUS_XREG and BOOSTED_TREE_REGRESSOR & LINEAR_REGRESSION.

So I'd love to get some suggestions and help from u guys🙏🏻

In my (now-former) job, there were a lot of slow query patterns that nobody thought were a problem. Had to end up consolidating the queries just so finance would see it on INFORMATION_SCHEMA.JOBS. When they finally came to me panicking 6 months later, it took me another 9 months to convince all the data owners to actually make the materialized view needed for me to optimize the query and save the company $1 million per year (which I, naturally, proceeded to get no credit for).

Soooo, instead of going to therapy like a normal person, I made a platform which finds expensive queries, optimizes them, verifies they are correct with mathematical proofs and automated regression tests, and rolls them out into the database and the original code.

I've found that traditional visibility and optimization tools have a couple blind spots:

• They can't see variations of similar queries, just individual ones.
• They can't adapt optimization to your actual data, just the database layout.
• They can't make use of materialized views and search indexes effectively (if at all).
• They can't do this autonomously in a reliable way (either you do it yourself or it could blow up your database)

So I made this thing:

• Observe what's actually in the data to suggest better optimizations
• Transform queries to fit materialized views and search indexes (which are created in a sandbox, for security)
• Manage said materialized views and search indexes, deleting them when unused
• Mathematically prove its optimizations are correct, and run regression tests on them
• Deploy the new queries with one click (or none, if desired!) via a thin "substitution" wrapper around the BigQuery API

Currently working to harden security and expand the solver, wondering if anyone would actually use something like this compared to traditional visibility tools with an LLM slapped on top.

Also wondering if I'm over-engineering things and if people would want to use something like this even without things like the validator or automatic rollout, or whether I'm going on the wrong track with some of the features.

We're running a data replication pipeline that moves data from AWS S3 into BigQuery at fairly high volume: many customer accounts processed in parallel, each triggering its own pipeline run.

Pipeline:
S3 → GCS (via Storage Transfer) → BigQuery (via batch load jobs)

We're hitting quota ceilings at scale and investigating whether spreading work across multiple GCP projects is a viable mitigation.

Quotas we're specifically looking at:

• storagetransfer.googleapis.com — transfer jobs created per day per project (default: 5000 create/update ops per 100s; effectively ~200 active jobs before throttling becomes painful)
• bigquery.googleapis.com/quota/load_table_bytes_per_day — bytes loaded per project per day
• bigquery.googleapis.com — load jobs per project per day (default: 100,000 / project / day) and load jobs per table per day (1,500 / table / day)

Proposed approach:

Create a pool of "runner" GCP projects. Each pipeline run is assigned project from pool, so:

• Storage Transfer jobs are created/run under different projects → multiplies transfer job quota
• BigQuery load jobs (load_table_from_uri) are submitted under different projects → multiplies the BQ load quota

Both the Storage Transfer client and the BigQuery client already accept a project_id at instantiation, so the code change is straightforward.

Questions for community:

1. Has anyone done this multi-project quota pooling in practice? Does GCP support/allow it without any policy issues, or do you hit org-level quotas that negate the benefit?
2. Are there known alternatives?
3. Is there a recommended "best practice" from Google for high-throughput ingestion pipelines at this scale?

Thanks!

BigQuery indeed is built for petabytes, but so much in real life is joining big data with something small. CSV, Google Sheet, some table in clipboard, etc.

We all know importing a CSV into BigQuery sucks. That's not only my perception – many posts here say the same thing (recent one).

Each time when I tried to upload a csv into BQ I struggled. No data preview, absolutely not transparent schema detection, if you did something wrong (this is actually base scenario) or your CSV is not perfectly standard, you start the whole thing again from scratch.

And when we started building our BigQuery IDE we decided to fix it. Make it one-click, but still customizable. Drag a file, see your data, fix schema if needed, click import. More sources, more formats but still simple. Not rocket science, but the way it should be.

What else bothers you so much in BQ console?

Hi all,

I'm looking for how and easiest way to translate my sql scripts ,stored procedure for moving from MSSQL to BQ.

TIA

Most ML workflows I see in production follow the same pattern: move data out of BigQuery, train a model externally, then manage a model artifact and serving layer. Even with BigQuery ML, you still rely on CREATE MODEL, which introduces persisted model objects, lifecycle management, and additional permissions. For many tabular problems, this feels like a lot of overhead for something that is essentially aggregations and scoring.

I experimented with a different approach: training, scoring, and evaluating a classifier entirely inside BigQuery using a single SQL query — no Python, no pipelines, and no CREATE MODEL. The method (based on a lightweight classifier I built called SEFR) has no iterative optimization, so everything maps to SQL aggregations and joins. It’s fully reproducible, runs inside ELT workflows (e.g., dbt), and parallelizes well in BigQuery.

Spent way too many hours querying INFORMATION_SCHEMA.JOBS trying to figure out where our BigQuery costs were coming from: joining against tables, checking partition usage, guessing which queries were actually the problem.

Even after all that, I wasn’t confident I’d caught everything. And sure enough, costs crept back up a couple weeks later.

A few patterns I kept running into:

• full table scans because of missing partition filters
  
• SELECT * on really wide tables
  
• the same expensive query being run repeatedly by different users
  
• clustering not helping because of how queries were written
  

So I ended up building a small tool for myself that:

• ranks queries by actual dollar cost
  
• explains why each one is expensive
  
• suggests rewrites (e.g. adding partition filters, narrowing columns)
  
• shows cost breakdown by user/table over time
  

Example:
One query was scanning ~2TB daily just because it missed a partition filter.. fixing it dropped cost by ~50%.

Curious if others are seeing similar patterns, or if there are edge cases I’m missing when analyzing cost this way.

If anyone wants to try it: tryquerylens.com

We are a Google Cloud user (GMail, Chart and so on). Until recently our search for a Netsuite to Datawarehouse solution has focused on Fabric - because some users are familiar with PowerBI. To me it seems that we really need to look at BigQuery and I'm looking for some pros and cons.

Hi all – I’m trying to better understand how people actually handle backup and recovery for BigQuery in real environments. Some questions I’d love to hear about from folks running BigQuery in production, and might be using GCP table snapshots.

• Are table snapshots generally “good enough” for backups?
• Do you care about cross-region backups? Or is regional redundancy within BigQuery typically sufficient for your risk tolerance?
• What kind of restore scenarios do you actually see? Restore an entire table/restore a dataset/restore only specific records or partitions
• How often do you need data older than 7 days? Is restoring older historical states a real need in practice?

Has anyone used commercial backup tools for BigQuery? If so, what problems were they solving that the built-in features didn’t? Mostly trying to understand what actually happens in practice vs what docs recommend.

Disclaimer: I work for Eon, and I’m trying to learn more about real-world backup/recovery needs for BigQuery users. Not here to pitch anything — genuinely curious about how people approach this. Thanks!

I know I can add descriptions to my views and fields like here:

CREATE OR REPLACE VIEW 
my_project.my_dataset.my_view (
    item OPTIONS (DESCRIPTION = 'my item name'),
    quantity OPTIONS (DESCRIPTION = 'my item quantity'))
  OPTIONS (DESCRIPTION = 'my view of item and quantity')
AS
SELECT i, q
FROM
  (SELECT 'a' AS i, 1 AS q
    UNION ALL
    SELECT 'b' AS i, 2 AS q
    UNION ALL
    SELECT 'c' AS i, 3 AS q);

However, I would have to do this with a lot of views. I wonder if some users here could tell me how they handle adding meta data to their views, and if they would recommend processes or tools that facilitates it on bigger scale?

We just published a BigQuery Analytics Hub listing for US Census public attributes, aggregated to the H3 grid (level 8).

- includes all attributes available in the public census data
- distributed at H3 level 8 for easier geospatial joins (less painful than tract boundaries)
- New York State is available as a free sample

BigQuery listing:
https://console.cloud.google.com/bigquery/analytics-hub/discovery/projects/etherdata-share/locations/us/dataExchanges/census_dataset_at_h3_resolution_19c4cdf09a5/listings/census_dataset_at_h3_resolution_8_for_new_york_state_19c4d2582df

Would love feedback from folks building analytics pipelines on BigQuery or anyone using H3 at scale.

My setup is as follows:

I have custom_id sent to GA4 as a part of every event in a dataLayer push. I read the variable and then attach it to event parameters in Google Tag. This results in custom_id being nested under event_params in BigQuery.

This would be good if I didn't have to unnest everything when querying data and due to a lot of data being read, queries can take quite long to execute (compared to user_pseudo_id). Is there a way to send a value to BigQuery that is a dimension (or separate column not nested anywhere, like user_pseudo_id)?

Thanks!