We're excited to announce version 1.7 of METL!

This release adds several new lookup tables that assist in reporting, data analysis, and even typical transactional claims processing, a bunch of foundational features that will be enabled in future releases, and some other data improvements surrounding EDI 837 claim storage.

What's new?​

New lookup data​

1. Horizon Blue Cross Case Management trigger list.
2. CPT hierarchy.
3. ICD diagnosis hierarchy.
4. ICD procedure hierarchy.
5. JHU wasteful drugs lookup.
6. Bill Type to Place of Service crosswalk.
7. Place of Service groups.
8. Stop loss trigger diagnosis codes.

Other table changes​

1. Remove deprecated name, contact_seen, name_given, provider_identification, member_identification, member_member_eligibility, and claim_member_match tables.
2. Updates to the claim table to align the database and the EDI 837 parsing.

New tables to support upcoming features​

1. member_canonical table. This replaces the member table and represents a canonical human represented ideally by a unique SSN number.
2. name_given_alias, claim_member_to_member_seen_match and member_seen_to_member_seen_match. These tables are used for patient matching between eligibility loads and from claim to eligibility data. They are used for both manual and automated patient matching.

Version 1.6 of METL has arrived!

This release adds a bunch of new data loads, improves view and data loading performance, adds additional type safety with PGGen and adds initial capability upon which we will be building out patient and provider matching.

New Features​

• Table updates to support the rest of the EDI 837 spec. This makes it much easier to query all types of data in the database since each field now has columns and rows rather than JSON fields.
• Table updates to support patient and provider matching. We're building our matching UI based on these tables.
• Performance improvements to views, tables and queries: Now takes about 1/2 the time to load member data through the member views in our environment due to fewer and simpler joins.
• PGGen - we were originally planning to do GraphQL for our API engine, but couldn't find a good solution that fully met our goals. We chose PGGen instead which will ensure the same sort of query safety and ease of use through an API. Our current API has been converted to use PGGen and it is working well for us.
• Update to csvloader to run analyze after loading data so that loads don't continue to slow down as new data gets added. Previously, the only way that statistics were updated was through AWS automated tools that didn't always run at the ideal times, making loading slower.

Bug fixes​

• NULL solution name error on empty input files has been made more clear.

Patch releases​

1.6.1​

Migration fix for claim_member. Previously the migration was not generating a unique list of correct IDs based on the new primary key which caused some migrations to fail.

1.6.2​

Fix for member_eligibility_current_view to correctly include all members after the switch to using trading_partner_submission table instead of eligibility_submission.

1.6.3​

Update migration for Health Rosetta.

1.6.4​

Migration fix.

1.6.5​

Migration fix.

The 1.5 release has arrived!

While this release brought important new data loads and additional features, it was never deployed outside of My Price Health.

Not released version​

• This version was never deployed outside of My Price Health due to an issue. See version 1.6 which fixed this issue and added additional features

The 1.4 METL release is here!

Now that we've got a bunch of data in the database, this release focuses on performance. We made some significant improvements to some of our tables and views to improve performance. We also switched to using a new function called insert_with_id to load many tables and saw a massive productivity improvement for data load script development.

What's new?​

1. Some significant improvements to tables and views to improve query times. All of the member* views are now faster than last release
2. Unified all the eligibility loads. We now have one set of scripts that load all eligibility files. Barring any unforeseen new things with eligibility, we think we can model any future eligibility files without load script changes. This cuts the time to configure a new eligibility load to 2-4 days
3. In an effort to make the member table even more unique to a single member, SSN is now the only unique key for this table (previously it also included DOB, but due to data inconsistencies, this was causing some SSN's to appear multiple times, once with each family member's SSN)
   1. This allows for the best method we know of to get non-duplicate names of members in a drop-down.
   2. The only downside to using only SSN is that this means that if a TPA is not sending SSN for all dependents that only the subscriber will show in the drop-down. The upside to that "problem" though is that it will remind us to bug the TPA to give us all the data 🙂
