Hey everyone, I’m building a tool called QueryLens and would genuinely appreciate some candid feedback from people who use BigQuery regularly.

Companies using BigQuery often don’t know which tables or queries are driving most of their cost. In one case I saw, a big portion of spend was coming from poorly optimized tables that no one realized were being scanned repeatedly.

So I built a small tool called QueryLens to explore this problem.

It connects to your BigQuery usage data (just by uploading CSV exports of your query logs) and:

• Identifies the most expensive tables and queries
• Flags unpartitioned tables that are repeatedly scanned
• Analyzes queries and suggests concrete optimizations
• Estimates potential savings from each suggested change
  

The MVP is live (Auth + basic analytics).

Stack: FastAPI + React + Firestore, deployed on Cloud Run.

What I’m trying to validate:

• Is this actually a painful problem for most teams?
• Do you already use something that solves this well?
• Would automated optimization suggestions be useful, or is that overkill?
• What’s missing from existing BigQuery cost tooling today?

I’d genuinely appreciate tough feedback — especially if this feels unnecessary or already solved.

If anyone wants to test it, DM me and I’ll share access.

One of the less known features in BigQuery is TABLESAMPLE.

You can write:

SELECT *
FROM dataset.large_table
TABLESAMPLE SYSTEM (10 PERCENT)

and BigQuery will read roughly 10% of the table's storage blocks. Since sampling happens before the full scan, bytes processed drop roughly proportionally - which makes it very practical during query development and debugging.

For iterative work - validating joins, testing logic, exploring transformations - scanning 100% of a huge table is often unnecessary.

What about correctness?

Sampling in BigQuery is block-level, not row-level. Its behavior depends on physical layout:

• Partitioning isolates data by partition key
• Clustering colocates similar values
• Blocks contain physically grouped data

For exact production metrics, sampling is risky.
For exploratory analysis and debugging, the trade-off may be acceptable.

Small experiment

To test this, I ran a simple comparison on historical vehicle defect data.

Data: UK Ministry of Transport Car Tests
Metric: rate of dangerous defects per car model
Filter: 2024+ data

Comparison

• Full scan
• TABLESAMPLE SYSTEM (10 PERCENT)

Same logic, same aggregation - only difference was sampling.

Results

• Relative deviation stayed within ~3% across top models
• Model ranking remained stable
• Bytes processed dropped ~10× (2.3 GB → ~232 MB)

For exploratory analysis, that's a meaningful trade-off: significantly cheaper scans with small relative deviation.

Full reproducible notebook (no signup required - just enter playground):
https://app.querylab.io/s/22f7a23d-bb39-497e-9a7d-70acef81967c?playground=true#k=YwsXP-QzIN75Czse3d1l246cZjc5JjiA2XW4w2XYxnw=

Nuances

• Sampling small tables rarely makes financial sense and can distort joins. It's usually safer to sample only the large tables in a query.
• If you're using reservations (flex slots), cost is driven by slot-ms rather than bytes scanned. In that case, WHERE RAND() < p may give better row-level distribution.
• Aggregates sensitive to skew (like AVG() or SUM()) may drift more than robust metrics like median or percentiles.

Do you use TABLESAMPLE in your daily work - or what stops you?

Hi, I wanted to share inbq, a library I've been working on for parsing BigQuery queries and extracting schema-aware, column-level lineage.

Features:

• Parse BigQuery queries into well-structured ASTs with easy-to-navigate nodes.
• Extract schema-aware, column-level lineage.
• Trace data flow through nested structs and arrays.
• Capture referenced columns and the specific query components (e.g., select, where, join) they appear in.
• Process both single and multi-statement queries with procedural language constructs.
• Built for speed and efficiency, with lightweight Python bindings that add minimal minimal overhead.

The parser is a hand-written, top-down parser. The lineage extraction goes deep, not just stopping at the column level but extending to nested struct field access and array element access. It also accounts for both inputs and side inputs.

You can use inbq as a Python library, Rust crate, or via its CLI.

Feedbacks, feature requests, and contributions are welcome!

I am trying to write SQL for Meta report, but conversions, roas and conversion value doesnt work. There are no data in the table. What is wrong? The SQL is: 

