Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
care_benefit_idPrimary key and care benefit unique identifier.id
createdThe timestamp when the care benefit record was created.
modifiedThe timestamp when the care benefit record was last modified.
is_deletedIndicates whether the care benefit record is deleted.
care_plan_idsArray of care plan IDs that include this care benefit, via the care plan benefit relationship.
nameThe name of the care benefit.name
statusThe current status of the care benefit (e.g., active, inactive).status
service_category_idsArray of service pricing category IDs associated with this care benefit. Services in these categories are covered by the benefit.service_categories
excluded_service_idsArray of group service IDs explicitly excluded from this care benefit, even if their category is otherwise covered.excluded_services
categoryThe category of the care benefit (e.g., PREVENTATIVE_AND_WELLNESS, DIAGNOSTIC_AND_MONITORING, ROUTINE_AND_CONVENIENCE, DENTAL_AND_SPECIALIZED, FOOD_AND_NUTRITION).category
kindThe type of care benefit determining how usage is tracked (e.g., UNLIMITED, POINTS, DISCOUNT).kind
care_benefit_servicesArray of objects representing the specific services and bundles associated with this care benefit.care_benefit_services
last_updatedThe timestamp when the care benefit record was last updated. This is the most recent timestamp from all related tables.
vetcove_corporate_idThe identifier of the corporate group associated with the care benefit.

​
SQL Definition

/*
    This model provides a business-ready, denormalized view of care benefit data,
    mirroring the /care-benefits/ API endpoint. Each row represents one care benefit,
    with associated services, service pricing categories, and excluded services
    aggregated as arrays.
*/

"
    )
}}

with care_benefits as (
    select * from {{ ref('care_plan_care_benefit') }}
)

, care_benefit_services as (
    select * from {{ ref('care_plan_care_benefit_service') }}
)

, careplanbenefit as (
    select * from {{ ref('care_plan_benefit') }}
)

, group_services as (
    select * from {{ ref('service') }}
)

, group_bundles as (
    select * from {{ ref('service_bundle') }}
)

-- Aggregate care benefit services (with service/bundle names) per care_benefit
, care_benefit_services_agg as (
    select
        care_benefit_services.care_benefit_id
        , array_agg(
            object_construct_keep_null(
                'id', care_benefit_services.id
                , 'group_service_id', care_benefit_services.service_id
                , 'group_service_name', group_services.name
                , 'group_bundle_id', care_benefit_services.service_bundle_id
                , 'group_bundle_name', group_bundles.name
                , 'points', care_benefit_services.points
            )
        ) as care_benefit_services
        , max(greatest(
            coalesce(care_benefit_services.last_updated, '1900-01-01')
            , coalesce(group_services.last_updated, '1900-01-01')
            , coalesce(group_bundles.last_updated, '1900-01-01')
        )) as last_updated
    from care_benefit_services
    left join group_services
        on care_benefit_services.service_id = group_services.id
    left join group_bundles
        on care_benefit_services.service_bundle_id = group_bundles.id
    where
        care_benefit_services.is_deleted = false
    group by care_benefit_services.care_benefit_id
)

-- Aggregate care plan IDs per care_benefit
, care_plan_ids_agg as (
    select
        care_benefit_id
        , array_agg(care_plan_id) as care_plan_ids
        , max(last_updated) as last_updated
    from careplanbenefit
    group by care_benefit_id
)

select
    -- Identifiers
    care_benefits.id as care_benefit_id
    , care_benefits.created
    , care_benefits.modified
    , care_benefits.is_deleted
    , coalesce(care_plan_ids_agg.care_plan_ids, parse_json('[]'))
        as care_plan_ids
    , care_benefits.name
    , care_benefits.status
    , care_benefits.service_category_ids
    , care_benefits.excluded_service_ids
    , care_benefits.category
    , care_benefits.kind
    , coalesce(care_benefit_services_agg.care_benefit_services, parse_json('[]'))
        as care_benefit_services
    , greatest(
        coalesce(care_benefits.last_updated, '1900-01-01')
        , coalesce(care_benefit_services_agg.last_updated, '1900-01-01')
        , coalesce(care_plan_ids_agg.last_updated, '1900-01-01')
    ) as last_updated
    , care_benefits.vetcove_corporate_id
from care_benefits
left join care_benefit_services_agg
    on care_benefits.id = care_benefit_services_agg.care_benefit_id
left join care_plan_ids_agg
    on care_benefits.id = care_plan_ids_agg.care_benefit_id