Your CRM Changed One Field. Why Did the Revenue Dashboard Break?

A company connects its customer relationship management (CRM) system to a central analytics database.

Sales transactions flow into a data warehouse.

A business intelligence dashboard uses those records to report revenue, customer acquisition, and sales performance.

Everything works as expected.

Then the CRM provider updates its data structure.

A field previously called amount_usd is replaced with amount_cents.

The new field stores monetary values as integer cents rather than dollars.

The dashboard still loads.

The data pipeline may even report that its latest execution succeeded.

But the revenue total is suddenly wrong.

Some transactions are missing. Others may be interpreted using the wrong units.

Management sees an unexpected revenue change.

Did sales performance deteriorate?

Not necessarily.

The underlying business may be operating normally.

The measurement system may have changed.

This is the problem that data contracts and controlled schema evolution are designed to help organizations manage.

The Short Answer: Treat Data Interfaces as Business-Critical Contracts

A data contract is an explicit agreement about the data that one system provides and another system expects.

It can define:

• Field names and data types.
• Required and optional fields.
• Business meaning and measurement units.
• Valid values and identifiers.
• Data ownership and change procedures.
• Compatibility expectations.
• Quality and freshness requirements.

Not every contract implementation enforces all these properties automatically.

Some tools enforce structural rules.

Others require additional validation for business meaning and data quality.

The important principle is straightforward:

A source-system change should not silently alter the meaning of a downstream metric.

When a CRM changes a field, the analytics system should either continue interpreting the data correctly or detect the incompatibility before publishing misleading results.

What Is a Schema Change?

A schema describes the structure of data.

In a relational database, it commonly includes table names, column names, data types, and constraints.

In an API or event stream, it may describe the fields and types contained in a message.

Consider a simplified CRM record.

Original version:

{
  "order_id": "O101",
  "amount_usd": 1200.00
}

A later version:

{
  "order_id": "O103",
  "amount_cents": 150000
}

Both records describe monetary amounts.

But their interfaces differ.

The first represents $1,200.

The second represents $1,500.

A downstream process expecting amount_usd may fail to find the field.

A process that treats amount_cents as dollars may dramatically overstate revenue.

The problem is not simply that a column was renamed.

The meaning and unit of the value also changed.

That distinction is critical.

A Worked Example: How $4,000 Becomes $2,000 or $202,000

Consider four hypothetical orders.

The first two use the original CRM schema.

The next two use the updated schema.

The correct total is:

$1,200 + $800 + $1,500 + $500

= $4,000.

This is the business value the reporting system should represent.

But different pipeline implementations can produce very different results.

Scenario A: The pipeline ignores the new field

Suppose the pipeline extracts only amount_usd.

When it encounters a record without that field, it treats the missing value as zero.

The reported revenue becomes:

$1,200 + $800 + $0 + $0

= $2,000.

The dashboard understates revenue by:

Percentage understatement:

Half the actual transaction value has disappeared from the report.

The business did not lose that revenue.

The reporting pipeline failed to interpret the updated records.

Scenario B: The pipeline combines values without converting units

Now suppose the pipeline recognizes both field names.

But it combines their numerical values without converting cents to dollars.

The calculation becomes:

1,200 + 800 + 150,000 + 50,000

= 202,000.

The dashboard reports $202,000.

The correct total is $4,000.

The incorrect result is:

the actual value.

This is an extreme illustrative example.

It demonstrates why recognizing a new field name is not sufficient.

A data interface must preserve the meaning of the information, not merely its availability.

Scenario C: The pipeline normalizes both versions

A properly designed compatibility layer recognizes the two schema versions.

For V1:

amount_usd = amount_usd.

For V2:

amount_usd = amount_cents / 100.

The normalized values become:

$1,200
$800
$1,500
$500

Total:

$4,000.

The reporting system now produces the intended business result.

The example is hypothetical.

It is not evidence about the frequency of CRM schema failures or their typical financial impact.

Its purpose is to demonstrate how a structural change can produce a measurement error.

Why Can a Data Pipeline Succeed While the Dashboard Is Wrong?

A pipeline's execution status and the accuracy of its output are different things.

A job can complete without raising a technical exception while still producing incorrect business metrics.

For example, a transformation might use:

COALESCE(amount_usd, 0)

Here, assume ingestion still materializes a nullable amount_usd column but supplies SQL NULL when the source JSON key is absent. If the SQL column itself is removed, PostgreSQL normally raises an undefined-column error; COALESCE does not catch that error.

If the source field disappears, this expression may turn missing amounts into zeros rather than reporting an error.

The SQL can execute successfully.

The dashboard can load successfully.

The revenue total can still be wrong.

This is a silent failure.

It occurs when a system continues operating but violates the business expectations that make its output useful.

Not every use of COALESCE is incorrect.

Replacing missing values can be appropriate when the business meaning justifies it.

