Transit TechExpert-built kit

Data Warehouse / Analytics Engineer

Designs dimensional data models, writes incremental transformation scripts for daily metrics, and optimizes warehouse queries.

Buy this kit · $29.99Preview questions

Interview content for Mid

29
What to ask. Competency and attitude questions, assigned to the right round.
212
What to listen for. Written indicators to credit and discount, per question.
8
What the hire must do. Capabilities with expected proficiency at each level.

Look inside: one question, as it appears in the kit

Pick the level you’re hiring. The sample changes with the level you select.

Round 2 · Hiring Manager Technical Deep Dive19 competency questions

Data Architecture & Engineering

Data Modeling & Schema Design

Independently designs and iterates on star and snowflake schemas for specific business domains, ensuring modular, reusable, and well-documented data structures.

Expected at Mid

Sample competency question

Describe a situation where you designed a new schema for a complex business domain. How did you approach defining the grain and structuring the relationships?

Ask once, as written, then allow silence. A helpful rephrase may hand the candidate the answer.

Credit

  • Mentions grain definition upfront
  • Describes version control usage
  • References stakeholder feedback loops
  • Notes minimal post-launch fixes

Discount

  • Jumps straight into table creation without planning
  • Ignores historical data tracking
  • Lacks mention of review processes
  • Focuses only on technical syntax

The Analytics Engineer must independently handle normal role-scope schema design and iteration without relying on senior oversight, directly supporting the level's mandate to own specific data domains.

Ryan Mahoney

Why this role is hard · Ryan Mahoney

Finding analytics engineers who can deliver quickly without sacrificing data quality is tough at this level. We need people who can build solid data models, set up reliable transformation workflows, and walk stakeholders through the tradeoffs without getting defensive. Most candidates swing too far one way, either overcomplicating their schemas until they buckle under real data loads, or rushing out brittle pipelines that fall apart the moment reporting needs change. To see where they actually land, I ask them to walk through a past refactoring and pay close attention to whether they considered both query speed and future upkeep.

Everything in the download, in the order you’ll use it

Level guides for Junior, Mid, Senior and Principal.

Before you post

  • 1Ready-to-use job description
  • 2Video screening prompts
  • 8Resume screening criteria
  • 2Knockout screening questions

In the room

  • 19Competency interview questions
  • 10Attitude interview questions
  • 1Hands-on work simulations
  • 1Presentation prompts
  • 2Coding tests

At the debrief

  • Progression framework
  • Exceeds / Meets / Below anchors for every exercise
  • 4Interview plan with time per round

Core Evaluation

Critical questions for this role

The competency and attitude questions below are where the hiring decision is made. They run in the live interview rounds and are calibrated to the level selected above.

19 Competency Questions

1 of 19

  1. Discipline

    Data Architecture & Engineering

  2. Job requirement

    Data Modeling & Schema Design

    Independently designs and iterates on star and snowflake schemas for specific business domains, ensuring modular, reusable, and well-documented data structures.

  3. Expected at Mid

    The Analytics Engineer must independently handle normal role-scope schema design and iteration without relying on senior oversight, directly supporting the level's mandate to own specific data domains.

Interview round: Hiring Manager Technical Deep Dive

Describe a situation where you designed a new schema for a complex business domain. How did you approach defining the grain and structuring the relationships?

Positive indicators

  • Mentions grain definition upfront
  • Describes version control usage
  • References stakeholder feedback loops
  • Notes minimal post-launch fixes

Negative indicators

  • Jumps straight into table creation without planning
  • Ignores historical data tracking
  • Lacks mention of review processes
  • Focuses only on technical syntax

10 Attitude Questions

1 of 10

Active Listening

The deliberate and structured practice of fully concentrating on, comprehending, and retaining stakeholder, domain expert, and operational feedback to accurately translate qualitative business needs, edge cases, and implicit constraints into robust data architectures, transformation logic, and analytical models. It involves suspending immediate technical assumptions, probing for underlying operational realities, and continuously validating understanding before implementing technical solutions.

Interview round: Recruiter Screen

How would you handle a scenario where a service planner repeatedly requests a metric that conflicts with established financial reporting definitions?

Positive indicators

  • Prioritizes understanding intent over enforcing rules
  • Suggests structured reconciliation approaches
  • Mentions involving both teams in the resolution
  • Focuses on traceable definition alignment

