Data Processing & Transformation Framework

A warehouse you can audit, on any dbt platform

Five layers from landing zone to business marts: full SCD2 history, one canonical model across all your systems, segregated views and marts, with testing, alerting and documentation built in. On BigQuery, Snowflake, Databricks, Fabric or any warehouse dbt supports, in Google Cloud, AWS or Azure.

Book a demo
5
layers, from landing zone to marts
3
kinds of test: unit, engine replay and data
25+
warehouses supported through dbt
Any
cloud: Google Cloud, AWS, Azure or Kubernetes
Why teams choose it

The warehouse your auditors, analysts and engineers agree on

Most dbt projects start as a folder of SQL and end as a maze. This framework starts with the structure, the history and the safety nets that usually arrive years later, if at all.

Every number, traceable

Each layer reads only from the one before it, so any figure in a mart can be followed back through history to the source file it came from. Auditors and regulators get answers, not workshops.

The full history, for free

Every change to every row is kept with its validity interval, so “what did we know on 31 March?” is a query, not a project. New sources get history the day they land.

One language for the business

A canonical model restates every system in one vocabulary and links the same customer across systems, so reports stop disagreeing about what a customer, a contract or an order is.

Tested like software

Unit tests on every mapping, replayed scenarios on the history engine and invariant checks in every build. Broken data stops at the gate instead of reaching a dashboard.

Problems find you first

Failed runs, warning tests, stale schedules and source schema changes raise alerts, with the failing rows kept for inspection. Silence is treated as a failure too.

Documented where people look

Every table and column carries its description inside the warehouse, in more than one language if you need it, and a documentation site is published on every deploy.

The layers

From landing zone to business answers

Each layer has one job and reads only from the layer before it, so every number in a mart can be traced back through history to the file it came from.

  1. 01

    External layer

    01_raw · external tables

    The landing zone, queryable in place. Each source table is an external table over its bucket, recreated on every run so its definition cannot drift from the configuration.

    • Parquet, CSV, JSON, Avro and ORC, with hive partitions exposed as columns
    • Typed column lists declared in the model, or autodetected where that is safe
    • No storage path in any model: buckets resolve per environment
  2. 02

    SCD2 history

    02_hist · incremental

    Every version of every row, with its validity interval. A history model is a configuration block and one macro call.

    • Engines for CDC feeds and for full snapshots, where a key missing from a snapshot closes its history
    • Surrogate, hash and change-detection keys, validity intervals and delete tombstones
    • Replays, late events and same-instant changes resolved deterministically, so reruns merge nothing
    • Merges restricted to open rows through clustering, so incremental runs stay cheap
    • Schema evolution: new columns appended, types widened in place, history never dropped
  3. 03

    Canonical model

    02_hist/conf · conformed SCD2

    The per-system histories restated in the vocabulary of one conceptual data model, so a column name means one thing whatever system produced the row.

    • Built from the histories and evaluated at every instant any input changed, so it is correct as of any point in time
    • A registry of source systems that every conformed and metadata model is tested against
    • Cross-system entity resolution: records linked by shared identifiers and check-digit-validated national IDs, conflicts flagged, and scored match candidates proposed for human review
  4. 04

    Current & segregation views

    03_curr · views

    The live row per key, as views, in one dataset per layer and source system, so access can be granted exactly as wide as each consumer needs.

    • Deleted rows are absent rather than flagged, so consumers need no filter
    • Joined views walk the canonical model from one entity across every related table, for exploration and support
    • An incremental copy serves the current layer from PostgreSQL (AlloyDB, Aurora or Azure Database) to applications, deletes included
  5. 05

    Marts

    04_mart · business views

    One view per business question over the canonical model, either current or as of any instant through the history.

    • Point-in-time questions answered from history, not from overwritten snapshots
    • UAT views that compute business-glossary terms row by row over a configurable window of month ends, so testers can compare them with the existing warehouse
    • Sample views that show a few complete records end to end, the same ones on every query
Built in

Testing, alerting and documentation from day one

What usually gets bolted on after go-live is part of the framework, and new models fail to build without it.

01

Testing

Three kinds of test, each answering a different question, all run in CI.

  • dbt unit tests pin every canonical mapping and every business rule’s boundaries with input and expected rows
  • Engine fixtures replay multi-phase scenarios through the SCD2 engines in throwaway datasets: watermarks, replays, deletes, reruns
  • Data tests in every build: SCD2 invariants (one open row per key, unique keys, contiguous intervals) on every history, enforced at parse time
  • Reconciliation of each canonical model against its sources, plus the business rules the mapping guarantees
  • Errors stop the run before anything is served; warnings keep it green and are reported. Failing rows land in an audit dataset, one table per test

Built with: dbt unit tests, singular and generic tests, store_failures and pytest.

02

Alerting & observability

Silence is treated as a failure too, not only errors.

  • Alerts for a failed job or workflow, and a daily digest when data tests warned
  • An alert when the metadata catalogue stops being refreshed, which catches a paused or skipped schedule
  • An alert naming the tables and columns a source added or removed
  • Every dbt invocation, model and test result persisted to an observability dataset

Built with: Your cloud’s native monitoring (Cloud Monitoring, CloudWatch or Azure Monitor) and the dbt_artifacts package.

03

Documentation & comments

