Apache Parquet acceptable machine-readable format

[jkauflin]

Would Apache Parquet be an acceptable machine-readable format?

[ericsilvertx]

A parquet file is a tabular format usually but these files are hierarchical in structure

We are going to route of JSON

[taylorpatriciab]

@shaselton Following up with the original question, is Apache Parquet an acceptable machine-readable format?

[keithwoodnc]

@shaselton I am also following up on the questions around Apache Parquet. The readme gives examples of both acceptable and unacceptable formats, which indicates to me that other open-source solutions such as Parquet would be ok. It would be great to have clarity on this.

[BobSyracuse]

@jkauflin I have been told by one vendor they plan on using Parquet. It will be interesting to see how the data aggregators that plan on using the data provided by the vendor will react when they have to download JSON, XML, Parquet and any other "machine readable format" from each payor/insurance carrier and build the necessary transformations.

[ericsilvertx]

I was incorrect about structured data and Parquet:

https://towardsdatascience.com/big-data-transformations-with-complex-and-nested-data-types-c1b9c45ca792

Parquet is a more complex format that a json for sure but I can see that it is compressing the data within the file. One thing about Parquet is it introduce the need to have technical people that knows how to use Parquet

[BobSyracuse]

@ericsilvertx Yes it does introduce a level of compression. You are exactly correct that it will involve will be technical people and specific tools to access the binary format with Java libraries or more than base functionality from tools like Informatica. It is though a natural fit for any data sciences and an easy format to work with in Microsoft Azure Data Factory, Databricks and Synapse Analytics.

[shaselton-usds]

@jkauflin @keithwoodnc

Parquet is definitely an open format so it would be allowed - https://www.federalregister.gov/d/2020-24591/p-778 It would have to be based on the JSON schema here.

As with all technology decisions, there are trade-offs. The complexity would be higher (both on the initially file development and additional processing as well as the consuming of the file) but the compression may be better.

[balbarka]

By "based upon the json schema", does that afford us flexibility to flatten schema? Meaning, is something like the following acceptable:

Original Schema for Allowed-Amounts (fba9de7, spark):

root
 |-- reporting_entity_name: string (nullable = false)
 |-- reporting_entity_type: string (nullable = false)
 |-- plan_name: string (nullable = true)
 |-- plan_id_type: string (nullable = true)
 |-- plan_id: long (nullable = true)
 |-- plan_market_type: string (nullable = true)
 |-- out_of_network: array (nullable = false)
 |    |-- element: struct (containsNull = false)
 |    |    |-- name: string (nullable = false)
 |    |    |-- billing_code_type: string (nullable = false)
 |    |    |-- billing_code: string (nullable = false)
 |    |    |-- billing_code_type_version: string (nullable = false)
 |    |    |-- description: string (nullable = false)
 |    |    |-- allowed_amounts: array (nullable = false)
 |    |    |    |-- element: struct (containsNull = false)
 |    |    |    |    |-- tin: struct (nullable = false)
 |    |    |    |    |    |-- type: string (nullable = false)
 |    |    |    |    |    |-- value: string (nullable = false)
 |    |    |    |    |-- service_code: string (nullable = true)
 |    |    |    |    |-- billing_class: string (nullable = false)
 |    |    |    |    |-- payments: array (nullable = false)
 |    |    |    |    |    |-- element: struct (containsNull = false)
 |    |    |    |    |    |    |-- allowed_amount: string (nullable = false)
 |    |    |    |    |    |    |-- billing_code_modifier: string (nullable = true)
 |    |    |    |    |    |    |-- providers: array (nullable = false)
 |    |    |    |    |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |    |    |    |    |-- billed_charge: string (nullable = false)
 |    |    |    |    |    |    |    |    |-- npi: string (nullable = false)
 |-- last_updated_on: string (nullable = false)
 |-- version: string (nullable = false)

Proposed / Flattened Schema for Allowed-Amounts (spark):

root
 |-- reporting_entity_name: string (nullable = false)
 |-- reporting_entity_type: string (nullable = false)
 |-- plan_name: string (nullable = false)
 |-- plan_id_type: string (nullable = false)
 |-- plan_id: long (nullable = false)
 |-- plan_market_type: string (nullable = false)
 |-- out_of_network_name: string (nullable = false)
 |-- out_of_network_billing_code_type: string (nullable = false)
 |-- out_of_network_billing_code: string (nullable = false)
 |-- out_of_network_billing_code_type_version: string (nullable = false)
 |-- out_of_network_description: string (nullable = false)
 |-- allowed_amount_tin_type: string (nullable = false)
 |-- allowed_amount_tin_value: string (nullable = false)
 |-- allowed_amount_service_code: string (nullable = false)
 |-- allowed_amount_billing_class: string (nullable = false)
 |-- payment_allowed_amount: float (nullable = false)
 |-- payment_billing_code_modifier: string (nullable = false)
 |-- provider_billed_charge: string (nullable = false)
 |-- provider_npi: string (nullable = false)
 |-- last_updated_on: string (nullable = false)
 |-- version: string (nullable = false)

Now, the proposed schema would be inefficient for formats like json, xml, but since parquet is a columner format, this will compress quite well. This will also be a format more scalable and expected in most HLS datalakes. The original schema would have too few records to make parquet a viable format IMHO.

@shaselton-usds , willing to start branch with parquet example and transforms between schemas if flattened / proposed schema for parquet is viable.

[checkbook-org]

Just found this discussion, really glad to see it.

I strongly support the use of Parquet, both because of the much smaller files sizes (we've seen 50-100x reductions) and because so many data science tools work natively with Parquet. I suspect, but haven't yet tested, that these smaller file sizes would result in fewer broken files and better ability to validate files on both the publisher and consumer sides.

@shaselton-usds I notice that this repo doesn't list Parquet - what is needed to make that possible?

Thanks!

[balbarka]

Ultimately, the json format had been so inefficient to use in application that I advise anyone attempting to read the JSON format to ingest into a columnar format, specifically parquet, first before analyses. As a consequence of the complexity of doing single pass reads, we had to develop a custom serde to read the json files in a single pass streaming job using, hls-payer-mrf-sparkstreaming. The ingest pattern I ultimately advise on using can be found at hls-price-transparency-workshop. That repo has a dependency on my pypi package, spark-price-transparency.

There are two concerns with promoting / citing these repos / packages in CMSgov/price-transparency-guide outside of discussions:

• Both repos have a use license which isn't appropriate to have in a gov't conventions repo
• Both repos have a platform bias which again isn't appropriate in a gov't conventions repo

Ideally, we would provide an accepted parquet schema here and avoid the very high compute cost of ingest for users of this data. This would also be a benefit to file providers as it is also more compute intensive to create large json format files than parquet.

I'd be willing to create a PR with these parquet schemas to avoid the problems above if I can get buy-in from a reviewer. My proposed schemas would however be in 6 instead of 4 schemas which I had done for down stream performance.