Modeling Data Transformations in Logical Steps

Stop Mixing Raw and Derived Data in Your Integration Layer

Summary

Too often, data teams store raw source columns and business derivations together in the same intermediate tables. This creates lineage confusion, audit headaches, and broken reproducibility when logic changes.

This article proposes a simple modeling pattern: separate derivations from pass-through data, historize every meaningful change (bi-temporal SCD2), and keep purely technical reshaping as lightweight views.

The result: clear lineage, reproducible outputs, and maintainable data platforms — while saving storage by avoiding unnecessary duplication of raw data.

🚀 Key Takeaways

Two transformation types: Structural reshaping (no meaning change) vs. Derivational transformations (new meaning or filters).
Reshape with views: Rename, cast, and split source entities without materializing.
Historize derivations only: Store and version only new calculations and filtered sets — don’t duplicate historized source ==data==.
Model step-by-step: One derivation = one transformation; avoid big monolithic SQL scripts.
Stable interfaces: Combine raw + derived data into clean business entities for downstream use.

A Familiar Pain Story

You’re debugging a critical report at 11 PM. The CFO asks why “active customers” counted 50k last month but only 43k now.
You open the SQL behind the table feeding the report — a 400-line script mixing raw CRM data, some mysterious filters, and a few calculated fields.

Where is the original customer set?
When did the “active” rule change?
Can we reproduce what was delivered last quarter?

Too often, you can’t answer. The transformation is a black box. Raw and derived data live together in one big intermediate table, so when business logic changes, history is lost and storage balloons with duplicated data.

It doesn’t have to be this way.

Two Kinds of Transformations — and Why the Distinction Matters

1. Structural / Technical Transformations (no meaning change)

Examples: column renames, data type casting, splitting one source table into multiple logical entities, key normalization.

Characteristics:

Idempotent: the same input always yields the same output.
No business logic applied.

Materialization:
Use views or ephemeral CTEs (in dbt). No history needed.

2. Derivational Transformations (change meaning)

Examples: calculating gross_margin = revenue — cost, classifying customers into segments, filtering to a curated set of primary keys, data quality adjustments or enrichment with reference data.

Characteristics:

Business logic is applied.
Results may change when rules change.

Materialization:
Use tables with bi-temporal SCD2 historization. Keep each derivation’s output versioned for auditability and reproducibility.

Storage note:
Only store and historize derivations.
Don’t copy raw, historized source data into the integration layer again — it’s already stored in your historized source layer.
The integration layer should be thin: persist only derived outputs and expose integrated business views that combine those derivations with the existing historized source data.

Relation to Data Vault Concepts

If you’re familiar with the Data Vault methodology, you’ll recognize a similar idea to the separation of the Raw Vault and Business Vault.
In this approach we deliberately don’t introduce the Raw/Business Vault

terminology — we want to avoid adding extra jargon. Instead, we logically separate transformations by their nature (pass-through vs. derivation) and use materialization settings (e.g., view, ephemeral, incremental) to achieve the right physical implementation.
The principle is the same: keep raw source history intact, and persist only the business logic transformations that change the meaning of data.

Recommended Materialization Strategy

This pattern works especially well with dbt:

Use ephemeral or view for structural steps.
Use incremental or table models for derivations with historization.
Build interface views that recompose raw and derived columns into a clean business entity without duplicating raw storage.

Example: Customer Data

stg_customer — historized raw source layer
reshape_customer — view: renaming & casting only
drv_customer_segment — table: SCD2 with calculated segment
drv_active_customers — table: SCD2 set of PKs meeting “active” rule
int_customer — final business-facing view combining base + derived

Notice that only the derivations (drv_*) are materialized.
The original stg_customer (already historized) stays untouched and isn’t duplicated.

A Simple dbt Setup

dbt_project.yml

models:
  my_project:
    staging:
      +materialized: view
      +tags: ["raw"]
    reshaping:
      +materialized: ephemeral
      +tags: ["raw"]
    derivations:
      +materialized: incremental
      +tags: ["derived", "historized"]
    integration:
      +materialized: view
      +tags: ["business_interface"]

Example model: models/derivations/drv_customer_segment.sql

{{ config(
    materialized='incremental',
    unique_key='customer_id',
    tags=['derived', 'historized'],
    meta={
      'business_owner': 'CRM team',
      'description': 'Calculates customer segments for marketing analytics'
    }
) }}

SELECT
    c.customer_id,
    CASE
        WHEN total_spend > 10000 THEN 'PLATINUM'
        WHEN total_spend > 5000  THEN 'GOLD'
        ELSE 'SILVER'
    END AS segment,
    CURRENT_DATE() AS valid_from
FROM {{ ref('reshape_customer') }} c

This setup keeps reshape steps light and ephemeral while ensuring every derivation is persisted and historized — without duplicating the source.

Why Historization Matters

Business logic changes — tax rules, product definitions, “active customer” thresholds.
If you don’t persist derivation results over time, you can’t explain past reports or reproduce numbers.
SCD2 bi-temporal tables capture both validity time and processing time, making your integration layer audit-ready — while keeping storage efficient by materializing only derivations.

How to Adopt This Pattern

Naming convention: e.g., stg_, reshape_, drv_, int_.
Default historization: assume every derivation will need SCD2.
Meta tagging: in your catalog or dbt meta, mark models as raw vs derived.
Refactor gradually: pull out derivations from monolithic SQL and materialize them.
Educate your team: raw ≠ derived; don’t duplicate historized data.

Connections to Other Modern Practices

Business-friendly mapping:
“Business-Friendly Mapping in Action — From Sources to Integration” and “Cooking with Data: How Business-Friendly Mapping Brings Clarity to ETL”.
Metadata-driven design:
“From Kimball to Metadata: How Dimensional Thinking Still Shapes Modern Data Architecture” and “Optimizing Historization with Logical Source Models”.
Business-shaped integration layers:
“The Case for a Business-Shaped Integration Layer” and “Unlock Your Business’s True Potential: The Power of Business-Oriented Data Modeling”.
dbt materializations:
dbt Docs — Materializations explains how to combine ephemeral, view, and incremental models to support this pattern.

Key Benefits Recap

Transparent lineage
Easier testing and deployment
Full reproducibility of historical outputs
Faster builds (fewer unnecessary materializations)
Lower storage footprint — only store derivations, not duplicate historized source data
Clear contract between integration & semantic layers

Final Thought

Every filter or calculation is a business decision.
Business decisions deserve history — column renames don’t.
And your storage deserves efficiency — don’t copy what you already historize.

I’d love to hear from other data modelers and engineers:

How do you separate raw vs derived data in your transformations?
Do you historize derivation outputs, and if so, how do you implement it (SCD2, snapshots, versioned tables)?

What patterns have worked well (or failed) for reproducibility, lineage clarity, and storage efficiency?

Share your experience or lessons learned in the comments — let’s compare notes and refine these patterns together.