Our client is a global pharmaceutical leader whose Canadian operations depend on precise alignment between medical representatives and Healthcare Providers (HCPs). To maintain a competitive edge, they must navigate complex datasets involving physician specialties, provincial territory alignments, and product performance metrics.

Despite their scale, the client's data operations were hindered by a "manual-first" culture. The primary challenge was not a lack of data, but the inability to effectively utilize it due to a fractured ingestion and distribution model that left every team working from a different version of the truth.

The "Data Vendor" Bottleneck

Analysts were required to log into vendor portals daily to manually download massive Excel files. Raw data required hours of manual cleaning and VLOOKUP-heavy manipulation before it could be used in any report, creating a significant lag between data availability and business insight.

Key-Person Fragility

Transformation logic resided in individual heads and local macros rather than in any shared, documented system. This created an existential risk where the departure of a single analyst could halt the entire reporting cycle, with no one else able to replicate the process.

Information Reliability Issue

Locally stored files shared via Teams and Outlook led to multiple competing "sources of truth," causing strategic paralysis among decision-makers. Insights were frequently obsolete by delivery, as source datasets were updated during the lengthy manual processing window.

‍

Compass engineered a comprehensive data transformation pipeline that fundamentally redefined how our Client manages its commercial data. By implementing a Medallion Architecture within Snowflake, we replaced manual vendor portal downloads and fragmented Excel workflows with a fully automated, centralized data ecosystem. The pipeline ingests data daily from three critical sources — IQVIA market data, Veeva/Salesforce CRM and field activity, and anonymized Patient Support Program (PSP) records — moving each dataset through Bronze, Silver, and Gold layers that progressively add quality, structure, and business logic. For sensitive PSP data, a specialized anonymization pipeline was integrated before ingestion to ensure strict adherence to Canadian healthcare privacy regulations. Every morning, the organization begins the business day with a fully synchronized, report-ready view of the Canadian pharmaceutical market.

Underpinning the architecture is a production-grade development lifecycle built on CI/CD pipelines using Bitbucket and Jenkins, with all transformation logic managed through SQL and secured under Git version control. A strict Dev → QA → Prod environment strategy ensures that every change to business logic is tracked, auditable, and reproducible — replacing the fragile local macros that previously held the organization's most critical institutional knowledge. The Snowflake Gold layer serves as the exclusive source of truth for Power BI dashboards used by field managers and executives, as well as custom-built applications that support high-stakes commercial workflows including trimestrial goal-setting and cycle target list management.

Compass delivered a scalable, fully automated analytics platform that transforms our Client's data operations from end to end. The solution eliminates manual effort across the entire data lifecycle — from ingestion to reporting — while establishing the governance and infrastructure needed to support the company's growing analytical demands.

Automated Medallion Architecture on Snowflake

The Bronze layer eliminates manual vendor downloads by automatically ingesting raw data from IQVIA, Veeva/Salesforce, and PSP systems into a centralized Snowflake landing zone each morning. The Silver layer applies automated quality checks to standardize inconsistent formatting across different provinces and data vendors, and captures the full historical evolution of physician alignments and territory structures through Slowly Changing Dimension Type 2 (SCD2) logic. The Gold layer fuses these cleansed datasets into a unified 360-degree view of the business, housing all pharmaceutical tiering, alignment, and KPI calculation logic in a single governed, updatable location that can be modified without re-engineering the underlying pipeline.

Benefit: The client now operates from a single, synchronized source of truth. Business rules can be updated in one place, and the complete historical record of physician and territory data is preserved — enabling a level of longitudinal strategic analysis that was previously impossible.

DevOps Governance & CI/CD

Compass institutionalized the client's data transformation logic in version-controlled SQL using CI/CD pipelines built on Bitbucket and Jenkins. A strict Dev → QA → Prod promotion process ensures no untested changes reach production, and every modification is logged with a clear audit trail for compliance and historical troubleshooting. By moving critical business logic out of personal macros and into managed, documented code, the entire data operation becomes transparent, repeatable, and independent of any single individual.

Benefit: The client has eliminated key-person dependency. The departure of any analyst no longer poses a risk to the reporting cycle, and compliance teams have a fully auditable record of every change made to business logic — replacing what was previously a black box of local files and institutional memory.

Power BI Reporting & Custom Applications

The Snowflake Gold layer serves as the authoritative data source for a suite of Power BI dashboards that give field managers and executives real-time access to their most critical KPIs. Beyond standard reporting, Compass built custom applications directly on top of the Snowflake database to support high-stakes operational workflows — including trimestrial goal-setting and cycle target list management. Decisions that previously required weeks of back-and-forth email coordination can now be made in a single Teams meeting, with all stakeholders working from the same live dataset.

Benefit: The commercial team has self-service access to trusted, up-to-date data when they need it. The platform is purpose-built to accommodate new reporting requirements and use cases as the business grows, without requiring a rebuild of the underlying data infrastructure.