Data Sources and Contracts

A datamart depends on systems it does not control: the field application evolves, a column changes type, a value code is added, an extract is moved. Without an agreement, each…

Guide≈ 7 min readCheckedReading

On this page

Why this theme
What this theme measures
The five levels
Milestones by level
Detailed reading by level
Partial achievement and rating
Move to the next level
See also

Level: 🟢→🔴 · Theme code: CTR

Why this theme#

A datamart depends on systems it does not control: the field application evolves, a column changes type, a value code is added, an extract is moved. Without an agreement, each upstream change is a downstream failure discovered at the next load, or even a silent wrong number. A data contract is a producer's explicit and verifiable commitment to its consumers: schema, semantics, freshness, quality, evolution. Its cost is low compared with that of a lost consolidation night. This theme is the counterpart, on the source side, of quality (QUA) and governance (GOV). Expected behavior in case of schema drift for reporting is detailed on the Power BI side in the file OneLake and Mobile SaaS — Challenges.

What this theme measures#

Axis: from passive dependency to bilaterally committed, automatically tested agreements.
Key indicator: share of critical sources covered by a tested contract, and number of unannounced breaks per quarter.
Unit: % of sources under contract; number of failures caused by an unannounced upstream change.

The five levels#

Level	Short name	Promise	Typical indicator (order of magnitude)
1	Passive	Data arrives as long as the source does not change; changes are discovered through failure.	Majority of breaks are unannounced
2	Known	We know who to contact, when sources change (release schedule), and how they are extracted.	100% of sources with contact and extraction method
3	Written	Each critical source has a written contract: schema, semantics, freshness, volumes, owner.	≥ 80% of critical sources under contract
4	Tested	The contract is automatically verified at every delivery; changes are versioned and announced in advance.	Unannounced breaks < 1 per quarter
5	Producer-driven	Producers publish and evolve their contracts; consumers validate before deployment.	Zero unannounced breaks over 12 months

Milestones by level#

ID	Milestone	Verifiable evidence
PD-CTR-1.1	Sources feeding the datamart are inventoried (system, version, access method).	Dated inventory.
PD-CTR-1.2	Extraction (full, incremental, via interface) is described for each source.	Source sheet with method and time window.
PD-CTR-1.3	An incident related to a source change leaves a trace.	Incident log with cause “upstream change”.
PD-CTR-1.4	Technical access to sources is named and read-only.	Service account review.
PD-CTR-2.1	A contact (producer) is designated for each critical source.	Up-to-date directory, tested with a message.
PD-CTR-2.2	The upstream release calendar is known by the data team.	Shared application production release calendar.
PD-CTR-2.3	Volumes and availability windows for each source are known.	Recorded baseline values.
PD-CTR-2.4	A schema non-regression test is run after each upstream release (even manual).	Test report after each release.
PD-CTR-3.1	A written contract per critical source describes schema, types, keys, nullability, allowed values.	Versioned contract document with owner.
PD-CTR-3.2	Semantics are described (business meaning of columns, units, time zones, business rules).	Dictionary attached to the contract (MET).
PD-CTR-3.3	The producer's freshness and quality commitments are explicit.	Contract SLA section (linked to QUA).
PD-CTR-3.4	The evolution rule is written: what is compatible, what is breaking, required notice.	Signed compatibility policy.
PD-CTR-4.1	An automatic check compares the actual schema to the contract at each load.	Schema compliance test in the pipeline, with explicit failure.
PD-CTR-4.2	Contract versions are numbered; a breaking change requires a new version.	Contract version history.
PD-CTR-4.3	Consumers receive advance notice of a change within the agreed timeframe.	Archived notifications with observed lead time.
PD-CTR-4.4	Behavior in case of drift is defined: rejection, quarantine, tracked automatic adaptation.	Test of simulated drift; result complies with the rule.
PD-CTR-5.1	Producers run contract tests in their delivery before production release.	Contract results in upstream pipelines.
PD-CTR-5.2	Consumers declare their dependencies (used columns) to measure impact.	Dependency register by contract.
PD-CTR-5.3	A breaking change is never delivered without a dual-run period.	Real case with two versions in parallel.
PD-CTR-5.4	Contracts are machine-readable and published in a catalog.	Standardized and published format (e.g., ODCS/Bitol, standard status to be verified).

Detailed reading by level#

🟢 Level 1 — Passive#

Signs: “it broke last night, the application changed”; a new code value is discovered through an aberrant number.
Gain: no commitment to uphold.
Does not guarantee: service continuity; understanding of semantics.
Difficulty: 1/5 · Effort: 1/5.

