dbt Wildcard Tables in BigQuery Without _TABLE_SUFFIX Filter: The Silent Cost

If a dbt model queries a BigQuery wildcard table (e.g. events_*) without a _TABLE_SUFFIX filter in the WHERE clause, BigQuery scans every single shard that matches the wildcard — every day, every run, with no pruning whatsoever. The cost scales linearly with the number of shards you have accumulated, and it grows silently until someone notices the bill.

This is one of the most common cost problems we find in BigQuery projects built on top of GA4 exports, Firebase event streams, or any ingestion pipeline that writes daily date-sharded tables rather than appending to a single partitioned table. The fix is straightforward once you know which models are affected. Finding them is the harder part — because dbt gives you no warning, the query runs successfully, and the BigQuery job metadata shows bytes scanned rather than bytes you expected to scan.

Why Date-Sharded Wildcard Tables Are Still Everywhere

BigQuery has supported native partitioned tables for years, and modern pipelines generally prefer them. But date-sharded tables — where each day's data lands in a separate table named events_20260101, events_20260102, and so on — remain extremely common for one reason: Google still exports GA4 and Firebase data into BigQuery using exactly this pattern.

Teams that ingest GA4 data inherit this structure without choosing it. They build dbt staging models on top of it, often without realising that the mechanics of cost control are fundamentally different from a partitioned table. On a partitioned table, a missing partition filter is a correctness and cost risk — but BigQuery will at least warn you if you have enabled require_partition_filter. On a wildcard table, there is no equivalent enforcement mechanism. A missing _TABLE_SUFFIX filter is silent, legal SQL that scans years of accumulated data.

Without a _TABLE_SUFFIX filter, BigQuery scans all matching tables. If your GA4 export has been running for two years, that means 730 daily shards are read on every model execution — whether your transformation logic needs one day of data or all of them.

Google BigQuery charges $6.25 per TB scanned on demand in 2026, and a single SELECT * on a 10 TB table costs $62.50. On a large GA4 export where each daily shard is 20–30 GB, scanning 730 shards costs roughly $90–$135 per run — and most teams are running their staging models multiple times per day.

---

