Matrix: Processing

How data is transformed and loaded: sequencing, recovery, incremental loading, checks, logging and performance.

≈ 12 min readReading

In brief

8 dimensions, levels 0 to 5, 112 criteria.
Tool: run an assessment by choosing “Processing”.
Levels and criteria: original synthesis, references cited at the bottom of the page.

On this page

Overview
Processing design
Reliability
1. Recovery and idempotence
Quality and evidence
1. Data checks within processing
2. Log, measurement and alerts
Performance and evolution
1. Incremental loading
2. Processing performance and resources
Sources and verification

Scope

What transforms and loads the data: sequencing and scheduling, recovery after an incident, delta loading, transformation rules, checks, execution log, performance. Keeping the results belongs to the “Storage” theme, the arrival of data to the “Ingestion” theme, and shaping it for reports to the “Abstraction layer” theme.

8 dimensions, six levels (0 to 5) and 112 observable criteria. To place your product: open the assessment for this theme. The calculation rules are here.

Overview#

Each cell is the level’s signature for the dimension.

Dimension	0 · Handcrafted	1 · Locally tooled	2 · Shared	3 · Industrialized	4 · Integrated and governed	5 · Autonomous
Orchestration and scheduling	Jobs launched by hand, in an order known to one person only.	Order written down and launches scheduled in isolation.	A single workflow, dependencies declared.	Priorities, windows and calendars under control.	Orchestration driven by events and metadata.	Adaptive orchestration.
Set-based writing and determinism	Transformations written row by row, with a result that may vary from one run to the next.	Transformations isolated and identified.	Sets preferred.	Preparation before the run.	Determinism exploited and verified.	Automatic measurement and safeguards.
Transformation rules and parameters	Rules buried in code and hard-coded values.	Rules listed.	Parameters externalized.	Parameters managed and layered.	Rules documented and tested.	Rules driven by the business.
Recovery and idempotence	A failure leaves data in an unknown state; recovery is manual and risky.	Failures known.	Rerun possible without duplicates.	Idempotent jobs, recovery from the failed step.	Recovery tested and periods replayable.	Automatic recovery and assisted diagnosis.
Incremental loading	Full reload on every run.	Reloads by period.	Change detection.	Complete incremental loading.	End-to-end incremental loading.	Continuous flow where useful.
Data checks within processing	No checks: the data is assumed to be correct.	Manual checks.	Systematic checks.	Declared checks and decision on anomaly.	Business consistency checks.	Adaptive checks.
Log, measurement and alerts	No trace of executions.	Scattered traces.	Single log.	Usable log.	Freshness published, health indicators.	Proactive detection.
Processing performance and resources	Duration endured: you find out a job is too long when it overruns.	Durations known.	Window met.	Parallelism and resources.	Volume growth anticipated.	Elastic resources.

Link with the global model

Dimensions of the global model most concerned: Data sources and flows, Data quality, Testing and fixes and Availability and performance. On how to write processing jobs, see set-based programming and the approach.

Processing design#

Orchestration and scheduling#

Who decides the order of jobs, when they start and how they depend on each other?

Level	Signature	Criteria to meet
0 · Handcrafted	Jobs launched by hand, in an order known to one person only.	Starting point: no criteria.
1 · Locally tooled	Order written down and launches scheduled in isolation.	The order of jobs is written down somewhere. Recurring jobs are triggered by a scheduler.
2 · Shared	A single workflow, dependencies declared.	A single workflow runs all of the product’s jobs. Dependencies between jobs are declared, not assumed from the launch time. A job starts only if the jobs it depends on have succeeded.
3 · Industrialized	Priorities, windows and calendars under control.	Jobs are ordered by priority (for example: ingestion, dimensions, facts, publication). Execution windows and calendars (business days, period closings) are parameterized. The longest path of the workflow is known.
4 · Integrated and governed	Orchestration driven by events and metadata.	Triggering can depend on an event (arrival of a file, end of an upstream job). The workflow is described in metadata, not in repeated code. A workflow can be simulated without running it, to check the order and the dependencies.
5 · Autonomous	Adaptive orchestration.	The workflow reorders or parallelizes itself automatically according to the durations observed. Unnecessary jobs (no new data upstream) are skipped automatically. A drift in duration or sequence is detected and reported before the incident.

Effect on the user

Data arrives at the announced time, in the right order, without anyone having to launch anything.

Set-based writing and determinism#

Are transformations written to process sets, with a result that depends only on the data?

