### Initialize and Configure Tuva Demo Environment

Source: https://thetuvaproject.com/getting-started

Commands to clone the demo repository, set up a Python virtual environment, and install the necessary dbt-duckdb adapter. These steps prepare the local system for running healthcare data transformations.

```bash
git clone https://github.com/tuva-health/demo.git
cd demo
python3 -m venv .venv
source .venv/bin/activate
python -m pip install --upgrade pip
python -m pip install dbt-duckdb
```

--------------------------------

### Install EMPI Lite as a Local dbt Package

Source: https://thetuvaproject.com/empi-lite/getting-started

Integrates EMPI Lite into an existing dbt project by referencing it as a local package in the 'packages.yml' file. After adding the local path, 'dbt deps' must be run to install it. The project's 'dbt_project.yml' must also include the EMPI Lite variables.

```yaml
packages:
  - local: ../empi_lite/empi_lite    # path relative to your project root
```

--------------------------------

### Configure Source Tables

Source: https://thetuvaproject.com/empi-lite/getting-started

Configuration snippet for models/sources.yml to define the source database and schema for EMPI Lite inputs.

```yaml
sources:
  - name: empi_input
    database: "{{ var('empi_input_database') }}"
    schema:   "{{ var('empi_input_schema') }}"
```

--------------------------------

### Inspect DuckDB Output

Source: https://thetuvaproject.com/getting-started

Instructions for installing the DuckDB CLI and querying the generated core tables. These commands allow users to verify that the patient and encounter data were successfully loaded and transformed.

```bash
brew install duckdb
duckdb demo.duckdb
```

```sql
select count(*) from core.patient;
select count(*) from core.encounter;
```

--------------------------------

### Initialize Manual Review Tables

Source: https://thetuvaproject.com/empi-lite/getting-started

SQL DDL statements to create the necessary tables for manual review decisions in the empi_manual_review schema.

```sql
CREATE SCHEMA IF NOT EXISTS your_database.empi_manual_review;

CREATE TABLE IF NOT EXISTS your_database.empi_manual_review.match_review_decisions (
    data_source_a       VARCHAR     NOT NULL,
    source_person_id_a  VARCHAR     NOT NULL,
    data_source_b       VARCHAR     NOT NULL,
    source_person_id_b  VARCHAR     NOT NULL,
    is_match            BOOLEAN,
    reviewed            BOOLEAN,
    reviewer_name       VARCHAR,
    review_date         DATE,
    notes               VARCHAR
);

CREATE TABLE IF NOT EXISTS your_database.empi_manual_review.split_review_decisions (
    data_source         VARCHAR     NOT NULL,
    source_person_id    VARCHAR     NOT NULL,
    is_split            BOOLEAN,
    reviewed            BOOLEAN,
    reviewer_name       VARCHAR,
    review_date         DATE,
    notes               VARCHAR
);
```

--------------------------------

### Routine dbt Operations Cadence

Source: https://thetuvaproject.com/empi-lite/getting-started

Outlines the standard operational workflow for the dbt EMPI Lite project after initial setup. This includes running snapshots and models after source data refreshes, and a separate process for handling the review queue.

```bash
# On each source data refresh:  
dbt snapshot && dbt run  

# Process review queue (ad hoc or scheduled):  
# 1. Query empi_review_queue_matches and empi_review_queue_splits  
# 2. Insert decisions into empi_manual_review tables  
# 3. Re-run: dbt run
```

--------------------------------

### Configure dbt Profiles for Warehouses

Source: https://thetuvaproject.com/empi-lite/getting-started

Templates for configuring the 'empi_lite' profile in ~/.dbt/profiles.yml for various supported data warehouses. These configurations define connection parameters and authentication methods.

```yaml
empi_lite:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: <your_account>
      user: <your_user>
      password: <your_password>
      role: <your_role>
      warehouse: <your_warehouse>
      database: <your_database>
      schema: empi
      threads: 4
```

```yaml
empi_lite:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: <your_gcp_project>
      dataset: empi
      threads: 4
      timeout_seconds: 300
```

```yaml
empi_lite:
  target: dev
  outputs:
    dev:
      type: redshift
      host: <your_cluster>.redshift.amazonaws.com
      user: <your_user>
      password: <your_password>
      port: 5439
      dbname: <your_database>
      schema: empi
      threads: 4
```