Negative indicators

  • Immediately rejects the request as incorrect
  • Assumes one team is always right without investigation
  • Creates a duplicate metric without documentation
  • Escalates conflict without attempting alignment

Supporting Evaluation

How candidates earn the selection conversation

The goal is to reduce effort for everyone by collecting more useful signals before adding more interviews. Lightweight application prompts and structured screens help your team focus interview time on the candidates most likely to succeed.

Stage 1 · Application

Filter at the door

Runs the moment a candidate hits Submit. Disqualifying answers end the application; everything else is captured for review.

Knock-out Questions

1 of 2

Application Screen: Knock-out

Have you professionally designed and maintained production analytics pipelines that ingest and process GTFS/GTFS-RT transit data formats or reconcile data against NTD reporting standards?

Yes
Qualifies
No
Auto-decline

Video-Response Questions

1 of 2

Application Screen: Video Response

Describe a time you had to explain complex data modeling constraints or schema limitations to a non-technical stakeholder who needed a specific feature urgently. How did you translate the technical trade-offs into business terms, and what was the outcome?

Candidate experience

REC
0:42 / 2:00
1Record
2Review
3Submit

Response time

2 min

Format

Recorded video

Stage 2 · Resume Screening

Read the resume against fixed criteria

Reviewers score every application that clears the door against the same criteria. Stronger reviews advance to live interviews; weaker ones are archived without further screening.

Resume Review Criteria

8 criteria
Owns end-to-end delivery of business-critical datasets, optimizing query performance and managing incremental loads.
Builds and maintains streaming pipelines for high-frequency operational data, handling binary or protobuf formats.
Implements systematic data quality checks and compliance validation for regulatory or financial reporting.
Translates legacy or vendor-specific data into standardized models while coordinating with non-technical teams.

Does the resume show relevant prior work experience?

Does the cover letter or personal statement convey clear relevance and familiarity with the job?

Does the resume indicate required academic credentials, relevant certifications, or necessary training?

Is the resume complete, well-organized, and free from formatting, spelling, and grammar mistakes?

Stage 3 · During Interviews

Where the hire is decided

Interview rounds use the competency and attitude questions outlined above, then add tests, work simulations, and presentations that reveal deeper evidence about how the candidate thinks and works.

Coding Test

1 of 2

Live Interview · Coding Test

Without AI

Write an incremental SQL model that aggregates daily trips. Handle late-arriving records and explain your partitioning and merge strategy.

Create an incremental model `fct_daily_trips` from `stg_trips`. Explain how you handle records arriving after the daily partition closes, how you define the incremental filter, and how you manage schema changes from upstream feeds.

With AI

Draft the incremental model using AI, then critique its assumptions around data latency, partition boundaries, and upstream schema changes. Document your corrections.

Generate an incremental SQL aggregation model for daily trips. Evaluate the AI's approach to late-arriving data, partitioning, and schema evolution. Adjust the logic for transit operational realities and document your changes.

Response time

20 min

Positive indicators

  • Correct use of is_incremental() or partition filtering
  • Idempotent merge/upsert logic
  • Clear strategy for late data and schema drift
  • Performance-aware partitioning
  • Identifies AI's default assumptions (e.g., perfect on-time data)
  • Adds explicit late-arrival windows and reconciliation steps
  • Documents schema evolution handling (e.g., column drops/adds)

Negative indicators

  • Full table scans on every run
  • No handling of late-arriving records
  • Ignores schema evolution impact
  • Missing merge conditions
  • Blindly accepts AI incremental logic
  • No consideration for transit-specific latency patterns
  • Fails to address upstream schema drift

Presentation Prompt

Prepare a short deck walking us through a past project where you owned end-to-end delivery of a business-critical dataset or pipeline. Discuss your approach to balancing rapid delivery with rigorous data quality testing, lineage documentation, and stakeholder alignment.

Format

deck-and-walkthrough · 20 min · ~2 hr prep

Audience

Hiring panel (Analytics Engineering Lead, Product Manager)

What to prepare

  • 3-5 slides summarizing the project context, technical approach, and outcomes
  • Brief notes on specific tradeoffs you made during delivery and how you validated them

