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 from seed_identifiers seed
• resource - optional resource column

Require seed_identifiers seed with columns alias and system

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 from seed_extension seed
• jpath - path of extension value inside extension in jsonpath format
• resource - optional resource column

Require seed_extension seed with columns alias and url

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 format
• alias - human readable alias value from seed_codesystems seed
• resource - optional resource column

Require seed_codesystems seed with columns alias and system

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 format
• alias - human readable alias value from seed_codesystems seed
• resource - optional resource column

Require seed_codesystems seed with columns alias and system

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