Imported from PatrickCuba/the_data_must_flow (dvos-skills/skills/dv-supernova/SKILL.md). Install upstream with npx skills add PatrickCuba/the_data_must_flow --skill dv-supernova. Copyright stays with the author.

/dv-supernova — Supernova Data Modelling Pattern

Supernova (Rick F. van der Lans, ~2015) reorganises the Data Vault above the raw vault layer into a pre-materialised wide format. On Snowflake this means INCREMENTAL Dynamic Tables — refreshed automatically as vault data changes, with no query-time joins in the delivery layer.

The vault remains the auditable source of record. Supernova is a derivative layer: it can be dropped and rebuilt at any time without data loss.

The 5-layer framework

Layers 3–5 live in a dedicated supernova schema.

When to choose Supernova over PIT + IM view

Decision questions

Before generating Supernova artefacts, confirm:

1. Which hub(s)? One set of Supernova DTs per hub.
2. Which link(s)? Links get a simplified Supernova DT (no versions DT unless they have adjacent satellites).
3. Which adjacent satellites? Include both RV and BV satellites. Satellites with type: nh (non-historised) can be included — they have no hashdiff comparison so the LEAD window is simpler.
4. Does the hub have no satellites yet? If yes, use the simplified versions DT (hub applied date only).
5. Target lag for Supernova DT? Default '1 minute'. Versions DT is always DOWNSTREAM.
6. Computed attributes for Layer 4? Common examples: balance tiers, age buckets, currency conversion, standardised column names, filtered flags.
7. Layer 5 delivery shapes needed? Filtered view (multi-tenancy), aggregate view, OBT (one big table), or star schema.

Naming rules

All Supernova objects live in the supernova schema. Names are lowercase.

Layer 5 delivery objects live in information_marts or a dedicated delivery schema.

Layer 3 — Versions DT (hub with satellites)

The versions DT is the time spine — a deduplicated UNION ALL of every satellite's dv_applieddate for each entity, with enddate computed as LEAD(startdate) - 1 second.

-- Step 3a: time spine — one SELECT per adjacent satellite
CREATE OR REPLACE DYNAMIC TABLE supernova.dt_<hub>_versions
    TARGET_LAG = DOWNSTREAM
    WAREHOUSE  = <wh>
AS
WITH twine AS (
    SELECT dv_tenantid, dv_hashkey_<hub>, dv_applieddate AS startdate
    FROM <vault_schema>.<sat_1>
    WHERE dv_recsource <> 'GHOST'

    UNION ALL

    SELECT dv_tenantid, dv_hashkey_<hub>, dv_applieddate AS startdate
    FROM <vault_schema>.<sat_2>
    WHERE dv_recsource <> 'GHOST'

    -- add one UNION ALL block per additional adjacent satellite
),
group_by AS (
    SELECT dv_tenantid, dv_hashkey_<hub>, startdate
    FROM twine
    GROUP BY 1, 2, 3
)
SELECT
    hub.<bk_column>,
    grp.dv_tenantid,
    grp.dv_hashkey_<hub>,
    grp.startdate,
    COALESCE(
        DATEADD(seconds, -1,
            LEAD(grp.startdate) OVER (PARTITION BY grp.dv_hashkey_<hub> ORDER BY grp.startdate)
        ),
        TO_TIMESTAMP('9999-12-31 23:59:59')
    ) AS enddate
FROM group_by grp
INNER JOIN <vault_schema>.<hub> hub
    ON grp.dv_hashkey_<hub> = hub.dv_hashkey_<hub>;

Layer 3 — Versions DT (hub with no satellites)

When a hub has no adjacent satellites yet, use the hub's own dv_applieddate as the time spine. enddate is always 9999-12-31 (no version changes until satellites are added).

CREATE OR REPLACE DYNAMIC TABLE supernova.dt_<hub>_versions
    TARGET_LAG = DOWNSTREAM
    WAREHOUSE  = <wh>
AS
SELECT
    <bk_column>,
    dv_tenantid,
    dv_hashkey_<hub>,
    dv_applieddate                          AS startdate,
    TO_TIMESTAMP('9999-12-31 23:59:59')     AS enddate
FROM <vault_schema>.<hub>;

Layer 3 — Supernova hub DT

Joins the versions DT to each satellite using an equi-join on sat.dv_applieddate = versions.startdate. Each satellite gets a leaf CTE that adds dv_applieddate_end via LEAD.

