-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 00:51:17.265171+00:00
WITH 
"claims_renamed" AS (
    -- Rename: Renaming columns
    -- Id -> claim_id
    -- PATIENTID -> patient_id
    -- PROVIDERID -> provider_id
    -- PRIMARYPATIENTINSURANCEID -> primary_insurance_id
    -- SECONDARYPATIENTINSURANCEID -> secondary_insurance_id
    -- DEPARTMENTID -> department_id
    -- PATIENTDEPARTMENTID -> patient_department_id
    -- DIAGNOSIS1 -> primary_diagnosis
    -- DIAGNOSIS2 -> secondary_diagnosis
    -- DIAGNOSIS3 -> tertiary_diagnosis
    -- DIAGNOSIS4 -> quaternary_diagnosis
    -- DIAGNOSIS5 -> quinary_diagnosis
    -- DIAGNOSIS6 -> senary_diagnosis
    -- DIAGNOSIS7 -> septenary_diagnosis
    -- DIAGNOSIS8 -> octonary_diagnosis
    -- REFERRINGPROVIDERID -> referring_provider_id
    -- APPOINTMENTID -> appointment_id
    -- CURRENTILLNESSDATE -> illness_onset_date
    -- SERVICEDATE -> service_date
    -- SUPERVISINGPROVIDERID -> supervising_provider_id
    -- STATUS1 -> primary_claim_status
    -- STATUS2 -> secondary_claim_status
    -- STATUSP -> patient_claim_status
    -- OUTSTANDING1 -> primary_outstanding_amount
    -- OUTSTANDING2 -> secondary_outstanding_amount
    -- OUTSTANDINGP -> patient_outstanding_amount
    -- LASTBILLEDDATE1 -> primary_last_billed_date
    -- LASTBILLEDDATE2 -> secondary_last_billed_date
    -- LASTBILLEDDATEP -> patient_last_billed_date
    -- HEALTHCARECLAIMTYPEID1 -> primary_claim_type_id
    -- HEALTHCARECLAIMTYPEID2 -> claim_type_id
    SELECT 
        "Id" AS "claim_id",
        "PATIENTID" AS "patient_id",
        "PROVIDERID" AS "provider_id",
        "PRIMARYPATIENTINSURANCEID" AS "primary_insurance_id",
        "SECONDARYPATIENTINSURANCEID" AS "secondary_insurance_id",
        "DEPARTMENTID" AS "department_id",
        "PATIENTDEPARTMENTID" AS "patient_department_id",
        "DIAGNOSIS1" AS "primary_diagnosis",
        "DIAGNOSIS2" AS "secondary_diagnosis",
        "DIAGNOSIS3" AS "tertiary_diagnosis",
        "DIAGNOSIS4" AS "quaternary_diagnosis",
        "DIAGNOSIS5" AS "quinary_diagnosis",
        "DIAGNOSIS6" AS "senary_diagnosis",
        "DIAGNOSIS7" AS "septenary_diagnosis",
        "DIAGNOSIS8" AS "octonary_diagnosis",
        "REFERRINGPROVIDERID" AS "referring_provider_id",
        "APPOINTMENTID" AS "appointment_id",
        "CURRENTILLNESSDATE" AS "illness_onset_date",
        "SERVICEDATE" AS "service_date",
        "SUPERVISINGPROVIDERID" AS "supervising_provider_id",
        "STATUS1" AS "primary_claim_status",
        "STATUS2" AS "secondary_claim_status",
        "STATUSP" AS "patient_claim_status",
        "OUTSTANDING1" AS "primary_outstanding_amount",
        "OUTSTANDING2" AS "secondary_outstanding_amount",
        "OUTSTANDINGP" AS "patient_outstanding_amount",
        "LASTBILLEDDATE1" AS "primary_last_billed_date",
        "LASTBILLEDDATE2" AS "secondary_last_billed_date",
        "LASTBILLEDDATEP" AS "patient_last_billed_date",
        "HEALTHCARECLAIMTYPEID1" AS "primary_claim_type_id",
        "HEALTHCARECLAIMTYPEID2" AS "claim_type_id"
    FROM "memory"."main"."claims"
),

"claims_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- primary_insurance_id: The problem is that the '0' value does not match the UUID format of the other values in the primary_insurance_id column. All other values are valid UUIDs in the format of 8-4-4-4-12 hexadecimal characters. The '0' value is likely a placeholder or default value used when no valid UUID was available. The correct values should all be valid UUIDs or an empty string if no valid UUID is available.
    SELECT
        "claim_id",
        "patient_id",
        "provider_id",
        CASE
            WHEN "primary_insurance_id" = '0' THEN NULL
            ELSE "primary_insurance_id"
        END AS "primary_insurance_id",
        "secondary_insurance_id",
        "department_id",
        "patient_department_id",
        "primary_diagnosis",
        "secondary_diagnosis",
        "tertiary_diagnosis",
        "quaternary_diagnosis",
        "quinary_diagnosis",
        "senary_diagnosis",
        "septenary_diagnosis",
        "octonary_diagnosis",
        "referring_provider_id",
        "appointment_id",
        "illness_onset_date",
        "service_date",
        "supervising_provider_id",
        "primary_claim_status",
        "secondary_claim_status",
        "patient_claim_status",
        "primary_outstanding_amount",
        "secondary_outstanding_amount",
        "patient_outstanding_amount",
        "primary_last_billed_date",
        "secondary_last_billed_date",
        "patient_last_billed_date",
        "primary_claim_type_id",
        "claim_type_id"
    FROM "claims_renamed"
),

"claims_renamed_cleaned_null" AS (
    -- NULL Imputation: Impute Null to Disguised Missing Values
    -- secondary_insurance_id: ['0']
    SELECT 
        CASE
            WHEN "secondary_insurance_id" = '0' THEN NULL
            ELSE "secondary_insurance_id"
        END AS "secondary_insurance_id",
        "patient_last_billed_date",
        "claim_id",
        "primary_claim_type_id",
        "patient_outstanding_amount",
        "primary_diagnosis",
        "provider_id",
        "claim_type_id",
        "secondary_outstanding_amount",
        "illness_onset_date",
        "appointment_id",
        "secondary_last_billed_date",
        "senary_diagnosis",
        "service_date",
        "quaternary_diagnosis",
        "patient_id",
        "primary_claim_status",
        "primary_insurance_id",
        "department_id",
        "octonary_diagnosis",
        "septenary_diagnosis",
        "patient_claim_status",
        "secondary_claim_status",
        "primary_last_billed_date",
        "primary_outstanding_amount",
        "patient_department_id",
        "referring_provider_id",
        "supervising_provider_id",
        "secondary_diagnosis",
        "quinary_diagnosis",
        "tertiary_diagnosis"
    FROM "claims_renamed_cleaned"
),

"claims_renamed_cleaned_null_casted" AS (
    -- Column Type Casting: 
    -- appointment_id: from VARCHAR to UUID
    -- claim_id: from VARCHAR to UUID
    -- illness_onset_date: from VARCHAR to TIMESTAMP
    -- octonary_diagnosis: from DECIMAL to VARCHAR
    -- patient_id: from VARCHAR to UUID
    -- patient_last_billed_date: from VARCHAR to TIMESTAMP
    -- primary_diagnosis: from INT to VARCHAR
    -- primary_insurance_id: from VARCHAR to UUID
    -- primary_last_billed_date: from VARCHAR to TIMESTAMP
    -- provider_id: from VARCHAR to UUID
    -- quaternary_diagnosis: from DECIMAL to VARCHAR
    -- quinary_diagnosis: from DECIMAL to VARCHAR
    -- referring_provider_id: from DECIMAL to VARCHAR
    -- secondary_diagnosis: from DECIMAL to VARCHAR
    -- secondary_last_billed_date: from VARCHAR to TIMESTAMP
    -- senary_diagnosis: from DECIMAL to VARCHAR
    -- septenary_diagnosis: from DECIMAL to VARCHAR
    -- service_date: from VARCHAR to TIMESTAMP
    -- supervising_provider_id: from VARCHAR to UUID
    -- tertiary_diagnosis: from DECIMAL to VARCHAR
    SELECT
        "secondary_insurance_id",
        "primary_claim_type_id",
        "patient_outstanding_amount",
        "claim_type_id",
        "secondary_outstanding_amount",
        "primary_claim_status",
        "department_id",
        "patient_claim_status",
        "secondary_claim_status",
        "primary_outstanding_amount",
        "patient_department_id",
        CAST("appointment_id" AS UUID) 
        AS "appointment_id",
        CAST("claim_id" AS UUID) 
        AS "claim_id",
        CAST("illness_onset_date" AS TIMESTAMP) 
        AS "illness_onset_date",
        CAST("octonary_diagnosis" AS VARCHAR) 
        AS "octonary_diagnosis",
        CAST("patient_id" AS UUID) 
        AS "patient_id",
        CAST("patient_last_billed_date" AS TIMESTAMP) 
        AS "patient_last_billed_date",
        CAST("primary_diagnosis" AS VARCHAR) 
        AS "primary_diagnosis",
        CAST("primary_insurance_id" AS UUID) 
        AS "primary_insurance_id",
        CAST("primary_last_billed_date" AS TIMESTAMP) 
        AS "primary_last_billed_date",
        CAST("provider_id" AS UUID) 
        AS "provider_id",
        CAST("quaternary_diagnosis" AS VARCHAR) 
        AS "quaternary_diagnosis",
        CAST("quinary_diagnosis" AS VARCHAR) 
        AS "quinary_diagnosis",
        CAST("referring_provider_id" AS VARCHAR) 
        AS "referring_provider_id",
        CAST("secondary_diagnosis" AS VARCHAR) 
        AS "secondary_diagnosis",
        CAST("secondary_last_billed_date" AS TIMESTAMP) 
        AS "secondary_last_billed_date",
        CAST("senary_diagnosis" AS VARCHAR) 
        AS "senary_diagnosis",
        CAST("septenary_diagnosis" AS VARCHAR) 
        AS "septenary_diagnosis",
        CAST("service_date" AS TIMESTAMP) 
        AS "service_date",
        CAST("supervising_provider_id" AS UUID) 
        AS "supervising_provider_id",
        CAST("tertiary_diagnosis" AS VARCHAR) 
        AS "tertiary_diagnosis"
    FROM "claims_renamed_cleaned_null"
)

-- COCOON BLOCK END
SELECT *
FROM "claims_renamed_cleaned_null_casted"

version: 2
models:
- name: stg_claims
  description: The table is about insurance claims. It contains details of each claim,
    including patient ID, provider ID, diagnosis codes, appointment details, and claim
    status. The table tracks claim processing information such as outstanding amounts,
    last billed dates, and healthcare claim type IDs. Each row represents a unique
    claim with its associated patient, provider, and billing information.
  columns:
  - name: secondary_insurance_id
    description: Secondary insurance identifier for the patient
    cocoon_meta:
      missing_reason: Not applicable if patient doesn't have secondary insurance.
      data_type:
        current_data_type: VARCHAR
  - name: primary_claim_type_id
    description: Type identifier for primary healthcare claim
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: patient_outstanding_amount
    description: Outstanding amount for patient claim
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: claim_type_id
    description: Identifies the type of healthcare claim
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: secondary_outstanding_amount
    description: Outstanding amount for secondary claim
    cocoon_meta:
      missing_reason: Not applicable if there's no secondary insurance claim.
      data_type:
        current_data_type: DECIMAL
  - name: primary_claim_status
    description: Status of primary claim
    tests:
    - not_null
    - accepted_values:
        values:
        - CLOSED
        - BILLED
    cocoon_meta:
      future_accepted_values:
      - OPEN
      - PENDING
      - APPROVED
      - DENIED
      - PAID
      - PARTIALLY PAID
      - APPEALED
      - UNDER REVIEW
      - CANCELLED
      data_type:
        current_data_type: VARCHAR
  - name: department_id
    description: Identifier for the department handling the claim
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: patient_claim_status
    description: Status of patient claim
    tests:
    - not_null
    - accepted_values:
        values:
        - CLOSED
        - BILLED
    cocoon_meta:
      future_accepted_values:
      - OPEN
      - PENDING
      - APPROVED
      - DENIED
      - UNDER REVIEW
      - APPEALED
      - PAID
      - PARTIALLY PAID
      - CANCELLED
      data_type:
        current_data_type: VARCHAR
  - name: secondary_claim_status
    description: Status of secondary claim
    tests:
    - accepted_values:
        values:
        - CLOSED
        - BILLED
    cocoon_meta:
      missing_reason: Not applicable if there's no secondary insurance claim.
      future_accepted_values:
      - OPEN
      - PENDING
      - DENIED
      - APPROVED
      - PAID
      - PARTIAL_PAID
      - APPEALED
      - CANCELLED
      - SUSPENDED
      data_type:
        current_data_type: VARCHAR
  - name: primary_outstanding_amount
    description: Outstanding amount for primary claim
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: patient_department_id
    description: Department identifier associated with the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: appointment_id
    description: Unique identifier for the appointment
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: claim_id
    description: Unique identifier for the claim
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column represents a unique identifier for each claim. For
        this table, each row represents a unique insurance claim. The claim_id appears
        to be unique across rows, as it's a UUID-like string that's different for
        each sample.
      data_type:
        current_data_type: UUID
  - name: illness_onset_date
    description: Date of current illness onset
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: octonary_diagnosis
    description: Eighth diagnosis code
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: patient_id
    description: Unique identifier for the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: patient_last_billed_date
    description: Last billed date for patient claim
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: primary_diagnosis
    description: Primary diagnosis code
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: primary_insurance_id
    description: Primary insurance identifier for the patient
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: UUID
  - name: primary_last_billed_date
    description: Last billed date for primary claim
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: provider_id
    description: Unique identifier for the healthcare provider
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: quaternary_diagnosis
    description: Fourth diagnosis code
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: quinary_diagnosis
    description: Fifth diagnosis code
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: referring_provider_id
    description: Identifier for the referring provider
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: secondary_diagnosis
    description: Secondary diagnosis code
    cocoon_meta:
      missing_reason: Not applicable if there's no secondary insurance claim.
      data_type:
        current_data_type: VARCHAR
  - name: secondary_last_billed_date
    description: Last billed date for secondary claim
    cocoon_meta:
      missing_reason: Not applicable if there's no secondary insurance claim.
      data_type:
        current_data_type: TIMESTAMP
  - name: senary_diagnosis
    description: Sixth diagnosis code
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: septenary_diagnosis
    description: Seventh diagnosis code
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: service_date
    description: Date of service provided
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: supervising_provider_id
    description: Identifier for the supervising provider
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: tertiary_diagnosis
    description: Third diagnosis code
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 01:16:20.447136+00:00
WITH 
"providers_renamed" AS (
    -- Rename: Renaming columns
    -- Id -> provider_id
    -- ORGANIZATION -> organization_id
    -- NAME -> provider_name
    -- ADDRESS -> street_address
    -- LAT -> latitude
    -- LON -> longitude
    -- ENCOUNTERS -> patient_encounters
    -- PROCEDURES -> procedures_performed
    SELECT 
        "Id" AS "provider_id",
        "ORGANIZATION" AS "organization_id",
        "NAME" AS "provider_name",
        "GENDER",
        "SPECIALITY",
        "ADDRESS" AS "street_address",
        "CITY",
        "STATE",
        "ZIP",
        "LAT" AS "latitude",
        "LON" AS "longitude",
        "ENCOUNTERS" AS "patient_encounters",
        "PROCEDURES" AS "procedures_performed"
    FROM "memory"."main"."providers"
),

