Data Engineering Pipelines

This data engineering project implements a closed-loop Google Ads attribution pipeline: web conversion events (purchases with GCLIDs) stored in PostgreSQL are transformed by a dbt model and uploaded back to Google Ads via the Conversion Upload API, enabling the ad platform to see which clicks led to real purchases. Two Airflow DAGs run on separate schedules — dbt refreshes hourly, the upload job runs daily. The entire stack is containerized with Docker using Astronomer's production-ready Airflow runtime.

Google Ads campaigns optimize for clicks by default. Without conversion data sent back to the platform, bid algorithms have no signal about which clicks actually produce revenue. The engineering challenge was building a reliable pipeline that: (1) correctly maps internal event data to Google Ads' conversion schema; (2) handles OAuth2 authentication in a containerized environment; (3) separates transformation from upload so each can fail and retry independently.

Build a production-ready ETL pipeline that: (1) extracts purchase events from PostgreSQL; (2) transforms them into Google Ads conversion format using dbt; (3) uploads to Google Ads Conversion API daily; (4) orchestrates with Airflow on separate schedules for transformation vs. upload; (5) runs reproducibly in Docker with pytest-validated DAGs.

• ◈Solo data engineer — pipeline architecture, dbt modeling, Airflow DAG authorship, Docker deployment

1. 01Separate DAG schedules for dbt (hourly) and upload (daily) — transformation and upload have different optimal frequencies. dbt runs hourly to keep the VIEW fresh as new conversion events arrive. The Google Ads upload runs daily to batch conversions efficiently and stay within API quota limits. Separating them means a dbt failure doesn't block the upload, and vice versa.
2. 02dbt model materialized as VIEW not TABLE — the purchase_clicks model is a SELECT with a WHERE event_name = 'purchase' filter. Materializing as a VIEW means no storage overhead and the source data is always reflected without running dbt refresh explicitly. The upload DAG queries the view directly; if source data changes, the view is automatically up-to-date.
3. 03BashOperator for dbt invocation instead of DbtOperator — gives full control over dbt flags and allows sourcing the virtual environment explicitly. The command structure (source venv + dbt run --project-dir) is more portable than a custom operator and easier to debug when dbt errors need to be read from Airflow logs.
4. 04OAuth2 InstalledAppFlow for Google Ads auth in a container — credentials (client_secret JSON + service account JSON) are copied into the container at build time and their paths set as environment variables. This avoids interactive OAuth flows at runtime while keeping credentials out of the DAG code.
5. 05Astronomer Runtime 10.6.0 as the base image — Astronomer's pre-built runtime includes Airflow providers, dbt-core, and common dependencies. Using it as the base (FROM quay.io/astronomer/astro-runtime:10.6.0) reduces the Dockerfile to ~10 lines and ensures the local dev environment matches production exactly.
6. 06pytest DAG validation as CI gate — three invariants enforced: no import errors (the DAG file must be parseable), tags present (required for Airflow UI filtering in production), retries ≥ 2 (any production DAG that calls an external API must have retry logic). This runs in CI before any DAG is deployed.

• →Separation of concerns between transformation and loading DAGs is essential for reliability — a monolithic DAG that runs dbt then uploads failed atomically when either step had a transient error. Separate DAGs with independent retry policies mean a Google Ads API 429 doesn't re-run the dbt transformation.
• →GCLID-based attribution requires careful data freshness management — GCLIDs expire after 90 days. Uploading conversions older than 90 days silently fails in the Google Ads API without an error response. Adding a WHERE conversion_datetime >= NOW() - INTERVAL '90 days' guard to the dbt model prevents silent attribution loss.
• →Astronomer's Astro CLI significantly reduces local Airflow setup complexity — astro dev start spins up 4 containers (webserver, scheduler, triggerer, Postgres) with one command. Without it, a vanilla docker-compose setup for Airflow requires 20+ environment variable configurations. The CLI is the right abstraction for iterating on DAGs.
• →pytest DAG validation catches import errors that would otherwise surface only in production — one DAG had a typo in an import path that only appeared when Airflow tried to parse the file at runtime. The pytest import-level test catches this in the local dev cycle before the DAG is deployed.

Functional — ETL pipeline for Google Ads conversion attribution