Skip to content

Exercises · Day 2 — Concept Sets in Atlas and SQL Validation

Primary tool: Atlas

These exercises use Atlas to build concept sets and your site's CDM for SQL validation. Open Atlas and confirm you can create a new concept set before starting.

No CDM access? Colab fallback

If you don't yet have a CDM connection, a synthetic-data companion notebook is available: Open In Colab or download it.

What you will do

  1. Build a concept set in Atlas using descendants.
  2. Inspect the "mapped" view to confirm coverage.
  3. Validate the concept set against the CDM with SQL.
  4. Read one Data Quality Dashboard result and decide whether it matters.

Setup and extraction are site specific

Every institution's environment is different. These steps use Databricks and DBeaver as the SQL client because that is what the reference site uses, but your site may use something else (for example Snowflake, Postgres, BigQuery, SQL Server, or Posit Workbench). The OMOP CDM and the SQL logic are the same everywhere. Only the connection details and the extraction tooling change. Substitute your local client, connection string, and data access steps wherever Databricks is mentioned.

Step 1: Build a concept set (warm-up)

  1. Open Atlas and go to Concept Sets, then New Concept Set.
  2. Search Athena for the ingredient sulfonylureas.
  3. Add it to the set, then turn on Include Descendants so all products and doses are captured.
  4. Save the set with a clear name, for example TtT Day2 Sulfonylureas.

Step 2: Inspect standard and mapped

  1. Open the Included Concepts tab and confirm every concept is standard.
  2. Open the Mapped view. These are the source codes that map into your standard concepts. Skim them and ask: does this look like complete coverage for your site, or are obvious codes missing?

Step 3: Validate against the CDM with SQL

Export the concept set expression SQL from Atlas, then run the count yourself in your SQL client. A simple validation query (adjust schema and client to your site):

-- How many drug exposures fall in the sulfonylurea concept set?
SELECT COUNT(*) AS exposures,
       COUNT(DISTINCT de.person_id) AS persons
FROM cdm.drug_exposure de
JOIN cdm.concept_ancestor ca
     ON ca.descendant_concept_id = de.drug_concept_id
WHERE ca.ancestor_concept_id = :sulfonylurea_ingredient_concept_id;

Compare the count to what Atlas reports. If they differ, the usual culprits are a non-standard concept in the set, a missing "include descendants" toggle, or a schema or vocabulary version mismatch.

Runnable practice without a CDM

If you do not yet have a CDM connection, the Day 1 sample notebook builds a tiny synthetic CDM in the notebook itself, so you can practice the same concept_ancestor join logic with no credentials.

Step 4: Read one data quality result

  1. Open a Data Quality Dashboard result for your training CDM (or a sample DQD report).
  2. Find one failed check and note (a) its Kahn category — conformance, completeness, or plausibility; (b) its subcategory if shown (e.g. value/relational/computational conformance, or uniqueness/atemporal/temporal plausibility); (c) its context — verification or validation; and (d) its threshold.
  3. Decide, in one sentence, whether that failure would affect a diabetes drug study. This judgment, not the pass/fail count, is the point.

The Kahn framework at a glance

Use this to place any check you find. Every DQD check is one category assessed in one context.

Category Subcategories Example check Context it's usually run in
Conformance value · relational · computational drug_concept_id exists in concept with domain_id = 'Drug' Verification (internal rules)
Completeness (none — presence only) Fraction of condition_occurrence rows with concept_id = 0 Verification (internal rules)
Plausibility uniqueness · atemporal · temporal No birth dates in the future; no drug era before birth Verification (internal rules)
Plausibility uniqueness · atemporal · temporal Diabetes prevalence matches published national estimates Validation (external benchmark)

Verification checks the data against the system's own rules and specifications; validation checks it against an external, trusted benchmark. Most DQD checks are verification; validation needs an outside source of truth.

Homework

  • Build concept sets for two more drug classes of your choice and validate each with the SQL pattern above.
  • Write two sentences on one data quality failure: what it is, and whether it threatens an analysis you care about.

Instructor notes

Show facilitation notes - Have a volunteer share their screen for the sulfonylurea build so the group sees the descendant toggle in action. - Expect the "mapped" view to surprise people. It is the fastest way to teach why non-standard concepts cause silent data loss. - The SQL validation step is where the site-specific reality lands. Ask each participant to name their own client and warehouse out loud so the group sees the variety.