AI-Powered Healthcare Data Modeling

Introduction

Healthcare data architects spend days designing schemas from scratch. A Medicare Advantage claims warehouse alone requires 20+ tables, hundreds of columns, carefully defined foreign key relationships, and platform-specific data types. Then the naming debates start: Is it patient_id or mbr_id? Is the service date column date_of_service, svc_dt, or clm_svc_from_dt?

These debates are not academic. Wrong column names fail ISO-11179 validation and create inconsistencies that cost days of remediation when regulators or auditors ask for clean documentation. Wrong data types — using FLOAT for a paid amount instead of DECIMAL(18,2) — introduce rounding errors in financial reconciliation. Wrong platform syntax means DDL that does not execute without manual edits.

While ERwin requires a complex setup for schema generation, you can generate clean DDL in seconds using our free converter.

Convert your first 5 DDLs — No Credit Card Required

What if you could skip all of that and generate a production-ready Star Schema for Snowflake in 30 seconds — with ISO-11179 standard column names built in?

The AI Data Modeling tool generates complete database schemas from a plain English description. You make five selections and write one sentence. The tool does the rest.

You select:

• Healthcare domain: Claims & Adjudication, Clinical & EHR, Pharmacy & PBM, Member Enrollment, Provider Network, Quality Measures, Finance & Revenue Cycle, or Population Health
• Output type: Logical model, Physical model, or Both
• Model architecture: Star Schema, Data Vault 2.0, Lakehouse (Delta/Iceberg), One Big Table, 3NF, MongoDB, or Cassandra
• Target platform: Snowflake, BigQuery, Databricks, PostgreSQL, SQL Server, Oracle, Amazon Redshift, or MySQL
• Plain English description of what you need

The tool generates:

• Complete DDL with CREATE TABLE statements
• ISO-11179 standard column names throughout
• Correct data types for your selected platform
• Foreign key relationships between tables
• Primary keys and surrogate key conventions
• Inline comments on every column
• Platform-specific syntax — not generic SQL renamed

🔄A Real Example — Medicare Advantage Claims Warehouse

Here is what the tool generates for a real Medicare Advantage claims warehouse request.

Input description:

“Medicare Advantage claims data warehouse for a health plan. Include claim header, claim line, member enrollment, provider, and diagnosis tables. Support HEDIS reporting and CMS risk adjustment scoring.”

📊

FREE TOOL

Calculate RAF scores with our free HCC Calculator →

Settings:

• Architecture: Star Schema
• Platform: Snowflake
• Domain: Claims & Adjudication
• Output: Physical Model

Generated DDL:

-- ── FACT TABLE ────────────────────────────────────────────────────────────
CREATE TABLE FACT_CLAIM_HEADER (
  CLM_ID           VARCHAR(50)    NOT NULL,
  MBR_KEY          INTEGER        NOT NULL,
  PRVDR_KEY        INTEGER        NOT NULL,
  PLAN_KEY         INTEGER        NOT NULL,
  SVC_FROM_DT_KEY  INTEGER        NOT NULL,
  CLM_TYP_CD       VARCHAR(10),
  TOT_CHRG_AMT     DECIMAL(18,2),
  TOT_PAID_AMT     DECIMAL(18,2),
  TOT_ALWD_AMT     DECIMAL(18,2),
  CLM_STAT_CD      VARCHAR(10),
  DIAG_CD_1        VARCHAR(10),
  DIAG_CD_2        VARCHAR(10),
  PROC_CD          VARCHAR(10),
  DRG_CD           VARCHAR(10),
  LOAD_DT          TIMESTAMP_NTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT PK_FACT_CLAIM_HEADER
    PRIMARY KEY (CLM_ID)
);

