Zus Operations Data Marts

Zus Operations data marts give you a self-serve view of the work Zus does on your behalf. Use them to see which of your patients are enrolled in which packages, track how enrollments change over time, and monitor the patient history (chart build) jobs run for your organization.

Because these tables live in your Snowflake data mart environment, you can join them to your other Zus data marts and build your own reports and dashboards, without waiting on a manual readout.

What's included

Availability and access

• Location: Database ZUS_OPERATIONS, schema PUBLIC, shared into your Snowflake environment alongside your other Zus data products.
• Data scope: You only see data for your own organization's patients and requests.
• Refresh schedule: Nightly.
• Joining to other data: Use upid (Zus Universal Patient ID) to join these tables to each other and to your other Zus patient-level data marts.

patient_enrollments

A complete history of your patients' package enrollments. Each time an enrollment changes (a patient is enrolled, unenrolled, or set to pending), a new row is added.

How to read this table

This is a history table, not a snapshot. A single enrollment can have many rows, one for each change, so keep these points in mind:

• To find current enrollments, filter on is_actively_enrolled = TRUE.
• enrollment_action describes an event, not a patient's current state. For example, a row with enrolled means the patient was enrolled at that time. They may have been unenrolled since.
• effective_to is NULL on the most recent event for an enrollment. A NULL value does not mean the patient is currently enrolled.
• Enrollments can move in any direction. A patient can be re-enrolled after being unenrolled, or set back to pending during a package change.
• One person can have more than one enrollment in the same package. This is expected. See Multiple enrollments for the same person.

Multiple enrollments for the same person

Zus links every patient record that represents the same person to a single upid. If your organization has written more than one FHIR Patient record for the same person (for example, duplicate records created in your system, or records created before and after a system migration), each record can have its own enrollment. You may then see two or more enrollments in the same package that share a upid but have different patient_id and enrollment_id values.

This is not inherently an error, and it's most common in older historical data. Choose the ID that fits your question:

To find people with more than one enrollment in the same package, see Find people with multiple enrollments in a package.

Columns

patient_history_jobs

One row for each patient history job requested for your patients, showing who initiated it and where it stands.

Columns

Understanding job status

• An error job may later show done. Zus retries failed jobs, so treat job_status as the latest known state rather than a final outcome.
• done means the job finished, but data from some sources can continue to arrive after a job completes.

Example queries

These are simple starting points. For more advanced patterns, including joins to patient names and your own patient IDs, see the Query cookbook at the end of this page.

The examples below use the default database name ZUS_OPERATIONS. If the share was mounted under a different name in your Snowflake account, substitute that name.

Count current enrollments by package

SELECT
    package_name,
    COUNT(DISTINCT upid) AS enrolled_patients
FROM ZUS_OPERATIONS.PUBLIC.patient_enrollments
WHERE is_actively_enrolled = TRUE
GROUP BY package_name
ORDER BY enrolled_patients DESC;

This counts people (distinct upid), so a person with two enrollments in the same package is counted once. To count enrollments instead, use COUNT(DISTINCT enrollment_id).

Review a patient's enrollment history

Filtering on upid returns every enrollment for that person, which may include enrollments tied to more than one of your patient records. Include patient_id and enrollment_id to tell them apart.

SELECT
    upid,
    patient_id,
    enrollment_id,
    package_name,
    enrollment_action,
    is_actively_enrolled,
    effective_from,
    effective_to
FROM ZUS_OPERATIONS.PUBLIC.patient_enrollments
WHERE upid = '<upid>'
ORDER BY package_name, patient_id, effective_from;

Find people with multiple enrollments in a package

Lists people who have more than one enrollment in the same package, and how many are currently active. Useful for spotting duplicate patient records in your own system.

SELECT
    upid,
    package_name,
    COUNT(DISTINCT enrollment_id)                              AS enrollments,
    COUNT(DISTINCT patient_id)                                 AS patient_records,
    COUNT(DISTINCT IFF(is_actively_enrolled, enrollment_id, NULL)) AS active_enrollments,
    ARRAY_AGG(DISTINCT patient_id)                             AS patient_ids
FROM ZUS_OPERATIONS.PUBLIC.patient_enrollments
WHERE upid IS NOT NULL
GROUP BY upid, package_name
HAVING COUNT(DISTINCT enrollment_id) > 1
ORDER BY active_enrollments DESC, enrollments DESC;

Find patients unenrolled in the last 30 days

SELECT
    upid,
    package_name,
    effective_from AS unenrolled_at
