Snowflake

Enterprise feature

Snowflake sync is part of Medplum Enterprise. A Medplum team member creates and enables the pipeline for your project. Contact us at hello@medplum.com to get started, or see pricing.

Medplum synchronizes your FHIR data to Apache Iceberg tables in Amazon S3 Tables on a schedule. Snowflake reads those tables in place, so there is no export job for your team to build or operate.

Use this for population health reporting, quality measure calculation, cohort analysis, and any workload that aggregates across large numbers of resources. FHIR search is designed for clinical lookups on individual patients, not for aggregation across a population.

Snowflake is one of several options. The sync writes open Iceberg tables rather than a Snowflake-specific format, so the same tables can be queried from Amazon Athena, Amazon Redshift, Apache Spark, or Trino. This page uses Snowflake for the examples. The setup on the Medplum side and the table layout are the same for any of them.

How it works

1. A sync worker runs on a schedule you choose. It reads from a read replica, so production traffic is not affected.
2. Each run writes only the resource versions created or changed since the previous run.
3. Snowflake attaches to the Iceberg catalog and queries the tables directly.

Freshness is set by the schedule. Hourly is a common starting point. This pipeline is not built for sub-minute latency: to react to individual resources in real time, use Bots and Subscriptions.

Table layout

Medplum writes one table per FHIR resource type, named after the resource type in lowercase with a _history suffix.

FHIR resource type	Table
Patient	patient_history
Observation	observation_history
Encounter	encounter_history
Condition	condition_history

Every table has the same five columns.

Column	Type	Description
id	string	Resource id. Stable across every version of the resource.
version_id	string	meta.versionId for this row.
content	string	The complete FHIR resource as JSON.
last_updated	timestamp	meta.lastUpdated for this row.
project_id	string	The Medplum project that owns the resource.

Two things to know about the shape:

These are history tables. Every version of every resource is its own row, so a Patient updated ten times contributes ten rows. Queries that want current state select the latest version per id. The pattern is below.

Resources arrive as JSON, not as columns. content holds the whole FHIR resource. This is deliberate. FHIR resources are deeply nested and sparsely populated, so flattening at export time would either drop data or produce thousands of mostly empty columns. You flatten the fields you need in Snowflake, where changing your mind costs a view definition instead of a re-export.

Querying in Snowflake

Parse content into a VARIANT and use Snowflake's semi-structured operators to read into it.

Current version of each resource

Use QUALIFY to keep the newest row per id.

create or replace view patient_current as
select
  id,
  version_id,
  last_updated,
  project_id,
  try_parse_json(content) as resource
from patient_history
qualify row_number() over (partition by id order by last_updated desc) = 1;

Deleted resources still appear, as a tombstone version carrying meta.deleted. Filter them out when you want live resources only:

select *
from patient_current
where resource:meta:deleted is null;

See Deleting Data for what a tombstone contains. Keeping the tombstones is what lets you answer "when did this resource go away," so filter at the view boundary rather than asking to have them excluded from sync.

Flattening fields

Pull out the fields your report needs:

select
  id,
  resource:birthDate::date         as birth_date,
  resource:gender::string          as gender,
  resource:name[0]:family::string  as family_name,
  resource:name[0]:given[0]::string as given_name
from patient_current
where resource:meta:deleted is null;

Repeating elements expand with LATERAL FLATTEN. This returns one row per identifier:

select
  p.id,
  i.value:system::string as identifier_system,
  i.value:value::string  as identifier_value
from patient_current p,
     lateral flatten(input => p.resource:identifier) i;

Worked example: numeric lab results

create or replace view observation_current as
select
  id,
  last_updated,
  try_parse_json(content) as resource
from observation_history
qualify row_number() over (partition by id order by last_updated desc) = 1;