"providers_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- provider_name: The problem is that all the provider names in this column contain numbers, which is highly unusual for real names. The names appear to be a combination of a first name followed by numbers, then a last name followed by more numbers. This pattern is consistent across all entries and doesn't represent typical naming conventions. The correct values should be the names without the numbers.
    SELECT
        "provider_id",
        "organization_id",
        CASE
            WHEN "provider_name" = 'Afton574 Kulas532' THEN 'Afton Kulas'
            WHEN "provider_name" = 'Agustín529 Gaona896' THEN 'Agustín Gaona'
            WHEN "provider_name" = 'Alan320 Keeling57' THEN 'Alan Keeling'
            WHEN "provider_name" = 'Alvin56 Hayes766' THEN 'Alvin Hayes'
            WHEN "provider_name" = 'Ammie189 Kuvalis369' THEN 'Ammie Kuvalis'
            WHEN "provider_name" = 'Anderson154 Lemke654' THEN 'Anderson Lemke'
            WHEN "provider_name" = 'Angelic427 McCullough561' THEN 'Angelic McCullough'
            WHEN "provider_name" = 'Annita733 Frami345' THEN 'Annita Frami'
            WHEN "provider_name" = 'Antwan357 Lindgren255' THEN 'Antwan Lindgren'
            WHEN "provider_name" = 'Ariane992 Pagac496' THEN 'Ariane Pagac'
            WHEN "provider_name" = 'Arla414 Fritsch593' THEN 'Arla Fritsch'
            WHEN "provider_name" = 'Barrett790 Wolf938' THEN 'Barrett Wolf'
            WHEN "provider_name" = 'Bernardo699 Nieves278' THEN 'Bernardo Nieves'
            WHEN "provider_name" = 'Bradly656 Lang846' THEN 'Bradly Lang'
            WHEN "provider_name" = 'Brigitte394 Bartoletti50' THEN 'Brigitte Bartoletti'
            WHEN "provider_name" = 'Brock407 Bergstrom287' THEN 'Brock Bergstrom'
            WHEN "provider_name" = 'Carisa395 Kutch271' THEN 'Carisa Kutch'
            WHEN "provider_name" = 'Carlos172 Wiza601' THEN 'Carlos Wiza'
            WHEN "provider_name" = 'Carri827 Flatley871' THEN 'Carri Flatley'
            WHEN "provider_name" = 'Chantell995 Krajcik437' THEN 'Chantell Krajcik'
            WHEN "provider_name" = 'Charlena802 Lockman863' THEN 'Charlena Lockman'
            WHEN "provider_name" = 'Cherrie404 Conn188' THEN 'Cherrie Conn'
            WHEN "provider_name" = 'Chong355 Balistreri607' THEN 'Chong Balistreri'
            WHEN "provider_name" = 'Clemente531 Schamberger479' THEN 'Clemente Schamberger'
            WHEN "provider_name" = 'Clemente531 Treutel973' THEN 'Clemente Treutel'
            WHEN "provider_name" = 'Cristi782 Miller503' THEN 'Cristi Miller'
            WHEN "provider_name" = 'Cristy798 Auer97' THEN 'Cristy Auer'
            WHEN "provider_name" = 'Dana512 Wilkinson796' THEN 'Dana Wilkinson'
            WHEN "provider_name" = 'Danial835 Lehner980' THEN 'Danial Lehner'
            WHEN "provider_name" = 'Danita413 Becker968' THEN 'Danita Becker'
            WHEN "provider_name" = 'Dannie881 Howell947' THEN 'Dannie Howell'
            WHEN "provider_name" = 'Danuta945 Little434' THEN 'Danuta Little'
            WHEN "provider_name" = 'Dean966 Kris249' THEN 'Dean Kris'
            WHEN "provider_name" = 'Delicia67 Bernhard322' THEN 'Delicia Bernhard'
            WHEN "provider_name" = 'Dorcas534 Quigley282' THEN 'Dorcas Quigley'
            WHEN "provider_name" = 'Drew592 Ankunding277' THEN 'Drew Ankunding'
            WHEN "provider_name" = 'Eduardo902 Méndez913' THEN 'Eduardo Méndez'
            WHEN "provider_name" = 'Eduardo902 Villegas15' THEN 'Eduardo Villegas'
            WHEN "provider_name" = 'Elise948 Smitham825' THEN 'Elise Smitham'
            WHEN "provider_name" = 'Eliseo499 Reinger292' THEN 'Eliseo Reinger'
            WHEN "provider_name" = 'Ellen406 Farrell962' THEN 'Ellen Farrell'
            WHEN "provider_name" = 'Elmer371 Kutch271' THEN 'Elmer Kutch'
            WHEN "provider_name" = 'Elvera717 Gusikowski974' THEN 'Elvera Gusikowski'
            WHEN "provider_name" = 'Emerita401 Satterfield305' THEN 'Emerita Satterfield'
            WHEN "provider_name" = 'Errol226 Gaylord332' THEN 'Errol Gaylord'
            WHEN "provider_name" = 'Ervin886 Leannon79' THEN 'Ervin Leannon'
            WHEN "provider_name" = 'Erwin847 Stiedemann542' THEN 'Erwin Stiedemann'
            WHEN "provider_name" = 'Esteban536 Torres807' THEN 'Esteban Torres'
            WHEN "provider_name" = 'Eufemia350 Casper496' THEN 'Eufemia Casper'
            WHEN "provider_name" = 'Evelia928 Bogisich202' THEN 'Evelia Bogisich'
            WHEN "provider_name" = 'Fidel864 Swift555' THEN 'Fidel Swift'
            WHEN "provider_name" = 'Francisco472 Gusikowski974' THEN 'Francisco Gusikowski'
            WHEN "provider_name" = 'Gabriel934 Reilly981' THEN 'Gabriel Reilly'
            WHEN "provider_name" = 'Gerardo48 Montez99' THEN 'Gerardo Montez'
            WHEN "provider_name" = 'Germán350 Dueñas839' THEN 'Germán Dueñas'
            WHEN "provider_name" = 'Gonzalo160 Rodríguez701' THEN 'Gonzalo Rodríguez'
            WHEN "provider_name" = 'Gregory545 Toy286' THEN 'Gregory Toy'
            WHEN "provider_name" = 'Hal307 Wiegand701' THEN 'Hal Wiegand'
            WHEN "provider_name" = 'Hank686 Kemmer137' THEN 'Hank Kemmer'
            WHEN "provider_name" = 'Harvey63 Tromp100' THEN 'Harvey Tromp'
            WHEN "provider_name" = 'Haywood675 Bednar518' THEN 'Haywood Bednar'
            WHEN "provider_name" = 'Horace32 Grady603' THEN 'Horace Grady'
            WHEN "provider_name" = 'Humberto482 Mann644' THEN 'Humberto Mann'
            WHEN "provider_name" = 'Hyman89 Schmeler639' THEN 'Hyman Schmeler'
            WHEN "provider_name" = 'Ignacio928 O''Keefe54' THEN 'Ignacio O''Keefe'
            WHEN "provider_name" = 'Ilana185 Torp761' THEN 'Ilana Torp'
            WHEN "provider_name" = 'Irvin970 Emard19' THEN 'Irvin Emard'
            WHEN "provider_name" = 'Isaias604 Dickinson688' THEN 'Isaias Dickinson'
            WHEN "provider_name" = 'Isiah14 Nikolaus26' THEN 'Isiah Nikolaus'
            WHEN "provider_name" = 'Isiah14 Schaefer657' THEN 'Isiah Schaefer'
            WHEN "provider_name" = 'Jacobo456 Vergara204' THEN 'Jacobo Vergara'
            WHEN "provider_name" = 'Janeth814 Hyatt152' THEN 'Janeth Hyatt'
            WHEN "provider_name" = 'Jeana169 Cronin387' THEN 'Jeana Cronin'
            WHEN "provider_name" = 'Jefferey580 Luettgen772' THEN 'Jefferey Luettgen'
            WHEN "provider_name" = 'Joanna347 Abbott774' THEN 'Joanna Abbott'
            WHEN "provider_name" = 'Johna806 Klein929' THEN 'Johna Klein'
            WHEN "provider_name" = 'Joline447 Stark857' THEN 'Joline Stark'
            WHEN "provider_name" = 'Jolynn62 Adams676' THEN 'Jolynn Adams'
            WHEN "provider_name" = 'Joshua658 Borer986' THEN 'Joshua Borer'
            WHEN "provider_name" = 'Juan88 Friesen796' THEN 'Juan Friesen'
            WHEN "provider_name" = 'Jules135 Emard19' THEN 'Jules Emard'
            WHEN "provider_name" = 'Julio255 Rivas297' THEN 'Julio Rivas'
            WHEN "provider_name" = 'Kai187 Mann644' THEN 'Kai Mann'
            WHEN "provider_name" = 'Kathi607 Hilpert278' THEN 'Kathi Hilpert'
            WHEN "provider_name" = 'Keith571 Friesen796' THEN 'Keith Friesen'
            WHEN "provider_name" = 'Kendra609 Kassulke119' THEN 'Kendra Kassulke'
            WHEN "provider_name" = 'Kirby843 Rippin620' THEN 'Kirby Rippin'
            WHEN "provider_name" = 'Kisha966 Beahan375' THEN 'Kisha Beahan'
            WHEN "provider_name" = 'Kizzie166 Lehner980' THEN 'Kizzie Lehner'
            WHEN "provider_name" = 'Kristina583 Bashirian201' THEN 'Kristina Bashirian'
            WHEN "provider_name" = 'Kyoko885 Price929' THEN 'Kyoko Price'
            WHEN "provider_name" = 'Lane844 Gutmann970' THEN 'Lane Gutmann'
            WHEN "provider_name" = 'Lashanda692 Gutmann970' THEN 'Lashanda Gutmann'
            WHEN "provider_name" = 'Lasonya941 Kiehn525' THEN 'Lasonya Kiehn'
            WHEN "provider_name" = 'Latasha666 Fay398' THEN 'Latasha Fay'
            WHEN "provider_name" = 'Latoria810 Eichmann909' THEN 'Latoria Eichmann'
            WHEN "provider_name" = 'Latrina689 White193' THEN 'Latrina White'
            WHEN "provider_name" = 'Leif534 Hane680' THEN 'Leif Hane'
            WHEN "provider_name" = 'Leonarda398 Schumm995' THEN 'Leonarda Schumm'
            WHEN "provider_name" = 'Lindsay928 Leffler128' THEN 'Lindsay Leffler'
            WHEN "provider_name" = 'Linn541 Lynch190' THEN 'Linn Lynch'
            WHEN "provider_name" = 'Lino542 Feest103' THEN 'Lino Feest'
            WHEN "provider_name" = 'Livia401 Rippin620' THEN 'Livia Rippin'
            WHEN "provider_name" = 'Luvenia178 Breitenberg711' THEN 'Luvenia Breitenberg'
            WHEN "provider_name" = 'Lyman173 Kshlerin58' THEN 'Lyman Kshlerin'
            WHEN "provider_name" = 'Madelaine318 Walker122' THEN 'Madelaine Walker'
            WHEN "provider_name" = 'Magdalena964 Torphy630' THEN 'Magdalena Torphy'
            WHEN "provider_name" = 'Maragaret140 Erdman779' THEN 'Maragaret Erdman'
            WHEN "provider_name" = 'Maren639 Aufderhar910' THEN 'Maren Aufderhar'
            WHEN "provider_name" = 'Margarite168 Koepp521' THEN 'Margarite Koepp'
            WHEN "provider_name" = 'Margene509 Schamberger479' THEN 'Margene Schamberger'
            WHEN "provider_name" = 'Mariah942 Hilpert278' THEN 'Mariah Hilpert'
            WHEN "provider_name" = 'Mariana775 Menéndez746' THEN 'Mariana Menéndez'
            WHEN "provider_name" = 'Mariette443 Rau926' THEN 'Mariette Rau'
            WHEN "provider_name" = 'Matthew562 Bailey598' THEN 'Matthew Bailey'
            WHEN "provider_name" = 'Maynard46 Ward668' THEN 'Maynard Ward'
            WHEN "provider_name" = 'Melissa844 Yundt842' THEN 'Melissa Yundt'
            WHEN "provider_name" = 'Mertie42 Lakin515' THEN 'Mertie Lakin'
            WHEN "provider_name" = 'Milda157 Schoen8' THEN 'Milda Schoen'
            WHEN "provider_name" = 'Millard193 Doyle959' THEN 'Millard Doyle'
            WHEN "provider_name" = 'Nicholle822 Satterfield305' THEN 'Nicholle Satterfield'
            WHEN "provider_name" = 'Noma845 Mraz590' THEN 'Noma Mraz'
            WHEN "provider_name" = 'Norah104 Jenkins714' THEN 'Norah Jenkins'
            WHEN "provider_name" = 'Olen518 Farrell962' THEN 'Olen Farrell'
            WHEN "provider_name" = 'Orpha286 Marks830' THEN 'Orpha Marks'
            WHEN "provider_name" = 'Paris331 Ruecker817' THEN 'Paris Ruecker'
            WHEN "provider_name" = 'Patrina117 Strosin214' THEN 'Patrina Strosin'
            WHEN "provider_name" = 'Paulene52 Kihn564' THEN 'Paulene Kihn'
            WHEN "provider_name" = 'Precious140 Runolfsson901' THEN 'Precious Runolfsson'
            WHEN "provider_name" = 'Ramona980 Zavala169' THEN 'Ramona Zavala'
            WHEN "provider_name" = 'Randa356 Ritchie586' THEN 'Randa Ritchie'
            WHEN "provider_name" = 'Randy380 Bergstrom287' THEN 'Randy Bergstrom'
            WHEN "provider_name" = 'Randy380 Kilback373' THEN 'Randy Kilback'
            WHEN "provider_name" = 'Rebekah348 Rippin620' THEN 'Rebekah Rippin'
            WHEN "provider_name" = 'Reginia455 Johnson679' THEN 'Reginia Johnson'
            WHEN "provider_name" = 'Rex53 Gerhold939' THEN 'Rex Gerhold'
            WHEN "provider_name" = 'Rhonda22 Bins636' THEN 'Rhonda Bins'
            WHEN "provider_name" = 'Rod343 Durgan499' THEN 'Rod Durgan'
            WHEN "provider_name" = 'Rod343 Goyette777' THEN 'Rod Goyette'
            WHEN "provider_name" = 'Rodney21 Hettinger594' THEN 'Rodney Hettinger'
            WHEN "provider_name" = 'Rolland302 Lemke654' THEN 'Rolland Lemke'
            WHEN "provider_name" = 'Royce974 Kohler843' THEN 'Royce Kohler'
            WHEN "provider_name" = 'Rupert654 Walker122' THEN 'Rupert Walker'
            WHEN "provider_name" = 'Rusty501 Rodriguez71' THEN 'Rusty Rodriguez'
            WHEN "provider_name" = 'Sallie654 Barrows492' THEN 'Sallie Barrows'
            WHEN "provider_name" = 'Samuel331 Runte676' THEN 'Samuel Runte'
            WHEN "provider_name" = 'Santina680 Dicki44' THEN 'Santina Dicki'
            WHEN "provider_name" = 'Sean831 DuBuque211' THEN 'Sean DuBuque'
            WHEN "provider_name" = 'Sharilyn202 Wolff180' THEN 'Sharilyn Wolff'
            WHEN "provider_name" = 'Shasta644 King743' THEN 'Shasta King'
            WHEN "provider_name" = 'Shawanna357 Roob72' THEN 'Shawanna Roob'
            WHEN "provider_name" = 'Shayla126 White193' THEN 'Shayla White'
            WHEN "provider_name" = 'Shon148 Reinger292' THEN 'Shon Reinger'
            WHEN "provider_name" = 'Shon148 Swift555' THEN 'Shon Swift'
            WHEN "provider_name" = 'Tambra47 Heaney114' THEN 'Tambra Heaney'
            WHEN "provider_name" = 'Tamisha203 Wilderman619' THEN 'Tamisha Wilderman'
            WHEN "provider_name" = 'Tashina114 Towne435' THEN 'Tashina Towne'
            WHEN "provider_name" = 'Teodoro374 Koss676' THEN 'Teodoro Koss'
            WHEN "provider_name" = 'Terence292 Brakus656' THEN 'Terence Brakus'
            WHEN "provider_name" = 'Tomás404 Hinojosa147' THEN 'Tomás Hinojosa'
            WHEN "provider_name" = 'Trey250 Satterfield305' THEN 'Trey Satterfield'
            WHEN "provider_name" = 'Trinity427 Hartmann983' THEN 'Trinity Hartmann'
            WHEN "provider_name" = 'Troy560 Carter549' THEN 'Troy Carter'
            WHEN "provider_name" = 'Tyron580 Littel644' THEN 'Tyron Littel'
            WHEN "provider_name" = 'Venice604 Rogahn59' THEN 'Venice Rogahn'
            WHEN "provider_name" = 'Vito638 Barton704' THEN 'Vito Barton'
            WHEN "provider_name" = 'Waldo53 Hintz995' THEN 'Waldo Hintz'
            WHEN "provider_name" = 'Willian804 Batz141' THEN 'Willian Batz'
            WHEN "provider_name" = 'Wilson960 Turner526' THEN 'Wilson Turner'
            WHEN "provider_name" = 'Zane918 Bosco882' THEN 'Zane Bosco'
            ELSE "provider_name"
        END AS "provider_name",
        "GENDER",
        "SPECIALITY",
        "street_address",
        "CITY",
        "STATE",
        "ZIP",
        "latitude",
        "longitude",
        "patient_encounters",
        "procedures_performed"
    FROM "providers_renamed"
),

"providers_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- ZIP: from INT to VARCHAR
    -- organization_id: from VARCHAR to UUID
    -- provider_id: from VARCHAR to UUID
    SELECT
        "provider_name",
        "GENDER",
        "SPECIALITY",
        "street_address",
        "CITY",
        "STATE",
        "latitude",
        "longitude",
        "patient_encounters",
        "procedures_performed",
        CAST("ZIP" AS VARCHAR) 
        AS "ZIP",
        CAST("organization_id" AS UUID) 
        AS "organization_id",
        CAST("provider_id" AS UUID) 
        AS "provider_id"
    FROM "providers_renamed_cleaned"
)

-- COCOON BLOCK END
SELECT *
FROM "providers_renamed_cleaned_casted"

version: 2
models:
- name: stg_providers
  description: The table is about healthcare providers. It includes their unique ID,
    organization, name, gender, specialty, and contact details. The table also provides
    location information including address, city, state, ZIP code, latitude, and longitude.
    Additionally, it records the number of encounters and procedures for each provider.
    All providers listed specialize in general practice and are located in Massachusetts.
  columns:
  - name: provider_name
    description: Full name of the healthcare provider
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: GENDER
    description: Gender of the healthcare provider
    tests:
    - not_null
    - accepted_values:
        values:
        - F
        - M
    cocoon_meta:
      future_accepted_values:
      - O
      data_type:
        current_data_type: VARCHAR
  - name: SPECIALITY
    description: Medical specialty of the provider
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: street_address
    description: Street address of the provider's practice
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: ^\d+\s+[A-Z\s]+(?:ST|AVE|RD|DRIVE|STREET|AVENUE|ROAD)$
        summary: Basic street address with number and street name
      - regex: ^\d+\s+[A-Z\s]+(?:ST|AVE|RD|DRIVE|STREET|AVENUE|ROAD)(?:\s+(?:SUITE|STE)\s+\d+)?$
        summary: Address with suite or unit number
      - regex: ^[A-Z0-9\s]+$
        summary: General pattern allowing for various formats
      - regex: \d+\s+[A-Za-z\s&''\.]+\s+(Road|ST\.|Street|ROAD|PL)
        summary: Street addresses with number, name, and type (most strict)
      - regex: \d+\s+[A-Za-z\s&''\.]+
        summary: Street addresses with number and name (less strict)
      data_type:
        current_data_type: VARCHAR
  - name: CITY
    description: City where the provider's practice is located
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: STATE
    description: State where the provider's practice is located
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: latitude
    description: Latitude coordinate of the provider's location
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: longitude
    description: Longitude coordinate of the provider's location
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: patient_encounters
    description: Number of patient encounters for the provider
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: procedures_performed
    description: Number of procedures performed by the provider
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: ZIP
    description: ZIP code of the provider's practice
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: organization_id
    description: Identifier for the provider's organization
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: provider_id
    description: Unique identifier for the healthcare provider
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column represents a unique identifier for the healthcare
        provider. For this table, each row is for a unique provider. The provider_id
        is designed to be unique for each provider, ensuring no two providers have
        the same ID.
      data_type:
        current_data_type: UUID
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 01:17:12.506926+00:00
WITH 
"supplies_renamed" AS (
    -- Rename: Renaming columns
    -- DATE_ -> order_date
    -- PATIENT -> patient_id
    -- ENCOUNTER -> encounter_id
    -- CODE -> supply_code
    -- DESCRIPTION -> supply_description
    -- QUANTITY -> quantity_ordered
    SELECT 
        "DATE_" AS "order_date",
        "PATIENT" AS "patient_id",
        "ENCOUNTER" AS "encounter_id",
        "CODE" AS "supply_code",
        "DESCRIPTION" AS "supply_description",
        "QUANTITY" AS "quantity_ordered"
    FROM "memory"."main"."supplies"
),

"supplies_renamed_dedup" AS (
    -- Deduplication: Removed 7 duplicated rows
    SELECT DISTINCT *
    FROM "supplies_renamed"
),

"supplies_renamed_dedup_cleaned" AS (
    -- Clean unusual string values: 
    -- supply_description: The supply_description column has several inconsistencies: 1. Inconsistent spacing: 'Air filter  device' has an extra space. 2. Inconsistent representations of CPAP/BPAP: Some use full words, others use abbreviations. 3. Inconsistent use of parentheses: Most entries have '(physical object)' at the end, but one has '(substance)' and another has '(product)'. 4. Inconsistent capitalization: Some entries start with capital letters, others don't. The correct values should have consistent spacing, use full words instead of abbreviations, use '(physical object)' consistently for devices, and have consistent capitalization.
    SELECT
        "order_date",
        "patient_id",
        "encounter_id",
        "supply_code",
        CASE
            WHEN "supply_description" = 'Air filter  device (physical object)' THEN 'Air filter device (physical object)'
            WHEN "supply_description" = 'CPAP/BPAP oral mask (physical object)' THEN 'Continuous positive airway pressure/Bilevel positive airway pressure oral mask (physical object)'
            WHEN "supply_description" = 'Solution (substance)' THEN 'Solution (physical object)'
            WHEN "supply_description" = 'Packed red blood cells (product)' THEN 'Packed red blood cells (physical object)'
            ELSE "supply_description"
        END AS "supply_description",
        "quantity_ordered"
    FROM "supplies_renamed_dedup"
),

