The Problem of Documentation in Data Warehouses

Every data team knows the problem: the warehouse grows, new analysts arrive, and no one knows more about what the table contains fct_orders_v2_final or where it comes from the column adjusted_revenue. Documentation exists in Confluence, but it is misaligned from the real code for months.

dbt solves this problem at the root: the documentation is code, lives in the same template repository, it updates with each dbt run and generates automatically an interactive catalog with lineage graph. It's not a best practice additional - it is an integral part of the workflow.

YAML Descriptions: The Declarative Data Contract

The starting point is the file schema.yml in the model directory. Each template and each column can have a free text (or Markdown) description:

# models/marts/finance/schema.yml
version: 2

models:
  - name: fct_orders
    description: >
      Fatto degli ordini completati, granularità ordine. Ogni riga rappresenta
      un ordine con stato 'completed', arricchito con informazioni cliente, prodotto
      e canale di acquisizione. Aggiornato quotidianamente alle 06:00 UTC.

      **Fonte dati:** `stg_orders` (sorgente transazionale) + `dim_customers` + `dim_products`
      **Owner:** team Finance Analytics
      **SLA:** dati aggiornati entro le 07:00 UTC

    meta:
      owner: "finance-analytics@company.com"
      freshness_expected_hours: 25
      pii_columns: ["customer_email", "billing_address"]

    columns:
      - name: order_id
        description: >
          Identificatore univoco dell'ordine. Corrisponde all'`order_id` nel
          sistema transazionale. Formato: stringa UUID v4.

      - name: customer_id
        description: >
          Chiave esterna verso `dim_customers`. Ogni ordine appartiene
          a esattamente un cliente.

      - name: order_date
        description: >
          Data (senza ora) in cui l'ordine è stato completato.
          Usare questa colonna per aggregazioni temporali, non `created_at`.

      - name: gross_revenue
        description: >
          Ricavo lordo dell'ordine in EUR, prima di sconti e rimborsi.
          Formula: `SUM(line_quantity * unit_price)`.

      - name: net_revenue
        description: >
          Ricavo netto in EUR, dopo sconti applicati.
          Formula: `gross_revenue * (1 - discount_pct)`.

      - name: acquisition_channel
        description: >
          Canale tramite cui il cliente ha effettuato l'acquisto.
          Valori possibili: `organic`, `paid_search`, `email`, `referral`, `social`, `direct`.
          Derivato dalla sessione di acquisto tramite `dim_sessions`.

Descriptions with doc() for Reusable Content

For long or shared descriptions between models, dbt supports i doc block: Markdown files in the directory models/ which can be referenced via the function doc():

-- models/docs/revenue_metrics.md
{% docs gross_revenue %}
Ricavo lordo dell'ordine in EUR, prima di sconti e rimborsi.

**Formula:** `SUM(line_quantity * unit_price)`

**Note contabili:** questo valore include l'IVA per i clienti IT/EU.
Per il ricavo netto IVA, usare `net_revenue_ex_vat`.

**Frequenza di aggiornamento:** giornaliera (06:00 UTC).
{% enddocs %}

{% docs customer_tier %}
Segmento di appartenenza del cliente al momento dell'ordine (SCD-2 snapshot).

| Valore   | Soglia annuale | Benefici |
|----------|---------------|----------|
| standard | < 1.000 EUR  | nessuno  |
| silver   | 1.000-5.000 EUR | spedizione gratuita |
| gold     | 5.000-20.000 EUR | 5% sconto + spedizione |
| platinum | > 20.000 EUR  | 10% sconto + account manager |
{% enddocs %}

# Uso nel schema.yml
columns:
  - name: gross_revenue
    description: "{{ doc('gross_revenue') }}"  # referenza al doc block
  - name: customer_tier
    description: "{{ doc('customer_tier') }}"

Generate the Catalog: dbt docs generate and serve

The dbt catalog is a static web application generated from project metadata and from the warehouse. Two commands to use it:

-- 1. Genera il catalogo (interroga il warehouse per schema e statistiche)
dbt docs generate

-- 2. Avvia un server locale per navigare il catalogo
dbt docs serve
-- Apre automaticamente http://localhost:8080

-- In CI/CD: genera il catalogo e pubblica gli artifact
dbt docs generate --no-compile  -- più veloce se già compilato

-- Output dei file generati in target/:
-- target/catalog.json    -- metadati di tabelle e colonne
-- target/manifest.json   -- DAG del progetto, modelli, test
-- target/sources.json    -- metadati sorgenti
-- target/index.html      -- l'applicazione web statica

The generated catalog contains:

• All model and column descriptions written in YAML
• Actual table schema (column types, sizes)
• Tests configured for each model
• The interactive lineage graph (DAG)
• Row count and table size statistics (where supported)

The Interactive DAG: Understanding Dependencies

