Data Engineer

About this role

JOIN HOTSPEX MEDIA!

🙌 #1 Ranked Media Buying and Planning Agency on Clutch.co http://Clutch.co

🔥 Finalist 'Best AI Tool', 2024 Digiday Technology Awards

🎉 Hybrid Work Model (1 Day in Office / Week)

🥇 Winner of Waterstone Canada's Most Admired Corporate Cultures

- Reports to: Director of AI & ML

- Location: Hybrid with the option for Remote if Outside Greater Toronto Area (must be legally authorized to work in and based in Canada)

- Team: Small, high-autonomy team with direct access to leadership.

- Impact: Owns design, build, operation of Hotspex's data transformation and storage layer.

ABOUT THE ROLE & MISSION

- Connect data across BigQuery, Postgres, and Airtable; expose clean datasets to AI, Workflow, Analytics consumers

- Build and maintain dbt models transforming marketing platform data into conformed dimensional schemas (Kimball facts/dimensions)

- Own SQL surface: queries, stored procedures, views, materialized views, scheduled routines

- Optimize warehouse performance and cost: query tuning, partitioning, clustering, incremental models

- Orchestrate pipelines with Airflow or similar

CORE COMPETENCIES

- SQL Engineering: Writes, tunes, maintains complex SQL across BigQuery and Postgres

- Stored Procedures & Routines: Designs and owns stored procedures, scripted procedures, UDFs, scheduled jobs

- dbt / Transformation Modeling: Builds and maintains dbt models with tests, docs, incremental patterns

- Pipeline Orchestration: Schedules and monitors pipelines via Airflow or similar

- Cross-Functional Partnership: Delivers consumable data products for AI, Workflow, Analytics

JOB SPECIFIC COMPETENCIES

- Advanced SQL: Complex joins, window functions, CTEs, query optimization, execution plans on BigQuery and Postgres

- Stored Procedures & Routines: Production stored procedures, scripted procedures (BigQuery scripting / PL/pgSQL), UDFs, scheduled queries with error handling, idempotency, observability

- dbt Modeling: Sources, staging, intermediate, marts; tests; documentation; incremental strategies; macros

- Pipeline Orchestration: Airflow, Dagster, Prefect, or equivalent

- Data Modeling: Kimball facts/dimensions, slowly changing dimensions, conformed schemas

- Warehouse Optimization: Partitioning, clustering, materialized views, cost tuning on BigQuery

- Airtable Integration: Schema mapping, sync patterns, base-as-source

JOB RESPONSIBILITIES

CONNECT & OPTIMIZE DATA

- 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

SQL & STORED PROCEDURE OWNERSHIP

- 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

DBT MODEL BUILD & MAINTENANCE

- 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

PIPELINE ORCHESTRATION

- 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

DATA QUALITY, MONITORING & RELIABILITY

- 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

CROSS-FUNCTIONAL PARTNERSHIP

- 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

- Owns: SQL design, stored procedure logic, transformation modeling, performance choices

- Does not own: automation logic (Workflow Eng), AI service code (Jr AI Eng), client-facing strategy

DOCUMENTATION & KNOWLEDGE

- Use Claude Code for stored procedure docs, model READMEs, schema references

- Version-controlled repos, clean Markdown, proper Git hygiene

- Document data contracts: ingestion → transformation → consumption

CONTINUOUS IMPROVEMENT

- 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

EXPLICITLY OUT OF SCOPE

- n8n automation design and ownership (Workflow Automation Engineer)

- Rust service development, RAG pipelines, embedding models (Junior AI Engineer)

- Looker dashboard authoring and LookML feature development

- Strategic analytics presentations to leadership

- ML model engineering, training, prompt engineering as a discipline

REQUIRED QUALIFICATIONS

- 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…