Common Query Patterns

This page describes the structural patterns that underpin most analytical workloads built on iPCV. Rather than listing individual query examples — which are available within each model’s own documentation — this page focuses on how to approach common scenarios and which models to reach for.

Getting Started Patterns

Data Freshness Check

Before running a report or extraction, verify that the relevant models have completed their latest processing cycle. Query the data_freshness model in your iPCV flavour to confirm the last successful refresh for each model you intend to use.

See Data Freshness & Availability for detail on refresh timing and the data_freshness table.

Initial Full Load

For most customers the recommended starting point is a full extraction of the required models, stored locally. Subsequent runs then use delta processing to keep the local copy synchronised rather than re-extracting everything each day.

See Understanding Deltas for the recommended loading pattern.

Incremental Sync

Use transform_datetime on the main tables to identify records that have changed since your last successful load. Apply deletions using is_deleted and upsert everything else.

Do not use the _delta suffix tables as your primary sync mechanism unless you are migrating from v1 — the main tables have no 28-day retention limit.

Recovery from Missed Cycles

Because main tables and delta tables carry only the single latest state per record, a missed processing cycle does not require a full reload. Resume from your last stored transform_datetime and the correct delta will be returned.

Common Analytical Scenarios

Each scenario below describes the typical question, the models most commonly involved, and the key join strategy. Individual model pages contain schema detail and worked query examples.

Patient Population Analysis

Typical questions: How many active patients are registered? What is the demographic breakdown of a population? Which patients meet a specific cohort criteria?

Key models: Patient, Patient Filter Options, Organisation

Pattern: Start from Patient with appropriate registration filters applied. Join to Organisation where organisation-level breakdowns are required. Use Patient Filter Options to apply cohort inclusion criteria without hardcoding filter logic into the query.

Clinical Activity Analysis

Typical questions: What coded events were recorded in a period? What are the most frequently recorded conditions? How has clinical activity changed over time?

Key models: Observation, Problem, Consultation, Consultation Section, Referral, Allergy, Immunisation

Pattern: Filter Observation by effective_date range and SNOMED concept. Join to Patient for demographic context. Join to Consultation or Consultation Section where encounter-level context is needed. For cohort-level trend analysis, aggregate by effective_date truncated to the required period granularity.

Medication Analysis

Typical questions: Which medications were prescribed in a period? Which patients are on a specific drug? What prescribing trends exist across a population?

Key models: Drug Record, Issue Record, Problem Medication Link

Pattern: Join Drug Record to Issue Record on the drug record key to obtain individual prescriptions. Use Problem Medication Link to associate prescriptions with a clinical problem. Filter on effective_date or issue_date as appropriate to the analytical question.

Appointment Analysis

Typical questions: How many appointments occurred in a period? What is utilisation by organisation or service? How are appointment types distributed?

Key models: Appointment Slot, Appointment Session, Appointment Session User

Pattern: Join Appointment Slot to Appointment Session for session-level context. Join Appointment Session User to identify the clinician. Filter on appointment date and status fields.

Organisation Analysis

Typical questions: Which patients are registered with an organisation? Which locations does an organisation operate? How do organisations share data under a DSA?

Key models: Organisation, Organisation Location, Sharing Organisations, Location

Pattern: Join Patient to Organisation on the organisation key. Use Sharing Organisations to understand the scope of data sharing agreements applicable to each organisation. Use Organisation Location and Location to resolve physical site detail.

Common Model Relationships

Most reporting scenarios combine Patient with one or more domain models. The table below shows the most frequently used combinations.

Analytical Scenario	Models
Patient observations	Patient + Observation
Medication reporting	Patient + Drug Record + Issue Record
Referral analysis	Patient + Referral
Appointment reporting	Patient + Appointment Slot + Appointment Session
Population reporting	Patient + Organisation
Clinical activity reporting	Patient + Observation + Consultation
Cohort filtering	Patient + Patient Filter Options

Review the Entity Relationship Diagrams (ERDs) in the Models section for join keys and cardinality.

Common Reporting Patterns

Point-in-Time Reporting

Represent the state of data at a specific date. Filter on effective_date (for clinical events) or transform_datetime (for iPCV processing state). Avoid mixing event dates and processing dates in the same filter unless intentional.

Typical use cases: monthly population snapshots, period-based service utilisation reports.

Trend Analysis

Aggregate clinical or operational events by a time dimension (day, week, month, quarter). Use effective_date as the time axis and group by an appropriate date truncation function. Apply a consistent date range filter to all models involved so the period boundary is uniform.

Typical use cases: prescribing trends, appointment demand over time, longitudinal disease progression.

Cohort Analysis

Define a patient cohort using inclusion and exclusion criteria, then join clinical or operational models to that cohort. Define the cohort once — in a CTE or subquery — and join everything else to it, rather than applying patient-level filters repeatedly across multiple model joins.

Typical use cases: patients with a specific diagnosis, patients on a named medication, patients within an age band and registration status.

Data Warehouse Synchronisation

Combine an initial full extraction with ongoing delta processing. Track the last successfully processed transform_datetime per model, use it as the lower bound on the next incremental load, and upsert or delete records accordingly.

See Understanding Deltas for the full recommended pattern.

Query Design Recommendations

Start narrow. Apply patient, date, and concept filters as early as possible. Only join additional models once the base population is filtered.
Join on native key types. Avoid casting join columns — it prevents Trino from using column statistics and Iceberg file pruning. See Trino Query Optimisation.
Use effective_date for clinical questions, transform_datetime for pipeline questions. Conflating the two is a common source of incorrect results.
Understand your privacy configuration before interpreting counts. Record counts vary by customer configuration. See Privacy & Filtering Controls.
Check data freshness before scheduled runs. Do not assume a model is ready — query data_freshness first.

Frequently Asked Questions

Which model should I start with?

Start with Patient. Most analytical scenarios require a defined patient cohort before joining clinical, medication, appointment or organisation models.

How do I know which models to use together?

Review the data model documentation and ERDs in the Models section. Each model page describes its relationships and common join patterns.

How do I identify records that have changed since my last extraction?

See Understanding Deltas.

Where are the individual query examples?

Each model page in the Models section contains schema detail and worked examples specific to that model.