Improving the overall data & analytics process and experience for citizens

Analytic Insights

Vermont Health Connect (VHC) is a health insurance marketplace, run by the State of Vermont (SoV), to serve its residents. It was established in 2013 in response to the Affordable Care Act (ACA), which required all states to establish a marketplace where individuals could enroll in health insurance plans and access financial assistance, if they qualified. VHC serves as the central platform for Vermont residents to find, compare, and enroll in health insurance plans, providing a one-stop-shop for all health insurance needs. It is designed to make the enrollment process as simple and straightforward as possible, while also providing access to financial assistance programs like Medicaid and tax credits to eligible individuals. For more information, visit

Get the Details


The SoV was facing several major challenges with its existing system. These challenges can be broadly classified into technical, functional and operational issues. The combination of these challenges was affecting the SoV's ability to provide reliable services to its constituents and causing significant headaches for its IT and Operational staff.

Technical Issues:

  • The environment was down multiple times a week, causing downtime and lost productivity.

  • The system was built with outdated and slow components, leading to slow reporting and data latency issues.

Functional Issues:

  • The reporting system's data was multiple days behind actual current data, making it difficult for decision makers to make informed choices.

  • The system's slowness made it difficult for users to complete their work efficiently and effectively.

Operational Issues:

  • Upgrades to the system were both frequent and time-consuming, draining resources and delaying other initiatives.

  • Tech support was essentially non-existent, leading to a lack of timely resolution for system issues.

  • Bug fixes were not pursued, leading to an accumulation of technical debt and further exacerbating the system's problems.


In order to address these challenges, VHC decided to implement Archetype’s Modern Data Analytics & Reporting (MDAR) solution.

Archetype’s MDAR offering is a Minimum Acceptable Risk Standards for Exchanges (MARS-E) compliant SaaS (Solution-as-a-Service) based modern data platform, analytics, and reporting solution for State Based Exchanges (SBE).

The specific workstreams supported are state business & operational reporting functions, 1095A & 1095B tax notice creation, xml file generation for the Internal Revenue Service (IRS), and State Based Marketplace (SBM) file generation for the Centers of Medicaid Medicare Services (CMS). Each of these workstreams has monthly mandated submissions to the federal government.

MDAR provides near real-time analysis of eligibility, enrollment, and effectuation for qualified health care and Medicaid plans, identifies issues in the eligibility and enrollment process that may prevent effective coverage for one or more citizens through reporting and data quality tools, and provides timely reports to State and Federal agencies, including IRS and CMS. Archetype’s MDAR solution modernizes the data platform, analytical & reporting capabilities for SBEs.


Archetype replaced the entire back-end data environment for VHC by connecting AWS to the Oracle front-end Database (OLTP DBs) and used AWS Data Migration Service (DMS) to sync the data from the Oracle source DB into Snowflake DB in AWS. This is a real-time sync that always keeps Snowflake up to date.

Archetype runs an ELT process scheduled with Airflow and use dbt to transform the source data into relevant data models in Snowflake for reporting.

The implementation of Snowflake at VHC was a multi-phase process.



VHC is now able to easily store, analyze, and gain insights from its data. Additionally, VHC has been able to improve its data visualization and reporting capabilities, which has allowed it to make more informed decisions. With MDAR, State resources are no longer dedicated to M&O tasks related to software upgrades and by adopting a system using open-source SQL, SoV staff can focus on their primary mission of enrolling and helping Vermont citizens.

MDAR provides new capabilities that improves citizen experience, for example, a Vermont citizen can make a call to VHC’s call center, make a change to their health insurance, and by the afternoon VHC can send out the proper notices with the latest information. In the past this would take days or even weeks.

The State of Vermont (SoV) considers this project a huge success primarily for three reasons:

  1. Previous solution: The ETL of the CRM (Siebel) data would take 1 - 2 days (at least) in the previous Oracle environment resulting in reports that used very old data.

    MDAR solution: The ELT step was reduced to 30 min - 2 hours (depending on Snowflake platform size used) and is scheduled to run twice a day for SoV ensuring their reporting results are based on much more current info from Siebel.

  2. One KPI for the project was the improvement in report run times as measured by about a dozen key reports that the State selected. Run times for the reports were gathered by the State in the old OBIEE systems and compared to the times in the new MDAR system. SoV hoped for a 20% reduction overall in run times. The reports in MDAR clocked in at 99% reduction in run times. This had a massive impact on the VHC reporting team's appreciation of the new system.

  3. The stability, availability, and 'always up to date' nature of the system is a big win for the SoV. With MDAR, they now have modern analytics capabilities that are always available, easy to use, powerful, and embraced by the SoV reporting team.

Overall, the implementation of MDAR at Vermont Health Connect has been a major success. The organization is now able to make more informed decisions, improve its performance and scalability, and gain insights from its data that were previously impossible. MDAR also protects sensitive information by incorporating the MARS-E (NIST 800-53) framework for securing information for both the cloud architecture and business processes as part of the solution. VHC has built trust with stakeholders, including customers, partners, and regulators, that information is secure and sensitive citizen information is protected. MDAR was built using Infrastructure as Code (IaC) and is easily scalable and deployable to work for other SBEs and use cases.

I would be remiss if I did not shout the necessary praises for the work of Archetype. Their availability, knowledge, and guidance that they’ve provided our team over the course of this project has been the most valuable and efficient collaboration that I have witnessed in the last 9 years while being at the State of Vermont. Thank you all for being phenomenal and professional working partners.

Dammy Mustapha
Business Reporting Administrator
Vermont Health Connect