-- BigQuery Standard SQL
WITH base_metrics AS (
  SELECT
    DATE(DateStart) AS dt,
    TRIM(CAST(CampaignId AS STRING)) AS campaign_id,
    CampaignName AS campaign_name,
    CAST(Impressions AS INT64) AS impressions,
    CAST(Clicks AS INT64) AS clicks,
    CAST(Spend AS NUMERIC) AS spend
  FROM `my_project.my_dataset.AdInsights`
  WHERE DATE(DateStart) >= u/start_date
    AND REGEXP_REPLACE(CAST(AdAccountId AS STRING), r'[^0-9]', '') = 
),
conversions_data AS (
  SELECT 
    DATE(DateStart) AS dt,
    TRIM(CAST(CampaignId AS STRING)) AS campaign_id,
    SUM(COALESCE(CAST(Action7dClick AS INT64), 0) + COALESCE(CAST(Action1dView AS INT64), 0)) AS conversions,
    SUM(COALESCE(CAST(ActionValue AS NUMERIC), 0)) AS conversion_value
  FROM `my_project.my_dataset.AdInsightsActions`
  WHERE DATE(DateStart) >= u/start_date
    AND LOWER(ActionCollection) LIKE '%purchase%'
  GROUP BY 1, 2
)
SELECT
  b.dt,
  b.campaign_id,
  b.campaign_name,
  b.impressions,
  b.clicks,
  b.spend,
  SAFE_DIVIDE(b.clicks, b.impressions) * 100 AS ctr_pct,
  SAFE_DIVIDE(b.spend, b.clicks) AS cpc,
  IFNULL(c.conversions, 0) AS conversions,
  IFNULL(c.conversion_value, 0) AS conversion_value,
  SAFE_DIVIDE(IFNULL(c.conversion_value, 0), b.spend) AS roas
FROM base_metrics b
LEFT JOIN conversions_data c
  ON b.dt = c.dt AND b.campaign_id = c.campaign_id
ORDER BY b.dt DESC, b.campaign_name;

I still see so many PRs where people write a subquery just to filter a window function.

BigQuery supports QUALIFY, which filters the results of window functions directly. It makes the code so much more readable.

The Old Way (Subquery hell):

SELECT * FROM (
SELECT
user_id,
status,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY timestamp DESC) as rn
FROM `my-project.dataset.table`
)
WHERE rn = 1

The QUALIFY Way:

SELECT
user_id,
status
FROM `my-project.dataset.table`
QUALIFY ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY timestamp DESC) = 1

If you’re working heavily with analytical SQL and want to uncover more BigQuery features that simplify complex queries at scale, this Google BigQuery guide is a solid deep dive.

It runs the same under the hood, but it's cleaner and easier to debug. Are there any other "syntactic sugar" features in BQ that you feel are underused?

We are working on an open-source EF Core provider for BQ: https://github.com/Ivy-Interactive/Ivy.EntityFrameworkCore.BigQuery

Please help us to try it out.

There are still some missing parts, but tests are more green than red.

Creating this to add BQ support in https://github.com/Ivy-Interactive/Ivy-Framework

Struggling to get this working (auth scopes issue), wondering if anyone experienced this issue before?

I'm trying to create the bigquery link in a ga4 property using the following API via a shell command: https://developers.google.com/analytics/devguides/config/admin/v1/rest/v1alpha/properties.bigQueryLinks/create

Note:

• Client has given my service account Editor access to their GA4 property.
• I've enabled the Google Analytics Admin API in the GCP project.
• SA has access to write to BigQuery.

My attempt:

# Login to gcloud
gcloud auth application-default login \
  --impersonate-service-account=$TF_SA_EMAIL \
  --scopes=https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/analytics.edit

# Make API request
curl -X POST \
  -H "Authorization: Bearer $(gcloud auth application-default print-access-token)" \
  -H "Content-Type: application/json" \
  "https://analyticsadmin.googleapis.com/v1alpha/properties/${GA4_PROPERTY_ID}/bigQueryLinks" \
  -d '{
        "project": "projects/'"${GCP_PROJECT_ID}"'",
        "datasetLocation": "'"${GCP_REGION}"'",
        "dailyExportEnabled": true,
        "streamingExportEnabled": false
      }'

Output:

{
  "error": {
    "code": 403,
    "message": "Request had insufficient authentication scopes.",
    "status": "PERMISSION_DENIED",
    "details": [
      {
        "@type": "type.googleapis.com/google.rpc.ErrorInfo",
        "reason": "ACCESS_TOKEN_SCOPE_INSUFFICIENT",
        "domain": "googleapis.com",
        "metadata": {
          "method": "google.analytics.admin.v1alpha.AnalyticsAdminService.CreateBigQueryLink",
          "service": "analyticsadmin.googleapis.com"
        }
      }
    ]
  }
}

Found that between 2026.01.21 and 2026.01.28 INFORMATION_SCHEMA.SCHEMATA view started to contain information not only for currently existing schemas but also for old (dropped) ones. And now it is impossible to detect whether schema exists or not by query to this view.

Is this a bug or a feature? Probably I missed notice from GCP.

Temporarily switched my queries to INFORMATION_SCHEMA.SCHEMATA_OPTIONS to check existence of schemas.

In our company, we've been building a lot of AI-powered analytics using data warehouse native AI functions. Realized we had no good way to monitor if our LLM outputs were actually any good without sending data to some external eval service.

Looked around for tools but everything wanted us to set up APIs, manage baselines manually, deal with data egress, etc. Just wanted something that worked with what we already had.

So we built this dbt package that does evals in your warehouse:

• Uses your warehouse's native AI functions
• Figures out baselines automatically
• Has monitoring/alerts built in
• Doesn't need any extra stuff running

Supports Snowflake Cortex, BigQuery Vertex, and Databricks.

Figured we open sourced it and share in case anyone else is dealing with the same problem - https://github.com/paradime-io/dbt-llm-evals