Documentation lives where people query, not only in a separate site.

  • Every table and column description persisted to the warehouse as a native comment
  • Bilingual descriptions (English plus your working language), with tests that fail when a language is missing
  • A catalogue view that exposes the descriptions to analysts and BI tools
  • The dbt docs site generated on every deploy and published per branch
  • A metadata catalogue of every source’s tables and columns, historised for your governance tool

Built with: persist_docs, docs blocks and a docs job published to static pages.

The difference

A typical dbt project vs the framework

Same open-source dbt underneath. The difference is everything built around it.

Capability Typical dbt project(hand-grown) Transformation Framework
SCD2 history for every source table Snapshots, per table
History from CDC feeds and full snapshots
Point-in-time canonical model across systems
Cross-system entity resolution with review queue
Invariant tests enforced on every history
Unit tests on every mapping and business rule Rarely
Failing rows kept in an audit dataset Sometimes
Alerts on failures, warnings, staleness and schema change Failures only
Descriptions persisted as warehouse comments, multilingual Sometimes
Models generated from the ingestion configuration
Platforms

Runs where dbt runs

dbt connects to more than 25 warehouses and query engines, and the framework goes wherever dbt goes: the platform you already pay for, in the cloud you already use.

Warehouses dbt works with

Through adapters maintained by dbt Labs, the platform vendors and the community. The framework runs on any of them.

  • BigQuery
  • Snowflake
  • Databricks
  • Amazon Redshift
  • Microsoft Fabric
  • Azure Synapse
  • SQL Server
  • PostgreSQL
  • Oracle
  • Teradata
  • Apache Spark
  • DuckDB
  • ClickHouse
  • Trino
  • Amazon Athena
  • AWS Glue
  • SingleStore
  • Apache Hive

Also AlloyDB (through the PostgreSQL adapter), Starburst, Dremio, StarRocks, Firebolt, Materialize, Exasol, Vertica, Apache Impala and IBM Db2.

Built on

  • dbt Core
  • Jinja
  • Python
  • Docker

Any cloud

Containerised jobs on each cloud’s managed services or on Kubernetes, landing on Cloud Storage, Amazon S3 or Azure Data Lake Storage.

  • Google Cloud
  • AWS
  • Microsoft Azure
  • Kubernetes
  • PostgreSQL serving

Operated with

  • Cloud Workflows
  • AWS Step Functions
  • Azure Data Factory
  • Cloud Monitoring
  • Amazon CloudWatch
  • GitLab CI
  • GitHub Actions
Engineering

The parts that keep it maintainable

Conventions, generators and one runner, so the tenth source system looks exactly like the first.

Environments and CI

Dev, test and prod from one codebase, a sandbox dataset per developer, and an image that parses every model at build time, so a broken macro never reaches a deploy.

One runner, laptop to cloud

A thin CLI loads each environment, checks it with a doctor command and calls dbt, so the same command runs on a laptop and as a scheduled cloud job.

Generated models

External, history and current models for a new source are generated from the ingestion configuration, with natural keys read from the source and checked for drift.

Analytics and applications

The warehouse serves analytics and BI, and the current layer is copied incrementally into PostgreSQL (AlloyDB, Aurora or Azure Database for PostgreSQL) for applications, indexed on every natural key.

How it works

From conceptual model to a warehouse in production

A fixed delivery path into your own cloud, not an open-ended project.

  1. 01

    Model the domain

    We agree the conceptual model with your business owners, then map each source system onto it entity by entity.

  2. 02

    Stand up the layers

    External tables and SCD2 histories are generated for every source, and the canonical models are written with unit tests from day one.

  3. 03

    Prove it in UAT

    Reconciliation tests and the UAT views let your testers compare the new warehouse with the old one, term by term and month by month.

  4. 04

    Run and extend

    Scheduled builds, alerts and published documentation. New sources and marts follow the same patterns, so the warehouse grows without drifting.

FAQ

Questions data teams ask us

Which warehouses does it run on?

Any warehouse dbt supports, including BigQuery, Snowflake, Databricks, Redshift, Microsoft Fabric, Synapse, SQL Server, Oracle and Teradata, on Google Cloud, AWS or Azure. Warehouse-specific behaviour lives in a handful of macros, so the layers, tests and conventions stay the same wherever it runs, and a PostgreSQL copy of the current layer can serve your applications.

Do we need the Data Ingestion Framework to use it?

No. The external layer reads any landing zone in Parquet, CSV, JSON, Avro or ORC, and the history engines accept either CDC feeds or periodic full snapshots. The ingestion framework simply produces that landing zone with no extra work.

How does the history survive source schema changes?

New columns are appended and their past versions read as NULL, widened types are altered in place, and a column that disappears from the source keeps its history. Changing a natural key is the one change that needs a deliberate rebuild.

How do you know the numbers are right?

Unit tests pin every mapping and business rule, engine fixtures replay the SCD2 engines across runs, and every production build runs invariant and reconciliation tests. A failing invariant stops the run before anything is served, and the failing rows are kept for inspection.

Can business users find their way around it?

Yes. Marts are named after the business questions they answer, every table and column carries a description in the warehouse itself and in more than one language if you need it, and the documentation site is published on every deploy.

Product names and logos shown are trademarks of their respective owners. dbt is a trademark of dbt Labs, Inc. The Data Processing & Transformation Framework is an independent product by Duces built on dbt Core and is not affiliated with, sponsored or endorsed by dbt Labs or the platform vendors listed.

Contact Duces

Book a demo

Tell us about your source systems and your warehouse, and we’ll walk you through the framework on a model shaped like yours.