Hamelin Brain · Healthee Data Engineering

Start here

What is Hamelin?

Hamelin is Healthee's data pipeline — the machinery that gathers information from every corner of the company (the app, the CRM, support tools, claims files, sales calls) and turns it into clean, reliable tables people can trust and query.

🧠

Think of Hamelin as the kitchen behind Healthee's data. Raw ingredients arrive from many suppliers, get washed and prepped, then plated into the dishes — dashboards and reports — that the rest of the company actually consumes. Hamelin Brain is the menu and the recipe book: it tells you what's cooking, where each ingredient comes from, and which dish to order.

Architecture

The medallion pipeline

Data flows left to right through progressively cleaner layers. Each stage adds trust: raw lands in Bronze, gets cleaned and joined in Silver, then aggregated into Gold tables ready for dashboards. The engine (SnowflakeETLUtils) reads a YAML schema for every SQL query to build and merge tables in Snowflake.

💡

Every step makes the data a little more trustworthy and a little easier to use — like refining ore into a finished coin. By the time it reaches Gold, it's safe to put in front of leadership.

Sources

Ingested data

App DB (MongoDB), Postgres, HubSpot, Intercom, Gong, Linear, claims files & more land via Fivetran and custom loaders.

EXTERNAL_SOURCES · CLAIMS

Bronze

Raw landing

First stop inside the DWH. Lightly transformed, one table per source — the faithful copy everything else is built on.

DWH_BRONZE.*

Silver

Cleaned & joined

Deduped, enriched, standardized and stitched together. The primary source for analytics and most queries.

DWH_SILVER.*

Gold

Aggregated marts

Final reporting layer — user/company facts, GTM & product marts feeding dashboards and weekly reports.

AGG · GTM · PRODUCT

The map

Databases & schemas

Everything lives in Snowflake. HEALTHEE_DWH is the main warehouse holding all transformation layers; a handful of specialized databases hold raw and reference data.

🏛️ HEALTHEE_DWH

The main warehouse. Holds Bronze → Silver → Gold plus specialized gold schemas: AGG, GTM, PRODUCT, CLAIMS, COMPASS, SHERLOCK, ENGINE_HEAT, CORTEX_AGENTS.

🔌 EXTERNAL_SOURCES

Raw feeds from outside systems — MongoDB (Sansa), Postgres, HubSpot, Intercom, Gong, Linear, CustomerIO, Google/LinkedIn Ads, Maxio — landed via Fivetran.

💳 CLAIMS

Partner-delivered medical & pharmacy claims (Vensure, UNC Health Pardee, Empath Health, Instacart). Raw tables in CLAIMS.BRONZE.

🩺 RIBBON_DIRECTORY

Provider network directory data from Ribbon Health — the doctors, facilities and specialties behind provider search.

💰 COST_DIRECTORY

Healthcare cost reference data powering cost-transparency and estimate features, partitioned by cycle.

🔐 HEALTHEE_ACCESS

Access control, permissions and member-eligibility data — Core, Raw and Staging layers.

Where the answers live

Tables you'll actually use

A curated shortlist of the tables most questions get answered from. When in doubt, start with the USERS_OVERVIEW_UNIFIED family for cross-product analytics.

📊

Not sure which table? These are the "front doors." A data engineer can help you pick, but 80% of questions about members, companies and engagement start right here.

AGG · ⭐ primary denominator

USERS_OVERVIEW_UNIFIED

Unified daily user-level fact table combining Healthee App & PCT engagement with eligibility flags and company info. The go-to spine for cross-product analytics.

AGG

USERS_PLANS_OVERTIME

Daily snapshot of each user's medical / dental / vision plans, tiers, demographics and eligibility. The spine feeding USERS_OVERVIEW_UNIFIED.

DWH_SILVER

HA_EVENTS

The event table for all Healthee App analysis — daily batch. Use HA_EVENTS_LIVE for the hourly, near-real-time version.

DWH_SILVER

USERS

