Data Platform and Warehouse Debt

What Data Platform Debt Actually Is

Model Sprawl and the Untraceable Reference Chain

A transformation framework such as dbt makes it trivially easy to add one more model. That is its great strength and the direct cause of its most common debt. A model is a file containing a SELECT statement; a reference to another model is a single function call. Nothing about adding the four hundredth model feels different from adding the fourth.

The shape the sprawl takes is predictable. Somebody needs a slightly different grain, so they copy the nearest existing model and change three lines. Somebody else needs the same thing filtered to one region, so they build on top of the copy. Within a year you have a chain like this, and no single person has ever read all of it:

-- models/marts/finance/fct_revenue_recognized_v3_final.sql
-- git blame: last meaningful change 19 months ago, author no longer at company

select
    r.order_id,
    r.recognized_amount,
    c.customer_segment,
    d.fiscal_period
from {{ ref('int_revenue_deferred_adjusted') }} r
left join {{ ref('dim_customer_enriched_v2') }} c on c.customer_id = r.customer_id
left join {{ ref('dim_date_fiscal_override') }} d on d.date_day = r.recognized_at::date
where r.recognized_amount is not null
  and r.order_id not in (select order_id from {{ ref('stg_orders_excluded_manual') }})

There are four references in that model. Each of them has references of its own. The interesting one is stg_orders_excluded_manual, which is almost certainly a hand-maintained exclusion list created to fix one reporting incident three years ago, and which is now silently removing rows from the revenue number. Nobody remembers it is there. It does not appear in any dashboard. It changes the answer.

The debt here is not the number of models. It is that the graph has no owners, no lifecycle, and no notion of a public interface. Every model can reference every other model, so every model is effectively public API. The fix begins with declaring layers and enforcing them, which most frameworks can do through project configuration rather than through hope:

# dbt_project.yml -- layers with enforced access and owners
models:
  analytics:
    staging:
      +materialized: view
      +access: private        # only this package may reference staging models
      +group: platform
    intermediate:
      +materialized: ephemeral
      +access: private
      +group: platform
    marts:
      +materialized: table
      +access: public         # the only layer other teams may build on
      +group: finance

groups:
  - name: finance
    owner:
      name: Finance Data Team
      email: [email protected]
  - name: platform
    owner:
      name: Data Platform Team
      email: [email protected]

Two things change once that is in place. Referencing a private staging model from another team's mart becomes a build failure rather than a private decision, so the graph stops growing sideways. And every model has a named owning group, which means the question "who do I ask before I change this" finally has an answer that is not "check git blame and hope".

Retrofitting this onto an existing project is not a weekend job, and it should not be attempted as one. Start by grouping and marking public only the models that appear in a dashboard or a downstream contract. Everything else defaults to private. The build failures that follow are not new breakage; they are the existing coupling finally becoming visible.

Semantic Drift: Three Dashboards, Three Definitions

Ask a company what an active customer is and you will usually get an immediate, confident answer. Ask the warehouse and you will get several. Here are three definitions that all shipped, all passed review, and all sit on executive dashboards under the same label:

-- Dashboard A (Growth): logged in at least once in the trailing 30 days
select count(distinct user_id)
from events
where event_name = 'session_start'
  and occurred_at >= current_date - interval '30 days';

-- Dashboard B (Finance): has a non-cancelled subscription as of today
select count(distinct customer_id)
from subscriptions
where status in ('active', 'past_due')
  and starts_at <= current_date
  and (ends_at is null or ends_at > current_date);

-- Dashboard C (Support): raised or received activity in the trailing 90 days
select count(distinct account_id)
from account_touchpoints
where touched_at >= current_date - interval '90 days';

None of these is wrong. Each is a reasonable answer to a question somebody actually had. The debt is that all three are called "active customers", none of them says which definition it uses, and the three numbers are compared to each other in meetings. The organisation now spends recurring analyst time reconciling numbers instead of acting on them, and every reconciliation ends the same way: the numbers were never measuring the same thing.

