Our engine never sees your warehouse password
datahashi

BlogGovernance & trust

How to survive warehouse schema changes without breaking dashboards

Upstream dbt jobs renamed an invoice column to amount_usd, but central semantic mapping and model tests kept dashboards running without a blip.

The datahashi team7 min read

The problem

At 6:00 AM, the data engineering team deploys a database migration across the central warehouse. Upstream dbt transformation models standardize multi-currency accounting. As part of this refactor, engineers rename the raw column amount on the physical invoices table to amount_usd.

By 8:30 AM, analytics engineers wake up to an incident channel full of alerts. Forty-five executive dashboards in Metabase, Looker, and internal portals show broken chart widgets. Each widget fired raw SQL statements selecting SUM(amount). Upstream teams need the freedom to refactor and optimize database storage. Downstream consumers cannot tolerate broken dashboards every time an engineer cleans up a database table name.

You need a decoupled semantic layer that absorbs physical database migrations. When upstream column names change, you update one central mapping file, run automated model verification tests, and keep every downstream dashboard running without edits.

+----------------------------------+
| Upstream Data Engineering        |
| Migration: rename amount to      |
| amount_usd in warehouse tables   |
+----------------------------------+
                 |
                 v
   Central Semantic Layer Mapping
   (absorbs physical column rename)
                 |
                 +--------------------------------+
                 |                                |
                 v                                v
    Dashboard 1: total_revenue       Dashboard 2: total_revenue
    (Zero widget breakage ✓)         (Zero widget breakage ✓)

The data

The database schema manages operational billing data across client accounts and issued invoice records. The accounts table stores company accounts, account subscription tiers, and country codes. The invoices table records individual billing invoices with the renamed physical column amount_usd.

+--------------------+              +----------------------+
|      accounts      |              |       invoices       |
+--------------------+              +----------------------+
| account_id (PK)    |<---+         | invoice_id (PK)      |
| account_tier       |    +---------| account_id (FK)      |
| country            |              | issued_at            |
+--------------------+              | amount_usd           |
                                    +----------------------+

Here is the SQL DDL and sample dataset runnable directly in DuckDB:

CREATE TABLE accounts (
    account_id VARCHAR PRIMARY KEY,
    account_tier VARCHAR NOT NULL,
    country VARCHAR NOT NULL
);

CREATE TABLE invoices (
    invoice_id VARCHAR PRIMARY KEY,
    account_id VARCHAR NOT NULL REFERENCES accounts(account_id),
    issued_at DATE NOT NULL,
    amount_usd DOUBLE NOT NULL
);

INSERT INTO accounts VALUES
    ('acc_01', 'Enterprise', 'US'),
    ('acc_02', 'Mid-Market', 'US'),
    ('acc_03', 'Enterprise', 'UK');

INSERT INTO invoices VALUES
    ('inv_01', 'acc_01', '2026-10-01', 50000.0),
    ('inv_02', 'acc_01', '2026-10-02', 35000.0),
    ('inv_03', 'acc_02', '2026-10-01', 12000.0),
    ('inv_04', 'acc_02', '2026-10-02', 18000.0),
    ('inv_05', 'acc_03', '2026-10-01', 42000.0),
    ('inv_06', 'acc_03', '2026-10-02', 28000.0);

The model

The semantic model cleanly separates physical database identifiers from governed business names. The Account entity maps to the accounts table. The Invoice entity maps to invoices.

Within the Invoice entity, the measure billed_amount points its SQL expression directly to the newly renamed physical column expr: amount_usd. Downstream consumers never select raw column names. They query the certified metric total_revenue.

The model also defines an automated model test named verify_enterprise_revenue. Before publishing the updated model artifact, the engine executes this test to verify that enterprise revenue on the new schema returns the exact expected balance.

source: warehouse_main

entities:
  - name: Account
    table: accounts
    primary_key: account_id
    dimensions:
      - name: account_id
        column: account_id
        type: string
      - name: account_tier
        column: account_tier
        type: string
      - name: country
        column: country
        type: string

  - name: Invoice
    table: invoices
    primary_key: invoice_id
    dimensions:
      - name: invoice_id
        column: invoice_id
        type: string
      - name: account_id
        column: account_id
        type: string
      - name: issued_at
        column: issued_at
        type: date
        grains: [day, month, quarter, year]
    measures:
      - name: billed_amount
        expr: amount_usd
        agg: sum
      - name: invoice_count
        expr: invoice_id
        agg: count

relationships:
  - name: invoice_account
    from: Invoice
    to: Account
    cardinality: many_to_one
    keys:
      - [account_id, account_id]

metrics:
  - name: total_revenue
    expr: billed_amount
    description: "Total recognized invoice revenue in USD"
    unit: USD
    format: currency
  - name: total_invoices
    expr: invoice_count
    description: "Total processed invoices count"

tests:
  - name: verify_enterprise_revenue
    description: "Ensure enterprise revenue on new amount_usd schema equals 155,000"
    query:
      metrics: [total_revenue]
      filters:
        - dimension: account_tier
          op: eq
          value: "Enterprise"
    expect:
      rows:
        - [155000]

Review the model specification for exact syntax rules on physical mapping. For earlier examples of model verification, see the guide on closing finance ARR balances.

The queries

All reporting tools and dashboards communicate through the protocol documented in the query specification. Clients query semantic concepts rather than table and column names. Where a request sets include_sql, the engine returns compiled SQL only if it was started with ENGINE_ALLOW_SQL=true. MCP never returns SQL.

