SQL querying against FHIR-derived data warehouses is where analytics teams spend most engineering time. Seven patterns cover essentially all production analytics work.
Pattern 1: JSONB-native queries on Aidbox-style storage. Aidbox stores FHIR as JSONB in Postgres. SQL queries use -> and ->> operators. Best for real-time queries against operational data.
Pattern 2: Flat warehouse tables from bulk export. Bulk Data IG NDJSON flattened to warehouse tables. SQL against flat schemas. Best for analytics separate from operations.
Pattern 3: Star schema with FHIR facts. Encounters/Observations as facts; Patient, Practitioner as dimensions. Best for BI tools (Tableau, Looker).
Pattern 4: dbt-managed transformation layer. Raw NDJSON → dbt models → analytics-friendly tables. Best for team-scale analytics engineering.
Pattern 5: Terminology snapshot joins. SNOMED CT, LOINC as separate tables joined at query time. Prevents runtime $expand latency.
Pattern 6: Time-partitioned Observation tables. Partition Observation by month or year. Query performance stays constant as historical data accumulates.
Pattern 7: Materialized views for common queries. Frequently-run queries materialized nightly. Trade-off: freshness vs. speed.
Warehouse choice comparison
| Warehouse | JSONB support | Cost profile | Best for |
|---|---|---|---|
| BigQuery | Struct queries | Storage cheap | Very large scale |
| Snowflake | VARIANT | Compute expensive | Enterprise |
| Postgres (analytics replica) | Native JSONB | Self-hosted | Moderate scale |
| Redshift | JSON functions | Reserved instances | AWS shops |
Common SQL analytics mistakes
1. Runtime terminology $expand — very slow. 2. Cross-partition scans without filters. 3. Missing indexes on JSONB paths. 4. Nested subqueries where CTEs are cleaner. 5. Materialized views without refresh scheduling.
SQL-based FHIR analytics is well-supported by modern warehouses. Get the patterns right and analytics scales for years.