The most powerful feature of the dbt catalog is the lineage graph: an interactive view of the project's complete DAG. Each node is a model, each arc is a dependency created by ref() o source().

From the UI you can:

• Filter by layer (staging, intermediate, marts) using standard dbt selectors
• Expand/collapse sub-graphs
• Click on a node to see description, columns, tests and compiled SQL
• See the lineage upstream (where this model comes from) and downstream (who depends on it)

Selectors to Navigate the Lineage

-- Nella barra di ricerca del catalogo, puoi usare i selettori dbt:

-- Tutti i modelli che dipendono da stg_orders (downstream)
+stg_orders

-- stg_orders e tutti i suoi upstream (sorgenti incluse)
stg_orders+

-- Tutti i modelli in marts/ e il loro upstream immediato
+tag:marts

-- Solo i modelli con un certo owner nei meta
exposure:finance_dashboard

-- CLI equivalente (per capire il subset visivamente):
dbt ls --select +fct_orders+  -- mostra upstream E downstream di fct_orders

Column-Level Lineage: Tracing a Field from Source to Report

Il column-level lineage (available in dbt Cloud and with some metadata additional in dbt Core) trace each column through the templates: from which source column drifts net_revenue in the final report? dbt Cloud Explorer shows this automatically analyzing the SELECTs.

# Per abilitare il column-level lineage in dbt Core,
# aggiungi le dipendenze colonne nei meta (opzionale ma utile):

models:
  - name: fct_orders
    columns:
      - name: net_revenue
        description: "Ricavo netto dopo sconti"
        meta:
          derived_from:
            - model: stg_orders
              column: total_amount
            - model: stg_orders
              column: discount_pct
          formula: "total_amount * (1 - discount_pct)"

In dbt Cloud Enterprise, column-level lineage is automatic: the system analyzes the compiled SQL of each model and builds the column-level dependency graph. Useful for impact analysis: if the definition of discount_pct in the source, which final reports are impacted?

Exposures: Documenting Warehouse Consumers

Le exposure document who uses dbt models - Tableau dashboards, reports Power BI, Python notebooks, API. They appear in the lineage graph as final nodes and enable to understand the impact of a change:

# models/exposures.yml
version: 2

exposures:
  - name: finance_weekly_report
    label: "Report Finanziario Settimanale"
    type: dashboard              # dashboard | ml | notebook | analysis | application
    maturity: high               # low | medium | high
    url: https://tableau.company.com/views/FinanceWeekly
    description: >
      Dashboard Tableau aggiornata ogni lunedì mattina con KPI finanziari
      della settimana precedente. Usata dal CFO e dal team Finance per il report
      settimanale al board.

    owner:
      name: Finance Analytics Team
      email: finance-analytics@company.com

    depends_on:
      - ref('fct_orders')
      - ref('fct_order_line_items')
      - ref('dim_customers')
      - ref('dim_products')

  - name: ml_churn_model
    label: "Modello ML Churn Prediction"
    type: ml
    maturity: medium
    description: >
      Feature store per il modello di churn prediction. Addestrato settimanalmente,
      serve dati degli ultimi 90 giorni di comportamento cliente.

    owner:
      name: Data Science Team
      email: datascience@company.com

    depends_on:
      - ref('fct_customer_events_90d')
      - ref('dim_customers')

Sources: Document External Sources

Raw warehouse sources deserve the same documentation care as dbt models. The file sources.yml allows you to document them and activate freshness checks:

# models/staging/sources.yml
version: 2

sources:
  - name: raw
    description: >
      Schema raw: dati grezzi caricati dai sistemi transazionali tramite Fivetran.
      Nessuna trasformazione applicata. Schema di appoggio - non usare direttamente
      nei report, usare i modelli staging/ come interfaccia stabile.
    database: analytics_db
    schema: raw

    loaded_at_field: _fivetran_synced   # colonna di timestamp per il freshness check

    freshness:
      warn_after:  {count: 12, period: hour}
      error_after: {count: 24, period: hour}

    tables:
      - name: orders
        description: "Ordini dal CRM, sincronizzati ogni 15 minuti."
        columns:
          - name: id
            description: "Primary key del sistema sorgente."
          - name: customer_id
            description: "FK verso la tabella customers."
          - name: status
            description: "Status dal CRM: created|paid|shipped|delivered|cancelled."
          - name: _fivetran_synced
            description: "Timestamp dell'ultima sincronizzazione Fivetran."

      - name: customers
        description: "Anagrafica clienti dal CRM."
        freshness:
          warn_after: {count: 24, period: hour}   # override per questa tabella

-- Verificare la freshness delle sorgenti:
dbt source freshness

-- Output esempio:
-- Found 3 sources, 7 tables
-- Checking freshness of 7 sources (7 checks)
-- OK freshness of raw.orders ..................... [ok in 0.34s]
-- WARN freshness of raw.customers ............... [warn in 0.41s]
-- ERROR freshness of raw.events ................. [error in 0.29s]

