Order

Order enriched model providing order, clinic, client, and payment intent data.

• Each row represents an order.
• Compatible with the Vetcove API endpoint(s): /order-analytics/
• API endpoint(s): https://connect.vetcove.com/ext/v2/order-analytics/
• Snowflake view: phd_enriched.orders_enriched

Columns

Column	Description	API Field Name
order_id	Primary key and order unique identifier.	id
created	The timestamp when the order record was created.
modified	The timestamp when the order record was last modified.
is_deleted	Indicates whether the order record is deleted.
order_placed_datetime	The timestamp when the order was placed.	order_placed_time
clinic_id	Foreign key to the clinic & clinic_enriched tables. The identifier of the clinic where the order was placed.	clinic_id
clinic_client_id	Foreign key to the client & client_enriched tables. The identifier of the client who placed the order.	client_id
subtotal	The order subtotal amount in dollars. Snowflake displays dollars, API returns cents.	subtotal
total	The total order amount in dollars. Snowflake displays dollars, API returns cents.	total
tax	The tax amount for the order in dollars. Snowflake displays dollars, API returns cents.	tax
shipping	The shipping cost for the order in dollars. Snowflake displays dollars, API returns cents.	shipping
handling_fee	The handling fee for the order in dollars. Snowflake displays dollars, API returns cents.	handling_fee
discount	The discount amount applied to the order in dollars. Snowflake displays dollars, API returns cents.	discount
shipping_address_id	Foreign key to the address table. The identifier of the shipping address for the order, if applicable.	shipping_address.id
shipping_address_address_one	The first line of the address (e.g., street address).	shipping_address.address_one
shipping_address_address_two	The second line of the address (e.g., apartment or suite number).	shipping_address.address_two
shipping_address_city	The city where the address is located.	shipping_address.city
shipping_address_state	The state where the address is located.	shipping_address.state
shipping_address_county	The county where the address is located.	shipping_address.county
shipping_address_zipcode	The postal code associated with the address.	shipping_address.zipcode
shipping_address_country	The country where the address is located.	shipping_address.country
shipping_address_name	The name associated with the address.	shipping_address.name
shipping_address_phone_number	The phone number associated with the address.	shipping_address.phone_number
shipping_address	JSON object containing all shipping address fields (name, address_one, address_two, city, state, county, zipcode, country, phone_number, id).	shipping_address
client_note	Notes from the client associated with the order.	client_note
clinic_note	Notes from the clinic associated with the order.	clinic_note
payment_method_id	Foreign key to the payment method used for the order, if applicable.	payment_method_id
status	The current status of the order (e.g., APPROVAL_NEEDED, APPROVED, CANCELED, PAYMENT_FAILED, SENT_TO_VENDOR, SHIPPED, COMPLETED, TRIAGED).	status
is_urgent	Indicates whether the order was marked as urgent.	is_urgent
urgency_fee	The urgency fee for the order in dollars. Snowflake displays dollars, API returns cents.	urgency_fee
transfer_ids	Array of transfer IDs for this order’s payment intent.	transfer_ids
refund_ids	Array of refund IDs for this order’s payment intent.	refund_ids
order_item_ids	Array of order item IDs belonging to this order.	order_item_ids
charge_capture_date	The date when the charge was captured. Sourced from the payment_intent table.	charge_capture_date
creator_type	The type of entity that created the order (e.g., SUBSCRIPTION, CLINIC_CHECKOUT_LINK, STAFF, CLIENT).	creator_type
client_type	The type of client that placed the order (e.g., new, recurring).	client_type
wholesale_tax	The wholesale tax amount for the order in dollars. Snowflake displays dollars, API returns cents.	wholesale_tax
client_service_fee	The client service fee for the order in dollars. Snowflake displays dollars, API returns cents.	client_service_fee
last_updated	The timestamp when the order 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

SQL Definition

View SQL

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

"
    )
}}

with orders as (
    select * from {{ ref('orders') }}
)

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