The cleaned user directory. Note: Trinet users carry a special role value and are usually filtered out for standard reporting.

DWH_BRONZE

COMPANY · COMPANIES

Customer / company data — COMPANY (HubSpot CRM) and COMPANIES (admin/internal). The reliable source for employer info.

PRODUCT

PRODUCT_ENGAGEMENT_MART

The product gold table behind the Product Dashboard and all three weekly Slack reports. Full-refreshed daily.

DWH_BRONZE

ZOE_USER_THREADS

Live source for every Zoe conversation, refreshed daily. Silver mirror adds company name & feedback for enriched analysis.

TALON_CLAIMS_RAW · CLAIMS

Claims tables

Two co-equal claims sources: multi-TPA Talon claims in TALON_CLAIMS_RAW, and partner files in the standalone CLAIMS database.

The engine room

54+ ETL pipelines

Each domain lives in dags/{domain}/ with paired sql_queries/ and yaml_files/, orchestrated by Airflow. Search to find one.

Core / orchestration Bronze / ingestion Silver / transform Gold / marts AI / intelligence External / integrations

See the data

Snowflake Streamlit dashboards

Interactive apps that run directly on Snowflake — no separate hosting. Each reads a gold table and turns it into something you can click through.

📈 Registration Live

Industry × size registration-rate matrix vs. Q2/H2 goals, company breakdown and monthly trend.

streamlit/registration

💵 ROI Live

Measured employer cost savings from platform engagement, filterable by funding type and date range.

streamlit/roi

🧭 Compass Live

Feature Intelligence — mines Gong call transcripts for feature requests, clusters into themes, attaches ARR.

streamlit/compass

🔎 Sherlock Live

QA Bug Insights — AI-extracted themes from Linear TEC bug tickets with drill-down and code-fix rate.

streamlit/sherlock

🔥 Engine Heat Live

Engineering sprint analytics (migrated from Looker) — velocity, planned vs. unplanned, label mix.

streamlit/engine_heat

📦 Product Live

Product engagement mart views — discovery, activation and stickiness across the platform.

streamlit/product

🧩 Customer Insights Live

Customer-level engagement and health signals for CS and account teams.

streamlit/customer_insights

✉️ CustomerIO Live

Messaging & campaign analytics from the CustomerIO integration.

streamlit/cio

📊 Product Analytics Live

The analytics workbench behind the weekly registration, CS and discovery-rate reports.

streamlit/product_analytics

The smart layer

AI & intelligence systems

Where Hamelin goes beyond tables — LLM-powered pipelines that read unstructured text (calls, tickets, threads) and turn it into structured insight, plus a self-serve data agent.

Compass

Feature Intelligence. GPT-4o reads Gong sales-call transcripts, extracts feature requests, clusters them into themes and attaches ARR — so Product knows what customers are actually asking for.

Sherlock

QA Bug Insights. GPT-4o distills one reusable QA theme per Linear TEC bug ticket, surfacing trends and a code-fix rate with drill-down to the source tickets.

Renaissance

A Claude Code plugin for company-wide, read-only DWH querying — lets anyone ask plain-English questions and get answers straight from Snowflake, safely.

Healthee 360 & Cortex Agents

Snowflake Cortex-powered agents over a semantic model — unified customer interactions and product feedback for AI-driven analysis.

🤖

These systems answer questions that used to need a human reading hundreds of calls or tickets. Ask "what are customers requesting?" or "which bugs keep coming back?" and the pipeline has already done the reading.

Delivered to your inbox

Automated weekly reports

Deterministic Slack pushes built on PRODUCT_ENGAGEMENT_MART and orchestrated by Airflow — the numbers show up without anyone pulling them.

📅 Registration Rate

Weekly registration-rate insights push, with an LLM-written narrative summary.

🤝 CS Registration Rate

Posts to #cs-product-analytics every Monday 8am ET — fully deterministic Block Kit message.

🎯 Discovery Rate / OKR

The OKR-headline discovery-rate report tracking the north-star engagement metric.