Clinic booking analytics — sensitive data and governance

The request

A clinic-booking platform (GCP, BigQuery as warehouse) wants analytics. Source tables include: patient name, national ID, phone, date of birth, appointment diagnosis notes, and — in a legacy billing module — full payment card numbers. Requests: analysts want cohort analysis by age group and diagnosis; marketing wants patient phone numbers for SMS campaigns; a data scientist wants raw diagnosis notes for an NLP model; the BI tool is Metabase. Design how this data enters and is governed in the warehouse, in sensitive-data-design.md.

Decision

Patient data enters BigQuery through a single ingestion job that passes only an approved list of columns, replaces patient identity with a keyed pseudonym (a stand-in ID that only the holder of a secret key can link back to a person), turns date of birth into an age band, and routes identifiers and free-text notes into separate restricted datasets. Analysts and Metabase see only the pseudonymised core dataset. Card numbers from the legacy billing module and national IDs are never loaded, and patient names are not loaded either because no requested use needs them. Marketing gets a consented audience list exported to the SMS tool, not access to phone numbers. The data scientist gets a scrubbed copy of the notes by default, with the raw notes in a restricted table behind named approval.

The design assumes managed Google Cloud services are acceptable (BigQuery, Cloud KMS, Sensitive Data Protection), that the booking system has an internal patient ID, and that the regulated-data answer is "yes" (see Questions and assumptions).

Architecture

---
config:
  layout: elk
---
flowchart LR
  subgraph existing[Existing systems]
    book[("Booking database<br/><i>patients, appointments</i>")]
    bill[("Legacy billing module<br/><i>payments</i>")]
  end
  subgraph gcp[Google Cloud account]
    ingest["Ingestion job<br/><i>column allowlist, pseudonymise, age band</i>"]
    kms["Cloud KMS<br/><i>pseudonym key</i>"]
    subgraph bq[BigQuery]
      core[("core<br/><i>pseudonymised facts</i>")]
      ident[("restricted_identifiers<br/><i>phone, keyed by pseudonym</i>")]
      notes[("restricted_notes<br/><i>raw diagnosis notes</i>")]
      scrub[("notes_scrubbed<br/><i>de-identified notes</i>")]
      aud[("marketing_audience<br/><i>consented phones, no clinical data</i>")]
      cohort["cohort views<br/><i>small groups suppressed</i>"]
      supp[("suppression_list<br/><i>erased pseudonyms</i>")]
    end
    dlp["Sensitive Data Protection<br/><i>scrub free text</i>"]
    export["Audience export job<br/><i>logged</i>"]
  end
  bi["Metabase<br/><i>Dashboards</i>"]
  nb["Notebook<br/><i>NLP work</i>"]
  sms["SMS tool<br/><i>Campaign sender</i>"]
  analyst((Analyst))
  ds((Data scientist))
  book -- extract allowed columns --> ingest
  bill -- extract payment status and amount only --> ingest
  kms -- key --> ingest
  supp -- read --> ingest
  ingest -- load --> core
  ingest -- load --> ident
  ingest -- load --> notes
  notes -- read --> dlp -- write --> scrub
  ident -- build --> aud
  aud -- read --> export -- send list --> sms
  core -- query --> cohort -- query --> bi -- dashboards --> analyst
  scrub -- read --> nb -- results --> ds
  classDef ksrc stroke-dasharray:0
  classDef kstore stroke-dasharray:0
  classDef kjob stroke-dasharray:0
  classDef ktool stroke-dasharray:0
  classDef kperson stroke-dasharray:0
  classDef kwarn stroke-dasharray:0
  class book,bill ksrc
  class ingest,kms,cohort,dlp,export,bi,nb,sms ktool
  class core,ident,notes,scrub,aud,supp kstore
  class analyst,ds kperson
  classDef restricted stroke-width:3px,stroke-dasharray:4
  class ident,notes,aud,supp restricted

Dashed outlines are restricted datasets: no analyst, Metabase or default group has access to them.

Build steps

1. 1

   Escalate the card storage in the legacy billing module to its owner and compliance

   a named owner and a decision on removing or tokenising stored card numbers, so the risk is tracked outside this project.

2. 2

   Agree the column allowlist with source owners: only listed columns may leave a source

   a reviewed list, and card numbers, national ID and names structurally unable to reach the warehouse.

3. 3

   Create the pseudonym key and the ingestion job's use of it

   a stable patient pseudonym that links an analyst's data across tables but cannot be reversed from the data alone.

4. 4

   Create the datasets and access groups: core and cohort views for analysts and the Metabase service account; restricted datasets for named groups only; set the restricted datasets' time travel to the 2-day minimum

   analysts and Metabase can query, and cannot reach identifiers or notes.

5. 5

   Load core with age band and diagnosis category in place of date of birth and notes

   cohort dashboards by age group and diagnosis in Metabase, with small groups hidden.

6. 6

   Load restricted_identifiers and restricted_notes; scan notes for card-like numbers on the way in and quarantine any hit

   raw data held once, in two small tables.

7. 7

   Build notes_scrubbed and agree with the data scientist that it is good enough to train on

   an NLP dataset with no direct identifiers.

8. 8

   Build marketing_audience from recorded consent and the export job with its log

   marketing can run SMS campaigns without seeing a phone number.

9. 9

   Build the erasure pipeline, request log and suppression list, and test one erasure end to end

   a patient's request can be completed and proven within a stated deadline.

Why

Decisions needed