A semantic layer is the structural fix. Rather than each tool re-implementing the definition in its own dialect, metrics are defined once, in version control, and every consumer queries the definition rather than the tables:

metrics:
  - name: active_customers_billing
    label: Active Customers (Billing)
    description: >
      Distinct customers with a subscription in active or past_due status as of the
      grain date. This is the definition used in board reporting and revenue forecasts.
    type: simple
    type_params:
      measure: distinct_customers
    filter: "{{ Dimension('subscription__status') }} in ('active','past_due')"

  - name: active_customers_engagement
    label: Active Customers (Engagement)
    description: >
      Distinct customers with at least one session in the trailing 30 days. Product and
      growth reporting only. Never use this in a revenue context.
    type: cumulative
    type_params:
      measure: distinct_customers
      window: 30 days

Notice what the fix is not. It is not picking one definition and declaring the others wrong -- that attempt fails every time, because the other definitions exist for real reasons. The fix is to give each definition a distinct, unambiguous name, force every consumer to say which one it means, and write down in the description where each one may and may not be used.

A semantic layer only pays off if it is the cheapest path for an analyst under deadline. If the semantic layer is slow, poorly documented, or missing the dimension somebody needs, they will write raw SQL against the tables and the drift resumes immediately. Adoption is the whole game; the technology is the easy part.

Making a Full-Scan Model Incremental

The single highest-leverage cost fix is converting the largest full-rebuild models to incremental, and the second is making sure the incremental predicate can actually prune partitions. Both are small diffs with permanent effect:

{{ config(
    materialized = 'incremental',
    unique_key   = 'event_id',
    incremental_strategy = 'merge',
    partition_by = {'field': 'occurred_date', 'data_type': 'date'},
    cluster_by   = ['account_id']
) }}

select
    event_id,
    account_id,
    occurred_at,
    cast(occurred_at as date) as occurred_date,
    payload
from {{ source('app', 'events') }}

{% if is_incremental() %}
  -- Filter on the PARTITION column, not on a derived expression.
  -- A predicate the engine cannot map to partitions reads the whole table
  -- and the model still costs what it did before the change.
  where cast(occurred_at as date) >= (
      select coalesce(max(occurred_date), '1900-01-01') - interval '2 days'
      from {{ this }}
  )
{% endif %}

The lookback window in that predicate is the part people get wrong. Filtering strictly on the maximum date already loaded means any record arriving late is never picked up. A small overlap plus a merge strategy makes the model self-healing for late data, at the cost of reprocessing a couple of days each run. Choosing that window is a data-correctness decision disguised as a performance tuning knob.

Attribution is the other half. Tag every query with the model, the team, and the trigger, so the monthly bill can be sliced by owner. A cost line item with a team name attached gets fixed. A cost line item with no name attached gets escalated, discussed, and left alone.

Orphaned Tables and the Pipeline Nobody Dares Delete

Every mature warehouse contains a layer of tables that are refreshed on schedule and read by nobody. They persist because deleting one is an unbounded risk taken by an individual, while leaving it costs a small amount charged to a shared budget. That asymmetry is the entire explanation, and it is why exhortation never works. The fix has to change the risk, not the enthusiasm.

Most warehouses expose query history, which turns "is anything reading this" from an opinion into a query:

-- Tables that still cost money to refresh but have not been read in 90 days.
-- Adapt the history view name to your platform's information schema.
with reads as (
    select
        lower(referenced_object_name) as table_name,
        max(query_start_time)         as last_read_at,
        count(*)                      as reads_90d
    from account_usage.access_history,
         lateral flatten(input => base_objects_accessed)
    where query_start_time >= current_timestamp - interval '90 days'
    group by 1
),
storage as (
    select lower(table_name) as table_name, bytes, table_schema
    from information_schema.tables
    where table_type = 'BASE TABLE'
)
select
    s.table_schema,
    s.table_name,
    s.bytes,
    coalesce(r.reads_90d, 0) as reads_90d,
    r.last_read_at