```yaml
empi_lite:
  target: dev
  outputs:
    dev:
      type: fabric
      driver: ODBC Driver 18 for SQL Server
      server: <your_server>.sql.azuresynapse.net
      port: 1433
      database: <your_database>
      schema: empi
      authentication: CLI
      threads: 4
```

```yaml
empi_lite:
  target: dev
  outputs:
    dev:
      type: databricks
      host: <your_workspace>.azuredatabricks.net
      http_path: /sql/1.0/warehouses/<your_warehouse_id>
      token: <your_pat_token>
      catalog: <your_catalog>
      schema: empi
      threads: 4
```

```yaml
empi_lite:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: /path/to/empi.duckdb
      schema: empi
      threads: 4
```

--------------------------------

### Execute Tuva Build and Verification

Source: https://thetuvaproject.com/getting-started

Commands to set environment variables, verify the dbt connection, install project dependencies, and execute the full dbt build. This process transforms the synthetic healthcare data within the DuckDB environment.

```bash
export DBT_PROFILES_DIR="$PWD/.dbt"
export DEMO_DUCKDB_PATH="$PWD/demo.duckdb"
dbt debug
dbt deps
dbt build
```

--------------------------------

### Clone EMPI Lite Repository

Source: https://thetuvaproject.com/empi-lite/getting-started

Commands to clone the project repository and navigate to the dbt project root directory. The inner directory is required for executing dbt commands.

```bash
git clone <your-repo-url> empi_lite
cd empi_lite/empi_lite
```

--------------------------------

### Run dbt Project Models

Source: https://thetuvaproject.com/empi-lite/getting-started

Executes all staging, intermediate, and final models in the dbt project. The initial run can take a significant amount of time depending on data volume and warehouse size. Options are provided to build only final output tables or specific tables.

```bash
dbt run

dbt run --select tag:final    # build only final output tables
dbt run --select empi_crosswalk empi_golden_record    # build specific tables
```

--------------------------------

### Configure EMPI Lite Vars in dbt_project.yml

Source: https://thetuvaproject.com/empi-lite/getting-started

Configures essential variables for the EMPI Lite project within the 'dbt_project.yml' file. This includes database and schema settings, matching thresholds, and flags for enabling snapshots and custom attributes. This is crucial for both standalone and integrated project setups.

```yaml
vars:
  empi_lite:
    empi_input_database: your_database
    empi_input_schema: your_schema
    match_threshold: 0.70
    review_threshold_low: 0.50
    review_threshold_high: 0.69
    empi_snapshot_enabled: false
    empi_custom_attributes_enabled: false
```

--------------------------------

### Register Custom Attributes in Tuvaproject

Source: https://thetuvaproject.com/empi-lite/configuration

This example demonstrates how to register custom attributes for the Tuvaproject's matching engine. It specifies the attribute name, weight, fuzzy matching settings, and mismatch penalty.

```csv
attribute,weight,use_fuzzy_match,fuzzy_threshold,exact_match_score,mismatch_penalty
NPI,...
EMPLOYEE_ID,...
```

--------------------------------

### Install Python Packages with pip

Source: https://thetuvaproject.com/connectors/building-a-connector

Installs Python packages listed in the 'requirements.txt' file. This is crucial for setting up project dependencies, including dbt-core and specific adapters.

```shell
pip install -r requirements.txt
```

--------------------------------

### Install Project Dependencies

Source: https://thetuvaproject.com/data-quality-dashboard

Installs all necessary Python packages listed in the 'requirements.txt' file. This step ensures that the dashboard has all the required libraries to run.

```bash
python -m pip install -r requirements.txt
```

--------------------------------

### Run dbt Tests

Source: https://thetuvaproject.com/empi-lite/getting-started

Executes all defined dbt tests to ensure data quality and model integrity. Common failure causes on the first run are listed, including missing source table columns, non-existent manual review tables, or profile misconfigurations.

```bash
dbt test
```

--------------------------------

### Enable dbt Snapshots

Source: https://thetuvaproject.com/empi-lite/getting-started

Enables snapshot functionality for change-detection events in 'empi_patient_events'. This involves running the 'dbt snapshot' command after the initial 'dbt run' and setting 'empi_snapshot_enabled' to true in 'dbt_project.yml'. Subsequent runs require 'dbt snapshot && dbt run'.