Query 1: Canonical revenue by country and account tier

Downstream dashboards request total_revenue and total_invoices broken down by customer country and account tier. The query references business metric names:

{
  "metrics": ["total_revenue", "total_invoices"],
  "dimensions": [
    { "name": "country", "entity": "Account" },
    { "name": "account_tier", "entity": "Account" }
  ],
  "order": [
    { "ref": "total_revenue", "desc": true }
  ],
  "include_sql": true
}

The gateway returns the grouped results:

country account_tier total_revenue total_invoices
US Enterprise 85000 2
UK Enterprise 70000 2
US Mid-Market 30000 2

When compiled into warehouse SQL, the engine uses the new physical column amount_usd transparently:

SELECT
  q0."country" AS "country",
  q0."account_tier" AS "account_tier",
  CAST(q0."billed_amount" AS DOUBLE) AS "total_revenue",
  CAST(q0."invoice_count" AS DOUBLE) AS "total_invoices"
FROM (
  SELECT
    "Account"."country" AS "country",
    "Account"."account_tier" AS "account_tier",
    CAST(SUM("Invoice"."amount_usd") AS DOUBLE) AS "billed_amount",
    COUNT("Invoice"."invoice_id") AS "invoice_count"
  FROM "invoices" AS "Invoice"
  JOIN "accounts" AS "Account"
    ON "Invoice"."account_id" = "Account"."account_id"
  GROUP BY "Account"."country", "Account"."account_tier"
) AS q0
ORDER BY "total_revenue" DESC

Dashboards continue displaying valid revenue figures without changing a single line of client configuration.

Query 2: Executing automated model tests

Before publishing the updated model version to production, CI pipelines execute model tests via POST /v1/model-tests:

{}

The engine runs the declared test query against the database and validates the expected row values:

{
  "passed": true,
  "summary": {
    "total": 1,
    "passed": 1,
    "failed": 0
  },
  "results": [
    {
      "name": "verify_enterprise_revenue",
      "passed": true,
      "rows": 1
    }
  ]
}

The test confirms that the updated amount_usd column produces the expected 155000 for Enterprise accounts in this sample data. If upstream data teams altered column calculations or introduced invalid nulls, the automated test fails immediately in CI before publication.

Query 3: Inspecting downstream blast radius with lineage

Before modifying a measure or entity in the semantic model, analytics engineers inspect the downstream blast radius using POST /v1/lineage:

{
  "node": "measure:Invoice.billed_amount",
  "direction": "downstream"
}

The engine traverses the dependency graph and returns every node relying on billed_amount:

{
  "version": "m1:709b07631d82e60c78003d3f60d059c761f445b33aa7818199461639d962447c",
  "node": {
    "id": "measure:Invoice.billed_amount",
    "kind": "measure",
    "name": "billed_amount",
    "entity": "Invoice"
  },
  "downstream": [
    {
      "id": "metric:total_revenue",
      "kind": "metric",
      "name": "total_revenue"
    },
    {
      "id": "test:verify_enterprise_revenue",
      "kind": "test",
      "name": "verify_enterprise_revenue"
    }
  ]
}

The lineage response reveals that changing billed_amount reaches metric:total_revenue and test:verify_enterprise_revenue. Engineers know exactly which tests and metrics to verify before pushing changes.

Query 4: Refusal for unmapped warehouse column

If an external caller attempts to bypass the semantic catalog and request an arbitrary unmodeled column or old column name directly, the query engine refuses the request:

{
  "metrics": ["unmapped_amount"]
}

The API returns HTTP 404:

{
  "error": "model: resolve: metric \"unmapped_amount\" is not exposed by the named view",
  "code": "not_found",
  "reason": "not_in_view",
  "ref": "unmapped_amount"
}

The engine rejects unmapped names at compile time. No invalid SQL reaches the database.

What to notice

  • Single point of update. Renaming a database column requires editing exactly one line in model.yaml. Neither downstream dashboards, reporting tools, nor embedded widgets require code modifications.
  • Contract protection. The semantic model acts as an abstraction layer between physical warehouse storage and business reporting interfaces. Physical warehouse refactors remain isolated from operational business tools.
  • Pre-publish test verification. The model_tests feature validates numeric calculations in CI pipelines. Teams catch schema regressions and unexpected null values before promoting models to production.
  • Instant blast radius visibility. The POST /v1/lineage API answers what breaks before any edit is made. Engineers query downstream dependencies programmatically instead of grepping across disparate repositories.
  • Strict interface enforcement. Undeclared column names fail fast with structured errors. Reporting systems cannot generate silent inaccuracies by querying obsolete physical column names.

To learn how to structure your initial semantic model, read the guide on building your first model.

Limits

  • Manual mapping maintenance. The semantic layer does not guess when upstream warehouse columns change. Analytics engineers must update expr or column fields in model.yaml when database teams modify physical warehouse tables.
  • Pure SQL view parsing. The lineage engine traces dependencies declared inside the semantic model graph. Raw SQL expressions inside calculated dimensions or entity subqueries are not parsed into individual column-level lineage nodes.
  • Schema change execution. The semantic layer does not apply database DDL migrations. Executing ALTER TABLE, dropping deprecated columns, and running backfills remain the responsibility of warehouse migration tools like dbt or Flyway.

Product notes

An occasional email when we ship something or write something worth reading.