Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
clinic_idPrimary key and clinic unique identifier.id
createdThe timestamp when the clinic record was created.
modifiedThe timestamp when the clinic record was last modified.
is_deletedIndicates whether the clinic record is deleted.
nameThe name of the clinic.name
client_display_nameThe display name of the clinic shown to clients.client_display_name
primary_address_idUnique foreign key to the address table. The identifier of the clinic’s preferred address (1:1 relationship).primary_address.id
primary_address_nameThe name associated with the primary address.primary_address.name
primary_address_address_oneThe first line of the primary address (e.g., street address).primary_address.address_one
primary_address_address_twoThe second line of the primary address (e.g., apartment or suite number).primary_address.address_two
primary_address_cityThe city of the primary address.primary_address.city
primary_address_stateThe state of the primary address.primary_address.state
primary_address_zipcodeThe postal code associated with the primary address.primary_address.zipcode
primary_address_countryThe country of the primary address.primary_address.country
primary_address_countyThe county of the primary address.primary_address.county
primary_address_phone_numberThe phone number associated with the primary address.primary_address.phone_number
primary_addressA JSON object containing the full primary address details (name, address_one, address_two, city, state, county, zipcode, country, phone_number, id).
phone_numberThe primary phone number of the clinic.phone_number
website_urlThe main website URL of the clinic.website_url
group_location_codeThe hospital number from the clinic tag, used as a group location code.group_location_code
is_pimsIndicates whether the PIMS UI is enabled for the clinic.is_pims
emailThe primary email address of the clinic.email
clinic_timezoneThe timezone in which the clinic operates.clinic_timezone
clinic_is_hd_launchedIndicates whether the clinic has been launched on the HD platform.
hd_launched_atThe timestamp when the clinic was launched on the HD platform.
vetcove_buyer_idThe identifier of the Vetcove buyer associated with the clinic.
last_updatedThe timestamp when the clinic record was last updated in the source table. This is the most recent timestamp from all related tables.
vetcove_corporate_idThe unique identifier of the corporate group associated with this record.

​
SQL Definition

/*
    This model provides a business-ready, denormalized view of clinic data
    pre-joined for easy consumption, mirroring clinic API.
*/

"
    )
}}

with clinics as (
    select * from {{ ref('clinic') }}
)

, primary_addresses as (
    select * from {{ ref('address') }}
)

, clinic_tags as (
    select * from {{ ref('clinic_tag') }}
)

select
    clinics.id as clinic_id
    , clinics.created
    , clinics.modified
    , clinics.is_deleted
    , clinics.name
    , clinics.client_display_name
    , clinics.primary_address_id
    , primary_addresses.name as primary_address_name
    , primary_addresses.address_one as primary_address_address_one
    , primary_addresses.address_two as primary_address_address_two
    , primary_addresses.city as primary_address_city
    , primary_addresses.state as primary_address_state
    , primary_addresses.zipcode as primary_address_zipcode
    , primary_addresses.country as primary_address_country
    , primary_addresses.county as primary_address_county
    , primary_addresses.phone_number as primary_address_phone_number
    , object_construct_keep_null(
        'name', primary_addresses.name
        , 'address_one', primary_addresses.address_one
        , 'address_two', primary_addresses.address_two
        , 'city', primary_addresses.city
        , 'state', primary_addresses.state
        , 'county', primary_addresses.county
        , 'zipcode', primary_addresses.zipcode
        , 'country', primary_addresses.country
        , 'phone_number', primary_addresses.phone_number
        , 'id', clinics.primary_address_id
    ) as primary_address
    , clinics.phone_number
    , clinics.website_url
    , clinic_tags.hospital_number as group_location_code
    , coalesce(clinic_tags.is_pims_ui_enabled, false) as is_pims
    , clinics.email
    , clinics.timezone as clinic_timezone
    , case
        when clinics.hd_launched_at is null then false
        else true
    end as clinic_is_hd_launched
    , clinics.hd_launched_at
    , clinics.vetcove_buyer_id
    , greatest(
        coalesce(clinics.last_updated, '1900-01-01')
        , coalesce(clinic_tags.last_updated, '1900-01-01')
        , coalesce(primary_addresses.last_updated, '1900-01-01')
    ) as last_updated
    , clinics.vetcove_corporate_id
from clinics
left join clinic_tags
    on clinics.primary_tag_id = clinic_tags.id
left join primary_addresses
    on clinics.primary_address_id = primary_addresses.id