BigQuery Implementation Mistakes: Fix Missing Data, Cost Surprises and Unreliable Reports

Last updated: 13 October 2026.

Define the output and its operating contract

Start with a business question that needs warehouse data: paid acquisition by customer cohort, trial activation by source or revenue after refunds. Then specify the source, reporting grain, identity rules, timezone, freshness requirement and owner.

Record three separate Google Cloud concepts: where data lives, which project runs and pays for jobs, and which identity performs the work. A user may read a dataset in one project while creating the query job in another. Troubleshooting only the storage project can miss the activity causing the bill.

For every production output, define what should happen when inputs are missing. A revenue chart should show that a billing source is late. Replacing unknown values with zero can make a broken pipeline look like a business crisis.

Our marketing data warehouse guide explains the modeling decisions. The checks below focus on the implementation failures that can invalidate that design.

Open full diagram

Mistake 1: Choosing a data location without checking the joins

Choose the location with the required sources, organization policies, downstream jobs and operating constraints in view. A GA4 export in US and a CRM dataset in us-central1 are not automatically colocated simply because both are in the United States.

Google's dataset documentation says the location is set at creation and cannot be edited afterward. Ordinary queries referencing multiple datasets require compatible placement in the same location. Record exact location identifiers in the implementation inventory, not broad labels such as “America” or “Europe.”

If the location is wrong, plan a migration. Preserve the existing data, inspect dependent views and connections, reconcile the new copy and verify new writes before cutover. A successful historical copy does not prove tomorrow's export reaches the intended destination.

The current GA4 setup guide describes replication as an alternative to deleting the export link. Evaluate its prerequisites for the actual dataset.

The replication documentation describes asynchronous secondary replicas, separate storage and transfer costs, and promotion requirements. Scheduled queries do not automatically move to a new primary location. Recreate or reconfigure those dependencies as required and test them. Do not promise a migration with no interruption solely because replication exists.

Mistake 2: Treating a linked GA4 property as proof of complete history

A property can be linked while the required daily tables are missing. Check which property, streams and export modes are enabled, then inventory the actual date coverage. Use the first complete observed export date in your reporting contract. Do not promise pre-link raw event history merely because the property has existed for years.

For standard properties, persistent excess over one million daily events can pause batch export; significant excess can pause it immediately. Missed days are not reprocessed. Export filters reduce volume but change reporting scope. Document the selected population in the report itself.

Streaming is a different tradeoff. Google's export overview describes no streaming volume limit, but calls the service best effort and warns about gaps and incomplete traffic-source information. Streaming is not a guaranteed substitute for a complete daily export.

Inspect date coverage before writing a complicated reconciliation query. This original metadata query lists whether each expected daily table exists for a fixed week:

WITH expected AS (
  SELECT export_date
  FROM UNNEST(GENERATE_DATE_ARRAY(
    DATE '2026-10-06', DATE '2026-10-12'
  )) AS export_date
), daily_tables AS (
  SELECT
    SAFE.PARSE_DATE('%Y%m%d', SUBSTR(table_name, 8)) AS export_date,
    table_name
  FROM `your-project.analytics_123456789.INFORMATION_SCHEMA.TABLES`
  WHERE REGEXP_CONTAINS(table_name, r'^events_[0-9]{8}$')
    AND table_type = 'BASE TABLE'
)
SELECT
  e.export_date,
  d.table_name,
  d.table_name IS NOT NULL AS daily_table_exists
FROM expected AS e
LEFT JOIN daily_tables AS d USING (export_date)
ORDER BY e.export_date;

Replace the project, property dataset and dates. Run the job in the dataset's location with appropriate metadata access. Google's TABLES reference documents the metadata fields.

The exact name filter excludes intraday tables, backups and similarly named views. Table existence is only the first check. An empty or partial daily table still requires row counts, event coverage and freshness checks. A missing table is unknown data, not evidence of zero events.

Keep a coverage record with four states: absent, present but provisional, accepted for reporting, and known incomplete. Store the reason and the last successful validation. Use that record to control downstream refreshes and annotate reports.

