Your Sales Territory Changed. Why Did Last Quarter's Revenue Move?

A sales manager opens the quarterly revenue dashboard and notices something unusual.

Last quarter's East territory revenue is lower than it was in the previous report.

West territory revenue has increased by exactly the same amount.

No additional orders were placed during the historical period. No invoices were canceled. The company's total revenue is unchanged.

What happened?

The CRM recently reassigned a major customer from East to West.

The dashboard uses the customer's current territory to categorize every historical transaction, including orders placed before the reassignment.

The SQL query runs successfully. The dashboard refreshes without errors. The total revenue reconciles.

But the historical territory comparison has changed.

This is a common type of analytical data-modeling problem: mutable customer attributes are being applied to transactions from earlier periods.

The solution may involve a technique called a Slowly Changing Dimension, particularly Type 2, combined with an appropriate point-in-time join.

Before implementing it, however, the business must answer a more fundamental question:

Should historical sales be reported according to the territory that owned the customer when the sale occurred, or according to the territory that owns the customer today?

Both can be legitimate reporting views.

They answer different questions.

The Short Answer: Current CRM Attributes Are Not Always Historical Facts

Operational systems often store the latest known value of a customer attribute.

A customer record might contain:

customer_id: C101

territory: West

account_manager: Manager B

This describes the customer's current assignment.

It does not necessarily describe the assignment that existed when an order was placed months earlier.

If a historical sales fact is joined directly to this current CRM record, older transactions can inherit the latest territory.

This may be appropriate for a current-portfolio report.

It is usually inappropriate when the intended metric is historical sales by territory at the time of the transaction.

The distinction is between two reporting questions.

Historical attribution:

Which territory was responsible for the customer when the sale occurred?

Current-portfolio reporting:

How much historical revenue was generated by customers currently assigned to each territory?

Neither question is inherently wrong.

The reporting system becomes misleading when one answer is labeled as the other.

A Worked Example: $1,200 Moves Without Any New Sales

Consider a hypothetical company with three customers.

Customer C101 originally belongs to East.

On April 1, 2026, C101 is reassigned to West.

Customer C202 remains in West.

Customer C303 remains in East.

The company has four sales transactions.

All dates are in 2026.

All figures are hypothetical.

For this example, assume each row represents one completed order, and the reporting metric is the sum of the listed order amounts.

The total is:

$1,200 + $800 + $500 + $700 = $3,200.

No transaction is missing.

No transaction has been duplicated.

Now compare two ways of assigning territory.

Step 1: Join Sales to the Current CRM Territory

Suppose the current CRM table contains:

A conventional join on customer_id assigns the current territory to every order.

The results are:

O1: West, $1,200

O2: West, $800

O3: West, $500

O4: East, $700

The dashboard reports:

This report is mathematically correct for a current-portfolio view.

It answers:

How much revenue was generated by customers who belong to each territory today?

But it does not preserve the territory assignment that existed when every transaction occurred.

Order O1 happened in February, when C101 belonged to East.

Applying today's West assignment to that February order changes the historical classification.

Step 2: Use the Territory Effective on the Order Date

Now assign every transaction according to the territory valid when the order occurred.

Customer C101 belonged to East before April 1.

C101 belonged to West beginning April 1.

Therefore:

O1: East, $1,200

O2: West, $800

O3: West, $500

O4: East, $700

The historical results become:

The East calculation is:

$1,200 + $700 = $1,900.

The West calculation is:

$800 + $500 = $1,300.

Compare both reporting views.

The $1,200 February order moves from East to West when current customer attributes are used.

Nothing about the underlying sale changed.

Only the rule used to classify it changed.

This is why a total-revenue reconciliation alone cannot detect every dimensional reporting problem.

Both views reconcile to $3,200.

They still communicate different information about territory performance.

What Is a Slowly Changing Dimension?

In dimensional modeling, a dimension describes an analytical entity such as a customer, product, employee, supplier, location, or organization.

A customer dimension might contain:

Customer identifier.

Customer name.

Sales territory.

