Building a Better Rules Data Platform on Snowflake for Government

The New Zealand Government's Better Rules for Government discovery report outlined a compelling vision: transform legislation, regulations, and policy into machine-consumable formats that digital services can interpret automatically. This is not just about digitisation — it is about encoding the intent of rules so that citizens, businesses, and government agencies can interact with rules as naturally as they interact with any modern digital service.

But here is the challenge: where does all the data live? How do you govern it? How do you enable real-time compliance checking across dozens of agencies while maintaining audit trails, versioning, and the ability to trace every decision back to its legislative source?

This post outlines a Snowflake-based data architecture designed specifically to support a government-wide Better Rules implementation.

---

The Vision: Rules as Code at Scale

The core concept from the discovery report is simple but transformative:

Encode government rules (legislation, regulations, policies) in machine-readable formats so they can be consumed by digital services automatically.

This enables:

---

Architecture Overview

Here is the proposed Snowflake architecture:

---

Layer 1: RAW Layer — Immutable Rule Ingestion

The RAW layer captures legislation and regulation data exactly as it arrives, preserving the original format for audit purposes.

-- Create transient database for raw data (no fail-safe, lower cost)
CREATE TRANSIENT DATABASE rules_raw;

-- Legislation as structured JSON
CREATE TABLE rules_raw.raw_legislation (
    legislation_id VARCHAR PRIMARY KEY,
    legislation_type VARCHAR,
    title VARCHAR,
    version_number INTEGER,
    effective_date DATE,
    source_url VARCHAR,
    raw_content VARIANT,
    checksum VARCHAR,
    loaded_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

Time-Travel for Legislative History

-- Enable 90-day time travel for legislation tables
ALTER TABLE rules_raw.raw_legislation 
SET DATA_RETENTION_TIME_IN_DAYS = 90;

-- Query legislation as it existed on a specific date
SELECT * FROM rules_raw.raw_legislation
AT(TIMESTAMP => '2025-04-01 00:00:00')
WHERE legislation_id = 'INCOME_TAX_ACT_2007';

---

Layer 2: RULES Engine Layer — Dimensional Model

This layer provides the authoritative, versioned rule definitions that power government services.

-- Dimension: All rules with full history
CREATE TABLE rules_engine.dim_rules (
    rule_key NUMBER PRIMARY KEY,
    rule_id VARCHAR,
    rule_version INTEGER,
    legislation_id VARCHAR,
    rule_code VARCHAR,
    rule_name VARCHAR,
    rule_type VARCHAR,
    is_active BOOLEAN,
    effective_from DATE,
    effective_to DATE,
    is_current BOOLEAN
)
CLUSTER BY (rule_id, effective_from);

Materialized View for Current Rules

CREATE MATERIALIZED VIEW rules_engine.mv_current_rules
AS
SELECT 
    r.rule_key,
    r.rule_id,
    r.rule_code,
    r.rule_name,
    r.rule_type
FROM rules_engine.dim_rules r
WHERE r.is_current = TRUE AND r.is_active = TRUE;

---

Layer 3: DECISIONS Layer — Audit Every Rule Evaluation

This is where government transparency meets data engineering. Every single rule evaluation is recorded with full traceability.

CREATE TABLE rules_decisions.fact_rule_evaluations (
    evaluation_id VARCHAR PRIMARY KEY,
    session_id VARCHAR,
    rule_id VARCHAR,
    rule_version INTEGER,
    evaluation_timestamp TIMESTAMP_NTZ,
    requesting_agency VARCHAR,
    requesting_service VARCHAR,
    input_entity_type VARCHAR,
    input_entity_id VARCHAR,
    input_parameters VARIANT,
    evaluation_result VARCHAR,
    output_values VARIANT,
    execution_time_ms INTEGER
)
CLUSTER BY (evaluation_timestamp, rule_id);

---

Streams and Tasks: Real-Time Rule Updates

When legislation changes, the platform must update immediately:

-- Stream for detecting new legislation
CREATE STREAM rules_raw.stream_legislation_changes
ON TABLE rules_raw.raw_legislation;

-- Task: Process new legislation every 5 minutes
CREATE TASK rules_engine.process_legislation_task
WAREHOUSE = 'rules_wh'
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('rules_raw.stream_legislation_changes')
AS
BEGIN
    INSERT INTO rules_staging.stg_rules_parsed
    SELECT * FROM rules_raw.stream_legislation_changes
    WHERE METADATA$ACTION = 'INSERT';
END;

---

Governance and Compliance Features

Row-Level Security for Multi-Agency Access

CREATE ROW ACCESS POLICY rules_engine.agency_access_policy
AS (owning_agency VARCHAR) RETURNS BOOLEAN ->
    CURRENT_ROLE() = 'GOVERNMENT_ADMIN_ROLE'
    OR owning_agency = CURRENT_USER_AGENCY();

ALTER TABLE rules_engine.dim_rules
ADD ROW ACCESS POLICY agency_access_policy ON (owning_agency);

Column Masking for Sensitive Data

CREATE MASKING POLICY rules_governance.mask_entity_id
AS (val VARCHAR) RETURNS VARCHAR ->
    CASE
        WHEN CURRENT_ROLE() IN ('AUDITOR_ROLE', 'PRIVACY_OFFICER_ROLE') THEN val
        ELSE 'ENTITY_' || SHA2(val, 256)
    END;

ALTER TABLE rules_decisions.fact_rule_evaluations
MODIFY COLUMN input_entity_id SET MASKING POLICY mask_entity_id;

---

Architecture Benefits Summary

---

Putting It All Together

The Better Rules for Government vision requires a platform that treats legislation as data — versioned, queryable, and traceable. Snowflake's unique combination of:

• Time-travel for legislative history
• Streams and Tasks for real-time rule propagation
• Row Access Policies for multi-agency governance
• VARIANT types for flexible rule definitions
• Materialized Views for fast rule lookups
• Complete audit logging for transparency

makes it an ideal foundation for government rules-as-code initiatives.

---

The future of government is digital, transparent, and data-driven. The platform you build today determines whether that future works for everyone.