Join Hotspex Media!

🙌 #1 Ranked Media Buying and Planning Agency on 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 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

Strongly Preferred

• 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

Nice to Have

• Looker / LookML exposure (consumer-side; not required to own)

• n8n or other workflow orchestrators

• RAG / vector search data prep

• Agency, media, or analytics domain

Technology Stack

• Languages: SQL (advanced), Python (or equivalent), optionally JavaScript for dbt/BigQuery UDFs

• Data: BigQuery, Postgres, Airtable, dbt, Redis (cache awareness)

• Orchestration: Airflow (or Dagster, Prefect, Cloud Composer), dbt Cloud Run

• Integration consumer-side: n8n

• Cloud: GCP

• Observability: Cloud Monitoring, Looker (consumer-side)

• Tools: Linear, GitHub, Claude Code, Cursor

What this Role is NOT

• Not a data analyst — no ad-hoc analysis, dashboarding, stakeholder reporting

• Not analytics engineering / dashboards — LookML and Looker dashboards not owned

• Not workflow automation — n8n belongs to Workflow Automation Engineer

• Not ML / AI engineering — model development belongs to AI team

• Not a DBA — no infrastructure provisioning or cluster management

Our Values:

We know our people are what allows us to achieve all that we do and that’s why it’s important that everyone we bring onto our team lives our values with us. 

• 🦁 Courage

• 1️⃣ One Team

• 💪 Resilience

• 🚀 Empowerment

Hotspex Media Inc. is an equal opportunity employer and values diversity in its workforce. Due to the large volume of applications received, Hotspex Media may, from time to time, use artificial intelligence to optimize screening efforts.