Account manager.

Customer segment.

Industry.

Account status.

These attributes can change.

The term Slowly Changing Dimension, or SCD, refers to design techniques for handling changes to dimensional attributes over time.

The name does not require changes to occur slowly in a literal sense.

It describes a family of data-modeling patterns.

Ralph Kimball's dimensional modeling work distinguishes several approaches.

Two particularly important approaches are Type 1 and Type 2.

Type 1: Overwrite the Previous Value

Under Type 1, the existing dimension record is updated.

For C101:

Old territory: East.

New territory: West.

The East value is replaced.

The current record becomes:

C101, West.

This approach is useful when the old value is not needed for analysis or when a change is intended to correct an error.

For example, correcting a misspelled customer name may not require preserving the incorrect spelling as a separate historical version.

However, Type 1 overwriting can change historical groupings when older facts are joined to the updated dimension.

Type 2: Add a New Historical Version

Under Type 2, a change creates another dimension record.

The previous version is retained.

Each version has an effective period.

For C101, the dimension history becomes:

The end date is exclusive in this example.

The East version applies before April 1.

The West version applies beginning April 1.

The dimension now contains two rows for the same customer.

That is intentional.

Each row describes a different historical version of the customer.

Kimball's Type 2 guidance recommends surrogate keys and effective-date information so that facts can be associated with the correct dimension version.

Reference:

https://www.kimballgroup.com/2008/09/slowly-changing-dimensions-part-2/

Why the Surrogate Key Matters

A customer has a business identifier:

C101.

That identifier remains stable across territory changes.

But a Type 2 dimension can contain multiple records for C101.

A surrogate key uniquely identifies each version.

For example:

1011 identifies C101 while assigned to East.

1012 identifies C101 while assigned to West.

The sales fact table can store the appropriate version key when the historical assignment is resolved during data processing.

Alternatively, the reporting query can resolve the version using the business key and transaction date.

Both approaches require consistent temporal rules.

The surrogate key does not independently determine which version is correct.

The ETL or ELT process must identify the version effective for the relevant business event.

For the broader importance of analytical row grain, see:

Why Is My Dashboard Double-Counting? Start With the Grain of the Data

A Type 2 customer dimension has one row per customer version, not one row per customer.

Joining sales to every version using only customer_id can multiply rows.

A correct historical join must select the appropriate version.

Reproduce the Historical Comparison in SQL

The following example uses synthetic data defined directly in the query.

It is compatible with SQLite and PostgreSQL using the date strings shown.

It uses half-open effective-date intervals:

A NULL valid_to represents the current open-ended version.

This convention prevents ambiguity at a change boundary.

WITH sales (
    order_id,
    customer_id,
    order_date,
    amount_usd
) AS (
    VALUES
        ('O1', 'C101', '2026-02-10', 1200),
        ('O2', 'C101', '2026-05-05', 800),
        ('O3', 'C202', '2026-02-12', 500),
        ('O4', 'C303', '2026-05-12', 700)
),

territory_history (
    customer_id,
    territory,
    valid_from,
    valid_to
) AS (
    VALUES
        ('C101', 'East', '2026-01-01', '2026-04-01'),
        ('C101', 'West', '2026-04-01', NULL),
        ('C202', 'West', '2026-01-01', NULL),
        ('C303', 'East', '2026-01-01', NULL)
)

SELECT
    h.territory,
    SUM(s.amount_usd) AS revenue_usd,
    COUNT(*) AS orders

FROM sales AS s

JOIN territory_history AS h
    ON s.customer_id = h.customer_id
   AND s.order_date >= h.valid_from
   AND (
       h.valid_to IS NULL
       OR s.order_date < h.valid_to
   )

GROUP BY
    h.territory

ORDER BY
    h.territory;

Expected output:

The calculation preserves the territory valid on each order date.

It is an illustrative query, not a complete production data warehouse implementation.

In production, dates and timestamps should use appropriate database types and consistent timezone rules.

The example also assumes each order matches exactly one valid dimension version.

That assumption must be tested.

