YoursSherpa
Contact us

SystemsSheet 05 of 08

Data warehouses built around the questions your teams ask

A warehouse is where reporting becomes reliable. We model it as a star schema, load it with tested pipelines, and tune storage and queries for the reports your teams run every day. A warehouse is deterministic by design. AI is added only where it helps people use it.

Model
Star schema with conformed dimensions
Runs on
Redshift, Snowflake, BigQuery, Databricks SQL, Fabric
Built before
10M+ healthcare records a year on Redshift

Warehouse load and serving path

Switch the environment to see the services it runs on. Play the walkthrough, or pick a step.

Deterministic codeAI stepHuman reviewYour existing systemAI assist, approved by a person
    Warehouse load and serving path

    Tested loads into a star schema, tuned from real query logs, serving BI tools and a query assistant.

    Delivered on Amazon Redshift: Glue, Airflow and EventBridge, distribution and sort keys, materialized views.

    See it run

    The star schema behind a typical claims report, and the query plan showing why distribution and sort keys matter.

    Star schema and queryfact_claims with conformed dimensionsIllustrative example
    dim_memberdimension
    • member_sk
    • member_id
    • plan_code
    • valid_from
    • valid_to
    • is_current
    fact_claimsfact
    • claim_line_id
    • member_sk
    • provider_sk
    • service_date_sk
    • diagnosis_sk
    • allowed_amount
    • paid_amount
    dim_providerdimension
    • provider_sk
    • npi
    • specialty
    • network_status
    dim_datedimension
    • date_sk
    • calendar_date
    • year_month
    • year
    dim_diagnosisdimension
    • diagnosis_sk
    • icd10_code
    • ccs_category

    Query

    select m.plan_code, d.year_month,
       sum(f.paid_amount) as paid,
       count(distinct f.member_sk) as members
