BigQuery vs PostgreSQL
| Decision | BigQuery | PostgreSQL |
|---|---|---|
| Best fit | Large analytical scans, Google Cloud workflows and low-ops warehousing. | Application-backed reporting, relational workflows and moderate datasets. |
| Loading pattern | Append or merge partitioned tables. | Upsert dimensions and append daily facts with controlled indexes. |
| Cost posture | Storage plus query processing; partitioning matters. | Provisioned compute and storage; maintenance and indexes matter. |
| Core requirement | Preserve raw data, stable IDs, effective dates, source definitions and reconciled totals. | |
Recommended data layers
Raw landing tables
Append the exact extracted fields with account ID, requested date range, API version and extraction timestamp.
Entity dimensions
Store customer, campaign, ad-group, asset and conversion-action IDs separately from mutable names and statuses.
Daily performance facts
Choose and document grain—such as account, campaign, date and device—before adding metrics.
History snapshots
Record name, status and configuration changes with effective dates so old reports can be reproduced.
Reporting marts
Build opinionated views only after raw and normalized layers reconcile to the source.
What to preserve
Identity
Customer, campaign, ad group, criterion, asset and conversion-action IDs.
State
Name, status, serving state, labels, budget and bidding configuration with effective dates.
Performance
Impressions, clicks, cost, conversions, values and the segmentation fields used in decisions.
Lineage
Query definition, API version, extraction time, currency, time zone and transformation version.
Deleted and paused campaigns
Never delete a warehouse dimension because the source entity is removed, paused or no longer returned by a default active-only view. Retain the stable ID, last known attributes and an explicit status. Historical fact rows should continue joining to the version of the entity that applied when the activity occurred.
Do not backfill mutable names over history
If Campaign A is renamed, old performance should remain attributable to the same campaign ID while the reporting layer can show either the historical name or current name deliberately.
Refresh and reconciliation
- Reload recent dates to capture conversion lag and attribution adjustments.
- Use idempotent keys so reruns do not duplicate fact rows.
- Compare cost, clicks and conversions with a documented source view.
- Alert on missing accounts, late extracts, rejected rows and schema changes.
- Archive raw extracts before changing transformations.
- Apply access controls and retention rules appropriate to customer and conversion data.
FAQ
Should deleted and paused Google Ads campaigns stay in the warehouse?
Yes. Preserve historical entities with their status and effective dates so prior reporting remains reproducible after campaigns change or disappear from active views.
Is BigQuery or PostgreSQL better for Google Ads data?
BigQuery is convenient for large analytical workloads and Google Cloud pipelines. PostgreSQL is flexible for application-backed reporting and moderate data volumes. The durable schema and reconciliation process matter more than the brand of database.
Protect the history before building the dashboard
Start with the Google Ads data-retention checklist, then connect the warehouse to a stable cross-channel budgeting workflow.