```bash
dbt snapshot

dbt snapshot && dbt run
```

--------------------------------

### Configure dbt Profiles for DuckDB

Source: https://thetuvaproject.com/getting-started

Creates a local dbt profile directory and defines the profiles.yml file required to connect dbt to a local DuckDB instance. This configuration is essential for the dbt build process to locate and write to the database file.

```bash
mkdir -p .dbt
cat > .dbt/profiles.yml <<'EOF'
default:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: "{{ env_var('DEMO_DUCKDB_PATH') }}"
      threads: 4
EOF
```

--------------------------------

### Clone Tuva DQI Repository

Source: https://thetuvaproject.com/data-quality-dashboard

Clones the Tuva DQI GitHub repository to your local machine. This is the first step to setting up the dashboard locally. It requires Git to be installed.

```bash
git clone https://github.com/tuva-health/tuva_dqi.git
cd tuva_dqi
```

--------------------------------

### Define Source Models

Source: https://thetuvaproject.com/connectors/building-a-connector

Example configuration for the sources.yml file to specify which data models (e.g., eligibility, claims) are active in the connector.

```yaml
- name: elibility
- name: medical_claim
- name: pharmacy_claim
```

--------------------------------

### Install dbt Project Dependencies

Source: https://thetuvaproject.com/connectors/building-a-connector

Installs any external packages or dependencies defined in the 'packages.yml' file for the dbt project. This ensures all required components are available.

```shell
dbt deps
```

--------------------------------

### Re-run Tuvaproject Models

Source: https://thetuvaproject.com/empi-lite/configuration

This command re-runs the Tuvaproject's data models using dbt, applying any configuration changes made to custom attributes or source settings.

```bash
dbt seed && dbt run
```

--------------------------------

### Validate Claims with Enrollment

Source: https://thetuvaproject.com/example-sql

Checks the integrity of claims by verifying if each claim has a corresponding member enrollment record. Ideally, this should result in 100% coverage.

```SQL
select 
mc.data_source 
, sum(case when mm.person_id is not null then 1 else 0 end) as claims_with_enrollment 
, count(*) as claims 
, cast(sum(case when mm.person_id is not null then 1 else 0 end) / count(*) as decimal(18,2)) as percentage_claims_with_enrollment 
from core.medical_claim mc 
left join core.member_months mm on mc.person_id = mm.person_id 
and mc.data_source = mm.data_source 
and strftime(mc.claim_start_date, '%Y%m') = mm.year_month 
GROUP BY mc.data_source
```

```SQL
select 
mc.data_source 
, sum(case when mm.person_id is not null then 1 else 0 end) as claims_with_enrollment 
, count(*) as claims 
, cast(sum(case when mm.person_id is not null then 1 else 0 end) / count(*) as decimal(18,2)) as percentage_claims_with_enrollment 
from core.pharmacy_claim mc 
left join core.member_months mm on mc.person_id = mm.person_id 
and mc.data_source = mm.data_source 
and strftime(mc.paid_date, '%Y%m') = mm.year_month 
GROUP BY mc.data_source
```

--------------------------------

### Analyze ED Visits by Facility

Source: https://thetuvaproject.com/example-sql

Provides performance metrics for ED facilities, including total visit counts and average paid amount per visit.

```SQL
select
 facility_id
, COUNT(*) AS ed_visits
, sum(cast(e.paid_amount as decimal(18,2))) as paid_amount
, cast(sum(e.paid_amount)/count(*) as decimal(18,2))as paid_per_visit
from core.encounter e
where encounter_type = 'emergency department'
group by
 facility_id
ORDER BY ed_visits desc;
```

--------------------------------

### Run the Data Quality Dashboard Application

Source: https://thetuvaproject.com/data-quality-dashboard

Starts the Data Quality Dashboard application. Once running, the dashboard can be accessed via a web browser at http://localhost:8080.

```bash
python app.py
```

--------------------------------

### Analyze ED Visits by Admit Source and Type

Source: https://thetuvaproject.com/example-sql

Breaks down ED utilization and costs based on admission source and type codes to understand patient entry pathways.

