DEV Community

Cover image for dbt Mesh & Multi-Project Architecture: Cross-Team Lineage & Public Models
Gowtham Potureddi
Gowtham Potureddi

Posted on

dbt Mesh & Multi-Project Architecture: Cross-Team Lineage & Public Models

#python #sql #dataengineering #interview

dbt mesh is the multi-project architecture pattern that finally lets a 500-model dbt monolith split into per-domain projects without losing the cross-team lineage that made dbt worth adopting in the first place. Every analytics organisation eventually hits the same wall: one repository, three teams, one CI build, one broken commit that stalls every dashboard for six hours; a dbt_project.yml so overloaded that nobody remembers who owns stg_events_v3; a documentation site that lists 500 models under the same navigation tree. The dbt multi-project model — landed as a first-class feature in dbt-core 1.6 and matured in Cloud through 2024–2026 — reframes the monolith as a federation of smaller projects that expose an explicit, versioned API to each other via dbt public models, model contracts, and cross-project ref() calls.

This guide is the senior analytics-engineering walkthrough you wished existed the first time an interviewer asked "walk me through how you'd split a 500-model monolith into a mesh," "what does access: public do on a model and how does it differ from protected," or "how do you version a public model that fifteen downstream projects depend on without breaking any of them?" It covers why the monolith problem is fundamentally organisational rather than technical, the four "must-answer" axes senior interviewers probe (dbt project splitting, access levels, dbt contract, dbt version), the mechanics of access: public | protected | private, model contracts as an enforced column-type + nullability API, semantic versioning with deprecation windows, cross-project {{ ref('project_name', 'model_name') }} calls wired through dependencies.yml, dbt Cloud Explorer for cross-team lineage and downstream impact analysis, the Discovery API for programmatic governance, and the monolith-to-mesh migration path — extract-one-domain, publish-public-models, cutover-consumers — that senior leads actually ship. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

PipeCode blog header for dbt Mesh — bold white headline 'dbt Mesh' with subtitle 'Multi-Project Cross-Team Lineage' over three interlocking hexagonal project cells, a stylised lineage bridge, and a central purple 'public' seal on a dark gradient with purple and blue halos and a small pipecode.ai attribution.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the ETL practice library →, and sharpen the modelling axis with the optimization practice library →.

On this page

1. Why dbt Mesh landed in 2023-2026

The monolith problem is organisational, not technical — dbt mesh is the architecture answer

The one-sentence invariant: a dbt monolith beyond ~300 models stops scaling with the team, not the warehouse — one repository, one CI build, and one broken commit means every team on the project shares an outage budget, whether their models depend on the broken code or not. Warehouses have kept getting faster; Snowflake, BigQuery, and Databricks all cheerfully compile and materialise thousands of models in a single dbt run. The bottleneck migrated from the compute layer up into the coordination layer — code review queues, dependency conflicts, ownership ambiguity, and the "who owns stg_events?" question that has no answer when a team of forty commits to the same models/ folder.

dbt Mesh is the answer dbt Labs shipped between 2023 and 2026 to reframe the problem: instead of one project with 500 models, ship N projects with 50 models each, each owned by exactly one team, each exposing a small set of public models with column-level contracts, and each consumed by downstream projects via a cross-project ref() call that dbt Cloud tracks in Explorer as a first-class edge in the cross-team lineage graph. The architecture is the same one that saved microservices in the 2010s — bounded contexts, published APIs, versioned contracts — reapplied to analytics engineering.

The four axes interviewers actually probe.

  • Project split. How do you draw the boundary between projects? Domain-driven (sales, marketing, finance, product), source-driven (one project per source system), or layer-driven (staging vs marts)? Senior interviewers push hard on the reasoning — the boundary decision is the single most consequential choice in the mesh design.
  • Access levels. access: public | protected | private — the model-level config that decides who can ref() your model. Public is the API; protected is the group; private is the project. Getting the default wrong (accidentally exposing internal staging models as public) is the operational sin senior teams guard against.
  • Contracts. contract: {enforced: true} freezes the column names, types, and nullability at build time — a schema drift in the underlying SQL fails the build instead of silently corrupting downstream consumers. Contracts are the mechanism that turns a public model into a stable API.
  • Versioning. versions: block on a public model lets dim_customer exist as v1 and v2 simultaneously, with a deprecation window that gives downstream consumers time to migrate. Interviewers probe whether you understand deprecation as a migration protocol, not just a config flag.

Why the monolith stops scaling around 300 models.

  • CI time. A single dbt build run over 500 models with tests takes 45–90 minutes on the cheapest warehouse tier. Every pull request pays the full cost, so review queues clog on CI throughput long before code review capacity.
  • Ownership diffusion. With 500 models under one models/ folder, CODEOWNERS becomes a game of pattern matching against filename prefixes. Nobody has clear on-call for the whole thing; broken models sit in a Slack channel until someone volunteers.
  • Dependency conflicts. Team A adds a column to stg_orders; team B's mart breaks; team B has no idea A was touching that model until Slack pings arrive. There is no contract between the teams — the graph is one big undifferentiated DAG.
  • Documentation collapse. dbt docs generate on 500 models produces a docs site that is technically complete and practically unnavigable. The taxonomy is flat; consumers cannot tell public API from internal plumbing.
  • The 300-model threshold. Every organisation we've seen hits this wall between 200 and 400 models. The trigger is usually the second incident where team B's release blocked team A's dashboard. Beyond that point, the ROI on the mesh migration is measured in weeks, not quarters.

What dbt Mesh actually gives you.

  • Per-domain repositories. One repo per domain (sales, marketing, finance, product), each owned by one team, each with its own CI budget, each with its own release cadence.
  • A published API. Each domain exposes a small number of access: public models with contracts and versions. Downstream projects consume only the public surface; internal staging and intermediate models stay private to the owning team.
  • Cross-project lineage. {{ ref('project_name', 'model_name') }} is a first-class cross-project reference. dbt Cloud Explorer renders the lineage across projects as one continuous graph; the Discovery API answers "what breaks if I change this column" queries programmatically.
  • Slim CI at the project level. Each project runs its own CI. Changes to a public model trigger downstream project CI via a contract check — not a full monolithic rebuild.
  • Ownership as code. CODEOWNERS at the project level, plus dbt-level owner metadata on public models, means every model has a clearly named team on the hook for incidents.

What interviewers listen for.

  • Do you say "the bottleneck is coordination, not compute" when asked why the mesh matters? — senior signal.
  • Do you frame access: public as "an API contract, not a permissions flag"? — senior signal.
  • Do you push back on "why not just split into folders?" with the CI-time and ownership arguments? — required answer.
  • Do you describe the mesh migration as "extract one domain first, publish the public models, cut over consumers, repeat" — never as a big-bang split? — required answer.

Worked example — one broken commit stalls three teams

Detailed explanation. The textbook monolith failure mode: a team-A engineer refactors stg_orders.sql, drops a column that team-B's mart quietly depended on, and merges through CI because team-A's tests don't cover the downstream mart. The next dbt build run in production fails at team-B's mart, which team-C's finance dashboard depends on. Three teams are now blocked on one team-A commit. Walk an interviewer through what actually happened and what a mesh split would have caught earlier.

  • The symptom. Production dbt build fails at mart_finance_daily with column "order_amount" does not exist.
  • The commit. Team-A merged a refactor of stg_orders.sql that renamed order_amount to gross_amount.
  • The blast radius. Team-B's int_orders_enriched breaks; team-C's mart_finance_daily breaks; team-C's exec dashboard is empty at 8 AM.
  • The fix window. Roll back team-A's commit, re-run the affected marts, page team-A to write a proper migration — 4 hours of blast radius, all three teams paged.

Question. A 500-model dbt monolith has three teams (orders, marketing, finance) sharing one repo. Design the mesh split that would have prevented this incident, quantify the CI-time and blast-radius improvement, and name the specific dbt Mesh feature that catches the schema drift at build time.

Input.

Metric Monolith Proposed mesh
Model count 500 3 × ~170
CI wall time per PR 60 min 15 min per project
Blast radius of a bad commit full graph one project + declared downstream contracts
Public API surface undefined small set of contract-enforced public models
Ownership ambiguous one project = one team

Code.

# Monolith today — one dbt_project.yml, one CI, no contracts
# dbt_project.yml
name: analytics_monolith
version: 1.0.0
profile: prod
model-paths: [models]
Enter fullscreen mode Exit fullscreen mode 
# Proposed mesh — three projects with public model contracts

# projects/orders/dbt_project.yml
name: orders
version: 2.0.0
profile: prod
model-paths: [models]

# projects/orders/models/marts/public_orders.yml
version: 2
models:
  - name: public_orders
    description: "Public orders fact — order_id, customer_id, gross_amount, status, order_ts"
    access: public
    contract:
      enforced: true
    columns:
      - name: order_id
        data_type: bigint
        constraints:
          - type: not_null
          - type: primary_key
      - name: customer_id
        data_type: bigint
        constraints:
          - type: not_null
      - name: gross_amount
        data_type: numeric(18,2)
        constraints:
          - type: not_null
      - name: status
        data_type: varchar
      - name: order_ts
        data_type: timestamp
Enter fullscreen mode Exit fullscreen mode 
# projects/finance/dependencies.yml — declares the upstream project
projects:
  - name: orders

# projects/finance/models/marts/mart_finance_daily.sql
select
    order_date::date          as order_date,
    sum(o.gross_amount)       as gross_revenue,
    count(*)                  as order_count
from {{ ref('orders', 'public_orders') }} as o
group by 1
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Under the monolith, team-A's rename order_amount → gross_amount slipped through because no dbt test asserted the contract between stg_orders and the downstream marts. The failure surfaced only at build time in production, three DAG hops away.
  2. Under the mesh, team-A's public API is public_orders with contract: {enforced: true}. The contract asserts that gross_amount is a column of type numeric(18,2). If team-A refactors the SQL but keeps the column name, the contract passes. If team-A drops the column, the contract fails at build time inside the orders project — before merge.
  3. The mesh split reduces the CI time per PR from 60 minutes (full monolith rebuild) to ~15 minutes per project. Team-A's PR builds the orders project only. Team-B (marketing) and team-C (finance) do not wait on team-A's CI at all.
  4. The blast radius is now bounded by the public API surface. Team-A can freely refactor internal staging models (stg_orders_v3, int_orders_enriched) without touching consumers; only changes to public_orders trigger the cross-project contract check.
  5. Ownership becomes crisp — the orders project is owned by team-A, and CODEOWNERS at the repository level enforces review requirements. When public_orders needs a change, it is a cross-team API change, and the process reflects that.

Output.

Dimension Monolith outcome Mesh outcome
Detected schema drift at build time no yes (contract enforcement)
CI wall time per PR 60 min 15 min per project
Blast radius of team-A commit 3 teams orders project only
Fix time for the incident 4 hours prevented at CI
Cross-team blocking commits per month 5–8 0–1

Rule of thumb. The mesh's biggest win is not faster compute — it is catching schema drift at build time inside the producing project, before it reaches consumers. Contracts turn implicit column-name dependencies into explicit, tested APIs.

Worked example — the CI-time cliff at 300 models

Detailed explanation. Another common inflection point: a monolith crosses 300 models and the CI queue starts backing up. Every PR takes 40+ minutes for dbt build --select state:modified+. Reviewers hesitate to approve because context switching across 40 minutes is expensive. The team ships fewer PRs per week. The apparent "warehouse cost" problem is really a "coordination throughput" problem — and the mesh split fixes it by turning one 40-minute CI into three 12-minute CIs, run in parallel.

  • The CI observation. Median PR CI wall time crossed 40 minutes at model count 320.
  • The throughput observation. Merged PRs per week dropped from 25 to 14 over the same period.
  • The root cause. The CI queue is a shared resource; every team pays the tail cost of every other team's changes.

Question. Given a 500-model monolith with a 60-minute CI, design the mesh split that reduces per-PR CI time by 4× and quantify the throughput gain.

Input.

Parameter Monolith Mesh (3 projects)
Model count 500 ~170 per project
CI wall time per PR 60 min 15 min per project (parallel)
Merged PRs per week 14 40+ (throughput bounded by review capacity)
Cross-project CI trigger full rebuild only if public model contract changes

Code.

# CI config — GitHub Actions, one workflow per project
# .github/workflows/orders-ci.yml
name: orders-ci
on:
  pull_request:
    paths:
      - 'projects/orders/**'
      - 'projects/orders/dependencies.yml'
jobs:
  build:
    runs-on: ubuntu-latest
    defaults:
      run:
        working-directory: projects/orders
    steps:
      - uses: actions/checkout@v4
      - name: dbt build (project-scoped, slim CI)
        run: |
          dbt deps
          dbt build --select state:modified+ --state ./target-prod
Enter fullscreen mode Exit fullscreen mode 
# .github/workflows/finance-ci.yml — triggers on finance changes OR upstream contract changes
name: finance-ci
on:
  pull_request:
    paths:
      - 'projects/finance/**'
  workflow_run:
    workflows: ['orders-contract-changed']
    types: [completed]
