Definition

This Sensitive SNOMED Code model provides the SNOMED CT codes that are considered sensitive. This includes data related to areas such as gender, assisted fertilisation, sexually transmitted diseases, and other information deemed confidential.

Information

This model is created by identifying the codes defined as sensitive from within a refset or the SCR dataset. The model includes concept IDs and code IDs, which will assist with joining to other models.

Model Changes in iPCV v2

Removing duplicate and nullified SNOMED codes

SNOMED codes with NULL code IDs mean there are SNOMEDs that do not map to an EMIS code ID. These are irrelevant as the code IDs are used to join sensitive SNOMED codes to other models.

Why?

This is considered as an improvement where we are excluding sensitive SNOMED codes that will not be used.

Customer benefit

The change provides the following benefits:

• Simplified and cleaner data model.
• Elimination of nulls and duplicates that could lead to confusion.

Customer action

Customers should:

• Update any process that might counter for NULL code ids in sensitive SNOMED queries.

Schema updates for data maintenance

• The is_deleted column has been added to indicate whether a clinical code has been marked as deleted
• The data_filter column is removed as it is no longer required

Benefit

• The is_deleted column enables more effective data maintenance by allowing customers to identify and manage deleted clinical codes
• The data model is streamlined by removing deprecated columns from iPCV v1 and v2, resulting in a cleaner and more efficient structure

Customer Action

• Update your ETL processes to use the is_deleted column to manage and clean up clinical codes
• Review your queries and remove any references to the deprecated data_filter column to align with the updated schema and avoid confusion

Examples

Selecting a concept ID

SELECT
  *
FROM hive.explorer_ipcv.srv_sensitive_snomed_code
WHERE concept_id =  2007581000006106 -- example code

Finding concept IDs with more than one code ID

SELECT
  concept_id
FROM hive.explorer_ipcv.srv_sensitive_snomed_code
GROUP BY concept_id
HAVING count(code_id) > 1

Finding the terms of the example code

SELECT
  ssc.concept_id,
  cc.fully_specified_name,
  ssc.code_id,
  cc.term
FROM hive.explorer_ipcv.srv_sensitive_snomed_code AS ssc
INNER JOIN  hive.explorer_ipcv.srv_codeable_concept AS cc
ON ssc.code_id = cc.code_id
WHERE ssc.concept_id = 2007581000006106 -- example code