from storage s
left join reads r on r.table_name = s.table_name
where coalesce(r.reads_90d, 0) = 0
order by s.bytes desc;

That query gives you candidates, not a delete list. Read the caveats before acting on it. A table read only at quarter end looks dead for most of the year. A table read exclusively by a service account through a driver that does not populate access history looks dead permanently. A table nobody queries but which a regulator expects to exist is not dead at all.

The safe procedure is a deprecation ladder, and it works because each rung is reversible:

-- Rung 1: announce. Rename with a deprecation marker and leave a view behind,
-- so anything still reading it keeps working AND shows up in query history.
alter table analytics.fct_legacy_orders rename to analytics.zz_deprecated_fct_legacy_orders;
create view analytics.fct_legacy_orders as
    select * from analytics.zz_deprecated_fct_legacy_orders;

-- Rung 2: stop paying. Drop the refresh job. The table goes stale but still resolves,
-- so a silent consumer surfaces as a freshness complaint rather than a hard failure.

-- Rung 3: break loudly, reversibly. Drop the view; the table remains.
drop view analytics.fct_legacy_orders;

-- Rung 4: after a full reporting cycle with no complaints, drop the table.
drop table analytics.zz_deprecated_fct_legacy_orders;

The ladder matters more than the query. It converts an irreversible individual decision into a sequence of reversible ones, and it gives silent consumers several increasingly loud opportunities to identify themselves. Teams that adopt a ladder delete things. Teams that rely on courage do not.

Schema Evolution Against Consumers You Cannot Enumerate

The classic paper on hidden technical debt in machine learning systems named undeclared consumers as one of its core risk factors: systems that read your output without telling you, creating a tight coupling you cannot see and did not agree to. A warehouse is where that pattern reaches its worst form, because reading a table requires no registration, no client library, and no handshake. Grant access once and you have created an unbounded, invisible set of dependants.

So the practical question is not "how do we version the schema" but "how do we make a change when we cannot list who will be affected". The changes that hurt are rarely the obvious ones. Dropping a column breaks loudly and gets fixed the same day. These are the ones that do damage:

None of these are caught by a pipeline-succeeded check, which is exactly why they belong in the same conversation as observability debt. A green orchestration run tells you the SQL executed. It tells you nothing about whether the output still means what it meant yesterday.

Reverse ETL and Accidental Circular Dependencies

Reverse ETL pushes warehouse-computed attributes back into operational systems: a churn score into the CRM, a lifetime value band into the billing tool, a segment into the marketing platform. It is genuinely useful, and it quietly turns the warehouse from a leaf node into a participant in the operational graph.

The failure mode is a loop that nobody drew on a diagram, because each individual hop was built by a different team in a different quarter:

CRM (source)                 --ingest-->  raw.crm_accounts
raw.crm_accounts             --transform->  marts.dim_account
marts.dim_account            --model----->  marts.fct_account_health_score
marts.fct_account_health_score --reverse ETL--> CRM custom field "health_tier"
CRM custom field             --ingest----->  raw.crm_accounts        <-- loop closed

Consequences once the loop exists:
  * The score becomes an input to the model that produces it.
  * A one-off scoring bug is written into the CRM and re-ingested as ground truth.
  * Backfilling the model rewrites operational records for every historical account.
  * Full-refreshing the warehouse now has customer-visible side effects.

Once that loop closes, a full refresh of the warehouse is no longer an internal operation. It can retrigger CRM automations, fire marketing emails, and change what sales representatives see on their screens. The blast radius of a routine data backfill now includes customers.

Three rules prevent almost all of it. Never ingest a field that reverse ETL writes -- exclude written-back fields from ingestion explicitly, by name, in the ingestion config, and let a test fail if one reappears. Write derived attributes to a namespace that is obviously derived, so that a human reading the CRM can tell a computed value from a human-entered one. And make reverse ETL syncs idempotent and rate limited, so that a backfill of five years of history does not translate into five years of operational events delivered in ten minutes.

