AvoMD
Case Study

Astrodata & AvoMD

About AvoMD

AvoMD is a healthcare technology company building AI tools for clinical decision support, led by a team that includes practicing physicians. Fresh off a Series A raise, the company needed its data infrastructure to grow up alongside its business. The goal was the ability to answer two questions with confidence: what is AI usage actually costing us, and who are our genuinely active users?

The Challenge

AvoMD had outgrown Mixpanel. It had gotten the company to a good starting point. Post-Series A, with board members and investors asking sharper questions, AvoMD needed a stronger foundation of metrics to continue scaling their reporting capabilities alongside the business.

In addition, AvoMD had a second, client-facing use case. AvoMD’s own healthcare clients were increasingly interested in their usage data, but Mixpanel only supported static, view-only dashboards. AvoMD wanted to give clients the ability to explore their own data, surfaced through an embedded Omni dashboard.

The Approach

Astrodata scoped and delivered a joint Snowflake + Omni implementation built around three goals:

Figure 01 — Built Around Three Goals
  1. 1

    A data platform foundation: development and production launch of a data platform incorporating multiple existing sources: HubSpot, Mixpanel, and Postgres

  2. 2

    Two priority use cases: AI cost tracking (token consumption, and cost by model, product, and org) and a formal, reproducible definition of onboarded/active users

  3. 3

    A structured handoff: documentation and prioritized recommendations for future use cases along with frequent co-development sessions to ensure AvoMD’s team would be able to adopt the new solution with ease.

The Delivery & Approach

To achieve robust AI Usage and User Definition metrics, Astrodata needed to unify three disparate data sources into a single analytics database:

  1. HubSpot, which defined customers and accounts
  2. Postgres, which defined users
  3. Mixpanel, which defined user events

Snowflake was the designated repository for all three sources. Each of the three sources required a different approach to load data into Snowflake.

  1. HubSpot: Astrodata used a Python Stored Procedure to capture HubSpot data.
  2. Postgres: daily snapshots were loaded from an S3 External Stage.
  3. Mixpanel: Mixpanel data loading brought on cost challenges. While Mixpanel offers a managed incremental Snowflake pipeline, they charge extra for it (even for customers who subscribe to their Enterprise Plan). Seeking to avoid this cost, the Astrodata team authored a custom Airflow DAG, to incrementally write Mixpanel API responses to S3, and then Snowflake (all while respecting the memory limits of a micro MWAA instance)
Figure 02 — The Joint Snowflake + Omni Implementation
The joint Snowflake and Omni implementationHubSpot (customers and accounts, loaded via a Python stored procedure), Postgres (users, loaded via daily snapshots from an S3 external stage), and Mixpanel (user events, loaded via a custom Airflow DAG through S3) feed Snowflake, the designated repository, with dbt Core in MWAA via Astronomer Cosmos and RBAC with PHI column masking, producing a star schema data model. Omni connects with a semantic layer, enabling internal and client-facing embedded dashboards with row-level security.HubSpotcustomers & accountsPython stored procedurePostgresPostgresuserssnapshots · S3 external stageMixpaneluser eventsAirflow DAG → S3Snowflakethe designated repositoryPer-source ingestiondbt Core · MWAA · CosmosRBAC + PHI column maskingStar schema data modelHIPAA posture simple to maintainOmnisemantic layerInternal dashboardsClient-facing embeddedrow-level security

With ingestion complete, Astrodata moved on to focus on the data model. Astrodata and AvoMD elected to use a star schema, with a central fact table containing Mixpanel events. While joins to the Postgres data proved straightforward with shared UUIDs, HubSpot joins required thoughtful engineering around email domains. The Astrodata team used dbt Core in MWAA via Astronomer Cosmos, to implement data model transformations.