"supplies_renamed_dedup_cleaned_casted" AS (
    -- Column Type Casting: 
    -- encounter_id: from VARCHAR to UUID
    -- order_date: from VARCHAR to DATE
    -- patient_id: from VARCHAR to UUID
    -- supply_code: from INT to VARCHAR
    SELECT
        "supply_description",
        "quantity_ordered",
        CAST("encounter_id" AS UUID) 
        AS "encounter_id",
        CAST("order_date" AS DATE) 
        AS "order_date",
        CAST("patient_id" AS UUID) 
        AS "patient_id",
        CAST("supply_code" AS VARCHAR) 
        AS "supply_code"
    FROM "supplies_renamed_dedup_cleaned"
)

-- COCOON BLOCK END
SELECT *
FROM "supplies_renamed_dedup_cleaned_casted"

version: 2
models:
- name: stg_supplies
  description: The table is about medical supply orders. It shows the date, patient
    ID, encounter ID, supply code, description, and quantity ordered. All entries
    are for blood glucose testing strips. The same patient received 50 strips on multiple
    occasions between 1993 and 2005. Each order is associated with a unique encounter
    ID.
  columns:
  - name: supply_description
    description: Text description of the ordered medical supply
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: quantity_ordered
    description: Number of units ordered
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: encounter_id
    description: Unique identifier for each medical encounter
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: order_date
    description: Date of the medical supply order
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DATE
  - name: patient_id
    description: Unique identifier for the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: supply_code
    description: Standardized code for the medical supply
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 00:59:11.305102+00:00
WITH 
"encounters_renamed" AS (
    -- Rename: Renaming columns
    -- Id -> encounter_id
    -- START -> encounter_start
    -- STOP -> encounter_end
    -- PATIENT -> patient_id
    -- ORGANIZATION -> organization_id
    -- PROVIDER -> provider_id
    -- PAYER -> payer_id
    -- ENCOUNTERCLASS -> encounter_type
    -- CODE -> procedure_code
    -- DESCRIPTION -> procedure_description
    -- BASE_ENCOUNTER_COST -> base_cost
    -- TOTAL_CLAIM_COST -> total_cost
    -- PAYER_COVERAGE -> payer_coverage
    -- REASONCODE -> reason_code
    -- REASONDESCRIPTION -> reason_description
    SELECT 
        "Id" AS "encounter_id",
        "START" AS "encounter_start",
        "STOP" AS "encounter_end",
        "PATIENT" AS "patient_id",
        "ORGANIZATION" AS "organization_id",
        "PROVIDER" AS "provider_id",
        "PAYER" AS "payer_id",
        "ENCOUNTERCLASS" AS "encounter_type",
        "CODE" AS "procedure_code",
        "DESCRIPTION" AS "procedure_description",
        "BASE_ENCOUNTER_COST" AS "base_cost",
        "TOTAL_CLAIM_COST" AS "total_cost",
        "PAYER_COVERAGE" AS "payer_coverage",
        "REASONCODE" AS "reason_code",
        "REASONDESCRIPTION" AS "reason_description"
    FROM "memory"."main"."encounters"
),

"encounters_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- procedure_description: The procedure_description column has several inconsistencies: 1. Some entries have "(procedure)" appended while others don't. 2. There are redundant entries like "Encounter for problem" and "Encounter for problem (procedure)". 3. Some entries have inconsistent capitalization or punctuation. 4. A few entries seem out of place or too specific compared to others.The correct values should follow a consistent pattern, preferably with "(procedure)" appended where appropriate, and use consistent capitalization and punctuation.
    -- reason_description: The problem is inconsistent labeling of medical conditions. Some values include '(disorder)' at the end while others don't. Additionally, some conditions are labeled as '(finding)' or '(situation)'. The correct values should consistently use the base condition name without these suffixes for uniformity.
    SELECT
        "encounter_id",
        "encounter_start",
        "encounter_end",
        "patient_id",
        "organization_id",
        "provider_id",
        "payer_id",
        "encounter_type",
        "procedure_code",
        CASE
            WHEN "procedure_description" = 'Follow-up encounter' THEN 'Follow-up encounter (procedure)'
            WHEN "procedure_description" = 'Encounter for problem' THEN 'Encounter for problem (procedure)'
            WHEN "procedure_description" = 'Prenatal visit' THEN 'Prenatal visit (procedure)'
            WHEN "procedure_description" = 'Patient encounter procedure' THEN 'Patient encounter procedure (procedure)'
            WHEN "procedure_description" = 'Encounter for ''check-up''' THEN 'Encounter for check up (procedure)'
            WHEN "procedure_description" = 'Emergency room admission (procedure)' THEN 'Emergency Room Admission (procedure)'
            WHEN "procedure_description" = 'Emergency Room Admission' THEN 'Emergency Room Admission (procedure)'
            WHEN "procedure_description" = 'Encounter for symptom' THEN 'Encounter for symptom (procedure)'
            WHEN "procedure_description" = 'Asthma follow-up' THEN 'Asthma follow-up (procedure)'
            WHEN "procedure_description" = 'Postnatal visit' THEN 'Postnatal visit (procedure)'
            WHEN "procedure_description" = 'Death Certification' THEN 'Death Certification (procedure)'
            WHEN "procedure_description" = 'Gynecology service (qualifier value)' THEN 'Gynecology service (procedure)'
            WHEN "procedure_description" = 'Screening surveillance (regime/therapy)' THEN 'Screening surveillance (procedure)'
            WHEN "procedure_description" = 'Encounter Inpatient' THEN 'Inpatient encounter (procedure)'
            WHEN "procedure_description" = 'Drug rehabilitation and detoxification' THEN 'Drug rehabilitation and detoxification (procedure)'
            WHEN "procedure_description" = 'Office Visit' THEN 'Office visit (procedure)'
            WHEN "procedure_description" = 'Emergency Encounter' THEN 'Emergency encounter (procedure)'
            WHEN "procedure_description" = 'Encounter for Problem' THEN 'Encounter for problem (procedure)'
            WHEN "procedure_description" = 'Patient-initiated encounter' THEN 'Patient-initiated encounter (procedure)'
            WHEN "procedure_description" = 'Admission to thoracic surgery department' THEN 'Admission to thoracic surgery department (procedure)'
            WHEN "procedure_description" = 'posttraumatic stress disorder' THEN 'Posttraumatic stress disorder follow-up (procedure)'
            WHEN "procedure_description" = 'Initial Psychiatric Interview with mental status evaluation' THEN 'Initial psychiatric interview (procedure)'
            ELSE "procedure_description"
        END AS "procedure_description",
        "base_cost",
        "total_cost",
        "payer_coverage",
        "reason_code",
        CASE
            WHEN "reason_description" = 'Chronic congestive heart failure (disorder)' THEN 'Chronic congestive heart failure'
            WHEN "reason_description" = 'Viral sinusitis (disorder)' THEN 'Viral sinusitis'
            WHEN "reason_description" = 'Acute bronchitis (disorder)' THEN 'Acute bronchitis'
            WHEN "reason_description" = 'Acute viral pharyngitis (disorder)' THEN 'Acute viral pharyngitis'
            WHEN "reason_description" = 'Malignant neoplasm of breast (disorder)' THEN 'Malignant neoplasm of breast'
            WHEN "reason_description" = 'Ischemic heart disease (disorder)' THEN 'Ischemic heart disease'
            WHEN "reason_description" = 'Anemia (disorder)' THEN 'Anemia'
            WHEN "reason_description" = 'Sinusitis (disorder)' THEN 'Sinusitis'
            WHEN "reason_description" = 'Sleep disorder (disorder)' THEN 'Sleep disorder'
            WHEN "reason_description" = 'Acute bacterial sinusitis (disorder)' THEN 'Acute bacterial sinusitis'
            WHEN "reason_description" = 'Streptococcal sore throat (disorder)' THEN 'Streptococcal sore throat'
            WHEN "reason_description" = 'Acute myeloid leukemia  disease (disorder)' THEN 'Acute myeloid leukemia'
            WHEN "reason_description" = 'Alzheimer''s disease (disorder)' THEN 'Alzheimer''s disease'
            WHEN "reason_description" = 'History of coronary artery bypass grafting (situation)' THEN 'History of coronary artery bypass grafting'
            WHEN "reason_description" = 'Abnormal findings diagnostic imaging heart+coronary circulat (finding)' THEN 'Abnormal findings diagnostic imaging heart and coronary circulation'
            WHEN "reason_description" = 'Acute ST segment elevation myocardial infarction (disorder)' THEN 'Acute ST segment elevation myocardial infarction'
            WHEN "reason_description" = 'Acute non-ST segment elevation myocardial infarction (disorder)' THEN 'Acute non-ST segment elevation myocardial infarction'
            WHEN "reason_description" = 'Pulmonary emphysema (disorder)' THEN 'Pulmonary emphysema'
            WHEN "reason_description" = 'Aortic valve stenosis (disorder)' THEN 'Aortic valve stenosis'
            WHEN "reason_description" = 'Sepsis (disorder)' THEN 'Sepsis'
            WHEN "reason_description" = 'Chronic obstructive bronchitis (disorder)' THEN 'Chronic obstructive bronchitis'
            WHEN "reason_description" = 'Fracture of forearm' THEN 'Fracture of Forearm'
            WHEN "reason_description" = 'Fracture subluxation of wrist' THEN 'Fracture Subluxation of Wrist'
            WHEN "reason_description" = 'Injury of medial collateral ligament of knee' THEN 'Injury of Medial Collateral Ligament of Knee'
            WHEN "reason_description" = 'Localized  primary osteoarthritis of the hand' THEN 'Localized Primary Osteoarthritis of the Hand'
            WHEN "reason_description" = 'Perennial allergic rhinitis' THEN 'Perennial Allergic Rhinitis'
            WHEN "reason_description" = 'Perennial allergic rhinitis with seasonal variation' THEN 'Perennial Allergic Rhinitis with Seasonal Variation'
            WHEN "reason_description" = 'Polyp of colon' THEN 'Polyp of Colon'
            ELSE "reason_description"
        END AS "reason_description"
    FROM "encounters_renamed"
),

"encounters_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- encounter_end: from VARCHAR to TIMESTAMP
    -- encounter_id: from VARCHAR to UUID
    -- encounter_start: from VARCHAR to TIMESTAMP
    -- organization_id: from VARCHAR to UUID
    -- patient_id: from VARCHAR to UUID
    -- payer_id: from VARCHAR to UUID
    -- procedure_code: from INT to VARCHAR
    -- provider_id: from VARCHAR to UUID
    -- reason_code: from DECIMAL to VARCHAR
    SELECT
        "encounter_type",
        "procedure_description",
        "base_cost",
        "total_cost",
        "payer_coverage",
        "reason_description",
        CAST("encounter_end" AS TIMESTAMP) 
        AS "encounter_end",
        CAST("encounter_id" AS UUID) 
        AS "encounter_id",
        CAST("encounter_start" AS TIMESTAMP) 
        AS "encounter_start",
        CAST("organization_id" AS UUID) 
        AS "organization_id",
        CAST("patient_id" AS UUID) 
        AS "patient_id",
        CAST("payer_id" AS UUID) 
        AS "payer_id",
        CAST("procedure_code" AS VARCHAR) 
        AS "procedure_code",
        CAST("provider_id" AS UUID) 
        AS "provider_id",
        CAST("reason_code" AS VARCHAR) 
        AS "reason_code"
    FROM "encounters_renamed_cleaned"
)

-- COCOON BLOCK END
SELECT *
FROM "encounters_renamed_cleaned_casted"

version: 2
models:
- name: stg_encounters
  description: The table is about medical encounters. It includes details like encounter
    ID, start and stop times, patient ID, provider ID, and payer ID. Each encounter
    has a class, code, description, and associated costs. The table links patients,
    healthcare providers, organizations, and payers involved in each encounter. It
    also includes information on claim costs and payer coverage.
  columns:
  - name: encounter_type
    description: Type or category of the medical encounter
    tests:
    - not_null
    - accepted_values:
        values:
        - ambulatory
        - wellness
        - outpatient
        - emergency
        - inpatient
        - home
        - urgentcare
        - snf
        - virtual
        - hospice
    cocoon_meta:
      future_accepted_values:
      - primary care
      - specialist
      - dental
      - vision
      - mental health
      - rehabilitation
      - palliative
      - long-term care
      data_type:
        current_data_type: VARCHAR
  - name: procedure_description
    description: Description of the procedure or visit
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: base_cost
    description: Base cost of the medical encounter
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: total_cost
    description: Total cost claimed for the encounter
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: payer_coverage
    description: Amount covered by the insurance payer
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: reason_description
    description: Description of the reason for the encounter
    cocoon_meta:
      missing_reason: Not applicable for routine wellness visits without specific
        issues.
      data_type:
        current_data_type: VARCHAR
  - name: encounter_end
    description: End date and time of the encounter
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: encounter_id
    description: Unique identifier for the encounter
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column is explicitly described as a unique identifier for
        the encounter. For this table, each row represents a unique medical encounter.
        The encounter_id is designed to be unique across all encounters.
      data_type:
        current_data_type: UUID
  - name: encounter_start
    description: Start date and time of the encounter
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: organization_id
    description: Unique identifier for the healthcare organization
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: patient_id
    description: Unique identifier for the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: payer_id
    description: Unique identifier for the insurance payer
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: procedure_code
    description: Medical code for the procedure or visit
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: provider_id
    description: Unique identifier for the healthcare provider
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: reason_code
    description: Code for the reason of the encounter
    cocoon_meta:
      missing_reason: Not applicable for routine wellness visits without specific
        issues.
      data_type:
        current_data_type: VARCHAR
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 01:12:14.679962+00:00
WITH 
"payers_renamed" AS (
    -- Rename: Renaming columns
    -- Id -> payer_id
    -- NAME -> payer_name
    -- OWNERSHIP -> ownership_type
    -- ADDRESS -> street_address
    -- CITY -> city
    -- STATE_HEADQUARTERED -> state
    -- ZIP -> zip_code
    -- PHONE -> phone_number
    -- AMOUNT_COVERED -> covered_amount
    -- AMOUNT_UNCOVERED -> uncovered_amount
    -- REVENUE -> revenue
    -- COVERED_ENCOUNTERS -> covered_encounters
    -- UNCOVERED_ENCOUNTERS -> uncovered_encounters
    -- COVERED_MEDICATIONS -> covered_medications
    -- UNCOVERED_MEDICATIONS -> uncovered_medications
    -- COVERED_PROCEDURES -> covered_procedures
    -- UNCOVERED_PROCEDURES -> uncovered_procedures
    -- COVERED_IMMUNIZATIONS -> covered_immunizations
    -- UNCOVERED_IMMUNIZATIONS -> uncovered_immunizations
    -- UNIQUE_CUSTOMERS -> unique_customers
    -- QOLS_AVG -> average_qol_score
    -- MEMBER_MONTHS -> member_months
    SELECT 
        "Id" AS "payer_id",
        "NAME" AS "payer_name",
        "OWNERSHIP" AS "ownership_type",
        "ADDRESS" AS "street_address",
        "CITY" AS "city",
        "STATE_HEADQUARTERED" AS "state",
        "ZIP" AS "zip_code",
        "PHONE" AS "phone_number",
        "AMOUNT_COVERED" AS "covered_amount",
        "AMOUNT_UNCOVERED" AS "uncovered_amount",
        "REVENUE" AS "revenue",
        "COVERED_ENCOUNTERS" AS "covered_encounters",
        "UNCOVERED_ENCOUNTERS" AS "uncovered_encounters",
        "COVERED_MEDICATIONS" AS "covered_medications",
        "UNCOVERED_MEDICATIONS" AS "uncovered_medications",
        "COVERED_PROCEDURES" AS "covered_procedures",
        "UNCOVERED_PROCEDURES" AS "uncovered_procedures",
        "COVERED_IMMUNIZATIONS" AS "covered_immunizations",
        "UNCOVERED_IMMUNIZATIONS" AS "uncovered_immunizations",
        "UNIQUE_CUSTOMERS" AS "unique_customers",
        "QOLS_AVG" AS "average_qol_score",
        "MEMBER_MONTHS" AS "member_months"
    FROM "memory"."main"."payers"
),

