Subscription

Subscription enriched model providing subscription, payment method, and metadata.

• Each row represents a subscription.
• Compatible with the Vetcove API endpoint(s): /subscriptions/
• API endpoint(s): https://connect.vetcove.com/ext/v2/subscriptions/
• Snowflake view: phd_enriched.subscription_enriched

Columns

Column	Description	API Field Name
subscription_id	Primary key and subscription unique identifier.	id
created	The timestamp when the subscription record was created.
modified	The timestamp when the subscription record was last modified.
is_deleted	Indicates whether the subscription record is deleted.
clinic_id	Foreign key to the clinic & clinic_enriched table. The identifier of the clinic associated with the subscription.	clinic_id
clinic_client_id	Foreign key to the client & client_enriched table. The identifier of the client associated with the subscription.	clinic_client_id
clinic_patient_id	Foreign key to the patient & client_enriched table. The identifier of the patient associated with the subscription.	clinic_patient_id
item_id	Foreign key to the item & item_enriched table. The identifier of the catalog item associated with the subscription.	item_id
quantity	The quantity of the item ordered per subscription interval.	quantity
interval_unit	The unit of time for the subscription interval (e.g., DAY, WEEK, MONTH, YEAR).	interval_unit
frequency	The frequency of the subscription orders within the interval unit.	frequency
status	The current status of the subscription.	status
status_changed	The timestamp when the subscription’s status was last updated.	status_changed
last_order_date	The date the most recent order was placed for this subscription.	last_order_date
next_order_date	The date the next order is scheduled for this subscription.	next_order_date
is_imported	Indicates whether the subscription was imported from a legacy PIMS system (derived from external_id).	is_imported
last_updated	The timestamp when the subscription record was last updated in the source table. This is the most recent timestamp from all related tables.
vetcove_corporate_id	The unique identifier of the corporate group associated with this record.

Relationships

• Clinic via clinic_id
• Client via clinic_client_id
• Patient via clinic_patient_id
• Item via item_id

SQL Definition

View SQL

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