Level	Signature	Criteria to meet
0 · Handcrafted	Transformations written row by row, with a result that may vary from one run to the next.	Starting point: no criteria.
1 · Locally tooled	Transformations isolated and identified.	Each transformation is an identified object (procedure, named query, step). Row-by-row loops are identified.
2 · Shared	Sets preferred.	Common transformations are written as queries on sets rather than as loops. Calculation dates are passed as parameters and not read from the clock inside the job. The result of a transformation does not depend on the order of the input rows.
3 · Industrialized	Preparation before the run.	The model (tables, indexes, scopes) is prepared before the calculation, not during it. Each job consists of a few large queries rather than many small ones. The functions used in calculations are deterministic, or their use is justified.
4 · Integrated and governed	Determinism exploited and verified.	Determinism is declared and exploited (computed columns, indexed views, generated code with hard-coded values). A test reruns a job on the same data and compares the results. No non-deterministic block is called from a loop.
5 · Autonomous	Automatic measurement and safeguards.	The cost of each job (reads, instructions) is measured and tracked over time. An automatic review or check detects loops and costly functions introduced by a change. Differences in results between two identical runs trigger an alert.

Effect on the user

The same dataset always gives the same result, and processing time grows little with volume.

Transformation rules and parameters#

Where do business rules and parameters live, and can they be explained and changed without rewriting?

Level	Signature	Criteria to meet
0 · Handcrafted	Rules buried in code and hard-coded values.	Starting point: no criteria.
1 · Locally tooled	Rules listed.	The main transformation rules are described in writing. Hard-coded values are identified.
2 · Shared	Parameters externalized.	Values that vary (thresholds, scopes, code lists) are stored outside the code. Each rule has a name and a description readable by the business. The code of the jobs is versioned.
3 · Industrialized	Parameters managed and layered.	Parameters are stored in the database with their description and default value. A client-specific or project-specific value overrides the standard one without changing the code. Changing a parameter is traced: who, when, old and new value.
4 · Integrated and governed	Rules documented and tested.	The documentation of the rules is produced from the code and the metadata, not written separately. Each important rule has a set of test cases with an expected result. The impact of a rule change (tables and reports affected) is known before delivery.
5 · Autonomous	Rules driven by the business.	The business can propose or validate a rule change through a defined process, without going through the code. The rules of several products or clients are comparable and reusable. A rule that is never used or that is contradictory is detected automatically.

Effect on the user

A business rule can be read, discussed and changed in one single place, with a trace of who modified it.

Reliability#

Recovery and idempotence#

What happens when a job fails or is rerun?

Level	Signature	Criteria to meet
0 · Handcrafted	A failure leaves data in an unknown state; recovery is manual and risky.	Starting point: no criteria.
1 · Locally tooled	Failures known.	A job failure is noticed (message, email, log). A manual rerun procedure is written down.
2 · Shared	Rerun possible without duplicates.	Rerunning a job does not create duplicates. A failure does not leave a partial result visible to reports. It is known at which step a workflow stopped.
3 · Industrialized	Idempotent jobs, recovery from the failed step.	Each job gives the same result on every run on the same data (idempotence). Recovering a workflow restarts at the failed step, without replaying everything. Automatic retries are bounded and spaced out before a failure is declared.
4 · Integrated and governed	Recovery tested and periods replayable.	A past period can be recalculated on demand, with no effect on the other periods. Recovery after a failure is tested at regular intervals (failure provoked in a test environment). Data is published by atomic switch: reports see the old or the new set, never a mix.
5 · Autonomous	Automatic recovery and assisted diagnosis.	Transient failures are recovered automatically, without intervention. For each failure, a probable cause and an action are proposed automatically. The mean time to recovery is tracked and it decreases.

Effect on the user

An incident costs one rerun, not a manual intervention or doubt about the state of the data.

Quality and evidence#

Data checks within processing#

Do jobs verify their inputs and outputs, and what do they do about an anomaly?

Level	Signature	Criteria to meet
0 · Handcrafted	No checks: the data is assumed to be correct.	Starting point: no criteria.
1 · Locally tooled	Manual checks.	Checks are done by hand after some loads. The volumes loaded are compared with those of the source.
2 · Shared	Systematic checks.	Each load verifies simple rules: key uniqueness, mandatory values, permitted values. Volume differences are quantified after each load. An anomaly is recorded with the row concerned.
3 · Industrialized	Declared checks and decision on anomaly.	Checks are declared in reusable definitions, not written case by case. Each check specifies whether it blocks publication, quarantines the row or only raises an alert. Rejected rows are kept and can be consulted.
4 · Integrated and governed	Business consistency checks.	Consistency checks exist across sources and across periods (totals, reconciliations, trends). The anomaly rate is tracked over time, by source and by check. A failing or obsolete check is identified and reviewed.
5 · Autonomous	Adaptive checks.	Alert thresholds are computed from the observed history. A new anomaly triggers the proposal of a new check. The result of the checks is visible to users alongside the reports.

