Definition

The Drug Code model provides a standardised reference catalogue of medication and drug preparation codes, primarily based on the NHS Dictionary of Medicines and Devices (dm+d), with defined exception codes retained for historical data compatibility.

The NHS Dictionary of Medicines and Devices (dm+d) is a dictionary of descriptions and codes which cover product information about medicines and devices in use across the NHS. It is the recognised NHS standard for uniquely identifying and communicating medicinal product and medical device information used in patient care across clinical IT systems.

Information

The Drug Code model contains clinical code identifiers and terms relating to medication, plus related coding attributes such as Read term/code and SNOMED concept ID fields.

Grain and Scope

Each row is a unique drug code record and each record is uniquely identified by code_id column.

This model includes the following key identifiers:

• code_id: The unique EMIS identifier for the drug code.

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

• is_deleted: Indicates whether the drug code record has been deleted at source.

• 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

• Drug Code Mapping Updates: Mappings from code ids to SNOMED CT and Read codes can change over time; EMIS MKB is updated monthly and may include relevant code updates, with more changes expected when releases coincide with NHS SNOMED CT updates.

Examples

Find codes based on DM+D product code

SELECT
  *
FROM
  hive.explorer_ipcv_vanilla.drug_code_v2
WHERE
  dmd_productcode_id = 12354611500001104;

Find medications with a specific ingredient

SELECT
  emis_code_id,
  emis_term,
  dmd_productcode_id
FROM
  hive.explorer_ipcv_vanilla.drug_code_v2
WHERE
  emis_term LIKE '%Paracetamol%'
  AND is_deleted = FALSE
ORDER BY
  emis_term;

Find medications migrated or degraded during system transfers

SELECT
  emis_code_id,
  emis_term,
  emis_old_code,
  CASE
    WHEN emis_code_id = 1572871000006117 THEN 'Awaiting migration'
    WHEN emis_code_id = 294711000000118 THEN 'Transfer-degraded'
  END AS exception_type
FROM
  hive.explorer_ipcv_vanilla.drug_code_v2
WHERE
  emis_code_id IN (1572871000006117, 294711000000118)
ORDER BY
  exception_type,
  emis_term;