US Department of Labor (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
| Geographic Coverage | United States |
| Entity Level | Form 5500 - Broker ID, Policy ID, Form 5500 ID, Company Sponsor |
| Release Frequency | Form 5500 - Monthly, within the first week |
| History | Form 5500 - January 2010 |
Description
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.
Snowflake Products
Tables above are available in the following Snowflake data products:
- Global Government (Unemployment Insurance)
- Snowflake Data Foundations - PAID (Unemployment Insurance)
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).
Snowflake is not endorsed by or affiliated with any of these providers. Contact snowflake-public-data@snowflake.com for questions.