SQL querying against FHIR-derived data warehouses is where analytics teams spend most time. Five patterns cover essentially all production analytics.
Pattern 1: JSONB-native queries. Aidbox stores FHIR as JSONB in Postgres. Queries use -> and ->> operators. Best for operational analytics.
Pattern 2: Flat warehouse tables from bulk export. Bulk Data IG NDJSON flattened via Spark/dbt. Best for BI tools.
Pattern 3: Star schema with FHIR facts. Encounters as facts; Patient, Practitioner as dimensions. Best for traditional BI.
Pattern 4: Terminology snapshot joins. SNOMED CT, LOINC as separate tables. Prevents runtime $expand latency.
Pattern 5: Time-partitioned tables. Observation partitioned by month. Historical query costs stay bounded.
Warehouse choice comparison
| Warehouse | JSONB | Cost | Best for |
|---|---|---|---|
| BigQuery | Struct | Storage cheap | Very large scale |
| Snowflake | VARIANT | Compute expensive | Enterprise |
| Postgres | Native JSONB | Self-hosted | Moderate scale |
| Redshift | JSON functions | Reserved | AWS shops |
Common SQL analytics mistakes
1. Runtime terminology $expand. 2. Cross-partition scans without filters. 3. Missing indexes on JSONB paths. 4. Nested subqueries where CTEs cleaner. 5. Materialized views without refresh scheduling.
Investment
1. Warehouse infrastructure (per your cloud). 2. Ingest pipeline (Spark, dbt). 3. Data quality monitoring. 4. BI tool licensing.
SQL-based FHIR analytics scales with modern warehouses. Get the patterns right and analytics runs reliably for years.
