Data Engineer

Hotspex Media • Toronto, ON

15d • CA$85,000 - CA$115,000 • Hybrid

About The Position

Join Hotspex Media, a top-ranked media buying and planning agency! This role is part of a small, high-autonomy team with direct access to leadership, offering the chance to own the design, build, and operation of Hotspex's data transformation and storage layer. The mission is to connect data across BigQuery, Postgres, and Airtable, exposing clean datasets to AI, Workflow, and Analytics consumers. Responsibilities include building and maintaining dbt models, owning the SQL surface, optimizing warehouse performance and cost, and orchestrating pipelines.

Requirements

• 2+ years data engineering, analytics engineering, or database development
• Strong SQL — complex joins, window functions, CTEs, query optimization (must demonstrate)
• Hands-on stored procedure experience — production stored procedures (BigQuery scripted procedures, PL/pgSQL, T-SQL, PL/SQL, or equivalent). Non-negotiable.
• Working knowledge of dbt (or strong SQL/Git fundamentals to ramp quickly)
• Python or other scripting language for data tasks (Java, Scala, TypeScript also acceptable)
• Airflow or similar pipeline orchestration experience (Dagster, Prefect, dbt Cloud schedules, Cloud Composer)
• Dimensional modeling fundamentals — facts, dimensions, grain, conformed schemas
• Git fundamentals — branches, PRs, code review participation
• Documentation discipline — version-controlled Markdown

Nice To Haves

• BigQuery production experience (partitioning, clustering, scripted procedures, scheduled queries)
• Postgres production experience (PL/pgSQL, indexes, query plans)
• Airtable production experience (schema design, sync patterns, API integration)
• Production dbt experience (Cloud or Core)
• Marketing/advertising data sources (Google Ads, Meta, LinkedIn)
• AI tooling (Claude Code, Cursor, ChatGPT) as daily accelerator
• Looker / LookML exposure (consumer-side; not required to own)
• n8n or other workflow orchestrators
• RAG / vector search data prep
• Agency, media, or analytics domain

Responsibilities

• Own connectivity between BigQuery, Postgres, and Airtable; ensure consumers (AI, Workflow, Analytics) get the schema they need
• Refactor ad-hoc SQL into versioned, tested, documented routines
• Optimize cost and performance: partitioning, clustering, materialization
• Detect and fix performance regressions before downstream impact
• Own every production stored procedure, scripted procedure, scheduled query across BigQuery and Postgres
• Author new stored procedures for batch transforms, reporting routines, AI/ML feature prep
• Maintain stored-procedure inventory with ownership, dependencies, runbooks
• Design schemas and write dbt models transforming marketing platform data (Google Ads, Meta, LinkedIn, etc.) into conformed dimensional schemas
• Implement dbt tests (uniqueness, not-null, referential integrity, custom rules) on every production model
• Maintain incremental models for high-volume tables; tune for cost and freshness
• Own dbt documentation and lineage
• Schedule, monitor, and version pipelines in Airflow or similar
• Alert routing, retry policy, backfill patterns
• Coordinate with Workflow Eng on hand-off points between n8n and orchestrated data pipelines
• Implement automated tests (dbt tests, freshness checks, row-count anomaly detection)
• Detect and acknowledge data quality incidents within 1 business hour (SLA)
• Author runbooks for common failure modes
• Track and reduce incident frequency; report trends quarterly
• Partner with Workflow Automation Engineer on ingestion contracts: landing schemas, refresh patterns
• Partner with Junior AI Engineer on data needs for RAG, embeddings, AI services: feature tables, serving views
• Translate PM/CS and Product requirements into dimensional models
• Use AI tooling (Claude Code, Cursor) to accelerate SQL authoring, refactoring, documentation
• Track and report query cost reduction and model freshness improvement quarterly
• Resolve categories of technical debt: consolidating duplicated SQL, retiring shadow tables

Benefits

• Hybrid Work Model (1 Day in Office / Week)
• Option for Remote if Outside Greater Toronto Area (must be legally authorized to work in and based in Canada)

What This Job Offers

• Job Type: Full-time
• Career Level: Mid Level
• Education Level: No Education Listed