Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
pharmacy_script_idPrimary key and pharmacy script unique identifier.id
createdThe timestamp when the prescription record was created.
modifiedThe timestamp when the prescription record was last modified.
is_deletedIndicates whether the prescription record is deleted.
vetcove_received_dateThe date the prescription request was received by Vetcove, derived from the created timestamp.vetcove_received_date
corporate_location_idThe corporate location identifier associated with the buyer clinic.corporate_location_id
clinicThe display name of the buyer clinic associated with the prescription request.clinic
client_nameThe name of the client associated with the prescription request.client_name
client_phone_numberThe client’s phone number recorded on the prescription request.client_phone_number
pet_nameThe name of the pet associated with the prescription request.pet_name
pet_speciesThe species of the pet associated with the prescription request.pet_species
medication_requestedThe name of the medication requested on the prescription.medication_requested
item_quantityThe quantity of the medication requested on the prescription.item_quantity
item_refill_countThe number of refills recorded on the prescription request.item_refill_count
third_party_retailerThe third-party retailer associated with the prescription request.third_party_retailer
third_party_priceThe price recorded for the third-party retailer on the prescription request, in dollars. Snowflake displays dollars, API returns cents.third_party_price
third_party_autoship_priceThe autoship price recorded for the third-party retailer on the prescription request, in dollars. Snowflake displays dollars, API returns cents.third_party_autoship_price
prescription_dateThe date of the prescription.prescription_date
prescription_typeThe type classification of the prescription.prescription_type
client_phone_sms_enabledIndicates whether the client’s phone number is SMS-enabled.client_phone_sms_enabled
client_emailThe client’s email address recorded on the prescription request.client_email
external_idThe external identifier associated with the prescription request.external_id
last_updatedThe timestamp when the prescription record was last updated. 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 pharmacy script data
    pre-joined for easy consumption, mirroring the third party prescription API.
*/

"
    )
}}

with prescriptions as (
    select * from {{ ref('prescriptions_third_party') }}
)

, buyers as (
    select * from {{ ref('buyer') }}
)

select
    prescriptions.id as pharmacy_script_id
    , prescriptions.created
    , prescriptions.modified
    , prescriptions.is_deleted
    , prescriptions.created::date as vetcove_received_date
    , buyers.location_id as corporate_location_id
    , buyers.name as clinic
    , prescriptions.client_name
    , prescriptions.client_phone_number
    , prescriptions.pet_name
    , prescriptions.pet_species
    , prescriptions.medication_requested
    , prescriptions.item_quantity
    , prescriptions.item_refill_count
    , prescriptions.third_party_retailer
    , prescriptions.third_party_price
    , prescriptions.third_party_autoship_price
    , prescriptions.prescription_date::date as prescription_date
    , prescriptions.prescription_type
    , prescriptions.client_phone_sms_enabled
    , prescriptions.client_email
    , prescriptions.external_id
    , greatest(
        coalesce(prescriptions.last_updated, '1900-01-01')
        , coalesce(buyers.last_updated, '1900-01-01')
    ) as last_updated
    , prescriptions.vetcove_corporate_id
from prescriptions
left join buyers
    on prescriptions.buyer_id = buyers.id