Affordable Care Act (ACA) Enterprise Data Engineering


The Patient Protection and Affordable Care Act (ACA) created new health insurance exchanges that began processing applications for coverage in 2014, creating large volumes of heterogeneous data across Federal and State data sources. Our client, the Internal Revenue Service, is accountable for calculating and applying premium tax credits (PTC) to individuals and small businesses based on taxable income and insurance throughout the coverage period. They also use this data for matching and analysis to produce accurate measures of tax reporting, filing, and payment compliance as well as decision-making and reporting to such external stakeholders as the Treasury, Congress, and the Government Accountability Office.

Download Case Study

The Challenge

The Patient Protection and Affordable Care Act (ACA) was a high profile, significant legislative initiative that created new technology, process and data requirements that needed to be implemented across the country in a short time frame. Health Insurance Exchanges (HIEs) began processing applications for coverage in 2014, creating large volumes of heterogeneous data across federal and state data sources. While this data is compiled by the U.S Department of Health and Human Services (HHS) and shared with the Internal Revenue Service, the variance across exchanges in the data types and formats created significant enterprise-level data challenges. In addition, as this data is used for complex premium tax credit calculations and high-profile analytics and reporting to external stakeholders such as the Treasury Department, Congress, and the Government Accountability Office (GAO), it is paramount that the data and analysis be timely and accurate.

Our Approach

Analytica helped design the architecture and conducts ongoing enterprise data engineering processes and procedures to store the ACA data received via tax forms through the ACE Information Return (AIR) e-filing system into a complex information returns database (IRDB). Our efforts also support the compilation of data across IRS Forms 1095-A/B/C, Form 1040, W-2, and supplemental documents used for income verification. The IRDB stores data related to the transmission of the forms and insurance information that we read from multiple tables to merge and create a single view for each 1095 form and fiscal year the data is available.

This work integrates with our larger enterprise data management initiative supporting IRS’s 6 Petabyte (PB) data warehouse environment. This environment comprises over 52 institutional databases yielding over 175,000 unique data element descriptions. Our team ingests data from multiple environments including IBM z/OS mainframe, Solaris, Linux, and Windows, and whose data sources include structured, semi-structured, and unstructured data sources. Analytica applies SQL and Python code to extract, transform, and load (ETL) data based on data volume and complexity resulting in over 2000 table refreshes per month. We review data sources for accuracy, determine the optimal structure for analytics, monitor data release actions, and maintain processes to ensure that data are released in a timely manner to the IRS’s enterprise data environment.

We developed various processes for joining and merging the IRDB tables to create de-normalized records for more efficient data retrieval. Analytica works proactively with various stakeholders to address data requirements and identify numerous hidden risks and data quality issues with information the IRS receives from other federal government agencies. Our team also reviews and analyzes ACA data sources, monitors ACA release management actions, identifies and tracks the status of data quality issues, and analyzes data for duplication and other anomalies to ensure accuracy for economists, researchers, and data scientists leveraging data to achieve ACA compliance for the IRS.

The Solution

Our team has supported the IRS with ACA data engineering and management since the ACA health insurance exchanges first opened, allowing our clients to focus on their mission of providing America’s taxpayers top quality service by helping them understand and meet their tax responsibilities and by applying the tax law with integrity and fairness to all.

By providing the federal enterprise-level data engineering and management that is needed, we support the foundation for accurate analyses, including the calculation of premium tax credit amounts, development of baselines for individual and small business filing patterns, understanding of customer needs for pre-filing and filing activities, development of new measures for ACA-related compliance, and improvement of existing case management operations. In addition, we have supported our clients on numerous IRS management decision-making and reporting to such external stakeholders as Treasury, Congress, and Government Accountability Office.


"*" indicates required fields

This field is for validation purposes and should be left unchanged.