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.
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.
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).claim_coverage_viewclaim_diagnosis_viewclaim_drg_viewclaim_member_viewclaim_procedure_viewclaim_provider_viewclaim_viewmember_eligibility. Last release it was in member_coverage.insert_with_idIt 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
Migration update
Migration update
Migration update
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.
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!
extractor improvementsisUnique supportcsvloader improvementsMETL 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.
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.
extractor configuration. METL extracts files before loading them. In METL, extracting can mean a number of different things:
extractor supports fixed-width files and CSV files. We anticipate adding additional file formats over time.extractor makes it tons easier to build load scripts for csvloadercsvloader 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.
.sql files and everything gets checked into source control so it's easy to see exactly what's going on at any time.SQL, any transformation that you can do with data in SQL, you can do with csvloaderIntroducing 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.
It can really mean whatever you want it to, but here are the ideas we came up with:
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.
claims schemaThese are the items that are included in the new database we've released.