When the dbt build passes but the dashboards still lie

A passing data pipeline is not the same thing as correct data. A successful dbt build guarantees that your SQL compiled, your tables materialized, and your primary keys stayed unique and non-null. It does not verify that the raw data matches physical business reality, and it does nothing to stop analysts from writing divergent, ad-hoc metric definitions downstream in the BI tool.

The short answer: standard pipeline validation is syntactic, not semantic. It checks shapes and types, not meaning. The discrepancies that reach an executive dashboard — revenue that’s off by a region, an order total that doesn’t match its line items, a number that disagrees with the CRM — slip straight through structural tests. To catch them you need data quality gates that read the numbers, plus a single semantic layer so the metric is defined once and consumed everywhere.

Why does a dbt build pass when business metrics are wrong?

We see the same silent failure mode across the DACH mid-market. You open the monitoring dashboard: the orchestration DAG is green, the Snowflake warehouse reports zero query errors, dbt ran without a single warning. Yet sales ops flags that the weekly revenue figure in Power BI is lower than what the CRM records.

Nothing is broken in the engineering sense. That’s exactly the problem. If your pipeline only runs structural assertions like unique or not_null, there’s a blind spot between database success and analytical accuracy — and it’s where trust goes to die.

Every row above has valid types and no nulls, so a schema-only test reports a clean run. But ORD-9083 carries an order header of €15.00 against €310.00 of line items. The pipeline is green; the dashboard is wrong.

Architecture of a data quality gate in dbt and Snowflake

To stop logical discrepancies from corrupting dashboards, put automated guardrails at the boundaries of the warehouse instead of letting raw data flow unimpeded from source to semantic layer. We use three kinds of gate.

Building an advanced semantic test in dbt

Here’s a concrete implementation: a custom generic test that checks relational integrity across two models — validating that the sum of line-item amounts matches the transaction header, within a tolerance.

Create the test at tests/generic/assert_sums_match.sql. A generic test returns the failing rows, so the test fails whenever the query yields anything:

{% test assert_sums_match(model, column_name, compare_model, compare_column, group_by_column, tolerance=0.01) %}

with source_totals as (
    select
        {{ group_by_column }} as join_key,
        sum({{ column_name }}) as primary_sum
    from {{ model }}
    group by 1
),

compare_totals as (
    select
        {{ group_by_column }} as join_key,
        sum({{ compare_column }}) as reference_sum
    from {{ compare_model }}
    group by 1
),

discrepancies as (
    select
        s.join_key,
        s.primary_sum,
        c.reference_sum,
        abs(s.primary_sum - c.reference_sum) as absolute_difference
    from source_totals s
    join compare_totals c on s.join_key = c.join_key
    where abs(s.primary_sum - c.reference_sum) > {{ tolerance }}
)

select * from discrepancies

{% endtest %}

Then reference it from models/schema.yml so it runs on every deployment. Tag it so you can select just the gates in CI:

version: 2

models:
  - name: fct_order_items
    description: "Granular line items for customer orders."
    columns:
      - name: line_item_amount_eur
        description: "Net amount of the individual line."
        tests:
          - assert_sums_match:
              compare_model: ref('fct_orders')
              compare_column: total_order_amount_eur
              group_by_column: order_id
              tolerance: 0.05
              tags: ['dq_gate']

Quarantine at the ingestion layer with Snowflake

Running every semantic check inside dbt means anomalous rows have already landed in your modelled tables. For high-volume sources, push the cheapest gates upstream using Snowflake Streams and Tasks: a stream tracks new rows on the raw landing table, a scheduled task evaluates the integrity rule, and failing batches get routed to a quarantine table instead of flowing on. You spend less warehouse compute reprocessing bad loads, and dbt only ever sees data that already cleared the first gate.

Closing the loop: one semantic layer for Power BI

Even with clean data in Snowflake, metrics still drift the moment analysts compute their own KPIs locally in Power BI with custom DAX. One “revenue” measure quietly becomes three, each subtly different, and nobody can say which is right. Clean warehouse data is necessary but it is not enough — the definition has to be centralized too.

Define the metric once with the dbt Semantic Layer

With the dbt Semantic Layer you declare each measure once, in the code repository, and every consumer reads the same definition:

semantic_models:
  - name: revenue_performance
    model: ref('fct_orders')
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
    measures:
      - name: total_revenue
        agg: sum
        expr: total_order_amount_eur

metrics:
  - name: sales_revenue
    label: "Sales Revenue (EUR)"
    type: simple
    type_params:
      measure: total_revenue

Serve Power BI from a certified model, not raw tables

To connect that centralized logic to Power BI without re-implementing it:

1. 01

   Enable the XMLA endpoint

   On a Power BI Premium or Fabric workspace, turn on XMLA read/write so external tools can deploy and manage semantic models programmatically rather than through point-and-click.

2. 02

   Report from one certified semantic model

   Build reports against a single, certified Power BI semantic model instead of importing raw database tables into each report. One definition, many reports — no per-report DAX forks.

3. 03

   Version definitions in Git with TMDL

   Deploy and track the semantic schema as code using Tabular Model Definition Language (TMDL), edited with Tabular Editor. Metric changes then go through peer review and automated testing before they hit production — the same workflow as the rest of your engineering.

Once metric logic lives in Git-managed models, a change to “revenue” is a pull request, not a silent edit in someone’s local report.

Alerting: catch bad data before a stakeholder does

The last piece is proactive alerting. When a semantic gate fails, the notification should reach the on-call data engineer — not wait for an executive to notice a broken chart. Wire the gate selection into CI so a failure triggers the alert path:

# In the CI pipeline
dbt build --select tag:dq_gate || python scripts/alert_data_team.py --status failure

The alert script reports the exact failure so whoever picks it up already knows what broke:

import os
import requests

def send_teams_alert(failed_test_name, affected_rows):
    webhook_url = os.environ["TEAMS_WEBHOOK_URL"]
    payload = {
        "title": "🚨 Data quality gate failure",
        "text": (
            f"Semantic validation **{failed_test_name}** failed on Snowflake. "
            f"Found **{affected_rows}** mismatched records. Downstream refreshes halted."
        ),
    }
    requests.post(webhook_url, json=payload, timeout=10)

Restoring trust in the numbers

When a business loses faith in its analytics, the failure mode is predictable: teams revert to manual spreadsheets, data silos reform, and strategic alignment quietly erodes. Closing that gap means moving past syntax validation to automated semantic gates and a single, governed metric layer. It’s the same operational discipline that decides whether an AI pilot ever reaches production — the model, like the green build, is rarely where the project actually fails.

We design and implement cloud data warehouses, pipeline automation, and BI architectures for DACH mid-market companies — with data quality gates and semantic layers built in from the start, not bolted on after the first wrong board deck.