📺 Watch: Big Query SQL Tutorial - How to easy JOIN tables (Part #5)

---

How to Find dbt Models Scanning Every Shard

The fastest diagnostic is a two-step process: first find the wildcard source tables in your project, then cross-reference them against BigQuery's query history to identify which models are scanning anomalously high byte volumes.

Step 1 — Find wildcard source references in your dbt project

Search your dbt model SQL files for any reference to a wildcard table. On the command line from your dbt project root:

grep -r "_\*" models/ --include="*.sql" -l

This finds any .sql file containing a wildcard reference. It will catch events_*, ga4_export_*, firebase_*, and any other sharded pattern. If you get results here, those files need to be inspected.

Step 2 — Check whether the model applies a _TABLE_SUFFIX filter

For each file returned, check whether _TABLE_SUFFIX appears in the WHERE clause:

grep -rn "_\*" models/ --include="*.sql" | awk -F: '{print $1}' | sort -u | while read f; do
  if ! grep -q "_TABLE_SUFFIX" "$f"; then
    echo "MISSING SUFFIX FILTER: $f"
  fi
done

Any file printed by this command is a model querying a wildcard table with no suffix filter — a guaranteed full scan of every shard, every run.

Step 3 — Confirm the blast radius in INFORMATION_SCHEMA

Once you have the model names, pull their actual scan history from BigQuery. Substitute your project ID and the relevant job label prefix:

SELECT
  job_id,
  query,
  total_bytes_processed,
  ROUND(total_bytes_processed / POW(1024, 4) * 6.25, 4) AS estimated_cost_usd,
  creation_time
FROM
  `region-eu.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND statement_type = 'SELECT'
  AND REGEXP_CONTAINS(query, r'events_\*|ga4_export_\*')
ORDER BY
  total_bytes_processed DESC
LIMIT 20;

Replace the regex pattern with your actual wildcard table prefix. This query returns the twenty most expensive wildcard-touching jobs in the last 30 days, with a rough USD cost per job. What you will typically find is that a small number of dbt staging models are responsible for a disproportionate share of your total scan volume — and that the cost is entirely preventable.

For a fuller picture, see the BigQuery INFORMATION_SCHEMA Cost Attribution guide for how to attribute costs across your entire project systematically.

If you want to run this check — and a dozen others like it — across your whole dbt project without writing custom queries, fintel-scan is a free, open-source, MIT-licensed CLI that does exactly that locally with no warehouse connection required: uvx fintel-scan.

The Three Patterns We See in the Field

Once you start looking, wildcard-without-suffix problems tend to cluster into three repeating patterns.

Pattern 1 — The unfiltered staging model

The most common case: a stg_ga4_events.sql model that reads from events_* with no date filter at all. The original author was building against a small export dataset during development and never noticed the cost. In production with 18 months of history, the same model scans everything.

We have seen this pattern in multiple growth-stage companies — typically e-commerce and mobile app businesses that integrated GA4 shortly after launch and built their analytics layer on top of the export. By the time the data volume becomes significant enough to feel the cost, the model is embedded in a dependency chain and nobody wants to touch it.

Pattern 2 — The incremental model that does not prune shards

Some teams build incremental dbt models on top of wildcard tables, reasoning that incremental logic will limit processing. It does not. The incremental filter controls which rows are inserted into the target table — it does not apply a _TABLE_SUFFIX filter to the source wildcard scan. You can query daily export tables with a wildcard and the _TABLE_SUFFIX pseudo-column, but incremental loads do not work the same way as with a partitioned table — a full scan of every shard occurs regardless of the incremental predicate.

This is a particularly expensive variant because teams believe their incremental strategy is protecting them, so they never investigate the source scan volume.

Pattern 3 — The macro-generated model with a dynamic suffix

Some projects use Jinja macros to build dynamic date expressions that are intended to generate a _TABLE_SUFFIX filter. These sometimes silently fail when the Jinja expression does not evaluate correctly — producing a query that omits the WHERE clause entirely or generates an invalid suffix comparison. The model appears to work, the output data looks correct, and the cost signal is the only thing that tells you something is wrong.

Wildcard table queries do not support cached results, even if the "Use Cached Results" option is enabled — so every run is billed in full. This compounds the cost of all three patterns, since you cannot rely on the query cache to absorb repeated executions.

How to Fix It: Three Approaches

Once you have identified the affected models, there are three possible fixes in increasing order of permanence.

Fix 1 — Add a static _TABLE_SUFFIX filter (quickest)

For a staging model that only ever needs recent data, add a suffix filter directly in the WHERE clause:

SELECT
  event_date,
  event_name,
  user_pseudo_id
FROM
  `project.analytics_123456789.events_*`
WHERE
  _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))

This limits the scan to the last 90 days of shards. Adjust the interval to match the actual lookback your downstream models require. This single change can reduce scan volume by 80–90% on a mature export.

Fix 2 — Use a dbt var or Jinja expression for a rolling window

For models where the lookback window should be configurable:

WHERE
  _TABLE_SUFFIX >= FORMAT_DATE(
    '%Y%m%d',
    DATE_SUB(CURRENT_DATE(), INTERVAL {{ var('ga4_lookback_days', 90) }} DAY)
  )

This makes the lookback period a project variable, testable and reviewable in version control. Set a sensible default. Document the variable in your dbt_project.yml.

Fix 3 — Migrate to a partitioned table (permanent)

The correct long-term fix is to consolidate the sharded tables into a single partitioned table during ingestion, so your dbt models work against native partitioned tables where require_partition_filter can be enforced.

BigQuery supports querying multiple tables using wildcard expressions, but performance suffers when wildcards are too broad — modern partitioning strategies dramatically outperform date-sharded table patterns.

Migrating from sharded to partitioned is a heavier lift, but for high-volume tables it is worth it — partitioned tables support enforced partition filters, better clustering, and more predictable cost governance. If you are already using dbt incremental models with a partition_by config, your transformation layer is already structured correctly for the switch.

If your staging models are also triggering downstream cost issues through repeated scans, the dbt Models Scanning the Same BigQuery Table Twice guide covers how to detect and consolidate redundant reads at the model DAG level.

How to Stop It Recurring

Detecting the problem once is not enough. Wildcard-without-suffix models tend to reappear when new engineers join, when new data sources are integrated, or when existing models are cloned as templates without the filter being carried forward.

Three controls that prevent recurrence:

1. Add a custom dbt test that asserts the suffix filter is present. Use a schema test against your staging models to check that the rendered SQL contains _TABLE_SUFFIX. This is possible with a custom generic test and a run_query macro that inspects the compiled SQL at test time.

2. Set a cost alert in BigQuery scoped to the specific dataset where your sharded tables live. A sudden spike in bytes scanned from that dataset is a reliable early warning that a new model has been introduced without a suffix filter.

3. Document the pattern in your dbt project README with an example of the correct filter expression. Engineers who have never worked with date-sharded tables will not know this is required — a short note in the project docs eliminates a whole category of mistakes.

Frequently Asked Questions

Q: What is a BigQuery wildcard table and how does it differ from a partitioned table?

A: A BigQuery wildcard table is a naming pattern where related data is stored across multiple tables with a shared prefix and a variable suffix — typically a date, such as events_20260101. You query them using a wildcard character (events_*). A partitioned table stores all data in a single table divided into logical segments by a partition column. Partitioned tables support enforced partition filters and better cost governance; wildcard tables do not.

Q: Why does a missing _TABLE_SUFFIX filter cause a full scan in BigQuery?

A: When BigQuery executes a wildcard query without a _TABLE_SUFFIX filter, it has no basis for excluding any matching table from the scan. It reads every table whose name matches the wildcard pattern, regardless of how many shards exist. There is no metadata-only shortcut — the data is physically read from each shard.

Q: Can dbt incremental models limit scanning on wildcard tables?

A: No. The incremental filter in dbt controls which rows are inserted or updated in the target model — it is applied after the source data is read. It does not translate into a _TABLE_SUFFIX filter on the source wildcard scan. You must add the suffix filter explicitly in the model's SELECT statement for scan pruning to occur.

Q: Does the BigQuery query cache help with wildcard table queries?

A: No. BigQuery does not cache results for wildcard queries, even when the "Use Cached Results" option is enabled. Every execution of a wildcard query is billed in full, which makes unfiltered wildcard models particularly expensive in pipelines that run frequently.

Q: How do I find which BigQuery dataset contains my GA4 date-sharded tables?

A: GA4 exports land in a dataset named analytics_<property_id> in the BigQuery project you configured in the GA4 admin. The tables are named events_YYYYMMDD. You can confirm the dataset by running SELECT table_schema, table_name FROM region-eu.INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'events_%' — substituting your region as appropriate.

If your dbt models are running expensive wildcard scans that you cannot easily attribute or contain, this is a solvable problem — and the fix is faster than most engineers expect. At Fintel Analytics, we have audited and rebuilt analytics layers across fintech, e-commerce, and mobile app businesses where unfiltered wildcard models were quietly doubling monthly BigQuery spend, often for months before anyone connected the bill to a specific model. If your BigQuery costs are climbing faster than your data volume justifies, our team can identify exactly where the waste is coming from and fix it at the root.