"payers_renamed_casted" AS (
    -- Column Type Casting: 
    -- city: from DECIMAL to VARCHAR
    -- payer_id: from VARCHAR to UUID
    -- phone_number: from DECIMAL to VARCHAR
    -- state: from DECIMAL to VARCHAR
    -- street_address: from DECIMAL to VARCHAR
    -- zip_code: from DECIMAL to VARCHAR
    SELECT
        "payer_name",
        "ownership_type",
        "covered_amount",
        "uncovered_amount",
        "revenue",
        "covered_encounters",
        "uncovered_encounters",
        "covered_medications",
        "uncovered_medications",
        "covered_procedures",
        "uncovered_procedures",
        "covered_immunizations",
        "uncovered_immunizations",
        "unique_customers",
        "average_qol_score",
        "member_months",
        CAST("city" AS VARCHAR) 
        AS "city",
        CAST("payer_id" AS UUID) 
        AS "payer_id",
        CAST("phone_number" AS VARCHAR) 
        AS "phone_number",
        CAST("state" AS VARCHAR) 
        AS "state",
        CAST("street_address" AS VARCHAR) 
        AS "street_address",
        CAST("zip_code" AS VARCHAR) 
        AS "zip_code"
    FROM "payers_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "payers_renamed_casted"

version: 2
models:
- name: stg_payers
  description: The table is about payers in the healthcare system. It includes government
    and private payers. Each payer has an ID, name, ownership type, and contact details.
    The table provides financial data like covered and uncovered amounts, revenue,
    and encounters. It also includes information on medications, procedures, immunizations,
    unique customers, quality of life scores, and member months.
  columns:
  - name: payer_name
    description: Name of the payer organization
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: ownership_type
    description: Type of ownership (government or private)
    tests:
    - not_null
    - accepted_values:
        values:
        - PRIVATE
        - GOVERNMENT
        - NO_INSURANCE
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: covered_amount
    description: Total amount covered by the payer
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: uncovered_amount
    description: Total amount not covered by the payer
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: revenue
    description: Total revenue generated by the payer
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: covered_encounters
    description: Number of covered healthcare encounters
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: uncovered_encounters
    description: Number of uncovered healthcare encounters
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: covered_medications
    description: Number of covered medications
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: uncovered_medications
    description: Number of uncovered medications
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: covered_procedures
    description: Number of covered medical procedures
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: uncovered_procedures
    description: Number of uncovered medical procedures
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: covered_immunizations
    description: Number of covered immunizations
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: uncovered_immunizations
    description: Number of uncovered immunizations
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: unique_customers
    description: Number of unique customers served
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: average_qol_score
    description: Average Quality of Life Score
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: member_months
    description: Total number of months members were enrolled
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: city
    description: City where the payer is located
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: payer_id
    description: Unique identifier for each payer
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column represents a unique identifier for each payer. For
        this table, each row is for a distinct payer, and payer_id is unique across
        rows.
      data_type:
        current_data_type: UUID
  - name: phone_number
    description: Contact phone number for the payer
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: state
    description: State where the payer is headquartered
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: street_address
    description: Street address of the payer
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: zip_code
    description: ZIP code of the payer's location
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 01:11:16.831598+00:00
WITH 
"patients_renamed" AS (
    -- Rename: Renaming columns
    -- Id -> patient_id
    -- BIRTHDATE -> birth_date
    -- DEATHDATE -> death_date
    -- SSN -> ssn
    -- DRIVERS -> drivers_license
    -- PASSPORT -> passport_number
    -- PREFIX -> name_prefix
    -- FIRST_ -> first_name
    -- LAST_ -> last_name
    -- SUFFIX -> name_suffix
    -- MAIDEN -> maiden_name
    -- MARITAL -> marital_status
    -- RACE -> race
    -- ETHNICITY -> ethnicity
    -- GENDER -> gender
    -- BIRTHPLACE -> birthplace
    -- ADDRESS -> street_address
    -- CITY -> city
    -- STATE -> state
    -- COUNTY -> county
    -- FIPS -> fips_code
    -- ZIP -> zip_code
    -- LAT -> latitude
    -- LON -> longitude
    -- HEALTHCARE_EXPENSES -> healthcare_expenses
    -- HEALTHCARE_COVERAGE -> healthcare_coverage
    -- INCOME -> annual_income
    SELECT 
        "Id" AS "patient_id",
        "BIRTHDATE" AS "birth_date",
        "DEATHDATE" AS "death_date",
        "SSN" AS "ssn",
        "DRIVERS" AS "drivers_license",
        "PASSPORT" AS "passport_number",
        "PREFIX" AS "name_prefix",
        "FIRST_" AS "first_name",
        "LAST_" AS "last_name",
        "SUFFIX" AS "name_suffix",
        "MAIDEN" AS "maiden_name",
        "MARITAL" AS "marital_status",
        "RACE" AS "race",
        "ETHNICITY" AS "ethnicity",
        "GENDER" AS "gender",
        "BIRTHPLACE" AS "birthplace",
        "ADDRESS" AS "street_address",
        "CITY" AS "city",
        "STATE" AS "state",
        "COUNTY" AS "county",
        "FIPS" AS "fips_code",
        "ZIP" AS "zip_code",
        "LAT" AS "latitude",
        "LON" AS "longitude",
        "HEALTHCARE_EXPENSES" AS "healthcare_expenses",
        "HEALTHCARE_COVERAGE" AS "healthcare_coverage",
        "INCOME" AS "annual_income"
    FROM "memory"."main"."patients"
),

"patients_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- ssn: The problem is that all the SSN values begin with 999, which is invalid for U.S. Social Security Numbers. Valid SSNs never start with 999. These appear to be placeholder or dummy SSNs rather than real ones. Since we can't determine the correct SSNs from this information alone, the best approach is to map these invalid values to an empty string to indicate missing data.
    -- first_name: The problem is that all first names have a random 3-digit number appended to them. These numbers appear to be meaningless and don't provide any useful information. The correct values should be just the first names without the numbers.
    -- last_name: The problem is that most last names follow a pattern of a capitalized English surname followed by 3 digits, but there are two exceptions: 1. "Bermúdez789" contains a non-English character (ú). 2. "Roldán470" contains a non-English character (á). These should be normalized to use standard English characters. The correct values should maintain the same naming pattern as the majority of entries.
    -- maiden_name: The problem is that all values in the maiden_name column are unusual because they combine surnames with three-digit numbers, which is not typical for real maiden names. This appears to be a data generation artifact rather than genuine maiden names. Since we can't determine the correct maiden names from this data, the best approach is to remove the numeric portion and keep only the surname part, which could potentially be valid maiden names.
    SELECT
        "patient_id",
        "birth_date",
        "death_date",
        CASE
            WHEN "ssn" = '999-10-1178' THEN NULL
            WHEN "ssn" = '999-10-6028' THEN NULL
            WHEN "ssn" = '999-12-9121' THEN NULL
            WHEN "ssn" = '999-14-9380' THEN NULL
            WHEN "ssn" = '999-14-9672' THEN NULL
            WHEN "ssn" = '999-16-4297' THEN NULL
            WHEN "ssn" = '999-17-2611' THEN NULL
            WHEN "ssn" = '999-20-4271' THEN NULL
            WHEN "ssn" = '999-20-5403' THEN NULL
            WHEN "ssn" = '999-22-2635' THEN NULL
            WHEN "ssn" = '999-23-4696' THEN NULL
            WHEN "ssn" = '999-23-9351' THEN NULL
            WHEN "ssn" = '999-26-4422' THEN NULL
            WHEN "ssn" = '999-26-8041' THEN NULL
            WHEN "ssn" = '999-29-2359' THEN NULL
            WHEN "ssn" = '999-29-4844' THEN NULL
            WHEN "ssn" = '999-29-7349' THEN NULL
            WHEN "ssn" = '999-30-8851' THEN NULL
            WHEN "ssn" = '999-32-4862' THEN NULL
            WHEN "ssn" = '999-33-4589' THEN NULL
            WHEN "ssn" = '999-35-8448' THEN NULL
            WHEN "ssn" = '999-36-1150' THEN NULL
            WHEN "ssn" = '999-37-8682' THEN NULL
            WHEN "ssn" = '999-38-7473' THEN NULL
            WHEN "ssn" = '999-41-1756' THEN NULL
            WHEN "ssn" = '999-42-9847' THEN NULL
            WHEN "ssn" = '999-43-4282' THEN NULL
            WHEN "ssn" = '999-44-2795' THEN NULL
            WHEN "ssn" = '999-44-9634' THEN NULL
            WHEN "ssn" = '999-48-5926' THEN NULL
            WHEN "ssn" = '999-49-9846' THEN NULL
            WHEN "ssn" = '999-50-5586' THEN NULL
            WHEN "ssn" = '999-50-5697' THEN NULL
            WHEN "ssn" = '999-51-3221' THEN NULL
            WHEN "ssn" = '999-55-3195' THEN NULL
            WHEN "ssn" = '999-55-6098' THEN NULL
            WHEN "ssn" = '999-56-9201' THEN NULL
            WHEN "ssn" = '999-57-7157' THEN NULL
            WHEN "ssn" = '999-58-7543' THEN NULL
            WHEN "ssn" = '999-59-2568' THEN NULL
            WHEN "ssn" = '999-61-4140' THEN NULL
            WHEN "ssn" = '999-61-6611' THEN NULL
            WHEN "ssn" = '999-61-6740' THEN NULL
            WHEN "ssn" = '999-62-7937' THEN NULL
            WHEN "ssn" = '999-70-2405' THEN NULL
            WHEN "ssn" = '999-70-4594' THEN NULL
            WHEN "ssn" = '999-71-8314' THEN NULL
            WHEN "ssn" = '999-73-5643' THEN NULL
            WHEN "ssn" = '999-77-7700' THEN NULL
            WHEN "ssn" = '999-79-2426' THEN NULL
            WHEN "ssn" = '999-79-3695' THEN NULL
            WHEN "ssn" = '999-85-2178' THEN NULL
            WHEN "ssn" = '999-88-1792' THEN NULL
            WHEN "ssn" = '999-89-9127' THEN NULL
            WHEN "ssn" = '999-91-9580' THEN NULL
            WHEN "ssn" = '999-93-7263' THEN NULL
            WHEN "ssn" = '999-95-3792' THEN NULL
            WHEN "ssn" = '999-96-6743' THEN NULL
            WHEN "ssn" = '999-99-2106' THEN NULL
            WHEN "ssn" = '999-99-2436' THEN NULL
            ELSE "ssn"
        END AS "ssn",
        "drivers_license",
        "passport_number",
        "name_prefix",
        CASE
            WHEN "first_name" = 'Hayden835' THEN 'Hayden'
            WHEN "first_name" = 'Adelia946' THEN 'Adelia'
            WHEN "first_name" = 'Andera917' THEN 'Andera'
            WHEN "first_name" = 'Antony83' THEN 'Antony'
            WHEN "first_name" = 'Burton124' THEN 'Burton'
            WHEN "first_name" = 'Carmelita854' THEN 'Carmelita'
            WHEN "first_name" = 'Carrol931' THEN 'Carrol'
            WHEN "first_name" = 'Cedrick207' THEN 'Cedrick'
            WHEN "first_name" = 'Cheyenne169' THEN 'Cheyenne'
            WHEN "first_name" = 'Christal240' THEN 'Christal'
            WHEN "first_name" = 'Cindy893' THEN 'Cindy'
            WHEN "first_name" = 'Coleman27' THEN 'Coleman'
            WHEN "first_name" = 'Corey514' THEN 'Corey'
            WHEN "first_name" = 'Damon455' THEN 'Damon'
            WHEN "first_name" = 'Daniela614' THEN 'Daniela'
            WHEN "first_name" = 'Dione665' THEN 'Dione'
            WHEN "first_name" = 'Dominick530' THEN 'Dominick'
            WHEN "first_name" = 'Elden718' THEN 'Elden'
            WHEN "first_name" = 'Ethel888' THEN 'Ethel'
            WHEN "first_name" = 'Fletcher87' THEN 'Fletcher'
            WHEN "first_name" = 'Garry927' THEN 'Garry'
            WHEN "first_name" = 'Gayle448' THEN 'Gayle'
            WHEN "first_name" = 'Grant908' THEN 'Grant'
            WHEN "first_name" = 'Guadalupe206' THEN 'Guadalupe'
            WHEN "first_name" = 'Herb645' THEN 'Herb'
            WHEN "first_name" = 'Herschel574' THEN 'Herschel'
            WHEN "first_name" = 'Hildred696' THEN 'Hildred'
            WHEN "first_name" = 'Huey641' THEN 'Huey'
            WHEN "first_name" = 'Hunter736' THEN 'Hunter'
            WHEN "first_name" = 'Irving123' THEN 'Irving'
            WHEN "first_name" = 'Janeth814' THEN 'Janeth'
            WHEN "first_name" = 'Jenae263' THEN 'Jenae'
            WHEN "first_name" = 'Jimmie93' THEN 'Jimmie'
            WHEN "first_name" = 'Kaycee352' THEN 'Kaycee'
            WHEN "first_name" = 'Kirk871' THEN 'Kirk'
            WHEN "first_name" = 'Lacey714' THEN 'Lacey'
            WHEN "first_name" = 'Lavette209' THEN 'Lavette'
            WHEN "first_name" = 'Leandro563' THEN 'Leandro'
            WHEN "first_name" = 'Lessie363' THEN 'Lessie'
            WHEN "first_name" = 'Lillia547' THEN 'Lillia'
            WHEN "first_name" = 'Linn541' THEN 'Linn'
            WHEN "first_name" = 'Lonny638' THEN 'Lonny'
            WHEN "first_name" = 'Luke971' THEN 'Luke'
            WHEN "first_name" = 'Manuel446' THEN 'Manuel'
            WHEN "first_name" = 'Martín25' THEN 'Martín'
            WHEN "first_name" = 'Mel236' THEN 'Mel'
            WHEN "first_name" = 'Mirta419' THEN 'Mirta'
            WHEN "first_name" = 'Moises22' THEN 'Moises'
            WHEN "first_name" = 'Raymon366' THEN 'Raymon'
            WHEN "first_name" = 'Shaquana156' THEN 'Shaquana'
            WHEN "first_name" = 'Shayla126' THEN 'Shayla'
            WHEN "first_name" = 'Shiela18' THEN 'Shiela'
            WHEN "first_name" = 'Steve819' THEN 'Steve'
            WHEN "first_name" = 'Stewart672' THEN 'Stewart'
            WHEN "first_name" = 'Tyrell880' THEN 'Tyrell'
            WHEN "first_name" = 'Vanesa40' THEN 'Vanesa'
            WHEN "first_name" = 'Whitney250' THEN 'Whitney'
            WHEN "first_name" = 'Yetta429' THEN 'Yetta'
            WHEN "first_name" = 'Zoila41' THEN 'Zoila'
            ELSE "first_name"
        END AS "first_name",
        CASE
            WHEN "last_name" = 'Bermúdez789' THEN 'Bermudez789'
            WHEN "last_name" = 'Roldán470' THEN 'Roldan470'
            WHEN "last_name" = 'Wilkinson796' THEN 'Wilkinson'
            WHEN "last_name" = 'Willms744' THEN 'Willms'
            WHEN "last_name" = 'Zboncak558' THEN 'Zboncak'
            ELSE "last_name"
        END AS "last_name",
        "name_suffix",
        CASE
            WHEN "maiden_name" = 'Barela183' THEN 'Barela'
            WHEN "maiden_name" = 'Beier427' THEN 'Beier'
            WHEN "maiden_name" = 'Deckow585' THEN 'Deckow'
            WHEN "maiden_name" = 'DuBuque211' THEN 'DuBuque'
            WHEN "maiden_name" = 'Gorczany269' THEN 'Gorczany'
            WHEN "maiden_name" = 'Hegmann834' THEN 'Hegmann'
            WHEN "maiden_name" = 'Hermann103' THEN 'Hermann'
            WHEN "maiden_name" = 'Kohler843' THEN 'Kohler'
            WHEN "maiden_name" = 'Lowe577' THEN 'Lowe'
            WHEN "maiden_name" = 'Predovic534' THEN 'Predovic'
            WHEN "maiden_name" = 'Shanahan202' THEN 'Shanahan'
            ELSE "maiden_name"
        END AS "maiden_name",
        "marital_status",
        "race",
        "ethnicity",
        "gender",
        "birthplace",
        "street_address",
        "city",
        "state",
        "county",
        "fips_code",
        "zip_code",
        "latitude",
        "longitude",
        "healthcare_expenses",
        "healthcare_coverage",
        "annual_income"
    FROM "patients_renamed"
),

"patients_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- birth_date: from VARCHAR to DATE
    -- death_date: from VARCHAR to DATE
    -- patient_id: from VARCHAR to UUID
    -- zip_code: from INT to VARCHAR
    SELECT
        "ssn",
        "drivers_license",
        "passport_number",
        "name_prefix",
        "first_name",
        "last_name",
        "name_suffix",
        "maiden_name",
        "marital_status",
        "race",
        "ethnicity",
        "gender",
        "birthplace",
        "street_address",
        "city",
        "state",
        "county",
        "fips_code",
        "latitude",
        "longitude",
        "healthcare_expenses",
        "healthcare_coverage",
        "annual_income",
        CAST("birth_date" AS DATE) 
        AS "birth_date",
        CAST("death_date" AS DATE) 
        AS "death_date",
        CAST("patient_id" AS UUID) 
        AS "patient_id",
        CAST("zip_code" AS VARCHAR) 
        AS "zip_code"
    FROM "patients_renamed_cleaned"
)

-- COCOON BLOCK END
SELECT *
FROM "patients_renamed_cleaned_casted"

version: 2
models:
- name: stg_patients
  description: The table is about patients' personal and healthcare information. It
    includes demographics like birth date, race, gender, and marital status. It also
    contains contact details such as address, city, and state. Healthcare-related
    information includes expenses, coverage, and income. The table provides a comprehensive
    profile of each patient, including unique identifiers like SSN and driver's license
    numbers.
  columns:
  - name: ssn
    description: Social Security Number of the patient
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: drivers_license
    description: Driver's license number of the patient
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: passport_number
    description: Passport number of the patient
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: name_prefix
    description: Title or honorific prefix of the patient
    tests:
    - accepted_values:
        values:
        - Mr.
        - Ms.
        - Mrs.
    cocoon_meta:
      missing_reason: Not applicable if person doesn't use a prefix.
      future_accepted_values:
      - Miss
      - Dr.
      - Prof.
      - Rev.
      - Sir
      - Lady
      - Dame
      - Lord
      - Capt.
      - Lt.
      - Col.
      - Gen.
      - Adm.
      - Mx.
      data_type:
        current_data_type: VARCHAR
  - name: first_name
    description: First name of the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: last_name
    description: Last name of the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: name_suffix
    description: Name suffix of the patient, if any
    tests:
    - accepted_values:
        values:
        - JD
        - MD
        - PhD
    cocoon_meta:
      missing_reason: Not applicable if person doesn't have a suffix.
      future_accepted_values:
      - Sr.
      - Jr.
      - I
      - II
      - III
      - IV
      - V
      - Esq.
      - DDS
      - DVM
      - DO
      - RN
      - BSN
      - MSN
      - PA
      - NP
      - CPA
      - PE
      data_type:
        current_data_type: VARCHAR
  - name: maiden_name
    description: Maiden name of the patient, if applicable
    cocoon_meta:
      missing_reason: Not applicable for males or unmarried females.
      data_type:
        current_data_type: VARCHAR
  - name: marital_status
    description: Marital status of the patient
    tests:
    - accepted_values:
        values:
        - M
        - S
    cocoon_meta:
      missing_reason: Not applicable for minors or very young individuals.
      future_accepted_values:
      - D
      - W
      - P
      - U
      data_type:
        current_data_type: VARCHAR
  - name: race
    description: Racial background of the patient
    tests:
    - not_null
    - accepted_values:
        values:
        - white
        - asian
        - black
    cocoon_meta:
      future_accepted_values:
      - White
      - Asian
      - Black
      - Hispanic or Latino
      - American Indian or Alaska Native
      - Native Hawaiian or Other Pacific Islander
      - Two or More Races
      - Other
      - Unknown
      - Prefer not to say
      data_type:
        current_data_type: VARCHAR
  - name: ethnicity
    description: Ethnic background of the patient
    tests:
    - not_null
    - accepted_values:
        values:
        - nonhispanic
        - hispanic
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: gender
    description: Gender of the patient
    tests:
    - not_null
    - accepted_values:
        values:
        - M
        - F
    cocoon_meta:
      future_accepted_values:
      - O
      data_type:
        current_data_type: VARCHAR
  - name: birthplace
    description: Place of birth of the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: street_address
    description: Street address of the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: city
    description: City of residence of the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: state
    description: State of residence of the patient
    tests:
    - not_null
    - accepted_values:
        values:
        - Massachusetts
    cocoon_meta:
      future_accepted_values:
      - Alabama
      - Alaska
      - Arizona
      - Arkansas
      - California
      - Colorado
      - Connecticut
      - Delaware
      - Florida
      - Georgia
      - Hawaii
      - Idaho
      - Illinois
      - Indiana
      - Iowa
      - Kansas
      - Kentucky
      - Louisiana
      - Maine
      - Maryland
      - Michigan
      - Minnesota
      - Mississippi
      - Missouri
      - Montana
      - Nebraska
      - Nevada
      - New Hampshire
      - New Jersey
      - New Mexico
      - New York
      - North Carolina
      - North Dakota
      - Ohio
      - Oklahoma
      - Oregon
      - Pennsylvania
      - Rhode Island
      - South Carolina
      - South Dakota
      - Tennessee
      - Texas
      - Utah
      - Vermont
      - Virginia
      - Washington
      - West Virginia
      - Wisconsin
      - Wyoming
      data_type:
        current_data_type: VARCHAR
  - name: county
    description: County of residence of the patient
    tests:
    - not_null
    - accepted_values:
        values:
        - Hampden County
        - Plymouth County
        - Essex County
        - Middlesex County
        - Suffolk County
        - Worcester County
        - Bristol County
        - Barnstable County
        - Norfolk County
        - Berkshire County
        - Franklin County
    cocoon_meta:
      future_accepted_values:
      - Dukes County
      - Hampshire County
      - Nantucket County
      data_type:
        current_data_type: VARCHAR
  - name: fips_code
    description: Federal Information Processing Standards code for location
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: DECIMAL
  - name: latitude
    description: Latitude coordinate of the patient's location
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: longitude
    description: Longitude coordinate of the patient's location
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: healthcare_expenses
    description: Total healthcare expenses for the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: healthcare_coverage
    description: Total healthcare coverage for the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: annual_income
    description: Annual income of the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: birth_date
    description: Date of birth of the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DATE
  - name: death_date
    description: Date of death of the patient, if applicable
    cocoon_meta:
      missing_reason: Not applicable for living individuals.
      data_type:
        current_data_type: DATE
  - name: patient_id
    description: Unique identifier for each patient record
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column is a unique identifier for each patient. For this
        table, each row represents a distinct patient, and patient_id appears to be
        unique across rows.
      data_type:
        current_data_type: UUID
  - name: zip_code
    description: ZIP code of the patient's residence
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 00:47:43.534687+00:00
WITH 
"careplans_renamed" AS (
    -- Rename: Renaming columns
    -- Id -> care_plan_id
    -- START -> start_date
    -- STOP -> end_date
    -- PATIENT -> patient_id
    -- ENCOUNTER -> encounter_id
    -- CODE -> treatment_code
    -- DESCRIPTION -> treatment_description
    -- REASONCODE -> diagnosis_code
    -- REASONDESCRIPTION -> diagnosis_description
    SELECT 
        "Id" AS "care_plan_id",
        "START" AS "start_date",
        "STOP" AS "end_date",
        "PATIENT" AS "patient_id",
        "ENCOUNTER" AS "encounter_id",
        "CODE" AS "treatment_code",
        "DESCRIPTION" AS "treatment_description",
        "REASONCODE" AS "diagnosis_code",
        "REASONDESCRIPTION" AS "diagnosis_description"
    FROM "memory"."main"."careplans"
),