But replacing an unexpected missing monetary field with zero can conceal an upstream schema problem.

A successful execution should not be treated as proof of data correctness.

What Should a Data Contract Actually Specify?

A useful contract goes beyond listing column names.

Consider an analytical order dataset.

These requirements describe different kinds of guarantees.

Some are structural.

Others concern data values.

Others concern business semantics and operational governance.

A single automated tool may not enforce all of them.

For example, a schema validator may confirm that amount_usd is a numeric field.

That does not prove the value represents dollars rather than cents.

A structural contract can be satisfied while the business meaning is still incorrect.

This is why reliable data contracts combine technical validation with explicit business definitions.

Schema Compatibility Is Not the Same as Business Compatibility

Schema compatibility concerns whether different versions of a data structure can work together under defined rules.

Business compatibility concerns whether downstream users can continue interpreting the information correctly.

These concepts overlap, but they are not identical.

Suppose a source system changes a field from:

amount_usd

to:

amount_cents.

Both may contain numeric values.

A loosely typed processing system might accept either.

But a dashboard that assumes all values represent dollars will produce incorrect totals.

Even a compatible structural change can be incompatible with a business metric.

The distinction matters because organizations sometimes focus exclusively on whether a pipeline runs.

A stronger test asks:

Does the pipeline still produce the intended business meaning?

Backward, Forward, and Full Compatibility

Schema-managed systems often distinguish several compatibility policies.

Backward compatibility

In a backward-compatible schema-evolution policy, a consumer using the new schema can read data written with an earlier schema within the policy's defined scope.

Forward compatibility

Forward compatibility concerns whether a consumer using an older schema can read data written using a newer schema.

Full compatibility

Full compatibility combines backward and forward compatibility requirements.

Transitive compatibility

A transitive compatibility policy checks compatibility against all relevant previous schema versions, rather than only the immediately preceding version.

These concepts are implemented by systems such as Confluent Schema Registry.

The precise rules depend on the serialization format, schema type, and compatibility configuration.

For example, Avro, Protobuf, and JSON Schema do not have identical evolution rules.

A field change that is acceptable under one configuration may be rejected under another.

Confluent's documentation provides detailed compatibility rules and examples:

Schema Evolution and Compatibility for Schema Registry

Importantly, these compatibility policies do not automatically guarantee that a business metric has retained its meaning.

A data contract should therefore also describe relevant semantic rules.

How to Handle Two CRM Schema Versions in SQL

Consider a PostgreSQL table containing raw CRM order events.

Assume the table is called:

order_events

It contains:

order_id

and a JSONB column:

payload.

Some payloads use the original schema.

Others use the updated schema.

The following query illustrates how both versions can be normalized into a consistent monetary field.

WITH classified AS (

    SELECT
        order_id,
        payload,

        CASE

            WHEN
                jsonb_typeof(
                    payload -> 'amount_usd'
                ) = 'number'

                AND NOT (
                    payload ? 'amount_cents'
                )

            THEN 'v1'

            WHEN
                jsonb_typeof(
                    payload -> 'amount_cents'
                ) = 'number'

                AND NOT (
                    payload ? 'amount_usd'
                )

            THEN 'v2'

            ELSE 'invalid'

        END AS schema_version

    FROM order_events

),

normalized AS (

    SELECT
        order_id,
        schema_version,

        CASE schema_version

            WHEN 'v1'
            THEN
                (
                    payload ->> 'amount_usd'
                )::numeric(18,2)

            WHEN 'v2'
            THEN
                (
                    payload ->> 'amount_cents'
                )::numeric(18,2) / 100

            ELSE NULL

        END AS amount_usd

    FROM classified

)

SELECT
    schema_version,
    COUNT(*) AS order_count,
    SUM(amount_usd) AS total_amount_usd

FROM normalized

GROUP BY schema_version

ORDER BY schema_version;

For the four hypothetical records, the normalized results would be:

Combined total:

$4,000.

The query also classifies unexpected record structures as invalid.

That is deliberate.

A production pipeline should not silently assume that an unknown schema represents a valid zero-dollar order.

Instead, invalid records should be investigated or quarantined according to a documented policy.

Important limitations of this example

This query is an illustration, not a complete production pipeline.

It assumes:

• The source records are valid JSON.
• The monetary fields contain numeric JSON values.
• The monetary units follow the stated rules.
• The source uses only the two expected versions.
• Order identifiers are available.
• The database supports PostgreSQL JSONB operators.

Additional validation is necessary.

For example, the query does not independently establish that every amount_cents value is a whole number.

It also does not resolve duplicate order identifiers, currency conversion, refund treatment, or historical record revisions.

Those requirements depend on the business data model.

A robust implementation should test them explicitly.

How dbt Model Contracts Help Protect Downstream Analytics

