ENEste artigo encontra-se publicado em inglês.
• 11 min read •By Tiago Marques

Spec-Driven BI: When It Works (And When It Doesn't)

Introduction

At its core, spec-driven development in Business Intelligence is a simple idea: instead of writing hundreds of SQL files and test scripts by hand, you write a configuration file (a specification) and let automation generate the code for you. Write the spec once, generate the pipelines everywhere.

When executed well, it makes data pipelines faster to build, more maintainable, and remarkably consistent.

But here is the reality: specs work brilliantly for some parts of your analytics stack and create massive friction for others.

The key insight—and the one that dictates whether automation helps or hurts—is understanding the difference between infrastructure and exploration.


The Essential Distinction: Infrastructure vs. Exploratory

Before anything else, you need to answer this question about any given piece of analytics work:

Is this infrastructure (stable, widely-reused, high-impact) or exploratory (fast iteration, low risk, transient)?

Your answer determines everything.

Infrastructure Characteristics

  • Changes quarterly or less
  • Used by many downstream dashboards or teams
  • High impact if broken (CFO’s scorecard goes dark, 50 people can’t do their jobs)
  • Small group maintains it (data engineers, not analysts)
  • Risk of failure is critical (breaking changes cascade across reporting)

Examples: conformed dimensions, core fact tables, shared KPI definitions.

Exploratory Characteristics

  • Changes weekly or daily
  • Used by one person or team
  • Lower blast radius if wrong (one dashboard needs a fix, easier to revert)
  • Many people touch it (analysts, business users)
  • Risk of failure is deceptive (wrong metric → wrong decision, often compounding silently for weeks before anyone notices)

Examples: one-off dashboards, quick analyses, metrics under active discussion.

The Honest Big-Tech Lesson

Large-scale data organizations do use spec-driven approaches. Airbnb’s Minerva centralizes metric definitions into a single semantic layer that all dashboards consume. Uber’s uMetric enforces metric governance so teams can’t redefine “trip” differently. LinkedIn’s Unified Metrics Platform standardizes how metrics are computed, stored, and served across products.

The common pattern across all of them is separating tiers while enforcing governed exploration:

  1. Infrastructure layer (specs, strict governance) – Data engineers maintain pipelines, schemas, and conformed dimensions using deterministic specs (e.g., schema.yml, data contracts).
  2. Semantic layer (governed metrics) – A unified, tool-agnostic layer (like Cube or dbt Semantic Layer) where metrics are defined explicitly.
  3. Exploratory layer (flexible, but within guardrails) – Analysts iterate and build one-off dashboards, but they do so by querying the semantic layer, not by writing rogue, ad-hoc SQL that re-invents metric definition logic.

The spec discipline lives in the infrastructure and semantic layers. The exploratory layer stays flexible, but never bypasses the governed metrics.

Promotion Pathways: When Exploration Becomes Infrastructure

The infrastructure/exploratory split isn’t static. Today’s one-off dashboard becomes tomorrow’s CFO scorecard. You need a deliberate promotion pathway—a process for hardening an exploratory artifact into governed infrastructure:

  1. Trigger: The dashboard is referenced by more than one team, is cited in a recurring meeting, or has survived two quarterly planning cycles without being retired.
  2. Promotion checklist: The analyst (or sponsoring team) opens a PR that moves the logic into the governed layer: defines the metrics in the semantic layer, adds data quality tests, writes a DDR documenting the grain and business rules, and links the dashboard to the conformed dimensions.
  3. Governance change: Once promoted, changes to the underlying model require code review and CI validation—the same bar as any other infrastructure artifact.

Without an explicit promotion process, you end up with shadow infrastructure: exploratory work that is infrastructure in practice but has none of the governance. This is how the “two dashboards, two answers” problem starts.


Where Specs Actually Shine

✅ Boilerplate Generation (The Staging Layer)

The staging layer (stg_*) is where you pull raw data from source databases and apply only mechanical transformations:

  • Rename columns: CUST_ID → customer_id
  • Cast types: VARCHAR → STRING
  • Add UTC timestamps
  • Apply basic null handling

A tool or LLM can generate 50 perfect staging models in seconds by reading the source schema. No complex business logic. No grain mismatches.

Why it works: This is syntax, not semantics.

✅ Metric Definition (The Semantic Layer)

You cannot write a universal spec that generates Snowflake SQL, Power BI DAX, and LookML simultaneously—those paradigms are incompatible. But you can create a single, version-controlled metric definition that multiple tools consume.

Here is what this looks like using dbt Semantic Layer / MetricFlow—not a homegrown schema, but the actual syntax a mature tool uses:

# dbt Semantic Layer — MetricFlow metric definition
semantic_models:
  - name: transactions
    defaults:
      agg_time_dimension: transaction_date
    entities:
      - name: transaction
        type: primary
        expr: transaction_id
      - name: customer
        type: foreign
        expr: customer_id
    dimensions:
      - name: transaction_date
        type: time
        type_params:
          time_granularity: day
    measures:
      - name: transaction_amount
        agg: sum
        expr: amount_usd

metrics:
  - name: daily_revenue
    description: Sum of settled transactions, per day, excluding internal accounts
    type: simple
    type_params:
      measure: transaction_amount
    filter: |
      {{ Dimension('transaction__account_type') }} != 'internal'

MetricFlow renders this into the correct SQL dialect for your warehouse. Different BI tools consume it. The key difference from a generic physical spec: MetricFlow’s schema enforces entities, grain, and aggregation type—it won’t let you build a metric that silently double-counts.

Why it works: You prevent silent metric drift. You don’t necessarily eliminate the “Finance says $10k, Sales says $12k” discrepancy—often, those are legitimately different metrics from different bounded contexts (e.g., booked vs. recognized revenue). But a semantic spec forces them to have distinct, governed definitions (sales_bookings vs finance_recognized_revenue) rather than colliding as ambiguous revenue columns.

✅ Test Automation (Data Contracts)

If a stakeholder says “Transaction ID must be unique,” a script can automatically generate the data quality test:

constraints:
  - column: transaction_id
    test: unique
    critical: true

You’re not writing complex logic—you’re translating explicit data-quality rules into deterministic test code.

Why it works: Automation for explicit contracts, not implicit semantics.

✅ Preventing Scope Creep: Infrastructure Specs

Apply specs ruthlessly to:

  • Conformed dimensions (dim_product, dim_date, dim_customer) – change quarterly
  • Core fact tables (fct_transactions, fct_events) – spine of the warehouse
  • Shared KPI definitions (revenue, churn, LTV) – 50+ downstream dependencies

Don’t use rigid infrastructure specs for:

  • One-off dashboards
  • Exploratory analysis (as long as it queries the semantic layer)
  • Quick iterations while stakeholders pivot
  • Local analyst work

The Golden Rule

Use automation for syntax, not for semantics.

Automate:

  • Boilerplate (stg_ models, naming conventions, casts)
  • Test generation (from explicit constraints)
  • Metric definitions (version-controlled, tool-agnostic)
  • Staging scaffolding (from source schemas)

Do NOT automate:

  • Grain decisions (what’s a “daily revenue” row?)
  • SCD logic (product category changes)
  • Dimensional modeling (conformed dimensions)
  • Complex business rules (how to calculate adjusted revenue)

Those require conversation. Code generation can draft them; human review is mandatory.


Where This Sits in the Ecosystem

If you’re reading this in 2026, you should know: the data contracts movement already solves many of these problems with deterministic tooling.

Tool / Pattern What It Does How It Compares
Native Data Contracts (e.g., schema.yml constraints) Enforce column types, not-null, uniqueness at build time Deterministic enforcement — no LLM needed. If you already use contracts natively, you may not need YAML specs for schema governance at all.
Great Expectations / Soda Data quality assertions on pipeline output Catches semantic issues (row counts, value ranges, distributions) that specs can’t express.
dbt Semantic Layer / MetricFlow Centralized metric definitions consumed by multiple BI tools The mature version of the “metric definition” pattern described above. If you’re on dbt Cloud, evaluate this before building your own.
Monte Carlo / data observability Anomaly detection, freshness SLAs, lineage tracking Solves drift detection without AI review agents.

Where spec-driven development fits

Spec-driven dev is a lightweight bridge for teams that aren’t ready for full data contract infrastructure. If your org:

  • Doesn’t use an orchestration and testing framework natively yet
  • Has no schema registry or contract framework
  • Needs a low-friction starting point

If you are just modernizing a legacy platform, then writing infrastructure-level specs and generating staging models from them is a pragmatic first step. But understand the ceiling: as your maturity grows, you’ll likely replace custom specs with native data contracts and a semantic layer tool.

Scope honesty

This approach is SQL-ecosystem specific. The physical specifications described here generate SQL. They don’t generate DAX, LookML, or anything outside the SQL paradigm. If your BI stack spans multiple paradigms, specs help within each one but don’t bridge them.


Orchestrating Spec-Driven Code Generation: Determinism is Mandatory

If you want to automate the workflow (spec → code → orchestration pipeline), you must rely on deterministic AST parsers and templating engines, not probabilistic LLMs.

Using an LLM to hallucinate SQL for core infrastructure based on a physical specification is an architectural anti-pattern. It injects non-determinism, fragility, and silent semantic failures into a tier that requires absolute predictability.

Instead, use tools designed for safe, programmatic scaffolding:

  1. Jinja Macros: Many tools allow you to write macros that loop through a graph object and dynamically generate SQL for staging layers.
  2. Code generators: Use established packages or scripts to generate baseline models deterministically from source schemas.
  3. Alternative dialects (e.g., PRQL): If SQL is too verbose for metric definitions, compile simpler declarative languages into SQL deterministically.

Here is a simple example of a Python script using Jinja2 to generate a staging model from a spec:

# Minimal example of deterministic code generation (No LLMs)
import yaml
from jinja2 import Template

def generate_staging_model(spec_file: str):
    with open(spec_file, 'r') as f:
        spec = yaml.safe_load(f)

    # A strict Jinja template guarantees output structure
    sql_template = Template("""
    WITH source AS (
        SELECT * FROM {{ "{{ source('" + source_name + "', '" + table_name + "') }}" }}
    ),
    renamed AS (
        SELECT
        {%- for col in columns %}
            cast({{ col.name }} as {{ col.cast_type }}) as {{ col.rename | default(col.name) }}{% if not loop.last %},{% endif %}
        {%- endfor %}
        FROM source
    )
    SELECT * FROM renamed
    """)

    return sql_template.render(
        source_name=spec['source']['database'],
        table_name=spec['source']['table'],
        columns=spec['columns']
    )

# The output is 100% predictable: same spec = same SQL, every time.

The workflow is straightforward and safe:

  1. Define your spec (YAML, JSON, or any structured format)
  2. Pass it through a deterministic templating engine
  3. Write the generated code to your models/ folder

This works well for mechanical scaffolding. It shouldn’t be used for complex logic involving grain, SCDs, or conformed dimensions—topics we’ll cover in Part 2.


Optional: Architectural Guidance Files

If you want to formalize architectural standards that your LLM or code generator should follow, you can create a lightweight guidance file (e.g., skills.md). This is not required, but it helps enforce consistency:

# Architectural Standards for Data Models

## Naming Conventions
- Staging models: `stg_` prefix
- Facts: `fct_` prefix 
- Dimensions: `dim_` prefix
- All files: snake_case

## Column Standards
- Primary keys: `{model_name}_id` (e.g., `customer_id`, `transaction_id`)
- Created/updated timestamps: `created_at`, `updated_at` (UTC)
- Boolean flags: `is_` prefix (e.g., `is_active`, `is_deleted`)
- Amounts: always `decimal(18,2)` or higher precision

## Testing Rules
- All primary keys: `not_null` + `unique` tests
- All foreign keys: `relationships` tests
- All fact amounts: `not_null` (no NULL amounts)
- Fact tables: at least one test on the grain (e.g., deduplication check)

## Documentation
- Every model must have a description block
- Every column must have a description block
- Fact tables must document their grain explicitly

Important Caveat

This guidance file helps when humans review generated code, but it doesn’t enforce anything automatically. An LLM will sometimes ignore it. Your real enforcement layer is:

  • CI linting (fail the build if fct_ tables don’t match the naming pattern)
  • Data quality tests (mandatory tests that fail if violated)
  • Code review (humans check that generated code follows the standards)

The guidance file is a reference, not a guarantee.


What Goes Wrong

The post assumes:

“Specs will make everything faster and more consistent.”

But when you apply specs universally, you get:

  • Year 1: Great! Specs for critical KPIs. Adoption and enthusiasm.
  • Year 2: Marketing wants a one-off Customer Lifetime Value dashboard. Do you mandate a spec PR?
  • Year 3: Half your dashboards have specs, half don’t. Specs feel like bureaucracy for the explorer-analysts because the platform’s Developer Experience (DX) is poor.
  • Year 4: Nobody remembers why specs exist. Adoption dies. You’re back to hand-written SQL with extra configuration files gathering dust.

The drift happens because you created a false dichotomy: rigorous infrastructure on one side, and unsupported ad-hoc SQL on the other. Good data engineering provides tools (like a robust semantic layer) that make doing the right thing easier than doing the wrong thing. Exploration shouldn’t mean abandoning guardrails; it should mean exploring through the semantic layer.


The Framework for Decision-Making

Before you commit a BI artifact to spec-driven development, ask:

  1. Is this infrastructure or exploration?

    • Infrastructure → specs are worth the overhead
    • Exploratory → let it stay flexible
  2. Will this change frequently?

    • Rarely → specs pay off (you reuse them)
    • Frequently → specs create friction
  3. How many teams/dashboards depend on this?

    • Many → specs reduce coordination overhead
    • One or two → not worth the complexity
  4. Is this a rule, or still being negotiated?

    • Settled rule → spec it
    • Still being figured out → explore via the semantic layer’s flexible interfaces, hardening into a spec only when the logic stabilizes

Next Steps

If you’re starting small, this is the roadmap:

  1. Identify your infrastructure layer – which tables/metrics are stable, widely-used, and rarely change?
  2. Apply specs to the staging layer – this is the easiest win and requires almost no semantic modeling.
  3. Define shared KPIs as specs – this prevents the “two dashboards, two answers” problem. This is the second-highest ROI.
  4. Empower analyst work with semantic layers – Don’t mandate rigid infrastructure specs for exploratory analysis, but give them a semantic layer that makes exploring governed metrics easier than writing ad-hoc SQL.
  5. Expose the semantic layer to AI agents – Once your semantic layer is governing your metrics, AI agents can query it directly through structured interfaces like MCP, REST APIs, or GraphQL endpoints (e.g., dbt-mcp, Cube’s REST API). The agents generate staging models, trace lineage, and run tests—all while respecting the grain and business rules you defined. The protocol is plumbing; the semantic contract is what matters.

Next: Part 2: The Semantic Gap: Specifying Business Reality


This post is part of the series Spec-Driven Development in BI: A Pragmatic Series.