Patterned Modeling, Human Judgment

Introduction

#

The best AI-assisted modeling work happens where the pattern is strong and the judgment boundary is clear. Data Vault 2.0 is a useful example here because hubs, links, satellites, and effective satellites are repeatable enough for an agent to scaffold, while the decisions that make the model correct still belong to the human.

That is the point of this chapter. It is not here to prove that I know a modeling methodology. It is here because patterned data modeling exposes the exact division of labor this book keeps returning to. The agent can multiply a pattern across a repository. It cannot decide what one row means.

Patterns Are Agent Fuel

#

An agent is strongest when the repository already contains a clear pattern. Give it one well-formed hub, link, or satellite, and it can usually produce the next one in the same local style. It can copy naming conventions, hash-key structure, load metadata, source references, tests, descriptions, and documentation blocks. That is useful because the repetitive part of modeling is real work, and it is easy to make small inconsistent mistakes when typing it by hand.

Data Vault makes this leverage unusually visible. A hub stores a business key. A link stores a relationship. A satellite stores descriptive attributes and change history. Once those patterns are established in a project, the agent can scaffold them quickly and consistently. It can also update paired files, such as SQL and YAML, without losing track of which column was added where.

That consistency is not a small benefit. In a warehouse with many similar objects, human attention should be spent on the handful of decisions that change meaning, not on retyping boilerplate. The agent is good at the boilerplate. I want it doing that work, provided the pattern it is copying is actually the pattern I want.

Grain Is Not a Template

#

The danger is that a plausible pattern can hide a wrong grain. Grain is the contract of a data model. It says what one row means, and every downstream metric, dashboard, application read, and test relies on that meaning. If the grain is wrong, clean syntax and consistent naming do not save the model.

This is where human judgment has to stay in front. The agent can suggest a business key, but it cannot own whether that key is truly stable. It can propose a link, but it cannot own whether the relationship is really one row per business event, one row per current state, or one row per association over time. It can generate a satellite, but it cannot know which attributes change together in a way the business actually recognizes unless the repository and the prompt give it that context.

I use the agent as a modeling assistant, not as the modeler of record. That means I ask it to draft, then I review the grain out loud in plain language. One row in this hub means one unique customer. One row in this link means one customer assigned to one account during one effective interval. One row in this satellite means one version of the descriptive attributes for that relationship. If I cannot say the sentence clearly, the model is not ready for the agent to multiply.

The Optional Relationship Trap

#

Given that I am using Data Vault 2.0 as the case study, the sharpest example I have encountered where human judgment is necessary is an optional relationship with an effective satellite. It sounds narrow, but it is exactly the kind of case that separates pattern copying from modeling judgment. The relationship exists for some driving rows and not for others, and the effective satellite records when that relationship is active. If I join the optional link, hub, and effective satellite as separate flat tables beside the driving relationship, the query can multiply rows or blur attributes across intervals.

That bug is dangerous because the SQL can look ordinary. The joins are valid. The columns resolve. The output returns. The problem is not syntax. The problem is that the optional path changed what one row means. An agent that has not been given the grain rule will happily produce the flat join because it resembles thousands of normal joins it has seen before.

The safer pattern is to resolve the optional path before it reaches the outer query.

with optional_scope as (
    select
        link_scope.scope_hk,
        hub_scope.scope_bk
    from link_scope
    left join hub_scope
        on link_scope.scope_hk = hub_scope.scope_hk
    inner join eff_sat_scope
        on link_scope.link_scope_hk = eff_sat_scope.link_scope_hk
        and eff_sat_scope.load_end_ts = '9999-12-31'
)
select
    hub_case.case_bk as case_id,
    sat_case.status_code,
    optional_scope.scope_bk as optional_scope_id
from link_case
inner join hub_case
    on link_case.case_hk = hub_case.case_hk
inner join sat_case
    on hub_case.case_hk = sat_case.case_hk
    and sat_case.load_end_ts = '9999-12-31'
left join optional_scope
    on link_case.scope_hk = optional_scope.scope_hk

The important part is the pattern. The optional side is resolved to one current row per key inside the CTE, then the outer query left joins that already-resolved set. The optional match can add columns, but it should not add rows. Once the pattern is named, the agent can reuse it. Before the pattern is named, the agent is a risk.

Make the Agent Prove the Shape

#

The solution is not to stop using the agent on modeling work. The solution is to require proof at the same level as the risk. When the agent drafts a model with optional relationships, I want it to write the validation query too. Count the distinct driving keys before the join. Count them after the join. Show whether empty optional data preserves the same count. Show whether populated optional data adds attributes without adding rows.

That proof should sit beside the generated model, not arrive as an afterthought. The agent can create a small slice query for one day, one cycle, or one known identifier. It can compare the naive flat join against the resolved CTE pattern. It can turn the grain rule into a dbt test or an analysis query. What it cannot do is decide that a count mismatch is acceptable because it wants the task to be finished.

This is the healthy version of AI-assisted modeling. The agent scaffolds the pattern, applies the CTE pattern consistently, updates the paired documentation, and writes the checks that prove the grain did not move. I provide the business meaning, the grain sentence, and the willingness to reject a plausible answer when the proof fails.

Putting It Into Practice

#

1. Use agents where the repository has a clear, repeated modeling pattern for them to follow.
2. Let the agent scaffold hubs, links, satellites, tests, and documentation once the grain decision is made.
3. State the grain in plain language before allowing the agent to multiply a pattern.
4. Treat optional relationships with effective satellites as grain traps until proven otherwise.
5. Resolve optional effective paths inside a CTE before joining them to the driving query.
6. Require distinct driving-key counts before and after the join, and make the agent write that proof.
7. Keep the agent on patterned execution and keep human judgment on what one row means.