Data Warehouse Share Customer Guide (v2)

Welcome to the Aclaimant Data Warehouse Share (DWS). This guide explains every table available in your Snowflake share, how they relate to each other, and common analytical patterns you can build on top of your data.

Getting Started

What is the Data Warehouse Share?

The DWS is a Snowflake Secure Data Share that gives you read-only access to your Aclaimant data in your own Snowflake account. Data refreshes automatically — no ETL pipelines to maintain.

Connecting to Your Share

1. Accept the share invitation in your Snowflake account
2. Create a database from the share: CREATE DATABASE aclaimant_dws FROM SHARE <share_name>;
3. Grant access to your roles as needed
4. All tables are views in the shared database — query them like any other Snowflake table

Key Concepts

• Incident: The core record — a reported event (injury, property damage, auto accident, near miss, etc.)
• Claim: A formal insurance claim filed against an incident, linked to a policy and line of coverage
• Event: A timeline entry on an incident (e.g., "Return to Work", "Doctor Visit", "Fatality")
• Safety Report: Safety inspection/audit records
• Company: An organizational unit in your hierarchy

Data Model & Table Reference

Entity Relationship Overview

companies
  └── incidents (company_id)
        ├── events (incident_id)
        ├── claims (incident_id)
        │     └── claim_extensions (claim_id) — detailed claim fields
        │           └── loss_runs (claim_id) — financial transactions
        ├── witnesses (incident_id)
        ├── notes (incident_id)
        ├── tasks (incident_id)
        ├── files (incident_id)
        └── safety_reports (incident_id)

policies
  └── policy_periods (policy_id)
        └── lines_of_coverage (policy_period_id)
              └── claims (line_of_coverage_id)

Core Tables

incidents

The primary record for any reported event. One row per incident.

events

Timeline entries on incidents. Used for tracking Return to Work, doctor visits, fatalities, and other milestones.

<aside>
⚠️

Events must be explicitly enabled for your share. Contact your Aclaimant representative if you don't see this table.

</aside>

claims

Insurance claims filed against incidents.

claims_acl_std_format (Claim Extensions)

Extended claim detail in Aclaimant's standardized format. Contains 400+ fields covering financials, medical, legal, and OSHA data. One row per claim.

Key field groups include:

• Financials: indemnity_payment, medical_payment, indemnity_reserves, medical_reserves, total_incurred, total_paid, etc.
• OSHA: osha_recordable, osha_administration_code, osha_description, osha_privacy_case, osha_sharps_case
• Lost Time: lost_time, lost_days, lost_work_days, restricted_days, restricted_work_days, return_to_work_date
• Medical: body_part, nature_of_injury, cause_of_injury, body_side, medical_facility_*
• Employment: date_of_hire, job_classification, employment_status, pay_rate, hours_worked_per_week

loss_runs

Financial transaction history for claims. Multiple rows per claim — one per transaction entry.

policies, policy_periods, lines_of_coverage

Insurance policy hierarchy. Policies contain time-bounded periods, which contain specific coverage lines (e.g., "Workers' Compensation", "General Liability").

Supporting Tables

Common Analytical Patterns

1. OSHA Days Away / Restricted Time Calculations

This is the most-requested calculation. The platform computes OSHA days away (DAFW) and days of job transfer/restriction (DJTR) from the events timeline on each incident. Here's how to replicate it:

How Days Away From Work (DAFW) is Calculated

1. Find the first event where type = 'Return to Work' AND category = 'Full Duty' for each incident
2. Count calendar days from the day after incidents.occurred_at to that event's event_date
3. Cap at 180 days (OSHA maximum)
4. If a Fatality event exists, the incident is classified as a death — not DAFW

How Days of Job Transfer/Restriction (DJTR) is Calculated

1. Find the first event where type = 'Return to Work' AND category = 'Modified Duty'
2. Count calendar days from that date to the first Full Duty date (or today if still ongoing)
3. Cap at 180 days

OSHA Case Classification (Form 300A columns G-J)

Sample SQL

WITH first_full_duty AS (
    SELECT
        e.incident_id,
        e.event_date AS first_full_duty_date,
        ROW_NUMBER() OVER (
            PARTITION BY e.incident_id
            ORDER BY e.event_date, e.event_num
        ) AS rn
    FROM events e
    WHERE e.type = 'Return to Work'
        AND e.category = 'Full Duty'
),

first_modified_duty AS (
    SELECT
        e.incident_id,
        e.event_date AS first_modified_duty_date,
        ROW_NUMBER() OVER (
            PARTITION BY e.incident_id
            ORDER BY e.event_date, e.event_num
        ) AS rn
    FROM events e
    WHERE e.type = 'Return to Work'
        AND e.category = 'Modified Duty'
),

fatalities AS (
    SELECT DISTINCT incident_id
    FROM events
    WHERE category = 'Fatality'
)

