Most growth teams hit the same wall at some point.
Reports are slow. Numbers don't match. The analyst spends half their time pulling data instead of using it.
The root cause is usually the same: the data infrastructure was never properly built. Exports are manual. Pipelines are fragile scripts nobody fully understands. The warehouse has raw tables that half the team queries differently.
Data engineering is the work of fixing that foundation.
What this actually involves
The core work breaks into a few areas.
Extraction. Getting data out of source systems - ad platforms, CRMs, databases, emails, APIs - reliably and on a schedule.
Loading and transformation. Getting that data into a warehouse (BigQuery, Redshift, Snowflake) and modeling it so it answers real questions at the right grain.
Orchestration. Making sure the pipelines run in the right order, at the right time, and alert you when something fails.
Validation. Checking that what landed in the warehouse actually matches what the source reported.
Reporting surface. Getting the modeled data in front of the people who need it - whether that's a dashboard, a sheet, or an internal tool.
Each layer depends on the one before it. A great dashboard backed by unreliable data is worse than no dashboard.
How I build pipelines for marketing analytics
Most of the projects I work on are some variation of this:
One or more ad platforms (Google Ads, Meta, DV360, LinkedIn) need to land in BigQuery. The raw data needs to be modeled into reporting-ready tables. A dashboard or sheet needs to show spend, performance, and attribution in a way the team actually uses.
The pattern that works:
Source-specific extraction. Each platform has its own extraction module. Meta's async API is different from Google Ads' streaming API, which is different from GA4's native BigQuery export. Trying to handle them all with a generic connector usually breaks on edge cases.
Staging tables. Raw data lands in a staging table first. No business logic yet. Just what the API returned, plus metadata (load time, source run ID).
Transformation layer. Dataform or dbt turns staging tables into modeled tables. Explicit grain, spend reconciliation assertions, deduplication logic, and attribution window handling.
Validation before the dashboard sees it. Spend totals in BigQuery should match the platform's own reporting for the same period. Row counts should be in expected range. If they are not, the pipeline stops and alerts.
Dashboard or reporting surface. Once the numbers are trusted, you can put them anywhere - Looker Studio, a custom Next.js dashboard, Google Sheets, or a BI tool.
The automation piece
Automation in data engineering is not just about scheduling.
It is about making the whole thing run without someone manually triggering it, watching it, or fixing it after every run.
Good automation means:
- The pipeline runs on a schedule (or on a trigger) without manual intervention
- Failures are caught early and surfaced clearly - not discovered three days later when someone notices the numbers stopped updating
- Reruns are safe - running the pipeline twice should not double the data
- Backfills work - if a source was down for three days, you can pull that window without breaking the current state
The tools I use for this depend on the project. For simpler pipelines, Cloud Run tasks triggered by Cloud Scheduler. For more complex orchestration with dependencies and retries, Airflow.
What keeps cost down
Two things drive cost in data engineering work more than anything else.
Undefined scope. If the sources, grain, output schema, and validation requirements are not clear before work starts, everything takes longer. I spend the first part of any engagement getting these defined before writing anything.
Starting too big. A two-source pipeline with basic validation is a much more contained project than a six-source warehouse with full modeling, reconciliation, and a custom dashboard. Starting with what is actually needed now and extending later is almost always the right path.
When to use managed connectors vs custom pipelines
Google Ads, GA4, and some other platforms have native BigQuery connectors (via BigQuery Data Transfer Service). When these exist and the report shape they produce fits the requirements, use them. They are free and they handle authentication, scheduling, and schema management.
Custom pipelines are necessary when:
- The platform does not have a native connector (Meta, LinkedIn, most affiliate networks)
- You need specific metrics, custom breakdowns, or cross-account aggregation the native connector does not support
- The update cadence or lookback logic needs to be different from the connector's defaults
There is no point building custom extraction for Google Ads when the native connector covers the requirements.
The warehouse modeling patterns that matter
A few patterns that come up on almost every marketing warehouse project:
Explicit grain. Every table should have a clearly defined grain. Campaign-day, ad group-week, creative-placement-day. If the grain is not defined, joins multiply rows in ways that are hard to catch until someone notices spend is doubled.
Spend reconciliation. The total spend in the warehouse for a given platform and date range should match the platform's own reporting. This sounds obvious, but most pipelines do not check this. When it fails, you catch it before the dashboard shows wrong numbers.
Attribution window as a column. If your pipeline pulls Meta data with a 7-day click window, that window needs to be stored as a column, not an assumption baked into the query. Otherwise, when the attribution window changes, old data looks different from new data with no explanation.
Merge, not append. Pipelines that append rows on each run accumulate duplicates when the same date range is pulled twice. Merging on a stable key (account + campaign + date + breakdown fields) keeps the table clean.
What I actually use
For most marketing analytics warehouse projects:
- Warehouse: BigQuery
- Transformation: Dataform (for GCP-native projects) or dbt (for clients already using it)
- Orchestration: Cloud Scheduler + Cloud Run for simple cases, Airflow for complex dependencies
- Dashboards: Looker Studio for quick reporting surfaces, Next.js for custom tools that need more control
- Language: Python for extraction and pipeline logic, SQL for transformation
The stack matters less than the modeling patterns. A well-modeled warehouse in any tool is more valuable than a poorly modeled one using the latest framework.
If you are building a marketing data pipeline or warehouse and want to talk through the scope, get in touch.