Figure 03 — The Star Schema Data Model
A star schema with a central fact table of Mixpanel eventsA central fact table containing Mixpanel events sits at the middle of a star schema. It joins to the Postgres user dimension through shared UUIDs, and to the HubSpot customer and account dimension through email domains — a conformed modeling layer with shared dimensions and fact tables.HubSpotcustomers & accountsdimensionFact tableMixpaneleventsstar schema · centerPostgresPostgresusersdimensionjoined viaemail domainsjoined viashared UUIDsshared dimensionsA conformed star-schema modeling layer with shared dimensions and fact tables

Using dbt

How the Astrodata team leveraged dbt:

Figure 04 — How the Team Leveraged dbt
  • Architecture

    Designed a conformed star-schema modeling layer with shared dimensions and fact tables built on explicit, consistent join keys, so the BI tool’s data model maps directly onto governed tables instead of ad-hoc SQL.

  • Data quality

    Engineered those measures to be safe to aggregate in the BI tool: trailing-window churn and onboarding definitions and product-agnostic rollups that let analysts sum across dimensions without double-counting.

  • Data integrity

    Enforced data quality with model contracts and automated tests, with marts materialized in a stable schema and refreshed on a scheduled pipeline for predictable performance.

  • Business logic

    Pushed business logic upstream into purpose-built fact models that sessionize raw event streams and pre-compute key metrics, so dashboards consume ready-made measures rather than re-deriving them.

With the refined data model ready, the team activated the Omni connection. The result was a semantic layer, enabling both internal and client-facing embedded dashboards. This solution replaced Mixpanel’s shallow, siloed experience with self-service analytics for AvoMD customers.

On the infrastructure side, the team also:

  • Evaluated ETL and transformation tooling against AvoMD’s cost, maintainability, and portability needs (ultimately implementing dbt Core inside Managed Workflows for Apache Airflow)
  • Implemented Snowflake RBAC and PHI column masking, centralizing security and compliance controls in Snowflake rather than spreading them across tools; this kept AvoMD’s HIPAA posture simple to maintain
  • Connected Omni to Snowflake with a semantic layer, enabling both internal dashboards and a client-facing embedded dashboard with row-level security
The Stack — Three Disparate Sources, One Platform
HubSpotPostgreSQLMixpanelSnowflakedbtOmni
Figure 05 — The Original Summer Timeline
  1. SummerOriginal timeline
  2. Mid-projectScope expands
  3. DeliveredOn time
  1. Astrodata scoped and delivered a joint Snowflake + Omni implementation built around three goals.
  2. Some mid-project scope expansion.
  3. Despite some mid-project scope expansion, Astrodata delivered within the original summer timeline.

The Outcome

Despite some mid-project scope expansion, Astrodata delivered within the original summer timeline.

AvoMD came away with:

  • A unified, SQL-queryable data model spanning HubSpot, Postgres, and Mixpanel
  • Token-level, JSON-granular visibility into AI cost (impossible in Mixpanel’s native UI)
  • A documented, defensible definition of active/onboarded users, built for board and investor scrutiny
  • A client-facing, self-service Omni dashboard with proper row-level security, replacing static Mixpanel reports
  • A clean, documented handoff to AvoMD’s incoming analytics engineer, with a prioritized roadmap for what comes next
Figure 06 — Two Questions, Answered with Confidence
  • What is AI usage actually costing us?

    Token-level, JSON-granular visibility into AI cost (impossible in Mixpanel’s native UI).

    Token consumptionBy modelBy productBy org
  • Who are our genuinely active users?

    A documented, defensible definition of active/onboarded users, built for board and investor scrutiny.

    FormalReproducibleOnboarded/active
Table 01 — Before and After
DimensionBeforeAfter
Client dashboardsStatic, view-only dashboardsA client-facing, self-service Omni dashboard with proper row-level security
AI costImpossible in Mixpanel’s native UIToken-level, JSON-granular visibility into AI cost
Data modelThree disparate data sourcesA unified, SQL-queryable data model spanning HubSpot, Postgres, and Mixpanel
Active/onboarded usersBoard members and investors asking sharper questionsA documented, defensible definition, built for board and investor scrutiny
Security & complianceControls spread across toolsRBAC and PHI column masking centralized in Snowflake