4. A new member_member_eligibility table. This joins our unique member to the eligibility information for that member over time. This allows us to see all the information about a member over time. Because member is by SSN, this even allows us to see changes across TPA's. A future release will allow for a UI to fix data errors that cause a member to be associated with a SSN that is not actually theirs but may've been typed incorrectly by the TPA.
5. We now use the function insert_with_id to load data to most of our lookup data tables. It has cut the amount of code in most of our load functions by > 2/3. It also fixed a number of load bugs that had crept in from all the manually created load files before. Its job is to insert new unique data based on a unique index, get back the correct ID from either the generated snowflake (if not yet loaded) or from the existing table (if previously loaded).
6. Speaking of load files, we made lots of improvements and fixes in general to our load files which already has made it simpler and faster to create new data loads.
7. We've added the ability to create a snapshot of our database after a set of data files are loaded. We've taken a snapshot of all the data we've loaded so far. This will make it comparatively trivial to validate that data continues to load as its supposed to in the future without regressions. And, if changes occur, it's pretty easy to do a quick diff in a standard diff tool to see and validate the changes
8. New validation views. We used all of these in our own data validation:
   1. claim_coverage_view
   2. claim_diagnosis_view
   3. claim_drg_view
   4. claim_member_view
   5. claim_procedure_view
   6. claim_provider_view
   7. claim_view

Change to be aware of:​

• Subscriber linking for each member is now in member_eligibility. Last release it was in member_coverage.

More about insert_with_id​

It sounds simple enough, but we have a lot of "unique" data tables in the database to track changes in data without also incurring the full cost of storing duplicate data all the time. For example, we have an address_seen table which has every unique address we've ever encountered, a provider_seen table with every unique provider we've seen, etc. In total, we have dozens of these tables. Unfortunately, they are hard to load because of the semantics of those load scripts. But, we've done it enough now that David took the template we had built and created a load_with_id function which does all the work for us. It looks up the unique constraint, dynamically builds the insert query and much more. It's quite the marvel that he created. 🙂

Fortunately, now loading those few dozen tables goes from a complicated and error-prone script like this:

-- strategy modified from https://stackoverflow.com/a/42217872
WITH input AS (
    SELECT
        address_id::bigint,
        upper(address1) AS address1,
        upper(address2) AS address2,
        upper(city) AS city,
        upper(state_or_province) AS state_or_province,
        upper(postal_code) AS postal_code,
        country_code
    FROM address_seen_staging
), inserted AS (
    INSERT INTO claims.address_seen (
        address_id,
        address1,
        address2,
        city,
        state_or_province,
        postal_code,
        country_code
    )
    SELECT * from input
    ON CONFLICT DO NOTHING
    RETURNING *
)
SELECT input.address_id, existing.address_id AS new_id
INTO TEMP TABLE address_seen_id
FROM input
INNER JOIN claims.address_seen existing
    ON coalesce(input.address1, '') = coalesce(existing.address1, '')
    AND coalesce(input.address2, '') = coalesce(existing.address2, '')
    AND coalesce(input.city, '') = coalesce(existing.city, '')
    AND coalesce(input.state_or_province, '') = coalesce(existing.state_or_province, '')
    AND coalesce(input.postal_code, '') = coalesce(existing.postal_code, '')
    AND coalesce(input.country_code, '') = coalesce(existing.country_code, '')
    AND '' = coalesce(existing.country_subdivision, '')
UNION ALL
SELECT input.address_id, inserted.address_id as new_id
FROM input
INNER JOIN inserted
    ON coalesce(input.address1, '') = coalesce(inserted.address1, '')
    AND coalesce(input.address2, '') = coalesce(inserted.address2, '')
    AND coalesce(input.city, '') = coalesce(inserted.city, '')
    AND coalesce(input.state_or_province, '') = coalesce(inserted.state_or_province, '')
    AND coalesce(input.postal_code, '') = coalesce(inserted.postal_code, '');