SELECT
    i.id AS incident_id,
    i.case_number,
    i.occurred_at,
    i.osha_recordable,

    fd.first_full_duty_date,
    md.first_modified_duty_date,

    -- Days Away From Work
    CASE
        WHEN f.incident_id IS NOT NULL THEN NULL
        WHEN fd.first_full_duty_date IS NOT NULL
            THEN LEAST(
                DATEDIFF('day', i.occurred_at::DATE, fd.first_full_duty_date) - 1,
                180
            )
    END AS days_away_from_work,

    -- Days of Job Transfer or Restriction
    CASE
        WHEN f.incident_id IS NOT NULL THEN NULL
        WHEN md.first_modified_duty_date IS NOT NULL
            AND fd.first_full_duty_date IS NOT NULL
            THEN LEAST(
                DATEDIFF('day', md.first_modified_duty_date, fd.first_full_duty_date),
                180
            )
        WHEN md.first_modified_duty_date IS NOT NULL
            THEN LEAST(
                DATEDIFF('day', md.first_modified_duty_date, CURRENT_DATE),
                180
            )
    END AS days_restricted_or_transferred,

    -- Case Classification
    CASE
        WHEN f.incident_id IS NOT NULL THEN 'Death'
        WHEN fd.first_full_duty_date IS NOT NULL
            AND DATEDIFF('day', i.occurred_at::DATE, fd.first_full_duty_date) > 1
            THEN 'Days Away From Work'
        WHEN md.first_modified_duty_date IS NOT NULL
            THEN 'Job Transfer or Restriction'
        ELSE 'Other Recordable Cases'
    END AS osha_case_classification

FROM incidents i
LEFT JOIN first_full_duty fd
    ON fd.incident_id = i.id AND fd.rn = 1
LEFT JOIN first_modified_duty md
    ON md.incident_id = i.id AND md.rn = 1
LEFT JOIN fatalities f
    ON f.incident_id = i.id
WHERE i.osha_recordable = TRUE;

<aside>
ℹ️

Important: This simplified SQL covers the core logic. The platform's full calculation also considers time zones, state-of-jurisdiction rules, and event ordering nuances. For most reporting purposes, this will match. If you see discrepancies on specific incidents, contact support.

</aside>

2. OSHA 300A Summary Statistics

-- Assuming you've built the days_away query above as a CTE or view called osha_calcs

SELECT
    YEAR(i.occurred_at) AS reporting_year,

    COUNT_IF(osha_case_classification = 'Death') AS total_deaths,
    COUNT_IF(osha_case_classification = 'Days Away From Work') AS total_dafw_cases,
    COUNT_IF(osha_case_classification = 'Job Transfer or Restriction') AS total_djtr_cases,
    COUNT_IF(osha_case_classification = 'Other Recordable Cases') AS total_other_cases,

    SUM(COALESCE(days_away_from_work, 0)) AS total_days_away,
    SUM(COALESCE(days_restricted_or_transferred, 0)) AS total_days_restricted

FROM osha_calcs
WHERE osha_recordable = TRUE
GROUP BY YEAR(occurred_at);

3. Incident Frequency Rates

-- OSHA Total Recordable Incident Rate (TRIR)
-- TRIR = (Number of recordable incidents × 200,000) / Total hours worked

SELECT
    YEAR(occurred_at) AS year,
    company_id,
    COUNT_IF(osha_recordable = TRUE) AS recordable_count
    -- You'll need to supply total_hours_worked from your HRIS/payroll system
    -- (recordable_count * 200000.0) / total_hours_worked AS trir
FROM incidents
GROUP BY 1, 2;

4. Claims Financial Summary

SELECT
    i.company_id,
    c.status AS claim_status,
    loc.name AS line_of_coverage,
    COUNT(*) AS claim_count,
    SUM(ce.total_incurred) AS total_incurred,
    SUM(ce.total_paid) AS total_paid,
    SUM(ce.indemnity_reserves) AS indemnity_reserves,
    SUM(ce.medical_reserves) AS medical_reserves
FROM claims c
JOIN incidents i ON i.id = c.incident_id
JOIN claims_acl_std_format ce ON ce.claim_id = c.id
LEFT JOIN lines_of_coverage loc ON loc.id = c.line_of_coverage_id
GROUP BY 1, 2, 3;

5. Incident Trend Analysis

SELECT
    DATE_TRUNC('month', occurred_at) AS month,
    type AS incident_type,
    status,
    COUNT(*) AS incident_count
FROM incidents
GROUP BY 1, 2, 3
ORDER BY 1 DESC;

6. Open Task Aging

SELECT
    t.incident_id,
    i.case_number,
    t.title AS task_title,
    t.status AS task_status,
    t.due_date,
    DATEDIFF('day', t.due_date, CURRENT_DATE) AS days_overdue
FROM tasks t
JOIN incidents i ON i.id = t.incident_id
WHERE t.status != 'completed'
    AND t.due_date < CURRENT_DATE
ORDER BY days_overdue DESC;

Tips & Best Practices

Joining Tables

• Always join through incident_id as the primary key for incident-centric analysis
• Use company_id to scope queries to a specific organizational unit
• Claims link to incidents via incident_id and to policies via line_of_coverage_id

Performance

• Filter by company_id early in your queries if you have a large multi-company hierarchy
• Use occurred_at date filters to limit scans on large datasets
• Materialized views in your Snowflake account can speed up frequently-run dashboards

Data Freshness

• The share refreshes on a scheduled cadence (typically daily)
• created_at and updated_at timestamps let you track when records changed
• For the most current data, always query the share directly rather than copying to local tables

Custom Fields

• Incident and safety report custom fields appear as additional columns
• Column names are derived from your Aclaimant field configuration
• Custom field availability varies by customer — check your schema for the full list

Requesting Changes

Enable Additional Tables

Some tables (events, witnesses, notes, tasks, files) are not enabled by default. Contact your Aclaimant representative to add them to your share.

Custom Fields

If you need additional custom fields exposed in the warehouse, let us know which fields and which table they belong to.

Questions?

Reach out to your Aclaimant Customer Experience Manager or email support@aclaimant.com.