🟢 Level 2 — Known#

Signs: the application release schedule is known; testing happens after each release; contacts are up to date.
Gain: breaks are anticipated rather than blindly endured.
Does not guarantee: that the producer will warn about everything.
Difficulty: 2/5 · Effort: 2/5.

🟡 Level 3 — Written#

Signs: one contract per critical source, with columns, types, keys, and rules; compatibility policy is read by both parties.
Gain: a factual discussion when conflict occurs; onboarding a new person becomes possible.
Does not guarantee: that the contract is respected (a document may drift from reality).
Difficulty: 3/5 · Effort: 3/5.

🧪 Example

A minimal contract for a reading table from an SFA app: REL_ID (integer, key, not null), PDV_ID (not null, PDV reference), REL_DAT (date, terminal capture time zone converted to UTC on producer side), REL_QTE (≥ 0; NULL forbidden), available at 5 a.m., 30-day notice for any breaking change, new value in a closed list = compatible but notified change.

🟡 Level 4 — Tested#

Signs: a test compares actual schema against the contract at each load; a non-compliant change is quarantined rather than loaded.
Gain: a break becomes an explicit, fast failure instead of a wrong number.
Does not guarantee: detection of a meaning change with identical schema (a recycled code); combine with distribution checks from QUA.
Difficulty: 4/5 · Effort: 3/5.

-- Vérification de conformité de schéma contre un contrat (T-SQL, extrait)
SELECT C.COLONNE, C.TYPE_ATTENDU, T.DATA_TYPE AS TYPE_REEL
FROM   [ctr].[ContratColonne]       AS C WITH (NOLOCK)
LEFT JOIN INFORMATION_SCHEMA.COLUMNS AS T
       ON T.TABLE_SCHEMA = C.SCHEMA_SRC AND T.TABLE_NAME = C.TABLE_SRC AND T.COLUMN_NAME = C.COLONNE
WHERE  T.COLUMN_NAME IS NULL OR T.DATA_TYPE <> C.TYPE_ATTENDU;   -- tout résultat = rupture de contrat

🔴 Level 5 — Producer-driven#

Signs: contract tests run in the producer's pipeline; dual version publication accompanies every breaking change.
Gain: surprise outages disappear; accountability sits where change originates.
Does not guarantee: buy-in from an external SaaS vendor, which will not publish a contract for you; an isolation layer is then still needed (see below).
Difficulty: 5/5 · Effort: 5/5.

💡 Expert tip

With an external producer (SaaS vendor, third-party API) you will not get a contract: build a unilateral contract downstream (intake zone that validates, quarantine, interpretation versioning). It delivers 80% of the benefit without agreement.

Partial achievement and rating#

Rate based on your five most critical sources. A milestone is worth 1 if true for all five, 0.5 for two to four, 0 otherwise. For external producers, a tested unilateral contract is worth 0.5 on level 5 milestones.

A “partial 5”: the main internal producer runs contract tests in its delivery (5.1 at 0.5), a breaking change has already been delivered with dual-run (5.3 at 1), contracts are shared spreadsheets not machine-readable (5.4 at 0), consumer dependencies are partially declared (5.2 at 0.5).

⚠️ Pitfall

The decorative contract: a 40-page document no one tests. Symptom: the contract says one thing, the source does another. Remedy: only count a level ≥ 4 milestone if an automatic execution proves it.

⚠️ Pitfall

Silent semantic drift: status “3” changes from “delivered” to “partially delivered” with no schema change. Remedy: closed-list values and code distribution are contract clauses, monitored.

Move to the next level#

Possible means (optional)

Transition	Possible levers
1 → 2	Subscribe to source application release notes; hold a quarterly meeting with the internal vendor.
2 → 3	Write the contract from the actual schema, then have it reviewed by the producer (the lowest-cost step).
3 → 4	An intake (staging) zone that validates schema before loading; version contracts in the repository (Version Control for Data: SQL and Power BI).
4 → 5	Publish contracts in a catalog; integrate their tests into producer pipelines (CI/CD for Data: Databases, Datamarts, Pipelines, and Reports).

Cross-cutting pitfalls: (1) False positive — contract tested on schema but never on freshness or values. (2) Goodhart — targeting “number of contracts” produces trivial contracts. (3) Rigidity — an overly strict contract blocks evolution; plan compatible changes that do not require a version.

🤖 AI

It generates a draft contract from a schema, compares two schema versions, and writes migration notes. It does not infer business semantics or producer commitments; a generated contract without producer validation is a hypothesis.