"careplans_renamed_casted" AS (
    -- Column Type Casting: 
    -- care_plan_id: from VARCHAR to UUID
    -- encounter_id: from VARCHAR to UUID
    -- end_date: from VARCHAR to DATE
    -- patient_id: from VARCHAR to UUID
    -- start_date: from VARCHAR to DATE
    -- treatment_code: from INT to VARCHAR
    SELECT
        "treatment_description",
        "diagnosis_code",
        "diagnosis_description",
        CAST("care_plan_id" AS UUID) 
        AS "care_plan_id",
        CAST("encounter_id" AS UUID) 
        AS "encounter_id",
        CAST("end_date" AS DATE) 
        AS "end_date",
        CAST("patient_id" AS UUID) 
        AS "patient_id",
        CAST("start_date" AS DATE) 
        AS "start_date",
        CAST("treatment_code" AS VARCHAR) 
        AS "treatment_code"
    FROM "careplans_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "careplans_renamed_casted"

version: 2
models:
- name: stg_careplans
  description: The table is about care plans for patients. It shows the start and
    stop dates of treatments. Each care plan is linked to a patient and an encounter.
    The table includes codes and descriptions for the treatment and reason. It relates
    patients, encounters, and specific medical interventions over time. The data covers
    various medical issues like fractures, wounds, and head injuries.
  columns:
  - name: treatment_description
    description: Text description of the treatment or procedure
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: diagnosis_code
    description: Numeric code for the reason of treatment
    cocoon_meta:
      missing_reason: Not applicable for non-diagnosis-related care plans.
      data_type:
        current_data_type: DECIMAL
  - name: diagnosis_description
    description: Text description of the reason for treatment
    cocoon_meta:
      missing_reason: Not applicable for non-diagnosis-related care plans.
      data_type:
        current_data_type: VARCHAR
  - name: care_plan_id
    description: Unique identifier for the care plan entry
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column is a unique identifier for each care plan entry.
        For this table, each row represents a distinct care plan. care_plan_id appears
        to be unique across rows, as it's designed to be a specific identifier for
        each care plan.
      data_type:
        current_data_type: UUID
  - name: encounter_id
    description: Unique identifier for the medical encounter
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: end_date
    description: End date of the care plan
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: DATE
  - name: patient_id
    description: Unique identifier for the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: start_date
    description: Start date of the care plan
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DATE
  - name: treatment_code
    description: Numeric code for the treatment or procedure
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 01:08:13.429847+00:00
WITH 
"organizations_renamed" AS (
    -- Rename: Renaming columns
    -- Id -> organization_id
    -- NAME -> facility_name
    -- ADDRESS -> street_address
    -- LAT -> latitude
    -- LON -> longitude
    -- REVENUE -> revenue
    -- UTILIZATION -> utilization_rate
    SELECT 
        "Id" AS "organization_id",
        "NAME" AS "facility_name",
        "ADDRESS" AS "street_address",
        "CITY",
        "STATE",
        "ZIP",
        "LAT" AS "latitude",
        "LON" AS "longitude",
        "PHONE",
        "REVENUE" AS "revenue",
        "UTILIZATION" AS "utilization_rate"
    FROM "memory"."main"."organizations"
),

"organizations_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- facility_name: The main problems are inconsistent use of 'INC' vs 'INC.', inconsistent spacing before 'LLC', and varying formats for company names. The correct values should use 'INC' without a period, remove extra spaces before 'LLC', and maintain consistent capitalization. Some entries also have typos or unusual abbreviations that need to be corrected.
    -- PHONE: The main problem is inconsistent formatting of phone numbers. Most numbers are either in a single string format (like '4134472000') or have parentheses for the area code (like '(978) 406-1327'). The value '413-731-6000 Or 413-731-6000' is particularly unusual as it repeats the same number and includes the word 'Or'. The correct values should follow the most common format, which appears to be a single string of digits without any separators.
    SELECT
        "organization_id",
        CASE
            WHEN "facility_name" = 'STEWARD GOOD SAMARITAN MEDICAL CENTER  INC.' THEN 'STEWARD GOOD SAMARITAN MEDICAL CENTER INC'
            WHEN "facility_name" = '135 BENTON DRIVE OPERATING COMPANY  LLC' THEN '135 BENTON DRIVE OPERATING COMPANY LLC'
            WHEN "facility_name" = '265 ESSEX STREET OPERATING COMPANY  LLC' THEN '265 ESSEX STREET OPERATING COMPANY LLC'
            WHEN "facility_name" = '4499 ACUSHNET AVENUE OPERATING COMPANY  LLC' THEN '4499 ACUSHNET AVENUE OPERATING COMPANY LLC'
            WHEN "facility_name" = 'ABBOTT HOME HEALTH CARE  INC' THEN 'ABBOTT HOME HEALTH CARE INC'
            WHEN "facility_name" = 'ARBOUR HOSPITAL  THE' THEN 'THE ARBOUR HOSPITAL'
            WHEN "facility_name" = 'ASSOCIATED PHYSICIANS OF HARVARD MEDICAL FACULTY PHYSICIANS AT BETH IS' THEN 'ASSOCIATED PHYSICIANS OF HARVARD MEDICAL FACULTY PHYSICIANS AT BETH ISRAEL'
            WHEN "facility_name" = 'BAYADA HOME HEALTH CARE  INC' THEN 'BAYADA HOME HEALTH CARE INC'
            WHEN "facility_name" = 'BEAR MT EAST LONGMEADOW LLC' THEN 'BEAR MOUNTAIN EAST LONGMEADOW LLC'
            WHEN "facility_name" = 'BEAR MT SPRINGFIELD LLC' THEN 'BEAR MOUNTAIN SPRINGFIELD LLC'
            WHEN "facility_name" = 'BERKSHIRE MEDICAL CENTER INC - 1' THEN 'BERKSHIRE MEDICAL CENTER INC'
            WHEN "facility_name" = 'BL HEALTHCARE INC DELEWARE' THEN 'BL HEALTHCARE INC DELAWARE'
            WHEN "facility_name" = 'BOSTON HOME  INC (THE)' THEN 'THE BOSTON HOME INC'
            WHEN "facility_name" = 'BOSTON MEDICAL CENTER CORPORATION-' THEN 'BOSTON MEDICAL CENTER CORPORATION'
            WHEN "facility_name" = 'BROCKTON HOSPITAL  INC.' THEN 'BROCKTON HOSPITAL INC'
            WHEN "facility_name" = 'CARECENTRAL URGENT CARE MEDICAL GROUP PC' THEN 'CARECENTRAL URGENT CARE MEDICAL GROUP P.C.'
            WHEN "facility_name" = 'CAREWELL URGENT CARE CENTERS OF MA  PC' THEN 'CAREWELL URGENT CARE CENTERS OF MA P.C.'
            WHEN "facility_name" = 'CARING HEALTH CENTER  INC' THEN 'CARING HEALTH CENTER INC'
            WHEN "facility_name" = 'CHARLES RIVER COMMUNITY HEALTH  INC' THEN 'CHARLES RIVER COMMUNITY HEALTH INC'
            WHEN "facility_name" = 'CHELMSFORD FAMILY PRACTICE  PC' THEN 'CHELMSFORD FAMILY PRACTICE P.C.'
            WHEN "facility_name" = 'COMPASSIONATE CARE HOSPICE OF SOUTHEASTERN MASS LL' THEN 'COMPASSIONATE CARE HOSPICE OF SOUTHEASTERN MASS LLC'
            WHEN "facility_name" = 'CONNECTICUT RIVER INTERNISTS. LLP' THEN 'CONNECTICUT RIVER INTERNISTS LLP'
            WHEN "facility_name" = 'DIMOCK COMMUNITY HEALTH CENTER  INC' THEN 'DIMOCK COMMUNITY HEALTH CENTER INC.'
            WHEN "facility_name" = 'DYOUVILLE TRANSITIONAL CARE INC' THEN 'D''YOUVILLE TRANSITIONAL CARE INC.'
            WHEN "facility_name" = 'EDWARD M KENNEDY COMMUNITY HEALTH CENTER INC' THEN 'EDWARD M. KENNEDY COMMUNITY HEALTH CENTER INC.'
            WHEN "facility_name" = 'ENCOMPASS HEALTH BRAINTREE HOSPITAL OF BRAINTREE' THEN 'ENCOMPASS HEALTH HOSPITAL OF BRAINTREE'
            WHEN "facility_name" = 'ENCOMPASS HEALTH REHAB HOSPITAL OF WESTERN MASS' THEN 'ENCOMPASS HEALTH REHAB HOSPITAL OF WESTERN MASS.'
            WHEN "facility_name" = 'EXCEL HOME CARE SERVICES INC' THEN 'EXCEL HOME CARE SERVICES INC.'
            WHEN "facility_name" = 'FALMOUTH HOSPITAL ASSOCIATION INC' THEN 'FALMOUTH HOSPITAL ASSOCIATION INC.'
            WHEN "facility_name" = 'FRANCISCAN HOSPITAL FOR CHILDREN INC' THEN 'FRANCISCAN HOSPITAL FOR CHILDREN INC.'
            WHEN "facility_name" = 'Fitchburg Outpatient Clinic' THEN 'FITCHBURG OUTPATIENT CLINIC'
            WHEN "facility_name" = 'HOLYOKE HEALTH CENTER INC' THEN 'HOLYOKE HEALTH CENTER INC.'
            WHEN "facility_name" = 'HOLYOKE MEDICAL CENTER INC' THEN 'HOLYOKE MEDICAL CENTER INC.'
            WHEN "facility_name" = 'LAHEY HOSPITAL & MEDICAL CENTER  BURLINGTON' THEN 'LAHEY HOSPITAL & MEDICAL CENTER BURLINGTON'
            WHEN "facility_name" = 'LIGHTHOUSE OF REVERE  INC.' THEN 'LIGHTHOUSE OF REVERE INC.'
            WHEN "facility_name" = 'LUTHERAN HOME OF JAMAICA PLAIN INC' THEN 'LUTHERAN HOME OF JAMAICA PLAIN INC.'
            WHEN "facility_name" = 'MELROSEWAKEFIELD HEALTHCARE  INC' THEN 'MELROSEWAKEFIELD HEALTHCARE, INC.'
            WHEN "facility_name" = 'MIDDLESEX INTERNAL MEDICINE ASSOCIATES  INC' THEN 'MIDDLESEX INTERNAL MEDICINE ASSOCIATES, INC.'
            WHEN "facility_name" = 'MORTON HOSPITAL  A STEWARD FAMILY HOSPITAL  INC.' THEN 'MORTON HOSPITAL, A STEWARD FAMILY HOSPITAL, INC.'
            WHEN "facility_name" = 'NEW BEDFORD INTERNAL MEDICINE & GERIATRICS  LLC' THEN 'NEW BEDFORD INTERNAL MEDICINE & GERIATRICS, LLC'
            WHEN "facility_name" = 'NEW ENGLAND FAMILY HEALTH LLC' THEN 'NEW ENGLAND FAMILY HEALTH, LLC'
            WHEN "facility_name" = 'NEW ENGLAND PROFESSIONAL HOME HEALTH CARE LLC' THEN 'NEW ENGLAND PROFESSIONAL HOME HEALTH CARE, LLC'
            WHEN "facility_name" = 'NORTH ADAMS COMMONS NURSING & REHABILITATION CENTE' THEN 'NORTH ADAMS COMMONS NURSING & REHABILITATION CENTER'
            WHEN "facility_name" = 'NORTH RIVER HOSPICE LLC' THEN 'NORTH RIVER HOSPICE, LLC'
            WHEN "facility_name" = 'NORTH SHORE MEDICAL CENTER INC' THEN 'NORTH SHORE MEDICAL CENTER, INC.'
            WHEN "facility_name" = 'OAKS  THE' THEN 'THE OAKS'
            WHEN "facility_name" = 'PALM SKILLED  NRSING CR & CTR FOR REHAB EXCELLENCE' THEN 'PALM SKILLED NURSING CARE & CENTER FOR REHAB EXCELLENCE'
            WHEN "facility_name" = 'PRIMARY & PREVENTIVE CARE  INC.' THEN 'PRIMARY & PREVENTIVE CARE, INC.'
            WHEN "facility_name" = 'Plymouth Outreach Clinic' THEN 'PLYMOUTH OUTREACH CLINIC'
            WHEN "facility_name" = 'QUINCY HEALTH AND REHABILITATION CENTER LLC' THEN 'QUINCY HEALTH AND REHABILITATION CENTER, LLC'
            WHEN "facility_name" = 'RIVER''S EDGE PRIMARY CARE LLC' THEN 'RIVER''S EDGE PRIMARY CARE, LLC'
            WHEN "facility_name" = 'ROYAL NORWELL NURSING & REHABILITATION CENTER LLC' THEN 'ROYAL NORWELL NURSING & REHABILITATION CENTER, LLC'
            WHEN "facility_name" = 'SEASONS HOSPICE & PALLIATIVE CARE OF MASS LLC' THEN 'SEASONS HOSPICE & PALLIATIVE CARE OF MASS, LLC'
            WHEN "facility_name" = 'SOUTH SHORE MEDICAL INVESTORS LLC' THEN 'SOUTH SHORE MEDICAL INVESTORS, LLC'
            WHEN "facility_name" = 'SOUTH SHORE PRIMARY AND URGENT CARE LLC' THEN 'SOUTH SHORE PRIMARY AND URGENT CARE, LLC'
            WHEN "facility_name" = 'SOUTHWOOD AT NORWELL NURSING CTR' THEN 'SOUTHWOOD AT NORWELL NURSING CENTER'
            WHEN "facility_name" = 'SUN MEDICAL CLINIC  PC' THEN 'SUN MEDICAL CLINIC, PC'
            WHEN "facility_name" = 'Springfield Outpatient Clinic' THEN 'SPRINGFIELD OUTPATIENT CLINIC'
            WHEN "facility_name" = 'TAUNTON MEDICAL CENTER  PC' THEN 'TAUNTON MEDICAL CENTER, PC'
            WHEN "facility_name" = 'TUFTS MEDICAL CENTER  INC' THEN 'TUFTS MEDICAL CENTER INC'
            WHEN "facility_name" = 'URGENT CARE MEDICAL ASSOCIATES  LLC' THEN 'URGENT CARE MEDICAL ASSOCIATES LLC'
            WHEN "facility_name" = 'URGENT CARE SPECIALISTS  P.C.' THEN 'URGENT CARE SPECIALISTS PC'
            WHEN "facility_name" = 'VIGILANT FAMILY HEALTH CLINIC  INC.' THEN 'VIGILANT FAMILY HEALTH CLINIC INC'
            WHEN "facility_name" = 'WESTBOROUGH BEHAVIORAL HEALTHCARE HOSPITAL  LLC' THEN 'WESTBOROUGH BEHAVIORAL HEALTHCARE HOSPITAL LLC'
            WHEN "facility_name" = 'WORCESTER INTERNAL MEDICINE  INC.' THEN 'WORCESTER INTERNAL MEDICINE INC'
            ELSE "facility_name"
        END AS "facility_name",
        "street_address",
        "CITY",
        "STATE",
        "ZIP",
        "latitude",
        "longitude",
        CASE
            WHEN "PHONE" = '(978) 406-1327' THEN '9784061327'
            WHEN "PHONE" = '413-731-6000 Or 413-731-6000' THEN '4137316000'
            WHEN "PHONE" = '978-342-9781 Or 978-342-9781' THEN '9783429781'
            WHEN "PHONE" = '800-865-3384' THEN '8008653384'
            ELSE "PHONE"
        END AS "PHONE",
        "revenue",
        "utilization_rate"
    FROM "organizations_renamed"
),

"organizations_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- ZIP: from INT to VARCHAR
    -- organization_id: from VARCHAR to UUID
    SELECT
        "facility_name",
        "street_address",
        "CITY",
        "STATE",
        "latitude",
        "longitude",
        "PHONE",
        "revenue",
        "utilization_rate",
        CAST("ZIP" AS VARCHAR) 
        AS "ZIP",
        CAST("organization_id" AS UUID) 
        AS "organization_id"
    FROM "organizations_renamed_cleaned"
)

-- COCOON BLOCK END
SELECT *
FROM "organizations_renamed_cleaned_casted"