A model contract can make the expected output structure of an analytical model explicit.

In dbt, enforced model contracts can validate column names and data types before a model is materialized.

Supported constraints depend on the database platform and model materialization.

Consider a hypothetical analytical model called:

fct_orders.

Its intended output contains:

order_id

and:

amount_usd.

An illustrative dbt YAML definition might look like this:

models:
  - name: fct_orders

    config:
      materialized: table

      contract:
        enforced: true

    columns:

      - name: order_id
        data_type: text

        data_tests:
          - not_null
          - unique

      - name: amount_usd
        data_type: numeric(18,2)

        data_tests:
          - not_null

The example assumes a compatible dbt version, database adapter, and model materialization.

The contract defines the expected model interface.

The data tests separately check selected properties of the resulting records.

These are not identical mechanisms.

The enforced contract helps prevent unexpected column names or data types from reaching downstream consumers.

The data tests help identify problems such as null identifiers, duplicate orders, and missing monetary values.

dbt's contract comparison does not independently verify granular precision or scale. When exact monetary precision matters, use adapter-aware validation and appropriate custom tests.

A successful contract check does not prove that revenue has been calculated correctly.

For example, a numeric column containing cents rather than dollars could satisfy a structural contract.

Additional business-rule tests and reconciliation are still required.

For implementation details, see dbt's official documentation:

Model Contracts

And:

Add Data Tests to Your DAG

How Should a Company Introduce a Breaking Schema Change?

Breaking changes should be managed as coordinated migrations, not unexpected substitutions.

A practical approach uses five stages.

Stage 1: Identify downstream dependencies

Before changing a field, determine which systems use it.

Possible consumers include:

Data pipelines.

Warehouse transformations.

BI dashboards.

Financial reports.

Machine learning features.

Scheduled exports.

Third-party integrations.

A field may appear unimportant within the CRM while supporting several business-critical reports.

Dependency mapping helps reveal the potential consequences of changing it.

Stage 2: Introduce the new interface

Instead of immediately removing the original field, introduce the new schema through a controlled release.

Where feasible, temporarily support both old and new representations.

For example:

V1:
amount_usd

V2:
amount_cents

A compatibility layer can normalize both into the established downstream interface.

This approach is sometimes called an expand-and-contract migration.

It allows producers and consumers to evolve without requiring every component to change at exactly the same moment.

Stage 3: Validate the new representation

Before publishing the migrated data, run checks covering:

Expected fields.

Data types.

Null values.

Duplicate identifiers.

Measurement units.

Valid schema versions.

Record counts.

Reconciliation totals.

Downstream model compatibility.

A migration should not pass only because its SQL executes.

It should pass because the resulting data satisfies the required business and technical expectations.

Stage 4: Migrate downstream consumers

Update the analytical models, dashboards, and other consumers that depend on the old interface.

Where appropriate, use versioned models or compatibility views.

dbt supports model versioning for managing changes to public analytical interfaces.

Its documentation explains how model versions can support consumers during breaking changes:

Model Versions

Stage 5: Retire the old interface

Once dependent systems have migrated successfully, the original field can be deprecated.

Before removal, confirm that no required consumer still depends on it.

The retirement process should be documented.

The objective is not to preserve every old field forever.

It is to make breaking changes intentional and observable.

Which Checks Should Block a Data Release?

Not every data-quality warning requires stopping publication.

But some failures can make a dashboard materially misleading.

A useful framework separates structural failures, business-rule failures, and operational warnings.

These are illustrative policies.

A real organization should determine severity based on business consequences, data criticality, and operational requirements.

For example, a five-minute delay in an exploratory dashboard may be acceptable.

An unexplained currency-unit change in a financial report may not be acceptable.

The appropriate control depends on what the data is used to decide.

How Can a Business Measure the Value of Data Contracts?

A data contract does not automatically create additional revenue.

Its potential value comes from reducing the frequency, duration, and consequences of preventable data failures.

Consider a hypothetical analytics operation.

Assume:

A reporting incident requires six analyst-hours to investigate and repair.

The fully loaded analytical labor cost is $75 per hour.

Direct investigation cost:

Suppose the organization experiences eight comparable incidents annually.

Annual direct investigation cost:

Now assume improved schema controls reduce those incidents from eight to three per year.

Avoided investigation cost:

(8 - 3) × $450

= $2,250 annually.

This is an illustrative scenario calculation.

It is not an empirical estimate of typical data-contract savings.

It also excludes:

Engineering implementation cost.

Ongoing maintenance.

Potential reporting delays.

Business decisions made using incorrect data.

Customer or operational consequences.

To establish actual financial value, the organization would need credible estimates of incident frequency, labor requirements, implementation costs, and business impact.

A reduction in incidents observed after introducing data contracts would not, by itself, prove that the contracts caused the improvement.

Other changes to staffing, infrastructure, monitoring, or reporting practices could contribute.