Critical rule — equi-join required for INCREMENTAL refresh: Snowflake's incremental DT refresh mode requires equi-joins between the upstream DTs. A range join (BETWEEN startdate AND enddate) forces a full rebuild on every refresh. The versions DT time spine converts temporal alignment into a set of equi-joins, making incremental refresh possible.

-- Step 3b: wide pre-join — one leaf CTE per satellite
CREATE OR REPLACE DYNAMIC TABLE supernova.dt_supernova_<hub>
    TARGET_LAG = '<lag>'     -- default '1 minute'
    WAREHOUSE  = <wh>
AS
WITH leaf_<sat_1> AS (
    SELECT
        s.*,
        COALESCE(
            LEAD(s.dv_applieddate) OVER (PARTITION BY s.dv_hashkey_<hub>
                                         ORDER BY s.dv_applieddate),
            TO_TIMESTAMP('9999-12-31 23:59:59')
        ) AS dv_applieddate_end
    FROM <vault_schema>.<sat_1> s
),
leaf_<sat_2> AS (
    SELECT
        s.*,
        COALESCE(
            LEAD(s.dv_applieddate) OVER (PARTITION BY s.dv_hashkey_<hub>
                                         ORDER BY s.dv_applieddate),
            TO_TIMESTAMP('9999-12-31 23:59:59')
        ) AS dv_applieddate_end
    FROM <vault_schema>.<sat_2> s
)
-- add one leaf CTE per additional satellite

SELECT
    hub.dv_tenantid,
    hub.dv_hashkey_<hub>,
    hub.startdate,
    hub.enddate,
    hub.<bk_column>,
    -- satellite 1 columns
    s1.<col_a>, s1.<col_b>,
    -- satellite 2 columns
    s2.<col_c>, s2.<col_d>
    -- add columns per satellite
FROM supernova.dt_<hub>_versions hub
LEFT JOIN leaf_<sat_1> s1
    ON  hub.dv_hashkey_<hub>  = s1.dv_hashkey_<hub>
    AND s1.dv_applieddate     = hub.startdate       -- equi-join: no range join
LEFT JOIN leaf_<sat_2> s2
    ON  hub.dv_hashkey_<hub>  = s2.dv_hashkey_<hub>
    AND s2.dv_applieddate     = hub.startdate;

Layer 3 — Supernova link DT

Links with no adjacent satellites (just recording the relationship):

CREATE OR REPLACE DYNAMIC TABLE supernova.dt_<link>_versions
    TARGET_LAG = DOWNSTREAM
    WAREHOUSE  = <wh>
AS
SELECT
    dv_tenantid,
    dv_hashkey_<link>,
    dv_hashkey_<hub_a>,
    dv_hashkey_<hub_b>,
    dv_applieddate   AS startdate,
    TO_TIMESTAMP('9999-12-31 23:59:59') AS enddate
FROM <vault_schema>.<link>;

CREATE OR REPLACE DYNAMIC TABLE supernova.dt_supernova_<link>
    TARGET_LAG = '<lag>'
    WAREHOUSE  = <wh>
AS
SELECT
    lnk.dv_tenantid,
    lnk.dv_hashkey_<link>,
    lnk.dv_hashkey_<hub_a>,
    lnk.dv_hashkey_<hub_b>,
    lnk.startdate
FROM supernova.dt_<link>_versions lnk;

For links with adjacent satellites, apply the same leaf CTE + equi-join pattern as the hub Supernova DT, partitioned by dv_hashkey_<link> instead.

Layer 4 — Extended Supernova DT

Adds computed attributes, column renames, and lightweight business rules. Always built on top of the Supernova DT — never directly on vault tables.

-- Step 4: computed attributes on top of the Supernova DT
CREATE OR REPLACE DYNAMIC TABLE supernova.dt_xsn_supernova_<hub>
    TARGET_LAG = '<lag>'
    WAREHOUSE  = <wh>
AS
SELECT
    *,
    -- examples of computed attributes:
    CASE
        WHEN <metric_col> > 50000 THEN 'high'
        WHEN <metric_col> > 10000 THEN 'medium'
        ELSE 'low'
    END                                     AS <tier_column>,
    DATEDIFF(year, <date_col>, CURRENT_DATE) AS <age_column>
FROM supernova.dt_supernova_<hub>;

Common Layer 4 patterns:

