What the conceptual layer must define to derive a Data Vault

The gap between mechanization and real automation closes only if the whole integration layer can be derived from the conceptual layer, deterministically. We argued that in the key to full data warehouse automation and left one thing open: what the conceptual layer actually has to contain. This article gives a first overview. It goes through the main parts of that layer piece by piece and names the information it must define, so the logical model, the physical model, and the data transformations all follow, leaving no Data Vault modeling to a human.

One boundary first. This article is about the integration layer, the Data Vault, and keeps its focus there. Deriving the dimensional model in the presentation layer, or a semantic layer on top, may take further information in the conceptual layer, and that belongs in its own article. Where a definition here also shapes those layers, we will point it out, but the Data Vault is enough to fill this one.

The completeness contract

A deterministic derivation sets a hard bar on the input: it recognizes patterns in the conceptual layer and translates them into Data Vault, with no guessing and no proposal to review. That only works if the conceptual layer is complete. Whatever the derivation would otherwise have to decide, the specification has to state. If it is not in the conceptual layer, it cannot appear in the warehouse.

Two pillars are derived, and each splits again into a data model and the data transformations that fill it. The structural definition, the business concepts and how they relate, gives the Raw Vault model, its hubs, links, and satellites, and the loads that populate them. The business logic, written as SQL on those concepts, gives the Business Vault model and the transformations that build it. So the sections below name, for each construct, what the conceptual layer must define, and what the model and the data transformations each take from that definition.

Business concepts and their keys

Start with identity. A business concept is anything the business names and can tell apart from the rest: a customer, a product, an order. What lets the model build on it is a business key that identifies one instance. Pinning that key down is the first thing the conceptual layer has to do.

A product concept, for instance, names its attributes and marks the business key:

# concepts/product/product.yaml (excerpt)
kind: concept
name: product

attributes:
  - name: product_number
    type: varchar(20)
  - name: product_id
    type: integer
  - name: list_price
    type: decimal(10, 2)

business_key:
  - product_number

Those attributes each carry a name and a data type. Where an attribute’s data comes from is settled later, in the source mappings. Not all attributes come from a source, though: some are defined by the business logic, derived from the concepts, and we come to that later.

A business key is one attribute or several, and a composite key comes with the delimiter that joins its parts. A concept can also carry more than one key: one is the primary business key, the rest are alternates, each with a label of its own. That product carries its product id as an alternate, shown as (ID:4711). The same concept declares it:

alternate_keys:
  - name: product_id
    attributes: [product_id]
    prefix: "(ID:"     # value 4711 becomes (ID:4711)
    suffix: ")"

Either key can be present or missing:

Alternates are not decoration. Some source systems never carry the business key and identify a record only by an alternate, so supporting alternates is what lets those sources be integrated at all. In the Data Vault they bring same-as-links into play, and the labels travel with them, ready for the presentation layer to tell one key from another.

Relationships

The conceptual layer records how concepts relate: a customer to its orders, a rental to its vehicle.

A relationship carries a name, so the same two concepts can relate in more than one way. An order points at an address twice, once as its billing address and once as its shipping address, two relationships to the same concept that only the names keep apart.

A concept can also point back at its own kind. An employee reports to another employee, and that self-reference is how a hierarchy is stated. We think it is worth letting the hierarchy’s levels be named as well, team lead, department head, division head. Then later SQL can address a level by name, which helps most in an unbalanced hierarchy where the depth varies from branch to branch.

Two concepts can also reference each other. An employee works in a department while the department is headed by an employee, and the conceptual layer allows that cycle rather than forbidding it.

Timelines

Timelines come in two kinds. Technical ones record what the pipeline did to the data: when it was extracted from the source, when it was loaded into the warehouse, the timestamp the source stamped on the record. Business ones carry meaning from the domain: the period a contract is valid, a price holds, a customer lived at an address. The conceptual layer states both, since a change can matter on either clock, the moment the data arrived or the moment the fact became true. A timeline attaches to a relationship as readily as to a concept, so the conceptual layer can say when a relationship held: which department an employee worked in, and from when to when.

A timeline is not always a period with a start and an end. Often it is a single timestamp, usually the start and sometimes the end, with the rest left implicit. When it is a period, the conceptual layer also says how to read its bounds, whether each end is open or closed: [start, end] includes both, [start, end) includes the start and excludes the end, and the open and closed cases in between change what the same two dates mean. Half-open bounds like [start, end) are the easiest to work with, since adjacent periods then meet without a gap or an overlap.

Left unstated, those conventions multiply, and every consumer downstream has to handle each one. Better to standardize the periods so they read the same way everywhere, and doing that asks one more thing of the conceptual layer: the clock tick, the smallest unit of time in play, a day or a second, so a boundary can be shifted by exactly one tick when a period is converted to the standard.

A concept can carry more than one business timeline, so each should be nameable, both to tell them apart and to reach them later. A named period, a contract’s term or a price’s window, can be referred to directly in the business logic, and it gives the dimensional model a timeline to build around.