version: 2
models:
- name: stg_organizations
  description: The table is about healthcare organizations. It includes details such
    as unique ID, name, address, city, state, ZIP code, latitude, longitude, phone
    number, revenue, and utilization. The organizations appear to be various healthcare
    facilities like clinics, hospitals, and urgent care centers. They are located
    in Massachusetts. The table provides contact information and some operational
    data for each organization.
  columns:
  - name: facility_name
    description: Name of the healthcare facility
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: street_address
    description: Street address of the facility
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: ^\d+\s+[A-Z\s]+(?:ST|AVE|RD|DRIVE|STREET|AVENUE|ROAD)$
        summary: Basic street address with number and street name
      - regex: ^\d+\s+[A-Z\s]+(?:ST|AVE|RD|DRIVE|STREET|AVENUE|ROAD)(?:\s+(?:SUITE|STE)\s+\d+)?$
        summary: Address with suite or unit number
      - regex: ^[A-Z0-9\s]+$
        summary: General pattern allowing for various formats
      - regex: \d+\s+[A-Za-z\s&''\.]+\s+(Road|ST\.|Street|ROAD|PL)
        summary: Street addresses with number, name, and type (most strict)
      - regex: \d+\s+[A-Za-z\s&''\.]+
        summary: Street addresses with number and name (less strict)
      data_type:
        current_data_type: VARCHAR
  - name: CITY
    description: City where the facility is located
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: STATE
    description: State abbreviation for the facility's location
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: latitude
    description: Latitude coordinate of the facility
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: longitude
    description: Longitude coordinate of the facility
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: PHONE
    description: Contact phone number for the facility
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: \d{10}
        summary: 10-digit phone numbers without separators
      data_type:
        current_data_type: VARCHAR
  - name: revenue
    description: Revenue information for the facility
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: utilization_rate
    description: Measure of facility usage or capacity
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: ZIP
    description: ZIP code of the facility's location
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: organization_id
    description: Unique identifier for the healthcare organization
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column represents a unique identifier for the healthcare
        organization. For this table, each row is for a unique healthcare facility.
        The organization_id appears to be a UUID, which is designed to be unique for
        each entry.
      data_type:
        current_data_type: UUID
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 01:01:52.568671+00:00
WITH 
"immunizations_renamed" AS (
    -- Rename: Renaming columns
    -- DATE_ -> immunization_datetime
    -- PATIENT -> patient_id
    -- ENCOUNTER -> encounter_id
    -- CODE -> vaccine_code
    -- DESCRIPTION -> vaccine_description
    -- BASE_COST -> base_cost
    SELECT 
        "DATE_" AS "immunization_datetime",
        "PATIENT" AS "patient_id",
        "ENCOUNTER" AS "encounter_id",
        "CODE" AS "vaccine_code",
        "DESCRIPTION" AS "vaccine_description",
        "BASE_COST" AS "base_cost"
    FROM "memory"."main"."immunizations"
),