select
  o.id,
  o.resource:subject:reference::string     as patient_reference,
  c.value:code::string                     as loinc_code,
  c.value:display::string                  as loinc_display,
  o.resource:valueQuantity:value::float    as result_value,
  o.resource:valueQuantity:unit::string    as result_unit,
  o.resource:effectiveDateTime::timestamp  as effective_time
from observation_current o,
     lateral flatten(input => o.resource:code:coding) c
where o.resource:meta:deleted is null
  and c.value:system::string = 'http://loinc.org'
  and o.resource:valueQuantity:value is not null;

Reports like this are only as good as the coding in the underlying data. Use Bots and Subscriptions to enforce coding at write time rather than repairing it in the warehouse. See Analytics for the coding systems involved.

Multiple projects

If your organization runs more than one Medplum project, filter on project_id. Build it into the view so that every downstream query inherits the scope:

create or replace view patient_current as
select ...
from patient_history
where project_id = '<your-project-id>'
qualify row_number() over (partition by id order by last_updated desc) = 1;

Getting set up

A Medplum team member configures the sync. To open the request, send us:

Item	Notes
AWS account and region	Where the S3 table bucket lives. Matching your Snowflake region avoids cross-region transfer.
Sync schedule	Hourly is a good default. Set it by how fresh your reports need to be.
Resource types	All types by default. You can name an include list or an exclude list, but not both.
Backfill start date	The earliest meta.lastUpdated to export. Omit it to load all history.

AuditEvent is worth a decision rather than a default. It is often the largest history table in a project, and it answers a different set of questions than clinical reporting does. Either exclude it or give it its own schedule.

On the Snowflake side, your team does three things:

1. Configure a catalog integration for Amazon S3 Tables with CATALOG_API_TYPE = AWS_S3TABLES, pointing CATALOG_NAME at the table bucket ARN.
2. Create a catalog-linked database on that integration. Snowflake discovers the namespace and its tables on its own and stays in sync, so resource types added to the sync later show up without further work.
3. Grant access on the database to the roles that will query it.

No external volume is required. S3 Tables uses catalog-vended credentials, which covers storage access as part of the catalog integration.

Self-hosted deployments configure the same pipeline through the dataWarehouse block in server config.

Access control and compliance

Access Policies stop at the warehouse boundary. Once a resource is in Snowflake, Medplum Access Policies no longer apply to it, and Snowflake's own roles and grants are the only thing standing between a user and the data. Treat the warehouse as a PHI system: put it in scope for your BAA, your access reviews, and your audit logging.

Two practices that follow from this:

• Grant on views, not on the history tables. A view that filters project_id, drops tombstones, and selects only the fields a team needs is easier to reason about than a policy on raw FHIR JSON.
• De-identify in Snowflake for analytics that do not need identity. Cohort and quality measure work usually does not need names, addresses, or contact details. Build a de-identified view layer and point most consumers at that.

What this does not do

• It does not write back. The sync is one directional. Changes made in Snowflake never reach the FHIR datastore.
• It does not carry attachment bytes. Binary resources sync as their FHIR JSON. The underlying file content stays in Medplum's storage service.
• It does not replace real-time workflows. Anything that needs to happen within seconds of a resource changing belongs in a Bot.

Other warehouses

The tables are open Iceberg, so the engine is your choice. Everything on this page applies except the query syntax: the schedule, the table layout, the five columns, and the history semantics are the same.

• Amazon Athena and Amazon Redshift read S3 Tables through the AWS Glue Data Catalog integration.
• Apache Spark and Amazon EMR read them through the S3 Tables catalog.
• Trino connects to the S3 Tables Iceberg REST endpoint using its Iceberg connector with SigV4 authentication.

Tell us which engine you are using when you open the request and we will point the catalog at it.

If you are not on Medplum Enterprise, the Bulk FHIR API exports resources as NDJSON that you can stage and load yourself. It runs on demand rather than on a schedule, and you own the loading and the incremental logic.

See also

• Analytics for program design, coding systems, and standard measures
• Bulk FHIR API
• Access Policies
• Server config: dataWarehouse