```SQL
select
admit_source_code
, admit_source_description
, admit_type_code
, admit_type_description
, count(*) AS ed_visits
, sum(cast(e.paid_amount as decimal(18,2))) as paid_amount
, cast(sum(e.paid_amount)/count(*) as decimal(18,2))as paid_per_visit
from core.encounter e
where encounter_type = 'emergency department'
group by
admit_source_code
, admit_source_description
, admit_type_code
, admit_type_description
ORDER BY ed_visits desc;
```

--------------------------------

### Create EMPI Manual Review Tables

Source: https://thetuvaproject.com/empi-lite/data-requirements

Initializes the schema and two required tables, match_review_decisions and split_review_decisions, used by reviewers to store matching and splitting decisions.

```sql
-- Create the schema
CREATE SCHEMA IF NOT EXISTS your_database.empi_manual_review;

-- Match review decisions
CREATE TABLE IF NOT EXISTS your_database.empi_manual_review.match_review_decisions (
    data_source_a       VARCHAR     NOT NULL,
    source_person_id_a  VARCHAR     NOT NULL,
    data_source_b       VARCHAR     NOT NULL,
    source_person_id_b  VARCHAR     NOT NULL,
    is_match            BOOLEAN,
    reviewed            BOOLEAN,
    reviewer_name       VARCHAR,
    review_date         DATE,
    notes               VARCHAR
);

-- Split review decisions
CREATE TABLE IF NOT EXISTS your_database.empi_manual_review.split_review_decisions (
    data_source         VARCHAR     NOT NULL,
    source_person_id    VARCHAR     NOT NULL,
    is_split            BOOLEAN,
    reviewed            BOOLEAN,
    reviewer_name       VARCHAR,
    review_date         DATE,
    notes               VARCHAR
);
```

--------------------------------

### Manage Split and Match Interaction in SQL

Source: https://thetuvaproject.com/empi-lite/manual-review

Demonstrates the workflow of confirming a record split followed by manually linking the split record to its correct match using the match_review_decisions table.

```SQL
-- First, confirm the split
INSERT INTO empi_manual_review.split_review_decisions
    (data_source, source_person_id, is_split, reviewed, reviewer_name, review_date, notes)
VALUES ('EHR_SYSTEM', 'PAT-10188', TRUE, TRUE, 'jsmith', CURRENT_DATE, 'Two patients shared this ID');

-- Then, manually link the split record to its correct match
INSERT INTO empi_manual_review.match_review_decisions
    (data_source_a, source_person_id_a, data_source_b, source_person_id_b, is_match, reviewed, reviewer_name, review_date, notes)
VALUES ('EHR_SYSTEM', 'PAT-10188', 'CLAIMS_PAYER', 'MBR-55001', TRUE, TRUE, 'jsmith', CURRENT_DATE, 'Linked after split');
```

--------------------------------

### Initialize Connector Repository

Source: https://thetuvaproject.com/connectors/building-a-connector

Commands to clone a new connector repository from the template and navigate into the project directory.

```bash
git clone https://github.com/your-username/my-connector.git
cd my-connector
```

--------------------------------

### Monitor Review Queue Progress in SQL

Source: https://thetuvaproject.com/empi-lite/manual-review

Provides queries to analyze the state of the review queue, including counting pending pairs by priority and identifying unreviewed high-priority pairs.

```SQL
-- How many pairs are in the review queue?
SELECT review_priority, COUNT(*) as pairs
FROM empi.empi_review_queue_matches
GROUP BY review_priority;

-- How many decisions have been made?
SELECT is_match, COUNT(*) as decisions
FROM empi_manual_review.match_review_decisions
WHERE reviewed = TRUE
GROUP BY is_match;

-- Unreviewed HIGH priority pairs (in queue but no decision yet)
SELECT q.*
FROM empi.empi_review_queue_matches q
LEFT JOIN empi_manual_review.match_review_decisions d
    ON (q.data_source_a = d.data_source_a AND q.source_person_id_a = d.source_person_id_a
        AND q.data_source_b = d.data_source_b AND q.source_person_id_b = d.source_person_id_b)
    OR (q.data_source_a = d.data_source_b AND q.source_person_id_a = d.source_person_id_b
        AND q.data_source_b = d.data_source_a AND q.source_person_id_b = d.source_person_id_a)
WHERE d.data_source_a IS NULL
  AND q.review_priority = 'HIGH';
```

--------------------------------

### Validate dbt Project Results

Source: https://thetuvaproject.com/empi-lite/getting-started