Mistake 3: Letting billing or sandbox limits become a data incident

Assign an owner to the Cloud billing account and its payment status. A warehouse engineer may see successful SQL while an export has stopped for a billing reason. Google documents that invalid-payment interruptions leave export gaps it cannot re-export.

Recover the pipeline first, then record the historical gap honestly. Check whether another authorized source retained the business facts you need, such as paid orders in the billing system. Restoring an order total does not reconstruct all missing browser events or attribution details. Keep substitute data and its provenance distinguishable.

The BigQuery sandbox has a lifetime storage allowance of 10 GiB that deletion does not replenish, one TiB of monthly query processing and automatic sixty-day expiration for tables, views and partitions. Streaming, DML and Data Transfer Service are unavailable there. These constraints differ from a billing-enabled project that happens to remain within eligible free usage.

Before relying on the export, check billing status and actual expiration settings. After an upgrade, verify existing tables and defaults rather than assuming that history is now retained indefinitely. Retention needs a business decision: acquisition analysis, annual cohorts and short-lived debugging datasets may require different periods.

Add alerts for missing expected dates and unexplained volume changes. Checking the payment method only after a monthly report fails is too late to protect history.

Mistake 4: Comparing an API report with raw export as if they were identical

Ask an API connector what it actually retrieves. Does it call the GA4 Data API for a report with selected dimensions and metrics, or load the native event export? Both can produce tables in BigQuery. They are different data products.

The Data API runReport method returns a report specified by dimensions, metrics, dates and filters. Loading that result into a warehouse does not turn it into individual events. A table of sessions by source cannot recreate the exact sequence of pages and product actions for each visitor.

Google's UI versus export explanation describes differences caused by reporting calculations, modeling, cardinality handling and metric scope. Use that distinction when investigating discrepancies, while checking the current export schema for fields added since older examples were written.

Write a comparison contract before calling a difference a bug:

• Same property and selected data streams.
• Same date boundaries and property timezone.
• Same event or business metric definition.
• Same filters, currency treatment and inclusion of refunds where relevant.
• Explicit choice of user, session, event or item scope.
• A known treatment of provisional data and unavailable modeled components.

For example, compare purchase-event counts before comparing an attributed revenue report. If raw purchase counts reconcile but channel totals differ, investigate attribution and scope. Rewriting the ingestion process would target the wrong layer.

Our GA4 purchase mismatch guide provides a more specific transaction investigation. Preserve unresolved differences as named reconciliation items instead of applying an unexplained adjustment factor.

Mistake 5: Treating yesterday's first export as permanently final

Daily and intraday tables serve different purposes. The GA4 export schema describes their naming and the regular late-arrival update window. Daily tables can receive updates after their first appearance. The export overview also describes exceptional historical reprocessing.

Design a recent-date rebuild window and a separate controlled backfill process. For a daily reporting model, reprocessing the recent exported dates can capture ordinary updates. Detect older changes or business corrections and rebuild the affected range deliberately. A scheduled job that always reads only yesterday can miss changes to earlier dates.

Make replacement idempotent: rerunning the same accepted input should produce the same business result. Append-only aggregation can count updated source rows twice. Reconcile counts and key financial totals after replacing a date partition.

Use completed daily tables for stable historical reporting and define a separate current-day view if the business needs it. Do not union overlapping daily and intraday data and sum both. Also avoid claiming that an intraday table's presence proves complete current-day collection.

Timezone belongs in the model contract. Calendar dates in the property timezone and UTC timestamp boundaries can assign near-midnight activity differently. Test an event near midnight in the actual property timezone rather than assuming the warehouse job's default date is the right reporting date.

Mistake 6: Letting dashboards repeatedly scan the raw layer

Start a cost investigation with job evidence. Identify the job project, identity, schedule and recurring query pattern. A dashboard with multiple tiles and frequent refreshes can perform far more work than its user count suggests.

This original diagnostic groups recent completed query jobs by submitting identity and reservation. It excludes parent script totals so child-query usage is not counted twice:

