Skip to main content

Getting started

This page gets you from a connection to a query you can trust. The SQL is written in ANSI style and should run with minor dialect changes on any warehouse Dax is deployed to.

Find your way around​

Everything you query day to day lives in dax_core. Start by listing what is there:

select table_name
from information_schema.tables
where table_schema = 'dax_core'
order by table_name;

The tables you will use most:

TableGrainUse it for
patientone row per person per sourcedemographics
member_monthone row per person per monthdenominators
encounterone row per episode of carevisit and admission counts
medical_claimone row per claim lineline-level codes and cost
pharmacy_claimone row per pharmacy claim linefills, days supply
eligibilityone row per enrollment spanwho was covered when
costone row per member monthspend by service category
utilizationone row per member monthencounter counts by category

Check the data is current​

Every core table carries dax_last_run, the timestamp of the build that produced the row. Confirm you are not reading a stale table:

select max(dax_last_run) as last_build
from dax_core.patient;

Rule 1: count denominators from member months​

The most common mistake in healthcare analytics is dividing by the wrong denominator. Do not count distinct patients who happen to appear in a claims table — that silently excludes everyone who was covered but did not seek care, which is exactly the population you usually care about.

-- Covered members and member months in 2025
select
    count(distinct person_id) as members,
    count(*)                  as member_months
from dax_core.member_month
where year_month between '202501' and '202512';

Rule 2: count episodes from encounter, not claim lines​

A single inpatient stay can produce dozens of claim lines. Counting medical_claim rows overstates utilization by an order of magnitude. Use encounter, which has already grouped those lines:

-- Inpatient admissions and average length of stay, 2025
select
    encounter_type,
    count(*)                as admissions,
    avg(length_of_stay)     as avg_los,
    sum(paid_amount)        as total_paid
from dax_core.encounter
where encounter_group = 'inpatient'
  and encounter_start_date >= date '2025-01-01'
  and encounter_start_date  < date '2026-01-01'
group by encounter_type
order by admissions desc;

Rule 3: carry data_source through joins​

Several core tables are grained by data_source as well as their business key, because the same person or encounter can be reported by more than one contributing system. Check the Grain row on a table's reference page, and include every grain column in your joins:

select
    p.person_id,
    p.age_group,
    e.encounter_type,
    e.paid_amount
from dax_core.patient   as p
join dax_core.encounter as e
  on  p.person_id   = e.person_id
  and p.data_source = e.data_source;   -- part of the grain of both tables

Omitting data_source here produces a fan-out: every patient row matches every same-person encounter row from every other source, and your totals inflate.

Putting it together: PMPM​

Per-member-per-month cost is the canonical measure, and it is a straight join because cost and member_month share a grain:

select
    c.year_month,
    count(distinct c.person_id)                        as members,
    sum(c.total_paid)                                  as total_paid,
    sum(c.total_paid) / nullif(count(*), 0)            as pmpm
from dax_core.cost as c
where c.year_month between '202501' and '202512'
group by c.year_month
order by c.year_month;

To decompose that spend, swap total_paid for any of the service-category columns — inpatient_paid, emergency_department_paid, pharmacy_paid and so on. They sum to the total, so a decomposition always reconciles. The cost reference page lists every category.

Cost per encounter​

Because cost and utilization share the member-month grain, combining them needs no aggregation:

select
    c.year_month,
    sum(c.inpatient_paid)                                          as inpatient_paid,
    sum(u.inpatient_count)                                         as admissions,
    sum(c.inpatient_paid) / nullif(sum(u.inpatient_count), 0)      as paid_per_admission
from dax_core.cost        as c
join dax_core.utilization as u
  on c.member_month_id = u.member_month_id
where c.year_month between '202501' and '202512'
group by c.year_month
order by c.year_month;

Before you trust a number​

Check what the warehouse already knows about the weaknesses in your data. The dax_data_quality schema carries per-source flags and structural test results:

select *
from dax_data_quality.structural_test_results
order by data_source;

Two habits worth forming:

  • Look at utilization.orphaned_claim_count for the period you are measuring. Claims that could not be grouped into an encounter are invisible to encounter-based counts, and a spike there usually explains a surprising dip.
  • Check eligibility coverage before trusting any rate. If enrollment data is missing for a payer, your denominator is wrong no matter how good the claims are.

Next steps​

Last updated on