A stronger evaluation would use appropriate comparisons and documented incident definitions.

Data Contracts and Data Quality Tests Solve Different Problems

A data contract defines what a producer is expected to provide.

Data quality tests evaluate whether particular data properties are satisfied.

These mechanisms complement one another.

For example:

A structural contract may require an amount_usd column with a decimal data type.

A data quality test may require the column to contain no unexpected nulls.

A reconciliation test may compare the sum of normalized orders with an authoritative source-system total.

A business definition may specify that the field represents gross completed-order value, not recognized accounting revenue.

All four contribute different guarantees.

No single check establishes complete correctness.

The strongest design makes these responsibilities explicit.

What About Late-Arriving Records?

Schema changes and late-arriving data are different problems.

A schema change affects how information is structured or interpreted.

Late-arriving data affects when information becomes available.

Both can alter historical reporting.

But they require different controls.

A schema compatibility check cannot guarantee that every transaction has arrived.

A data freshness check cannot guarantee that a monetary field uses the correct units.

Organizations need controls appropriate to each failure mode.

For a detailed discussion of reporting cutoffs and historical revisions, see:

Why Did Yesterday's Revenue Change? Late-Arriving Data, Backfills, and Reporting Cutoffs

The important lesson is that pipeline reliability has multiple dimensions.

Structural validity, semantic correctness, completeness, freshness, and reproducibility should not be treated as interchangeable.

When Does a Business Actually Need Data Contracts?

Not every business needs a sophisticated schema registry or enterprise data platform.

A small organization using one stable database may be able to manage changes through documented schemas, SQL tests, and a simple review process.

Formal contracts become more valuable when:

Multiple systems exchange data.

Several teams own different parts of the pipeline.

External APIs change frequently.

Business-critical dashboards depend on automated ingestion.

The same analytical models serve many downstream consumers.

Schema changes require coordinated deployments.

Historical consistency and auditability matter.

The question is not whether the organization uses a particular technology.

It is whether unexpected changes to data interfaces create meaningful business risk.

For a broader architectural discussion, see:

When Does a Business Actually Need a Data Warehouse?

A warehouse provides a place to integrate and analyze information.

A data contract helps make the interfaces between systems more predictable.

These are complementary architectural decisions.

A Practical Framework for Preventing Broken Dashboards

Organizations can organize their controls around six questions.

1. What is the critical data interface?

Identify the datasets that support important business decisions.

Document the required fields and their meaning.

2. Who owns the interface?

Identify the producer responsible for the data and the consumers that depend on it.

Establish a clear change-notification process.

3. Which changes are breaking?

Define whether renaming a field, changing its type, altering its unit, or changing its meaning requires a new version.

Do not assume all additive changes are harmless.

4. What must be validated?

Test structural compatibility, business rules, record integrity, and reconciliation.

Use checks appropriate to the data's intended purpose.

5. What happens when validation fails?

Determine whether the pipeline should stop, quarantine records, retain the last validated output, or publish a clearly labeled preliminary report.

Do not silently substitute missing financial values with zeros unless that behavior is explicitly justified.

6. How is the migration completed?

Introduce the new interface.

Validate compatibility.

Migrate consumers.

Monitor the transition.

Retire the old interface only after dependencies have been addressed.

This process turns unexpected schema failures into managed changes.

A Stable Dashboard Is Not Proof of a Stable Data System

A dashboard can look normal while its underlying data has become unreliable.

The visual interface may continue displaying charts and totals.

But those values depend on assumptions about field names, data types, identifiers, measurement units, and business definitions.

When those assumptions change, the meaning of the dashboard may change with them.

Data contracts help make those assumptions explicit.

Schema versioning helps manage change.

Automated tests help detect violations.

Reconciliation helps connect technical outputs to business expectations.

Together, these practices can reduce the risk that a source-system update silently changes a business-critical metric.

The objective is not to prevent systems from evolving.

It is to make evolution controlled, observable, and compatible with the decisions that depend on the data.

References and Further Reading

1. dbt Developer Hub. Model Contracts.
2. dbt Developer Hub. Add Data Tests to Your DAG.
3. dbt Developer Hub. Model Versions.
4. Confluent Documentation. Schema Evolution and Compatibility for Schema Registry.

Need More Reliable Data Pipelines and Business Reporting?

UAD Consulting helps organizations structure analytical data, improve SQL workflows, integrate multiple sources, and develop validation processes for more dependable reporting.

Projects can involve database modeling, data cleaning, pipeline validation, data warehouse analysis, and business intelligence.

The goal is to connect technical data foundations with the business questions they are intended to support.

Explore our Data Engineering & Database Analytics services to learn more about analytical data architecture and reporting support.

Contact UAD Consulting to discuss your data engineering and analytics requirements.

Explore ServicesGet in Touch