Meta: Custom Metadata for Governance

The field meta in YAML allows you to add custom structured metadata that appear in the catalog and can be used by external governance tools:

models:
  - name: fct_orders
    meta:
      owner: "finance-analytics@company.com"
      team: "Finance Analytics"
      tier: "gold"                    # bronze/silver/gold (data quality tier)
      sla_hours: 25                   # ore massime dall'ultimo aggiornamento
      pii: false                      # contiene PII?
      gdpr_relevant: true             # rilevante per GDPR?
      retention_days: 365             # giorni di retention
      business_glossary_link: "https://wiki.company.com/glossary/orders"
      downstream_systems:
        - "Tableau Finance Dashboard"
        - "Looker Revenue Report"
        - "ML Churn Feature Store"

Publish the Catalog in Production

In dbt Core, the generated catalog is a folder of static files that you can host on any static file server. Here's a common pattern with GitHub Actions + GitHub Pages:

# .github/workflows/dbt-docs.yml
name: Publish dbt Docs

on:
  push:
    branches: [main]

jobs:
  docs:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Setup Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'

      - name: Install dbt
        run: pip install dbt-snowflake==1.8.0

      - name: dbt deps
        run: dbt deps
        env:
          DBT_PROFILES_DIR: .

      - name: dbt docs generate
        run: dbt docs generate --target prod
        env:
          SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
          SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}

      - name: Deploy to GitHub Pages
        uses: peaceiris/actions-gh-pages@v4
        with:
          github_token: ${{ secrets.GITHUB_TOKEN }}
          publish_dir: ./target
          destination_dir: docs

Best Practices for dbt Documentation

Having the ability to document does not mean that documentation is automatically good. Here are the practices that distinguish a useful catalog from a catalog abandoned:

Write Operational, Not Obvious Descriptions

# EVITA: descrizioni ridondanti che ripetono il nome della colonna
columns:
  - name: order_id
    description: "L'ID dell'ordine."  # inutile

# PREFERISCI: descrizioni operative con contesto, formato, vincoli
columns:
  - name: order_id
    description: >
      Identificatore univoco dell'ordine. UUID v4 generato dal sistema CRM.
      Usare questo campo come chiave di join con i sistemi transazionali.
      Non corrisponde all'order_number visibile all'utente (quello è in `order_number`).

  - name: gross_revenue
    description: >
      Ricavo lordo dell'ordine in EUR, centesimi di precisione.
      Include IVA per ordini EU, esclusa per ordini US.
      Per ricavo netto usare `net_revenue`. Per ricavo ex-IVA usare `net_revenue_ex_vat`.
      Non include spese di spedizione (vedi `shipping_amount`).

Document Known Anomalies and Limitations

models:
  - name: fct_orders
    description: >
      **NOTA (2024-03-15):** Gli ordini precedenti al 2022-01-01 hanno `acquisition_channel`
      sempre NULL: il tracking non era presente prima di quella data.
      Non filtrare su NULL in analisi pre-2022 o i risultati saranno distort.

      **LIMITAZIONE NOTA:** I rimborsi parziali non riducono `gross_revenue` in questo modello
      (feature pianificata per Q3 2024). Per analisi rimborsi usare `fct_refunds`.

      **DIPENDENZA CRITICA:** Questo modello dipende da `snap_customers` (snapshot SCD-2).
      Se lo snapshot non è aggiornato, la segmentazione cliente potrebbe essere
      basata su dati del giorno precedente.

Automation: Generate Draft Documentation

-- dbt Cloud e alcuni tool (dbt-osmosis, dbt-coverage) permettono di
-- generare automaticamente bozze di schema.yml per modelli non documentati

-- Installa dbt-osmosis per propagare automaticamente le descrizioni
-- dalle sorgenti fino ai modelli downstream:
pip install dbt-osmosis

-- Genera schema.yml per tutti i modelli privi di documentazione:
dbt-osmosis yaml generate --select path:models/staging/

-- Propaga le descrizioni dai modelli upstream ai downstream:
dbt-osmosis yaml refactor --select path:models/marts/

-- dbt-coverage: verifica la percentuale di documentazione nel progetto
pip install dbt-coverage
dbt-coverage compute doc --cov-report coverage.json
dbt-coverage check doc --cov-report coverage.json --min-coverage 80

Conclusions and Next Steps

Dbt documentation is not documentation in the traditional sense: it is a living contract that evolves with the code, automatically generates a navigable catalog, and allows each team member to understand where the data came from and what it was like transformed.

Invest in the quality of YAML descriptions, exposures and documentation of sources pays off in proportion to team size and complexity of the warehouse.

The next module addresses a much requested practical comparison: dbt Core vs dbt Cloud. When self-hosting is sufficient and when managed service worth the cost.