from fact_claims f
join dim_member m on m.member_sk = f.member_sk and m.is_current
join dim_date d on d.date_sk = f.service_date_sk
where d.year = 2026
group by 1, 2;

    Plan and result

    • Join on member_sk: co-located, no redistributionDS_DIST_NONE
    • Blocks scanned, sort key on service_date_sk38 of 412
    plan_codeyear_monthpaidmembers
    HMO-A2026-01$4,812,34011,204
    HMO-A2026-02$4,655,91011,187
    PPO-B2026-01$3,090,7756,842

    1.4 s on a warm cluster.

    Component view

    The same system as an exploded 3D drawing. Each plate is one step, and each block is one component in one of four materials.

    Drag to rotate. Select a layer to inspect it.

    Parts list

    Numbered bottom to top

    1. Claims, eligibility, provider, finance or sales data from the systems that run the business.

      • Application databases Your existing system
      • File drops and SFTP Your existing system
      • Third-party feeds Your existing system

      Typical toolingMySQL, PostgreSQL, SQL Server, S3 landing zones, SFTP feeds

    2. Jobs extract full and incremental loads and apply business rules before loading, such as quality-measure logic or risk stratification.

      • ETL jobs Deterministic code
      • Business rules Deterministic code
      • Change capture Deterministic code

      Typical toolingAWS Glue, dbt, Spark, Fivetran, Airbyte

    3. Loads land in staging first. Duplicates are removed and dimension history is kept with SCD Type 2.

      • Staging schema Deterministic code
      • De-duplication Deterministic code
      • SCD Type 2 history Deterministic code

      Typical toolingSQL MERGE, dbt snapshots, SCD Type 2

    4. Fact tables for claims, encounters and quality measures, joined to conformed dimensions for members, providers, dates and codes. Every team reports from the same definitions.

      • Claims and encounters facts Deterministic code
      • Member Deterministic code
      • Provider Deterministic code
      • Date Deterministic code
      • Diagnosis and procedure Deterministic code

      Typical toolingKimball dimensional modelling, conformed dimensions, surrogate keys

    5. Distribution keys co-locate the joins that matter, compound sort keys match common filters, and materialized views pre-compute frequent aggregations. Workload management keeps interactive queries ahead of batch jobs.

      • Distribution keys Deterministic code
      • Sort keys Deterministic code
      • Materialized views Deterministic code

      Typical toolingRedshift DISTKEY and SORTKEY, Snowflake clustering keys, BigQuery partitioning, WLM queues

    6. Nightly full refreshes and intra-day incremental loads, with event triggers when new files arrive and checks that stop a bad load before it reaches reports.

      • Scheduler Deterministic code
      • Event triggers Deterministic code
      • Load checks Deterministic code

      Typical toolingApache Airflow, AWS EventBridge, Step Functions, dbt tests

    7. Analysts and BI tools query the model directly. An optional assistant writes SQL against documented tables, and an LLM drafts column descriptions for the data dictionary, which the data owner reviews.

      • BI tools Deterministic code
      • Query assistant AI step
      • Dictionary drafter AI step

      Typical toolingPower BI, Tableau, Looker, text-to-SQL over documented tables

    Deterministic codeAI stepHuman reviewYour existing system
    Sheet05 of 08
    Layers7
    SystemData warehousing
    Drawn byA. Adhikari
    IssuedSeptember 2026
    ScaleNot to scale

    Decisions we make with you

    These choices shape every report built on the warehouse, so we settle them early and write them down.

    Grain

    What one row in each fact table means. If the grain is wrong, every total built on it is wrong.

    Conformed dimensions

    One member, one provider and one calendar dimension, shared by every fact table.

    History

    Which attributes keep history with SCD Type 2, and which are simply overwritten.

    Load pattern

    Full refresh, incremental or event-driven, decided per table.

    Physical design

    Distribution, sort, clustering and partition keys chosen from real query logs.

    Access

    Row- and column-level security for sensitive data such as PHI.

    Warehouse, lakehouse, or both

    The two work together more often than they compete.

    Choose a warehouse when

    • Most consumers are SQL and BI users
    • The data is mostly structured
    • Consistent reporting matters most

    Add a lakehouse when

    • You also hold semi-structured files and raw history
    • Data science and machine learning need the same data
    • You want open formats that several engines can read

    Many clients run a lakehouse for raw and Silver data and a warehouse layer for Gold. See sheet 06 for the lakehouse drawing.

    Where it runs

    The drawing stays the same. The services change with the environment you already run, and the feasibility analysis picks the fit.

    Environment
    What we use
    When it fits

    Amazon Redshift

    Built before
    What we useGlue, Airflow, EventBridge, materialized views, WLM
    When it fits10M+ healthcare records a year, with a 30% query performance improvement after key and view redesign.

    Snowflake

    Built before
    What we useSnowpipe, dbt, Tasks, clustering keys
    When it fitsPublishing target for curated tables, or the main warehouse.

    Databricks SQL

    What we useDelta tables, Unity Catalog, Lakeflow Jobs
    When it fitsA warehouse layer on top of a lakehouse.

    Google BigQuery

    What we useDataform or dbt, Cloud Composer, partitioning and clustering
    When it fitsServerless, pay-per-query teams.

    Microsoft Fabric or Synapse

    What we useData Factory, dbt, Power BI
    When it fitsMicrosoft-centred teams.

    Feasibility first

    Before anything is built, we check whether this system is worth building for you, and where it should run.

    • Which reports and decisions the warehouse must support first.
    • Source systems, volumes, and how fresh the data needs to be.
    • Existing models and definitions, and where teams get different numbers.
    • Platform choice based on your cloud, your team's skills and your budget.
    • Security and compliance rules for sensitive data.
    What you receiveA dimensional model for the first subject areas, the platform recommendation with running-cost estimate, a load plan, and a go or no-go recommendation.

    Built before

    Work delivered by Ashish Adhikari, who leads engineering at YoursSherpa.

    • Enterprise data warehouse on Amazon Redshift: star schema for claims, encounters and quality measures, 10M+ records a year, and a 30% query performance improvement from distribution keys, compound sort keys and materialized views.Cedar Gate Technologies
    • ETL pipelines on AWS Glue and Airflow processing 500K+ healthcare claims a day.Cedar Gate Technologies
    • Curated Gold tables published to Snowflake for downstream analytics teams.Abacus Insights

    Start with a feasibility call

    Tell us about one process or data problem. In the first call we will say which parts we would automate with code, which need an agent, and which we would leave alone.

    Send a short note through the contact form and we will set up the call.

    Contact us See Ashish Adhikari's profile

    What helps us prepare

    • The process or system you have in mind, and who works on it today.
    • Where the data lives: cloud, platform and main tools.
    • Security or hosting rules we need to work within.