Care Benefit

Care benefit enriched model providing care benefit, care plan, and service data.

• Each row represents a care benefit.
• Compatible with the Vetcove API endpoint(s): /care-benefits/
• API endpoint(s): https://connect.vetcove.com/ext/v2/care-benefits/
• Snowflake view: phd_enriched.care_benefit_enriched

Columns

Column	Description	API Field Name
care_benefit_id	Primary key and care benefit unique identifier.	id
created	The timestamp when the care benefit record was created.
modified	The timestamp when the care benefit record was last modified.
is_deleted	Indicates whether the care benefit record is deleted.
care_plan_ids	Array of care plan IDs that include this care benefit, via the care plan benefit relationship.
name	The name of the care benefit.	name
status	The current status of the care benefit (e.g., active, inactive).	status
service_category_ids	Array of service pricing category IDs associated with this care benefit. Services in these categories are covered by the benefit.	service_categories
excluded_service_ids	Array of group service IDs explicitly excluded from this care benefit, even if their category is otherwise covered.	excluded_services
category	The category of the care benefit (e.g., PREVENTATIVE_AND_WELLNESS, DIAGNOSTIC_AND_MONITORING, ROUTINE_AND_CONVENIENCE, DENTAL_AND_SPECIALIZED, FOOD_AND_NUTRITION).	category
kind	The type of care benefit determining how usage is tracked (e.g., UNLIMITED, POINTS, DISCOUNT).	kind
care_benefit_services	Array of objects representing the specific services and bundles associated with this care benefit.	care_benefit_services
last_updated	The timestamp when the care benefit record was last updated. This is the most recent timestamp from all related tables.
vetcove_corporate_id	The identifier of the corporate group associated with the care benefit.

SQL Definition

View SQL

/*
    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