FROM ZUS_OPERATIONS.PUBLIC.patient_enrollments
WHERE enrollment_action = 'unenrolled'
  AND effective_to IS NULL
  AND effective_from >= DATEADD('day', -30, CURRENT_TIMESTAMP())
ORDER BY unenrolled_at DESC;

Count chart build jobs by month

SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS chart_build_jobs
FROM ZUS_OPERATIONS.PUBLIC.patient_history_jobs
WHERE initiated_by = 'chart build'
GROUP BY month
ORDER BY month DESC;

Check job outcomes over the last 30 days

SELECT
    initiated_by,
    job_status,
    COUNT(*) AS jobs
FROM ZUS_OPERATIONS.PUBLIC.patient_history_jobs
WHERE created_at >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY initiated_by, job_status
ORDER BY initiated_by, job_status;

Known limitations

These apply during the beta and may change:

• Refresh is nightly. Changes made today appear after the next nightly refresh.
• Scheduled jobs show as freshmaker. Jobs your organization scheduled in advance currently show initiated_by = 'freshmaker' rather than chart build.
• upid reflects the value when the row was last processed. If patient records are later merged, older rows may keep the previous upid:
  • In patient_history_jobs, a job keeps the upid from when it last ran once it reaches done or error.
  • In patient_enrollments, historical rows update only when the enrollment has a new event.
• A very small number of jobs (well under 1%) reference patients that no longer exist in your patient records.
• target_date may be slightly earlier than created_at on some jobs, by a few moments. This does not affect the job.

Share feedback

This feature is in beta, and your input shapes what comes next. If there are fields, statuses, or use cases you'd like us to support, let your Zus representative know.

Query cookbook

These queries cover the most common questions customers answer with Zus Operations data. Several of them join to your FHIR Relational Data Marts to show patient names, birth dates, and your own patient identifiers, so you can match results against your own systems.

Before you run them, replace the placeholders:

Tip: Join on patient_id (not upid) when you want your version of the patient record. patient_id is the FHIR Patient your organization wrote to Zus, and it's populated even when upid isn't yet resolved. Joining on upid returns every source's record for that person, which can mean multiple rows per patient.

Counting people vs. records: a person can have more than one enrollment in the same package if your organization has more than one Patient record for them (see Multiple enrollments for the same person). Queries that group by upid count people; queries that group by patient_id or enrollment_id count your records. Queries 3 and 5 note which one they use.

The queries alias some columns to plain-language names (changed_at, superseded_at, is_current) to make results easier to read.

1. Current enrollments (one row per enrollment)

A snapshot of current state: each enrollment's latest event, without the full history.

SELECT
    enrollment_id,
    patient_id,
    upid,
    package_name,
    enrollment_action      AS current_state,
    effective_from         AS state_since,
    is_actively_enrolled
FROM ZUS_OPERATIONS.PUBLIC.patient_enrollments
WHERE effective_to IS NULL
ORDER BY package_name, state_since;

2. Current enrollments with patient name, birth date, and your ID

Adds human-readable identifiers so you can find each patient in your own system.

WITH current_enrollments AS (
    SELECT patient_id, upid, package_name, effective_from AS enrolled_since
    FROM ZUS_OPERATIONS.PUBLIC.patient_enrollments
    WHERE is_actively_enrolled = TRUE
),
your_ids AS (
    SELECT pi.patient_id, pi.value AS your_patient_id
    FROM <COMPANY>_EXPORT_ZUS.<COMPANY>_RELATIONAL.patient_identifier pi
    WHERE pi.system ilike '<your-identifier-system>'
)
SELECT
    ce.package_name,
    y.your_patient_id,
    p.name_family             AS last_name,
    p.name_given_1            AS first_name,
    p.birth_date,
    ce.upid,
    ce.patient_id,
    ce.enrolled_since
FROM current_enrollments ce
LEFT JOIN <COMPANY>_EXPORT_ZUS.<COMPANY>_RELATIONAL.patient p ON p.id = ce.patient_id
LEFT JOIN your_ids y ON y.patient_id = ce.patient_id
where y.your_patient_id is not NULL
ORDER BY ce.package_name, last_name, first_name;

3. Full activity timeline for one patient

Every enrollment event and patient history job for a single patient, in order. This is the starting point for investigating any individual patient.

WITH target AS (
    SELECT '<upid>' AS upid
)
SELECT
    e.effective_from       AS event_at,
    'enrollment'           AS event_type,
    e.package_name         AS detail,
    e.enrollment_action    AS changed_to,
    e.effective_to         AS superseded_at,
    NULL                   AS job_id
