Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
subscription_idPrimary key and subscription unique identifier.id
createdThe timestamp when the subscription record was created.
modifiedThe timestamp when the subscription record was last modified.
is_deletedIndicates whether the subscription record is deleted.
clinic_idForeign key to the clinic & clinic_enriched table. The identifier of the clinic associated with the subscription.clinic_id
clinic_client_idForeign key to the client & client_enriched table. The identifier of the client associated with the subscription.clinic_client_id
clinic_patient_idForeign key to the patient & client_enriched table. The identifier of the patient associated with the subscription.clinic_patient_id
item_idForeign key to the item & item_enriched table. The identifier of the catalog item associated with the subscription.item_id
quantityThe quantity of the item ordered per subscription interval.quantity
interval_unitThe unit of time for the subscription interval (e.g., DAY, WEEK, MONTH, YEAR).interval_unit
frequencyThe frequency of the subscription orders within the interval unit.frequency
statusThe current status of the subscription.status
status_changedThe timestamp when the subscription’s status was last updated.status_changed
last_order_dateThe date the most recent order was placed for this subscription.last_order_date
next_order_dateThe date the next order is scheduled for this subscription.next_order_date
is_importedIndicates whether the subscription was imported from a legacy PIMS system (derived from external_id).is_imported
last_updatedThe timestamp when the subscription 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.

​
Relationships

​
SQL Definition

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

"
    )
}}

with subscriptions as (
    select * from {{ ref('subscription') }}
)

select
    subscriptions.id as subscription_id
    , subscriptions.created
    , subscriptions.modified
    , subscriptions.is_deleted
    , subscriptions.clinic_id
    , subscriptions.client_id as clinic_client_id
    , subscriptions.patient_id as clinic_patient_id
    , subscriptions.item_id
    , subscriptions.quantity
    , subscriptions.interval_unit
    , subscriptions.frequency
    , subscriptions.status
    , subscriptions.status_changed
    , subscriptions.last_order_date
    , subscriptions.next_order_date
    , case
        when
            coalesce(subscriptions.external_id, '') != ''
            then true
        else false
    end as is_imported
    , subscriptions.last_updated
    , subscriptions.vetcove_corporate_id
from subscriptions