-- ── DIMENSION TABLE: MEMBER ───────────────────────────────────────────────
CREATE TABLE DIM_MEMBER (
  MBR_KEY          INTEGER        NOT NULL,
  MBR_ID           VARCHAR(50)    NOT NULL,  -- PHI: mask in non-prod
  MBR_FIRST_NM     VARCHAR(100),             -- PHI
  MBR_LAST_NM      VARCHAR(100),             -- PHI
  BIRTH_DT         DATE,                     -- PHI
  SEX_CD           CHAR(1),
  STATE_CD         CHAR(2),
  CNTY_CD          VARCHAR(10),
  ZIP_CD           VARCHAR(10),              -- PHI
  DUAL_ELIG_IND    CHAR(1),
  PLAN_ID          VARCHAR(20),
  EFF_START_DT     DATE           NOT NULL,
  EFF_END_DT       DATE,
  CURR_ROW_IND     BOOLEAN        NOT NULL DEFAULT TRUE,
  CONSTRAINT PK_DIM_MEMBER
    PRIMARY KEY (MBR_KEY)
);

-- ── DIMENSION TABLE: PROVIDER ─────────────────────────────────────────────
CREATE TABLE DIM_PROVIDER (
  PRVDR_KEY        INTEGER        NOT NULL,
  PRVDR_NPI        VARCHAR(10),
  PRVDR_FIRST_NM   VARCHAR(100),
  PRVDR_LAST_NM    VARCHAR(100),
  PRVDR_ORG_NM     VARCHAR(255),
  PRVDR_TYP_CD     VARCHAR(20),
  PRVDR_SPCLTY_CD  VARCHAR(10),
  PRVDR_STATE_CD   CHAR(2),
  PRVDR_ZIP_CD     VARCHAR(10),
  EFF_START_DT     DATE           NOT NULL,
  EFF_END_DT       DATE,
  CURR_ROW_IND     BOOLEAN        NOT NULL DEFAULT TRUE,
  CONSTRAINT PK_DIM_PROVIDER
    PRIMARY KEY (PRVDR_KEY)
);

-- ── DIMENSION TABLE: DATE ─────────────────────────────────────────────────
CREATE TABLE DIM_DATE (
  DT_KEY           INTEGER        NOT NULL,
  FULL_DT          DATE           NOT NULL,
  YR_NBR           SMALLINT,
  QTR_NBR          SMALLINT,
  MO_NBR           SMALLINT,
  WK_NBR           SMALLINT,
  DAY_OF_WK_NBR    SMALLINT,
  CONSTRAINT PK_DIM_DATE
    PRIMARY KEY (DT_KEY)
);

Key design decisions in this output:

ISO-11179 column names: The tool generates MBR_ID not member_id, PRVDR_NPI not provider_npi_number, CLM_STAT_CD not claim_status. These abbreviations come from the mdatool glossary of 100,000+ healthcare data terms. Any healthcare data engineer picks them up immediately without a decoder ring.

Surrogate keys with _KEY suffix: Dimension tables use integer surrogate keys (MBR_KEY, PRVDR_KEY) that fact tables reference. This is standard Star Schema convention — the natural business key (MBR_ID) is preserved in the dimension table but joins happen on fast integer keys.

DECIMAL(18,2) for amounts, not FLOAT: Financial columns use fixed-precision decimal types. FLOATintroduces rounding errors that cause reconciliation failures in claims financial reporting. The tool knows this distinction and applies it automatically.

TIMESTAMP_NTZ for Snowflake: Snowflake-specific timestamp type that stores no time zone offset — the correct choice for healthcare event timestamps that are already in local time at point of care. Generic SQL would generate TIMESTAMP or DATETIME, which behaves differently across platforms.

SCD Type 2 on dimensions: DIM_MEMBER includes EFF_START_DT, EFF_END_DT, and CURR_ROW_IND for slowly changing dimension tracking. Member demographics — ZIP codes, plan assignments, eligibility status — change over time, and payers need to reconstruct which coverage a member held at the time of any given claim.

Generate your own healthcare schema → mdatool.com/tools/modeling