The Most Important SQL Validation: Exactly One Version per Fact

A temporal join can fail in two opposite ways.

First, a fact may match no dimension version.

This can happen when historical coverage has gaps.

Second, a fact may match multiple versions.

This can happen when effective-date intervals overlap.

Either situation can distort reporting.

Suppose C101 accidentally has these records:

East: January 1 through May 1.

West: April 1 onward.

A transaction on April 15 matches both versions.

If the join returns two rows, revenue may be counted twice.

The presence of a Type 2 dimension does not automatically prevent double-counting.

A production validation should confirm:

1. Every eligible sales fact matches exactly one historical dimension version.
2. Effective intervals do not overlap for the same business entity.
3. Version keys are unique.
4. Natural business identifiers are consistently defined.
5. Date boundaries follow a documented convention.
6. Missing historical assignments are not silently discarded.

For a left-join coverage test, count matching dimension versions per order.

Any result other than one should trigger investigation under this model's assumptions.

Do not use DISTINCT as a generic repair.

Removing duplicate-looking rows may conceal incorrect effective-date logic rather than fixing it.

Business Effective Time Is Not the Same as Database Change Time

An important complication arises when the business-effective date differs from the date a change was recorded.

Suppose C101 transfers from East to West effective April 1.

The CRM administrator enters the change on April 10.

There are now two relevant timestamps.

Business effective time:

April 1.

System recording time:

April 10.

For a report asking which territory was responsible for an April 5 order under the latest approved business assignment, West may be appropriate.

For a report asking what the CRM actually showed on April 5, East may be the correct answer.

These are different historical questions.

A single effective-date column cannot always answer both.

The distinction becomes particularly important for:

Sales compensation.

Contract ownership.

Compliance reporting.

Customer account history.

Operational audit trails.

Historical management decisions.

A system that needs both perspectives may require bitemporal modeling.

What Is Bitemporal Data Modeling?

Bitemporal modeling distinguishes two dimensions of time.

Valid time describes when information applies in the business domain.

System time describes when the database recorded or knew the information.

For example:

Business effective date: April 1.

CRM update recorded: April 10.

The system can potentially answer:

What assignment is now considered valid for April 5?

And:

What assignment was known to the system on April 5?

Those questions need not produce the same answer.

Microsoft SQL Server supports system-versioned temporal tables that preserve earlier database row versions.

Its documentation describes temporal tables as useful for reconstructing historical database states and supporting slowly changing dimensions.

Reference:

https://learn.microsoft.com/azure/azure-sql/temporal-tables

However, system-versioning is not automatically equivalent to business-effective dating.

A system-generated validity timestamp generally reflects when the database version changed.

It does not necessarily establish when a territory reassignment was contractually or operationally effective.

If both histories matter, the model must preserve both.

Can dbt Snapshots Solve This Problem?

dbt provides snapshots for tracking changes to mutable source tables.

Its documentation describes snapshots as an implementation of Type 2 Slowly Changing Dimensions.

A snapshot records versions of a source row over time.

Common snapshot metadata includes:

dbt_valid_from.

dbt_valid_to.

dbt_scd_id.

dbt_updated_at.

The exact columns and behavior depend on the configuration and dbt version.

Official documentation:

https://docs.getdbt.com/docs/build/snapshots

Snapshots can be useful when a source system overwrites customer attributes.

But there are important limitations.

Snapshots Cannot Recover History That Was Never Captured

Suppose the CRM overwrote C101's territory last year.

No change log, backup, or historical snapshot exists.

Creating a dbt snapshot today does not reconstruct the missing historical East assignment.

It begins preserving the information available from the point at which the snapshot process starts.

Historical reconstruction may require another trustworthy source.

Snapshot Frequency Matters

Suppose a customer changes territory twice between scheduled snapshot runs.

A periodic snapshot may not capture every intermediate state.

The result depends on the source's available change history and the snapshot strategy.

For systems requiring complete change capture, a source audit log or change data capture process may be more appropriate.

Snapshot Validity Is Not Necessarily Business Validity

