| name | dv-mart |
| description | Build Information Mart views from the Raw Vault. Hash keys are never exposed — BI tools see only business keys and descriptive attributes. |
| enabled | true |
/dv-mart — Information Mart Views
Build query-ready views on top of the Raw Vault for BI tools and analysts. Hash keys are an internal vault implementation detail — they must never appear in Information Mart views.
Why data modelling discipline in the IM matters: storage is cheap, compute is not
Columnar storage on cloud object stores is near-free. This has led to the OBT (One Big Table) pattern — storing everything in one wide table and resolving complexity at query time. The problem: compute is not cheap.
Without a modelled IM layer, every query must resolve the same complexity — current state detection, SCD logic, join conditions — every time any user runs it. Cost scales linearly with concurrency. In a properly modelled IM (or data vault + IM), complexity is solved once in the model and all consumers inherit the savings:
- A
VC_SAT_* current-state view resolves QUALIFY ROW_NUMBER() = 1 once — thousands of users query it for free
- A CPIT Dynamic Table pre-materialises the current locators — queries are O(1) joins
- A PIT or Bridge table pre-computes multi-satellite join paths — every IM view benefits
The correct principle: solve complexity once at the model layer; let all consumers inherit the result. The alternative — OBT or unmodelled SQL views — forces every user to pay the computation cost every time.
Rule: no hash keys in the IM
Every IM view must:
- Substitute
<NAME>_BK for <NAME>_HK wherever a key is needed
- Expose natural business keys that the business recognises
- Never include
BINARY columns
- Never expose
dv_hashdiff, dv_load_timestamp, dv_applied_timestamp, or dv_recordsource unless the view is explicitly an audit view
If validation is needed, the Doctrine Enforcer checks this automatically.
Input
Ask the user:
- Which hub (or hubs) is the mart anchored on?
- Which satellites should be included?
- Do you need current-state only, or point-in-time snapshots?
- Are there links to traverse (e.g. customer → orders → products)?
- Does a PIT table exist for the anchor hub? (recommend one if not)
Satellite views (vc_ and vh_)
DVOS generates two views per satellite when satellite_views is enabled in the manifest. These encapsulate the temporal logic so that IM views and ad-hoc queries can join directly without repeating window functions.
vc_<sat_name> — Current record view
Returns exactly one row per parent hash key: the latest active record.
CREATE OR REPLACE VIEW <vault_schema>.VC_<SAT_NAME> AS
SELECT *
FROM <vault_schema>.SAT_<SAT_NAME>
QUALIFY ROW_NUMBER() OVER (
PARTITION BY dv_hashkey_hub_<parent>
ORDER BY dv_applied_timestamp DESC, dv_load_timestamp DESC
) = 1;
vh_<sat_name> — History view
Returns all records with computed dv_applied_timestamp_end and dv_currentflag columns, enabling range-based "as-of" queries without a PIT table.
CREATE OR REPLACE VIEW <vault_schema>.VH_<SAT_NAME> AS
SELECT
*,
COALESCE(
LEAD(dv_applied_timestamp) OVER (
PARTITION BY dv_hashkey_hub_<parent>
ORDER BY dv_applied_timestamp, dv_load_timestamp
),
'9999-12-31 23:59:59'::TIMESTAMP_NTZ
) AS dv_applied_timestamp_end,
CASE
WHEN ROW_NUMBER() OVER (
PARTITION BY dv_hashkey_hub_<parent>
ORDER BY dv_applied_timestamp DESC, dv_load_timestamp DESC
) = 1 THEN TRUE
ELSE FALSE
END AS dv_currentflag
FROM <vault_schema>.SAT_<SAT_NAME>;
Using satellite views in IM construction
Instead of repeating QUALIFY logic in every IM view, join the vc_ view directly:
CREATE OR REPLACE VIEW <im_schema>.DIM_<ENTITY> AS
SELECT
h.<bk_column>,
s1.<attr1>,
s2.<attr2>
FROM <vault_schema>.HUB_<ENTITY> h
LEFT JOIN <vault_schema>.VC_SAT_<ENTITY>_<CONTEXT1> s1
ON s1.dv_hashkey_hub_<entity> = h.dv_hashkey_hub_<entity>
LEFT JOIN <vault_schema>.VC_SAT_<ENTITY>_<CONTEXT2> s2
ON s2.dv_hashkey_hub_<entity> = h.dv_hashkey_hub_<entity>;
Naming rules
| View type | Prefix | Example |
|---|
| Current (latest record) | VC_ | VC_SAT_RV_HUB_{badge}_CUSTOMER_DEMOGRAPHICS |
| History (all records + end-date + flag) | VH_ | VH_SAT_CUSTOMER_DEMOGRAPHICS |
- Prefix is uppercase in DDL, lowercase in manifest references (
vc_sat_customer_demographics)
- The satellite name follows unchanged after the prefix
- These views live in the vault schema (not the IM schema) — they are internal to the vault layer
When to use which
| Need | Use |
|---|
| Latest record per entity | vc_ view |
| "As-of" query without PIT | vh_ view with WHERE dv_applied_timestamp <= @as_of AND dv_applied_timestamp_end > @as_of |
| Multiple satellites aligned at same point in time | PIT table (satellite views alone can't align across satellites) |
| IM dimension (current state) | Join vc_ views to hub |
| IM dimension (history) | PIT-based view or join vh_ views for single-satellite history |
View patterns
Current-state view (no PIT needed)
DVOS satellites have no end-date column. Current row is selected via QUALIFY ROW_NUMBER() ordered by dv_applied_timestamp DESC, dv_load_timestamp DESC. DVOS also generates vc_* (current) and vh_* (history) views per satellite when satellite_views is enabled in the manifest — prefer joining those if available.
CREATE OR REPLACE VIEW <schema>.DIM_<ENTITY> AS
SELECT
h.<bk_column>,
s1.<attr1>,
s1.<attr2>,
s2.<attr3>
FROM <vault_schema>.HUB_<ENTITY> h
LEFT JOIN (
SELECT * FROM <vault_schema>.SAT_<ENTITY>_<CONTEXT1>
QUALIFY ROW_NUMBER() OVER (
PARTITION BY dv_hashkey_hub_<entity>
ORDER BY dv_applied_timestamp DESC, dv_load_timestamp DESC
) = 1
) s1 ON s1.dv_hashkey_hub_<entity> = h.dv_hashkey_hub_<entity>
LEFT JOIN (
SELECT * FROM <vault_schema>.SAT_<ENTITY>_<CONTEXT2>
QUALIFY ROW_NUMBER() OVER (
PARTITION BY dv_hashkey_hub_<entity>
ORDER BY dv_applied_timestamp DESC, dv_load_timestamp DESC
) = 1
) s2 ON s2.dv_hashkey_hub_<entity> = h.dv_hashkey_hub_<entity>;
Alternative (if satellite_views enabled): Join the generated vc_<sat_name> view directly — it already encapsulates the QUALIFY pattern.
Point-in-time view (PIT required)
When the hub has multiple satellites, use the PIT table to avoid correlated subqueries and correctly align snapshot dates. PIT stores per-satellite dv_applied_timestamp — join satellites on that column:
CREATE OR REPLACE VIEW <schema>.DIM_<ENTITY>_HISTORY AS
SELECT
h.<bk_column>,
pit.SNAPSHOT_DATE,
s1.<attr1>,
s1.<attr2>,
s2.<attr3>
FROM <vault_schema>.PIT_<ENTITY> pit
JOIN <vault_schema>.HUB_<ENTITY> h
ON h.dv_hashkey_hub_<entity> = pit.dv_hashkey_hub_<entity>
LEFT JOIN <vault_schema>.SAT_<ENTITY>_<CONTEXT1> s1
ON s1.dv_hashkey_hub_<entity> = pit.dv_hashkey_hub_<entity>
AND s1.dv_applied_timestamp = pit.<sat1_alias>_dv_applied_timestamp
LEFT JOIN <vault_schema>.SAT_<ENTITY>_<CONTEXT2> s2
ON s2.dv_hashkey_hub_<entity> = pit.dv_hashkey_hub_<entity>
AND s2.dv_applied_timestamp = pit.<sat2_alias>_dv_applied_timestamp;
Bridge-based view (multi-link traversal)
Use a Bridge table to traverse multiple links from an anchor hub without expensive ad-hoc joins:
CREATE OR REPLACE VIEW <schema>.FACT_<CONTEXT> AS
SELECT
h_anchor.<bk_col_anchor>,
h_related.<bk_col_related>,
bdg.SNAPSHOT_DATE,
s_anchor.<attr1>,
s_related.<attr2>
FROM <vault_schema>.BDG_<ANCHOR>_<CONTEXT> bdg
JOIN <vault_schema>.HUB_<ANCHOR> h_anchor
ON h_anchor.dv_hashkey_hub_<anchor> = bdg.dv_hashkey_hub_<anchor>
JOIN <vault_schema>.HUB_<RELATED> h_related
ON h_related.dv_hashkey_hub_<related> = bdg.dv_hashkey_hub_<related>
LEFT JOIN (
SELECT * FROM <vault_schema>.SAT_<ANCHOR>_<CONTEXT>
QUALIFY ROW_NUMBER() OVER (
PARTITION BY dv_hashkey_hub_<anchor>
ORDER BY dv_applied_timestamp DESC, dv_load_timestamp DESC
) = 1
) s_anchor ON s_anchor.dv_hashkey_hub_<anchor> = bdg.dv_hashkey_hub_<anchor>
LEFT JOIN (
SELECT * FROM <vault_schema>.SAT_<RELATED>_<CONTEXT>
QUALIFY ROW_NUMBER() OVER (
PARTITION BY dv_hashkey_hub_<related>
ORDER BY dv_applied_timestamp DESC, dv_load_timestamp DESC
) = 1
) s_related ON s_related.dv_hashkey_hub_<related> = bdg.dv_hashkey_hub_<related>;
Fact-style view (traversing a link)
Traverse a link to join two hubs and their satellites into a fact-style view:
CREATE OR REPLACE VIEW <schema>.FACT_<RELATIONSHIP> AS
SELECT
h_a.<bk_col_a>,
h_b.<bk_col_b>,
s_a.<attr1>,
s_b.<attr2>,
s_lnk.<rel_attr1>,
s_lnk.<rel_attr2>
FROM <vault_schema>.LNK_<NAME> lnk
JOIN <vault_schema>.HUB_<ENTITY_A> h_a
ON h_a.dv_hashkey_hub_<entity_a> = lnk.dv_hashkey_hub_<entity_a>
JOIN <vault_schema>.HUB_<ENTITY_B> h_b
ON h_b.dv_hashkey_hub_<entity_b> = lnk.dv_hashkey_hub_<entity_b>
LEFT JOIN (
SELECT * FROM <vault_schema>.SAT_<ENTITY_A>_<CONTEXT>
QUALIFY ROW_NUMBER() OVER (
PARTITION BY dv_hashkey_hub_<entity_a>
ORDER BY dv_applied_timestamp DESC, dv_load_timestamp DESC
) = 1
) s_a ON s_a.dv_hashkey_hub_<entity_a> = lnk.dv_hashkey_hub_<entity_a>
LEFT JOIN (
SELECT * FROM <vault_schema>.SAT_<ENTITY_B>_<CONTEXT>
QUALIFY ROW_NUMBER() OVER (
PARTITION BY dv_hashkey_hub_<entity_b>
ORDER BY dv_applied_timestamp DESC, dv_load_timestamp DESC
) = 1
) s_b ON s_b.dv_hashkey_hub_<entity_b> = lnk.dv_hashkey_hub_<entity_b>
LEFT JOIN (
SELECT * FROM <vault_schema>.SAT_RV_LNK_{badge}_{file}
QUALIFY ROW_NUMBER() OVER (
PARTITION BY dv_hashkey_<lnk_name>
ORDER BY dv_applied_timestamp DESC, dv_load_timestamp DESC
) = 1
) s_lnk ON s_lnk.dv_hashkey_<lnk_name> = lnk.dv_hashkey_<lnk_name>;
Effectivity satellite query templates
Three standard query patterns for effectivity satellites, each as a reusable template:
1. Active relationships only:
SELECT lnk.*, eff.dv_start_date
FROM LNK_<NAME> lnk
JOIN SAT_EF_RV_LNK_{badge}_{file} eff ON lnk.dv_hashkey_lnk_rv_<name> = eff.dv_hashkey_lnk_rv_<name>
QUALIFY ROW_NUMBER() OVER (PARTITION BY eff.dv_hashkey_lnk_<name> ORDER BY eff.dv_applied_timestamp DESC) = 1
WHERE eff.dv_end_date = '9999-12-31'::TIMESTAMP_NTZ;
2. Full history (active + all closed):
SELECT lnk.*, eff.dv_start_date, eff.dv_end_date,
CASE WHEN eff.dv_end_date = '9999-12-31'::TIMESTAMP_NTZ THEN 'ACTIVE' ELSE 'CLOSED' END AS relationship_status
FROM LNK_<NAME> lnk
JOIN SAT_EF_RV_LNK_{badge}_{file} eff ON lnk.dv_hashkey_lnk_rv_<name> = eff.dv_hashkey_lnk_rv_<name>;
3. Active + replaced (latest slice showing current and predecessor):
SELECT lnk.*, eff.dv_start_date, eff.dv_end_date,
LAG(lnk.dv_hashkey_hub_<non_driving>) OVER (
PARTITION BY lnk.dv_hashkey_hub_<driving> ORDER BY eff.dv_start_date
) AS prev_relationship
FROM LNK_<NAME> lnk
JOIN SAT_EF_RV_LNK_{badge}_{file} eff ON lnk.dv_hashkey_lnk_rv_<name> = eff.dv_hashkey_lnk_rv_<name>
QUALIFY ROW_NUMBER() OVER (PARTITION BY lnk.dv_hashkey_hub_<driving> ORDER BY eff.dv_start_date DESC) = 1;
The third template is useful for showing "customer moved from account X to account Y" in a single row.
Stem-and-leaf view pattern
A two-phase templated approach for constructing consolidated views over multiple satellites around a hub or link when full history (not just current state) is required.
Problem: Each satellite around an entity has its own independent change timeline. A change in satellite A at date X and a change in satellite B at date Y means neither satellite has the full consolidated timeline. Simple joins produce incorrect results (misaligned timestamps).
Solution: Stem-and-leaf pattern:
Phase 1 — Leaf CTEs (one per satellite):
- Select only the columns needed for the IM (not all satellite columns)
- Rehash the selected subset:
SHA2(CONCAT_WS('||', selected_cols)) — if the subset produces duplicates that weren't duplicates in the full satellite, this rehash deduplicates them
- Compute virtual end-date via
LEAD(dv_applied_timestamp) OVER (...) - INTERVAL '1 day'
- Result: each leaf has
(hashkey, start_date, end_date, selected_attributes)
Phase 2 — Stem CTE:
- UNION ALL of all leaf start_dates to create a consolidated timeline (all event dates from all satellites merged)
- Left-join each leaf back using range predicates:
leaf.start_date <= stem.snapshot_date AND leaf.end_date >= stem.snapshot_date
- Result: one row per entity per consolidated snapshot date, with columns from all satellites aligned
CREATE OR REPLACE VIEW DIM_CUSTOMER_HISTORY AS
WITH
leaf_demo AS (
SELECT dv_hashkey_hub_customer,
dv_applied_timestamp AS start_date,
COALESCE(LEAD(dv_applied_timestamp) OVER (
PARTITION BY dv_hashkey_hub_customer ORDER BY dv_applied_timestamp
) - INTERVAL '1 day', '9999-12-31') AS end_date,
first_name, last_name, email
FROM SAT_RV_HUB_CUSTOMER_DEMO
),
leaf_fin AS (
SELECT dv_hashkey_hub_customer,
dv_applied_timestamp AS start_date,
COALESCE(LEAD(dv_applied_timestamp) OVER (
PARTITION BY dv_hashkey_hub_customer ORDER BY dv_applied_timestamp
) - INTERVAL '1 day', '9999-12-31') AS end_date,
credit_limit, account_status
FROM SAT_RV_HUB_CUSTOMER_FIN
),
stem AS (
SELECT dv_hashkey_hub_customer, start_date AS snapshot_date FROM leaf_demo
UNION
SELECT dv_hashkey_hub_customer, start_date FROM leaf_fin
)
SELECT
h.customer_bk,
s.snapshot_date,
d.first_name, d.last_name, d.email,
f.credit_limit, f.account_status
FROM stem s
JOIN HUB_CUSTOMER h ON h.dv_hashkey_hub_customer = s.dv_hashkey_hub_customer
LEFT JOIN leaf_demo d ON d.dv_hashkey_hub_customer = s.dv_hashkey_hub_customer
AND d.start_date <= s.snapshot_date AND d.end_date >= s.snapshot_date
LEFT JOIN leaf_fin f ON f.dv_hashkey_hub_customer = s.dv_hashkey_hub_customer
AND f.start_date <= s.snapshot_date AND f.end_date >= s.snapshot_date;
Current-only shortcut: For current-state views (no history needed), skip the stem construction entirely — use GREATEST(leaf1.start_date, leaf2.start_date) with INNER JOINs on the latest row from each leaf.
Intra-day leaf consolidation: For satellites with intra-day keys, the virtual end-date must be derived from SELECT DISTINCT hashkey, DATE(dv_applied_timestamp) (grouped to day level) because sub-day end-dates produce overlapping ranges.
Hash-join dimension pattern (parallel fact + dimension loading)
Instead of traditional surrogate sequence keys that force sequential dimension-then-fact loading, hash all dimension attributes (excluding event dates) into a binary join key:
CREATE TABLE DIM_PRODUCT (
d_product_hashjoin BINARY(20) NOT NULL PRIMARY KEY,
product_name VARCHAR,
category VARCHAR,
brand VARCHAR
);
INSERT INTO DIM_PRODUCT
SELECT SHA1(CONCAT_WS('||', product_name, category, brand)), product_name, category, brand
FROM staged_data
WHERE SHA1(CONCAT_WS('||', product_name, category, brand)) NOT IN (SELECT d_product_hashjoin FROM DIM_PRODUCT);
CREATE TABLE FACT_SALES (
sale_date DATE,
d_product_hashjoin BINARY(20),
d_customer_hashjoin BINARY(20),
quantity NUMBER,
amount NUMBER(18,2)
);
Key properties:
- Parallel loading — facts and dimensions load from the same staged file simultaneously (no lookup dependency)
- No SCD-2 overhead — dimensions are accumulative (unique states only, no start/end dates, no current flags). Each unique attribute combination = one row forever.
- Hash-join determinism — the hash key is derived from the attribute values; same values always produce same key regardless of load order
- Event dates (sale_date, order_date) are NEVER included in the dimension hash — they belong in the fact table
Trade-off: Only viable when all dimension attributes and measures are available in the same source file. If dimensions arrive from a different source, traditional sequence-key patterns may still be needed.
Dynamic source-selection mart (prime-number modulus)
During a system migration where both legacy and new sources supply the same business keys with an overlap period, use a data-driven source-precedence pattern:
- Assign each source a unique prime number (e.g. legacy=2, new_system=3)
- UNION ALL both sources with their prime identifier
- Use
SUM(prime) % COUNT(*) per business key — if modulus is non-zero, the key exists in both sources
CREATE OR REPLACE VIEW MART_POLICY_MIGRATED AS
WITH combined AS (
SELECT policy_bk, 2 AS src_prime, attributes.* FROM legacy_policy_view
UNION ALL
SELECT policy_bk, 3 AS src_prime, attributes.* FROM new_system_policy_view
),
source_check AS (
SELECT *,
SUM(src_prime) OVER (PARTITION BY policy_bk) % COUNT(*) OVER (PARTITION BY policy_bk) AS overlap_flag
FROM combined
)
SELECT * FROM source_check
WHERE overlap_flag = 0
OR (overlap_flag != 0 AND src_prime = 3);
Key property: Entirely data-driven — as the migration completes and legacy stops supplying records, the mart auto-adjusts without code changes. No manual cutover date logic required.
Lambda view pattern — blending historical and real-time data
A lambda view is an IM view that augments historical vault data with real-time or near-real-time data in a single consumer-facing query. Named after the lambda architecture (batch layer + speed layer), it uses UNION ALL to combine two data sources:
- Historical layer — the standard vault satellite query (full history, validated, fully auditable)
- Speed layer — a direct join to a real-time staging table, Kappa Vault stream, or an NSAT that captures intraday/live events not yet in the main satellite
CREATE OR REPLACE VIEW <schema>.LAMBDA_DIM_<ENTITY> AS
SELECT
h.<bk_column>,
s.attribute_1,
s.attribute_2,
s.dv_applied_timestamp,
'VAULT' AS data_layer
FROM <vault_schema>.HUB_<ENTITY> h
JOIN <vault_schema>.VC_SAT_<ENTITY>_<CONTEXT> s
ON s.dv_hashkey_hub_<entity> = h.dv_hashkey_hub_<entity>
UNION ALL
SELECT
<bk_column>,
attribute_1,
attribute_2,
event_timestamp AS dv_applied_timestamp,
'REALTIME' AS data_layer
FROM <staging_schema>.STG_<ENTITY>_REALTIME
WHERE event_timestamp > (
SELECT COALESCE(MAX(dv_applied_timestamp), '1900-01-01') FROM <vault_schema>.VC_SAT_<ENTITY>_<CONTEXT>
);
When to use: consumers need to see the most recent state including events that have landed in staging but haven't completed the vault load cycle yet (e.g. dashboards with sub-minute freshness SLAs where batch vault loads run hourly).
When not to use: the speed layer content is not yet validated by hard rules — the lambda view exposes pre-vault data. If the vault's hard rules would reject some real-time records, those records appear valid in the lambda view until the vault load runs. Make this trade-off explicit to consumers via the data_layer discriminator column.
Calculated attributes in the IM
If a calculation or derivation produces an attribute that belongs on a vault entity (e.g. customer lifetime value, order total), load it as a standard satellite first — treating the calculation result as a source — then expose it through the IM view like any other satellite. No special layer is needed.
Calculation/derived source → SAT_CUSTOMER_METRICS (standard satellite load)
↓
DIM_CUSTOMER view (IM)
Pre-materialised alternative — Supernova
For high-volume, continuously-updated data where result caching is insufficient and query-time join performance is a bottleneck, use /dv-supernova instead of VC_/VH_ view-based IM views.
Supernova implements the same IM delivery goal as this skill but materialises the satellite joins as Incremental Dynamic Tables rather than resolving them at query time.
| Approach | When to use |
|---|
| VC_/VH_ views + IM view (this skill) | Storage cost matters; cross-hub traversal needed (use Bridge); data doesn't change frequently |
Supernova DTs (/dv-supernova) | BI query speed is the priority; full column pre-materialisation is acceptable; computed attributes should be pre-built |
The Layer 5 delivery view patterns in /dv-supernova (filtered, aggregate, OBT, star schema) directly mirror the patterns in this skill — the only difference is that the source is supernova.dt_xsn_supernova_* instead of raw vault VC_/VH_ views.
IM Performance Escalation Flowchart
When an IM view underperforms, escalate through these stages in order. Each stage adds complexity — do not skip ahead without validating the previous stage is insufficient:
Stage 1: STEM-AND-LEAF VIEWS
│ Simple VC_/VH_ satellite views joined directly
│ ├─ Performance acceptable? → STOP (simplest solution wins)
│ └─ Too slow? → escalate
│
Stage 2: PIT TABLE
│ Pre-computed temporal alignment; eliminates QUALIFY at query time
│ ├─ Performance acceptable? → STOP
│ └─ Too slow (multi-link traversal needed)? → escalate
│
Stage 3: BRIDGE TABLE
│ Pre-joined hub-to-hub paths; eliminates multi-hop link joins
│ ├─ Performance acceptable? → STOP
│ └─ Too slow (both temporal + traversal)? → escalate
│
Stage 4: PIT + BRIDGE (hybrid)
│ Combine temporal alignment with pre-joined traversal
│ ├─ Performance acceptable? → STOP
│ └─ Still insufficient? → escalate
│
Stage 5: PHYSICALIZE (Supernova / Dynamic Table)
│ Materialise the entire IM result as an incremental DT
└─ Use `/dv-supernova` — this is the last resort
Key principles:
- Always start at Stage 1 — premature optimisation creates maintenance debt
- PIT solves temporal performance (many satellites per hub); Bridge solves traversal performance (many hops between hubs)
- Stage 5 trades storage for speed — acceptable when BI SLAs demand sub-second response on large fact tables
- Each stage is disposable — escalation is reversible if data volumes or query patterns change
Dynamic Iceberg Tables — delivering IMs to the data lakehouse
IMs can also be delivered as Dynamic Iceberg Tables when the enterprise requires curated BI content to be accessible outside of Snowflake's proprietary perimeter — for example, by Spark, Trino, or other Iceberg-compatible compute engines operating on the same object storage.
The mechanics are identical to FDN-backed Dynamic Tables. Write the same SQL in Snowflake, but the output is materialised as Apache Iceberg files (Parquet format) in cloud object storage (S3 / Azure Blob / GCS):
CREATE OR REPLACE DYNAMIC ICEBERG TABLE <im_schema>.DIM_CUSTOMER
TARGET_LAG = '1 hour'
WAREHOUSE = <wh>
EXTERNAL_VOLUME = '<volume_name>'
CATALOG = 'SNOWFLAKE'
BASE_LOCATION = 'dim_customer/'
AS
SELECT
h.customer_bk,
s.customer_name,
s.customer_status,
s.dv_applied_timestamp
FROM <vault_schema>.HUB_CUSTOMER h
JOIN <vault_schema>.VC_SAT_RV_HUB_{badge}_CUSTOMER_DEMOGRAPHICS s
ON s.dv_hashkey_hub_customer = h.dv_hashkey_hub_customer;
Same rules as FDN Dynamic Tables apply:
- Design SQL for incremental refresh — full refresh is the anti-pattern (see
/dv-pit-bridge Dynamic Table full refresh rule)
- Set
TARGET_LAG to match the business freshness SLA — not unnecessarily tight
- Source tables can be a mix of Iceberg and FDN (Snowflake resolves both transparently)
When to choose Dynamic Iceberg Tables for IM delivery:
| Need | Approach |
|---|
| IM only consumed by Snowflake users and tools | Standard VC_/VH_ view-based IM or Supernova DT (FDN) |
| IM must be queryable by non-Snowflake engines (Spark, Trino, etc.) | Dynamic Iceberg Table |
| IM contains PII | Stay with FDN Dynamic Table (keeps PII within Snowflake's encrypted perimeter) |
What to put in the IM vs. the vault
| Lives in Raw Vault | Lives in Information Mart |
|---|
| Hash keys | Business keys |
dv_load_timestamp, dv_applied_timestamp, dv_hashdiff, dv_recordsource | None (internal metadata) |
| All historical versions | Current or snapshot views |
| All source records | Joined, filtered, readable |
| Binary columns | Natural types only |
Information Marts are disposable
Because the vault holds full insert-only audit history, every IM view can be dropped and rebuilt from scratch at any time with zero data loss. The vault is the corporate memory. The IM is a presentation layer over it.
The IM is the Anti-Corruption Layer (ACL)
From a Domain-Driven Design perspective, the Information Mart is the Anti-Corruption Layer — it shields business users and BI tools from the internal complexity of the Data Vault model. Without the IM, consumers would have to:
- Navigate hub + link + satellite join patterns with binary hash keys
- Understand QUALIFY / LEAD() view semantics for current-row retrieval
- Manage PIT / Bridge structures for cross-satellite equijoins
The IM resolves all of this and presents a clean, business-vocabulary interface. This is why:
- Hash keys must never appear in IM output — they are vault-internal join keys with no business meaning
- IM naming uses business terms, not vault table names — column aliases, view names, and entity labels must come from the business domain vocabulary
- When the vault changes, the IM must be updated to maintain its ACL contract — schema evolution, source migrations, or deprecated source systems must trigger an IM review to ensure the consumer-facing interface remains stable
This has three important consequences:
-
IM views never need to be migrated — if the IM needs to change (new columns, different joins, renamed entities), drop the view and recreate it. There is nothing in the IM that doesn't already exist in the vault. Unlike a star schema where the fact and dimension tables are the history, the vault holds history independently of the presentation layer.
-
IM design can evolve freely — because the IM is disposable, there is no lock-in to an initial design decision. The cost of changing an IM view is almost zero. This is what makes the vault model a long-lived platform while IMs adapt to changing business reporting needs.
-
Vault corrections automatically propagate to all IMs — when a vault correction is applied (e.g. XTS corrects an out-of-sequence satellite timeline), IM views built as SQL views over vault tables automatically reflect the corrected state on the next query. No IM rebuild is needed. The correction is applied once, in the vault, and all downstream views pick it up immediately. This is the full consequence chain: XTS corrects vault timeline → views reflect correction → no IM rebuild required.
When asked "what happens if the IM needs to change?", the answer is: "Drop and recreate it. The history is in the vault."
When asked "what happens if a vault correction is applied?", the answer is: "IM views automatically reflect it. No rebuild needed."
Unix philosophy applied to data products
A useful design heuristic for Information Mart and data product design — borrowed from Unix philosophy:
- Do one thing and do it well — each IM view should serve one well-defined consumer need or persona. Avoid overloaded IMs that try to answer every possible question from every possible consumer in a single view. A wide, multi-purpose mart becomes a legacy mart.
- Design data products that can be assembled and work together — composable IMs (a customer dimension view + an orders fact view + a product view) are more maintainable than one monolithic OBT. Composable patterns also allow PIT + Bridge + IM to layer cleanly on top of each other.
- Create data products to be tried early and retired when redundant — the vault's disposable IM principle makes this possible: try an IM design early, let consumers validate it, and retire it without guilt when it's superseded. Data products that outlive their purpose accumulate maintenance cost. Retire them.
IM rules vs. BV rules — the routing criterion
Not every derived calculation or pre-computed metric needs to be physicalised in Business Vault. The decision criterion:
If a calculation can be easily re-created from Raw Vault and Business Vault data without any data loss, it is an Information Mart rule — keep it in the IM view or bridge table, not in BV.
| Where it belongs | Criterion | Examples |
|---|
| Business Vault | The calculation produces a persistent, versioned business insight that must be auditable across rule changes | Risk scores, ML model outputs, derived lifecycle states, inferred relationships |
| Information Mart | The calculation can be reconstructed on-demand from RV+BV; no auditability of the formula itself is required | Rolling sums, running balances via window functions, date dimension keys, column renaming, unit conversions |
Pre-calculated metrics added to a bridge/fact table (e.g. SUM(transaction) OVER (PARTITION BY ... ORDER BY dv_loaddate)) are IM rules: the underlying transaction records are in the link-satellite; the rolling balance can be recalculated at any time. There is no business insight locked inside the aggregation that would be lost if the bridge were dropped and rebuilt.
Doctrine check
Before finalising an IM view, spawn the Doctrine Enforcer subagent and ask it to verify:
- No BINARY columns selected
- No
_HK columns in the SELECT list
- No
dv_hashdiff, dv_load_timestamp, dv_applied_timestamp, dv_recordsource in the SELECT list (unless it's an explicit audit view)
- Every hub join uses the business key (
<bk_column>) in the output
Column name conformance is an IM-only concern
Column names in Raw Vault satellites must be kept as close to source as possible. Column names in Business Vault satellites must reflect the derived concept. Renaming and conforming column names to business vocabulary is exclusively an IM-layer responsibility.
Reasons this rule matters:
- RV conformance creates tech debt — if the source later starts supplying a column with a name you've already used for a conformed alias, you have a naming collision with no clean resolution
- BV conformance defeats separation of concerns — BV is for derived business rules, not cosmetic renaming. A BV satellite that just renames RV columns (and adds no derivation) is an anti-pattern: it duplicates storage and introduces a second load that must stay in sync
- IM is disposable; RV/BV is not — IM views can be dropped and recreated freely. The correct place to solve naming for each consumer domain is in each domain's IM — different business units can conform to different vocabularies in their own IM layer without affecting the shared vault
If different consumers need different column names for the same satellite column, create separate IM views for each consumer. Do not solve this in the vault layers.
IM query performance rules:
-
Use UNION ALL instead of UNION — UNION performs an implicit DISTINCT deduplication (sort + compare) on every row. If data is correctly modelled at the source grain, there should be no duplicates to remove. UNION ALL is always faster and should be the default. Only use UNION when deduplication is genuinely required.
-
Use DISTINCT sparingly — a SELECT DISTINCT in an IM query is usually a signal of either an incorrect join (producing fanout), a satellite placed at the wrong grain, or a missing deduplication in the staging layer. Investigate the root cause rather than masking it with DISTINCT.
-
Join at the correct grain — avoid cartesian products by ensuring every join condition is complete. Missing a join predicate produces row multiplication that DISTINCT then masks, hiding a performance and correctness problem.
-
Select only the columns you need — wide projections reduce columnar compression effectiveness and increase bytes scanned.
-
IM views benefit from Snowflake result cache — because IMs are SQL views over vault tables, the exact same query with no underlying data changes returns from result cache (24-hour window) at zero compute cost — no virtual warehouse credits consumed. This is a free performance win for frequently-repeated IM queries (e.g. daily dashboard refreshes). Two conditions invalidate result cache: (1) the underlying vault tables have changed since the last run, or (2) the query includes context functions (CURRENT_TIMESTAMP, CURRENT_USER, etc.).
VW auto-suspend policy affects cache availability: Virtual warehouse cache (distinct from result cache) is flushed when the warehouse suspends. If the IM query warehouse is configured to suspend immediately after each query, the VW cache is lost — subsequent queries that overlap with previously-fetched data must re-fetch from storage. Keep the IM query warehouse alive for at least the duration of a typical reporting session (recommended minimum: 5–10 minutes auto-suspend for interactive BI workloads).
Activity Schema relationship Dynamic Tables
When the source of truth for the IM is a SAT_BV_NH_{ENTITY}_STREAM table (Activity Schema satellite), build Dynamic Tables instead of SQL views. This section covers the full pattern from per-activity DTs through enriched relationship DTs and dimension enrichment.
Step 1 — Per-activity Dynamic Table (prerequisite)
Each activity needs its own DT with activity_occurrence, activity_previous_at, activity_repeated_at columns. These are generated by /dv-bv-activity-schema. If they don't exist yet, generate them first.
Step 2 — Enriched relationship Dynamic Table
The enriched DT implements Activity Schema relationships: for a cohort activity, find what happened before, after, or in-between using another (append) activity.
Pattern: cohort + append (before and after)
CREATE OR REPLACE DYNAMIC TABLE <im_schema>.dt_<entity>_stream_enriched
TARGET_LAG = '1 minute'
WAREHOUSE = <wh>
AS
WITH
select_before AS (
SELECT
cohort.<bk_column>,
cohort.dv_applied_timestamp AS cohort_ts,
cohort.activity_repeated_at AS cohort_activity_repeated_at,
cohort.activity_occurrence AS cohort_activity_occurrence,
append_before.revenue_impact AS append_revenue_impact_before,
cohort.feature_json AS cohort_feature_json,
FIRST_VALUE(append_before.dv_applied_timestamp) OVER (
PARTITION BY cohort.<bk_column>, cohort.dv_applied_timestamp
ORDER BY append_before.dv_applied_timestamp) AS first_before_ts,
LAST_VALUE(append_before.dv_applied_timestamp) OVER (
PARTITION BY cohort.<bk_column>, cohort.dv_applied_timestamp
ORDER BY append_before.dv_applied_timestamp) AS last_before_ts,
FIRST_VALUE(append_before.feature_json) OVER (
PARTITION BY cohort.<bk_column>, cohort.dv_applied_timestamp
ORDER BY append_before.dv_applied_timestamp) AS first_before_feature_json,
LAST_VALUE(append_before.feature_json) OVER (
PARTITION BY cohort.<bk_column>, cohort.dv_applied_timestamp
ORDER BY append_before.dv_applied_timestamp) AS last_before_feature_json
FROM <im_schema>.dt_<entity>_stream_<cohort_activity> cohort
INNER JOIN <im_schema>.dt_<entity>_stream_<append_activity> append_before
ON cohort.<bk_column> = append_before.<bk_column>
AND cohort.dv_applied_timestamp >= append_before.dv_applied_timestamp
AND cohort.activity_previous_at < append_before.dv_applied_timestamp
),
aggregate_before AS (
SELECT <bk_column>, cohort_ts,
COUNT(*) AS append_before_count,
SUM(append_revenue_impact_before) AS revenue_impact_before
FROM select_before
GROUP BY 1, 2
),
select_after AS (
SELECT
cohort.<bk_column>,
cohort.dv_applied_timestamp AS cohort_ts,
cohort.activity_repeated_at AS cohort_activity_repeated_at,
cohort.activity_occurrence AS cohort_activity_occurrence,
append_after.revenue_impact AS append_revenue_impact_after,
cohort.feature_json AS cohort_feature_json,
FIRST_VALUE(append_after.dv_applied_timestamp) OVER (
PARTITION BY cohort.<bk_column>, cohort.dv_applied_timestamp
ORDER BY append_after.dv_applied_timestamp) AS first_after_ts,
LAST_VALUE(append_after.dv_applied_timestamp) OVER (
PARTITION BY cohort.<bk_column>, cohort.dv_applied_timestamp
ORDER BY append_after.dv_applied_timestamp) AS last_after_ts,
FIRST_VALUE(append_after.feature_json) OVER (
PARTITION BY cohort.<bk_column>, cohort.dv_applied_timestamp
ORDER BY append_after.dv_applied_timestamp) AS first_after_feature_json,
LAST_VALUE(append_after.feature_json) OVER (
PARTITION BY cohort.<bk_column>, cohort.dv_applied_timestamp
ORDER BY append_after.dv_applied_timestamp) AS last_after_feature_json
FROM <im_schema>.dt_<entity>_stream_<cohort_activity> cohort
INNER JOIN <im_schema>.dt_<entity>_stream_<append_activity> append_after
ON cohort.<bk_column> = append_after.<bk_column>
AND cohort.dv_applied_timestamp <= append_after.dv_applied_timestamp
AND cohort.activity_repeated_at > append_after.dv_applied_timestamp
),
aggregate_after AS (
SELECT <bk_column>, cohort_ts,
COUNT(*) AS append_after_count,
SUM(append_revenue_impact_after) AS revenue_impact_after
FROM select_after
GROUP BY 1, 2
),
stalk AS (
SELECT <bk_column>, cohort_ts FROM select_before
UNION ALL
SELECT <bk_column>, cohort_ts FROM select_after
),
stalk_distinct AS (
SELECT *,
FIRST_VALUE(cohort_ts) OVER (PARTITION BY <bk_column> ORDER BY cohort_ts) AS first_ever_ts,
LAST_VALUE(cohort_ts) OVER (PARTITION BY <bk_column> ORDER BY cohort_ts) AS last_ever_ts
FROM stalk
QUALIFY ROW_NUMBER() OVER (PARTITION BY <bk_column>, cohort_ts ORDER BY cohort_ts) = 1
)
SELECT
stalk.<bk_column>,
stalk.cohort_ts,
stalk.first_ever_ts,
stalk.last_ever_ts,
before.first_before_ts,
before.last_before_ts,
after.first_after_ts,
after.last_after_ts,
agg_before.append_before_count,
agg_before.revenue_impact_before,
agg_after.append_after_count,
agg_after.revenue_impact_after,
COALESCE(before.cohort_feature_json, after.cohort_feature_json) AS cohort_feature_json,
before.first_before_feature_json,
before.last_before_feature_json,
after.first_after_feature_json,
after.last_after_feature_json
FROM stalk_distinct stalk
LEFT JOIN aggregate_before agg_before
ON stalk.<bk_column> = agg_before.<bk_column> AND stalk.cohort_ts = agg_before.cohort_ts
LEFT JOIN select_before before
ON stalk.<bk_column> = before.<bk_column> AND stalk.cohort_ts = before.cohort_ts
LEFT JOIN aggregate_after agg_after
ON stalk.<bk_column> = agg_after.<bk_column> AND stalk.cohort_ts = agg_after.cohort_ts
LEFT JOIN select_after after
ON stalk.<bk_column> = after.<bk_column> AND stalk.cohort_ts = after.cohort_ts
QUALIFY ROW_NUMBER() OVER (PARTITION BY stalk.<bk_column>, stalk.cohort_ts
ORDER BY stalk.cohort_ts) = 1;
Key design decisions:
INNER JOIN for before/after selects — only cohort rows that have matching append rows contribute to the window analyticsSTALK union ensures all cohort rows appear in the final output, even with no matches (LEFT JOINs on aggregates handle the NULLs)QUALIFY ROW_NUMBER() = 1 at the end deduplicates the stalk union
Step 3 — Dimension enrichment with ASOF JOIN
ASOF JOIN resolves a slowly-changing dimension to the correct row at the point in time of each cohort event. No date-range predicates needed — Snowflake handles the temporal alignment.
CREATE OR REPLACE DYNAMIC TABLE <im_schema>.dt_<entity>_stream_enriched_with_<dim>
TARGET_LAG = '1 minute'
WAREHOUSE = <wh>
AS
SELECT
enriched.<bk_column>,
enriched.cohort_ts,
dim.dv_applied_timestamp AS <dim>_effective_date,
dim.<dim_col_1>,
dim.<dim_col_2>,
enriched.first_ever_ts,
enriched.last_ever_ts,
enriched.first_before_ts,
enriched.last_before_ts,
enriched.first_after_ts,
enriched.last_after_ts,
enriched.cohort_feature_json,
enriched.append_before_count,
enriched.revenue_impact_before,
enriched.append_after_count,
enriched.revenue_impact_after
FROM <im_schema>.dt_<entity>_stream_enriched enriched
ASOF JOIN <im_schema>.dim_<entity> dim
MATCH_CONDITION (enriched.cohort_ts >= dim.dv_applied_timestamp)
ON enriched.<bk_column> = dim.<bk_column>;
ASOF JOIN is Snowflake-native and exactly equivalent to WHERE dim.dv_applied_timestamp = MAX(dim.dv_applied_timestamp) WHERE dim.dv_applied_timestamp <= enriched.cohort_ts — but without the subquery and with better performance.
Relationship types summary
| Relationship | Pattern | Activity Schema term |
|---|
| Before (in-between) | cohort.dv_applied_timestamp >= append.dv_applied_timestamp AND cohort.activity_previous_at < append.dv_applied_timestamp | Aggregate In Before |
| After (in-between) | cohort.dv_applied_timestamp <= append.dv_applied_timestamp AND cohort.activity_repeated_at > append.dv_applied_timestamp | Aggregate In Between |
| First ever | FIRST_VALUE(append.dv_applied_timestamp) OVER (PARTITION BY entity ORDER BY append.dv_applied_timestamp) | First Ever |
| Last ever | LAST_VALUE(append.dv_applied_timestamp) OVER (...) | Last Ever |
| SCD dim join | ASOF JOIN dim MATCH_CONDITION (cohort.ts >= dim.effective_date) | Last Before (for dimensions) |
Twine — near-real-time IM enrichment pattern
Twine is a named technique for enriching fact-grain records with the correct version of dimension data at a point in time. It is suited for streaming / near-real-time workloads at very large scale where a pre-built PIT or star schema is not feasible.
Selection rule — Twine/ASOF vs. hash join:
| Workload | Preferred approach | Join plan | Why |
|---|
| Data at rest, pre-built star schema / bridge | Hash join (equi-join via PIT/SNOPIT) | Right-Deep (build + probe) | Optimal for OLAP queries over pre-modelled dimensional structures |
| Data in motion (streaming), very large fact tables, near-real-time enrichment | Twine / ASOF JOIN | Left-Deep (nested loop) | Memory-efficient for enriching records at event grain without pre-modelling |
Two implementations:
- Manual twine —
UNION ALL all fact and dimension tables on a common key + timestamp, then use window functions to find the nearest valid dimension date for each fact row, then left-join back to each dimension on that date:
SELECT id, ${fact_columns}, d1.${dim_columns}
FROM (
SELECT twine.id, twine.timepoint,
MAX(CASE WHEN timeline = 'D1' THEN timepoint END)
OVER (PARTITION BY id ORDER BY timepoint) AS d1_ValidFrom,
${fact_columns}
FROM (
SELECT id, fact_ts AS timepoint, 'F' AS timeline, ${fact_columns} FROM fact_table
UNION ALL
SELECT id, dim_ts AS timepoint, 'D1' AS timeline, NULL FROM dim_table_1
) twine
) in_effect
LEFT JOIN dim_table_1 d1
ON in_effect.id = d1.id AND in_effect.d1_ValidFrom = d1.dim_ts
WHERE in_effect.timeline = 'F';
- ASOF JOIN — Snowflake's simplified SQL abstraction for twine. Same semantics, simpler code, similar Left-Deep join plan. Preferred for readability:
SELECT f.*, d1.${dim_columns}
FROM fact_table f
ASOF JOIN dim_table_1 d1
MATCH_CONDITION(f.fact_ts >= d1.dim_ts)
ON f.id = d1.id;
Note: ASOF JOIN is NOT suitable for PIT table construction — it produces a Left-Deep Join Tree which is less performant than the standard equi-join (Right-Deep hash join) used in PIT builds. See Rule 3 in /dv-pit-bridge.
CQRS streaming IM pattern
Command Query Responsibility Segregation (CQRS) applied to Data Vault streaming:
| Component | DV construct | Technology |
|---|
| Write Model | Insert-only NHL or NSAT (immutable event log) | Snowpipe Streaming / Kafka — exactly-once semantics |
| Read Model | Dynamic Table using ASOF JOIN | Incremental refresh as new events arrive |
The Dynamic Table (Read Model) aggregates write model events at the business grain and refreshes incrementally, producing a near-real-time IM. Only apply this when you need data within a ~5-minute window from business event to analytical value. For batch or micro-batch needs, standard vault loading patterns are sufficient.
After generating IM views
Suggest:
"Consider creating a semantic layer on top of these views — column aliases and metric definitions — before connecting BI tools. Use /dv-explain semantic-layer for guidance."
Semantic layer doctrine
The Information Mart views produced by /dv-mart feed into the semantic layer. The semantic layer translates vault and IM constructs into business-friendly terms: entities, dimensions, metrics, and hierarchies. It is the final step in the data delivery chain — not a modelling layer.
The following principles govern what the semantic model should and should not do:
What the semantic layer IS
- A business vocabulary layer — translates technical column names into business terms (entity = customer, dimension = account type, metric = total transactions)
- The end state of harmonised information — by the time data reaches the semantic layer, all modelling conflicts, source deviations, and technical debt must already be resolved
- Ephemeral and views-first — deployed as views cached in memory for immediate accessibility; not a physical storage layer
- Business-vetted — every definition in the semantic model is confirmed by business users, because they are accountable for the analytical results
What the semantic layer IS NOT
- A modelling layer — data modelling is solved in raw vault and business vault. The semantic model should do as little modelling as possible.
- A technical debt resolution layer — tech debt accumulated upstream is resolved as far left (upstream) as possible. The semantic layer never resolves tech debt; it inherits the result of upstream resolution.
- A layered/stacked structure — stacking semantic models (model A depends on model B which depends on model C) introduces unvetted dependencies that make the semantic layer fragile and hard to change. There is only one semantic layer.
- An audit trail — the semantic layer has no history. Calculations that need historization must be pushed left into the vault or Business Vault. The semantic model is ephemeral.
- A business rules engine — business rules belong upstream in the vault or BV. Only very lightweight presentation logic is permitted in the semantic model.
- A hardcoded-values store — no hardcoded values in the semantic model. Any data-driven change must come from reference data, not from edited semantic model definitions.
12 semantic layer principles (summary)
| # | Principle |
|---|
| 1 | Do as little modelling as possible — vault and BV solve the model |
| 2 | Push technical debt left — resolve it at source, not here |
| 3 | No layering/stacking — one flat semantic layer |
| 4 | Only one semantic layer per enterprise |
| 5 | Reflect business ontology and business terms |
| 6 | End state of harmonised data — inherits, doesn't resolve |
| 7 | No audit trail — ephemeral; historization is pushed left |
| 8 | Must be vetted by business |
| 9 | Accessible, searchable, secure, and accurate |
| 10 | Provides insights without delay (views, cached in memory) |
| 11 | Does not apply business rules |
| 12 | No hardcoded values — data-driven via reference data |
DV → semantic layer mapping
| Vault construct | Semantic layer concept |
|---|
| Hub | Entity |
| Satellite attributes | Dimensions (descriptive attributes for slicing/dicing) |
| Satellite date columns | Time dimensions |
| Link satellite measures | Metrics / measures (additive, semi-additive facts) |
| Hub-to-hub path via link | Hierarchy (drill path) |
Bridge table (BRDG_) | Fact table — aggregable measures across snapshot periods |
Use /dv-explain semantic-layer for a conceptual explanation of these principles.
Cortex Analyst delivery chain
Snowflake Semantic Views are first-class Snowflake objects (not YAML files) that serve as the structured data interface for Cortex Analyst — Snowflake's natural-language REST API for enterprise data. The full delivery chain is:
IM view → Semantic View → Cortex Analyst → Cortex Agent
- IM view — vault query patterns, hash key elimination, business vocabulary
- Semantic View — entities, dimensions, facts, metrics, relationships declared as a Snowflake object; synonyms and verified queries improve LLM accuracy
- Cortex Analyst — accepts NL questions via REST API, translates to SQL using the Semantic View, returns structured results
- Cortex Agent — orchestrates Cortex Analyst (structured data) and Cortex Search (unstructured data) for agentic AI workflows
The semantic layer is not a new architectural layer — it is the AI-accessible interface to the existing IM layer. No additional modelling is required in the Semantic View beyond what the IM already provides.
Three metrics categories — IM and semantic layer design
When designing IM views and semantic layer metrics, three standard categories from dimensional modelling apply. Each maps to a different vault query pattern:
| Category | Description | Vault query pattern | DV source |
|---|
| Transactional | Atomic-grain fact; one row per business event; additive or non-additive | Direct join to link satellite for measures; no aggregation required | Link satellite (one row per UoW) |
| Periodic snapshot | Data aggregated to a defined reporting period (daily, monthly, quarterly); semi-additive measures (e.g. AVG balances, SUM volumes for the period) | Aggregate satellite rows within the reporting window using GROUP BY period | Satellite history + dv_applied_timestamp date truncation |
| Accumulating snapshot | Tracks a business process from initiation to completion; milestones are recorded progressively (e.g. loan origination: applied → approved → funded → closed) | PIT table with one row per business entity tracking the latest state at each milestone satellite | Hub + PIT table spanning multiple milestone satellites |
Design implication: transactional metrics are the simplest and most common IM pattern. Periodic snapshots need careful choice of aggregation function (avoid SUM on semi-additive measures like balances). Accumulating snapshots are the most complex — they require a PIT table that spans milestone satellites and often a bridge table when the process crosses hub boundaries.