, payment_intents as (
    select * from {{ ref('payment_intent') }}
)

, order_item_ids_per_order as (
    select
        order_id
        , array_agg(id) within group (
            order by id
        ) as order_item_ids
        , max(last_updated) as last_updated
    from {{ ref('order_item') }}
    where is_deleted = false
    group by order_id
)

, refund_ids_per_order as (
    select
        payment_intent_id
        , array_agg(id) within group (
            order by id
        ) as refund_ids
        , max(last_updated) as last_updated
    from {{ ref('payment_client_refund') }}
    where is_deleted = false
    group by payment_intent_id
)

, transfer_ids_per_order as (
    select
        payment_intent_id
        , array_agg(id) within group (
            order by id
        ) as transfer_ids
        , max(last_updated) as last_updated
    from {{ ref('payment_transfer') }}
    where is_deleted = false
    group by payment_intent_id
)

select
    orders.id as order_id
    , orders.created
    , orders.modified
    , orders.is_deleted
    , orders.order_placed_time as order_placed_datetime
    , orders.clinic_id
    , orders.client_id as clinic_client_id
    , orders.subtotal
    , orders.total
    , orders.tax
    , orders.shipping
    , orders.handling_fee
    , orders.discount
    , shipping_addresses.id as shipping_address_id
    , shipping_addresses.address_one as shipping_address_address_one
    , shipping_addresses.address_two as shipping_address_address_two
    , shipping_addresses.city as shipping_address_city
    , shipping_addresses.state as shipping_address_state
    , shipping_addresses.county as shipping_address_county
    , shipping_addresses.zipcode as shipping_address_zipcode
    , shipping_addresses.country as shipping_address_country
    , shipping_addresses.name as shipping_address_name
    , shipping_addresses.phone_number as shipping_address_phone_number
    , object_construct_keep_null(
        'name', shipping_addresses.name
        , 'address_one', shipping_addresses.address_one
        , 'address_two', shipping_addresses.address_two
        , 'city', shipping_addresses.city
        , 'state', shipping_addresses.state
        , 'county', shipping_addresses.county
        , 'zipcode', shipping_addresses.zipcode
        , 'country', shipping_addresses.country
        , 'phone_number', shipping_addresses.phone_number
        , 'id', shipping_addresses.id
    ) as shipping_address
    , orders.client_note
    , orders.clinic_note
    , orders.payment_method_id
    , orders.status
    , orders.is_urgent
    , orders.urgency_fee
    , coalesce(transfer_ids_per_order.transfer_ids, array_construct()) as transfer_ids
    , coalesce(refund_ids_per_order.refund_ids, array_construct()) as refund_ids
    , coalesce(order_item_ids_per_order.order_item_ids, array_construct()) as order_item_ids
    , payment_intents.charge_capture_date
    , orders.creator_type
    , orders.client_type
    , orders.wholesale_tax
    , orders.client_purchasing_fee as client_service_fee
    , greatest(
        coalesce(orders.last_updated, '1900-01-01')
        , coalesce(shipping_addresses.last_updated, '1900-01-01')
        , coalesce(payment_intents.last_updated, '1900-01-01')
        , coalesce(order_item_ids_per_order.last_updated, '1900-01-01')
        , coalesce(refund_ids_per_order.last_updated, '1900-01-01')
        , coalesce(transfer_ids_per_order.last_updated, '1900-01-01')
    ) as last_updated
    , orders.vetcove_corporate_id
from orders
left join shipping_addresses
    on orders.shipping_address_id = shipping_addresses.id
left join order_item_ids_per_order
    on orders.id = order_item_ids_per_order.order_id
left join payment_intents
    on orders.payment_intent_id = payment_intents.id
left join refund_ids_per_order
    on orders.payment_intent_id = refund_ids_per_order.payment_intent_id
left join transfer_ids_per_order
    on orders.payment_intent_id = transfer_ids_per_order.payment_intent_id