What We're Looking For
We’re hiring a mid-level Data Modeler to turn diverse healthcare and commercial data into curated, trusted analytics within Microsoft Fabric. You’ll design Gold-layer star schemas and Power BI semantic models to support PCCA’s core domains: product/APIs catalog, membership and education programs, customer orders and fulfillment, cleanroom services, USP compliance and regulatory tracking, clinical/formulary usage, and operational quality. You’ll partner with data engineering and BI to move data from Bronze → Silver → Gold in a Medallion architecture and deliver certified, performant metrics for leadership and operational teams.
What You Will Do - Design dimensional models in the Gold layer for PCCA domains:
- Commercial/Operations: Orders and order lines, inventory availability, shipment/fulfillment, returns, pricing, promotions, member subscriptions.
- Membership/Education: Membership lifecycle, entitlements, course enrollments and completions (CE), event attendance, certifications.
- Product/Clinical: Product catalog (APIs, bases, devices), formulations/recipes and versioning, stability/BUD attributes, allergens, SDS/CoA linkage.
- Compliance/Quality: USP 795/797/800 compliance data, audit findings, corrective actions, sensitivity labeling, support case/ticketing.
- Define model grain, surrogate keys, conformed dimensions (e.g., Customer/Account, Product, Practitioner, Date, Geography), and role-playing dimensions.
- Implement SCD Type 1/2 in Delta (Warehouse/Lakehouse) for key dims (Customer, Product, Practitioner, Membership).
- Handle late-arriving facts/dimensions, inferred members, and bridge tables for many-to-many (e.g., formulation → multiple actives/allergens).
- Build and maintain Power BI semantic models:
- Clean star schemas, relationship management, hierarchies, display folders.
- Author DAX measures for revenue, margin, order volume, fill rates/OTIF, inventory days, membership MRR/retention/churn, education completions, compliance KPIs.
- Implement time intelligence (MTD/QTD/YTD, PY), semi-additive measures (inventory snapshots).
- Configure incremental refresh, aggregations where needed, and Row-Level Security (e.g., by region/account team).
- Optimize performance and cost:
- Propose partitioning/clustering/Z-ordering strategies in Delta; consider Warehouse materialized views for heavy rollups; reduce cardinality in Power BI; choose pre-aggregation vs. Aggregations in PBI.
- Collaborate on Medallion data flow:
- Specify Silver contracts and quality checks (dedupe, CDC alignment, schema expectations).
- Review Pipelines/Dataflows Gen2/Notebooks outputs for model-readiness and effective dating.
- Governance and documentation:
- Maintain data dictionaries, lineage, SCD behavior, and certified metric definitions; apply sensitivity labels and endorsements.
Who You Are
Must Have Qualiffications - 3–5 years in data modeling or analytics engineering with strong SQL.
- Hands-on Microsoft Fabric:
- Lakehouse/Warehouse, OneLake, Delta tables, Dataflows Gen2 or Pipelines; familiarity with SQL endpoint usage.
- Dimensional modeling proficiency:
- Star schemas; fact types (transactional, periodic snapshot, accumulating); bridge tables for M:N; degenerate and junk dimensions.
- SCD Type 1/2 with MERGE; effective/expiry dating; handling late-arriving data.
- Power BI semantic modeling and DAX:
- Clean tabular model design; CALCULATE/KEEPFILTERS/USERELATIONSHIP; date intelligence; semi-additive measures; model properties (data types, sort-by, formatting).
- Incremental refresh; basic aggregations; RLS.
- Data quality and governance:
- Define tests (unique/not-null/accepted values), document metrics, manage endorsements; apply sensitivity labels for PII/regulated data.
- Communication:
- Translate stakeholder requirements into grain/facts/dimensions and certified measures; collaborate across DE, BI, and business teams.
Nice-to-Have Qualifications - Healthcare/life sciences or pharmacy domain exposure:
- Product master for APIs/bases/devices, SDS/CoA linkages, allergens, formulations and versioning, USP compliance artifacts, membership programs and CE tracking.
- Performance tuning in Fabric:
- Warehouse materialized views/indexing, Delta OPTIMIZE and Z-ordering, partition pruning; direct lake/direct query/composite model patterns.
- Testing/observability:
- Power BI dataset testing patterns; data freshness/volume drift monitoring.
- Git workflows:
- Power BI Projects (.pbip), Fabric Git integration, environment-based deployment pipelines.
- Experience with calculation groups for time intelligence; metric layer governance.
Who We Are
PCCA helps pharmacists and prescribers create personalized medicine that makes a difference in patients’ lives. As a complete resource for independent and health system compounding pharmacists, PCCA provides high-quality products, education, and support to more than 3,000 pharmacies throughout the United States, Canada, Australia and other countries around the world. Incorporated in 1981 by a network of pharmacists, PCCA has supported pharmacy compounding for more than 40 years. Learn more at www.pccarx.com.