Business logic and metrics

Business logic in a data warehouse covers calculated metrics, data quality checks, and, later on, the information and metrics presented as dimensions and facts.

To keep full flexibility in that logic while making knowledge of the Data Vault model unnecessary, we chose to let it be written, in the conceptual layer, as SQL against the concepts rather than the Data Vault tables. That SQL can draw on the named relationships and timelines as well, not the concepts alone.

No direct access to the Data Vault tables is needed. The derivation translates the SQL into hubs, links, and satellites, with the correct joins, fully on its own.

A rental’s charge and insurance, for instance, read from the rental and its vehicle:

select rt.rental_no,
       rt.days * v.daily_rate     as rental_charge,
       rt.days * v.insurance_rate as insurance
  from c9.concept.rental as rt
  join c9.concept.vehicle as v
 using (c9.relation.rental_vehicle)

The query joins the two concepts by the named rental_vehicle relationship and computes the figures from the rental’s days and the vehicle’s rates, with no hub, link, or satellite anywhere in it. Your data warehouse as code goes through the syntax in more detail.

Abstracting the Data Vault away is one of the things conflux9 is built on: the business logic never has to know it is there.

Source and source mappings

For simplicity, assume the source data already sits in a landing zone on the target system. A source is then given by its location, a table name, and its fields, which are the column names and their data types. The mapping ties those columns to the concept: which one carries which attribute, and which supplies the business key.

A concept’s data can come from more than one source. When several of them carry the same attribute, the integration layer keeps the original value from each source, in the Raw Vault, and a consolidated value in the Business Vault. If the conceptual layer states how the sources are prioritized, a typical consolidation for the attribute follows automatically. For a date of birth carried by the CRM, the billing system, and the shop, that is coalesce(crm.date_of_birth, billing.date_of_birth, shop.date_of_birth): the CRM’s value first, the billing system’s where that is missing, the shop’s last.

Three sources feed the customer concept, each with a default precedence:

# sources/crm/customers.yaml
kind: source
name: customers
priority: 1
location: crm.customers
columns:
  - name: customer_id
    type: integer
  - name: full_name
    type: varchar(200)
  - name: email
    type: varchar(200)
  - name: date_of_birth
    type: date

# sources/billing/customers.yaml
kind: source
name: customers
priority: 2
location: billing.customers
columns:
  - name: customer_id
    type: integer
  - name: email
    type: varchar(200)
  - name: date_of_birth
    type: date

# sources/shop/customers.yaml
kind: source
name: customers
priority: 3
location: shop.customers
columns:
  - name: customer_id
    type: integer
  - name: full_name
    type: varchar(200)
  - name: date_of_birth
    type: date

The concept lists its attributes, and one of them overrides that order:

# concepts/customer/customer.yaml
kind: concept
name: customer

attributes:
  - name: customer_id
    type: integer
  - name: full_name
    type: varchar(200)
  - name: email
    type: varchar(200)
    sources:
      - billing.customers
      - crm.customers
  - name: date_of_birth
    type: date

business_key:
  - customer_id

Without a sources: list, an attribute follows the source precedence, so date_of_birth resolves crm, then billing, then shop, the coalesce above. email is the exception: its own sources: list puts billing ahead of crm, because billing holds the current one.

In the business logic, the consolidated value is there to read directly. For a rule of its own, the SQL can also reach each source’s value of the attribute, which is what lets custom consolidation logic be defined.

Sources rarely agree on how they signal the rest, a deletion, a change since the last load, the shape of a timestamp. The mapping normalizes each with a SQL expression, so the derivation reads one format whatever the source sent. The technical columns among them, the load or extraction timestamps, are marked as such in the source, so the mapping knows them for what they are.

The pattern principle

We said the derivation works by recognizing patterns in the conceptual layer, and that recognition is the part that counts. Once a pattern is recognized, translating it into Data Vault is the derivation’s own job, carried out automatically. So the Data Vault follows the specifications in the conceptual layer.

Several of the constructs already were patterns, though we did not call them that. An alternate business key is one, and we saw where it leads, to same-as-links. An attribute fed by more than one source is another, the one that keeps each source’s value and a consolidated one as well. In both, the derivation recognized the pattern and produced the Data Vault structures, with nothing left to decide.

Patterns are not all of one kind. Some concern how a business concept is identified and where its data comes from. Alternate keys and multiple sources are of that kind, close to the mechanics of integration. Others concern how the business makes sense of things, how it groups and names them. A classification is one of these: the business sorts a concept into a general type and more specific ones, and each can carry attributes of its own. Every party has a name and an address. A person adds a date of birth, an organization a registration number. The derivation recognizes it as a pattern like the rest and builds what it calls for, several Data Vault structures at once rather than one.

These are examples, not the complete catalog. The derivation recognizes the patterns and translates them deterministically. What matters is not the list but the mechanism. Describe the business in the conceptual layer, and the patterns in that description are what the Data Vault is built from.