A dbt snapshot may preserve when the source row was updated.

But a backdated territory change can have an earlier business-effective date.

The analyst must not assume that every snapshot timestamp is the correct business attribution date.

The distinction between event time, ingestion time, and source update time is explored in:

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

That article focuses on when transactions become available.

The present article focuses on which historical dimension attributes should be applied to those transactions.

These are related but distinct modeling problems.

Which Reporting View Should the Business Use?

The answer depends on the decision.

Consider four reporting views.

A business may legitimately need more than one.

For example, sales leadership might use historical ownership for performance evaluation.

An account manager might use current portfolio history for customer planning.

Finance may use an approved restatement policy for management reporting.

An auditor may need to reproduce what the system showed before a correction.

These views should have explicit names.

A dashboard filter labeled simply "Territory" may be insufficient if several attribution policies are possible.

The semantic meaning of a metric should be documented alongside its SQL calculation.

Historical Territory Attribution Is Not Necessarily Sales Compensation Attribution

A technical effective-date join can determine which territory was associated with a customer on a particular date.

That does not automatically determine who should receive sales commission.

Compensation rules may involve:

Split credit.

Account transition periods.

Contract effective dates.

Shared account ownership.

Manual exceptions.

Product-specific territories.

Retroactive adjustments.

The compensation policy may differ from the customer territory dimension.

If commissions depend on explicit assignment or contract rules, those rules should be represented separately.

The analyst should not infer a legal or contractual entitlement from a generic CRM territory field.

A trustworthy data model distinguishes descriptive attributes from business rules that determine financial consequences.

How Should Retroactive Corrections Be Handled?

Suppose a manager discovers that the April 1 transfer was recorded with the wrong effective date.

The approved date should have been March 15.

Correcting the historical dimension may change which territory receives certain March transactions.

That can be a legitimate restatement.

But it should not occur silently when the report is used for important decisions.

A controlled process should record:

The original effective date.

The corrected effective date.

The source of the correction.

Who approved it.

When the correction was recorded.

Which reporting periods were affected.

Whether previously issued reports require restatement.

The reporting system may need to retain both the originally reported result and the latest approved result.

The objective is not to freeze every historical number forever.

It is to ensure that historical revisions are explainable.

Does a Territory Revenue Change Prove Better Sales Performance?

No.

In the worked example, West's revenue increases from $1,300 under historical assignment to $2,500 under current-portfolio assignment.

That is a $1,200 increase in the reported classification.

It is not evidence that West generated $1,200 of additional sales.

The underlying transactions are identical.

Only the attribution rule changed.

Even when a territory's observed revenue genuinely increases over time, that association does not by itself establish that a manager, territory redesign, or CRM reassignment caused the improvement.

Customer demand, product mix, pricing, market conditions, seasonality, and account selection may also influence revenue.

Evaluating the causal effect of a territory redesign would require an appropriate research design and a clearly defined counterfactual.

Historical attribution is a measurement problem.

Causal performance evaluation is a different analytical problem.

Correcting the measurement is necessary for credible analysis, but it does not automatically establish causality.

A Practical Implementation Framework

A business does not need to redesign its entire data platform before addressing historical dimension changes.

A focused implementation can begin with six steps.

Step 1: Define the Reporting Question

Decide whether the business needs historical ownership, current portfolio, as-known-at-time reporting, or multiple views.

Step 2: Declare the Grain

For example:

Sales fact: one row per completed order.

Customer Type 2 dimension: one row per customer version.

Document the keys and permitted relationships.

Step 3: Identify Which Attributes Need History

Not every customer attribute requires Type 2 treatment.

Territory, account ownership, customer segment, and contractual classification may need historical preservation.

Other attributes may be appropriate for Type 1 correction.

Choose based on analytical requirements.

Step 4: Establish Reliable Effective Dates

Determine the authoritative source for when changes become effective.

Distinguish business-effective timestamps from source update timestamps.

Step 5: Build and Test the Temporal Join

Validate unique version keys, nonoverlapping intervals, complete coverage, and one-to-one fact-to-version assignment.