jobs:
  build:
    runs-on: ubuntu-latest
    defaults:
      run:
        working-directory: projects/finance
    steps:
      - uses: actions/checkout@v4
      - run: |
          dbt deps
          dbt build --select state:modified+
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The mesh split moves each project into its own directory (projects/orders, projects/marketing, projects/finance). Each project has its own dbt_project.yml, its own models/, and its own CI workflow.
  2. The paths filter on the GitHub Actions workflow means the orders CI only runs when files under projects/orders/** change. A team-B PR that touches only marketing models does not trigger the orders CI — the shared-resource problem disappears.
  3. Slim CI (--select state:modified+) further bounds the CI to only the modified models plus their downstream neighbours within the project. On average, an orders PR touches 5–10 models; the CI builds 5–10, not 170.
  4. Cross-project contract changes trigger downstream CI via workflow_run. If team-A changes public_orders's contract (a breaking change), the finance CI runs as a downstream contract check — not a full monolithic rebuild.
  5. The throughput gain is roughly 4× — CI wall time drops from 60 minutes to 15, and the three projects run in parallel. Review capacity, not CI throughput, is now the bottleneck — which is the correct place for the constraint to live.

Output.

Metric Monolith Mesh
CI wall time per PR (median) 60 min 15 min
Cross-team PRs blocked by CI queue 40% 5%
Merged PRs per week (across all teams) 14 40+
Contract violation caught pre-merge no yes

Rule of thumb. The CI-time cliff at 300 models is the leading indicator that the monolith has outgrown its coordination model. Split the projects before the CI becomes the bottleneck, not after — retrofitting slim CI onto an established monolith is 2× the work.

Worked example — the ownership matrix falls apart at 15 engineers

Detailed explanation. A dbt monolith with 8 engineers has clear informal ownership — everyone knows who wrote what, and Slack pings resolve most conflicts. At 15 engineers, informal ownership collapses; nobody remembers who wrote stg_events_v3 two years ago. CODEOWNERS with folder-based patterns is a partial fix but breaks down when models cross folder boundaries. dbt Mesh makes ownership project-level, so the question "who owns this model" has a one-word answer: the project's team.

  • The 8-engineer state. Informal ownership works. Nobody argues.
  • The 15-engineer state. Model authorship is a git-blame archaeology exercise. Cross-team dependencies leak.
  • The mesh answer. One project = one team. The dbt_project.yml names the owning team; the CODEOWNERS file enforces review.

Question. Design the ownership model for a 4-team, 20-engineer analytics organisation using dbt Mesh. Show the CODEOWNERS file, the project-level owner metadata, and the escalation path for a cross-project incident.

Input.

Component Value
Teams orders, marketing, finance, product
Engineers per team 5
Projects one per team
Public model count per project 3–8

Code.

# projects/orders/dbt_project.yml
name: orders
version: 2.1.0
profile: prod
config-version: 2

# Project-level ownership metadata — surfaced in dbt Cloud Explorer
vars:
  owner_team: "team-orders"
  owner_slack: "#team-orders"
  on_call_rotation: "team-orders-oncall"

models:
  orders:
    +meta:
      owner: "team-orders"
      slack: "#team-orders"
Enter fullscreen mode Exit fullscreen mode 
# CODEOWNERS — repository level, enforces review requirements
# projects/orders is owned by @team-orders
projects/orders/**            @org/team-orders
projects/marketing/**         @org/team-marketing
projects/finance/**           @org/team-finance
projects/product/**           @org/team-product

# Cross-cutting infrastructure
.github/workflows/**          @org/data-platform
packages.yml                  @org/data-platform
Enter fullscreen mode Exit fullscreen mode 
# projects/orders/models/marts/public_orders.yml — per-model owner metadata
version: 2
models:
  - name: public_orders
    access: public
    meta:
      owner: "team-orders"
      slack: "#team-orders"
      escalation: "team-orders-oncall"
      sla_hours: 4
    contract:
      enforced: true
    columns:
      - name: order_id
        data_type: bigint
        constraints: [{type: not_null}]
      - name: gross_amount
        data_type: numeric(18,2)
        constraints: [{type: not_null}]
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Project-level ownership is expressed in three places: dbt_project.yml vars, model-level meta on public models, and the repository-level CODEOWNERS file. All three point at the same team so there is exactly one source of truth.
  2. CODEOWNERS with the projects/<team>/** pattern means any PR that touches a project's files requires review from that team's GitHub group. A cross-team PR (e.g. dependencies.yml change in finance that adds orders as a dependency) requires review from both teams.
  3. Per-model meta fields (owner, slack, escalation, sla_hours) are surfaced in dbt Cloud Explorer and via the Discovery API. When a downstream consumer looks up public_orders, they see who owns it and how to escalate — no git-blame required.
  4. The escalation path for a cross-project incident is: consumer team detects the issue → looks up owner in Explorer → pings slack channel → the producer team's on-call responds within sla_hours. This is the same runbook microservices teams have used for a decade.
  5. The organisational structure now maps 1:1 onto the dbt project structure. Team growth (from 15 to 30 engineers) is absorbed by splitting a project (orders becomes orders-domestic + orders-international), not by adding to the shared monolith.

Output.

Dimension Monolith Mesh
Ownership resolution time git blame (hours) Explorer lookup (seconds)
Cross-team PR review path ambiguous CODEOWNERS-enforced
Escalation runbook tribal knowledge meta.escalation
Team growth absorption painful split a project

Rule of thumb. When the engineering headcount crosses ~10, ownership must be coded, not tribal. dbt Mesh gives you three complementary places to code it — project vars, model meta, and repository CODEOWNERS — and Explorer surfaces all three back to consumers.

Senior interview question on dbt mesh and the monolith split

A senior interviewer often opens with: "You inherit a 500-model dbt monolith with 20 engineers, three teams, and 60-minute CI. Walk me through the architecture case for dbt Mesh, the specific dbt features you'd lean on, and the first four weeks of the migration."

Solution Using a domain-driven three-project split with public contracts 

# Week 1 — extract the "orders" domain into its own project
# projects/orders/dbt_project.yml
name: orders
version: 2.0.0
profile: prod
config-version: 2

# Week 2 — define the public API of the orders project
# projects/orders/models/marts/public_orders.yml
version: 2
models:
  - name: public_orders
    access: public
    contract:
      enforced: true
    versions:
      - v: 1
      - v: 2
    columns:
      - name: order_id
        data_type: bigint
        constraints: [{type: not_null}, {type: primary_key}]
      - name: customer_id
        data_type: bigint
        constraints: [{type: not_null}]
      - name: gross_amount
        data_type: numeric(18,2)
        constraints: [{type: not_null}]
      - name: status
        data_type: varchar
      - name: order_ts
        data_type: timestamp

# Week 3 — extract finance and marketing, declare cross-project deps
# projects/finance/dependencies.yml
projects:
  - name: orders

# projects/marketing/dependencies.yml
projects:
  - name: orders

# Week 4 — CI per project, cross-project contract check, sunset monolith
# .github/workflows/orders-ci.yml   → triggered on projects/orders/**
# .github/workflows/finance-ci.yml  → triggered on projects/finance/** + workflow_run(orders)
# .github/workflows/marketing-ci.yml → triggered on projects/marketing/** + workflow_run(orders)
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Week Milestone Deliverable
1 Extract orders orders project builds green in isolation
2 Publish orders API public_orders with contract v1 + v2 declared
3 Extract finance + marketing Both projects declare orders as dependencies.yml
4 Per-project CI + sunset monolith Monolithic repo archived; three projects in production

After week 4, the monolith is archived; three domain projects live in their own repos; each project's CI runs in 15 minutes; the public API surface between projects is public_orders (contract v2) plus a handful of similar public models per domain. Cross-team incidents drop from 5–8 per month to 0–1.

Output:

Metric Monolith Mesh (after 4 weeks)
Projects 1 3
CI wall time per PR 60 min 15 min per project
Cross-team blocking commits 5–8 / month 0–1 / month
Ownership clarity ambiguous project = team
Public API surface undefined 12 public models w/ contracts

Why this works — concept by concept:

  • Domain-driven split — the boundary between projects follows the team boundary, not the layer boundary (staging vs marts). One team = one project = one release cadence.
  • Public models as API — access: public + contract: {enforced: true} turns the intra-team public surface into a stable, versioned API. Consumers depend on the contract, not on the internal SQL.
  • Cross-project ref — {{ ref('orders', 'public_orders') }} is the mesh-native equivalent of an API call. dbt tracks the edge; Cloud Explorer renders it; Discovery API answers impact-analysis queries.
  • Slim CI per project — state:modified+ bounds the build to the changed models plus their in-project downstream. Cross-project impact is a separate downstream CI trigger, not a full rebuild.
  • Cost — the migration is ~4 senior-engineer-weeks for a 500-model monolith. The recurring cost is O(projects) in CI runtime and O(1) in coordination overhead. Compared to the ~40 engineer-hours per month lost to monolithic-CI-blocking events, the migration pays back in one quarter.

SQL
Topic — sql
SQL modelling and dbt-shaped problems

Practice →

ETL Topic — etl ETL problems on multi-project pipelines

Practice →

2. Splitting a monolith — domains + boundaries

Domain-driven partitioning — the split boundary is the team boundary, not the schema boundary

The mental model in one line: dbt project splitting succeeds when every project owns one domain (sales, marketing, finance, product) with one team, one release cadence, one CI, and one small set of published public models — and fails when the split follows technical boundaries (staging vs marts, source system vs consumer) that cross team lines. Every other decision in the mesh migration is a consequence of this boundary call.

Project deps and cross-project ref diagram — two dbt project cards side by side with a producer project containing stg, int, and public models on the left, a consumer project with fct and mart models on the right, a bold ribbon arrow labelled 'project dep' bridging their dbt_project.yml chips, and a ref-arrow rising from the consumer's fact model to the producer's public model on a light PipeCode card.

The four candidate boundaries.

  • Domain-driven. One project per business domain (orders, marketing, finance, product). This is the pattern that mirrors team structure and the one dbt Labs recommends.
  • Source-driven. One project per source system (salesforce, stripe, shopify). Works for the staging layer but creates awkward marts that pull from many source projects.
  • Layer-driven. One project for staging, one for intermediate, one for marts. Trivial to implement but the projects have no team ownership; the split is technical, not organisational.
  • Consumer-driven. One project per consumer (finance-mart, growth-mart). Works if consumers rarely share upstream models; breaks down if they do.

Why domain-driven wins for teams above 10 engineers.

  • Team ↔ project mapping. The team boundary and the project boundary are the same line. Ownership, on-call, and release cadence align 1:1.
  • Public API stays small. Each domain publishes 3–8 public models — the aggregated facts and canonical dimensions that other domains genuinely need. Internal staging and intermediate models stay private.
  • Release cadence is per-team. Finance can freeze during quarter-close; marketing can ship 10 PRs a day. Neither blocks the other.
  • CI is per-team. Slim CI (state:modified+) bounds the build to the changed models plus downstream within the project. Cross-project impact is a separate, cheap downstream CI trigger.

The "must-answer" checklist before splitting.

  • Does every model have a clear domain? Ambiguous models (e.g., a dim_customer that both marketing and finance use) need a decision — one domain owns it; the other consumes via ref('domain', 'dim_customer'). Splitting ownership 50/50 across domains is the single most common mistake.
  • Are the public models identified? Before splitting, run the whole team through the exercise: which 3–8 models per domain will you publish? If the list is empty ("we don't know yet"), you're not ready to split.
  • Is CODEOWNERS ready? Every project's ** glob maps to exactly one GitHub team. If your team structure is fluid, harden it first.
  • Is dbt Cloud (or an equivalent Discovery API) available? dbt Core supports Mesh via dbt deps + manifest passing, but the observability story (Explorer, cross-project lineage graph) is native to Cloud. Plan the observability layer before the split.

The upstream/downstream contract.

  • The producer's obligation. Publish stable public models with contracts and versions; deprecate old versions with a runway; document each public model's owner, slack, and sla_hours.
  • The consumer's obligation. Declare the upstream project in dependencies.yml; use ref('project', 'model') — not raw SQL against the warehouse; pin to a version and upgrade via a controlled migration.
  • The mesh's obligation. dbt Cloud tracks cross-project edges; contract failures surface at the producer's build time; deprecation warnings surface at the consumer's compile time.

Common interview probes on the split.

  • "Domain-driven vs layer-driven — which do you pick and why?" — domain-driven; layer-driven creates ownership gaps.
  • "What's the minimum team size that justifies a mesh split?" — usually ~10 engineers or ~300 models, whichever comes first.
  • "How many public models per project?" — 3–8 is typical; more than 15 suggests the domain itself needs splitting.
  • "How do you handle a model that legitimately belongs to two domains?" — one domain owns it; the other consumes via cross-project ref(). Shared ownership is the wrong answer.

Worked example — mapping a 400-model project onto four domains

Detailed explanation. A concrete exercise: a 400-model monolith serves an e-commerce business. The models span raw source data (Stripe, Shopify, Salesforce), staging, intermediate transformations, and marts consumed by finance, marketing, and product. The team must pick the domain boundaries, assign every model to exactly one project, and identify the public model surface.

  • The current state. 400 models under one models/ folder; three teams share the repo; no clear ownership.
  • The proposed split. Four projects: orders (Shopify + orders-related), payments (Stripe + finance-facing), marketing (Salesforce + growth), platform (shared dims + operational metadata).
  • The public surface. Each project publishes ~5 public models — canonical facts and dimensions.

Question. Design the mapping of 400 models onto four projects, list the public models per project, and show the cross-project dependency graph.

Input.

Existing folder Model count Proposed home
models/staging/shopify/ 40 orders
models/staging/stripe/ 30 payments
models/staging/salesforce/ 25 marketing
models/intermediate/orders/ 60 orders
models/intermediate/payments/ 40 payments
models/intermediate/marketing/ 50 marketing
models/marts/finance/ 45 payments (consumes orders)
models/marts/marketing/ 35 marketing (consumes orders)
models/marts/product/ 30 platform
models/dims/ 25 platform
models/utils/ 20 platform

Code.

# projects/orders/dbt_project.yml
name: orders
version: 2.0.0
profile: prod
config-version: 2

# Public API
# projects/orders/models/marts/schema.yml
version: 2
models:
  - name: public_orders
    access: public
    contract: {enforced: true}
    columns:
      - {name: order_id,     data_type: bigint,          constraints: [{type: not_null}, {type: primary_key}]}
      - {name: customer_id,  data_type: bigint,          constraints: [{type: not_null}]}
      - {name: gross_amount, data_type: numeric(18,2),   constraints: [{type: not_null}]}
      - {name: currency,     data_type: varchar}
      - {name: status,       data_type: varchar}
      - {name: order_ts,     data_type: timestamp}
  - name: public_order_lines
    access: public
    contract: {enforced: true}
    columns:
      - {name: order_line_id, data_type: bigint, constraints: [{type: not_null}]}
      - {name: order_id,      data_type: bigint}
      - {name: product_id,    data_type: bigint}
      - {name: quantity,      data_type: int}
      - {name: unit_price,    data_type: numeric(18,2)}
  - name: public_customers
    access: public
    contract: {enforced: true}
    columns:
      - {name: customer_id, data_type: bigint, constraints: [{type: not_null}, {type: primary_key}]}
      - {name: email,       data_type: varchar}
      - {name: signup_ts,   data_type: timestamp}
Enter fullscreen mode Exit fullscreen mode 
# projects/payments/dependencies.yml
projects:
  - name: orders
  - name: platform

# projects/payments/models/marts/mart_finance_daily.sql
select
    o.order_date::date                            as order_date,
    sum(o.gross_amount)                           as gross_revenue,
    sum(p.amount - p.refund_amount)               as net_revenue,
    count(distinct o.order_id)                    as order_count
from {{ ref('orders',   'public_orders')   }} as o
join {{ ref('payments', 'public_payments') }} as p using (order_id)
group by 1
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The domain mapping starts from the source: Shopify data is orders' concern; Stripe is payments'; Salesforce is marketing's. Cross-cutting dims (dim_date, dim_currency) go to platform — the shared-services project every other domain consumes.
  2. The finance marts move into payments because payments is the domain that owns revenue reporting. finance was a consumer name, not a domain; the mesh split rejects consumer-named projects because they blur team ownership.
  3. Each project publishes ~5 public models. Orders publishes public_orders, public_order_lines, public_customers (customers move with orders because the customer lifecycle is owned by the orders team). Payments publishes public_payments, public_refunds. Marketing publishes public_leads, public_campaigns. Platform publishes dim_date, dim_currency, dim_country.
  4. The cross-project dependency graph: payments → orders + platform; marketing → orders + platform; platform → (root). The DAG is a shallow tree; platform is the shared root; orders sits one level down; payments and marketing each depend on orders and platform.
  5. Internal staging and intermediate models stay access: protected (the default) — visible within the project but not to other projects. Only the ~5 public models per project cross project boundaries. The API surface is deliberately small.

Output.

Project Total models Public models Depends on
platform 45 (dims + utils) 3 (dim_date, dim_currency, dim_country) (none)
orders 100 (staging + int + marts) 3 (public_orders, public_order_lines, public_customers) platform
payments 115 (staging + int + finance marts) 2 (public_payments, public_refunds) orders + platform
marketing 110 (staging + int + growth marts) 2 (public_leads, public_campaigns) orders + platform
product marts 30 0 (product marts are terminal) orders + platform

Rule of thumb. Aim for a shallow tree of projects — one root (platform), a middle layer (domains), and terminal consumers (product-facing marts). Deep chains (A → B → C → D) create long deprecation windows and hard-to-reason-about blast radii.

Worked example — resolving a model that belongs to two domains

Detailed explanation. The mesh boundary decision gets ugly when a model genuinely serves two domains. dim_customer is the classic example: marketing wants it for growth analytics; finance wants it for revenue attribution; both teams have historically extended it with domain-specific columns. Under the mesh, one team owns it; the other consumes via cross-project ref(). Walk through the decision framework and the ownership handoff.

  • The conflict. dim_customer has 25 columns; marketing added 8; finance added 5; orders added 12 (the core customer identity + signup + status columns).
  • The correct answer. Orders owns dim_customer (or public_customers) — the customer identity is an orders-domain concept. Marketing and finance consume it via ref('orders', 'public_customers') and extend it inside their own projects with a view that adds their domain-specific columns.
  • The wrong answer. A shared "customers" project owned jointly by all three teams. Shared ownership violates the "one project = one team" invariant.

Question. Design the migration for dim_customer where orders publishes the core customer surface and finance / marketing extend it inside their own projects.

Input.

Column category Column count Owner
Core identity 12 orders
Marketing extensions 8 marketing
Finance extensions 5 finance
Total 25 ambiguous today

Code.

# projects/orders/models/marts/public_customers.yml
version: 2
models:
  - name: public_customers
    access: public
    contract: {enforced: true}
    columns:
      - {name: customer_id,   data_type: bigint,    constraints: [{type: not_null}, {type: primary_key}]}
      - {name: email,         data_type: varchar}
      - {name: first_name,    data_type: varchar}
      - {name: last_name,     data_type: varchar}
      - {name: signup_ts,     data_type: timestamp}
      - {name: signup_source, data_type: varchar}
      - {name: country_code,  data_type: char(2)}
      - {name: locale,        data_type: varchar}
      - {name: status,        data_type: varchar}
      - {name: churn_ts,      data_type: timestamp}
      - {name: last_seen_ts,  data_type: timestamp}
      - {name: customer_tier, data_type: varchar}
Enter fullscreen mode Exit fullscreen mode 
# projects/marketing/models/marts/dim_customer_marketing.sql
{{ config(materialized='view') }}

select
    c.customer_id,
    c.email,
    c.signup_ts,
    c.signup_source,
    c.country_code,
    c.customer_tier,

    -- Marketing extensions live in the marketing project
    m.attribution_channel,
    m.first_campaign_id,
    m.lifetime_touchpoints,
    m.email_engagement_score,
    m.growth_segment,
    m.acquisition_cost_cents,
    m.marketing_persona,
    m.opt_in_status
from {{ ref('orders',   'public_customers')       }} as c
left join {{ ref('int_customer_marketing_attrs')  }} as m using (customer_id)
Enter fullscreen mode Exit fullscreen mode 
# projects/payments/models/marts/dim_customer_finance.sql
{{ config(materialized='view') }}

select
    c.customer_id,
    c.email,
    c.signup_ts,
    c.country_code,
    c.customer_tier,

    -- Finance extensions live in the payments project
    f.lifetime_gross_revenue,
    f.lifetime_refund_amount,
    f.first_purchase_ts,
    f.last_purchase_ts,
    f.payment_risk_tier
from {{ ref('orders',   'public_customers')     }} as c
left join {{ ref('int_customer_finance_attrs') }} as f using (customer_id)
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The decision framework: pick the domain that creates the entity, not the domain that consumes it. Customers are created when someone signs up — an orders/commerce concern. Marketing and finance are consumers, not creators.
  2. public_customers in the orders project owns the canonical identity columns (customer_id, email, signup_ts, status, country) — the 12 columns every downstream project needs. Contract enforcement guarantees these columns remain stable.
  3. Marketing extends the customer surface inside its own project with dim_customer_marketing — a view that joins public_customers with marketing-internal attribution and engagement models. The marketing team owns the extension logic without pushing marketing columns into orders' public model.
  4. Payments similarly extends with dim_customer_finance — a view that joins public_customers with payment-internal revenue and risk models. Finance owns the extension without polluting orders.
  5. The three projects now have three domain-specific views of the same customer entity, each owned by the domain that uses it, and all anchored on orders' canonical public_customers. Ownership is crisp; the public API stays small.

Output.

View Owner Columns Depends on
public_customers orders 12 canonical (root of customer graph)
dim_customer_marketing marketing 12 canonical + 8 marketing orders + marketing-internal
dim_customer_finance payments 12 canonical + 5 finance orders + payments-internal

Rule of thumb. When a model serves multiple domains, the entity's creator owns the canonical public model; consumers extend it inside their own projects with domain-specific views. Shared ownership is always the wrong answer — someone will end up on-call for something they don't understand.

Worked example — the "platform" project as a shared root

Detailed explanation. Every mesh needs a small platform project that owns the shared plumbing every other domain consumes — canonical calendars, currency conversion tables, country/region dimensions, and macro utilities. This project is the root of the dependency graph. It is owned by the data platform team (not any single domain), has the strictest change-control policy (breaking changes require RFC), and publishes the widest range of public models. The failure mode of not having a platform project is duplicated calendar tables in every domain and inconsistent country codes across marts.

  • The need. Ten kinds of "shared plumbing" every domain needs — none of which belongs to any single domain.
  • The answer. A platform project owned by the data platform team; every public model in this project is a stable, versioned, contract-enforced API.
  • The change-control. Breaking changes go through a two-week RFC + downstream-notification workflow.

Question. Design the platform project's public API (5–8 public models) and the change-control policy for breaking changes.

Input.

Shared plumbing Belongs in platform?
dim_date yes
dim_currency yes
dim_country yes
dim_locale yes
macro fx_convert() yes
macro safe_divide() yes
stg_billing_events no — payments
dim_customer no — orders

Code.

# projects/platform/dbt_project.yml
name: platform
version: 3.0.0
profile: prod
config-version: 2

# projects/platform/models/dims/schema.yml
version: 2
models:
  - name: dim_date
    access: public
    contract: {enforced: true}
    columns:
      - {name: date_day,         data_type: date,     constraints: [{type: not_null}, {type: primary_key}]}
      - {name: day_of_week,      data_type: int}
      - {name: iso_week,         data_type: int}
      - {name: fiscal_quarter,   data_type: varchar}
      - {name: is_business_day,  data_type: boolean}
  - name: dim_currency
    access: public
    contract: {enforced: true}
    columns:
      - {name: currency_code, data_type: char(3), constraints: [{type: not_null}, {type: primary_key}]}
      - {name: currency_name, data_type: varchar}
      - {name: symbol,        data_type: varchar}
  - name: dim_country
    access: public
    contract: {enforced: true}
    columns:
      - {name: country_code,   data_type: char(2), constraints: [{type: not_null}, {type: primary_key}]}
      - {name: country_name,   data_type: varchar}
      - {name: region,         data_type: varchar}
      - {name: subregion,      data_type: varchar}
  - name: fx_rates_daily
    access: public
    contract: {enforced: true}
    columns:
      - {name: rate_date,      data_type: date,           constraints: [{type: not_null}]}
      - {name: from_currency,  data_type: char(3),        constraints: [{type: not_null}]}
      - {name: to_currency,    data_type: char(3),        constraints: [{type: not_null}]}
      - {name: rate,           data_type: numeric(18,8),  constraints: [{type: not_null}]}
Enter fullscreen mode Exit fullscreen mode 
# projects/platform/CHANGELOG.md — the change-control policy
# Change control for platform project
#
# Breaking changes to `access: public` models require:
#   1. RFC in #data-platform-rfc — 2 week comment period
#   2. Downstream project notification (all consumers named + Slacked)
#   3. New version (v_N+1) published; old version kept with `deprecation_date`
#   4. 6-week deprecation window during which consumers migrate
#   5. Only then the old version is dropped
#
# Non-breaking changes (adding a nullable column) require:
#   1. Standard PR review from the data platform team
#   2. Announcement in #data-consumers
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The platform project owns the shared plumbing every other domain depends on. Everything in this project is access: public by default — the project is the API.
  2. Contract enforcement is the strictest of any project. Breaking dim_date's column set breaks every mart in every domain simultaneously — the change-control policy reflects that.
  3. The RFC + 6-week deprecation window is the operational protocol for breaking changes. Consumers get plenty of runway; the platform team gets to make breaking changes eventually, but never surprise-breaks them.
  4. The public models are chosen for stability, not feature richness. dim_date has 5 columns; adding a 6th is a minor version bump. If a consumer needs a project-specific fiscal calendar, they build it inside their own project as a view over dim_date.
  5. The platform project's team is the data platform team — deliberately not any single domain's team. This prevents any domain from unilaterally shipping breaking changes to the shared root.

Output.

Public model Downstream projects using Change control
dim_date all 5 domains RFC + 6-week window
dim_currency orders, payments, marketing RFC + 6-week window
dim_country orders, marketing RFC + 6-week window
fx_rates_daily payments, orders RFC + 6-week window

Rule of thumb. Every mesh needs a platform project at the root of the DAG, owned by the platform team, with the strictest change-control policy. If you can't name the platform project on day one, your split will accumulate duplicated dimensions across domains within six months.

Senior interview question on domain-driven splits

A senior interviewer might ask: "You're picking the boundary for splitting a 400-model monolith. Walk me through the decision framework, name the domains you'd pick, and explain how you'd handle a model that legitimately belongs to two domains."

Solution Using a domain-driven split with a platform root and creator-owned public models 

# The mesh topology — one root, three domains, terminal marts
#
#                       platform
#                     /    |    \
#                    /     |     \
#                   /      |      \
#              orders  payments  marketing
#                 |       |         |
#                 v       v         v
#              (public_orders, public_payments, public_leads etc.)
#                            |
#                            v
#                    product/analytics marts

# projects/platform/dbt_project.yml   → owned by data platform team
# projects/orders/dbt_project.yml     → owned by orders team
# projects/payments/dbt_project.yml   → owned by payments team
# projects/marketing/dbt_project.yml  → owned by marketing team

# The creator-owned rule for cross-domain entities:
#   customer_id   → orders (creator)
#   order_id      → orders (creator)
#   payment_id    → payments (creator)
#   lead_id       → marketing (creator)
#   product_id    → platform  (canonical catalog)
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Decision Answer Rationale
Split boundary domain (not layer, not consumer) Team ↔ project 1:1
Number of projects 4 (platform + 3 domains) Fewer than teams = ambiguity; more = orphaned projects
Platform ownership data platform team Prevents any domain from unilaterally breaking shared plumbing
Creator-owned entities customer_id → orders, payment_id → payments Creator of the entity owns the public surface
Downstream extensions views inside consuming project Adds domain-specific columns without polluting the public model

The final topology is a shallow tree: platform at the root, three domain projects one level down, and terminal marts consuming the domain public models. Ownership is crisp; the public API surface is small; every model has exactly one owner.

Output:

Project Owner Public models Depends on
platform data platform dim_date, dim_currency, dim_country, fx_rates_daily (root)
orders orders team public_orders, public_order_lines, public_customers platform
payments payments team public_payments, public_refunds orders + platform
marketing marketing team public_leads, public_campaigns orders + platform

Why this works — concept by concept:

  • Creator-owned rule — the domain that creates an entity owns the canonical public surface. Consumers extend via views inside their own projects. This resolves every "which team owns this?" argument in constant time.
  • Platform as shared root — a dedicated project for shared plumbing (calendars, currencies, countries) prevents duplicated dims across domains. The platform team's strict change-control keeps the root stable.
  • Shallow-tree DAG — root + one middle layer + terminals. Deep chains (A → B → C → D) create long deprecation windows and make impact-analysis hard. A shallow tree keeps blast radius bounded.
  • One project = one team — ownership is coded via CODEOWNERS + dbt_project.yml vars + per-model meta. Team growth is absorbed by splitting a project, not by adding to a shared repo.
  • Cost — the boundary decision is ~1 week of senior-engineer + team-lead time to work through with all stakeholders. The recurring cost is O(1) — the tree structure means new domains slot in as new branches without re-organising existing projects.

SQL
Topic — sql
SQL modelling and dimensional-design problems

Practice →

Optimization Topic — optimization Optimization problems on data model architecture

Practice →

3. Public models + access + contracts

access: public | protected | private — the model-level access modifier is the mesh's API surface

The mental model in one line: dbt public models are the API of a project — anything marked access: public can be referenced by any other project; anything marked protected (the default) can only be referenced within the same group; anything marked private can only be referenced within the same project — and contracts + versions turn those public models from accidental exposure into *deliberate, stable APIs*. Every downstream project that reaches for a ref('project', 'model') call fails unless the target is access: public.

Access levels triptych — three model-cards side by side, each with a different badge (open key for public, shield for protected, padlock for private), a fan of outgoing arrows showing which teams can reference each, and a bottom strip showing downstream visibility on a light PipeCode card.

The three access levels.

  • public. Any project (declaring your project in dependencies.yml) may ref() this model. Public models are the API surface; they should have contracts and versions. Every public model is a promise the owning team must maintain.
  • protected. The default. The model can be referenced within the same group (a dbt-native grouping mechanism inside a project). Cross-project ref() fails. Use for models that are internal to the project but shared across sub-teams.
  • private. The model can only be referenced from within the same group in the same project. The most restrictive access — appropriate for staging models, intermediates, and one-off transformations.

The default is protected — and that's usually wrong for staging models.

  • The dbt default. If you don't set access:, the model is protected. That's a safe default from a leakage perspective — nothing accidentally becomes public.
  • The trap. Teams often forget to mark staging models private. protected staging models can be ref()d by other groups within the same project — creating hidden dependencies that survive the mesh split unintentionally.
  • The rule. Mark every staging and intermediate model access: private unless you specifically want it shared across groups. Explicit is better than default.

Groups — the intra-project access unit.

  • Definition. A group is a named collection of models within a single project, owned by a specific person or team. Defined in a groups: block in dbt_project.yml.
  • Access interaction. protected and private are scoped by group. Models in group A cannot reference private models in group B — even within the same project.
  • When to use. Larger projects (100+ models) benefit from groups for intra-project ownership. Smaller projects (<50 models) can skip groups entirely.

Model contracts — the API's type signature.

  • What they lock down. Column names, column data types, nullability constraints (not_null), primary/unique keys, foreign keys. A schema drift in the underlying SQL fails the build.
  • What they don't lock down. Row-level values, row counts, business logic. Contracts are type-level, not value-level — data tests (Great Expectations, dbt tests) handle the row-level assertions.
  • The build-time check. When contract: {enforced: true} is set, dbt build runs an implicit create table ... (schema definition) as select ... and fails if the SQL output doesn't match. No configuration; no separate test step.

Versions — the deprecation protocol.

  • The pattern. Public model dim_customer can exist as dim_customer_v1 and dim_customer_v2 simultaneously. Both are built; both are materialised; both are queryable.
  • The consumer choice. Downstream projects ref('project', 'dim_customer', v=1) or v=2. The version is part of the reference.
  • The deprecation. Set deprecation_date: on a version. dbt compile emits warnings for consumers still on the deprecated version. After the date, the version can be removed.
  • The migration protocol. Publish v2; give consumers 4–6 weeks; set v1's deprecation_date; watch Discovery API for laggards; drop v1 after consumers migrate.

Common interview probes on public models + contracts.

  • "What's the difference between access: protected and access: private?" — protected is cross-group within a project; private is same-group only.
  • "What does contract: {enforced: true} actually enforce at build time?" — columns, types, nullability; fails the build on drift.
  • "Contract vs schema test — what's the difference?" — contract is type-level build-time; schema tests are value-level post-build.
  • "How do you deprecate a public model?" — publish new version, set deprecation_date on old, watch Discovery API for consumers, drop after migration.

Worked example — a public model with a full contract

Detailed explanation. The canonical public-model definition: public_customers in the orders project, access: public, contract: {enforced: true}, with column types, nullability, and a primary key constraint. Walk through what happens at build time when the underlying SQL drifts.

  • The desired shape. 6 columns: customer_id (bigint, not_null, PK), email (varchar), first_name (varchar), signup_ts (timestamp, not_null), country_code (char(2)), tier (varchar).
  • The drift scenario. A developer refactors the SQL and accidentally omits country_code.
  • The expected outcome. dbt build fails inside the orders project with a contract-mismatch error. The developer's PR cannot merge until the SQL matches the contract or the contract is updated (which requires cross-team review because it's a breaking change to the public API).

Question. Write the full schema.yml for public_customers with a contract, then simulate a drift and walk through the build-time failure.

Input.

Column Type Nullability Constraint
customer_id bigint not_null primary key
email varchar nullable —
first_name varchar nullable —
signup_ts timestamp not_null —
country_code char(2) nullable —
tier varchar nullable —

Code.

# projects/orders/models/marts/schema.yml
version: 2
models:
  - name: public_customers
    description: "Public customer dimension — canonical identity + signup + tier."
    access: public
    contract:
      enforced: true
    meta:
      owner: "team-orders"
      slack: "#team-orders"
      sla_hours: 4
    columns:
      - name: customer_id
        description: "Surrogate customer key."
        data_type: bigint
        constraints:
          - type: not_null
          - type: primary_key
      - name: email
        description: "Primary email address; may be null for guests."
        data_type: varchar
      - name: first_name
        description: "First name from signup form."
        data_type: varchar
      - name: signup_ts
        description: "Timestamp of first successful signup event."
        data_type: timestamp
        constraints:
          - type: not_null
      - name: country_code
        description: "ISO 3166-1 alpha-2 country code."
        data_type: char(2)
      - name: tier
        description: "Customer loyalty tier."
        data_type: varchar
Enter fullscreen mode Exit fullscreen mode 
-- projects/orders/models/marts/public_customers.sql — CORRECT version
select
    customer_id::bigint          as customer_id,
    lower(email)::varchar        as email,
    first_name::varchar          as first_name,
    signup_ts::timestamp         as signup_ts,
    upper(country_code)::char(2) as country_code,
    tier::varchar                as tier
from {{ ref('int_customers_enriched') }}

-- --------------------------------------------------------------
-- DRIFTED version (accidentally omits country_code)
-- select
--     customer_id::bigint    as customer_id,
--     lower(email)::varchar  as email,
--     first_name::varchar    as first_name,
--     signup_ts::timestamp   as signup_ts,
--     tier::varchar          as tier
-- from {{ ref('int_customers_enriched') }}
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The schema.yml declares the contract: six columns, exact types, nullability, and a primary key constraint on customer_id. contract: {enforced: true} tells dbt to compare the SQL output against this declaration at build time.
  2. The correct SQL (top block) produces exactly the six columns with the exact types. dbt runs create table public_customers (customer_id bigint not null, ... ) as select ... — the DDL matches the contract; the build passes.
  3. The drifted SQL (commented block) omits country_code. When dbt runs the build, it prepares the DDL with country_code char(2) in the target table schema — then the select statement doesn't produce that column, so the insert fails with a warehouse-level error, wrapped by dbt as a contract-mismatch failure.
  4. The error message names the missing column and the SQL file. The developer's PR is red; the merge is blocked. The developer must either add the column back to the SQL (restoring the contract) or open a separate PR to update the contract (which counts as a breaking API change and triggers the deprecation protocol).
  5. The consumer projects (payments, marketing) are unaffected because the drift was caught inside orders' build. This is the mesh's superpower — contract violations surface at the producer, not at the consumer three DAG hops downstream.

Output.

Scenario Build outcome Consumer impact
Correct SQL matches contract passes consumers unaffected
Drifted SQL (missing column) fails inside orders consumers unaffected (drift never merges)
Contract update PR passes if reviewed and merged consumers migrate on next version bump

Rule of thumb. Every public model must have contract: {enforced: true}. The 5 minutes it takes to write the schema.yml is the cheapest insurance against 4-hour outages caused by upstream schema drift.

Worked example — access: public vs protected vs private in practice

Detailed explanation. The three access levels have distinct downstream visibility, and getting them wrong is the most common mesh mis-configuration. Walk through a real project with a mix of staging, intermediate, and mart models, assign the correct access level to each, and show what happens when a downstream project tries to ref() a protected or private model.

  • The staging models. stg_shopify_orders, stg_shopify_customers — internal, project-only. Access: private.
  • The intermediate models. int_orders_enriched, int_customers_enriched — shared across groups within the project. Access: protected.
  • The mart models. public_orders, public_customers — cross-project API. Access: public.
  • The negative test. A payments project tries to ref('orders', 'stg_shopify_orders') — should fail at compile time.

Question. Design the access-level assignments for a 100-model orders project split across three groups (staging, marts, exports), and show the compile-time failure for a cross-project ref to a private model.

Input.

Layer Model count Correct access Which groups can ref?
staging 40 private staging group only
intermediate 30 protected any group in project
marts (internal) 20 protected any group in project
marts (public) 10 public any project

Code.

# projects/orders/dbt_project.yml
name: orders
version: 2.0.0

groups:
  - name: staging
    owner:
      name: "team-orders-eng"
  - name: marts
    owner:
      name: "team-orders-analytics"
  - name: exports
    owner:
      name: "team-orders-analytics"

# Model paths → group + default access
models:
  orders:
    staging:
      +group: staging
      +access: private
    intermediate:
      +group: marts
      +access: protected
    marts:
      +group: marts
      # public/protected set per-model below
    exports:
      +group: exports
      +access: protected
Enter fullscreen mode Exit fullscreen mode 
# projects/orders/models/marts/schema.yml — explicit public marks
version: 2
models:
  - name: public_orders
    access: public
    contract: {enforced: true}
  - name: public_customers
    access: public
    contract: {enforced: true}
  - name: mart_orders_daily_internal
    access: protected      # only exposed within the orders project's marts group
Enter fullscreen mode Exit fullscreen mode 
# projects/payments/models/marts/mart_finance.sql
# Legitimate: refs the public model
select * from {{ ref('orders', 'public_orders') }}

# Illegal: refs a private staging model — compile-time error
# select * from {{ ref('orders', 'stg_shopify_orders') }}
#
# dbt compile error:
#   Node `mart_finance` attempted to reference node `stg_shopify_orders`
#   which has access `private`. This node cannot be referenced from
#   project `payments`.
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The dbt_project.yml declares three groups (staging, marts, exports) and assigns default access per model path. models/staging/** defaults to private; models/intermediate/** to protected; models/marts/** to a group-level default that individual models override.
  2. Public marts (public_orders, public_customers) are explicitly marked access: public in schema.yml. Internal marts stay protected — they're shared inside the project but not exposed as cross-project API.
  3. Staging models are private — the staging group is the only group in the orders project that can reference them. The marts group cannot; the exports group cannot; and certainly no other project can.
  4. When payments tries to ref('orders', 'public_orders') — the public model — the reference resolves. dbt compiles the query, wires the cross-project lineage edge, and the build succeeds.
  5. When payments tries to ref('orders', 'stg_shopify_orders') — a private model — dbt fails at compile time with an access-violation error. The check is deterministic and cheap; no build runs; no data moves. The mesh's access model is enforced at compile, not at build.

Output.

Access level Cross-project ref? Same-project cross-group ref? Same-group ref?
public yes yes yes
protected no yes yes
private no no yes

Rule of thumb. Always mark staging models access: private explicitly. The protected default is safer than nothing, but explicit private prevents the "why is marketing referencing my staging model?" bug that appears six months after the mesh split.

Worked example — versioning a public model with deprecation

Detailed explanation. The most operationally intense mesh scenario: public_orders_v1 has been in production for a year, fifteen downstream projects depend on it, and the orders team wants to change the gross_amount column from numeric(18,2) to numeric(20,4) (higher precision for international multi-currency). This is a breaking change — the SQL type differs, downstream marts that cast the column will change behaviour. The correct migration ships v2 alongside v1, gives consumers a deprecation window, and watches the Discovery API for laggards.

  • The trigger. International expansion requires higher-precision decimal.
  • The breaking change. gross_amount numeric(18,2) → numeric(20,4).
  • The consumers. 15 downstream projects with ref('orders', 'public_orders').
  • The protocol. Publish v2; set v1's deprecation_date for 6 weeks out; migrate consumers; drop v1.

Question. Show the versions: block for public_orders, the deprecation-date-driven migration protocol, and how a downstream project pins to a specific version.

Input.

Version gross_amount type Status Deprecation date
v1 numeric(18,2) deprecating 2026-08-15
v2 numeric(20,4) current (none)

Code.

# projects/orders/models/marts/schema.yml
version: 2
models:
  - name: public_orders
    latest_version: 2
    access: public
    contract: {enforced: true}
    columns:
      - name: order_id
        data_type: bigint
        constraints: [{type: not_null}, {type: primary_key}]
      - name: customer_id
        data_type: bigint
        constraints: [{type: not_null}]
      - name: status
        data_type: varchar
      - name: order_ts
        data_type: timestamp
    versions:
      - v: 1
        deprecation_date: "2026-08-15"
        columns:
          # v1 overrides gross_amount type
          - include: all
          - name: gross_amount
            data_type: numeric(18,2)
      - v: 2
        columns:
          - include: all
          - name: gross_amount
            data_type: numeric(20,4)
Enter fullscreen mode Exit fullscreen mode 
-- projects/orders/models/marts/public_orders_v1.sql
select
    order_id::bigint            as order_id,
    customer_id::bigint         as customer_id,
    round(gross_amount, 2)::numeric(18,2) as gross_amount,
    status::varchar             as status,
    order_ts::timestamp         as order_ts
from {{ ref('int_orders_enriched') }}

-- projects/orders/models/marts/public_orders_v2.sql
select
    order_id::bigint            as order_id,
    customer_id::bigint         as customer_id,
    gross_amount::numeric(20,4) as gross_amount,
    status::varchar             as status,
    order_ts::timestamp         as order_ts
from {{ ref('int_orders_enriched') }}
Enter fullscreen mode Exit fullscreen mode 
# Downstream consumer — pins to a specific version
# projects/payments/models/marts/mart_finance_daily.sql

# Legacy consumer still on v1 (warning emitted at compile because deprecation_date is set)
# select * from {{ ref('orders', 'public_orders', v=1) }}

# Migrated consumer on v2
select
    order_id,
    customer_id,
    gross_amount::numeric(20,4) as gross_amount,
    status,
    order_ts
from {{ ref('orders', 'public_orders', v=2) }}
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The schema.yml declares both versions: v1 with gross_amount numeric(18,2) and v2 with numeric(20,4). Both versions are built, materialised, and queryable in the warehouse.
  2. latest_version: 2 tells dbt that a consumer who omits the version defaults to v2. Explicit pinning (v=1) still works and returns v1's shape.
  3. deprecation_date: "2026-08-15" on v1 tells dbt to emit a compile-time warning for any consumer still on v1. The warning names the consumer project and model — so the producer team can build a migration dashboard from the Discovery API.
  4. Downstream projects migrate at their own pace within the window: they change their ref() from v=1 (or unversioned) to v=2, adjust downstream SQL to handle the new type, and merge. The producer watches the Discovery API for the count of consumers still on v1.
  5. After the deprecation date, the producer PR that removes public_orders_v1.sql and the v1 entry from schema.yml merges. Any consumer still on v1 gets a compile error (not a silent breakage) and must migrate to v2 immediately. The old materialised table is dropped by a cleanup script.

Output.

Timeline v1 status v2 status Consumers on v1 Consumers on v2
Day 0 current (none) 15 0
Day 1 (v2 ships) current current 15 0
Day 14 deprecated current 10 5
Day 42 (deprecation date) scheduled removal current 1 14
Day 45 (post-window) removed current 0 15

Rule of thumb. Every breaking change to a public model goes through the versions: block with a 4–6 week deprecation window. Big-bang migrations of shared APIs are the mesh's failure mode; the version + deprecation protocol is how you avoid them.

Senior interview question on public models + contracts + versions

A senior interviewer might ask: "You own the orders project in a dbt Mesh. A dozen downstream projects depend on public_orders. You need to change a column's data type. Walk me through the process — the SQL changes, the schema.yml changes, how consumers get notified, and how you know when it's safe to remove v1."

Solution Using versioned public models with a deprecation-date-driven migration 

# Step 1 — publish v2 alongside v1
# projects/orders/models/marts/schema.yml
version: 2
models:
  - name: public_orders
    latest_version: 2
    access: public
    contract: {enforced: true}
    versions:
      - v: 1
        deprecation_date: "2026-08-15"     # 6 weeks out
        columns:
          - include: all
          - {name: gross_amount, data_type: numeric(18,2)}
      - v: 2
        columns:
          - include: all
          - {name: gross_amount, data_type: numeric(20,4)}
    columns:
      - {name: order_id,     data_type: bigint,    constraints: [{type: not_null}, {type: primary_key}]}
      - {name: customer_id,  data_type: bigint,    constraints: [{type: not_null}]}
      - {name: status,       data_type: varchar}
      - {name: order_ts,     data_type: timestamp}
Enter fullscreen mode Exit fullscreen mode 
# Step 2 — query the Discovery API to find consumers still on v1
# https://cloud.getdbt.com/api/discovery
query ConsumersOnV1 {
  environment(id: "prod") {
    definition {
      models(filter: {access: PUBLIC, name: "public_orders", version: "1"}) {
        edges {
          node {
            name
            dependencies {
              projectName
              modelName
            }
          }
        }
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode 
# Step 3 — after deprecation date, drop v1
# projects/orders/models/marts/schema.yml
version: 2
models:
  - name: public_orders
    latest_version: 2
    access: public
    contract: {enforced: true}
    versions:
      - v: 2
        columns:
          - include: all
          - {name: gross_amount, data_type: numeric(20,4)}
    columns:
      - {name: order_id,     data_type: bigint,    constraints: [{type: not_null}, {type: primary_key}]}
      - {name: customer_id,  data_type: bigint,    constraints: [{type: not_null}]}
      - {name: gross_amount, data_type: numeric(20,4), constraints: [{type: not_null}]}
      - {name: status,       data_type: varchar}
      - {name: order_ts,     data_type: timestamp}
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Step Action Consumer effect
1 Publish v2 in schema.yml + SQL file Both v1 and v2 materialise; consumers unchanged
2 Set deprecation_date on v1 Compile-time warnings for v1 consumers
3 Post migration guide in Slack + Explorer Consumers migrate ref() calls at their own pace
4 Query Discovery API weekly for v1 consumers Watch the count decline; ping laggards individually
5 After deprecation date, drop v1 Any straggler consumer fails compile; migrates immediately

At the end of the six-week window, all 15 downstream projects have migrated to v2, the compile-time warnings are gone, and the producer PR that drops v1 merges cleanly. The migration protocol scales linearly with consumer count — 15 consumers is manageable manually; 150 needs automation via Discovery API.

Output:

Metric Before migration Day 14 Deprecation date After
Consumers on v1 15 10 1 0
Consumers on v2 0 5 14 15
Compile warnings 0 10 1 0
Contract violations 0 0 0 0

Why this works — concept by concept:

  • Versions as first-class artifacts — v1 and v2 are both materialised in the warehouse, both queryable, both contract-enforced. The dual-existence period is what gives consumers the runway to migrate.
  • Deprecation date as a soft deadline — deprecation_date emits compile-time warnings without breaking anything. Consumers see a nudge every compile; producers get to enforce a hard deadline after the date passes.
  • Discovery API for observability — the producer team runs a weekly Discovery API query to count v1 consumers. The count is the migration progress meter; when it hits zero, v1 is safe to drop.
  • Compile-time compile-time compile-time — every check in this flow (access, contract, deprecation, version pinning) is a compile-time check. Nothing runs data. Nothing costs warehouse compute to validate. The mesh's checks are cheap.
  • Cost — 6 weeks of runway, one Discovery API query per week, one Slack ping per laggard. The cost scales with consumer count linearly; big-bang breaking changes cost 10× more because they trigger downstream outages.

SQL
Topic — sql
SQL contract and schema-drift problems

Practice →

Optimization Topic — optimization Optimization problems on versioned data APIs

Practice →

4. Cross-project refs + dbt Cloud Explorer

{{ ref('project_name', 'model_name') }} — the cross-project reference wired through dependencies.yml

The mental model in one line: cross-project ref is the mesh-native call that lets one project reference another project's public model — declared upstream in dependencies.yml, resolved at compile time by dbt's dependency graph, and rendered in dbt Cloud Explorer as a first-class edge in the cross-team lineage graph — and it fails cleanly at compile if the target isn't access: public or if the target version has been deprecated. Every mesh consumer relationship reduces to this one call.

Contracts and versions diagram — a signed contract scroll pinned to a model card with column-type rows, a pair of v1 and v2 model tiles with a deprecation stamp on v1, an arrow labelled 'deprecation_date' pointing to a calendar chip, and a downstream consumer sending a ref-arrow to v2 on a light PipeCode card.

The three-part call.

  • dependencies.yml. Declares the upstream projects. projects: [{name: orders}, {name: platform}] tells dbt: "our project depends on these two upstream projects." dbt pulls their manifests at compile.
  • ref('project_name', 'model_name'). The Jinja call inside SQL. dbt resolves the reference by (a) looking up the model in the upstream project's manifest, (b) checking the access level, (c) checking the version, (d) writing the fully qualified table name into the compiled SQL.
  • Optional v= argument. ref('orders', 'public_orders', v=2) pins to a specific version. Omitting v picks latest_version.

How dbt Cloud resolves cross-project refs at compile time.

  • Manifest lookup. dbt Cloud has access to every project's manifest (via the mesh-native integration). At compile, the consumer's ref('orders', 'public_orders') triggers a lookup in the orders project's manifest.json.
  • Access check. If the target isn't access: public, compile fails with an access-violation error naming both projects.
  • Version pin resolution. If v= is set, dbt resolves to that specific version; otherwise latest_version. If the requested version is deprecated, a warning is emitted; if it's been removed, compile fails.
  • Physical resolution. The compiled SQL contains the fully qualified table name (e.g. orders_prod.public_orders_v2). No runtime lookup; no metadata fetch during query execution.

dbt Core vs dbt Cloud.

  • dbt Core support. Mesh is supported in dbt Core via dbt deps pulling the upstream project as a package and passing its manifest. The mechanism works; the ergonomics are rough — you have to build the manifest exchange yourself.
  • dbt Cloud native. Cloud's mesh-native integration handles manifest exchange, access enforcement, version resolution, and Explorer lineage automatically. The UI overhead disappears.
  • The recommendation. For production Mesh deployments, dbt Cloud is the ergonomic default. Mesh on Core is achievable but requires more custom tooling (CI-time manifest exchange, Discovery API replacement, etc.).

dbt Cloud Explorer — the cross-team lineage graph.

  • What it shows. Every project's models rendered as a single continuous DAG. Cross-project edges are marked visually. Filters for project, group, access level, and owner.
  • The impact-analysis view. Click a public model; see every downstream consumer across all projects. The blast-radius question ("what breaks if I change this column?") becomes a UI click.
  • The catalog view. All access: public models across all projects, searchable by name, description, or column. The mesh's catalog — new consumers discover which public models exist without reading source code.
  • The freshness + status view. Every model's last-successful build timestamp, contract compliance, and test status. On-call teams monitor the graph, not the log stream.

Discovery API — programmatic governance.

  • The GraphQL surface. https://cloud.getdbt.com/api/discovery — a GraphQL API that answers "who depends on this model?", "how many consumers are on v1?", "which public models were built successfully in the last 24 hours?".
  • Common queries. Impact analysis (downstream consumers of X), migration tracking (consumers on deprecated versions), governance dashboards (public models per project, contract coverage).
  • Automation surface. Wire the Discovery API into CI checks ("this PR removes a public model — are there consumers?"), into Slack notifications ("v1 is 5 days from deprecation, 3 consumers haven't migrated"), and into governance dashboards.

Common interview probes on cross-project refs.

  • "What does dependencies.yml do?" — declares upstream projects so dbt pulls their manifests at compile.
  • "How does dbt Cloud enforce access at cross-project ref time?" — compile-time manifest lookup + access-level check.
  • "Explorer vs Discovery API — when do you reach for each?" — Explorer for humans (interactive lineage), Discovery API for automation (CI checks, dashboards).
  • "What happens if a consumer refs a version that's been removed?" — compile fails with a version-not-found error; the consumer must migrate to the current version.

Worked example — a four-project mesh with cross-project refs

Detailed explanation. A concrete end-to-end mesh: platform publishes dim_date and dim_currency; orders publishes public_orders and consumes platform; payments publishes public_payments and consumes orders + platform; analytics (the consumer terminal) consumes payments, orders, and platform. Walk through the dependencies.yml, the cross-project ref() calls, and the resulting lineage graph.

  • Topology. platform → orders → payments → analytics; platform is also a direct dep of payments and analytics.
  • The DAG. Shallow-tree: platform at the root, orders one level down, payments and analytics as terminals.
  • The public API. Each intermediate project publishes 2–3 public models.

Question. Produce the four dependencies.yml files, the cross-project ref() calls in the analytics project, and describe how Explorer renders the lineage.

Input.

Project Depends on Publishes
platform (root) dim_date, dim_currency
orders platform public_orders, public_customers
payments orders, platform public_payments
analytics payments, orders, platform (terminal marts)

Code.

# projects/platform/dependencies.yml
# (empty — platform is the root)
projects: []

# projects/orders/dependencies.yml
projects:
  - name: platform

# projects/payments/dependencies.yml
projects:
  - name: orders
  - name: platform

# projects/analytics/dependencies.yml
projects:
  - name: payments
  - name: orders
  - name: platform
Enter fullscreen mode Exit fullscreen mode 
-- projects/analytics/models/marts/mart_finance_daily.sql
with
d as (
    select date_day
    from {{ ref('platform', 'dim_date') }}
    where date_day between date '2026-01-01' and date '2026-12-31'
),
o as (
    select
        order_id,
        customer_id,
        order_ts::date as order_date,
        gross_amount
    from {{ ref('orders', 'public_orders', v=2) }}
    where order_ts >= date '2026-01-01'
),
p as (
    select
        order_id,
        amount,
        refund_amount
    from {{ ref('payments', 'public_payments') }}
),
c as (
    select currency_code, currency_name
    from {{ ref('platform', 'dim_currency') }}
)

select
    d.date_day,
    sum(o.gross_amount)                          as gross_revenue,
    sum(p.amount - coalesce(p.refund_amount, 0)) as net_revenue,
    count(distinct o.order_id)                   as order_count,
    count(distinct o.customer_id)                as unique_customers
from d
left join o on o.order_date = d.date_day
left join p using (order_id)
group by 1
order by 1
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Each project's dependencies.yml declares its upstream projects. dbt uses this at compile to pull each upstream project's manifest.json and register their public models in the current project's namespace.
  2. The analytics project references four public models across three projects: platform.dim_date, platform.dim_currency, orders.public_orders (pinned to v=2), and payments.public_payments. Each ref() is resolved against the corresponding upstream manifest.
  3. The compiled SQL replaces each ref() with the fully qualified table name. ref('orders', 'public_orders', v=2) compiles to orders_prod.public_orders_v2 — the physical location in the warehouse. No runtime lookup.
  4. dbt Cloud Explorer renders the lineage as a single continuous DAG: analytics's mart_finance_daily has four incoming edges from four upstream public models across three upstream projects. Cross-project edges are visually distinct (different colour or line style) from in-project edges.
  5. The impact-analysis view for public_orders_v2 shows analytics's mart_finance_daily as a downstream consumer. If the orders team plans a breaking change, they can query Discovery API for the consumer count before opening the PR.

Output.

Compiled reference Resolves to Explorer edge type
{{ ref('platform', 'dim_date') }} platform_prod.dim_date cross-project
{{ ref('orders', 'public_orders', v=2) }} orders_prod.public_orders_v2 cross-project
{{ ref('payments', 'public_payments') }} payments_prod.public_payments cross-project
{{ ref('platform', 'dim_currency') }} platform_prod.dim_currency cross-project

Rule of thumb. Every cross-project reference is one dependencies.yml entry + one ref('project', 'model') call. If a project has more than ~5 upstream project dependencies, the mesh boundary is probably drawn wrong — one project shouldn't need to know about half the mesh.

Worked example — dbt Cloud Explorer for impact analysis

Detailed explanation. The orders team is planning to add a column to public_orders (nullable, non-breaking) and rename a column in public_customers (breaking). Walk through how they use Explorer to enumerate the downstream consumers, plan the deprecation for the breaking change, and communicate the non-breaking addition.

  • The two changes. Add discount_amount numeric(18,2) to public_orders (non-breaking); rename first_name → given_name in public_customers (breaking).
  • The tool. Explorer's impact-analysis view + Discovery API for automation.
  • The output. A migration plan with consumer counts, timelines, and communication points.

Question. Show the Explorer-driven impact analysis, the Discovery API query, and the migration plan.

Input.

Change Type Consumers on public_orders Consumers on public_customers
Add discount_amount non-breaking 8 —
Rename first_name breaking — 12

Code.

# Discovery API — enumerate consumers of both public models
# https://cloud.getdbt.com/api/discovery
query ConsumersOfPublicModels {
  environment(id: "prod") {
    definition {
      publicModelsConsumers: applied {
        publicModels {
          edges {
            node {
              name
              projectId
              projectName
              access
              versions {
                v
                deprecationDate
              }
              dependents {
                nodes {
                  name
                  projectName
                  materializedType
                  filePath
                }
              }
            }
          }
        }
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode 
# Migration plan for the breaking change — schema.yml
# projects/orders/models/marts/schema.yml
version: 2
models:
  - name: public_customers
    latest_version: 2
    access: public
    contract: {enforced: true}
    versions:
      - v: 1
        deprecation_date: "2026-09-01"
        columns:
          - include: all
          - {name: first_name, data_type: varchar}
      - v: 2
        columns:
          - include: all
          - {name: given_name, data_type: varchar}   # renamed from first_name
    columns:
      - {name: customer_id, data_type: bigint, constraints: [{type: not_null}, {type: primary_key}]}
      - {name: email,       data_type: varchar}
      - {name: signup_ts,   data_type: timestamp, constraints: [{type: not_null}]}

  # Non-breaking addition — no version bump needed
  - name: public_orders
    latest_version: 2
    access: public
    contract: {enforced: true}
    columns:
      - {name: order_id,        data_type: bigint,        constraints: [{type: not_null}, {type: primary_key}]}
      - {name: customer_id,     data_type: bigint,        constraints: [{type: not_null}]}
      - {name: gross_amount,    data_type: numeric(20,4), constraints: [{type: not_null}]}
      - {name: discount_amount, data_type: numeric(18,2)}  # new column, nullable
      - {name: status,          data_type: varchar}
      - {name: order_ts,        data_type: timestamp}
Enter fullscreen mode Exit fullscreen mode 
# Automation — post migration reminders to Slack for consumers still on v1
import requests
import os

def get_consumers_on_v1(model_name: str, project_name: str) -> list[dict]:
    query = """
    query {
      environment(id: "prod") {
        definition {
          publicModels(filter: {name: "%s", projectName: "%s", version: "1"}) {
            edges {
              node {
                dependents {
                  nodes {
                    projectName
                    filePath
                  }
                }
              }
            }
          }
        }
      }
    }
    """ % (model_name, project_name)
    resp = requests.post(
        "https://cloud.getdbt.com/api/discovery",
        json={"query": query},
        headers={"Authorization": f"Bearer {os.environ['DBT_CLOUD_TOKEN']}"},
    )
    edges = resp.json()["data"]["environment"]["definition"]["publicModels"]["edges"]
    return [d for e in edges for d in e["node"]["dependents"]["nodes"]]

# Weekly Slack ping
consumers = get_consumers_on_v1("public_customers", "orders")
for c in consumers:
    print(f"WARN: {c['projectName']} / {c['filePath']} still on public_customers v1 — deprecation 2026-09-01")
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The Explorer impact-analysis view lists both public models with their downstream consumers. public_orders has 8 consumers across 4 projects; public_customers has 12 consumers across 5 projects.
  2. For the non-breaking discount_amount addition, the migration plan is trivial: add the column to the contract; update the SQL; ship. Consumers can start using the column on their next dbt build without any code change; a Slack announcement in #data-consumers is the only communication.
  3. For the breaking first_name → given_name rename, the migration plan uses the version protocol: publish v2 (with given_name); set v1's deprecation_date for 6 weeks out; migrate consumers; drop v1. Every step is code, none is manual coordination.
  4. The Discovery API query enumerates consumers of v1 programmatically. Wired into a weekly cron, the query outputs a list of consumer projects and their file paths — feeding into a Slack reminder pipeline that names the laggards without human intervention.
  5. Explorer shows the migration progress visually: as consumers migrate their ref() calls from v1 to v2, the edge count on v1 declines. When it reaches zero, v1 is safe to drop.

Output.

Change Type Consumers Plan
Add discount_amount non-breaking 8 Ship on next PR; Slack announcement
Rename first_name → given_name breaking 12 v2 + 6-week deprecation + Discovery API monitoring

Rule of thumb. Explorer is for humans (visual lineage, planning, communication). Discovery API is for automation (CI checks, deprecation reminders, governance dashboards). Every mesh needs both.

Worked example — Discovery API for governance dashboards

Detailed explanation. A senior analytics engineering lead wants a governance dashboard — one view that shows, across every project in the mesh, (a) how many public models have contracts, (b) how many are on the latest version, (c) how many downstream consumers each has, and (d) any public models built with test failures in the last 24 hours. The Discovery API is the source; a scheduled query populates a small governance table, and a BI tool renders the dashboard.

  • The metrics. Contract coverage %, latest-version %, consumer count per public model, test failure count.
  • The source. Discovery API GraphQL queries.
  • The materialisation. A small governance_public_models table refreshed nightly.

Question. Build the GraphQL query, the loading Python job, and the SQL for the dashboard.

Input.

Metric Query source
Contract coverage Discovery API — contract.enforced field
Latest version Discovery API — latestVersion vs versions
Consumer count Discovery API — dependents field
Test status Discovery API — runResults field

Code.

# Discovery API — governance query
query GovernanceMetrics {
  environment(id: "prod") {
    definition {
      publicModels: applied {
        publicModels(first: 200) {
          edges {
            node {
              name
              projectName
              access
              contract {
                enforced
              }
              latestVersion
              versions {
                v
                deprecationDate
              }
              dependents {
                totalCount
              }
              runResults(last: 1) {
                status
                testResults {
                  status
                  testName
                }
              }
            }
          }
        }
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode 
# Load into a governance table nightly
import requests
import os
import json
from datetime import datetime

def load_governance_snapshot(warehouse_conn):
    resp = requests.post(
        "https://cloud.getdbt.com/api/discovery",
        json={"query": open("governance.graphql").read()},
        headers={"Authorization": f"Bearer {os.environ['DBT_CLOUD_TOKEN']}"},
    )
    edges = resp.json()["data"]["environment"]["definition"]["publicModels"]["publicModels"]["edges"]

    with warehouse_conn.cursor() as cur:
        cur.execute("truncate table governance_public_models")
        for e in edges:
            m = e["node"]
            cur.execute(
                """
                insert into governance_public_models (
                    snapshot_ts, project_name, model_name, contract_enforced,
                    latest_version, deprecated_versions, dependent_count,
                    last_run_status, failed_test_count
                ) values (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                """,
                (
                    datetime.utcnow(),
                    m["projectName"],
                    m["name"],
                    m["contract"]["enforced"],
                    m["latestVersion"],
                    [v["v"] for v in m["versions"] if v.get("deprecationDate")],
                    m["dependents"]["totalCount"],
                    m["runResults"][0]["status"] if m["runResults"] else None,
                    sum(1 for t in (m["runResults"][0]["testResults"] if m["runResults"] else []) if t["status"] == "fail"),
                ),
            )
Enter fullscreen mode Exit fullscreen mode 
-- Governance dashboard queries
-- Metric 1: contract coverage per project
select
    project_name,
    count(*)                              as total_public_models,
    sum(case when contract_enforced then 1 else 0 end) as with_contract,
    round(
        sum(case when contract_enforced then 1 else 0 end)::numeric
        / nullif(count(*), 0)::numeric * 100.0,
        1
    ) as contract_coverage_pct
from governance_public_models
group by project_name
order by contract_coverage_pct;

-- Metric 2: models with deprecated versions still around
select project_name, model_name, deprecated_versions, dependent_count
from governance_public_models
where cardinality(deprecated_versions) > 0
  and dependent_count > 0
order by dependent_count desc;

-- Metric 3: public models with failing tests in the last 24h
select project_name, model_name, dependent_count, failed_test_count
from governance_public_models
where failed_test_count > 0
order by failed_test_count desc, dependent_count desc;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The GraphQL query pulls every public model across every project in the environment, along with contract status, versions, dependent count, and latest run result. The response is a single JSON blob keyed by project + model.
  2. The Python loader (a) hits the Discovery API endpoint with the query, (b) parses the response, and (c) writes one row per public model into governance_public_models. Truncate-and-load pattern; the table is a snapshot, not a slowly-changing dimension.
  3. The dashboard SQL runs three governance queries: contract coverage per project (should be 100% for a healthy mesh), models with deprecated versions still having consumers (the migration-progress dashboard), and public models with failing tests in the last 24h (the on-call view).
  4. The job runs on a nightly schedule. The governance dashboard reflects yesterday's state; for real-time incidents, the on-call still queries Discovery API directly. The materialised table is for the executive-facing weekly review, not for live incidents.
  5. The whole pipeline — GraphQL, Python loader, SQL dashboard — is roughly 100 lines of code. The mesh governance story is not a separate product; it's a thin layer over the existing Discovery API. Every senior analytics engineering team should ship this on day one of Mesh.

Output.

Governance metric Healthy value Alert threshold
Contract coverage per project 100% < 90%
Public models with active consumers on deprecated versions 0 > 0 after deprecation date
Public models with failing tests in last 24h 0 > 0 for 2 consecutive scrapes
Public models on latest version 100% < 95%

Rule of thumb. Every mesh needs a governance dashboard powered by Discovery API. Contract coverage, deprecation-window compliance, and public-model test health are the three metrics an engineering lead actually needs to see weekly.

Senior interview question on cross-project refs + Explorer + Discovery API

A senior interviewer might ask: "You're rolling out a new mesh with 4 projects and 15 public models. Walk me through how you'd wire cross-project refs, what you'd build in Explorer, and what governance you'd automate via the Discovery API — including one CI check that would prevent a bad public-model change from merging."

Solution Using cross-project refs + Explorer + Discovery-API-driven CI 

# projects/analytics/dependencies.yml
projects:
  - name: platform
  - name: orders
  - name: payments
  - name: marketing
Enter fullscreen mode Exit fullscreen mode 
-- projects/analytics/models/marts/mart_customer_360.sql
with
o as (select customer_id, sum(gross_amount) as gross_ltv
      from {{ ref('orders', 'public_orders', v=2) }}
      group by customer_id),
p as (select customer_id, sum(amount - coalesce(refund_amount, 0)) as net_ltv
      from {{ ref('payments', 'public_payments') }}
      group by customer_id),
m as (select customer_id, first_campaign_id, attribution_channel
      from {{ ref('marketing', 'public_lead_attribution') }}),
c as (select customer_id, email, signup_ts, country_code
      from {{ ref('orders', 'public_customers', v=2) }})

select
    c.customer_id,
    c.email,
    c.signup_ts,
    c.country_code,
    coalesce(o.gross_ltv, 0)              as gross_ltv,
    coalesce(p.net_ltv, 0)                as net_ltv,
    m.attribution_channel,
    m.first_campaign_id
from c
left join o using (customer_id)
left join p using (customer_id)
left join m using (customer_id)
Enter fullscreen mode Exit fullscreen mode 
# CI check — Discovery API query that fails a PR removing a public model with consumers
# .github/actions/public-model-safety/main.py
import os
import sys
import subprocess
import requests

def public_models_in_pr() -> set[str]:
    """Detect public models touched by this PR."""
    diff = subprocess.check_output(["git", "diff", "--name-only", "origin/main..."]).decode()
    return {f for f in diff.splitlines() if f.endswith(".sql") and "/marts/public_" in f}

def dependents_of(model_name: str) -> int:
    query = """
    query {
      environment(id: "prod") {
        definition {
          publicModels(filter: {name: "%s"}) {
            edges {
              node { dependents { totalCount } }
            }
          }
        }
      }
    }
    """ % model_name
    resp = requests.post(
        "https://cloud.getdbt.com/api/discovery",
        json={"query": query},
        headers={"Authorization": f"Bearer {os.environ['DBT_CLOUD_TOKEN']}"},
    )
    edges = resp.json()["data"]["environment"]["definition"]["publicModels"]["edges"]
    return sum(e["node"]["dependents"]["totalCount"] for e in edges)

def main():
    removed_or_broken = [f for f in public_models_in_pr() if not os.path.exists(f)]
    for m in removed_or_broken:
        model_name = os.path.basename(m).replace(".sql", "")
        count = dependents_of(model_name)
        if count > 0:
            print(f"BLOCK: PR removes public model {model_name} — {count} downstream consumers")
            sys.exit(1)
    print("OK: no public models with active consumers were removed")

if __name__ == "__main__":
    main()
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Step Action Effect
1 dependencies.yml declares 4 upstream projects dbt pulls their manifests at compile
2 Cross-project ref() calls across 4 projects Explorer renders the DAG with cross-project edges
3 Discovery API weekly governance dashboard Contract coverage, deprecation compliance visible
4 CI check on public-model removal PR blocked if consumers exist
5 Slack reminder for consumers on deprecated versions Migration progress tracked without human coordination

The end-state is a mesh where every cross-project reference is deliberate, every access violation is caught at compile, every breaking change goes through the version protocol, and every governance decision is code — not tribal knowledge.

Output:

Surface Result
Cross-project compile failures zero (all refs resolve cleanly)
Contract coverage 100% across public models
Deprecation window compliance tracked weekly, zero missed windows
PR-time public-model removals with consumers zero (CI blocks)
Explorer lineage completeness one continuous DAG across all 4 projects

Why this works — concept by concept:

  • dependencies.yml as first-class dependency graph — the file is the single source of truth for cross-project deps. dbt uses it to fetch manifests; Explorer uses it to render the DAG; Discovery API uses it to answer "who depends on whom."
  • Compile-time enforcement — access levels, contracts, and version pins are all checked at compile. No compute runs; no data moves. Bad references are caught before any warehouse cost is incurred.
  • Explorer for humans + Discovery API for automation — Explorer is the interactive view; Discovery API is the programmatic view. Every mesh needs both, and the automation surface is where the mesh's operational leverage lives.
  • CI check as a safety net — the public-model-removal CI check is 30 lines of Python. Not writing it is the reason "we accidentally deleted a public model with 12 consumers" incidents happen. Ship it on day one.
  • Cost — the infrastructure cost is O(1) — one Discovery API endpoint, one Explorer UI, one CI check. The recurring cost is one weekly dashboard review. The avoided cost is one 4-hour production incident per quarter, comfortably.

SQL
Topic — sql
SQL lineage and dependency-graph problems

Practice →

ETL Topic — etl ETL problems on cross-project pipelines

Practice →

5. Migration + governance

The five governance patterns every mesh lead ships — migration path, ownership matrix, deprecation policy, slim CI, contract enforcement

The mental model in one line: a healthy dbt data mesh in production is the cumulative effect of a domain-by-domain migration path, an ownership matrix coded in CODEOWNERS + dbt_project.yml, a deprecation policy with a 4–6 week runway, slim CI at each project boundary, and contract enforcement on every public model — miss any one, and the mesh degrades to "monolith with more repos". None of these is exotic; missing any one is a governance failure waiting to happen.

Catalog and migration diagram — a wide 'public catalog' card at the top with searchable public-model tiles and a compass icon, and a bottom half showing a large 'one-repo monolith' block on the left with a curved orange 'split by team' arrow arcing right to three interlocking hex project-cells labelled finance / marketing / platform with step chips 'pick boundaries', 'lift public', 'add deps' on a light PipeCode card.

Pattern 1 — the migration path in full.

  • Extract one domain first. Never split all domains simultaneously — the coordination cost is quadratic. Pick the domain with the clearest ownership, the fewest cross-team dependencies, and the smallest surface area.
  • Publish the public models. Before flipping consumers, ensure the extracted domain's public API is stable. Contracts on every public model; versions declared even for v1.
  • Cutover consumers one at a time. Consumer projects migrate their ref() calls from monolithic ref('model') to cross-project ref('project', 'public_model'). One consumer PR per week.
  • Archive the monolith for that domain. After all consumers migrate, delete the domain's models from the monolith. The monolith shrinks with each domain extracted.
  • Repeat. Extract the next domain. The playbook stabilises after the first extraction; extractions 2, 3, 4 are progressively faster.

Pattern 2 — the ownership matrix.

  • Project-level. CODEOWNERS at the repository level maps projects/<name>/** to exactly one GitHub team.
  • Model-level. Every public model has meta.owner, meta.slack, meta.escalation, meta.sla_hours — surfaced in Explorer and Discovery API.
  • Project-level metadata. dbt_project.yml vars records the owning team, primary Slack channel, and on-call rotation.
  • The invariant. Every model has exactly one owner. Shared ownership is disallowed; disputes are resolved by moving the model to the domain that creates the underlying entity.

Pattern 3 — the deprecation policy.

  • Runway. 4 weeks minimum, 6 weeks recommended. Long enough for downstream consumers to plan and ship migrations; short enough that v1 and v2 don't linger indefinitely.
  • Announcement. deprecation_date: in schema.yml (compile-time warnings) + Slack announcement in #data-consumers + Discovery-API-driven migration progress dashboard.
  • The escalation. Consumers still on v1 within 1 week of deprecation date get a personal Slack ping from the producer's owner (surfaced from meta.owner).
  • The removal. After the deprecation date, the producer's PR that removes v1 merges cleanly. Any straggler consumer fails compile — a hard nudge to migrate immediately.

Pattern 4 — slim CI at the project boundary.

  • In-project CI. dbt build --select state:modified+ — builds only the changed models plus their in-project downstream. 5–15 minutes wall time.
  • Cross-project CI trigger. Public-model contract changes trigger a downstream dbt compile in every consumer project. If the compile fails, the PR is blocked. This is the mesh's "cross-team CI" — cheap, targeted, and surface-only (no data movement).
  • Full-mesh CI on schedule. Once a day, run a full-mesh dbt build --select +tag:public across every project. Catches drift that in-PR checks might miss.

Pattern 5 — contract enforcement on every public model.

  • The invariant. Every access: public model has contract: {enforced: true}. Contract without enforcement is theatre.
  • The CI check. A governance check enumerates public models and asserts contract enforcement; a PR that adds a public model without a contract is blocked.
  • The compliance report. Discovery-API-driven weekly governance dashboard shows contract coverage per project; leadership expects 100% and any dip is an audit trail.

Common interview probes on migration + governance.

  • "How do you extract the first domain from a monolith?" — pick a domain with clear ownership, publish public models, cutover consumers, archive.
  • "What's the deprecation window for a breaking change?" — 4–6 weeks; enforced via deprecation_date and Discovery API monitoring.
  • "How do you enforce that every public model has a contract?" — CI check against Discovery API; PR blocks if a public model lacks contract: {enforced: true}.
  • "What's slim CI in a mesh context?" — per-project state:modified+ build plus cross-project contract compile check on changed public models.

Worked example — extracting the first domain from a monolith

Detailed explanation. The team decides to extract orders first (clearest ownership, largest CI-time saver). Walk through the four-week extraction, week by week, including the monorepo layout change, the moved models, the published contracts, and the consumer cutover.

  • Week 1. Scaffold the orders project inside the monorepo (projects/orders); move the orders-domain models; ensure it builds green in isolation.
  • Week 2. Define the public API (public_orders, public_order_lines, public_customers) with contracts; declare v1 with a placeholder for future versions.
  • Week 3. Migrate downstream consumers one at a time — payments' marts, marketing's marts, product's marts. Each migration is a PR that changes ref('orders_model') to ref('orders', 'public_orders').
  • Week 4. Archive orders-domain models in the old monolith; enable per-project CI; ship the governance dashboard.

Question. Produce the week-by-week deliverables, the file-tree changes, and the CI configuration.

Input.

Week Deliverable Success criterion
1 orders project scaffolded cd projects/orders && dbt build green
2 Public API published 3 public models with contracts
3 Consumers migrated 100% of consumer refs use ref('orders', ...)
4 Monolith cleanup Orders models removed from monolith; per-project CI live

Code.

# Week 1 — file tree change
# BEFORE:
#   /
#   ├── dbt_project.yml         (monolith)
#   ├── models/
#   │   ├── staging/orders/
#   │   ├── intermediate/orders/
#   │   └── marts/orders/
#   └── ...

# AFTER (week 1):
#   /
#   ├── dbt_project.yml         (monolith, orders models removed)
#   ├── projects/
#   │   └── orders/
#   │       ├── dbt_project.yml
#   │       ├── models/
#   │       │   ├── staging/
#   │       │   ├── intermediate/
#   │       │   └── marts/
#   │       └── dependencies.yml   (empty for now)
#   └── ...

# projects/orders/dbt_project.yml
name: orders
version: 1.0.0
profile: prod
model-paths: [models]
Enter fullscreen mode Exit fullscreen mode 
# Week 2 — publish the public API
# projects/orders/models/marts/schema.yml
version: 2
models:
  - name: public_orders
    access: public
    contract: {enforced: true}
    versions:
      - v: 1
    columns:
      - {name: order_id,     data_type: bigint,        constraints: [{type: not_null}, {type: primary_key}]}
      - {name: customer_id,  data_type: bigint,        constraints: [{type: not_null}]}
      - {name: gross_amount, data_type: numeric(18,2), constraints: [{type: not_null}]}
      - {name: status,       data_type: varchar}
      - {name: order_ts,     data_type: timestamp}
  - name: public_order_lines
    access: public
    contract: {enforced: true}
    versions: [{v: 1}]
    columns:
      - {name: order_line_id, data_type: bigint, constraints: [{type: not_null}]}
      - {name: order_id,      data_type: bigint}
      - {name: product_id,    data_type: bigint}
      - {name: quantity,      data_type: int}
      - {name: unit_price,    data_type: numeric(18,2)}
  - name: public_customers
    access: public
    contract: {enforced: true}
    versions: [{v: 1}]
    columns:
      - {name: customer_id, data_type: bigint, constraints: [{type: not_null}, {type: primary_key}]}
      - {name: email,       data_type: varchar}
      - {name: signup_ts,   data_type: timestamp, constraints: [{type: not_null}]}
Enter fullscreen mode Exit fullscreen mode 
# Week 3 — consumer migration (payments PR)
# projects/payments/dependencies.yml
projects:
  - name: orders

# projects/payments/models/marts/mart_finance_daily.sql
# BEFORE (monolithic ref)
# select ... from {{ ref('public_orders') }}

# AFTER (cross-project ref)
select ...
from {{ ref('orders', 'public_orders', v=1) }}
Enter fullscreen mode Exit fullscreen mode 
# Week 4 — CI per project
# .github/workflows/orders-ci.yml
name: orders-ci
on:
  pull_request:
    paths: ['projects/orders/**']
jobs:
  build:
    runs-on: ubuntu-latest
    defaults: {run: {working-directory: projects/orders}}
    steps:
      - uses: actions/checkout@v4
      - run: |
          dbt deps
          dbt build --select state:modified+

# .github/workflows/monolith-ci.yml
name: monolith-ci
on:
  pull_request:
    paths: ['models/**', 'dbt_project.yml']
jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: |
          dbt deps
          dbt build --select state:modified+
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Week 1 focuses on the mechanical scaffold: create projects/orders/, move the orders-domain models, adjust ref paths, ensure the project builds green in isolation. No consumer changes yet. The old monolith still contains a copy of the orders models — deliberately, so consumers keep working during the extraction.
  2. Week 2 focuses on the public API. Every model destined to be consumed by other domains is marked access: public with contract: {enforced: true} and a versions: block starting at v1. This is the promise the orders team is making to the rest of the mesh; get it right before flipping consumers.
  3. Week 3 migrates consumers one at a time. Each consumer PR (a) adds - name: orders to dependencies.yml and (b) changes ref('public_orders') to ref('orders', 'public_orders', v=1). The change is mechanical and non-breaking — the data is the same; the SQL is one line different. Reviewers focus on lineage correctness, not on migration risk.
  4. Week 4 archives the orders models in the old monolith. Once all consumers use the cross-project ref, the monolith's stg_orders, int_orders, public_orders etc. can be removed; the CI reflects the new topology (monolith-ci for what's left; orders-ci for the extracted project).
  5. The governance dashboard goes live at end of week 4: contract coverage for orders' public models is 100%; consumer count per public model matches expectations; the migration success metric is "zero downstream references to the monolithic public_orders."

Output.

Week Deliverable Metric
1 orders project scaffolded Green in-isolation build
2 3 public models with contracts 100% contract coverage
3 Consumers migrated 0 remaining monolithic refs
4 Monolith cleaned orders models deleted from monolith

Rule of thumb. Extract one domain per month, not one per week. The bottleneck is consumer migration coordination, not the mechanical work of moving files. Move faster than the consumers can absorb and you accumulate half-migrated consumer projects that block future extractions.

Worked example — the deprecation policy in action

Detailed explanation. Six months into the mesh, orders wants to change public_orders's gross_amount type. The deprecation policy — 6 weeks, Discovery-API monitoring, Slack pings for laggards — turns a potentially chaotic breaking change into a predictable, documented migration.

  • Change. gross_amount numeric(18,2) → numeric(20,4) (higher precision for international multi-currency).
  • Consumers. 12 downstream projects use public_orders.
  • Policy. Publish v2; set v1's deprecation_date for 6 weeks out; weekly Discovery API check; personal Slack ping for laggards; drop v1 after deprecation date.

Question. Show the deprecation policy as code — schema.yml, CI check, and the Slack ping automation.

Input.

Metric Day 0 Day 21 Day 42
Consumers on v1 12 6 0
Consumers on v2 0 6 12
Compile warnings emitted 12 6 0
Slack pings sent 0 3 0

Code.

# projects/orders/models/marts/schema.yml
version: 2
models:
  - name: public_orders
    latest_version: 2
    access: public
    contract: {enforced: true}
    versions:
      - v: 1
        deprecation_date: "2026-08-15"
        columns:
          - include: all
          - {name: gross_amount, data_type: numeric(18,2)}
      - v: 2
        columns:
          - include: all
          - {name: gross_amount, data_type: numeric(20,4)}
    columns:
      - {name: order_id,    data_type: bigint,    constraints: [{type: not_null}, {type: primary_key}]}
      - {name: customer_id, data_type: bigint,    constraints: [{type: not_null}]}
      - {name: status,      data_type: varchar}
      - {name: order_ts,    data_type: timestamp}
Enter fullscreen mode Exit fullscreen mode 
# scripts/deprecation_slack.py — weekly cron
import os
import requests
from datetime import date, timedelta

DEPRECATION_DATE = date(2026, 8, 15)

def consumers_on_deprecated_version(model_name: str, project: str, version: int) -> list[dict]:
    query = """
    query {
      environment(id: "prod") {
        definition {
          publicModels(filter: {name: "%s", projectName: "%s", version: "%d"}) {
            edges { node { dependents { nodes { projectName filePath } } } }
          }
        }
      }
    }
    """ % (model_name, project, version)
    resp = requests.post(
        "https://cloud.getdbt.com/api/discovery",
        json={"query": query},
        headers={"Authorization": f"Bearer {os.environ['DBT_CLOUD_TOKEN']}"},
    )
    edges = resp.json()["data"]["environment"]["definition"]["publicModels"]["edges"]
    return [d for e in edges for d in e["node"]["dependents"]["nodes"]]

def post_to_slack(msg: str, channel: str):
    requests.post(
        "https://slack.com/api/chat.postMessage",
        headers={"Authorization": f"Bearer {os.environ['SLACK_TOKEN']}"},
        json={"channel": channel, "text": msg},
    )

def main():
    days_left = (DEPRECATION_DATE - date.today()).days
    consumers = consumers_on_deprecated_version("public_orders", "orders", 1)
    if not consumers:
        return

    if days_left <= 7:
        # Personal ping
        for c in consumers:
            post_to_slack(
                f":warning: {c['projectName']} — {c['filePath']} still on public_orders v1 "
                f"({days_left} days until removal). Please migrate.",
                channel=f"#team-{c['projectName']}"
            )
    else:
        # Weekly summary in shared channel
        summary = f"{len(consumers)} consumers still on public_orders v1 (deprecation: {DEPRECATION_DATE})"
        post_to_slack(summary, "#data-consumers")

if __name__ == "__main__":
    main()
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Day 0: orders team ships the schema.yml change + public_orders_v2.sql. Both versions build. Consumers see no immediate change — v1 continues to work.
  2. Day 0–35: deprecation_date on v1 emits compile-time warnings on every downstream dbt compile. The weekly Slack summary posts to #data-consumers. Consumers migrate at their own pace; the count of v1 consumers declines steadily.
  3. Day 35 (T-7 days): the automation switches from "weekly summary" to "personal ping." Each remaining v1 consumer gets a Slack message in their team channel naming the specific file that still refs v1.
  4. Day 42 (deprecation date): the producer PR that removes v1 merges. If any consumer is still on v1, their next compile fails — a hard nudge to migrate immediately. In practice, the personal-ping cadence usually gets everyone across the line by T-1 day.
  5. Day 45: the Discovery API dashboard shows zero consumers on v1. The producer team runs dbt run-operation drop_deprecated_v1 (or the warehouse-specific cleanup script) to drop the old materialised table.

Output.

Day Consumers on v1 Slack action
0 12 Weekly summary posted
7 10 Weekly summary posted
14 8 Weekly summary posted
21 6 Weekly summary posted
28 4 Weekly summary posted
35 (T-7) 3 Personal pings to each consumer team
42 (deprecation) 0 v1 removed

Rule of thumb. The deprecation window is 6 weeks + personal pings in the last week. Shorter windows create panic; longer windows create indifference. Six weeks matches the typical sprint cadence and gives every consumer team two sprint boundaries to plan around.

Worked example — slim CI across projects

Detailed explanation. The mesh's CI story has two layers: per-project CI (fast, isolated, changes-only) and cross-project CI (contract compile check on public-model changes). The combination catches all breakages that a monolithic CI would catch, but with a fraction of the wall time and shared-resource contention.

  • Per-project CI. dbt build --select state:modified+ on each project's PRs. 5–15 minutes.
  • Cross-project CI. When a PR modifies a access: public model, a downstream dbt compile runs in every consumer project. Compile only; no data. 2–5 minutes per consumer.
  • Nightly full-mesh CI. Full dbt build --select +tag:public across every project. Catches drift missed by in-PR checks. 30–60 minutes; runs during off-peak hours.

Question. Configure the GitHub Actions workflows for the three CI layers, and show what happens when a PR modifies public_orders.

Input.

Layer Trigger Runtime Cost
Per-project project's own PR 5–15 min in-PR
Cross-project public-model change 2–5 min per consumer in-PR
Nightly full-mesh cron 30–60 min off-peak

Code.

# .github/workflows/orders-ci.yml — per-project
name: orders-ci
on:
  pull_request:
    paths: ['projects/orders/**']
jobs:
  build:
    runs-on: ubuntu-latest
    defaults: {run: {working-directory: projects/orders}}
    outputs:
      public_changed: ${{ steps.detect.outputs.public_changed }}
    steps:
      - uses: actions/checkout@v4
      - name: Detect public-model change
        id: detect
        run: |
          if git diff --name-only origin/main... | grep -E 'projects/orders/models/marts/public_'; then
            echo "public_changed=true" >> "$GITHUB_OUTPUT"
          else
            echo "public_changed=false" >> "$GITHUB_OUTPUT"
          fi
      - name: dbt build (project-scoped)
        run: |
          dbt deps
          dbt build --select state:modified+

  # Trigger downstream cross-project compile if a public model changed
  cross-project-check:
    needs: build
    if: needs.build.outputs.public_changed == 'true'
    runs-on: ubuntu-latest
    strategy:
      matrix:
        consumer: [payments, marketing, analytics]
    steps:
      - uses: actions/checkout@v4
      - name: dbt compile downstream
        working-directory: projects/${{ matrix.consumer }}
        run: |
          dbt deps
          dbt compile
Enter fullscreen mode Exit fullscreen mode 
# .github/workflows/nightly-mesh.yml — full-mesh build
name: nightly-mesh
on:
  schedule:
    - cron: '0 6 * * *'   # 06:00 UTC daily
jobs:
  build:
    runs-on: ubuntu-latest
    strategy:
      matrix:
        project: [platform, orders, payments, marketing, analytics]
    steps:
      - uses: actions/checkout@v4
      - name: dbt build (full public surface)
        working-directory: projects/${{ matrix.project }}
        run: |
          dbt deps
          dbt build --select +tag:public
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The per-project CI runs on every PR to that project. state:modified+ bounds the build to models the PR changed plus their in-project downstream. Typical wall time is 5–15 minutes.
  2. If the PR touches a public_* model (detected by filename convention or dbt manifest diff), the CI triggers a downstream matrix job — one dbt compile per consumer project. Compile only; no data; no cost. Any consumer whose SQL breaks against the new public-model contract fails at compile.
  3. The cross-project check is compile, not build. Building would require running the upstream project first, which defeats the "no data" property. Compile verifies that the SQL is valid against the new manifest, which is enough to catch contract-shape breakages.
  4. The nightly full-mesh CI runs a full dbt build on every project's public models. This catches any drift that in-PR checks might miss — for example, a manifest exchange bug or a subtle contract-vs-warehouse discrepancy that only surfaces on a real build.
  5. The three-layer CI has the same coverage as a monolithic CI (every model is built at least once daily) but the per-PR cost is 4–10× cheaper. Reviewers pay for their own changes; nightly automation pays for the shared drift check.

Output.

Scenario Layers triggered Wall time
PR modifies orders internal model orders per-project 8 min
PR modifies orders public model orders per-project + 3 consumer compiles 8 + 3×3 = 17 min
Nightly full-mesh 5 projects in parallel 40 min
Full monolithic CI (hypothetical) 500 models 60 min

Rule of thumb. Slim CI has three layers: per-project build (5–15 min), cross-project compile on public-model changes (2–5 min per consumer), and nightly full-mesh build (30–60 min). Every mesh needs all three; skipping any one creates a coverage gap that inevitably becomes a production incident.

Senior interview question on migration + governance

A senior interviewer might ask: "You're the analytics engineering lead. Walk me through the first quarter of a mesh migration from a 500-model monolith — the extraction sequence, the governance dashboards you'd stand up, the deprecation policy, and one CI check you'd ship on day one to protect the public API."

Solution Using a domain-per-month extraction, coded governance, and Discovery-API-driven CI 

Quarter-1 mesh migration — 500-model monolith → 4-project mesh
==============================================================

Month 1 — Extract orders (the clearest ownership)
   Week 1 — scaffold + move models + green in-isolation build
   Week 2 — publish public API (3 public models, contracts, v1)
   Week 3 — migrate 8 downstream consumers to ref('orders', ...)
   Week 4 — archive orders models in monolith + per-project CI live
   Deliverable — orders project in production; monolith down 100 models

Month 2 — Extract payments (depends on orders' public API)
   Week 1 — scaffold + move models + declare orders as dep
   Week 2 — publish public API (2 public models, contracts, v1)
   Week 3 — migrate 6 downstream consumers
   Week 4 — archive payments models in monolith
   Deliverable — payments project in production; monolith down 200 models

Month 3 — Extract marketing + platform (parallel, low-risk)
   Weeks 1–4 — same playbook, run in parallel
   Deliverable — full 4-project mesh; monolith archived

Continuous — Governance
   - Discovery API weekly dashboard: contract coverage, deprecation compliance
   - Slack automation: v1 laggard pings 7 days before deprecation
   - CI check on day 1: block PR if public model removed with active consumers
   - Nightly full-mesh CI: catch drift
Enter fullscreen mode Exit fullscreen mode 
# Day-1 CI check — block PR that removes a public model with active consumers
# .github/actions/public-model-safety/main.py (repeated for emphasis)
import os
import sys
import subprocess
import requests

def removed_public_models() -> set[str]:
    diff = subprocess.check_output(["git", "diff", "--diff-filter=D", "--name-only", "origin/main..."]).decode()
    return {os.path.basename(f).replace(".sql", "")
            for f in diff.splitlines()
            if "/marts/public_" in f and f.endswith(".sql")}

def dependents_of(model_name: str) -> int:
    query = """
    query {
      environment(id: "prod") {
        definition {
          publicModels(filter: {name: "%s"}) {
            edges { node { dependents { totalCount } } }
          }
        }
      }
    }
    """ % model_name
    resp = requests.post(
        "https://cloud.getdbt.com/api/discovery",
        json={"query": query},
        headers={"Authorization": f"Bearer {os.environ['DBT_CLOUD_TOKEN']}"},
    )
    edges = resp.json()["data"]["environment"]["definition"]["publicModels"]["edges"]
    return sum(e["node"]["dependents"]["totalCount"] for e in edges)

for model in removed_public_models():
    n = dependents_of(model)
    if n > 0:
        print(f"BLOCK: public model {model} removed with {n} active consumers")
        sys.exit(1)
print("OK")
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Month Activity Cumulative state
1 Extract orders orders project live; monolith -100 models
2 Extract payments payments project live; monolith -200 models
3 Extract marketing + platform 4-project mesh in production; monolith archived
Continuous Governance dashboard + Slack ping + CI check 100% contract coverage; zero un-migrated consumers

At end of quarter 1, the monolith is archived; four projects are in production; each project's CI runs in 15 minutes; the governance dashboard shows 100% contract coverage; and the day-one CI check has already caught one attempted breaking removal, saving a downstream outage.

Output:

Metric Start of Q1 End of Q1
Projects 1 (monolith) 4 (mesh)
Model count 500 500 (distributed)
CI wall time per PR 60 min 15 min per project
Contract coverage on public models undefined 100%
Cross-team blocking commits per month 5–8 0–1
Governance dashboard none live
Day-one CI checks none 3 (contract, removal, deprecation)

Why this works — concept by concept:

  • One domain per month — the sequenced extraction lets each extraction absorb the coordination cost of consumer migrations without piling them up. Big-bang splits accumulate 4-project consumer migrations and stall.
  • Public API before consumer cutover — publishing the public models with contracts before flipping any consumer means the API is stable when consumers migrate. Migrating consumers to a moving target creates rework.
  • Governance as code — every governance decision (contract coverage, deprecation compliance, public-model removal) is a Discovery API query wired into a CI check or a scheduled dashboard. Tribal knowledge is disallowed.
  • Day-one CI check — the public-model-removal safety net is 30 lines of Python. Ship it before the first extraction so no future PR can silently delete a public model with consumers.
  • Cost — one senior engineer plus each domain team's involvement for one quarter. The avoided cost is 5–8 cross-team blocking commits per month, saving ~40 engineer-hours monthly. The migration pays back in ~2 quarters and compounds thereafter.

SQL
Topic — sql
SQL mesh-migration and governance problems

Practice →

Optimization
Topic — optimization
Optimization problems on multi-project pipelines

Practice →

Cheat sheet — dbt Mesh recipes

  • When to split. ~300 models, ~10 engineers, or the second cross-team CI-blocking incident — whichever comes first. Direct-monolith work is fine below these thresholds; the mesh's operational overhead only pays back beyond them.
  • Domain-split boundary. One project per business domain (orders, payments, marketing, product) plus one platform project owning shared plumbing. The team boundary is the project boundary. Layer-driven (staging vs marts) and consumer-driven (finance-mart vs growth-mart) splits are anti-patterns.
  • Public API surface per project. 3–8 access: public models per project. Every public model has contract: {enforced: true} and a versions: block. More than 15 public models per project suggests the domain itself needs splitting.
  • Access-level defaults. Staging models = access: private; intermediate models = access: protected (default); public marts = access: public (explicit). Never rely on the protected default for staging — mark them private explicitly.
  • Public model config template. access: public, contract: {enforced: true}, latest_version: N, versions: [{v: 1}, {v: 2, ...}], meta: {owner, slack, escalation, sla_hours}. Contract lists every column with data_type, constraints, and description.
  • Cross-project ref template. dependencies.yml: projects: [{name: orders}, {name: platform}]. SQL: {{ ref('orders', 'public_orders', v=2) }}. Version pin is optional but recommended for breaking-change safety.
  • Deprecation window. 4 weeks minimum, 6 weeks recommended. Set deprecation_date on the old version in schema.yml. Emit compile-time warnings; run weekly Discovery API check; personal Slack ping to laggards in the final week; drop old version after date.
  • Contract vs data test. Contract = type-level (columns, types, nullability) enforced at build time; data test = value-level (row counts, distinct values, cross-model consistency) enforced post-build. Every public model needs both.
  • Discovery API impact-analysis query. publicModels(filter: {name: "X", projectName: "Y"}) { edges { node { dependents { nodes { projectName filePath } } } } }. Returns every downstream consumer file across every project. Use before breaking changes.
  • Slim CI stack. Per-project state:modified+ build (5–15 min), cross-project dbt compile on public-model change (2–5 min per consumer), nightly full-mesh dbt build --select +tag:public (30–60 min, off-peak). Three layers, complete coverage, 4–10× faster per-PR than monolithic CI.
  • Ownership matrix. CODEOWNERS at repository level (projects/<name>/** → team); dbt_project.yml vars for project owner + Slack + on-call; per-model meta for owner + Slack + escalation + SLA. Three complementary places; Explorer surfaces all three.
  • Governance dashboard. Discovery-API-fed weekly snapshot: contract coverage % per project, public models with active v1 consumers past deprecation date, public models with failing tests in 24h. Contract coverage should be 100%; any dip is an audit trail.
  • Day-one CI check. Discovery API query in a GitHub Action that blocks a PR removing a public model with active consumers. 30 lines of Python; ship on day one before the first extraction; catches the "I didn't know anyone was using that" class of incidents.
  • dbt Core vs dbt Cloud. Mesh works on both; ergonomics differ. Cloud has native manifest exchange, Explorer, and Discovery API. Core requires custom tooling for the manifest exchange and a self-hosted Discovery-API-like surface. For production Mesh, Cloud is the low-friction default.

Frequently asked questions

What is dbt Mesh and when do I actually need it?

dbt Mesh is the multi-project architecture pattern shipped by dbt Labs across dbt-core 1.6 and dbt Cloud 2023–2026, where a single logical data warehouse is modelled as N domain-aligned dbt projects that expose small, versioned, contract-enforced public APIs to each other via access: public models and cross-project {{ ref('project', 'model') }} calls. You need it when the monolith stops scaling with the team rather than the warehouse — typically around 300 models, 10 engineers, or the second cross-team CI-blocking incident in a quarter. Below those thresholds, a monolithic dbt project with folder-based ownership and shared CI is fine and simpler. Above them, the mesh pays for itself in weeks: 4× faster CI, crisp ownership, contract-enforced schema drift catching, and cross-team lineage in Explorer. The right way to think about Mesh is as "microservices for analytics engineering" — bounded contexts, published APIs, versioned contracts — reapplied to the dbt DAG.

Do I need dbt Cloud for Mesh, or does dbt Core support it?

dbt Core supports Mesh mechanically — dbt deps can pull an upstream project as a package, manifest exchange between projects is possible, and {{ ref('project', 'model') }} resolves against the imported manifest. In practice, Core-only Mesh requires significant custom tooling: manifest exchange in CI, a Discovery-API-like surface for governance queries, and a lineage visualisation layer. dbt Cloud is the ergonomic default for production Mesh — native manifest exchange between projects (no CI plumbing), dbt Cloud Explorer for cross-team lineage and impact analysis (no self-hosted UI), Discovery API for programmatic governance (no custom GraphQL server), and access + contract enforcement wired end-to-end. For most senior analytics engineering teams evaluating Mesh, the recommendation is Cloud for production and Core for local development / smaller experiments.

Public vs protected vs private models — when do I pick each?

access: public is the API — any project (declaring your project in dependencies.yml) can {{ ref('project', 'model') }} it. Public models must have contract: {enforced: true} and a versions: block; they are a promise the owning team maintains under a deprecation policy. Use for 3–8 mart-layer models per project that other domains genuinely consume. access: protected is the default; the model is referenceable within the same group in the same project. Use for intermediate models and internal marts that sub-teams inside the project share. access: private is the most restrictive — the model can only be referenced from within the same group in the same project. Use for staging models and one-off transformations. The most common mistake is leaving staging models at the protected default; explicit private prevents accidental intra-project coupling that survives the mesh split unintentionally. The rule: staging = private, intermediate = protected, public marts = public, and always be explicit.

How do I version a public model without breaking downstream consumers?

Every breaking change to an access: public model goes through the versions: block with a deprecation window. Publish v2 alongside v1 by adding - v: 2 to the versions: block and creating public_model_v2.sql with the new shape. Both versions build, materialise, and are queryable. Set deprecation_date on v1 for 4–6 weeks out — dbt emits compile-time warnings for every consumer still on v1, and the latest_version moves to 2. Monitor consumer migration via Discovery API — a weekly GraphQL query returns the count of consumers still on v1, and a Slack automation pings laggards individually 7 days before the deprecation date. Drop v1 after the deprecation date in a producer PR; any straggler consumer fails compile — a hard nudge to migrate immediately. The whole protocol is code — schema.yml, Discovery API, Slack — not manual coordination. The recommendation is to reserve the version protocol for genuinely breaking changes (type changes, column removals) and to ship non-breaking additions (new nullable columns) as a normal PR without a version bump.

Contract vs schema test — what's the difference and which do I need?

Model contracts are type-level declarations enforced at build time: contract: {enforced: true} in schema.yml lists every column with data_type, nullability, and constraints. dbt runs create table (schema) as select ... and fails the build if the SQL output doesn't match the declaration. Contracts freeze the shape of the model — column names, types, and nullability. Schema tests (unique, not_null, accepted_values, relationships, dbt_utils.expression_is_true, etc.) are value-level assertions run after the build — they check that the data satisfies constraints beyond just the schema. Tests can catch data-quality issues that a contract cannot — a unique test catches accidental duplicates; a relationships test catches broken foreign keys; a custom expression catches business-rule violations. Every public model needs both — a contract to freeze the shape (schema drift = build fails inside the producing project) and a test suite to validate the values (data quality = post-build alert). Ship them together on every public model; contracts without tests are shape without substance; tests without contracts are substance without shape.

Should I always split my dbt project into Mesh?

No — the mesh is not universally the right answer. Below ~300 models and ~10 engineers, a monolithic dbt project with folder-based ownership, CODEOWNERS, and a shared CI is simpler and pays back faster than a mesh migration. The operational overhead of the mesh — separate dbt_project.ymls, cross-project ref discipline, contract governance, deprecation windows, Discovery API monitoring — has fixed setup cost and only amortises across many teams. The trigger to split is one of three signals: (1) the second cross-team CI-blocking incident in a quarter, (2) 300+ models where per-PR CI wall time exceeds 30 minutes, or (3) 10+ engineers where informal ownership is producing "who owns this model?" arguments weekly. Even when the trigger hits, extract one domain first, prove the pattern, and only then extract the second and third. Big-bang splits into 4 projects simultaneously usually fail at the coordination-cost step; the domain-per-month cadence is what actually ships. For teams below the threshold, invest in CODEOWNERS, groups, access: private/protected/public, and contracts inside the monolith — you'll get 60% of the mesh's benefits with 10% of the setup cost, and you'll be ready to split cleanly when the threshold arrives.

Practice on PipeCode

  • Drill the SQL practice library → for the modelling, dimensional-design, and schema-drift problems that translate directly to dbt Mesh public-model contracts.
  • Rehearse on the ETL practice library → for the multi-project pipeline patterns, orchestration boundaries, and cross-team data contracts that mesh migrations depend on.
  • Sharpen the modelling axis with the optimization practice library → for the data-model architecture, versioned API, and cross-team governance problems senior interviewers probe.
  • Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the domain-split + contracts + versioning intuition against real graded inputs.

Lock in dbt Mesh muscle memory

dbt docs explain the config. PipeCode drills explain the decision — when to split a monolith, which model becomes `access: public`, when a contract change needs a version bump, and how to migrate 15 downstream consumers on a 6-week deprecation window. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the modelling and governance trade-offs senior analytics engineers actually face.

Practice SQL problems →
Practice optimization problems →

Top comments (0)