• Tier/band classification (balance tiers, risk bands, age groups)
• Standardised column names (rename source-specific columns to canonical business terms)
• Currency conversion columns
• dv_tenantid-based flags for multi-tenancy filtering in Layer 5

Layer 5 — Data Delivery

Layer 5 shapes the Extended Supernova DT for specific BI tools, consumers, or use cases. dv_tenantid is available throughout for row access policy filtering.

Filtered view (multi-tenancy)

-- Row-access-policy-friendly: dv_tenantid carried through from Layer 2
CREATE OR REPLACE VIEW information_marts.v_filtered_<hub> AS
SELECT * FROM supernova.dt_xsn_supernova_<hub>
WHERE dv_tenantid = '<tenant>';  -- or replace with row access policy attachment

Aggregate view

CREATE OR REPLACE VIEW information_marts.v_agg_<hub>_<period> AS
SELECT
    DATE_TRUNC('month', startdate) AS period,
    <bk_column>,
    SUM(<metric_col>)              AS total_<metric>,
    COUNT(*)                       AS event_count
FROM supernova.dt_xsn_supernova_<hub>
GROUP BY 1, 2;

One Big Table (OBT)

-- Flat wide join of hub supernova + linked entity supernova
CREATE OR REPLACE VIEW information_marts.v_obt_<subject> AS
SELECT
    sn_hub.<bk_column>,
    sn_hub.startdate,
    sn_hub.enddate,
    sn_hub.<cols_hub>,
    sn_lnk.<cols_link>,
    sn_related_hub.<cols_related_hub>
FROM supernova.dt_xsn_supernova_<hub>          sn_hub
LEFT JOIN supernova.dt_supernova_<link>         sn_lnk
    ON sn_hub.dv_hashkey_<hub> = sn_lnk.dv_hashkey_<hub>
LEFT JOIN supernova.dt_xsn_supernova_<hub_b>    sn_related_hub
    ON sn_lnk.dv_hashkey_<hub_b> = sn_related_hub.dv_hashkey_<hub_b>
    AND sn_lnk.startdate         = sn_related_hub.startdate;

Star schema view

-- Dimension: hub supernova current state
CREATE OR REPLACE VIEW information_marts.dim_<entity> AS
SELECT <bk_column>, <descriptive_cols>
FROM supernova.dt_xsn_supernova_<hub>
WHERE enddate = TO_TIMESTAMP('9999-12-31 23:59:59');  -- current rows only

-- Fact: link supernova + measures
CREATE OR REPLACE VIEW information_marts.fact_<subject> AS
SELECT
    sn_lnk.startdate,
    hub_a.<bk_column_a>,
    hub_b.<bk_column_b>,
    sn_lnk.<measure_cols>
FROM supernova.dt_supernova_<link>       sn_lnk
JOIN supernova.dt_supernova_<hub_a>      hub_a
    ON sn_lnk.dv_hashkey_<hub_a> = hub_a.dv_hashkey_<hub_a>
JOIN supernova.dt_supernova_<hub_b>      hub_b
    ON sn_lnk.dv_hashkey_<hub_b> = hub_b.dv_hashkey_<hub_b>;

Dynamic Table pipeline management

-- Check all Supernova DTs and their refresh status
SHOW DYNAMIC TABLES IN SCHEMA supernova;

-- Retrieve via result_scan for programmatic access
SET supernova_dts = (SELECT LAST_QUERY_ID());
SELECT "name", "target_lag", "refresh_mode", "scheduling_state"
FROM TABLE(RESULT_SCAN($supernova_dts));

-- Suspend all Supernova DTs (e.g. for maintenance)
ALTER DYNAMIC TABLE supernova.dt_<hub>_versions SUSPEND;
ALTER DYNAMIC TABLE supernova.dt_supernova_<hub> SUSPEND;
ALTER DYNAMIC TABLE supernova.dt_xsn_supernova_<hub> SUSPEND;

-- Resume
ALTER DYNAMIC TABLE supernova.dt_xsn_supernova_<hub> RESUME;
ALTER DYNAMIC TABLE supernova.dt_supernova_<hub> RESUME;
ALTER DYNAMIC TABLE supernova.dt_<hub>_versions RESUME;

Resume order: versions DT first, then supernova DT, then XSN DT. Snowflake respects dependency ordering but resuming from the leaf upward avoids transient errors.

Subagent files

• SQL Generator: agents/sql-generator.md — Supernova DT templates
• Doctrine Enforcer: agents/doctrine-enforcer.md — validate no hash keys in Layer 5