Provides SQL queries to validate the results of the dbt project, focusing on match quality, cluster sizes, and data quality anomalies. These queries help in spot-checking the output tables and identifying potential issues that may require configuration adjustments.

```sql
-- How many records matched vs. singletons? 
SELECT match_status, COUNT(*) as records
FROM empi.empi_crosswalk
GROUP BY match_status;

-- How large are the clusters?  
SELECT cluster_size, COUNT(DISTINCT empi_id) as clusters
FROM empi.empi_crosswalk
GROUP BY cluster_size
ORDER BY cluster_size;

-- Review a sample of matches with their narratives  
SELECT *
FROM empi.empi_patient_events
WHERE event_type = 'EMPI_MATCH'
LIMIT 25;

-- Check data quality anomalies  
SELECT *
FROM empi.empi_demographic_anomalies
ORDER BY affected_records DESC;
```

--------------------------------

### Load Seed Data

Source: https://thetuvaproject.com/empi-lite/getting-started

Loads reference data into your data warehouse using dbt seed. It provides options to exclude large static tables or select specific seeds for loading. The ZIP proximity table is noted as a one-time load unless a refresh is explicitly needed.

```bash
# Load all seeds except the large static ones (ZIP proximity table, eval ground truth)
dbt seed --exclude tag:large_seed

# Load the ZIP code proximity table (only needed once; takes a few minutes on first load)
dbt seed --select gaz2024zcta5distance50miles --full-refresh
```

--------------------------------

### Create and Activate Python Virtual Environment

Source: https://thetuvaproject.com/connectors/building-a-connector

Creates a Python virtual environment named '.venv' and activates it. This isolates project dependencies. The activation command differs between Unix-based systems and Windows.

```shell
python3 -m venv .venv
source .venv/bin/activate  # or `.venv\Scripts\activate` on Windows
```

--------------------------------

### Install and Update dbt Packages

Source: https://thetuvaproject.com/data-quality-dashboard

These commands are used to clean the dbt project and install or update the packages listed in your packages.yml file, including the Tuva package. Run these after modifying your packages.yml.

```bash
dbt clean  
dbt deps  

```

--------------------------------

### Calculate ED Visit Trends and PKPY

Source: https://thetuvaproject.com/example-sql

Calculates monthly ED visit volume, average paid amounts, and Per Kilo-Person Year (PKPY) metrics by joining encounter data with member month denominators.

```SQL
with ed as (
select
  data_source
  ,strftime(encounter_end_date, '%Y%m') AS year_month
  ,COUNT(*) AS ed_visits
  ,AVG(paid_amount) as avg_paid_amount
  ,sum(paid_amount) as total_paid_amount
from core.encounter
where encounter_type = 'emergency department'
group by data_source
 ,strftime(encounter_end_date, '%Y%m')
)

, member_months as (
select
  data_source
, year_month
, count(1) as member_months
from core.member_months
group by
  data_source
, year_month

)
select
  a.data_source
, a.year_month
, b.member_months
, ed_visits
, cast(ed_visits / member_months * 12000 as decimal(18,2)) as ed_visits_pkpy
, cast(avg_paid_amount as decimal(18,2)) as avg_paid_amount
, cast(total_paid_amount as decimal(18,2))as ed_total_paid_amount
from  member_months b
left join ed a
  on a.year_month = b.year_month
  and a.data_source = b.data_source
order by 1,2;
```

--------------------------------

### Count ED Facilities

Source: https://thetuvaproject.com/example-sql

Counts the number of unique facilities providing emergency department services per data source.

```SQL
select data_source
 ,count(distinct facility_id) as ed_facilities_count
from core.encounter e
where encounter_type = 'emergency department'
group by
 data_source
order by ed_facilities_count desc
```

--------------------------------

### Understand EMPI Lite Threshold Interaction Logic

Source: https://thetuvaproject.com/empi-lite/configuration

Illustrates the logic flow for EMPI Lite's scoring thresholds, defining how scores determine dismissal, review routing, priority, and auto-matching.

```plaintext
score < 0.50              → dismissed, no action
0.50 ≤ score < 0.70       → routed to review queue
    0.50 ≤ score ≤ 0.69   →   MEDIUM or LOW priority
    0.69 < score < 0.70   →   HIGH priority
score ≥ 0.70              → auto-matched

```