Data Warehouse Share - Customer Guide (v3)

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

• Workflow: The core organizational unit in v3. Each workflow (e.g., "Workers' Comp Incident", "Auto Incident", "Safety Inspection") gets its own table in your share, named after the workflow.
• Claim: A formal insurance claim filed against a workflow record, linked to a policy and line of coverage.
• Event: A timeline entry on a workflow record (e.g., "Return to Work", "Doctor Visit", "Fatality"). Used for OSHA days-away calculations.
• Subject: The person, property, or vehicle information involved in a workflow record. Carries OSHA eligibility and recordability flags.
• Company: An organizational unit in your hierarchy.

Data Model & Table Reference

Entity Relationship Overview

companies (company_id)
  └── <workflow tables> (company_id)
        ├── events (parent_model_id → model_id)
        ├── subjects (parent_model_id → model_id)
        ├── claims (incident_id → id)
        │     ├── claims_acl_std_format (claim_id) — detailed claim fields
        │     ├── loss_runs (claim_id) — financial snapshots
        │     └── transactions (claim_id) — financial transactions
        ├── notes (parent_model_id → model_id)
        └── tasks (parent_model_id → model_id)

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

users — user directory

Workflow Tables

In v3, each workflow you've configured in Aclaimant gets its own dedicated table. The table name is derived from the workflow title (e.g., workers_comp_incident_workflow, auto_incident_report). Your share may include one or several of these depending on your configuration.

Every workflow table shares a common set of columns, plus workflow-specific columns for each question/field defined in that workflow.

Common Columns (all workflow tables)

Workflow-Specific Columns

Beyond the common columns above, each workflow table includes columns for every question defined in the workflow. Column names are derived from the question keys (slugified to uppercase with underscores). These vary by workflow and by customer configuration.

For example, a Workers' Comp workflow might include columns like BODY_PART, NATURE_OF_INJURY, CAUSE_OF_INJURY, SUPERVISOR_NAME, DATE_OF_HIRE, etc.

Run SHOW COLUMNS IN TABLE <workflow_table> to discover all available columns.

Core Shared Tables

companies

Your organizational hierarchy. Every other table references company_id to scope data.

claims

Insurance claims filed against workflow records.

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").

transactions

Financial transaction records for claims. Filtered to claims in your share — each row is an individual transaction entry (reserves, payments, etc.) on a claim.

Joining: transactions.claim_id = claims.id

users

User directory for your organization.

Module-Dependent & Optional Tables

<aside>
⚠️

Not all tables appear in every share. Several tables depend on which Aclaimant modules are enabled for your account, and some require explicit opt-in. If you expect to see a table below but don't, contact your Aclaimant representative to confirm module availability and request enablement.

</aside>

events

Timeline entries on workflow records. Used for tracking Return to Work, doctor visits, fatalities, and other milestones. Required for OSHA days-away calculations.

Module: Events must be enabled for your account and explicitly added to your share.

Joining events to workflow records: events.parent_model_id = <workflow_table>.model_id

subjects

People involved in workflow records. Each subject carries OSHA-relevant flags.

Module: Available on all v3 accounts with incident-type workflows.

Joining subjects to workflow records: subjects.parent_model_id = <workflow_table>.model_id

notes

Free-text notes attached to workflow records. Module: Notes must be explicitly enabled for your share.

Joining: notes.parent_model_id = <workflow_table>.model_id

tasks

Workflow tasks (assignments, follow-ups, reminders) on workflow records. Module: Tasks must be explicitly enabled for your share.

Joining: tasks.parent_model_id = <workflow_table>.model_id

Module Summary

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. Here's how to replicate it in SQL.

How Days Away From Work (DAFW) is Calculated

1. Find the first event where type = 'Return to Work' AND category = 'Full Duty' for each workflow record
2. Count calendar days from the day after the incident's occurred_at date to that event's event_date
3. Cap at 180 days (OSHA maximum)
4. If a Fatality event exists, the record 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)

Each OSHA-recordable case falls into exactly one category:

Sample SQL

In the example below, replace <your_workflow_table> with your actual workflow table name (e.g., workers_comp_incident_workflow).

WITH first_full_duty AS (
    SELECT
        e.parent_model_id,
        e.event_date AS first_full_duty_date,
        ROW_NUMBER() OVER (
            PARTITION BY e.parent_model_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.parent_model_id,
        e.event_date AS first_modified_duty_date,
        ROW_NUMBER() OVER (
            PARTITION BY e.parent_model_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 parent_model_id
    FROM events
    WHERE category = 'Fatality'
)

SELECT
    w.id,
    w.identifier,
    w.occurred_at,
    s.osha_recordable,

    fd.first_full_duty_date,
    md.first_modified_duty_date,

    -- Days Away From Work
    CASE
        WHEN f.parent_model_id IS NOT NULL THEN NULL  -- fatality
        WHEN fd.first_full_duty_date IS NOT NULL
            THEN LEAST(
                DATEDIFF('day', w.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.parent_model_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.parent_model_id IS NOT NULL THEN 'Death'
        WHEN fd.first_full_duty_date IS NOT NULL
            AND DATEDIFF('day', w.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 <your_workflow_table> w
LEFT JOIN subjects s
    ON s.parent_model_id = w.model_id
LEFT JOIN first_full_duty fd
    ON fd.parent_model_id = w.model_id AND fd.rn = 1
LEFT JOIN first_modified_duty md
    ON md.parent_model_id = w.model_id AND md.rn = 1
LEFT JOIN fatalities f
    ON f.parent_model_id = w.model_id
WHERE s.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 records, contact support.

</aside>

2. OSHA 300A Summary Statistics

Build the annual summary statistics for Form 300A:

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

SELECT
    YEAR(occurred_at) AS reporting_year,

    -- Columns G-J: Number of cases
    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,

    -- Day counts
    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
GROUP BY YEAR(occurred_at);

3. Incident Frequency Rates

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

SELECT
    YEAR(w.occurred_at) AS year,
    w.company_id,
    COUNT_IF(s.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 <your_workflow_table> w
LEFT JOIN subjects s ON s.parent_model_id = w.model_id
GROUP BY 1, 2;

4. Claims Financial Summary

SELECT
    w.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 <your_workflow_table> w ON w.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. Record Trend Analysis

SELECT
    DATE_TRUNC('month', reported_at) AS month,
    workflow_title,
    status,
    COUNT(*) AS record_count
FROM <your_workflow_table>
GROUP BY 1, 2, 3
ORDER BY 1 DESC;

6. Open Task Aging

SELECT
    t.parent_model_id,
    w.identifier,
    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 <your_workflow_table> w ON w.model_id = t.parent_model_id
WHERE t.status != 'completed'
    AND t.due_date < CURRENT_DATE
ORDER BY days_overdue DESC;

Tips & Best Practices

Joining Tables

In v3, the key join pattern depends on the table:

Use company_id to scope queries to a specific organizational unit.

Performance

• Filter by company_id early in your queries if you have a large multi-company hierarchy
• Use reported_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

Workflow-Specific Columns

• Each workflow table includes columns for every question in the workflow
• Column names are slugified from the question labels
• Run SHOW COLUMNS IN TABLE <workflow_table> to discover available columns
• If a workflow question is added, the new column will appear on the next refresh
• If a workflow question is renamed or removed, the old column is preserved (empty/null) so existing reports are not broken. A new column is created for the renamed question

Requesting Changes

Enable Additional Tables

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

Additional Workflows

If you have workflows that aren't yet in your share, let us know which ones to add.

Questions?

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