Menu
Design and deploy Business Vault constructs — BV links and BV satellites. Covers delivery modes, rule views, staging, and doctrine rules.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
Based on SOC occupation classification
| name | dv-bv |
| description | Design and deploy Business Vault constructs — BV links and BV satellites. Covers delivery modes, rule views, staging, and doctrine rules. |
| enabled | true |
Business Vault (BV) constructs sit above the Raw Vault. They capture derived relationships and enriched attributes produced by business rules — not raw source data. All BV constructs are INSERT-only, same as Raw Vault.
The one-line routing decision: where does the business rule engine live?
| Rule engine location | Vault layer | Rationale |
|---|---|---|
| Source-system application (ERP, CRM, SaaS, custom app) | Raw Vault | The application automates the business rule; the vault records the outcome as raw data. The source is the authority. |
| Analytics platform (SQL, Python, Spark, ML model running inside the data warehouse) | Business Vault | The analytics team implements and owns the rule; the outcome is a derived business insight that needs the same auditability as raw data. |
If a result comes from a source application → it's raw vault, regardless of how complex the logic inside the source is. If a result is computed inside your analytics platform → it's business vault, regardless of how simple the calculation is.
This distinction is the boundary between RV and BV. "Where was the rule executed?" is the only question you need to answer.
A BV link captures a relationship that does not exist in any source system directly — it is derived by a business rule (e.g. an account-to-product link inferred from transaction history, or a hierarchy link derived from a rules engine).
BV links are needed in three situations:
Why BV links matter: solve UoW complexity once for all users
One of the core jobs of a data vault is: solve unit-of-work complexity once for all users. Without a BV link, every analyst and IM view must independently resolve the gap between the source's UoW and the business's UoW. With a BV link, that complexity is solved in one place — the BV rule view — and all downstream consumers query from the single approved result.
Deterministic anchor for surrogate key derivation
When the business needs a stable identifier that the source doesn't supply (e.g. an account number derived from a card lineage):
The first record in a lineage (grandparent) can be identified using a recursive CTE. Snowflake supports recursive CTEs natively. Note: Apache Spark SQL and HiveSQL do NOT support recursive CTEs (at time of writing) — if BV business rules must run on those platforms, the lineage traversal requires an iterative alternative.
links:
- name: <link_name>
vault_layer: bv # marks this as a Business Vault link
source_name: bv_<concept_name> # must match the BV rule view name
participants:
- hub: <hub_a>
business_key_column: <bk_col_a>
role: <role_a> # required when same hub appears more than once
- hub: <hub_b>
business_key_column: <bk_col_b>
role: <role_b>
depends_on_sources: # declare RV source dependencies for scheduler
- <rv_source_name>
Naming: DVOS generates table lnk_bv_{name} for a BV link.
stg_bv_{concept_name} — computes all hub hashkeys and composite link hashkey from the rule viewload__lnk_bv_{name} — INSERT-only, anti-semi joinThe BV rule view (bv_{concept_name}) in the BV staging schema. It must output:
business_key_column)dv_applied_timestamp — derived from contributing RV satellite timestamps (GREATEST of sources)dv_hashkey_* columns — DVOS computes these in stagingdv_hashdiff_* columnsA BV link can assign a derived key to simplify multi-hop raw vault traversals. Example: an account hierarchy requires recursive CTE traversal in the RV (card → account → parent account). The BV link pre-resolves the hierarchy and stores the final "account number" directly:
dv_recordsource contains the business rule name + version (e.g. 'BV_RULE:account_lineage:v2')Exploration links: BV links can include speculative "exploration links" in an isolated schema for testing hypothetical relationships. These are exempt from audit and governance requirements until validated and promoted to the governed BV schema.
A BV satellite stores attributes derived from business rules over Raw Vault data. Structure and loading are identical to a Raw Vault satellite — INSERT-only, anti-semi join on dv_hashdiff.
| Mode | When to use | Landing table? | Execution unit? |
|---|---|---|---|
landed | Business rule output is a physical ODS table | Yes | Yes |
view | Business rule is a SQL view over RV (no materialisation) | No | No |
business_satellites:
- concept_name: <concept_name>
mode: landed
parent: <hub_or_link_name>
parent_type: hub # or lnk
source_badge: bv
source_file: <concept_name> # matches the landing table name
depends_on_sources:
- <rv_source_name>
attributes:
- <attr1>
- <attr2>
Naming: DVOS generates table sat_bv_{concept_name}.
business_satellites:
- concept_name: <concept_name>
mode: view
parent: <hub_or_link_name>
parent_type: hub
attributes:
- <attr1>
- <attr2>
Virtual BV satellites do not produce execution units. The business rule runs as a SQL view. No landing table is needed.
Raw Vault satellites
│
▼
bv_{concept_name} ← you write this (SQL view or proc output)
(business rule view) outputs: BKs + dv_applied_timestamp only
│
▼
stg_bv_{concept_name} ← DVOS generates this
(BV staging view) adds: hashkeys, hashdiff, dv_recordsource,
dv_tenant_id, dv_collisioncode, dv_load_timestamp
│
▼
sat_bv_{concept_name} ← DVOS generates INSERT-only loader
(BV satellite table)
Business Vault artefacts implement soft rules — transformations, derivations, and calculations applied after raw data has been loaded into the vault. Every piece of BV logic must satisfy these six principles:
Domain owned, defined once, public — BV logic is owned by the domain that produces it. Once defined, it is shared and made discoverable across all consumer domains. It is not reproduced in each consumer's private layer.
Idempotent — executing the same BV rule multiple times against the same input data with the same parameters must produce the same output, every time. Idempotence enables safe replay after orchestration failures, rule version changes, or dataset rebuilds.
Versioned — every change to a BV rule is tracked. The version change is recorded in the dv_recordsource metadata column of the BV satellite row that results from the new rule version. This means the vault carries a complete audit trail of which version of the rule produced each row.
Recommended dv_recordsource format for BV: bv_<rule_name>_v<version> — for example bv_relationship_quality_v1.0, bv_account_lifecycle_v2.3. This makes every BV row traceable to the exact named rule and version that produced it, enabling both forward (what did this rule produce?) and backward (who produced this row?) tracing. When a business rule changes, increment the version in the recordsource — older rows retain the old version string, new rows carry the new one.
Iterative — BV logic will evolve. The structure (satellite DDL) is designed to be stable while the rule code (the bv_ view) changes independently. See /dv-bv Key rules: physicalise BV satellites so rule code can evolve from a point in time without hardcoded dates.
Autonomous — BV logic units should have minimal dependencies on each other. Avoid aggregations on aggregations — chaining BV satellites introduces compounding complexity and change management risk. If BV satellite B depends on BV satellite A, a change in A cascades to B and all of B's consumers. Design each BV unit to operate as close to the raw vault as possible.
Maximise reuse — BV logic closer to source has greater reuse potential. A rule defined over the raw vault can serve all consumer domains. A rule defined within a consumer's private layer serves only that consumer. Always define shared logic in SAL/CAL before replicating it in consumer BALs.
Soft rules only — BV is for soft business rules (derivations, aggregations, entity resolution, calculated attributes). Hard rules (data quality thresholds, PII tagging, schema validation, BK identification) belong in the curated zone before data reaches staging. See
/dv-stagefor the hard rule boundary.
Business Rule Register
Every BV rule should be tracked in a business rule register — a governance artifact that makes rules discoverable, auditable, and owned. Minimum required fields:
| Field | Required | Purpose |
|---|---|---|
| Code | Yes | Internal rule code (matches the bv_<rule_name> in dv_recordsource) |
| Business name | Yes | Human-readable rule name as it appears in the record source column |
| Description | Yes | What the rule does, dependencies, and which business processes use it |
| Owner | Yes | Data steward or role with direct responsibility for the rule |
| Category / domain | Yes | Ties to the business ontology; flags whether the rule exists for regulatory compliance |
| Implementation | No | JIRA ticket, code location, flow diagram, test results |
| Classification | No | Rule type: Term, Fact, Compute, Trigger, Validate |
| Tags | No | Search keywords sourced from the business glossary — enables discovery in data governance tooling |
The register is the authoritative catalogue of what soft rules exist, who owns them, and what they produce. It is not a code repository — it is a governance document that makes rule audit and impact analysis possible without reading SQL.
| Rule | Severity | Description |
|---|---|---|
| DV-BV-100 | WARNING | BV satellite must declare mode: landed or mode: view |
| DV-BV-101 | ERROR | Landed BV satellite must declare source_badge and source_file |
| DV-BV-102 | WARNING | Virtual BV satellite must not produce execution units |
| DV-BV-103 | WARNING | Landed BV satellite should declare depends_on_sources |
| DV-BV-110 | ERROR | BV staging and loaders must not contain UPDATE/DELETE/TRUNCATE. Exception: links (standard, BV, SA, HY) MAY use MERGE with last_seen_date update if explicitly declared in manifest (last_seen: true). Default is INSERT-only (anti-semi-join). |
| DV-BV-111 | ERROR | dv_applied_timestamp must NOT use CURRENT_TIMESTAMP() — derive from RV sources |
| DV-BV-120 | ERROR | No BV hubs — a Business Vault does not contain hub tables. A BV hub would imply the business key was derived inside the analytics platform, which is incorrect. Business keys are manufactured exclusively by source systems and applications. If a derived identifier is needed, it is a surrogate computed in a BV satellite or BV link — not a hub. |
BV constructs are INSERT-only — same immutability doctrine as Raw Vault
dv_applied_timestamp must be derived from contributing RV data (GREATEST of source timestamps), never CURRENT_TIMESTAMP. Why GREATEST: if you take a PIT/SNOPIT snapshot at time T, you need the BV satellite to have a record for time T that is derived from the RV data at time T. Using GREATEST ensures the BV record is anchored to the same temporal frame as the RV records it was derived from. Without this, PIT/SNOPIT snapshots of RV and BV will be misaligned.
The business rule view (bv_{concept_name}) must output business keys only — DVOS adds all hashkeys and hashdiff in staging
depends_on_sources controls scheduler dependency on RV load units — always declare it for correct execution ordering
BV links with role-playing participants must declare role per participant
BV satellites are never copies of RV satellites — even for the purpose of column renaming. If a downstream consumer needs different column names, create a SQL VIEW over the RV satellite. Copying a satellite to rename columns introduces redundant storage and a second load that must stay in sync.
BV and RV share the same database and schema — the Business Vault is not a separate physical area. Raw Vault and Business Vault artefacts coexist in the same schema and are distinguished by table name convention only (SAT_RV_ vs SAT_BV_, LNK_ vs LNK_BV_). Creating a separate schema or database for BV is an anti-pattern — it severs the logical connection between raw and derived content and introduces unnecessary promotion pipelines.
Physicalise BV satellites — separate the business rule code (the bv_ view) from the satellite structure. This allows the business rule to evolve from a specific point in time without hardcoded dates in the rule code. The satellite structure remains stable; the rule view changes independently.
Why this matters — code vs. data are fundamentally different:
A manipulation of past data using today's updated rule code is misinformation — the old data was correct under the rule that existed at the time. Physicalising BV satellites preserves this: the dv_recordsource column records which rule version produced each row, making every result traceable to the code version that was current when that row was inserted. If rule code changes but produces no true-change output, the satellite correctly records no change.
Do not stack BV views — deploying chains of BV logic as stacked SQL views (view A depends on view B which depends on view C) creates a fragile dependency graph. Any change to a lower-level view immediately cascades to all dependent views, creating unexpected downstream impacts that are difficult to test and control. The correct approach: physicalise intermediate BV outcomes as BV satellite tables. Each table is a stable interface; the rule view feeding it can change independently.
BV satellites may be multi-sourced — they can consolidate, combine, or rationalise data from multiple RV satellites or even multiple BV artefacts. This is intentional and correct; BV is the integration and enrichment layer. Raw vault satellites remain single-sourced.
Why raw vault satellites must be single-sourced — schema drift and grain safety: If multiple sources are merged into one raw vault satellite and one source experiences schema drift (a column is added, removed, or renamed), the mapping must be updated for all sources simultaneously — or the satellite grain no longer matches across sources. Worse: if two sources supply a column with the same name but different semantic interpretations, the satellite becomes ambiguous. Once data is mixed at the raw vault level, it cannot be unmixed without a reload. Raw vault is sold as never requiring refactoring — merging sources into one raw satellite inevitably breaks this guarantee. Each source gets its own raw vault satellite; BV satellites are where integration and merging legitimately occur.
Shared BV vs. private BV — BV artefacts default to shared: defined once, domain-owned, published and discoverable across all consumer domains (see Business logic principle 1). However, a private BV is a legitimate pattern when:
A private BV artefact is managed entirely within the owning domain — its own naming, its own governance, its own lifecycle. The trade-off is that reusability is sacrificed for domain autonomy. When the same private BV artefact is independently built by multiple domains, that is a signal to promote it to a shared BV artefact under centralised ownership.
The Business Vault has several well-recognised use case categories. Each produces auditable, insert-only BV satellites (and/or BV links) with the same guarantees as the Raw Vault.
| Use case category | What it solves | Output artefact |
|---|---|---|
| Derived relationship | Relationship not in source, or source UoW ≠ business UoW | BV link |
| Entity resolution | Two sources use different keys for the same real-world entity | BV link + BV satellite (or SAL in RV) |
| Entity lifecycle | Source never sends explicit deletion events — vault must infer aging/death | BV satellite (status: M/D/R) |
| Derived attributes | Business-agreed calculations or derivations that need an audit trail | BV satellite |
| Data quality / Business Quality Vault | DQ rule results persisted for downstream BI and SLA reporting | BV satellite |
| Data preparation / Feature engineering | ML-ready attributes prepared once, persisted with full auditability | BV satellite |
| Write-back | Insights or derived outputs from the semantic/analytics layer fed back into the vault | BV satellite (see below) |
When insights or derived outputs from the semantic layer or downstream analytics need to flow back into the data platform, two named patterns apply:
Pattern 1 — Via source application (→ Raw Vault)
The derived insight is fed back into the source operational system (e.g. a recommendation engine updates a CRM record). The source system then processes it and delivers the updated record to the vault in the normal ingestion flow. The vault receives it as a standard source delivery and models it into Raw Vault. The source application is the authority; the vault records what the source says.
dv_recordsourcePattern 2 — As landed content (→ Business Vault)
The derived insight bypasses the source application entirely and lands directly in the analytics platform (e.g. ML model output scores, semantic-layer-derived aggregations, dashboard-derived metrics). Because it is not passing through an operational source, it is modelled as Business Vault artefacts — derived content that completes or extends the raw vault.
dv_recordsource = bv_<rule_name>_v<version>)Doctrine: the routing decision is simple — if it went through an operational source system, it's RV. If it came directly from analytics/ML without a source system intermediary, it's BV.
Pattern 3 — Knowledge graph inference (→ Business Vault link)
When a knowledge graph infers a relationship not directly observable in any source system (e.g. "customer A knows customer B" derived through householding analysis, co-transaction co-occurrence, or network graph inference), that inferred relationship can be persisted back into the vault as a Business Vault link table.
Why BV link (not BV satellite)?
dv_recordsource = 'bv_kg_<rule>_v<version>'dv_applied_timestamp historises the inference: if the KG rule is re-run with updated data, the new inference carries a new applied timestamp, and both old and new inferences coexist for auditBenefit: the vault provides the temporal dimension that knowledge graphs typically lack. By persisting KG inferences as BV links, the inferred knowledge graph can be queried across time — enabling analysis of how relationships evolved as the underlying data and model changed.
Data scientists traditionally spend most of their time cleaning and organising data. If that preparation work is persisted in the BV it becomes auditable, reusable, and available to all consumers — not just the data scientist who originally built it.
Shareable vs. non-shareable features — the BV boundary
Not all feature engineering belongs in Business Vault. The deciding test: "Would another ML model or analytical consumer benefit from this feature?"
| Feature category | BV? | Examples |
|---|---|---|
| Shareable / reusable | Yes | Time-windowed aggregations (rolling counts, min/max, avg, stddev), derived metrics, normalised attributes consistent across use cases |
| Non-shareable / model-specific | No | Imputation, outlier treatment, model-specific one-hot encoding, binning tuned for a specific algorithm — apply at training pipeline time |
The non-shareable transformations have point-in-time state tied to a specific model's input requirements. Storing them in BV creates satellite columns that no other consumer will use, inflates the satellite schema, and may even mislead other consumers.
Common feature engineering techniques that are appropriate BV patterns:
| Technique | Description |
|---|---|
| Grouping / aggregation | Rolling counts, min/max, avg, stddev over time windows — reusable across models |
| Log transform | Normalising skewed distributions to reduce outlier impact — if consistent across use cases |
| Feature splits | Combining or splitting attribute columns into analytically useful groupings |
| Scaling | Normalising or standardising attribute ranges — if the scaling is common across consumers |
| Date extraction | Extracting year, month, day, week, quarter as separate columns for time-based features |
| One-hot encoding | Only if the encoding is consistent and reusable across models — otherwise apply at training time |
Doctrine note: Each prepared feature set should be stored as a separate BV satellite on the relevant hub or link — not all features in one wide table. This preserves the separation-of-concern and rate-of-change isolation that the DV pattern depends on.
Feature store support — offline and online
The vault's insert-only audit history makes it a natural foundation for both types of feature store:
| Feature store type | Description | Vault source |
|---|---|---|
| Offline feature store | Historical feature values used for model training and backtesting | Full satellite history: SELECT * FROM SAT_BV_CUSTOMER_FEATURES (all rows, ordered by dv_applied_timestamp) |
| Online feature store | Low-latency current feature values for model serving / real-time inference | Current-state view: QUALIFY ROW_NUMBER() = 1 over BV satellite |
Versioning feature labels when algorithms change
When a ML model algorithm is improved or a feature value definition changes, the new feature values must coexist with the old values in the audit trail. The BV satellite's insert-only, versioned loading pattern handles this automatically:
dv_recordsource = 'bv_churn_model_v1.0'dv_recordsource = 'bv_churn_model_v2.0'dv_recordsource column distinguishes themThis means the vault carries a complete audit trail of which version of the feature engineering logic produced each row — enabling model comparison, rollback analysis, and regulatory explainability without any data loss.
Backfilling BV features
Raw vault can be used to backfill BV feature history when ML model training requires more historical data than the BV satellite currently holds. Three rules for a correct backfill:
dv_recordsource must indicate the backfill origin — e.g. bv_churn_model_v1.0_backfill — so the reload is traceable and distinguishable from normal loadsdv_applied_timestamp must match the source raw vault applied timestamp — the historical business context is preserved accuratelydv_load_timestamp is the backfill execution timestamp — it documents when the one-time operation occurred without altering the business timelineML model drift monitoring
Two named phenomena cause ML model performance to degrade over time. Store model predictions in a BV satellite per entity to support ongoing drift analysis:
| Phenomenon | Definition | BV monitoring approach |
|---|---|---|
| Concept drift | Statistical properties of the target variable (prediction) change in unforeseen ways | Compare prediction distribution over time against training baseline |
| Data drift | Statistical properties of the predictor variable (model input) change | Track feature value distributions in BV satellite over time |
Both types are detected by comparing current BV satellite values against the historical baseline used to train the model. The vault's insert-only history makes this comparison straightforward without requiring separate monitoring infrastructure.
Snowflake Cortex AI functions as named BV feature sources
When running on Snowflake, the following native Cortex AI functions produce structured outputs that are well-suited for BV satellite attributes. Each maps to a specific BV feature engineering use case:
| Cortex function | BV satellite use case | Notes |
|---|---|---|
SNOWFLAKE.CORTEX.SENTIMENT(text) | Customer communication sentiment score per entity | Returns a score between -1 (negative) and 1 (positive) |
SNOWFLAKE.CORTEX.CLASSIFY_TEXT(text, labels) | Document or event classification | Returns a label from the provided list |
SNOWFLAKE.CORTEX.SUMMARIZE(text) | Condensed narrative attribute for large text fields | Useful for long-form satellite attributes |
SNOWFLAKE.CORTEX.EXTRACT_ANSWER(question, context) | Structured extraction from unstructured text | Extracts a specific field value from a document column |
SNOWFLAKE.CORTEX.TRANSLATE(text, source_lang, target_lang) | Normalised language dimension for multi-language content | Standardise language in satellite text attributes |
SNOWFLAKE.ML.FORECAST(...) | Predicted metric values (time-series) | Persisted as auditable BV attributes per entity |
SNOWFLAKE.ML.DETECT_ANOMALIES(...) | Anomaly flag/score per entity or time series | Attach anomaly flag to relevant hub or link satellite |
All of the above follow the standard BV write-back pattern (Pattern 2 — landed content → BV satellite) — the Cortex function runs over vault data, the output is treated as a derived source, and the result loads into a physicalised BV satellite with the standard DV metadata columns.
"Humans in the loop" — quality rule for AI-produced BV content
When a BV satellite contains AI or LLM-generated outputs, the standard BV quality guarantees (idempotent, versioned, auditable via dv_recordsource) are necessary but not sufficient. LLMs and AI models are vulnerable to hallucinations (confidently incorrect outputs) and model drift. Two additional governance rules apply:
confidence_score (float) or source_reference (VARCHAR) column that records the basis for the derived value. This enables downstream consumers to filter on confidence and gives the SME review process a reference point.dv_recordsource pattern is promoted to shared/enterprise status. Mark experimental AI BV artefacts with a private BV designation until their accuracy is validated across enough iterations to meet the agreed SLA.The entity aging rule is one of the most common Business Vault use cases. It detects entities (hubs) or relationships (links) that have stopped appearing in source feeds and progressively marks them as missing, then deceased.
Create a BV satellite to record the lifecycle status of each entity:
-- BV satellite: entity lifecycle status
-- sat_bv_hub_{entity}_lifecycle
-- mode: landed
-- dv_applied_timestamp = CURRENT_TIMESTAMP() is acceptable here (the rule runs on schedule,
-- not from source data — this is the one legitimate use of CURRENT_TIMESTAMP in BV)
CREATE OR REPLACE VIEW bv_{entity}_lifecycle AS
SELECT
h.dv_hashkey_hub_{entity},
h.{bk_column},
CASE
WHEN h.last_seen_date IS NULL THEN 'UNKNOWN'
WHEN DATEDIFF('day', h.last_seen_date, CURRENT_TIMESTAMP()) < 30 THEN 'ACTIVE'
WHEN DATEDIFF('day', h.last_seen_date, CURRENT_TIMESTAMP()) < 60 THEN 'MISSING'
ELSE 'DECEASED'
END AS lifecycle_status,
h.last_seen_date,
CURRENT_TIMESTAMP() AS dv_applied_timestamp
FROM LIB_PRD01_EDW.SAL.HUB_{ENTITY} h
WHERE h.dv_recordsource != 'GHOST';
DVOS generates staging (stg_bv_{entity}_lifecycle) and the INSERT-only satellite loader from this rule view.
| Threshold | Status | Meaning |
|---|---|---|
| Seen in last 30 days | ACTIVE | Currently present in source |
| Not seen 30\u201360 days | MISSING | Absent; may be transient |
| Not seen 60+ days | DECEASED | Declared dead in absentia — upon business agreement |
| Reappears after DECEASED | REANIMATED | Investigate: data quality issue or legitimate reactivation |
The ~60-day threshold for DECEASED is a starting convention. Confirm the appropriate threshold with the business before implementation — some industries require shorter or longer periods.
If a DECEASED entity reappears in a source extract, the vault will insert a new hub row (the hub BK already exists; MERGE updates last_seen_date). The BV rule detects the reappearance and inserts a REANIMATED row into the lifecycle satellite. Flag this for business investigation — it may indicate a data quality issue, or it may be legitimate (a customer account reactivated after dormancy).
For GDPR-erased entities, a Record Tracking Satellite (RTS) is the guard: configure it to detect the reappearing BK and block insertion rather than recording it as REANIMATED.
If the BV satellite models a stream of entity events following the Activity Schema 2.0 pattern — rows with activity_id, activity, feature_json, revenue_impact, link — use /dv-bv-activity-schema instead of this skill.
/dv-bv-activity-schema handles the full pipeline:
stg_bv_{entity}_activity) — maps event codes to activity names, projects authoritative feature_json via OBJECT_CONSTRUCTSAT_BV_NH_{ENTITY}_STREAM DDL (standardised Activity Schema column set)dt_{entity}_stream_{activity})/dv-mart for relationship DTs and ASOF JOIN dim enrichmentNot all BV constructs need to be persisted tables. Use this escalation path:
| Stage | Form | When |
|---|---|---|
| 1 | IM views referencing RV directly | Simple derivations; < 5 contributing satellites; query latency acceptable |
| 2 | IM views + PIT/Bridge | Multiple satellites; timeline consolidation needed; query latency acceptable with pre-joined assistance |
| 3 | Persisted BV tables | Complex rules; rule versioning needed; multiple downstream consumers; reprocessing from RV is expensive |
Arguments for physical BV (tables):
dv_applied_timestampdv_recordsource; views cannot versionArguments for virtual BV (views):
Rule of thumb: Start with IM views. As they become larger or rule complexity grows, escalate to PIT/Bridge assistance. If the rule needs versioning, or multiple consumers depend on the same derived result, persist as a BV table.
A SQL pattern that detects timeline gaps in source-supplied start/end date ranges. Useful for data quality monitoring of insurance policies, contracts, subscriptions — any entity with expected continuous coverage.
-- Detect gaps in policy coverage timeline
CREATE OR REPLACE VIEW bv_policy_gap_detection AS
SELECT
dv_hashkey_hub_policy,
dv_applied_timestamp,
coverage_start_date,
coverage_end_date,
LAG(coverage_end_date) OVER (
PARTITION BY dv_hashkey_hub_policy ORDER BY coverage_start_date
) AS prev_end_date,
DATEDIFF('day', prev_end_date, coverage_start_date) AS gap_days
FROM SAT_RV_HUB_POLICY_COVERAGE
WHERE gap_days > 1; -- gap detected
Why BV satellite, not IM view: Implementing this as a persisted BV satellite (not just an IM view) inherits full audit trail. The gap detection result is historised — you can answer "was this gap known at date X?" which is impossible with a view.
Persist the gap detection output as SAT_BV_POLICY_GAPS with the gap metadata (start, end, gap_days, detection_date) as satellite attributes.
Starter set — this taxonomy helps teams begin establishing naming standards. It is not exhaustive. Each project should extend it with domain-specific suffixes documented in their standards register.
When naming derived columns in Business Vault satellites, use these standard suffixes to communicate intent and data type at a glance:
| Suffix | Meaning | Example |
|---|---|---|
*_amt | Monetary amount (single transaction or event) | transaction_amt, fee_amt |
*_bal | Running balance or point-in-time position | closing_bal, available_bal |
*_pmt_? | Payment-related (suffix _d = debit, _c = credit) | mortgage_pmt_d, interest_pmt_c |
*_ind | Boolean indicator (Y/N or 1/0) | active_ind, overdue_ind |
*_cd | Code value (short reference classification) | status_cd, currency_cd |
*_bnd | Band / bucket / tier classification | risk_bnd, age_bnd |
nxt_* | Next-period / forward-looking value | nxt_review_dt, nxt_payment_amt |
prv_* | Previous-period / backward-looking value | prv_balance_amt, prv_status_cd |
cur_* | Current-period / as-of-now value | cur_balance_amt, cur_rate_amt |
*_dt | Date (DATE type, no time component) | effective_dt, expiry_dt |
*_dttm | Datetime / timestamp (with time component) | created_dttm, modified_dttm |
*_stmt_* | Statement-period value (financial reporting cycle) | monthly_stmt_bal, stmt_closing_amt |
Usage rules:
amount → transaction_amt; balance → closing_balprv_closing_bal, nxt_payment_dtagents/doctrine-enforcer.mdagents/sql-generator.mdDesign and deploy an Activity Schema 2.0 pipeline as a Business Vault satellite. Covers BV staging transformation view, SAT_BV_NH_{ENTITY}_STREAM DDL, stream-on-view, triggered tasks, and per-activity IM Dynamic Tables.
Generate deployment artifacts for a Data Vault — schema DDL, dependency-ordered execution scripts, role/grant setup, and environment promotion.
Analyze source tables or schema descriptions and propose a Pragmatic Data Vault model (hubs, links, satellites)
Explain any Pragmatic Data Vault concept, pattern, rule, or modeling decision in plain language
Generate SQL DDL and load patterns for a validated Pragmatic Data Vault construct
Generate Snowflake orchestration (Tasks, DAGs, execution order) for vault loading. Handles dependency ordering, sequential same-hub loads, and ghost record deployment.