Deliverables

  • A structured narrative walkthrough of your past work, decision-making process, and lessons learned

Ground rules

  • Use only work you are permitted to share; anonymize sensitive data or client details as needed
  • Focus on your reasoning, tradeoff evaluation, and cross-functional collaboration rather than just technical outputs
  • Do not build new strategic artifacts; this is a retrospective walkthrough of your actual experience

Scoring anchors

Exceeds
Delivers a compelling narrative that balances technical rigor with business value, explicitly details quality/testing frameworks, and shows mature reflection on stakeholder management and iterative improvement.
Meets
Provides a clear, structured walkthrough of a relevant pipeline project, covers key tradeoffs, and demonstrates solid ownership of delivery and documentation.
Below
Offers a superficial or disorganized account, lacks depth on data quality/testing, or fails to connect technical work to business outcomes and stakeholder needs.

Response time

20 min

Positive indicators

  • Clearly articulates the tension between delivery speed and data quality, explaining how both were managed
  • Provides concrete examples of lineage documentation, testing frameworks, and monitoring implemented
  • Demonstrates proactive stakeholder communication and expectation management throughout the project lifecycle
  • Reflects honestly on lessons learned and explains how they would adjust their approach today

Negative indicators

  • Presents a purely technical overview without discussing business impact or stakeholder alignment
  • Glosses over data quality failures, testing gaps, or pipeline instability
  • Fails to explain tradeoffs or rationale behind tooling and architecture choices
  • Takes sole credit without acknowledging team collaboration, code review, or cross-functional dependencies

Work Simulation Scenario

Scenario. You own the pipeline for a core transit domain (fare transactions). The team needs to implement an incremental dbt model for daily trip aggregation that handles late-arriving data, deduplicates overlapping tap events, and aligns with finance settlement cycles.

Problem to solve. Design an incremental processing strategy that balances data freshness with financial accuracy, defines deduplication logic, and establishes data quality tests.

Format

discovery-interview · 35 min · ~2 hr prep

Success criteria

  • Clearly defines incremental partition strategy and handles late-arriving records
  • Proposes idempotent deduplication logic for faulty reader events
  • Outlines concrete Great Expectations/dbt tests and discusses batch vs near-real-time trade-offs

What to review beforehand

  • Incremental dbt patterns and idempotency principles
  • Data quality testing frameworks and transit fare lifecycle basics

Ground rules

  • Drive the discussion with clarifying questions about SLAs, error rates, and reconciliation windows.
  • The partner answers honestly but only when prompted.
  • Focus on pipeline architecture, trade-offs, and downstream impact.

Roles in scenario

Hiring Manager (informed_partner, played by hiring_manager)

Motivation. Ensure the pipeline is robust, auditable, and doesn't break month-end financial reconciliation while supporting near-real-time dashboards.

Constraints

  • Settlement files arrive with a strict 48-hour lag
  • Approximately 3-5% of tap events are duplicated by faulty hardware readers

Tensions to introduce

  • Partner will push for real-time dashboard updates despite the financial lag constraint
  • Partner will express concern about pipeline runtime costs if full-refresh fallbacks are proposed

In-character guidance

  • Provides specific lag times, error rates, and budget constraints when asked
  • Answers factually about current pipeline pain points without volunteering solutions
  • Maintains focus on financial accuracy and operational reliability

Do not

  • Do not give away the optimal partition strategy or deduplication logic
  • Do not write the dbt config or solve the reconciliation math
  • Do not lead the candidate or coach them toward a specific tooling choice

Scoring anchors

Exceeds
Anticipates downstream impacts, designs a robust idempotent pipeline with clear SLA trade-offs, and establishes comprehensive automated validation.
Meets
Solid incremental strategy, addresses deduplication and late data, and proposes practical testing and monitoring steps.
Below
Relies on inefficient full refreshes, ignores data quality/reconciliation needs, fails to address late data, or freezes under technical ambiguity.

Response time

35 min

Positive indicators

  • Asks about SLA vs accuracy trade-offs and defines clear incremental boundaries
  • Designs for idempotency and explicitly addresses late-arriving record handling
  • Proposes a concrete testing strategy (e.g., unique key checks, reconciliation counts)
  • Balances technical constraints with downstream stakeholder expectations clearly

