Dbt + BigQuery contract: fix vet clinic drug shrinkage & OR turnover models

Category: Data Science

Budget: 1900–1900 USDC

We operate a regional network of 14 veterinary hospitals. Raw transaction data from ezyVet and automated dispensing cabinets syncs to BigQuery via Fivetran every two hours.

Our current transformation models calculate surgical suite turnover times and controlled substance reconciliation incorrectly because emergency add-on procedures are not partitioned cleanly against scheduled surgery blocks. Our quarterly partner audit is on May 12th, and our data engineer is out on parental leave, so we need someone to step in and fix the transformation logic in dbt.

Deliverables:

• Refactor 2 staging and 3 mart dbt models (fct_surgical_turnover, fct_controlled_substance_variance)
• Add dbt tests for foreign key integrity, negative volume flags, and unexpected timestamp overlaps
• Update 3 Metabase dashboards with corrected metrics and verified date filters
• Document edge-case rules for multi-day emergency patient stays in a markdown runbook

Stack: BigQuery, dbt-core, GitHub Actions, Metabase. We can grant repo access and a staging BigQuery dataset today.

Skills required

• dbt
• Google BigQuery
• SQL
• Data Modeling
• Metabase

Milestones are funded into USDC escrow on Polygon before work begins.