8 Architectures Supported

The tool supports eight data model architectures, each generating structurally different DDL optimized for its intended use case.

Star Schema Best for analytics and BI reporting. Use when your team uses Tableau, Power BI, or Looker against Snowflake or Redshift. Generates fact and dimension tables with surrogate keys, foreign key relationships, and SCD Type 2 support. The right default for most health plan analytics warehouses, HEDIS measure calculation schemas, and claims financial reporting.

Data Vault 2.0 Best for enterprise data warehouses with multiple source systems and strict audit requirements. Generates hubs (business keys), links (relationships), and satellites (descriptive attributes) with hash keys and immutable append-only loading. The right choice for large health plans aggregating claims + clinical + member data from 10+ source systems where HIPAA audit trail completeness is a compliance requirement.

Lakehouse (Delta Lake / Iceberg) Best for Databricks and Spark-based workloads. Generates Bronze (raw ingestion), Silver (curated), and Gold (aggregated) medallion layer tables with Delta Lake syntax. The right architecture for population health analytics platforms, real-time eligibility streaming pipelines, and ML feature engineering for risk stratification models.

One Big Table (OBT) Best for simple analytics on small teams or single-payer environments. Generates a wide denormalized table optimized for direct query without joins. Useful for rapid prototyping or small-scale claims analysis where schema simplicity matters more than warehouse optimization.

3NF (Third Normal Form) Best for operational transactional systems, not analytics. Generates normalized relational tables without denormalization. Use when building a claims processing system (OLTP) rather than a claims analytics warehouse (OLAP). The tool generates different structures than Star Schema — no fact/dimension split, proper normalization to eliminate redundancy.

MongoDB Document Model Generates JSON schema definitions for document-oriented healthcare data, such as FHIR-native clinical records or unstructured encounter notes where flexible schema is more practical than rigid relational structure.

Apache Cassandra Generates partition-key-optimized table schemas for high-throughput write workloads, such as real-time pharmacy dispensing event streams or IoT health device data ingestion.

Tired of legacy complexity and high pricing?

mdatool offers instant DDL conversion, HL7 support, and AI-driven data modeling for a fraction of the cost of ER/Studio or ERwin.

Try mdatool for Free

8 Target Platforms Supported

Each platform gets correct, immediately executable syntax — not generic SQL with a different name at the top.

PLATFORMPLATFORM-SPECIFIC OUTPUTSnowflakeTIMESTAMP_NTZ, VARIANT, NUMBER, clustering key hints, transient table syntaxBigQueryNUMERIC, DATETIME, REPEATED fields, STRUCT types, partition and clustering clausesDatabricksDECIMAL, ARRAY, COMMENT clauses, OPTIMIZE and ZORDER hints, Delta Lake syntaxPostgreSQLSERIAL, JSONB, standard index definitions, sequencesSQL ServerIDENTITY, NVARCHAR, clustered index definitions, GO batch separatorsOracleNUMBER types, sequences, tablespace hintsAmazon RedshiftDISTKEY, SORTKEY, Redshift-specific compression encodingsMySQLAUTO_INCREMENT, ENGINE=InnoDB, VARCHAR with charset hints

If you need to convert an existing schema between platforms — for example, migrating a SQL Server claims schema to Snowflake — use the DDL Converter after generating your initial model.

ISO-11179 Naming Built In — No More Naming Debates

Every schema generated by the tool uses ISO-11179 standard healthcare column names automatically. The ISO-11179 standard defines column names as: object class + property + representation class.

The result eliminates the most common and most expensive debate in healthcare data teams.

Instead of:

patient_id date_of_birth claim_number paid_amount provider_name member_zip

You get:

MBR_ID BIRTH_DT CLM_NBR CLM_PD_AMT PRVDR_NM MBR_ZIP_CD