FROM ZUS_OPERATIONS.PUBLIC.patient_enrollments e
JOIN target t ON e.upid = t.upid

UNION ALL

SELECT
    j.created_at           AS event_at,
    'patient history job'  AS event_type,
    j.initiated_by         AS detail,
    j.job_status           AS changed_to,
    NULL                   AS superseded_at,
    j.job_id
FROM ZUS_OPERATIONS.PUBLIC.patient_history_jobs j
JOIN target t ON j.upid = t.upid

ORDER BY event_at;

Because this filters on upid, it includes activity for every Patient record your organization has for this person. Add patient_id to the output if you need to tell them apart.

To look a patient up by your own ID instead, replace the target CTE with a lookup through patient_identifier and identifier (see query 2), and join on patient_id.

4. Enrolled patient-months

One row per patient, per package, per calendar month in which the patient was enrolled at any point. Use this to estimate usage trends month to month.

WITH enrolled_periods AS (
    SELECT
        upid,
        patient_id,
        package_name,
        effective_from                                AS period_start,
        COALESCE(effective_to, CURRENT_TIMESTAMP())   AS period_end
    FROM ZUS_OPERATIONS.PUBLIC.patient_enrollments
    WHERE enrollment_action = 'enrolled'
),
months AS (
    SELECT month_start
    FROM (
        SELECT DATEADD('month', ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1, '2023-01-01'::DATE) AS month_start
        FROM TABLE(GENERATOR(ROWCOUNT => 120))
    )
    WHERE month_start <= CURRENT_DATE()
)
SELECT DISTINCT
    m.month_start          AS enrolled_month,
    ep.package_name,
    ep.patient_id,
    ep.upid
FROM enrolled_periods ep
JOIN months m
  ON ep.period_start < DATEADD('month', 1, m.month_start)
 AND ep.period_end   >= m.month_start
ORDER BY enrolled_month, package_name;

5. Monthly enrolled-patient trend

Builds on query 4 to show enrolled patients per month and the change from the prior month. This counts distinct patient_id (your records). Switch to COUNT(DISTINCT upid) to count people instead; the two differ when a person has more than one enrollment in the same package.

WITH patient_months AS (
    -- paste query 4 here, without the ORDER BY
),
monthly AS (
    SELECT enrolled_month, package_name, COUNT(DISTINCT patient_id) AS enrolled_patients
    FROM patient_months
    GROUP BY enrolled_month, package_name
)
SELECT
    enrolled_month,
    package_name,
    enrolled_patients,
    enrolled_patients
      - LAG(enrolled_patients) OVER (PARTITION BY package_name ORDER BY enrolled_month)
      AS change_from_prior_month
FROM monthly
ORDER BY package_name, enrolled_month DESC;

6. Enrollment adds and removals by month

How many enrollments started and ended each month. Useful for spotting unexpected spikes, like a sync job that unenrolled a batch of patients.

SELECT
    DATE_TRUNC('month', effective_from)                          AS month,
    package_name,
    COUNT_IF(enrollment_action = 'enrolled')                     AS enrolled_events,
    COUNT_IF(enrollment_action = 'unenrolled')                   AS unenrolled_events,
    COUNT_IF(enrollment_action = 'enrollment_pending')           AS pending_events
FROM ZUS_OPERATIONS.PUBLIC.patient_enrollments
GROUP BY month, package_name
ORDER BY month DESC, package_name;

7. Enrolled patients with no completed patient history job

Currently enrolled patients who don't have a done job since they were enrolled. A good check that enrollment is triggering the work you expect.

WITH current_enrollments AS (
    SELECT patient_id, upid, package_name, effective_from AS enrolled_since
    FROM ZUS_OPERATIONS.PUBLIC.patient_enrollments
    WHERE is_actively_enrolled = TRUE
)
SELECT
    ce.package_name,
    ce.upid,
    ce.patient_id,
    ce.enrolled_since,
    MAX(j.created_at)      AS last_job_created_at,
    MAX_BY(j.job_status, j.created_at) AS last_job_status
FROM current_enrollments ce
LEFT JOIN ZUS_OPERATIONS.PUBLIC.patient_history_jobs j
       ON j.patient_id = ce.patient_id
      AND j.created_at >= ce.enrolled_since
GROUP BY ce.package_name, ce.upid, ce.patient_id, ce.enrolled_since
HAVING COUNT_IF(j.job_status = 'done') = 0
ORDER BY ce.enrolled_since;