Turning activity data into animal lifecycle insight with Databricks

Building a Lakehouse View of Veterinary Populations

Veterinary organisations hold a rich picture of animal care across their operational systems. Every consultation, sale, registration, and practice interaction contributes a small piece of context. But for business leaders, the real value often comes from a more strategic question: how is the animal population changing over time?

That question is harder than it first appears. Animals may be newly registered, actively returning, re-engaged after a long gap, beginning to lapse, fully lapsed, lost from the active population, deceased, or deregistered. Those states also need to be understood consistently across countries, source systems, practices, species, and time periods.

CGI has supported the delivery of a lakehouse data productplatform built on Databricks that calculates and snapshots animal lifecycle status across Great Britain and Ireland, then processes those snapshots into weekly trend outputs for reporting and analytics. The latest production snapshot covers more than 29 million animals across both markets, with the underlying snapshot layer now processing over 2.6 billion animal status rows.

At a business level, the solution creates a reliable view of animal population movement. It can show how many animals were active at the start and end of a week, how many were newly active, how many recovered from inactivity, and how many moved into lapsing, deceased, or deregistered states. This gives commercial, operational, and clinical stakeholders a more consistent foundation for understanding population health, engagement, and retention.

In the latest reporting week, the platform tracked more than 2.28 million active animals at week close across Great Britain and Ireland. It also identified over 10,000 new animals, more than 7,000 recovered animals returning to active status, and over 13,000 animals moving into a lapsing state during that single week.

The work is built around a simple principle: animal lifecycle reporting should be reproducible. Instead of relying only on point-in-time queries over current data, the platform records requested snapshot dates and appends animal status snapshots for each relevant source-system platform. These snapshots are then transformed into an effective-dated status history and weekly transition outputs.

This approach means teams can analyse both population balances and movement events. For example, the reporting layer can support questions such as:

• How many active animals did we have at the beginning and end of each week?
• Which animals are new, recurring, re-engaged, lapsing, lapsed, or lost?
• How many animals returned to active status after a period of inactivity?
• Which countries, practice groups, species, or age categories are driving movement?
• Are changes in the active population being driven by acquisition, recovery, lapse, death, or deregistration?

For data and engineering teams, the implementation uses Databricks Lakeflow Jobs and Lakeflow Declarative Pipelines to orchestrate the flow from source activity data through Bronze, Silver, and Gold layers. The animal-status pipeline builds daily animal-level snapshots from consolidated practice-management activity and master-data dimensions. It then uses change data feed processing and an SCD Type 2 ledger to maintain a compact, durable history of status changes.

The platform also uses newer Databricks capabilities such as Lakeflow Auto-TTL on the append-only snapshot table. This allows the detailed snapshot layer to retain a managed recent history, while the compact ledger and downstream history preserve the long-term lifecycle signal needed for analytics. For historical backfills, the pipeline can temporarily disable snapshot retention, process controlled date ranges, validate the resulting history, and then restore the retention configuration.

Once the Silver lifecycle layer has been calculated, Gold reporting outputs are published for Great Britain, Ireland, and a combined global view. These outputs include weekly summary views for opening and closing balances, movement counts, and animal-level transition drill-through. Databricks metric views then provide governed definitions for measures such as animal population, retention, recovery, lapse, and movement counts, helping dashboards and AI/BI consumers use the same business logic consistently.

The result is more than a set of tables. It is a reusable data product that turns operational veterinary activity into trusted lifecycle intelligence. Business users get a clearer view of how animal populations are changing. Data teams get governed, testable pipelines and metric definitions. And the organisation gainsgets a foundation for better decisions around engagement, retention, planning, and service delivery.