SELECT
  user_email,
  reservation_id,
  COUNT(*) AS query_jobs,
  COUNTIF(total_bytes_billed IS NULL) AS jobs_with_unknown_bytes,
  SUM(total_bytes_billed) / POW(1024, 4) AS known_billed_tib,
  SUM(total_slot_ms) / 3600000.0 AS consumed_slot_hours
FROM `your-project.region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND (statement_type IS NULL OR statement_type != 'SCRIPT')
GROUP BY user_email, reservation_id
ORDER BY known_billed_tib DESC;

Use the job project and correct region qualifier; the execution location must match. Access to project-wide job metadata requires the relevant permissions. Google's JOBS reference explains the fields and the absent byte values for queries over tables with row-level access policies.

This is a workload diagnostic, not an invoice. Null bytes remain unknown, and consumed slot-hours do not equal purchased reservation capacity. Do not multiply every row by one on-demand rate: capacity-based usage and some operations have different billing rules. The creation-time window also differs from a billing-period reconciliation.

Trace the largest recurring groups to their purpose, then design a reporting model at the grain the dashboard actually needs. A daily campaign summary can serve many ordinary charts; event-level investigation can retain a separate path to raw data.

Include the cost of creating and refreshing the summary. In an illustrative workload, forty dashboard queries each scan 25 GiB per day: 1,000 GiB daily. If a daily summary build scans 100 GiB and each dashboard query then scans 0.25 GiB, total work becomes 110 GiB daily, an 89 percent reduction under those assumptions. Measure actual billed bytes before claiming that saving in your account.

The cheaper model still has to preserve the answer. Check dimensions, late updates, unknown attribution and refund handling. A summary that drops inconvenient records can appear fast and inexpensive because it no longer answers the original question.

Mistake 7: Putting a budget alert where a query control was needed

Google's cost-control guidance supports dry-run estimates and maximum bytes billed for on-demand queries. Set controls in the clients and job configurations that actually submit work. A limit used in one analyst's console does not automatically constrain a BI connector or generated SQL from another tool.

Avoid relying on LIMIT 100 to reduce scans of an unclustered table. Select the required columns and constrain the source dates. For date-partitioned reporting tables, a required partition filter can reject queries without the necessary partition predicate. Verify that the dashboard's filters reach the SQL in a form that actually prunes data.

Required filtering is not a maximum date range: a query can still ask for a very broad period. Pair model design with sensible execution limits and monitor recurring workloads.

For on-demand workloads, custom query quotas provide project and per-user safeguards. They are approximate, not exact dollar caps. The per-user setting applies separately to all users and service accounts, not as a unique custom quota for one named analyst.

Cloud Billing's current spend-cap budget documentation covers selected services and does not list BigQuery. Do not assume a budget feature for another Cloud product stops warehouse usage. Combine the appropriate query or reservation controls with billing visibility and a named response owner. Our BigQuery pricing guide explains the different billing units.

Mistake 8: Handing over a dashboard without its dependencies

The implementation owner should be able to identify the source link, export identity, job identities, billing project, locations, models, schedules and consumers. Avoid an undocumented chain of resources owned by the person who happened to set them up.

Check the identity shown on the GA4 link itself. New links use a built-in resource identity; older links may still use the legacy service account. Inspect the actual link before removing an unfamiliar identity or copying old IAM instructions.

Test a scheduled run under its real service identity, including reading source data, creating or replacing the intended output and serving the BI consumer. Do not accept a developer's broader permissions as proof that production works.

Use a handover exercise that another team member can perform: identify a missing date, locate the relevant failed job, repair a nonproduction test input and rerun the affected model without duplication. Record how to pause a bad downstream refresh and how to restore the last accepted result.

An implementation estimate should reserve time for these checks. A first-source proof of concept and a production marketing warehouse are different scopes. Estimate the work from source access, model complexity, historical repair and validation requirements, then specify the acceptance evidence for each milestone. A fixed launch date cannot make unavailable raw history recoverable.

If the setup is already messy, start with one consequential report and trace its dependencies end to end. Our data warehouse and CDP implementation work can turn that investigation into a scoped repair: accepted data coverage, reconciled models, usable cost controls and a recovery procedure the team owns.