CREATE INDEX address_seen_id_idx ON address_seen_id (address_id);

To a simple one that looks like this:

CREATE TEMP VIEW insert_data AS (
    SELECT
        address_id::bigint,
        upper(address1) AS address1,
        upper(address2) AS address2,
        upper(city) AS city,
        upper(state_or_province) AS state_or_province,
        upper(postal_code) AS postal_code,
        country_code,
        '' AS country_subdivision
    FROM address_seen_staging
);

SELECT claims.insert_with_id('claims.address_seen', 'address_id');

The use of insert_with_id was helpful already this release and dramatically cut down debugging and fixing time because some of our load scripts were wrong. The new function fixed them immediately. Moving forward, we won't have to write another of these scripts which will speed up development time too.

The other change we made this release was to further standardize all of our loads. We've been slowly building up all of our loads so that every TPA data load includes all the data available in the database. Our loading is now just a matter of plugging in fields to each template. We have slowly gotten rid of all the custom load scripts that we've written in the past and standardized everything into one set of scripts. All of our eligibility files are loaded with one standard set of load SQL files. Our claims data is down to 3 special load files that I hope we'll be able to get rid of next release. The goal is to make it drop-dead easy to load data and I think we're really close to that. The last big improvement we have on our roadmap to improve is to build a template so that we can map fields to the database in the exact same format as the Excel spreadsheets we develop for each load. At that point, we hope the YAML file for extractor will be as easy as copy/paste from Excel.

So, even as much as we've improved our speed for loading data this release vs. last release, I hope that subsequent releases will see a similar improvement in speed

Patch releases​

1.4.1​

Migration update

1.4.2​

Migration update

1.4.3​

Migration update

1.4.4​

Migration update

METL 1.3 is released and represents a big improvement in eligibility loading.

A big focus for the 1.3 release was to make eligibility loading more standardized. At this point, we've loaded eligibility for several TPA's and we wanted to standardize the way we've been doing that. For this release we unified load scripts as much as possible. We hope this can continue to speed up the amount of time it takes to load new eligibility data.

What's new?​

• Adds line_item_adjudication_snapshot. This is a point-in-time snapshot of what a TPA is reporting for member responsibility, plan responsibility, not covered amounts, copay, coinsurance, deductible, and much more. These numbers are not yet comparable across TPA
• Adds support for ACS and JP Farley eligibility loading
• Unifies eligibility loading into a single approach. All of our scripts are now almost exactly the same for each TPA. They each have special data conversion functions, but otherwise they are the same. We anticipate being able to reduce the time to load eligibility files to 25% the time as previously.
• Significantly improves the data clarity for eligibility data vs. coverage data. Previously there were a number of pieces of data that fit better as "coverage" data which made it so that finding unique plan members was harder.
• Began populating the member table which represents an individual person. It consists of just a SSN and a DOB and will represents a singe person even across multiple plans, TPA's, etc. We will fill out the remaining pieces to begin using a member next release. This gives us three ways to view a person:
  • member: A person. Represents a human being, specifically a human which happens to be eligible for or enrolled in a health plan. Whether that person is enrolled in one plan or 1000, there will only be one row for that person.
  • member_eligibility: A person at a company. If a person works at 2 different companies, they would have two member_eligibility rows.
  • member_coverage: A person at a company enrolled in a health plan of some sort. Depending on how the TPA sends their data, there can be multiple rows of coverage data for a single person. They could be enrolled in a dental, vision, RX, and medical plan, and there could be a row for each. Likewise, if the member's coverage changes over the year (e.g. they have a baby and go from "Employee Only" to "Employee and Child", there would be a coverage row for each with the begin and end coverage dates for each.)

We're excited to release METL 1.2.

