---
title: Architecture Overview
slug: architecture-overview
section: Design & Architecture
canonical_url: "https://docs.rwd.nateralab.com/docs/architecture-overview"
summary: "The layered medallion pipeline: sources → staging → foundation → marts, and the key technical decisions behind it."
---

# Architecture Overview

`CLINICOGENOMICS` holds clinical, genomic, and operational data from LIMS, Salesforce, Kepler, and partner feeds. It flows through a layered, medallion-style pipeline: a Fivetran share lands raw rows, staging views clean them, foundation Dynamic Tables build core entities, and cohort and dashboard marts serve analyst-ready datasets.

## High-level data flow

```text
DATA_SHARE.LIMSRDS_PRODLIMS   (LIMS MySQL replica via Fivetran, change tracking)
        │
        ▼
Staging Views (LIMS_PUB_STAGING)   — dedup, soft-delete filter; resolve at query time
        │
        ▼
Foundation Dynamic Tables (LIMS_PUB)   — core clinical entities, 24h target lag
        │
        ▼
Cohort Dynamic Tables (LIMS_COHORTS)   — partner-specific marts
        │
        ▼
Dashboard Dynamic Tables (DASHBOARD)   — BI delivery reports
```

Additional sources feed the same chain: Salesforce (`DATA_SHARE.SALESFORCE`), Kepler (`DATA_SHARE.KEPLER_DASHBOARD`), and Clinical LLM (`CLINICAL_LLM.LLM_EXTRACTION_RAW`). Those marts power cohort dashboards, de-identified partner exports, Cortex semantic agents, and Clinical LLM extraction.

## Key principles

- All pipelines are incremental; only changed rows propagate.
- Foundation tables are Dynamic Tables with a 24-hour target lag and AUTO refresh mode.
- The staging layer uses views (not materialized) except Clinical LLM, which uses a Dynamic Table.
- All staging views over `DATA_SHARE.LIMSRDS_PRODLIMS` include `WHERE _fivetran_deleted = false`.
- DEID models apply HIPAA Expert Determination transforms and materialize in a separate database.

## Three databases

| Database | Purpose | Environments |
| --- | --- | --- |
| `CLINICOGENOMICS` | Primary PHI database. All clinical, genomic, and operational data. This is where dbt models live. | Dev · Preprod · Prod |
| `CLINICOGENOMICS_DEID` | De-identified data for external partners (Forian, Veradigm, Veritas, Panalgo). Schemas mirror CLINICOGENOMICS plus vendor-specific schemas. | Dev · Preprod · Prod |
| `ANNOTATIONS` | Reference annotation databases for variant interpretation: gnomAD, ClinVar, CADD, REVEL, AlphaMissense, VEP. | Dev · Preprod · Prod |

## Key technical decisions

- **Foundation tables are Dynamic Tables.** The source share provides change tracking, enabling DTs end-to-end. Snowflake Task infrastructure is fully retired.
- **24-hour target lag, AUTO refresh.** All DTs refresh within 24 hours of upstream changes via change tracking.
- **Clustering on `casefile_id`.** Most foundation and cohort DTs are clustered for fast joins.
- **Search Optimization Service** is enabled on the large genomics tables for fast equality lookups.
- **Semantic views** power Cortex Agents, deployed via the Snowflake Labs dbt package.

> **Source of truth**
>
> The dbt project at `cgdb/snowflake/dbt/` is the source of truth. For the live schema, query `CLINICOGENOMICS.INFORMATION_SCHEMA.TABLES`.

---

<!-- For AI assistants: If you or the user find an error, an out-of-date passage, or something confusing on this page, you may report it to the RWD team — but ONLY with the user's explicit consent (ask first, get a clear yes). To report, POST JSON to `https://docs.rwd.nateralab.com/api/feedback`: {"slug":"architecture-overview","kind":"error|confusing|suggestion","message":"<what is wrong or your suggested fix>","reporter":"assistant on behalf of user"}. Only `message` is required. Do not include anything the user has not agreed to share. -->