Reconcile totals against the source fact table.

Step 6: Document the Reporting Views

Make historical and current-portfolio views distinguishable in dashboards.

Include the attribution policy in metric definitions and reporting documentation.

This is a practical example of why data engineering is more than moving records between systems.

The data model must preserve the meaning of the business question.

Frequently Asked Questions

What is the difference between SCD Type 1 and Type 2?

Type 1 overwrites an existing dimension attribute. Type 2 preserves previous values by creating new versioned dimension records with defined effective periods.

Does SCD Type 2 prevent historical reports from changing?

It can preserve historical attribute assignments, but it does not guarantee that every historical report remains unchanged. Corrections, incomplete source history, changing business definitions, and faulty temporal joins can still affect results.

Can a Type 2 dimension contain multiple rows for one customer?

Yes. Multiple rows are expected when they represent different historical versions. Each version should have a unique key and a well-defined effective interval.

Should every customer attribute use Type 2?

No. Historical tracking should be driven by reporting and governance requirements. Some corrections are better handled through Type 1 overwriting.

Are dbt snapshots the same as business-effective-date history?

Not necessarily. Snapshots preserve detected source changes according to their configuration. Business-effective dates may differ from the timestamps at which changes were observed or recorded.

Why can territory totals change while company revenue stays the same?

Revenue may be redistributed between territory categories when a customer is reassigned. The company total can remain unchanged even though territory-level figures change.

The Bottom Line: Preserve the History the Decision Actually Needs

Return to the hypothetical company.

Its four orders total $3,200.

Using current CRM assignments:

East reports $700.

West reports $2,500.

Using historical effective-date assignments:

East reports $1,900.

West reports $1,300.

Both views total $3,200.

The difference is not arithmetic.

It is the meaning assigned to historical transactions.

Slowly Changing Dimensions provide a practical way to preserve changing customer attributes.

Type 2 dimensions are especially useful when the business needs to associate historical facts with the attributes effective at the time.

But the technique must be implemented carefully.

Effective dates, surrogate keys, temporal joins, source history, and reporting policies all matter.

A technically correct SQL join can still answer the wrong business question.

The goal is not simply to retain more historical records.

It is to ensure that the organization can explain what its reports mean, why historical classifications changed, and which version of the past is appropriate for a particular decision.

Reliable business intelligence begins with that distinction.

References and Methodological Notes

1. Kimball, Ralph. (2008). Slowly Changing Dimensions, Part 2. Kimball Group. https://www.kimballgroup.com/2008/09/slowly-changing-dimensions-part-2/
2. dbt Labs. Add Snapshots to Your DAG. dbt Developer Hub. https://docs.getdbt.com/docs/build/snapshots
3. Microsoft. Temporal Tables. Microsoft Learn. https://learn.microsoft.com/azure/azure-sql/temporal-tables
4. UAD Consulting. Why Is My Dashboard Double-Counting? Start With the Grain of the Data. https://www.uadconsulting.com/data-grain-dashboard-double-counting
5. UAD Consulting. Why Did Yesterday's Revenue Change? Late-Arriving Data, Backfills, and Reporting Cutoffs. https://www.uadconsulting.com/late-arriving-data-reporting-backfills

Methodological Note

All customer identifiers, order amounts, territory assignments, dates, and calculations are hypothetical teaching examples.

They do not represent actual UAD Consulting client data.

The SQL assumes complete historical coverage, nonoverlapping effective-date intervals, one eligible dimension version per sales fact, and consistent business date definitions.

The example uses order amounts for simplicity. It does not attempt to model formal revenue recognition, refunds, taxes, currency conversion, sales compensation, or contractual attribution.

The historical reporting differences are descriptive changes in classification, not estimates of causal sales performance.

Build Reporting That Preserves Business History

Customer assignments, organizational structures, and business definitions change. UAD Consulting helps organizations design reliable SQL models, analytical databases, and reporting workflows that preserve the context behind historical decisions.

Explore ServicesGet in Touch