"immunizations_renamed_casted" AS (
    -- Column Type Casting: 
    -- encounter_id: from VARCHAR to UUID
    -- immunization_datetime: from VARCHAR to TIMESTAMP
    -- patient_id: from VARCHAR to UUID
    SELECT
        "vaccine_code",
        "vaccine_description",
        "base_cost",
        CAST("encounter_id" AS UUID) 
        AS "encounter_id",
        CAST("immunization_datetime" AS TIMESTAMP) 
        AS "immunization_datetime",
        CAST("patient_id" AS UUID) 
        AS "patient_id"
    FROM "immunizations_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "immunizations_renamed_casted"

version: 2
models:
- name: stg_immunizations
  description: The table is about immunizations given to patients. It includes details
    such as the date of immunization, patient ID, encounter ID, vaccine code, vaccine
    description, and base cost. Each row represents a single immunization event. Multiple
    immunizations can be given to the same patient on the same date. All immunizations
    in the sample have the same base cost of $136.00.
  columns:
  - name: vaccine_code
    description: Numeric code representing the vaccine type
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: vaccine_description
    description: Text description of the vaccine administered
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: base_cost
    description: Base cost of the immunization in dollars
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: encounter_id
    description: Unique identifier for the healthcare encounter
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: immunization_datetime
    description: Date and time of immunization
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: patient_id
    description: Unique identifier for the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 01:13:46.432396+00:00
WITH 
"payer_transitions_renamed" AS (
    -- Rename: Renaming columns
    -- PATIENT -> patient_id
    -- MEMBERID -> member_id
    -- START_DATE -> coverage_start_date
    -- END_DATE -> coverage_end_date
    -- PAYER -> primary_payer_id
    -- SECONDARY_PAYER -> secondary_payer_id
    -- PLAN_OWNERSHIP -> plan_ownership
    -- OWNER_NAME -> owner_name
    SELECT 
        "PATIENT" AS "patient_id",
        "MEMBERID" AS "member_id",
        "START_DATE" AS "coverage_start_date",
        "END_DATE" AS "coverage_end_date",
        "PAYER" AS "primary_payer_id",
        "SECONDARY_PAYER" AS "secondary_payer_id",
        "PLAN_OWNERSHIP" AS "plan_ownership",
        "OWNER_NAME" AS "owner_name"
    FROM "memory"."main"."payer_transitions"
),

"payer_transitions_renamed_casted" AS (
    -- Column Type Casting: 
    -- coverage_end_date: from VARCHAR to TIMESTAMP
    -- coverage_start_date: from VARCHAR to TIMESTAMP
    -- member_id: from VARCHAR to UUID
    -- patient_id: from VARCHAR to UUID
    -- primary_payer_id: from VARCHAR to UUID
    -- secondary_payer_id: from VARCHAR to UUID
    SELECT
        "plan_ownership",
        "owner_name",
        CASE
            WHEN regexp_full_match("coverage_end_date", '\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:04Z') THEN CAST("coverage_end_date" AS TIMESTAMP)
            WHEN regexp_full_match("coverage_end_date", '\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:07Z') THEN CAST("coverage_end_date" AS TIMESTAMP)
            WHEN regexp_full_match("coverage_end_date", '\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}Z') THEN CAST("coverage_end_date" AS TIMESTAMP)
        END 
        AS "coverage_end_date",
        CASE
            WHEN regexp_full_match("coverage_start_date", '\d{4}-\d{2}-\d{2}T13:42:04Z') THEN CAST("coverage_start_date" AS TIMESTAMP)
            WHEN regexp_full_match("coverage_start_date", '\d{4}-\d{2}-\d{2}T03:41:07Z') THEN CAST("coverage_start_date" AS TIMESTAMP)
            WHEN regexp_full_match("coverage_start_date", '\d{4}-\d{2}-\d{2}T19:43:25Z') THEN CAST("coverage_start_date" AS TIMESTAMP)
            WHEN regexp_full_match("coverage_start_date", '\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}Z') THEN CAST("coverage_start_date" AS TIMESTAMP)
        END 
        AS "coverage_start_date",
        CAST("member_id" AS UUID) 
        AS "member_id",
        CAST("patient_id" AS UUID) 
        AS "patient_id",
        CAST("primary_payer_id" AS UUID) 
        AS "primary_payer_id",
        CAST("secondary_payer_id" AS UUID) 
        AS "secondary_payer_id"
    FROM "payer_transitions_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "payer_transitions_renamed_casted"

version: 2
models:
- name: stg_payer_transitions
  description: The table represents payer transitions for patients. It shows insurance
    coverage periods for patients. Each row contains a patient ID, member ID, start
    and end dates of coverage, primary payer, secondary payer (if any), plan ownership,
    and owner name. The table tracks changes in insurance coverage over time for individual
    patients.
  columns:
  - name: plan_ownership
    description: Type of ownership for the insurance plan
    tests:
    - accepted_values:
        values:
        - Self
        - Guardian
        - Spouse
    cocoon_meta:
      missing_reason: Unknown
      future_accepted_values:
      - Parent
      - Child
      - Sibling
      - Domestic Partner
      - Other Family Member
      - Employer
      - Trust
      - Other
      data_type:
        current_data_type: VARCHAR
  - name: owner_name
    description: Name of the insurance plan owner
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: coverage_end_date
    description: Ending date of insurance coverage period
    tests:
    - not_null
    cocoon_meta:
      unusual_values: 'Two different time formats are used: 13:42:04Z for most dates
        and 03:41:07Z for two December dates.'
      data_type:
        current_data_type: TIMESTAMP
  - name: coverage_start_date
    description: Beginning date of insurance coverage period
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: member_id
    description: Unique identifier for the insurance member
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: UUID
  - name: patient_id
    description: Unique identifier for the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: primary_payer_id
    description: Primary insurance payer identifier
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: secondary_payer_id
    description: Secondary insurance payer identifier, if applicable
    cocoon_meta:
      missing_reason: Not all patients have a secondary insurance plan.
      data_type:
        current_data_type: UUID
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 01:03:31.386317+00:00
WITH 
"medications_renamed" AS (
    -- Rename: Renaming columns
    -- START -> order_start_datetime
    -- STOP -> order_end_datetime
    -- PATIENT -> patient_id
    -- PAYER -> payer_id
    -- ENCOUNTER -> encounter_id
    -- CODE -> medication_code
    -- DESCRIPTION -> medication_description
    -- BASE_COST -> base_cost
    -- PAYER_COVERAGE -> payer_coverage_amount
    -- DISPENSES -> dispense_count
    -- TOTALCOST -> total_cost
    -- REASONCODE -> reason_code
    -- REASONDESCRIPTION -> reason_description
    SELECT 
        "START" AS "order_start_datetime",
        "STOP" AS "order_end_datetime",
        "PATIENT" AS "patient_id",
        "PAYER" AS "payer_id",
        "ENCOUNTER" AS "encounter_id",
        "CODE" AS "medication_code",
        "DESCRIPTION" AS "medication_description",
        "BASE_COST" AS "base_cost",
        "PAYER_COVERAGE" AS "payer_coverage_amount",
        "DISPENSES" AS "dispense_count",
        "TOTALCOST" AS "total_cost",
        "REASONCODE" AS "reason_code",
        "REASONDESCRIPTION" AS "reason_description"
    FROM "memory"."main"."medications"
),

"medications_renamed_casted" AS (
    -- Column Type Casting: 
    -- encounter_id: from VARCHAR to UUID
    -- medication_code: from INT to VARCHAR
    -- order_end_datetime: from VARCHAR to TIMESTAMP
    -- order_start_datetime: from VARCHAR to TIMESTAMP
    -- patient_id: from VARCHAR to UUID
    -- payer_id: from VARCHAR to UUID
    -- reason_code: from DECIMAL to VARCHAR
    SELECT
        "medication_description",
        "base_cost",
        "payer_coverage_amount",
        "dispense_count",
        "total_cost",
        "reason_description",
        CAST("encounter_id" AS UUID) 
        AS "encounter_id",
        CAST("medication_code" AS VARCHAR) 
        AS "medication_code",
        CAST("order_end_datetime" AS TIMESTAMP) 
        AS "order_end_datetime",
        CAST("order_start_datetime" AS TIMESTAMP) 
        AS "order_start_datetime",
        CAST("patient_id" AS UUID) 
        AS "patient_id",
        CAST("payer_id" AS UUID) 
        AS "payer_id",
        CAST("reason_code" AS VARCHAR) 
        AS "reason_code"
    FROM "medications_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "medications_renamed_casted"

version: 2
models:
- name: stg_medications
  description: The table is about medication orders. It includes patient IDs, payer
    information, encounter IDs, and medication details. Each row represents a medication
    prescription with its start date, code, description, cost, coverage, and quantity
    dispensed. The table tracks both base cost and total cost for each medication.
    Some entries have reason codes and descriptions, though these are often empty
    in the sample data.
  columns:
  - name: medication_description
    description: Description of the medication
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: ^[A-Za-z\s-]+\s\d+(\.\d+)?\s(MG|UNT|ACTUAT)(/[A-Z]+)?\s[A-Za-z\s]+(\[[\w\s]+\])?$
        summary: Medication name followed by dosage (in MG, UNT, or ACTUAT) and form
          (e.g., Oral Tablet, Injection)
      - regex: ^\d+\sHR\s[A-Za-z\s-]+\s\d+(\.\d+)?\s(MG|UNT|ACTUAT)(/[A-Z]+)?\s[A-Za-z\s]+(\[[\w\s]+\])?$
        summary: Medication with time duration (e.g., 24 HR) at the beginning
      - regex: ^\d+\sML\s[A-Za-z\s-]+\s\d+(\.\d+)?\s(MG|UNT|ACTUAT)(/[A-Z]+)?\s[A-Za-z\s]+(\[[\w\s]+\])?$
        summary: Medication with volume (e.g., 1 ML) at the beginning
      - regex: ^[A-Za-z]+\s\d+\sDay\sPack$
        summary: Birth control packs (e.g., 28 Day Pack)
      - regex: ^.+\s\d+(\.\d+)?\s(MG|UNT|ACTUAT|ML)(/[A-Z]+)?.*$
        summary: Looser pattern to catch most entries
      - regex: ^[A-Za-z]+\s\d+\.\d+/\d+\.\d+\sMG\sper\s\d+HR\s\d+\sDay\s[A-Za-z]+\s[A-Za-z]+$
        summary: Medication name followed by dosage in MG and administration method
      - regex: ^[A-Za-z]+\s\d+\.\d+/\d+\s\d+\sDay\sPack$
        summary: Medication name followed by dosage and pack information
      - regex: ^[A-Za-z]+/[A-Za-z]+$
        summary: Medication names separated by forward slash
      data_type:
        current_data_type: VARCHAR
  - name: base_cost
    description: Base cost of the medication
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: payer_coverage_amount
    description: Amount covered by the payer
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: dispense_count
    description: Number of times the medication was dispensed
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: total_cost
    description: Total cost of the medication
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: reason_description
    description: Description of the reason for prescription
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: encounter_id
    description: Unique identifier for the medical encounter
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: medication_code
    description: Medication code
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: order_end_datetime
    description: End date and time of medication order
    cocoon_meta:
      missing_reason: Ongoing medications don't have an end date.
      data_type:
        current_data_type: TIMESTAMP
  - name: order_start_datetime
    description: Start date and time of medication order
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: patient_id
    description: Unique identifier for the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: payer_id
    description: Unique identifier for the insurance payer
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: reason_code
    description: Code for the reason of prescription
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 01:01:06.123995+00:00
WITH 
"imaging_studies_renamed" AS (
    -- Rename: Renaming columns
    -- Id -> study_id
    -- DATE_ -> study_datetime
    -- PATIENT -> patient_id
    -- ENCOUNTER -> encounter_id
    -- SERIES_UID -> series_uid
    -- BODYSITE_CODE -> bodysite_code
    -- BODYSITE_DESCRIPTION -> bodysite_description
    -- MODALITY_CODE -> modality_code
    -- MODALITY_DESCRIPTION -> modality_description
    -- INSTANCE_UID -> instance_uid
    -- SOP_CODE -> sop_code
    -- SOP_DESCRIPTION -> sop_description
    -- PROCEDURE_CODE -> procedure_code
    SELECT 
        "Id" AS "study_id",
        "DATE_" AS "study_datetime",
        "PATIENT" AS "patient_id",
        "ENCOUNTER" AS "encounter_id",
        "SERIES_UID" AS "series_uid",
        "BODYSITE_CODE" AS "bodysite_code",
        "BODYSITE_DESCRIPTION" AS "bodysite_description",
        "MODALITY_CODE" AS "modality_code",
        "MODALITY_DESCRIPTION" AS "modality_description",
        "INSTANCE_UID" AS "instance_uid",
        "SOP_CODE" AS "sop_code",
        "SOP_DESCRIPTION" AS "sop_description",
        "PROCEDURE_CODE" AS "procedure_code"
    FROM "memory"."main"."imaging_studies"
),

"imaging_studies_renamed_casted" AS (
    -- Column Type Casting: 
    -- bodysite_code: from INT to VARCHAR
    -- encounter_id: from VARCHAR to UUID
    -- patient_id: from VARCHAR to UUID
    -- procedure_code: from INT to VARCHAR
    -- study_datetime: from VARCHAR to TIMESTAMP
    -- study_id: from VARCHAR to UUID
    SELECT
        "series_uid",
        "bodysite_description",
        "modality_code",
        "modality_description",
        "instance_uid",
        "sop_code",
        "sop_description",
        CAST("bodysite_code" AS VARCHAR) 
        AS "bodysite_code",
        CAST("encounter_id" AS UUID) 
        AS "encounter_id",
        CAST("patient_id" AS UUID) 
        AS "patient_id",
        CAST("procedure_code" AS VARCHAR) 
        AS "procedure_code",
        CAST("study_datetime" AS TIMESTAMP) 
        AS "study_datetime",
        CAST("study_id" AS UUID) 
        AS "study_id"
    FROM "imaging_studies_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "imaging_studies_renamed_casted"

version: 2
models:
- name: stg_imaging_studies
  description: The table is about imaging studies. It contains details of medical
    imaging procedures. Each row represents a unique imaging study. It includes the
    study ID, date, patient ID, encounter ID, body site, imaging modality, and procedure
    codes. The table also contains technical details like series UID, instance UID,
    and SOP codes. This data likely comes from a healthcare system's radiology department.
  columns:
  - name: series_uid
    description: Unique identifier for the image series
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column represents a unique identifier for the image series.
        For this table, each row is for a unique imaging study. The series_uid appears
        to be unique across rows in the sample data.
      data_type:
        current_data_type: VARCHAR
  - name: bodysite_description
    description: Description of the body part imaged
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: modality_code
    description: Code representing the imaging modality
    tests:
    - not_null
    - accepted_values:
        values:
        - CR
        - DX
        - US
        - CT
    cocoon_meta:
      future_accepted_values:
      - MR
      - NM
      - PT
      - XA
      - RF
      - MG
      - IO
      - PX
      - GM
      - SM
      - XC
      - ES
      - ECG
      - HD
      - SR
      - OT
      - BI
      - CD
      - DD
      - LS
      - OPT
      data_type:
        current_data_type: VARCHAR
  - name: modality_description
    description: Description of the imaging modality
    tests:
    - not_null
    - accepted_values:
        values:
        - Computed Radiography
        - Digital Radiography
        - Ultrasound
        - Computed Tomography
    cocoon_meta:
      future_accepted_values:
      - Magnetic Resonance Imaging
      - Nuclear Medicine
      - Positron Emission Tomography
      - Fluoroscopy
      - Mammography
      - Angiography
      - Dual-Energy X-ray Absorptiometry
      - Optical Coherence Tomography
      data_type:
        current_data_type: VARCHAR
  - name: instance_uid
    description: Unique identifier for the image instance
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: sop_code
    description: Service-Object Pair code for DICOM standard
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: sop_description
    description: Description of the SOP code
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: bodysite_code
    description: Code representing the body part imaged
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: encounter_id
    description: Unique identifier for the healthcare encounter
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: patient_id
    description: Unique identifier for the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: procedure_code
    description: Code representing the imaging procedure performed
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: study_datetime
    description: Date and time of the imaging study
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: study_id
    description: Unique identifier for the imaging study
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column represents a unique identifier for the imaging study.
        For this table, each row is for a unique imaging study. The study_id appears
        to be unique across rows in the sample data.
      data_type:
        current_data_type: UUID
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 00:56:59.986257+00:00
WITH 
"devices_renamed" AS (
    -- Rename: Renaming columns
    -- START -> usage_start_date
    -- STOP -> usage_end_date
    -- PATIENT -> patient_id
    -- ENCOUNTER -> encounter_id
    -- CODE -> device_code
    -- DESCRIPTION -> device_description
    -- UDI -> device_udi
    SELECT 
        "START" AS "usage_start_date",
        "STOP" AS "usage_end_date",
        "PATIENT" AS "patient_id",
        "ENCOUNTER" AS "encounter_id",
        "CODE" AS "device_code",
        "DESCRIPTION" AS "device_description",
        "UDI" AS "device_udi"
    FROM "memory"."main"."devices"
),

"devices_renamed_casted" AS (
    -- Column Type Casting: 
    -- device_code: from INT to VARCHAR
    -- encounter_id: from VARCHAR to UUID
    -- patient_id: from VARCHAR to UUID
    -- usage_end_date: from VARCHAR to TIMESTAMP
    -- usage_start_date: from VARCHAR to TIMESTAMP
    SELECT
        "device_description",
        "device_udi",
        CAST("device_code" AS VARCHAR) 
        AS "device_code",
        CAST("encounter_id" AS UUID) 
        AS "encounter_id",
        CAST("patient_id" AS UUID) 
        AS "patient_id",
        CAST("usage_end_date" AS TIMESTAMP) 
        AS "usage_end_date",
        CAST("usage_start_date" AS TIMESTAMP) 
        AS "usage_start_date"
    FROM "devices_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "devices_renamed_casted"

version: 2
models:
- name: stg_devices
  description: The table is about medical devices. It contains start and stop dates
    for device usage. Each row represents a device associated with a patient and encounter.
    The table includes device codes, descriptions, and unique device identifiers (UDI).
    Devices listed are home nebulizers and manual wheelchairs. The UDI contains encoded
    information about the device.
  columns:
  - name: device_description
    description: Text description of the device
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: device_udi
    description: Unique Device Identifier with encoded information
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column contains the Unique Device Identifier (UDI) for each
        medical device. For this table, each row represents a unique device usage
        instance associated with a patient and encounter. The device_udi appears to
        be unique across rows, as it contains encoded information specific to each
        device instance.
      patterns:
      - regex: \(01\)\d+\(11\)\d+\(17\)\d+\(10\)\d+\(21\)\d+
        summary: 'UDI code with 5 segments: (01), (11), (17), (10), and (21)'
      data_type:
        current_data_type: VARCHAR
  - name: device_code
    description: Numeric code identifying the device type
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: encounter_id
    description: Unique identifier for the medical encounter
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: patient_id
    description: Unique identifier for the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: usage_end_date
    description: End date of device usage
    cocoon_meta:
      missing_reason: Device usage might be ongoing, without an end date.
      data_type:
        current_data_type: TIMESTAMP
  - name: usage_start_date
    description: Start date of device usage
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 01:06:20.318190+00:00
WITH 
"observations_renamed" AS (
    -- Rename: Renaming columns
    -- DATE_ -> observation_datetime
    -- PATIENT -> patient_id
    -- ENCOUNTER -> encounter_id
    -- CODE -> observation_code
    -- VALUE_ -> observation_value
    -- TYPE -> data_type
    SELECT 
        "DATE_" AS "observation_datetime",
        "PATIENT" AS "patient_id",
        "ENCOUNTER" AS "encounter_id",
        "CATEGORY",
        "CODE" AS "observation_code",
        "DESCRIPTION",
        "VALUE_" AS "observation_value",
        "UNITS",
        "TYPE" AS "data_type"
    FROM "memory"."main"."observations"
),

"observations_renamed_dedup" AS (
    -- Deduplication: Removed 556 duplicated rows
    SELECT DISTINCT *
    FROM "observations_renamed"
),

"observations_renamed_dedup_cleaned" AS (
    -- Clean unusual string values: 
    -- DESCRIPTION: The problem is inconsistent capitalization and punctuation in question-format values, as well as unexplained abbreviations. The correct values should have consistent capitalization (sentence case) and punctuation (question marks for questions), and abbreviated terms should be expanded for clarity.
    -- observation_value: '0.0' and 'No' likely represent the same concept. '1.0' and 'Yes' might also be redundant representations.
    -- UNITS: The problem is that some values in the UNITS column are not standard unit abbreviations and lack specificity. The unusual values are '{score}', '{nominal}', 'a', '{#}', '{INR}', '{presence}', '{SG}', and '{T-score}'. These are not standardized units of measurement but rather descriptors or placeholders. Additionally, some units have inconsistent formatting, such as '[pH]' vs 'pH', and '[degF]' which should be standardized. The correct values should be standardized units of measurement or left empty if they represent a score or nominal value without a specific unit.
    SELECT
        "observation_datetime",
        "patient_id",
        "encounter_id",
        "CATEGORY",
        "observation_code",
        CASE
            WHEN "DESCRIPTION" = 'At any point in the past 2 years  has season or migrant farm work been your or your family''s main source of income?' THEN 'At any point in the past 2 years, has season or migrant farm work been your or your family''s main source of income?'
            WHEN "DESCRIPTION" = 'During the past year  what was the total combined income for you and the family members you live with? This information will help us determine if you are eligible for any benefits.' THEN 'During the past year, what was the total combined income for you and the family members you live with? This information will help us determine if you are eligible for any benefits.'
            WHEN "DESCRIPTION" = 'Has lack of transportation kept you from medical appointments  meetings  work  or from getting things needed for daily living?' THEN 'Has lack of transportation kept you from medical appointments, meetings, work, or from getting things needed for daily living?'
            WHEN "DESCRIPTION" = 'How many family members  including yourself  do you currently live with?' THEN 'How many family members, including yourself, do you currently live with?'
            WHEN "DESCRIPTION" = 'How often do you see or talk to people that you care about and feel close to (For example: talking to friends on the phone  visiting friends or family  going to church or club meetings)?' THEN 'How often do you see or talk to people that you care about and feel close to? (For example: talking to friends on the phone, visiting friends or family, going to church or club meetings)'
            WHEN "DESCRIPTION" = 'In the past year  have you been afraid of your partner or ex-partner?' THEN 'In the past year, have you been afraid of your partner or ex-partner?'
            WHEN "DESCRIPTION" = 'In the past year  have you or any family members you live with been unable to get any of the following when it was really needed?' THEN 'In the past year, have you or any family members you live with been unable to get any of the following when it was really needed?'
            WHEN "DESCRIPTION" = 'In the past year  have you spent more than 2 nights in a row in a jail  prison  detention center  or juvenile correctional facility?' THEN 'In the past year, have you spent more than 2 nights in a row in a jail, prison, detention center, or juvenile correctional facility?'
            WHEN "DESCRIPTION" = 'Stress is when someone feels tense  nervous  anxious or can''t sleep at night because their mind is troubled. How stressed are you?' THEN 'Stress is when someone feels tense, nervous, anxious or can''t sleep at night because their mind is troubled. How stressed are you?'
            WHEN "DESCRIPTION" = 'DALY' THEN 'Disability-Adjusted Life Year (DALY)'
            WHEN "DESCRIPTION" = 'QALY' THEN 'Quality-Adjusted Life Year (QALY)'
            WHEN "DESCRIPTION" = 'QOLS' THEN 'Quality of Life Scale (QOLS)'
            WHEN "DESCRIPTION" = 'Total score [DAST-10]' THEN 'Total score [Drug Abuse Screening Test-10]'
            WHEN "DESCRIPTION" = 'Total score [HARK]' THEN 'Total score [Humiliation, Afraid, Rape, Kick Domestic Violence Screening Tool]'
            WHEN "DESCRIPTION" = 'Total score [AUDIT-C]' THEN 'Total score [Alcohol Use Disorders Identification Test-Concise]'
            WHEN "DESCRIPTION" = 'Patient Health Questionnaire-9: Modified for Teens total score [Reported.PHQ.Teen]' THEN 'Patient Health Questionnaire-9: Modified for Teens total score [PHQ-Teen]'
            WHEN "DESCRIPTION" = 'FEV1/FVC' THEN 'FEV1/FVC Ratio [Forced Expiratory Volume in 1 second / Forced Vital Capacity]'
            WHEN "DESCRIPTION" = 'Body temperature' THEN 'Body Temperature'
            WHEN "DESCRIPTION" = 'INR in Platelet poor plasma by Coagulation assay' THEN 'International Normalized Ratio (INR) in Platelet Poor Plasma by Coagulation Assay'
            WHEN "DESCRIPTION" = 'What number best describes how  during the past week  pain has interfered with your enjoyment of life?' THEN 'Pain Interference with Life Enjoyment (Past Week)'
            WHEN "DESCRIPTION" = 'What number best describes how  during the past week  pain has interfered with your general activity?' THEN 'Pain Interference with General Activity (Past Week)'
            WHEN "DESCRIPTION" = 'What number best describes your pain on average in the past week?' THEN 'Average Pain Level (Past Week)'
            WHEN "DESCRIPTION" = 'Head Occipital-frontal circumference' THEN 'Head Occipital-Frontal Circumference'
            WHEN "DESCRIPTION" = 'Head Occipital-frontal circumference Percentile' THEN 'Head Occipital-Frontal Circumference Percentile'
            WHEN "DESCRIPTION" = 'Weight-for-length Per age and sex' THEN 'Weight-for-Length Percentile by Age and Sex'
            WHEN "DESCRIPTION" = 'aPTT in Blood by Coagulation assay' THEN 'Activated Partial Thromboplastin Time (aPTT) in Blood by Coagulation Assay'
            WHEN "DESCRIPTION" = 'Bicarbonate [Moles/volume] in Arterial blood' THEN 'Bicarbonate [Moles/Volume] in Arterial Blood'
            WHEN "DESCRIPTION" = 'Carbon dioxide [Partial pressure] in Arterial blood' THEN 'Carbon Dioxide [Partial Pressure] in Arterial Blood'
            WHEN "DESCRIPTION" = 'Oxygen [Partial pressure] in Arterial blood' THEN 'Oxygen [Partial Pressure] in Arterial Blood'
            WHEN "DESCRIPTION" = 'pH of Arterial blood' THEN 'pH of Arterial Blood'
            WHEN "DESCRIPTION" = 'Phosphate [Mass/volume] in Serum or Plasma' THEN 'Phosphate [Mass/Volume] in Serum or Plasma'
            WHEN "DESCRIPTION" = 'Prothrombin time (PT)' THEN 'Prothrombin Time (PT)'
            WHEN "DESCRIPTION" = 'Patient Health Questionnaire 9 item (PHQ-9) total score [Reported]' THEN 'Patient Health Questionnaire-9 (PHQ-9) Total Score [Reported]'
            WHEN "DESCRIPTION" = 'Troponin I.cardiac [Mass/volume] in Serum or Plasma by High sensitivity method' THEN 'Cardiac Troponin I [Mass/Volume] in Serum or Plasma by High Sensitivity Method'
            WHEN "DESCRIPTION" = 'Drugs of abuse 5 panel - Urine by Screen method' THEN 'Drugs of Abuse 5-Panel - Urine Screen'
            WHEN "DESCRIPTION" = 'Iron binding capacity [Mass/volume] in Serum or Plasma' THEN 'Iron Binding Capacity [Mass/Volume] in Serum or Plasma'
            WHEN "DESCRIPTION" = 'Iron saturation [Mass Fraction] in Serum or Plasma' THEN 'Iron Saturation [Mass Fraction] in Serum or Plasma'
            WHEN "DESCRIPTION" = 'NT-proBNP' THEN 'N-Terminal Pro-B-Type Natriuretic Peptide (NT-proBNP)'
            WHEN "DESCRIPTION" = 'Total score [MMSE]' THEN 'Total Score [Mini-Mental State Examination (MMSE)]'
            WHEN "DESCRIPTION" = 'Natriuretic peptide.B prohormone N-Terminal [Mass/volume] in Blood by Immunoassay' THEN 'N-Terminal Pro-B-Type Natriuretic Peptide [Mass/Volume] in Blood by Immunoassay'
            WHEN "DESCRIPTION" = 'Left ventricular Ejection fraction' THEN 'Left Ventricular Ejection Fraction'
            WHEN "DESCRIPTION" = 'Operative Status Value' THEN 'Operative Status'
            WHEN "DESCRIPTION" = 'American house dust mite IgE Ab in Serum' THEN 'American House Dust Mite IgE Antibody in Serum'
            ELSE "DESCRIPTION"
        END AS "DESCRIPTION",
        "observation_value",
        CASE
            WHEN "UNITS" = '{score}' THEN NULL
            WHEN "UNITS" = 'mm[Hg]' THEN 'mmHg'
            WHEN "UNITS" = 'a' THEN '/year'
            WHEN "UNITS" = '{nominal}' THEN NULL
            WHEN "UNITS" = 'kg/m2' THEN 'kg/m²'
            WHEN "UNITS" = '{#}' THEN NULL
            WHEN "UNITS" = '10*3/uL' THEN '10³/µL'
            WHEN "UNITS" = '/a' THEN '/year'
            WHEN "UNITS" = '10*6/uL' THEN '10⁶/µL'
            WHEN "UNITS" = 'Cel' THEN '°C'
            WHEN "UNITS" = '{INR}' THEN NULL
            WHEN "UNITS" = 'mL/min/{1.73_m2}' THEN 'mL/min/1.73m²'
            WHEN "UNITS" = '[pH]' THEN 'pH'
            WHEN "UNITS" = '{presence}' THEN NULL
            WHEN "UNITS" = 'ug/dL' THEN 'µg/dL'
            WHEN "UNITS" = 'ug/L' THEN 'µg/L'
            WHEN "UNITS" = '[degF]' THEN '°F'
            WHEN "UNITS" = 'm[IU]/L' THEN 'mIU/L'
            WHEN "UNITS" = '{SG}' THEN NULL
            WHEN "UNITS" = '{T-score}' THEN NULL
            ELSE "UNITS"
        END AS "UNITS",
        "data_type"
    FROM "observations_renamed_dedup"
),

"observations_renamed_dedup_cleaned_casted" AS (
    -- Column Type Casting: 
    -- encounter_id: from VARCHAR to UUID
    -- observation_datetime: from VARCHAR to TIMESTAMP
    -- observation_value: from VARCHAR to DECIMAL
    -- patient_id: from VARCHAR to UUID
    SELECT
        "CATEGORY",
        "observation_code",
        "DESCRIPTION",
        "UNITS",
        "data_type",
        CAST("encounter_id" AS UUID) 
        AS "encounter_id",
        CAST("observation_datetime" AS TIMESTAMP) 
        AS "observation_datetime",
        "observation_value" 
        AS "observation_value",
        CAST("patient_id" AS UUID) 
        AS "patient_id"
    FROM "observations_renamed_dedup_cleaned"
)

-- COCOON BLOCK END
SELECT *
FROM "observations_renamed_dedup_cleaned_casted"

version: 2
models:
- name: stg_observations
  description: The table is about medical observations. It contains details of patient
    health measurements. Each row represents a single observation. The table includes
    the date, patient ID, encounter ID, category, measurement code, description, value,
    units, and type of measurement. Observations cover vital signs like body height,
    weight, and pain severity scores.
  columns:
  - name: CATEGORY
    description: Category of the medical observation
    tests:
    - accepted_values:
        values:
        - laboratory
        - survey
        - vital-signs
        - social-history
        - procedure
        - exam
        - therapy
    cocoon_meta:
      missing_reason: Unknown
      future_accepted_values:
      - medication
      - imaging
      - diagnosis
      - allergy
      - immunization
      - family-history
      data_type:
        current_data_type: VARCHAR
  - name: observation_code
    description: Standardized code for the medical observation
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: \d{5,7}-\d
        summary: LOINC codes (5-7 digits, hyphen, 1 digit)
      - regex: '[A-Z]{3,4}'
        summary: Short alphabetic codes (3-4 uppercase letters)
      - regex: '[A-Za-z0-9-]+'
        summary: Alphanumeric codes (mix of digits and letters)
      data_type:
        current_data_type: VARCHAR
  - name: DESCRIPTION
    description: Description of the medical observation
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: UNITS
    description: Units of measurement for the observation
    cocoon_meta:
      missing_reason: Not all vital signs or measurements require units.
      data_type:
        current_data_type: VARCHAR
  - name: data_type
    description: Type of data for the observation
    tests:
    - not_null
    - accepted_values:
        values:
        - numeric
        - text
    cocoon_meta:
      future_accepted_values:
      - categorical
      - boolean
      - date
      - datetime
      - time
      data_type:
        current_data_type: VARCHAR
  - name: encounter_id
    description: Unique identifier for the medical encounter
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: UUID
  - name: observation_datetime
    description: Date and time of the medical observation
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: observation_value
    description: Numeric value of the medical observation
    tests:
    - not_null
    cocoon_meta:
      unusual_values: '''0.0'' and ''No'' likely represent the same concept. ''1.0''
        and ''Yes'' might also be redundant representations.'
      patterns:
      - regex: ^\d+(\.\d+)?$
        summary: Floating point numbers (including whole numbers)
      - regex: ^(Yes|No)$
        summary: Yes/No values
      - regex: ^(3 to 5 times a week|5 or more times a week)$
        summary: Frequency descriptions
      - regex: ^(More than high school|High school diploma or GED)$
        summary: Education levels
      - regex: ^(Private insurance|Medicare|Medicaid)$
        summary: Insurance types
      - regex: ^(Full-time work|Part-time or temporary work)$
        summary: Employment statuses
      - regex: ^(Finding of .+|\w+.+\(finding\))$
        summary: Medical findings (starting with "Finding of" or ending with "(finding)")
      - regex: ^.+\(qualifier value\)$
        summary: Qualifiers (ending with "(qualifier value)")
      data_type:
        current_data_type: VARCHAR
        expected_data_type: DECIMAL
  - name: patient_id
    description: Unique identifier for the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 00:55:38.865223+00:00
WITH 
"conditions_renamed" AS (
    -- Rename: Renaming columns
    -- START -> condition_start_date
    -- STOP -> condition_end_date
    -- PATIENT -> patient_id
    -- ENCOUNTER -> encounter_id
    -- CODE -> condition_code
    -- DESCRIPTION -> condition_description
    SELECT 
        "START" AS "condition_start_date",
        "STOP" AS "condition_end_date",
        "PATIENT" AS "patient_id",
        "ENCOUNTER" AS "encounter_id",
        "CODE" AS "condition_code",
        "DESCRIPTION" AS "condition_description"
    FROM "memory"."main"."conditions"
),

"conditions_renamed_casted" AS (
    -- Column Type Casting: 
    -- condition_code: from INT to VARCHAR
    -- condition_end_date: from VARCHAR to DATE
    -- condition_start_date: from VARCHAR to DATE
    -- encounter_id: from VARCHAR to UUID
    -- patient_id: from VARCHAR to UUID
    SELECT
        "condition_description",
        CAST("condition_code" AS VARCHAR) 
        AS "condition_code",
        CAST("condition_end_date" AS DATE) 
        AS "condition_end_date",
        CAST("condition_start_date" AS DATE) 
        AS "condition_start_date",
        CAST("encounter_id" AS UUID) 
        AS "encounter_id",
        CAST("patient_id" AS UUID) 
        AS "patient_id"
    FROM "conditions_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "conditions_renamed_casted"

version: 2
models:
- name: stg_conditions
  description: The table is about patient conditions and employment status. It shows
    start and end dates for conditions. Each row links a patient to an encounter and
    a condition code. The conditions include high school equivalency and full-time
    employment. The same patient has multiple entries over different time periods.
    The table tracks changes in employment status for the patient across years.
  columns:
  - name: condition_description
    description: Text description of the condition or status
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: .+\s\((finding|disorder)\)$
        summary: Entries ending with "(finding)" or "(disorder)"
      - regex: ([A-Z][a-z]+\s?)+(\([a-z]+\))?
        summary: Capitalized phrases (allowing for some lowercase words)
      data_type:
        current_data_type: VARCHAR
  - name: condition_code
    description: Standardized code for the condition or status
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: condition_end_date
    description: End date of the condition or status
    cocoon_meta:
      missing_reason: Ongoing conditions or one-time events don't have end dates.
      data_type:
        current_data_type: DATE
  - name: condition_start_date
    description: Start date of the condition or status
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DATE
  - name: encounter_id
    description: Unique identifier for the medical encounter
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: patient_id
    description: Unique identifier for the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 00:54:34.603382+00:00
WITH 
"claims_transactions_renamed" AS (
    -- Rename: Renaming columns
    -- ID -> transaction_id
    -- CLAIMID -> claim_id
    -- CHARGEID -> charge_id
    -- PATIENTID -> patient_id
    -- TYPE -> transaction_type
    -- AMOUNT -> transaction_amount
    -- METHOD -> payment_method
    -- FROMDATE -> service_start_datetime
    -- TODATE -> service_end_datetime
    -- PLACEOFSERVICE -> service_location_id
    -- PROCEDURECODE -> procedure_code
    -- MODIFIER1 -> procedure_modifier_1
    -- MODIFIER2 -> procedure_modifier_2
    -- DIAGNOSISREF1 -> primary_diagnosis_ref
    -- DIAGNOSISREF2 -> secondary_diagnosis_ref
    -- DIAGNOSISREF3 -> tertiary_diagnosis_ref
    -- DIAGNOSISREF4 -> quaternary_diagnosis_ref
    -- UNITS -> service_units
    -- DEPARTMENTID -> department_id
    -- NOTES -> service_description
    -- UNITAMOUNT -> unit_cost
    -- TRANSFEROUTID -> transfer_out_id
    -- TRANSFERTYPE -> transfer_type
    -- PAYMENTS -> payment_amount
    -- ADJUSTMENTS -> adjustment_amount
    -- TRANSFERS -> transfer_amount
    -- OUTSTANDING -> outstanding_balance
    -- APPOINTMENTID -> appointment_id
    -- LINENOTE -> line_note
    -- PATIENTINSURANCEID -> patient_insurance_id
    -- FEESCHEDULEID -> fee_schedule_id
    -- PROVIDERID -> provider_id
    -- SUPERVISINGPROVIDERID -> supervising_provider_id
    SELECT 
        "ID" AS "transaction_id",
        "CLAIMID" AS "claim_id",
        "CHARGEID" AS "charge_id",
        "PATIENTID" AS "patient_id",
        "TYPE" AS "transaction_type",
        "AMOUNT" AS "transaction_amount",
        "METHOD" AS "payment_method",
        "FROMDATE" AS "service_start_datetime",
        "TODATE" AS "service_end_datetime",
        "PLACEOFSERVICE" AS "service_location_id",
        "PROCEDURECODE" AS "procedure_code",
        "MODIFIER1" AS "procedure_modifier_1",
        "MODIFIER2" AS "procedure_modifier_2",
        "DIAGNOSISREF1" AS "primary_diagnosis_ref",
        "DIAGNOSISREF2" AS "secondary_diagnosis_ref",
        "DIAGNOSISREF3" AS "tertiary_diagnosis_ref",
        "DIAGNOSISREF4" AS "quaternary_diagnosis_ref",
        "UNITS" AS "service_units",
        "DEPARTMENTID" AS "department_id",
        "NOTES" AS "service_description",
        "UNITAMOUNT" AS "unit_cost",
        "TRANSFEROUTID" AS "transfer_out_id",
        "TRANSFERTYPE" AS "transfer_type",
        "PAYMENTS" AS "payment_amount",
        "ADJUSTMENTS" AS "adjustment_amount",
        "TRANSFERS" AS "transfer_amount",
        "OUTSTANDING" AS "outstanding_balance",
        "APPOINTMENTID" AS "appointment_id",
        "LINENOTE" AS "line_note",
        "PATIENTINSURANCEID" AS "patient_insurance_id",
        "FEESCHEDULEID" AS "fee_schedule_id",
        "PROVIDERID" AS "provider_id",
        "SUPERVISINGPROVIDERID" AS "supervising_provider_id"
    FROM "memory"."main"."claims_transactions"
),

"claims_transactions_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- service_description: 'Encounter for problem' and 'Encounter for problem (procedure)' are redundant. Inconsistent use of '(procedure)' suffix.
    -- transfer_type: The problem is that the transfer_type column contains a mix of numeric and alphabetic values. The value 'p' is unusual because it's alphabetic while the others ('1' and '2') are numeric. Without more context, it's unclear what 'p' represents. However, since '1' is the most frequent value and the column seems to be intended for numeric codes, it's reasonable to map 'p' to '1'.
    SELECT
        "transaction_id",
        "claim_id",
        "charge_id",
        "patient_id",
        "transaction_type",
        "transaction_amount",
        "payment_method",
        "service_start_datetime",
        "service_end_datetime",
        "service_location_id",
        "procedure_code",
        "procedure_modifier_1",
        "procedure_modifier_2",
        "primary_diagnosis_ref",
        "secondary_diagnosis_ref",
        "tertiary_diagnosis_ref",
        "quaternary_diagnosis_ref",
        "service_units",
        "department_id",
        "service_description",
        "unit_cost",
        "transfer_out_id",
        CASE
            WHEN "transfer_type" = 'p' THEN '1'
            ELSE "transfer_type"
        END AS "transfer_type",
        "payment_amount",
        "adjustment_amount",
        "transfer_amount",
        "outstanding_balance",
        "appointment_id",
        "line_note",
        "patient_insurance_id",
        "fee_schedule_id",
        "provider_id",
        "supervising_provider_id"
    FROM "claims_transactions_renamed"
),

"claims_transactions_renamed_cleaned_null" AS (
    -- NULL Imputation: Impute Null to Disguised Missing Values
    -- service_end_datetime: ['1970-01-01T00:00:00Z']
    SELECT 
        CASE
            WHEN "service_end_datetime" = '1970-01-01T00:00:00Z' THEN NULL
            ELSE "service_end_datetime"
        END AS "service_end_datetime",
        "service_description",
        "patient_insurance_id",
        "quaternary_diagnosis_ref",
        "claim_id",
        "procedure_code",
        "outstanding_balance",
        "provider_id",
        "charge_id",
        "appointment_id",
        "primary_diagnosis_ref",
        "line_note",
        "patient_id",
        "service_units",
        "department_id",
        "transfer_type",
        "payment_amount",
        "unit_cost",
        "service_start_datetime",
        "adjustment_amount",
        "transaction_id",
        "service_location_id",
        "transaction_amount",
        "supervising_provider_id",
        "procedure_modifier_2",
        "tertiary_diagnosis_ref",
        "transaction_type",
        "secondary_diagnosis_ref",
        "payment_method",
        "procedure_modifier_1",
        "transfer_amount",
        "transfer_out_id",
        "fee_schedule_id"
    FROM "claims_transactions_renamed_cleaned"
),

"claims_transactions_renamed_cleaned_null_casted" AS (
    -- Column Type Casting: 
    -- appointment_id: from VARCHAR to UUID
    -- claim_id: from VARCHAR to UUID
    -- line_note: from DECIMAL to VARCHAR
    -- patient_id: from VARCHAR to UUID
    -- primary_diagnosis_ref: from INT to VARCHAR
    -- procedure_code: from INT to VARCHAR
    -- procedure_modifier_1: from DECIMAL to VARCHAR
    -- procedure_modifier_2: from DECIMAL to VARCHAR
    -- provider_id: from VARCHAR to UUID
    -- quaternary_diagnosis_ref: from DECIMAL to VARCHAR
    -- secondary_diagnosis_ref: from DECIMAL to VARCHAR
    -- service_end_datetime: from VARCHAR to TIMESTAMP
    -- service_location_id: from VARCHAR to UUID
    -- service_start_datetime: from VARCHAR to TIMESTAMP
    -- supervising_provider_id: from VARCHAR to UUID
    -- tertiary_diagnosis_ref: from DECIMAL to VARCHAR
    -- transaction_id: from VARCHAR to UUID
    -- transfer_out_id: from DECIMAL to VARCHAR
    SELECT
        "service_description",
        "patient_insurance_id",
        "outstanding_balance",
        "charge_id",
        "service_units",
        "department_id",
        "transfer_type",
        "payment_amount",
        "unit_cost",
        "adjustment_amount",
        "transaction_amount",
        "transaction_type",
        "payment_method",
        "transfer_amount",
        "fee_schedule_id",
        CAST("appointment_id" AS UUID) 
        AS "appointment_id",
        CAST("claim_id" AS UUID) 
        AS "claim_id",
        CAST("line_note" AS VARCHAR) 
        AS "line_note",
        CAST("patient_id" AS UUID) 
        AS "patient_id",
        CAST("primary_diagnosis_ref" AS VARCHAR) 
        AS "primary_diagnosis_ref",
        CAST("procedure_code" AS VARCHAR) 
        AS "procedure_code",
        CAST("procedure_modifier_1" AS VARCHAR) 
        AS "procedure_modifier_1",
        CAST("procedure_modifier_2" AS VARCHAR) 
        AS "procedure_modifier_2",
        CAST("provider_id" AS UUID) 
        AS "provider_id",
        CAST("quaternary_diagnosis_ref" AS VARCHAR) 
        AS "quaternary_diagnosis_ref",
        CAST("secondary_diagnosis_ref" AS VARCHAR) 
        AS "secondary_diagnosis_ref",
        CAST("service_end_datetime" AS TIMESTAMP) 
        AS "service_end_datetime",
        CAST("service_location_id" AS UUID) 
        AS "service_location_id",
        CAST("service_start_datetime" AS TIMESTAMP) 
        AS "service_start_datetime",
        CAST("supervising_provider_id" AS UUID) 
        AS "supervising_provider_id",
        CAST("tertiary_diagnosis_ref" AS VARCHAR) 
        AS "tertiary_diagnosis_ref",
        CAST("transaction_id" AS UUID) 
        AS "transaction_id",
        CAST("transfer_out_id" AS VARCHAR) 
        AS "transfer_out_id"
    FROM "claims_transactions_renamed_cleaned_null"
)

-- COCOON BLOCK END
SELECT *
FROM "claims_transactions_renamed_cleaned_null_casted"

version: 2
models:
- name: stg_claims_transactions
  description: The table represents claims transactions in a healthcare system. It
    includes charges and payments for medical services. Each row is a transaction
    linked to a claim. Transactions have details like patient ID, procedure code,
    amount, and payment method. The table connects patients, providers, and financial
    aspects of healthcare services.
  columns:
  - name: service_description
    description: Description of the procedure or service
    tests:
    - not_null
    cocoon_meta:
      unusual_values: '''Encounter for problem'' and ''Encounter for problem (procedure)''
        are redundant. Inconsistent use of ''(procedure)'' suffix.'
      data_type:
        current_data_type: VARCHAR
  - name: patient_insurance_id
    description: Identifier for patient's insurance
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: outstanding_balance
    description: Remaining balance to be paid
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: charge_id
    description: Identifier for the specific charge within a claim
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: service_units
    description: Number of units of service provided
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: department_id
    description: Identifier for the department providing the service
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: transfer_type
    description: Type of transfer (e.g., 'p' for payment)
    tests:
    - accepted_values:
        values:
        - '1'
        - '2'
    cocoon_meta:
      missing_reason: Not applicable when there's no transfer involved
      future_accepted_values:
      - p
      - t
      - w
      - d
      - r
      - f
      data_type:
        current_data_type: VARCHAR
  - name: payment_amount
    description: Amount paid for the service
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: unit_cost
    description: Cost per unit of service
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: adjustment_amount
    description: Adjustments made to the charge
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: transaction_amount
    description: Monetary value of the transaction
    cocoon_meta:
      missing_reason: Not applicable for PAYMENT transactions, only for CHARGES
      data_type:
        current_data_type: DECIMAL
  - name: transaction_type
    description: Type of transaction (e.g., CHARGE, PAYMENT)
    tests:
    - not_null
    - accepted_values:
        values:
        - PAYMENT
        - CHARGE
        - TRANSFERIN
        - TRANSFEROUT
    cocoon_meta:
      future_accepted_values:
      - REFUND
      - WITHDRAWAL
      - DEPOSIT
      - INTEREST
      - FEE
      - ADJUSTMENT
      - REVERSAL
      - CASHBACK
      - CREDIT
      - DEBIT
      data_type:
        current_data_type: VARCHAR
  - name: payment_method
    description: Payment method used (e.g., CHECK, CASH)
    tests:
    - accepted_values:
        values:
        - ECHECK
        - COPAY
        - CASH
        - CC
        - CHECK
    cocoon_meta:
      missing_reason: Not applicable for CHARGE transactions, only for PAYMENTS
      future_accepted_values:
      - DEBIT
      - WIRE
      - ACH
      - MONEY ORDER
      - PAYPAL
      - VENMO
      - APPLE PAY
      - GOOGLE PAY
      - CRYPTOCURRENCY
      - GIFT CARD
      - STORE CREDIT
      - LOYALTY POINTS
      data_type:
        current_data_type: VARCHAR
  - name: transfer_amount
    description: Amount transferred between accounts
    cocoon_meta:
      missing_reason: Not applicable when there's no transfer involved
      data_type:
        current_data_type: DECIMAL
  - name: fee_schedule_id
    description: Identifier for the fee schedule used
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: appointment_id
    description: Identifier for the associated appointment
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: claim_id
    description: Identifier for the associated claim
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: line_note
    description: Additional notes for the transaction line
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: patient_id
    description: Unique identifier for the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: primary_diagnosis_ref
    description: Primary diagnosis reference
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: procedure_code
    description: Code identifying the medical procedure performed
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: procedure_modifier_1
    description: First modifier for the procedure code
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: procedure_modifier_2
    description: Second modifier for the procedure code
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: provider_id
    description: Unique identifier for the healthcare provider
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: quaternary_diagnosis_ref
    description: Quaternary diagnosis reference
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: secondary_diagnosis_ref
    description: Secondary diagnosis reference
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: service_end_datetime
    description: End date and time of the service
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: TIMESTAMP
  - name: service_location_id
    description: Identifier for the location of service
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: service_start_datetime
    description: Start date and time of the service
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: supervising_provider_id
    description: ID of supervising provider for the service
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: tertiary_diagnosis_ref
    description: Tertiary diagnosis reference
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: transaction_id
    description: Unique identifier for the transaction
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column represents a unique identifier for each transaction.
        For this table, each row is a distinct transaction (either a charge or a payment).
        The transaction_id appears to be unique for each row, as it's different for
        every sample provided.
      data_type:
        current_data_type: UUID
  - name: transfer_out_id
    description: Identifier for transfer out transactions
    cocoon_meta:
      missing_reason: Not applicable when there's no transfer involved
      data_type:
        current_data_type: VARCHAR
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-24 01:14:58.976415+00:00
WITH 
"procedures_renamed" AS (
    -- Rename: Renaming columns
    -- START -> procedure_start_time
    -- STOP -> procedure_end_time
    -- PATIENT -> patient_id
    -- ENCOUNTER -> encounter_id
    -- CODE -> procedure_code
    -- DESCRIPTION -> procedure_description
    -- BASE_COST -> procedure_cost
    -- REASONCODE -> reason_code
    -- REASONDESCRIPTION -> reason_description
    SELECT 
        "START" AS "procedure_start_time",
        "STOP" AS "procedure_end_time",
        "PATIENT" AS "patient_id",
        "ENCOUNTER" AS "encounter_id",
        "CODE" AS "procedure_code",
        "DESCRIPTION" AS "procedure_description",
        "BASE_COST" AS "procedure_cost",
        "REASONCODE" AS "reason_code",
        "REASONDESCRIPTION" AS "reason_description"
    FROM "memory"."main"."procedures"
),

"procedures_renamed_casted" AS (
    -- Column Type Casting: 
    -- encounter_id: from VARCHAR to UUID
    -- patient_id: from VARCHAR to UUID
    -- procedure_code: from INT to VARCHAR
    -- procedure_end_time: from VARCHAR to TIMESTAMP
    -- procedure_start_time: from VARCHAR to TIMESTAMP
    -- reason_code: from DECIMAL to VARCHAR
    SELECT
        "procedure_description",
        "procedure_cost",
        "reason_description",
        CAST("encounter_id" AS UUID) 
        AS "encounter_id",
        CAST("patient_id" AS UUID) 
        AS "patient_id",
        CAST("procedure_code" AS VARCHAR) 
        AS "procedure_code",
        CAST("procedure_end_time" AS TIMESTAMP) 
        AS "procedure_end_time",
        CAST("procedure_start_time" AS TIMESTAMP) 
        AS "procedure_start_time",
        CAST("reason_code" AS VARCHAR) 
        AS "reason_code"
    FROM "procedures_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "procedures_renamed_casted"

version: 2
models:
- name: stg_procedures
  description: The table is about medical procedures performed on patients. It includes
    details like start and stop times, patient ID, encounter ID, procedure code and
    description, base cost, and reason codes. Each row represents a specific procedure
    performed on a patient during a medical encounter. The table links patients, encounters,
    and procedures, providing a comprehensive view of medical services rendered.
  columns:
  - name: procedure_description
    description: Brief description of the medical procedure
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: .+\s\(procedure\)$
        summary: Procedures ending with "(procedure)"
      - regex: .+\s\(regime\/therapy\)$
        summary: Regimes or therapies ending with "(regime/therapy)"
      - regex: .+\s\([a-z\/]+\)$
        summary: General pattern for procedures or regimes in parentheses
      data_type:
        current_data_type: VARCHAR
  - name: procedure_cost
    description: Base cost of the medical procedure
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: reason_description
    description: Description of the reason for the procedure
    cocoon_meta:
      missing_reason: Not applicable for procedures without specific medical reasons.
      data_type:
        current_data_type: VARCHAR
  - name: encounter_id
    description: Unique identifier for the medical encounter
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: patient_id
    description: Unique identifier for the patient
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: UUID
  - name: procedure_code
    description: Numerical code for the medical procedure
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: procedure_end_time
    description: End time of the medical procedure
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: procedure_start_time
    description: Start time of the medical procedure
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: reason_code
    description: Code indicating the reason for the procedure
    cocoon_meta:
      missing_reason: Not applicable for procedures without specific medical reasons.
      data_type:
        current_data_type: VARCHAR
  tests: []