Definition

The Family History data model is a family member level clinical view of inherited-risk information recorded in primary care. It transforms family-history observations into one row per family member relationship.

Information

The Family History data model provides a view of all family history entries associated with a patient. Records are derived from observation family history data and data is provided at member-level grain.

This model contains the following key information for family history:

• Record and patient identifiers

• Clinical coding and terms for the source observation

• Family-member coding and descriptions

• Clinical and system dates

• Confidentiality and sensitivity information

Grain and Scope

Each row is a unique family history member record and each record is uniquely identified by either combining observation_id, member_key and organisation or member_key and the observation_uuid columns.

This model includes the following key identifiers:

• observation_id: The unique internal identifier for the source observation that contains the family history entry.

• member_key: The stable key representing the individual family-member row within that observation.

• observation_uuid: The UUID derived from observation_id and organisation, providing a stable unique identifier.

The following fields are important for tracking data lineage and freshness:

• is_deleted: Indicates whether the family history observation record has been deleted at source.

• family_member_is_deleted: Indicates whether the specific member row has been removed from the family history entry.

• transform_datetime: The timestamp indicating when the record was last processed and updated in the data model. This field is crucial for understanding the current state of the data.

Things to be aware of

• Member-level grain: A single observation can produce multiple rows where more than one family member type is recorded. There can be data where there is more than one entry for a family member type as well if the data in the source is a mix of coded and uncoded entries.

Overview

flowchart TB
  org1["Organisation 1"] --> patA["Patient A"]
  org1 --> patB["Patient B"]
  org2["Organisation 2"] --> patC["Patient C"]

  patA --> fh1["Family History: Mother - Diabetes"]
  patA --> fh2["Family History: Father - Hypertension"]
  patB --> fh3["Family History: Sibling - Asthma"]
  patC --> fh4["Family History: Grandparent - Stroke"]

  fh1 --> gather["Gather Family History Data"]
  fh2 --> gather
  fh3 --> gather
  fh4 --> gather
  gather -->|Unique IDs: observation_id + member_key + organisation| etl["ETL"]
  etl --> final["Final Consolidated Data"]

  classDef nodeStyle stroke:#9961a4;
  class org1,patA,patB,org2,patC,fh1,fh2,fh3,fh4,gather,etl,final nodeStyle;

  linkStyle default stroke:#117abf,fill:none;

Examples

Retrieve family history records for a patient

SELECT
  emis_observation_id,
  familymember_description,
  observation_emis_original_term,
  effective_date,
  organisation
FROM
  hive.explorer_ipcv_vanilla.observation_fh_v2
WHERE
  emis_patient_id = 12345
ORDER BY
  effective_date DESC;

Find active family history member records

SELECT
  emis_observation_id,
  familymember_description,
  observation_emis_original_term,
  is_deleted
FROM
  hive.explorer_ipcv_vanilla.observation_fh_v2
WHERE
  is_deleted = FALSE
ORDER BY
  effective_date DESC;

Count family history members by coded family-member concept

SELECT
  familymember_emis_code_id,
  familymember_description,
  COUNT(*) AS record_count
FROM
  hive.explorer_ipcv_vanilla.observation_fh_v2
GROUP BY
  familymember_emis_code_id,
  familymember_description
ORDER BY
  record_count DESC;