Some gaps a mapping cannot close. The model says Customer; the source offers three tables, a join nobody ever recorded, and not a foreign key in sight.

This is the moment projects reach for paint: a diagram that claims the source fits the model, wishful documentation stretched over a structural hole. Part one of this series, Stop Asking Data Engineers to Paint, made the model real metadata, and part two made the mapping real. This last post is about the holes, and how the bridge across them gets built rather than painted.

How DeltaVault recovers the foreign keys the source never declared

Plenty of source systems never declared a foreign key. Flat file drops land that way, and so does any database whose constraints came off for load performance years ago and never went back on. To find relationships between tables without foreign keys by hand, you read column names and guess at parents for hours.

DeltaVault proposes them instead. Foreign key discovery starts with a deterministic pre-filter that rules out table pairs that cannot be parent and child on structural grounds, and it works before your key design does: on a freshly imported schema it falls back to key-shaped columns, a column named id, names ending in id, endings like code, key, number, and reference. The AI then weighs the survivors using names, types, and the profiling signals your catalog already holds: value distributions, cardinality, how far sample data overlaps between two columns.

Every candidate comes back with a confidence score, the two columns behind it, and a short written reason, drawn as a dashed edge on the canvas. Some will be wrong, and the interface assumes it: you read the reason, judge the score, and dismiss the bad ones in a single read. Accepting is the only write, it lands on your branch, and accepted relationships are promoted forward to the staging layers, each layer previewed before it lands.

Watch the relationship pass in action:

Then, build the view that holds the model’s shape

With the joins recovered, the bridge itself is a view.

  1. Select the tables on the canvas, and DeltaVault works out the joins for you, temporal overlap on effective-dated ranges included, every join editable when the suggestion needs a human correction.
  2. Pick the columns the business shape needs, then rename and reorder them.
  3. Add computed columns as SQL expressions.

The full CREATE VIEW statement builds live in front of you: what you save is what runs.

Or skip the assembly and describe it. Ask AI on the canvas takes a plain-language request for a view, resolves the table names against your catalog rather than inventing them, and parks the result as a proposal. Accepting opens the view designer pre-populated; the AI writes nothing, and the view exists only when you save it.

The translation is never lost again

Here is what makes the bridge trustworthy rather than one more artifact to doubt. Every projected column records exactly which source columns feed it, computed expressions included, so the source to target mapping is part of the view itself: DeltaVault resolves each expression back to the columns it reads, and even calculated fields carry column-level lineage. Click a column and its whole path lights up, every ancestor and every descendant, across as many hops as the pipeline has. That data lineage is not a report generated after the fact; it is part of the metadata, committed alongside everything else.

Selecting a built object opens its detail pane, listing the source tables behind it and the business key columns that identify it

Select any object on the canvas and the same panel answers the same question: which source tables stand behind this, and which columns identify it.

That was the whole argument of this series. The conceptual model was never the problem, and neither were the engineers who could not make sources match it by force of will. The problem was the medium: a painting on one side, a warehouse on the other, and the translation between them living in nobody’s system. Make the model metadata, record the mapping, build the views, and keep the lineage. The bridge gets built. Nobody has to paint.

Two chapters of The Data Lakehouse for Everyone carry that further. Chapter 5 takes one mapping decision apart and asks who owns it: The mapping is the deliverable. Chapter 9 argues that the only lineage you will ever have is the lineage written at creation, because nobody funds the project to reconstruct it later: Lineage, governance, and trust before go-live.