Six steps to your integration layer

In this chapter

• Scope comes first. One slice of the business, through six steps that each feed the next.
• Every step has a test. Who’s in the room, when it’s done, how it fails.
• The second pass is faster. What this pass recorded, checked against one scorecard, is the next pass’s context.

Where do you start? The whole estate is where teams most often go wrong. Cut the work to a slice of the business one team can finish end to end, such as Customer, Order or Product. That slice is a subject area. One governed subject in six weeks teaches you more than a charter in six months, and leaves something running.

Walk it through six steps, each producing what the next consumes, with the people and platform you have and no new budget line. At the end stands an integration layer, the silver layer built against your business’s agreed model. Steps 1 and 2 run in parallel (modelers on the language, engineers on the sources). Step 3 is the first that needs both.

Figure 10.1: read the sequence as dependencies, not calendar slots. This pass’s decisions are the next pass’s context.

Step 1: Agree the language, and only for this subject

Write down this subject area’s business language, as Speak before you store lays out. List entities with definitions and synonyms, and relationships as sentences. Record each entity’s business key (how the business tells one occurrence from another), or flag that nobody can name one. Add naming rules somebody can execute in order.

Book the meeting first, because the meeting is the model. Keep the room small: the business expert who owns the meaning, someone who knows what the data holds, a modeler running the modeling rules in real time, and a steward who owns the standard.

You are done when each relationship reads aloud as a sentence the business would say, and the business expert agrees it’s true. The way this fails is the enterprise-wide glossary program: a year of workshops, a taxonomy nobody consumes, and no subject area finished.

Step 2: Capture the sources, then profile them

Put each source on record, as Know your sources describes. Its source register entry gives connection, owner, extract timing, capabilities and limits. The inventory keeps tables and columns on stable identifiers, and each import lands as a diff somebody approves. Then profile the data. A column unique in today’s extract is a candidate key, not yet a ruling. The engineer runs this. Book the source system owner’s hours now, before their absence blocks a mapping.

You are done when you can answer “what can this source not tell us” for every source in the register. The way this fails is trusting the schema about the business. In Willibald, the garden webshop this book’s examples run on, club membership and a club’s contact person are both foreign keys. Only step 1 says which is which.

Step 3: Map one subject end to end

This is the bridge from bronze to silver, from data as it landed to the integration layer. Bind source table to entity and source column to attribute. Every decision, rejections included, carries the record The mapping is the deliverable asks for: who decided, when and why. Which source wins when two disagree? That’s ruled per attribute and recorded with its decider like any mapping, as source precedence. Engineer, modeler, steward and business expert all sit in this room.

You are done when a governed attribute traces back to every column that populates it, across every system that feeds it. The way this fails is a mapping spreadsheet with a deadline and no owner, stale by the following sprint.

Step 4: Choose the shape, in writing

Choose silver’s physical shape from the four integration shapes: dimensional-first, third normal form, Data Vault and one big table. Weigh the factors Choose an integration shape without a religion sets out, among them how often sources change shape, what auditors ask for, what your team can already run, and what each shape costs to query on the platform you own. Write down the decision with its factors, date and deciders.

Under the shape sit its rulings: which business keys the estate counts by, how much history the business owes, and where each split boundary falls and why. The architect proposes. The stewards and the business rule on the keys.

You are done when the rationale sits in the catalog, where the next engineer will look. The way this fails is choosing by religion, then defending it for three years because nobody wrote down why.

Step 5: Stand up the templates and their contract

Write one pattern per kind of object and render every object from metadata, as Templates, not handcrafted tables argues. Declare the conventions as metadata, not code: naming rules, system columns (the load timestamp, record source and others each layer adds) with their roles, hashes, type mappings, bindings of pattern to object, and what each pattern may know, its declared context. The metadata contract the templates read adds the knowledge documents the AI reads (glossaries, requirements, meeting transcripts) as governed, versioned inputs. It also adds the environment and dependency facts a generator needs, such as which platform each layer runs on and the load order. Prove the templates on real metadata, integrated objects included. And name a template author, a role most teams leave empty.

You are done when you change one naming pattern and every generated name moves with it. The way this fails is hand-editing generated output, which quietly turns a generated object back into a maintained one.

Step 6: Generate, validate, govern, and commit

Generate the layer as reviewable files, each with a pass or fail, not a script writing into a schema. Write each column’s lineage as it’s derived. Point the validation rules at the layer, tuned so every finding is actionable, as Lineage, governance, and trust before go-live argues. Put the model on branches, with readable diffs, commits gated by the rules and a history that survives a delete. One named reviewer approves, and everyone else keeps their day job. The machine does the repetitive work.

In Willibald, the contact-person foreign key survives four layers down in persistent staging, the layer keeping every version the source delivered, because step 1 recorded it.

You are done when you can answer “who accepted this, and when” for any object in the layer. The way this fails is a catalog nobody can diff.

What should a subject area leave on record?

Score a subject area against this list in an afternoon.

• Language. Entities, definitions, synonyms, relationships, business keys, executable naming rules.
• Sources. The register with capability notes, the inventory on stable identifiers, approved import diffs.
• Profiles. Observed profiles, with candidate keys held apart from rulings.
• Mappings. Decider, date and reason on each, rejections included.
• Shape. The shape decision, with its rulings underneath.
• Conventions and context. Naming patterns, system columns, hashes, type mappings, bindings, knowledge documents, environment and dependency facts.
• Provenance. Lineage at column grain, written at derivation, with classification and ownership on attributes.
• Governance. Requirements and the actions they gate, the rule catalog, the audit trail.

Why is the second subject area faster than the first?

The six steps are a loop, not a waterfall. Step 6 feeds step 1, because what this pass recorded is the context the next pass reads. An accepted mapping becomes the standard for the next proposal. And a settled naming rule generates the next hundred names. Your decisions don’t exit the process once you’ve made them. They become it.

By the tenth subject area, it’s routine.

Every platform will tell you to land your data and let its AI do the rest. But the rest assumes what these steps produce. No vendor supplies that. In every step, AI proposes and a person decides.

The people part	Four meetings carry the steps (a weekly language hour, a source interview per table, a standing mapping review, one shape decision), and steps 5 and 6 need only a named template author and reviewer.
Metadata to capture	Anything on the scorecard you can’t point at is the next thing to capture.
Regardless of stack	Step 4 weighs one platform-dependent factor, query cost, and holds no platform decision.