US Department of Labor (DOL)

DOL

Overview

The US Department of Labor (DOL) requires all large (>100 people) US employers that provide benefits to complete Form 5500. The Form 5500 filing includes data on all insurance plans (e.g. medical, healthcare, annuity) offered by employers (i.e. sponsor), the carrier (i.e. insurance company), and contractual information such as payments made to brokers and the effective and expiration dates.

Example topics covered:

Broker <> carrier relationships
Employer <> carrier relationships
Sponsor name, address, phone number
Broker name and address
Fees and commissions paid to broker
Plan name, type, active dates
Number of employees covered

The Department of Labor publishes weekly unemployment insurance claims at the state and national level. Commonly referred to as "Jobless Claims," the report measures the number of people filing for unemployment insurance in the United States. Claims are broken down into two categories: initial and continued.

Example topics covered:

Initial unemployment insurance filings - Number of people filing for unemployment benefits for the first time
Continued unemployment insurance filings - Number of people filing for ongoing unemployment benefits

Key Attributes

As with all Public Domain datasets, Cybersyn aims to release data on Snowflake Marketplace as soon as the underlying source releases new data. We check periodically for changes to the underlying source and, upon detecting a change, propagate the data to Snowflake Marketplace immediately. See our release process for more details.

Description

EAV Model: All Cybersyn products follow the EAV (entity, attributes, value) model with a unified schema. Entities are tangible objects (e.g. geography, company) that Cybersyn provides data on. Index tables contain all entities of a certain type. Timeseries tables contain all timeseries' dates and values that refer to an entity type. Additional tables, such as the relationships table and attributes table, are used to describe the entities and timeseries. Data is joinable across all Cybersyn products that have a GEO_ID. Refer to Cybersyn Concepts for more details

Form 5500: Form 5500 filings can have multiple Schedule As. Schedule As provide overviews of the policies associated with a company's filing. Schedule A Part 1 gives insight into the brokers associated with each insurance policy and provides commission and fee amounts received by a broker. Since 5500 filings can have multiple Schedule As attached to it, there can be multiple brokers associated with a single Form 5500.

US_DEPARTMENT_OF_LABOR_FORM_5500_BROKER_INDEX provides a wide format overview of brokers (BROKER_ID) associated with particular policies (INSURANCE_POLICY_ID) and Form 5500 filings (ACK_ID). The table demonstrates the relationship between a broker (BROKER_ID), an insurance policy (INSURANCE_POLICY_ID), and a Form 5500 filing (ACK_ID: acknowledgement ID). It provides commission and fee amounts received by a broker for an insurance policy as well as broker address, classification, and notes pertaining to compensation.

US_DEPARTMENT_OF_LABOR_FORM_5500_FILING_INDEX provides a wide format overview of filings (ACK_ID) of Form 5500 by company sponsor. This table represents each unique filing of the Form 5500. It provides detailed information on the sponsor company and the filing specifications.

US_DEPARTMENT_OF_LABOR_FORM_5500_POLICY_INDEX provides a wide format overview of each insurance policy (INSURANCE_POLICY_ID). Since a Form 5500 filing can have multiple Schedule As attached to it, there is a many-to-one relationship between INSURANCE_POLICY_ID found in the Schedule As for a sponsor company and the ACK_ID (acknowledgement ID representing the Form 5500 filing). The table provides information on each insurance policy provided by a sponsor company. It details the policy active period, the insurance carrier, insurance type, and the number of employees covered by the policy.

Tables & Sources

Tables

Sources

Cybersyn Products

Tables above are available in the following Cybersyn data products:

US Healthcare & Insurance (Form 5500)
Global Government (Unemployment Insurance)
Enterprise (Unemployment Insurance)

Examples & Sample Queries

Companies using specific insurance providers

Find Texas-based companies offering Bluecross Blueshield health insurance.

SELECT
    sponsor_name,
    sponsor_ein,
    SUM(employees_covered_eoy) AS count_covered_employees
FROM cybersyn.us_department_of_labor_form_5500_policy_idx AS policy
JOIN cybersyn.us_department_of_labor_form_5500_filing_idx AS filing
  ON (policy.ack_id = filing.ack_id)
WHERE YEAR(policy.policy_end_date) = 2021
  AND filing.sponsor_state = 'TX'
  AND ARRAY_CONTAINS('Health Insurance'::VARIANT, insurance_types)
  AND insurance_carrier_name ILIKE 'BLUECROSS BLUESHIELD%'
GROUP BY sponsor_name, sponsor_ein
ORDER BY count_covered_employees DESC NULLS LAST
LIMIT 500;

Insurance carrier penetration by industry, market, and type of insurance

Show the top life insurance providers in Florida that serve companies in the Educational Services industry.

SELECT
    insurance_carrier_name,
    COUNT(DISTINCT sponsor_ein) AS count_sponsors,
    SUM(employees_covered_eoy) AS count_covered_employees
FROM cybersyn.us_department_of_labor_form_5500_policy_idx AS policy
JOIN cybersyn.us_department_of_labor_form_5500_filing_idx AS filing
  ON (policy.ack_id = filing.ack_id)
WHERE YEAR(policy.policy_end_date) = 2021
  AND filing.sponsor_state = 'FL'
  AND ARRAY_CONTAINS('Life Insurance'::VARIANT, insurance_types)
  AND filing.sponsor_naics_description = 'Educational Services'
GROUP BY insurance_carrier_name
ORDER BY count_covered_employees DESC NULLS LAST;

Disclaimers

The data in this product is sourced from US Department of Labor (DOL). Cybersyn is not endorsed by or affiliated with any of these providers. Contact support@cybersyn.com for questions.

Last updated 18 days ago