Negative indicators

  • Proposes full-refresh strategies for large transactional tables without justification
  • Ignores financial reconciliation needs or late-data edge cases
  • Lacks a structured testing or monitoring plan for pipeline failures
  • Guesses partition strategies or SLA requirements without asking

Progression Framework

This table shows how competencies evolve across experience levels. Each cell shows competency at that level.

Data Architecture & Engineering

4 competencies

CompetencyJuniorMidSeniorPrincipal
Data Modeling & Schema Design

Implements predefined data models and assists with basic table creation under supervision, ensuring alignment with established dimensional standards.

Independently designs and iterates on star and snowflake schemas for specific business domains, ensuring modular, reusable, and well-documented data structures.

Architects enterprise-wide data models, establishes modeling standards, and resolves complex normalization trade-offs.

Defines strategic modeling frameworks, evaluates next-gen paradigms, and aligns models with long-term business architecture.

Infrastructure & Cloud Platform Strategy

Provisions basic cloud resources and monitors platform health dashboards to ensure stable data warehouse operations under supervision.

Manages warehouse clusters, configures access controls, and implements infrastructure-as-code templates for domain-specific deployments.

Designs secure, scalable cloud architectures, optimizes multi-environment deployments, and leads platform migrations.

Defines enterprise cloud data strategy, negotiates vendor roadmaps, and ensures platform resilience across global regions.

Performance Tuning & Query Optimization

Identifies slow-running queries and applies basic indexing or partitioning recommendations to support routine performance maintenance.

Analyzes execution plans, optimizes joins/aggregations, and tunes warehouse configurations to balance query speed and compute cost for analytical workloads.

Leads performance audits across large datasets, implements advanced materialization strategies, and resolves systemic bottlenecks.

Architects performance tuning frameworks, establishes cost-governance policies, and drives platform-wide optimization initiatives.

Pipeline Development & Orchestration

Writes basic transformation scripts and configures simple scheduled jobs to ensure reliable data movement and freshness within established pipeline frameworks.

Develops robust, idempotent pipelines using orchestration tools, manages incremental loads, and ensures reliable end-to-end delivery of business-critical datasets.

Designs scalable DAGs, implements complex error handling, and optimizes pipeline throughput.

Establishes enterprise pipeline architecture, drives automation strategies, and mentors teams on workflow best practices.

Data Governance, Integration & Advanced Analytics

4 competencies

CompetencyJuniorMidSeniorPrincipal
API Integration & Real-time Data Streaming

Consumes basic APIs and assists with simple streaming data ingestion scripts to support real-time and batch integration workflows.

Builds reliable API integrations, manages authentication, and configures stream processors for near-real-time data ingestion and downstream exposure.

Designs scalable API gateways for data exposure, implements complex event-driven architectures, and ensures low-latency delivery.

Defines enterprise integration strategy for real-time data ecosystems, evaluates emerging streaming tech, and aligns APIs with product roadmaps.

Data Quality & Validation

Runs predefined data quality checks, documents anomalies, and executes basic validation routines to maintain data accuracy and reliability.

Develops automated validation rules, profiles datasets, and establishes baseline quality metrics to ensure data accuracy, completeness, and reliability.

Architects comprehensive DQ frameworks, integrates quality gates into CI/CD, and leads root-cause analysis.

Defines organizational data quality strategy, sets enterprise SLAs for reliability, and aligns DQ with business risk management.

Metadata Management & Data Cataloging

Documents table schemas, maintains basic metadata entries in shared repositories, and supports catalog discoverability for existing data assets.

Implements automated lineage tracking, curates data dictionaries, and enhances catalog discoverability to support self-service analytical consumption.

Architects metadata management systems, establishes governance workflows, and integrates cataloging with discovery tools.

Drives metadata strategy as a foundational asset, aligns cataloging with compliance mandates, and scales self-service data culture.

Predictive Modeling & Advanced Analytics Support

Extracts and cleans datasets for data science teams and maintains basic feature pipelines under established engineering guidelines.

Builds feature stores, automates model training data refreshes, and bridges engineering with ML workflows for predictive use cases.

Architects production-ready MLOps infrastructure, optimizes feature engineering at scale, and ensures model reproducibility.

Strategizes advanced analytics enablement, aligns ML infrastructure with business AI initiatives, and drives cross-functional partnerships.