The tool references the mdatool glossary of 100,000+ healthcare terms to generate compliant abbreviations. MBRis the standard object class for member. DT is the standard representation class for date. AMT for monetary amount. CD for code. NM for name. NBR for number. IND for indicator.

These are not arbitrary abbreviations. They are the recognized standard in healthcare data engineering. Any certified healthcare data engineer reads CLM_PD_AMT and knows it is claim paid amount. Any NPI lookup connects to PRVDR_NPI without documentation. Any HEDIS calculation references DIAG_CD_1 without ambiguity.

For the full standard abbreviation reference, see the mdatool glossary and the ISO-11179 naming guide.

View as Diagram — Instant ER Visualization

After generating a schema, click View as Diagram to instantly visualize your data model as an interactive entity-relationship diagram.

The Data Model Canvas renders your generated schema with:

• All tables and column definitions
• Foreign key relationships drawn as connectable lines
• Color-coding by healthcare domain (claims tables, member tables, provider tables)
• Drag-and-drop table positioning to arrange by logical grouping
• Click-to-expand any table to see all columns and data types
• PDF export for architecture reviews, governance documentation, and team walkthroughs

This is the step that turns a DDL block into a shareable artifact. Paste the schema into the canvas and you have a diagram ready for a data governance review meeting in under two minutes.

See the canvas: mdatool.com/model-diagram

How to Use It — Step by Step

1. Go to mdatool.com/tools/modeling
2. Select your healthcare domain from the dropdown (e.g., Claims & Adjudication)
3. Choose Physical Model as your output type
4. Select Star Schema as your architecture (or whichever fits your use case)
5. Choose your target platform (e.g., Snowflake)
6. Write a plain English description of your data model in the text field
7. Click Generate Data Model
8. Review the generated DDL — tables, columns, data types, constraints
9. Click View as Diagram to visualize the entity relationships
10. Copy the DDL into your database tool (Snowflake Worksheet, BigQuery console, etc.)
11. Iterate: adjust the description and regenerate to refine

Free tier limits:

• 1 generation without an account — no signup required
• Free account: 3 generations per month
• Pro: 20 generations per month + unlimited canvas exports and PDF export

Real Use Cases

Healthcare data teams use the AI Data Modeling tool across every segment of the industry.

Medicare Advantage health plans Designing a claims warehouse from scratch for a new MA plan is a 3–4 week project for an experienced data architect. The tool generates the core Star Schema in 30 seconds — fact tables for claims headers and lines, dimension tables for members, providers, plans, and dates, with HEDIS-ready table structures and HCC risk adjustment support. See the HCC risk adjustment guide for how these tables connect to CMS risk scoring. For NPI-related provider data, see the NPI reference.

Pharmacy Benefit Managers PBM data models have distinct schema requirements — NDC codes, formulary tier tables, days-supply calculations, and medication adherence metrics. Selecting the Pharmacy & PBM domain generates columns like NDC_CD VARCHAR(11), DAYS_SPLY_CNT INTEGER, REFILL_NBR SMALLINT, and FORMULARY_TIER_CD VARCHAR(10) — the right names and types without manual lookup.

Hospital systems Clinical data warehouses for hospital systems need EHR integration schemas, quality measure reporting tables, and encounter-level clinical data models. The Clinical & EHR domain generates LOINC code columns, place-of-service codes, DRG tables, and CMS-aligned encounter grains.

Provider networks Provider master data models require credentialing tables, specialty hierarchies, network adequacy schemas, and NPI registry integration. The Provider Network domain generates the right structure for multi-specialty provider directories.

Health IT vendors Vendors building healthcare analytics platforms for multiple clients use the tool to generate client-specific data models rapidly — adapting the same base schema for different platforms (one client on Snowflake, another on BigQuery) by changing only the platform selector. Platform migration projects use it alongside the DDL Converter to translate existing schemas between database platforms.

Try the AI Data Modeling tool free → mdatool.com/tools/modeling