Databases & Query Optimization
How your ad and order data is stored, why reports can be slow or costly to run, and how to keep them fast and cheap — no engineering background needed.
Articles in this collection
11 articlesThe GA4 Database Export Schema, Explained Field by Field for Marketers
A marketer's guide to GA4 export's nested table structure — what event_params actually holds, and how to query it without a data engineering background.
Partitioning and Clustering, Explained With One Marketing Table
Walk through what partitioning and clustering actually do to a query, using a single GA4-style events table as the running example.
Google Ads Data Transfer vs. the API: Which One Your Reporting Should Use
Two ways to get Google Ads data into BigQuery, with very different freshness, cost and maintenance tradeoffs. A practical comparison for choosing one.
One Currency, One Timezone: Normalising Multi-Account Reporting Before It Lies to You
USD and INR spend in one column, UTC and account-timezone days in one join. The exchange-rate table, the convert-then-truncate rule, and the view every report should read.
Why Your Revenue Doubled Overnight: Deduplicating Orders and Events in SQL
Ingestion doubles, fan-out joins and grain confusion multiply rows three different ways. The diagnostics that catch each and the dedup views that prevent them.
Stale Dashboards Lie Confidently: Freshness Checks That Catch a Broken Pipeline First
A credential expired and the Monday review debated a demand collapse that never happened. Three layers of scheduled validation queries — freshness, shape, reconciliation.
Your First 10 BigQuery Cost Guardrails — Before the Bill, Not After
Ten controls you can put in place in an afternoon, ordered by how much they save per hour of effort. Custom quotas, maximum bytes billed, partition requirements, and the ones people skip.
Incremental Models Without dbt: Scheduled Queries That Don't Re-Scan the Year
You do not need a transformation framework to stop rebuilding a rolling table from scratch every morning. MERGE, a watermark, and a late-arriving-data window — in plain scheduled SQL.
Joining Ad Spend to Orders When There's No Common Key
Campaign-level spend and row-level orders share no id. Here are the three join grains that actually work, the fan-out bug that ruins the naive version, and the SQL for each.
Postgres vs. a Warehouse for Marketing Reporting: When to Stop Querying Prod
Your production database can carry marketing reporting further than most people assume — and then it cannot. The four signals that mean it is time, and the cheapest thing to do next.
How to Read Database Query Plans and Stop the $400 Surprise Bill
Cloud databases and warehouses bill by data scanned and compute used, not rows returned. Here is how to inspect query estimates before running, and the three habits that cut query costs 10-100x.