BigQuery Wildcard Tables vs Partitioned Tables: The Migration That Reduced Query Costs by 91%
Many companies migrate to BigQuery and unknowingly bring one of the most expensive architectural habits from legacy analytics platforms with them.
Instead of storing events in a single partitioned table, they create a new table every day.
The pattern usually looks familiar:
events_20250101
events_20250102
events_20250103
...
events_20261231
At first, everything works perfectly.
Queries are simple.
Engineers understand the structure.
Reporting is fast because there are only a few dozen tables.
The problem appears two or three years later.
By then, the architecture has become expensive to operate, difficult to optimize, and increasingly resistant to change.
This investigation began during a BigQuery modernization project for an e-commerce company processing approximately 3.8 billion events every month. The analytical platform had evolved over five years and contained more than 2,100 daily event tables, 470 scheduled transformations, and 95 executive dashboards.
The company wasn’t looking for performance improvements.
It was looking for an explanation.
Despite relatively stable traffic growth, BigQuery costs had increased by almost 48% over eighteen months. Dashboard response times had also become noticeably less predictable. Some reports completed in seconds. Others required several minutes despite querying similar business metrics.
The first assumption was that data volume had simply outgrown the original architecture.
Cloud Billing Export suggested otherwise.
Storage costs had increased exactly as expected.
Query costs had increased much faster than data volume.
The engineering team extracted execution statistics from INFORMATION_SCHEMA.JOBS_BY_PROJECT and ranked every production query by total bytes processed, execution frequency, and execution duration.
One recurring SQL pattern appeared almost everywhere.
FROM `analytics.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
More than 72% of scheduled workloads relied on wildcard tables.
That wasn’t automatically wrong.
Wildcard queries are fully supported by BigQuery and can perform well when used appropriately.
The question was whether they still represented the best architectural choice after five years of continuous growth.
Instead of debating theory, the engineers selected one representative workload.
The report calculated daily revenue, customer acquisition cost, conversion rate, and average order value for the previous thirty days.
Business requirements were modest.
The query needed only 31 days of data.
The execution statistics were less modest.
Average runtime:
97 seconds
Average data scanned:
12.8 TB
Monthly executions:
1,440
Monthly compute cost:
Approximately $9,200
Nothing in the SQL looked suspicious.
The date range was correct.
Filters were selective.
Aggregations were efficient.
The problem wasn’t the query.
It was the storage model.
To understand why, the engineers rebuilt the workload using a single partitioned table containing exactly the same data.
No SQL optimizations were introduced.
No clustering changes were made.
No materialized views were added.
The only architectural difference was replacing more than 2,100 daily tables with one partitioned table using event_date as the partition key.
The comparison was immediate.
The partitioned version scanned 1.4 TB instead of 12.8 TB.
Execution time dropped from 97 seconds to 19 seconds.
Monthly query cost fell from approximately $9,200 to less than $1,000.
The SQL itself had barely changed.
The architecture had.
The engineering team resisted declaring victory.
One successful migration proved only that one workload benefited from partitioning.
The important question remained unanswered.
Would every wildcard query become cheaper after migration—or were some workloads actually better left exactly as they were?
The answer turned out to be far more nuanced than most BigQuery migration guides suggest, and it completely changed how the company designed analytical storage going forward.
The engineering team expanded the investigation beyond a single dashboard. Rather than assuming that partitioned tables were always superior, they identified every production query using wildcard tables and measured how those workloads would behave after migration.
Using INFORMATION_SCHEMA.JOBS_BY_PROJECT, they extracted three months of production history and identified 512 scheduled jobs referencing tables through the events_* pattern.
Each workload was classified according to four characteristics:
- Reporting period (1 day, 7 days, 30 days, or historical).
- Query frequency.
- Average bytes processed.
- Business criticality.
The engineers expected nearly every workload to benefit from migration.
The data told a different story.
| Workload Type | Improvement After Migration |
|---|---|
| Daily operational reports | 84–93% |
| Weekly business dashboards | 71–88% |
| Monthly KPI reports | 42–61% |
| Historical research queries | Less than 10% |
The pattern made sense.
Operational reports almost always analyzed recent data, allowing BigQuery to eliminate irrelevant partitions efficiently.
Historical investigations, however, often scanned several years of information. Whether that data lived in one partitioned table or thousands of daily tables made relatively little difference because almost every partition still had to be read.
The migration strategy immediately changed.
Instead of rebuilding every dataset, the company focused first on workloads where measurable business value existed.
The second phase of the investigation exposed another hidden cost that had nothing to do with query execution.
Metadata.
The platform contained more than 2,100 event tables.
Every new table required metadata management, IAM validation, monitoring, lineage updates, backup policies, and schema verification.
Individually, these operations consumed almost no resources.
Collectively, they complicated every platform change.
One example illustrated the problem perfectly.
A new business requirement introduced a single additional field: customer_loyalty_level.
With daily wildcard tables, engineers had to ensure that every newly created table adopted the updated schema while maintaining compatibility with thousands of historical tables.
Even with automation, deployment validation became increasingly complex.
The partitioned architecture required only one schema modification.
One deployment.
One validation process.
The technical debt accumulated over years disappeared almost overnight.
The investigation also measured engineering effort.
The company reviewed twelve months of Jira tickets related to BigQuery maintenance.
Out of 186 infrastructure-related tickets, 74 involved wildcard-table architecture.
Typical issues included:
- Missing daily table creation.
- Schema inconsistencies between dates.
- Failed scheduled jobs caused by absent tables.
- Incorrect wildcard filters.
- Manual backfills after pipeline interruptions.
Those incidents represented approximately 410 engineering hours during a single year.
Assuming an average engineering cost of $95 per hour, operational maintenance alone exceeded $38,000 annually.
Those costs never appeared in Cloud Billing Export.
They appeared in payroll.
The migration itself required careful planning.
Several architects proposed replacing every wildcard query immediately.
That approach was rejected.
Instead, the engineering team introduced partitioned tables alongside the existing architecture and redirected workloads gradually.
The migration sequence followed a strict order:
- Build partitioned tables from historical data.
- Validate row counts and business metrics.
- Compare dashboard outputs.
- Redirect scheduled transformations.
- Monitor costs for two billing cycles.
- Decommission wildcard tables only after verification.
The staged migration avoided business disruption while producing measurable financial evidence.
After four months, the results were independently confirmed using Cloud Billing Export.
| Metric | Before | After |
| Monthly query cost | $84,600 | $49,300 |
| Average dashboard response | 74 sec | 21 sec |
| Scheduled job failures | 23/month | 5/month |
| Engineering maintenance | ~410 h/year | ~90 h/year |
Interestingly, the largest financial benefit did not come from lower compute costs.
It came from reducing operational complexity.
Engineers spent less time maintaining infrastructure and more time building analytical products.
The company also learned an important architectural lesson.
Partitioned tables are not automatically superior.
They become superior when business workloads primarily analyze recent data and when the platform benefits from centralized schema management.
Wildcard tables still retained value for specific use cases, including immutable archival datasets, external integrations where table naming could not be controlled, and certain migration scenarios.
The mistake had never been using wildcard tables.
The mistake was continuing to use them after the original business justification had disappeared.
Many architectural decisions are perfectly reasonable when a platform contains fifty tables.
The same decisions become expensive liabilities when the platform contains several thousand.
Architecture should evolve with the business.
Otherwise, yesterday’s best practice quietly becomes tomorrow’s recurring cloud invoice.
What Most Teams Do Wrong
A common pattern looks like this:
- Create one table per day.
- Build reports using
events_*. - Continue adding daily tables for years.
- Accept increasing maintenance as “normal.”
- Assume higher cloud costs are simply the price of growth.
A better approach is:
- Measure which workloads benefit from partition pruning.
- Estimate operational maintenance, not only compute costs.
- Migrate incrementally with production validation.
- Keep wildcard tables only where they solve a genuine architectural problem.
- Review storage architecture every few years instead of treating it as permanent.
Evidence Collected
- Cloud Billing Export
INFORMATION_SCHEMA.JOBS_BY_PROJECT- BigQuery table metadata
- Audit Logs
- Jira operational history
- Cloud Monitoring
- Dashboard validation reports
Executive Recommendations
- Evaluate storage architecture using both engineering effort and cloud costs.
- Prioritize migrations with measurable financial return instead of pursuing architectural purity.
- Standardize schemas wherever possible to reduce operational risk.
- Don’t assume that an architecture chosen five years ago is still the right one today.
- Treat technical debt as a recurring operational expense, not merely an engineering inconvenience.
The Question Every CTO Should Ask
“If we designed our BigQuery storage architecture from scratch today, would we build it the same way—or are we simply maintaining yesterday’s decisions because they still work?”
We evaluate your current data setup to eliminate performance bottlenecks, refactor complex pipelines, and prevent unexpected cloud costs. Schedule a BigQuery Migration & Architecture Audit to ensure your data infrastructure is scalable, secure, and cost-efficient.