Aidbox FHIR DBT package
This dbt package provides useful macros, build in models, and tests for work with Aidbox FHIR platform. Only PostgreSQL datasource supported.
See Sample project for deep dive into analytics on FHIR.
Installation
packages:
- package: "Aidbox/dbt_fhir"
version: 0.1.0
Features
- FHIR models - build in FHIR resources models
- Utils - provide set of useful macros for FHIR data types
- Cohorts - supported cohort analyses
- Tests - provide tests for FHIR data types
FHIR resources models
This package provides models for all FHIR resources of version 4.0.1
Usage example: count of Patients
select count(*)
from {{ ref('aidbox', 'Patient')}}
-- Expand
select count(*)
from "db"."dbt_fhir"."Patient"
Macros
path(path, resource=None)
Extract resource value by json path, equivalent of #>>
operator
path
- comma separated path of the value like"name, 0, given, 0"
resource
- optional resource column
select {{ aidbox.path("name, 0, given, 0") }} as name
from {{ ref('aidbox', 'Location')}}
-- Expand
select ("resource"#>>'{ name, 0, given, 0 }') as name
from "db"."dbt_fhir"."Location"
identifier(alias, resource=None)
Extract identifier value for given identifier system alias
alias
- human readable identifier alias fromseed_identifiers
seedresource
- optional resource column
Require
seed_identifiers
seed with columnsalias
andsystem
seed/seed_identifiers.csv
alias,system npi,http://hl7.org/fhir/sid/us-npi ssn,http://hl7.org/fhir/sid/us-ssn mrn,http://hospital.smarthealthit.org
SELECT id
, {{ aidbox.identifier('ssn') }} ssn
, {{ aidbox.identifier('mrn') }} mrn
FROM {{ ref('aidbox', 'Patient') }}
-- Expand
SELECT id
, (trim('"' FROM (jsonb_path_query_first("resource", concat('$.identifier ?(@.system=="', (SELECT system FROM "db"."dbt"."seed_identifiers" WHERE alias = 'ssn' limit 1), '").value')::jsonpath))::text)) ssn
, (trim('"' FROM (jsonb_path_query_first("resource", concat('$.identifier ?(@.system=="', (SELECT system FROM "db"."dbt"."seed_identifiers" WHERE alias = 'mrn' limit 1), '").value')::jsonpath))::text)) mrn
FROM "db"."dbt_fhir"."Patient"
extension(alias, jpath, resource=None)
Extract extension value for given extension alias
alias
- human readable identifier alias fromseed_extension
seedjpath
- path of extension value inside extension in jsonpath formatresource
- optional resource column
Require
seed_extension
seed with columnsalias
andurl
seed/seed_extension.csv
alias,url us-race,http://hl7.org/fhir/us/core/StructureDefinition/us-core-race us-ethnicity,http://hl7.org/fhir/us/core/StructureDefinition/us-core-ethnicity us-birthsex,http://hl7.org/fhir/us/core/StructureDefinition/us-core-birthsex
select {{ aidbox.extension('us-race', 'extension.valueString') }}
from {{ ref('aidbox', 'Patient')}}
-- Expand
select (trim('"' FROM (jsonb_path_query_first("resource", concat('$.extension ? (@.url == "', (SELECT url FROM "db"."dbt"."seed_extension" WHERE alias = 'us-race' limit 1), '").extension.valueString')::jsonpath))::TEXT))
from "db"."dbt_fhir"."Patient"
codesystem_code(path, resource=None)
Extract codesystem code for given system alias
jpath
- path in jsonpath formatalias
- human readable alias value fromseed_codesystems
seedresource
- optional resource column
Require
seed_codesystems
seed with columnsalias
andsystem
seed/seed_codesystems.csv
alias,system organization-type,http://terminology.hl7.org/CodeSystem/organization-type SNOMED CT-INT,http://snomed.info/sct ActCode,http://terminology.hl7.org/CodeSystem/v3-ActCode language,urn:ietf:bcp:47 MaritalStatus,http://terminology.hl7.org/CodeSystem/v3-MaritalStatus loinc,http://loinc.org
SELECT id
, {{ aidbox.codesystem_code('type', 'organization-type')}} type_code
FROM {{ ref('aidbox', 'Organization') }}
-- Expand
SELECT id
, (trim('"' FROM (jsonb_path_query_first("resource", concat('$.type.coding ?(@.system=="', (SELECT system FROM "db"."dbt"."seed_codesystems" WHERE alias = 'organization-type' limit 1), '").code')::jsonpath))::text)) type_code
FROM "db"."dbt_fhir"."Organization"
codesystem_display(jpath, alias, resource=None)
Extract codesystem display for given system alias
jpath
- path in jsonpath formatalias
- human readable alias value fromseed_codesystems
seedresource
- optional resource column
Require
seed_codesystems
seed with columnsalias
andsystem
SELECT id
, {{ aidbox.codesystem_display('type', 'organization-type')}} type_display
FROM {{ ref('aidbox', 'Organization') }}
-- Expand
SELECT id
, (trim('"' FROM (jsonb_path_query_first("resource", concat('$.type.coding ?(@.system=="', (SELECT system FROM "db"."dbt"."seed_codesystems" WHERE alias = 'organization-type' limit 1), '").display')::jsonpath))::text)) type_display
FROM "db"."dbt_fhir"."Organization"
trim(expr)
Remove surrounded ".."
of string
expr
- sql expression
select {{ aidbox.trim('s') }}
from (select '"Hello"') t(s)
-- Expand
select trim('"' FROM ( s ) ::TEXT)
from (select '"Hello"') t(s)
Cohorts
Based on Vulcan RWD
Demographics
- age(resource=None) - Get Patient age
- gender(resource=None) - Get Patient Gender
- alive(resource=None) - Get Patient alive status
- death(resource=None) - Get Patient death status
- race(resource=None) - Get Patient US core IG race
- ethnicity(resource=None) - Get Patient US core IG ethnicity
resource
argument is optional. If not passed,resource
expression will be used.You can directly specify resource column, for example, in JOIN statements
Example:
SELECT id
, {{ aidbox.age() }} age
, {{ aidbox.identifier('ssn') }} ssn
, {{ aidbox.identifier('mrn') }} mrn
, {{ aidbox.gender() }} gender
, {{ aidbox.alive() }} alive
, {{ aidbox.race() }} race
, {{ aidbox.ethnicity() }} ethnicity
, {{ aidbox.extension('us-birthsex', 'valueCode') }} birthsex
, {{ aidbox.path('birthDate') }}::date birthdate
, extract('YEAR' from {{ aidbox.path('birthDate') }}::date) birth_year
, {{ aidbox.path('deceased,dateTime') }}::date deceased
, extract('YEAR' from {{ aidbox.path('deceased,dateTime') }}::date) deceased_year
, {{ aidbox.path('address,0,state') }} state
, {{ aidbox.codesystem_code('maritalStatus', 'MaritalStatus') }} ms_code
, {{ aidbox.codesystem_display('maritalStatus', 'MaritalStatus') }} ms_display
, {{ aidbox.codesystem_code('communication.language', 'language') }} language_code
, {{ aidbox.codesystem_display('communication.language', 'language') }} language_display
FROM {{ ref('aidbox', 'Patient') }}
Diagnosis
- Work in progress...
LabTests
- Work in progress...
Medications
- Work in progress...
Procedures
- Work in progress...
Visits
- Work in progress...
Tests
- fhir_base64Binary - (doc)
- fhir_canonical - (doc)
- fhir_code - (doc)
- fhir_date - (doc)
- fhir_dateTime - (doc)
- fhir_id - (doc)
- fhir_instant - (doc)
- fhir_oid - (doc)
- fhir_string - (doc)
- fhir_time - (doc)
- fhir_uri - (doc)
- fhir_url - (doc)
- fhir_uuid - (doc)
- fhir_markdown - (doc)
- fhir_boolean - (doc)
- fhir_decimal - (doc)
- fhir_positiveInt - (doc)
- fhir_unsignedInt - (doc)
- fhir_integer - (doc)
- fhir_integer64 - (doc)
Example:
version: 2
models:
- name: Location
columns:
- name: "id"
tests:
- aidbox.fhir_id
- name: Observation
columns:
- name: "resource#>>'{ issued }'"
tests:
- aidbox.fhir_date
- name: "resource#>>'{ effective,dateTime }'"
tests:
- aidbox.fhir_date
Run tests
# Run all tests
dbt test --store-failures
# Test specific model
dbt test --store-failures --select Location
Powered by Health Samurai | Aidbox | Fhirbase