There are lots of pretty major changes with this release that make it possible to keep iterating as quickly as possible on METL. One of the biggest for us was introducing Atlas for database deployment. Previously, we had been using sqitch, but it requires 100% of the migration scripts be written by hand. Atlas allows us to define the database model in .HCL files and it builds the migration for us. Already, we're finding it easier to make significant code changes to the database faster and easier. Our database feels more agile thanks to Atlas!

What's new?​

• Switched to using Atlas for database deployment
• Slack notifications
• Open Telemetry Tracing
• External secrets in AWS
• Reporting view (line_item_everything_view)
• ABA eligibility database updates

New data loads​

• HCUP loads
• HCPCS lookup
• CPT lookup
• NANPA Area codes
• ICD-10 PCS and CM loads

extractor improvements​

• Support for XLSX files
• isUnique support

csvloader improvements​

• Release milliseconds support

METL 1.1 is released!

This release is an important milestone in the development of METL. With this release, we introduced metl-deploy which is a set of scripts that make it super easy to deploy METL on AWS. My Price Health uses METL on Google Cloud and Health Rosetta uses METL on AWS so metl-deploy takes METL on a multi-cloud journey. Since METL runs on Kubernetes, we are confident it can also easily run on Azure and bare metal too.

What's new?​

The principal user-facing feature for METL 1.1 is the introduction of a set of claims loader scripts. This gives us the ability to view post-adjudicated claims data for this TPA's claims data. In addition to the loader, this was our first load into the claims schema with post-adjudicated claims data, so we needed to also make some modifications to the schema to support the claims load.

As with all other METL database loads, loading data to the database consists of 2 components.