Data Contracts and Why They Usually Fail

A data contract is a machine-readable declaration of what a dataset guarantees: its columns, its types, its grain, its freshness, and who owns it. The idea is sound and the tooling is now mature. Most implementations still fail, and they fail for organisational reasons rather than technical ones.

models:
  - name: fct_orders
    description: One row per order at the point of payment authorisation.
    config:
      contract:
        enforced: true      # a type mismatch fails the build, not a dashboard
    meta:
      owner: [email protected]
      grain: one row per order_id
      freshness_sla_minutes: 60
      consumers:            # declared consumers; undeclared ones are the real risk
        - finance-close-dashboard
        - billing-reconciliation-job
    columns:
      - name: order_id
        data_type: varchar
        constraints:
          - type: not_null
          - type: unique
        tests: [unique, not_null]
      - name: order_status
        data_type: varchar
        description: Enum. Adding a value is a BREAKING change for this dataset.
        tests:
          - accepted_values:
              values: ['pending','authorised','captured','refunded','cancelled']
      - name: gross_amount_cents
        data_type: integer
        description: Integer cents, excluding tax. Never a float, never a currency string.
        tests:
          - not_null
          - dbt_utils.accepted_range: {min_value: 0}

That is a good contract. Note the accepted_values test on the enum: it converts the silent enum-addition failure described above into a build failure at the boundary, which is the single highest-value test in the file.

Here is why it will probably still fail. The contract is written by the data team, but the schema is controlled by the application team, who did not sign it and are not measured on it. When their sprint requires a column change, the contract test breaks in a pipeline they do not watch, and the data team is told after the fact. A contract that only one party has agreed to is a monitoring rule wearing a contract's clothes.

The second failure is placement. Contracts are usually enforced at the warehouse, which is downstream of the change, so they detect breakage rather than prevent it. Enforcement has to sit in the producing team's own pipeline -- a schema check in the application repository's CI, run against the same declaration -- so that the build that breaks is the build of the team who made the change.

The third failure is scope. Teams try to contract everything, produce hundreds of contracts nobody maintains, and the whole effort collapses under its own weight within two quarters. Contract the handful of datasets that feed revenue reporting, regulatory filing, and customer-facing features. Leave the exploratory layer explicitly uncontracted and say so out loud, so that the absence of a contract is a documented decision rather than an oversight.

Freshness SLAs and Lineage

A freshness SLA answers a question every consumer has and almost no platform states: how old is this number allowed to be before it is wrong to act on it? Without a declared answer, every consumer invents their own, and the platform team is simultaneously over-delivering on datasets nobody urgently needs and under-delivering on the one the finance close depends on.

sources:
  - name: billing
    tables:
      - name: invoices
        loaded_at_field: _ingested_at
        freshness:
          warn_after:  {count: 30, period: minute}
          error_after: {count: 90, period: minute}
        meta:
          # State the CONSEQUENCE, not just the threshold. An on-call engineer
          # woken at 03:00 needs to know whether this can wait until morning.
          breach_impact: >
            Finance close and revenue dashboards go stale. Tolerable overnight.
            Not tolerable between 08:00 and 18:00 on the last two working days
            of the month. Escalate to [email protected].

Lineage is the other half. When freshness breaks, the useful question is not "which job failed" but "which dashboards, models, and reverse-ETL syncs are now serving stale data, and who should be told". Column-level lineage answers that. Table-level lineage mostly does not, because a table has dozens of downstream consumers and only two of them care about the column that actually broke.

Lineage is also the prerequisite for every other repair on this page. You cannot safely deprecate a table without knowing who reads it. You cannot assess a schema change without knowing what it feeds. Teams that try to pay down data platform debt without lineage end up doing archaeology on every single change, which is slow enough that the effort is usually abandoned.