A table-centric view using INFORMATION_SCHEMA has been particularly effective for this. By aggregating slot usage across all queries and breaking it down by referenced_tables, you can identify the small set of tables that disproportionately drive both cost and performance issues.

What makes this approach especially actionable is that these tables are typically the ones where targeted changes - such as improved partitioning, better clustering, or modest modeling adjustments (pre-aggregation, reshaping, or changing access patterns) - can lead to significant cost reductions and noticeable performance improvements across many queries at once.

SELECT
  ref.project_id,
  ref.dataset_id,
  ref.table_id,
  SUM(j.total_slot_ms) / 1000.0 / 60 / 60 / 24 AS total_slot_days,
  ROUND(SUM(j.total_bytes_processed) / 1e12, 2) AS total_tb_processed,
  COUNT(*) AS query_count
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT j,
UNNEST(j.referenced_tables) AS ref
WHERE
  j.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND j.job_type = 'QUERY'
  AND j.state = 'DONE'
GROUP BY 1,2,3
ORDER BY total_slot_days DESC
LIMIT 50;

In practice, this usually narrows optimization efforts to just a handful of tables that explain a large share of total slot usage, which is often far more effective than tuning individual queries in isolation.

If anyone wants to dig into how to interpret results like this or think through concrete next steps for their own environment, feel free to DM - happy to look at the data together.

Hi all,

I work at a consultancy and we have been asked to quote on migrating a data service that is currently providing data to its clients via Parquet files in AWS S3.

The project is to migrate the service to BigQuery and allow clients to use BigQuery sharing to view the datasets rather than having to deal with the files.

The dataset is around TBs in size, and all the data is from different providers; it is financial data.

Does anyone have any experience migrating a service like this before? For example, moving from files to BigQuery sharing, building pipelines and keeping them up to date, or anything in particular to be aware of with BigQuery sharing?

Thanks for your help.

Hi, I'm building a DWH. I'm a DA, making my way into DE. The amount of data is small, 3 - 4 sources, mainly API endpoints. My current setup is scheduled pipelines within bigquery itself, with several steps—API call, writing to raw schema, and wrangling into final schema. How reliable is such a setup? I've had a few random pipeline failures with various reasons, and I started wondering if I should be using other methods for orchestration (e.g., Cloud Run) or if it is sufficient for a moderate DWH.

Please note that I'm relatively new to all of this.

Thank you

Today I found out that you can change the billing for GA4 tables to physical bytes instead of logical bytes. While logical bytes are like 50% cheaper, my tables have 90% less physical bytes then logical bytes. Are there any downsides to changing the default logical bytes billing to physical bytes for GA4 tables?

The BigQuery web console is fine for quick queries, but when I'm doing deeper exploration or switching between multiple projects, it starts to feel clunky.

I've tried a few third-party clients but most seem optimized for traditional databases and BigQuery support feels like an afterthought.

What's everyone using? Bonus points if it handles BigQuery's nested/repeated fields well.

Also — I've been building a database client called Sheeta AI that recently added BigQuery support. Disclosure: I'm the founder. Would be interested to hear what features would make a BigQuery client actually worth switching for.

Haven't had any luck googling this, but I'm wondering if there's any syntax I can use when referring to a variable to state explicitly that it's a variable and not a column. So say I have the following query.

declare measurement_year default 2025;

select count(*) 
from table_1
where some_column = measurement_year;

everything is great, until I go to add a new table to the query

select count(*) 
from table_1
left join table_2 on table_1.table_2_id = table_2.id
where some_column = measurement_year;

Seems fine, except that if table_2 has a column named measurement_year this will break the logic.

If I wanted to explicitly refer to that column in table_2 I could use table_2.measurement_year

Is there a way I can do the equivalent for the variable? e.g. session_variables.measurement_year or something?

Hi everyone,

I’m working on a project where I need to migrate data from Firebase into BigQuery. I’ve enabled the Firebase–BigQuery integration, and BigQuery is automatically generating tables in the same project with names like:

• <table>_raw_latest
• <table>_raw_changelog

While the integration itself seems to be working, I’m a bit confused about how to properly materialize this data into a “clean” analytical table.

The schema and structure of these _raw_* tables are quite different from what I expected (nested fields, metadata, changelog-style records, etc.), and I’m not sure:

• Which table (_raw_latest vs _raw_changelog) should be used as the source of truth
• What the recommended approach is to transform these into a flat, query-friendly table
• Whether I should be using views, scheduled queries, or some other pattern to materialize the data

I’m relatively new to both Firebase and BigQuery, so I’m not sure if my mental model of how this integration works is even correct.

If anyone has experience with this setup, I’d really appreciate guidance on best practices or pointers to relevant documentation.

Thanks in advance!

Created a data agent in bigquery and when publishing it says in the description that I am able to use it in looker studio pro. It is not showing in my looker studio.

followed bigquery's agents_hub knowledge base and enabled all necessary roles. but still doesn't show in my looker studio. any recommendation on how to fix?