1. extractor configuration. METL extracts files before loading them. In METL, extracting can mean a number of different things:
   1. Unzip a file. This is a frequent use case for CMS and other standard data files online
   2. Convert data from multiple formats. For now extractor supports fixed-width files and CSV files. We anticipate adding additional file formats over time.
   3. Clean invalid data from data files. It is fairly common with data files from CMS and other sources that there will be spurious invalid characters in them. Our personal favorite is the NPI registry data files. They are around 9 GB unzipped and it can take a long time to load that much data to the database. It's always fun to spend 50 minutes waiting for a file to extract and attempt to load only to find that there is an error on line 1,230,874. If you manually fix that, you'll then find an error on 1,597,145. If we have to manually clean up files like this, a 50 minute load can stretch on for many hours, if not over multiple days of manual cleaning.
   4. Pull data from one or more files in a zip or a single file. Sometimes it's important to be able to combine data from multiple files into a single file (e.g. NPI registry data that spreads name information across multiple files), and sometimes it's important to be able to split a single file into multiple files (e.g. claims data which may repeat the same data on multiple lines, but we don't want to load that data multiple times). If data is in files that looks similar to the tables they will be loaded into, it's a LOT easier to write load scripts.
   5. Write data to an easily bulk-loadable set of CSV format files. By unifying the format for the files that we load to the database, extractor makes it tons easier to build load scripts for csvloader
2. csvloader configuration and load scripts. METL starts with uniform, clean CSV's, loads them to staging tables of the same names and then runs load scripts to transform them to their final shape.
   1. The first step is for METL to read the load config file. It identifies the order that files will be loaded and what scripts are run. All load scripts are just .sql files and everything gets checked into source control so it's easy to see exactly what's going on at any time.
   2. In the order specified in the config file, load steps are performed. For most load steps, a CSV is loaded into a staging table and then a load script is run to load the data into the production tables.
   3. Because every load file is just SQL, any transformation that you can do with data in SQL, you can do with csvloader

Introducing METL, the new healthcare data platform and our 1.0 release!

Prior to this release, METL was being used by My Price Health for our claims pricing workflow. With this release, we teamed up with Health Rosetta to share the codebase and build something amazing together. The main feature of the 1.0 release is that we moved our claims schema to a new repository at https://github.com/mypricehealth/claims.

What does METL stand for?​

It can really mean whatever you want it to, but here are the ideas we came up with:

1. My Price Health Extract, Transform & Load
2. Medical Extract, Transform & Load

What is a data platform?​

A data platform is a complete system for data import, management, transactional workflows, and reporting.

For now, METL is primarily focused on ETL, but over time, we expect METL will expand far beyond ETL. We'll add UI's and many more capabilities. We hope many healthcare companies will build capabilities on top of of METL to reduce duplication of work and make it easier to cooperate with other healthcare companies. Our goal is to lower the cost of healthcare care in this country by 50% by eliminating administrative costs and making it easier to be healthy and METL is a key part of that effort.

What's new in this release?​

The claims schema​

These are the items that are included in the new database we've released.

• Tables
  • address_canonical
  • address_seen
  • claim
  • claim_adjustment
  • claim_coverage
  • claim_diagnosis
  • claim_drg
  • claim_member
  • claim_occurrence
  • claim_other_subscriber
  • claim_procedure
  • claim_provider
  • claim_value
  • hcpcs
  • hcup_diagnosis_crosswalk
  • hcup_diagnosis_group
  • hcup_hcpcs_crosswalk
  • hcup_hcpcs_group
  • hcup_procedure_crosswalk
  • hcup_procedure_group
  • icd_diagnosis
  • icd_procedure
  • line_item
  • line_item_adjudication
  • line_item_adjustment
  • line_item_diagnosis
  • line_item_provider
  • login
  • member
  • member_contact
  • member_coverage
  • member_match_hash
  • member_seen
  • member_seen_match
  • msdrg
  • nanpa_area_code
  • npi_affiliate
  • npi_authorized_official
  • npi_ccn_addition
  • npi_country
  • npi_endpoint
  • npi_license
  • npi_location
  • npi_location_match_addition
  • npi_match_hash
  • npi_name
  • npi_name_match_addition
  • npi_other_identifier
  • npi_provider
  • npi_state
  • npi_tax_id_addition
  • npi_taxonomy
  • nubc_condition
  • nubc_occurrence
  • nubc_value
  • nucc_taxonomy_lookup
  • plan
  • plan_hierarchy
  • plan_role
  • plan_solution
  • plan_sponsor
  • provider_seen
  • provider_seen_match
  • release
  • role
  • snowflake_node
  • solution
  • sql_metrics
  • trading_partner
  • trading_partner_extract_ncpdp
  • trading_partner_extract_text
  • trading_partner_extract_x12
  • trading_partner_load
  • trading_partner_load_claim
  • trading_partner_load_enrollment
  • trading_partner_load_rx
  • trading_partner_sftp
  • trading_partner_sftp_sync
• Views
  • npi_provider_view
• Functions
  • create_read_schema_role
  • create_read_write_execute
  • drop_read_schema_role
  • drop_read_write_execute_schema_role
  • insert_addresses
  • setup_address_seen_temp
  • type_exists
• Roles
  • claims_read
  • claims_read_write_execute
  • read_only
  • read_write_execute
  • sqitch_readonly
• Enums
  • claim_deny_action
  • diagnosis_code_type
  • enrollment_error_action
  • gender_or_sex_code
  • geo_precision_type
  • medical_or_surgical
  • npi_affiliate_type
  • npi_endpoint_type
  • npi_endpoint_use
  • npi_entity_type
  • npi_group_type
  • npi_identifier_type
  • npi_location_type
  • npi_name_type
  • plan_status
  • plan_type
  • provider_type
  • residential_delivery_indicator_type
  • yes_no_response_code
• Lookup data loads
  • npi_country
  • npi_state
  • nubc_condition
  • nubc_occurrence
  • nubc_value
  • solution
  • trading_partner
  • trading_partner_extract_text
  • trading_partner_extract_x12