CUE & ETL transforms

[steeling]

Follow up from #484, we're looking to do ETL transforms from a source schema to a target schema. The steps we're investigating are:

1. [Outside of CUE] Given a set of (source) relational data, and its foreign keys, we can convert the relational data to JSON. ie:

Patient:
id, name, dob, address, city, zip, country


Doctor:
id, name, specialty, practice_id, address, city, zip, country


Visit:
patient_id, doctor_id, date, reason, outcome

We can create a denormalized JSON object based on these foreign keys, ie:

Patient: {
  /* patient fields */
  id: 1
  name: "steeling"
  visits: [
     {
        patient_id: 1
        doctor_id: 2
     },
     {
        patient_id: 1
        doctor_id: 2
      },
     {
        patient_id: 1
        doctor_id: 0
      },
     ...
  ]
}

2 Tranform from the source JSON to a target JSON using CUE.

Let's say our target schema is different, and is going into a larger database that has patients from multiple sources. We don't want to use the original ID's, but instead want to let the DB autogen the ID's

We might have a target schema defined as:

#Person:{
  id: int
  name: != ""
  gender? string
  marital_status?: string
  age: int
  birth_date: string
  ethnicity?: string
  location_id: int
  visits: [...#Visit]
}

#Visit: {
  patient_id: int
  practice_id: int
  date: string
  reason: string
  outcome: string
}

#Location: {
  id: int
  address?: string
  city?: string
  zip?: string
  country?: string
}

#Physician: {
   id: int
   name: string
   practice_id: int
   location_id: int
}

#Practice: {
id: int
name: string
location_id: int
}

3. We can unfurl the resulting JSON mappings, and insert things into the database. However, we get stuck in that we want to partially evaluate the data as we do our inserts, since the foreign keys require a write to the db to be generated.

Imagine we have 3 Practices, with source ID's ID'd 1, 2, 3

Given the source data above, we might have a translation:

mapping: {
   input: #Patient
   output: #Person&{
      id: // can't set this!
      name: input.name
      visit: [for visit in input.visits {
         patient_id: // can't set this since it hasn't been inserted in the database!!!
         doctor_id: // same here!
      }]
   }
}

In reality our transformations are much more complex, requiring the embedding of nested documents for field access. The difficulty is that we need to perform these mappings prior to inserting in the DB, then we need to flatten back out into their relational format to do the inserts.

Once flattened we lose the relation to the original parent, making it difficult to set it's foreign key.

We could do a recursive:

1. unflatten child
2. insert
3. set foreign key

But it would be nice to leverage as much within CUE as possible