Effect on the user

A wrong figure is stopped or flagged before publication, not discovered by a user.

Log, measurement and alerts#

Can you say what ran, for how long, with what result, and who is notified?

Level	Signature	Criteria to meet
0 · Handcrafted	No trace of executions.	Starting point: no criteria.
1 · Locally tooled	Scattered traces.	Each execution leaves a trace (file, message) that can be consulted. Errors can be read by someone other than the author of the job.
2 · Shared	Single log.	A central log records the start, end, duration and result of each job. Error messages are recorded in full, with the job concerned. Failures trigger an alert to a designated person or mailbox.
3 · Industrialized	Usable log.	The log records the number of rows read, written and rejected per step. The history of durations makes it possible to spot jobs that are drifting. An alert distinguishes blocking incidents from mere warnings.
4 · Integrated and governed	Freshness published, health indicators.	The date of the last update is visible to users for each dataset. A dashboard tracks the health of jobs (success, duration, delay). Alerts have an owner and a response time.
5 · Autonomous	Proactive detection.	A probable delay is announced before the job ends, based on past durations. The users concerned are notified automatically of a data delay. The causes of incidents are classified and their frequency tracked to decide on improvements.

Effect on the user

You know before the users whether the data is up to date, and you know why when it is not.

Performance and evolution#

Incremental loading#

Do you process only what has changed, or everything every time?

Level	Signature	Criteria to meet
0 · Handcrafted	Full reload on every run.	Starting point: no criteria.
1 · Locally tooled	Reloads by period.	Large datasets are reloaded by period rather than in full. The scope to reload is chosen by hand.
2 · Shared	Change detection.	Each source exposes a way to identify what has changed (modification date, version, log). Jobs load new or modified rows. A full reload remains possible for verification or recovery.
3 · Industrialized	Complete incremental loading.	Deletions in the source are detected and propagated. Data arriving late or out of order is handled correctly. A check regularly compares the incremental result with a full reload.
4 · Integrated and governed	End-to-end incremental loading.	Each layer (raw, validated, ready to use) is updated incrementally. Aggregates are recalculated only for the periods affected. The update frequency is chosen according to business need, layer by layer.
5 · Autonomous	Continuous flow where useful.	Data that warrants it arrives as a continuous or near-continuous flow. The frequency adapts automatically to data availability and load. The cost and latency of each mode (continuous, scheduled) are compared to decide.

Effect on the user

Data is updated more often, with fewer resources, even as the history grows.

Processing performance and resources#

Do jobs meet their window, and can they take advantage of the available resources?

Level	Signature	Criteria to meet
0 · Handcrafted	Duration endured: you find out a job is too long when it overruns.	Starting point: no criteria.
1 · Locally tooled	Durations known.	The duration of the main jobs is recorded. The slowest job is identified.
2 · Shared	Window met.	A target execution window is set for each workflow. The most costly jobs have been optimized at least once. Durations are compared from one run to the next.
3 · Industrialized	Parallelism and resources.	Independent jobs run in parallel. Compute resources are sized from measurements, not by guesswork. A reproducible measure (reads, instructions) complements the duration, which varies with load.
4 · Integrated and governed	Volume growth anticipated.	Tests on a larger volume than the current one are carried out before a scale-up. The duration of a job is tracked as a function of the volume processed. A change that degrades duration is detected before going into production.
5 · Autonomous	Elastic resources.	Compute capacity adjusts automatically to the load of the workflow. Resources are released as soon as the jobs finish. Trade-offs between duration and cost are reviewed on the basis of regular measurements.

Effect on the user

Data is ready when users arrive, and growth in volume does not call the window into question.

Origin of the criteria

The dimensions are inspired by the references cited below; the levels and the criteria are an original synthesis, not a reproduction of a framework. They are offered to be discussed and adapted, not as authority.

Sources and verification#

Kimball Group — ETL Architecture's 34 Subsystems — four families of subsystems (extract, transform, load, manage); management covers audit, recovery, scheduling and alerts
Apache Airflow — Best Practices — idempotent tasks (same result on every rerun), test levels for a workflow, avoiding costly code at the top level
dbt — Add data tests to your DAG — a data test is a query that returns the failing rows; four generic tests: unique, not_null, accepted_values, relationships
Microsoft Learn — What is the medallion lakehouse architecture? (Azure Databricks) — validation and deduplication in the validated layer, incremental reads, trade-off between cost and latency depending on ingestion frequency
DAMA International — DAMA-DMBOK2 — the “Data Integration and Interoperability” domain; paid reference, therefore not read line by line