YoursSherpa
Contact us

SystemsSheet 02 of 08

Data engineering automation with a private LLM

Four jobs repeat with every new data feed: ingesting it, mapping it to your Silver standard, validating it, and fitting it into a standard data model (SAM). We automate each one in code. An LLM hosted inside your network, with no public access, drafts the parts that used to be written by hand, and your engineers approve them.

Automates
Ingestion, Silver mappings, validation, SAM creation
Model hosting
Private endpoint, no public internet access
Runs on
Databricks, AWS, Azure, Google Cloud, Snowflake

Medallion automation reference architecture

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
    Medallion automation reference architecture

    A Medallion pipeline where code does every load, and an LLM inside your network drafts parsers, mappings, rules and models for people to approve.

    Delivered on Databricks on AWS: Auto Loader, Delta Lake and Unity Catalog, with the model on a private Model Serving endpoint.

    See it run

    The mapping suggester has read a new partner feed. The steward approves, edits or flags each proposal, code is generated from the approved config, and the validation gate runs.

    Mapping reviewFeed partner_837_v2 to silver.claims_lineIllustrative example
    Source columnSampleProposed Silver columnTransformationConfidenceStatus
    SBR_MBR_IDA0042 17member_idstrip spaces, upper
    0.98
    		Approved
    SVC_FR_DT20260214service_start_dateto_date(yyyyMMdd)
    0.97
    		Approved
    DX_CD_1M5416diagnosis_code_primaryICD-10 format: M54.16
    0.93
    		Approved
    RNDR_PRV_NPI1467582031rendering_provider_npiLuhn check
    0.96
    		Approved
    PAID_AMT000012550paid_amountimplied 2 decimals: 125.50
    0.88
    		Edited
    PLN_CDHMOX2plan_codecrosswalk plan_map_v3
    0.64
    		Needs review

    4 approved, 1 edited by the steward, 1 waiting for review. Approved mappings are saved as config version 14.

    silver = (bronze
  .withColumn("member_id", F.upper(F.regexp_replace("SBR_MBR_ID", r"\s", "")))
  .withColumn("service_start_date", F.to_date("SVC_FR_DT", "yyyyMMdd"))
  .withColumn("diagnosis_code_primary", icd10_format("DX_CD_1"))
  .withColumn("rendering_provider_npi", npi_checked("RNDR_PRV_NPI"))
  .withColumn("paid_amount", (F.col("PAID_AMT").cast("long") / 100).cast("decimal(12,2)"))
)  # generated from mapping config v14, approved by J. Rivera

    Validation run Passed with warnings

    Rows checked1,204,388
    Quarantined318with reasons
    • member_id is not null100%
    • diagnosis codes are valid ICD-1099.98%
    • NPI passes Luhn check100%
    • service_start_date not in the future318 failed
    • paid_amount within plan limits100%

    Rule drafter proposed 2 new checks from this batch's profile. Waiting for engineer approval.

    Model: private endpoint in your VPC. 11 prompts logged, 0 left the network.

    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. New partners send data in whatever format they use. Healthcare feeds alone arrive as X12, HL7, XML, fixed-width files and spreadsheets.

      • EDI X12 837, 835, 834 Your existing system
      • HL7 and FHIR Your existing system
      • CSV, Excel, JSON Your existing system
      • Database extracts Your existing system

      Typical toolingSFTP, S3, ADLS, partner APIs, database change data capture

    2. Files are picked up as they land and loaded without hand-written loaders. When a layout is new, the LLM reads a sample and the partner's specification and drafts a parser configuration. An engineer approves it once, and from then on the load is pure code.

      • File arrival trigger Deterministic code
      • Layout reader AI step
      • Parser config Deterministic code

      Typical toolingDatabricks Auto Loader, AWS Glue, EventBridge, Airflow, config-driven parsers

    3. Raw data is kept exactly as received, with ingestion time, source system and file hash on every row. A schema registry catches upstream changes before they break anything downstream.

      • Raw Delta tables Deterministic code
      • Audit columns Deterministic code
      • Schema registry Deterministic code

      Typical toolingDelta Lake, Unity Catalog, schema registry

    4. The LLM compares source columns, sample values and your data dictionary with the Silver standard, and proposes column mappings, transformations and code-set crosswalks with a confidence score. A data steward approves or edits each one. Approved mappings are stored as versioned config, and the mapping engine generates the PySpark or SQL.

      • Mapping suggester AI step
      • Steward approval Human review
      • Mapping engine Deterministic code

      Typical toolingVersioned YAML mappings, PySpark, SQL, SCD Type 2, ICD-10, CPT, HCPCS and NPI standardisation

    5. Every load passes expectation checks before promotion. The LLM drafts new rules from data profiles and the data dictionary, and engineers keep the ones worth keeping. Rows that fail go to quarantine with the reason attached.

      • Rule engine Deterministic code
      • Rule drafter AI step
      • Quarantine Deterministic code

      Typical toolingGreat Expectations, Lakeflow (DLT) expectations, dbt tests, Soda

    6. For a new standard data model, the LLM drafts entities, keys and relationships from the requirements and the profiled sources, including a gap analysis against target specifications such as FHIR resources. Code generates the DDL and documentation, and an architect signs off.

      • Model drafter AI step
      • DDL and docs generator Deterministic code
      • Architect sign-off Human review

      Typical toolingFHIR, CMS-9115-F and CMS-0057-F gap analysis, entity-relationship modelling, generated DDL

    7. Business tables are built from Silver and published to the warehouse and BI tools, with lineage from each Gold column back to the raw file.

      • Business aggregates Deterministic code
      • Warehouse publish Deterministic code
      • Lineage Deterministic code

      Typical toolingDelta Lake, Snowflake, Redshift, Delta Sharing, Unity Catalog lineage

    Deterministic codeAI stepHuman reviewYour existing system
    Sheet02 of 08
    Layers7
    SystemData engineering automation
    Drawn byA. Adhikari
    IssuedSeptember 2026
    ScaleNot to scale

    Four automations, each with an agent assist

    Code does the work every time. The LLM drafts what used to be written by hand, and a named person approves it.

    JobAutomated in codeWhere the agent helpsWho approves
    IngestionEvent-driven pickup, config-driven parsers, raw loads into Bronze with audit columns on every row.Reads a sample of a new file and the partner's specification, then drafts the parser config and landing schema.Data engineer, once per new layout
    Silver mappingsApproved mappings applied from versioned config, SCD Type 2 history, code-set standardisation.Proposes source-to-target column mappings, transformations and crosswalks, with a confidence score and its reasoning.Data steward, per mapping change
    ValidationExpectation suites on every load, failures quarantined with reasons, quality reports.Drafts new expectations from data profiles and the dictionary, and explains in plain language why a batch failed.Data engineer, per rule
    SAM creationDDL, documentation and tests generated from the approved model.Drafts entities, keys and relationships, and runs a gap analysis against the target specification.Data architect, per model version

    The model never leaves your network

    Healthcare, finance and public-sector data often cannot be sent to a public AI service. We host the model where the data already is.

    Where the model runs

    • Private endpoints: Databricks Model Serving, Amazon Bedrock through VPC endpoints, Azure OpenAI with private endpoints, or Vertex AI through Private Service Connect.
    • Fully private: open-weight models served with vLLM on your own GPUs or Kubernetes cluster, with no outbound internet access.

    How it stays safe

    • The model only proposes. Deterministic code executes, so a wrong suggestion cannot change data until someone approves it.
    • The model sees profiles, metadata and the samples you allow. PHI and PII can be masked first.
    • Prompts, responses and approvals are logged to your own tables for audit.

    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

    Databricks on AWS

    Built before
    What we useAuto Loader, Delta Lake, Unity Catalog, Model Serving, Lakeflow Jobs
    When it fitsHealthcare standardisation pipelines ran here. The natural home for Medallion pipelines with governed model serving.

    AWS

    Built before
    What we useGlue, Airflow or Step Functions, EventBridge, Lambda, Bedrock through VPC endpoints
    When it fitsIngestion and orchestration on AWS-native services.

    Snowflake

    Built before
    What we useSnowpipe, Snowpark, Cortex AI functions
    When it fitsPublishing target for Gold tables, or the whole pipeline when your data already lives there.

    Azure

    What we useData Factory, Fabric or Azure Databricks, Azure OpenAI with private endpoints
    When it fitsThe same design on Azure-native services.

    Google Cloud

    What we useDataflow, BigQuery, Vertex AI with Private Service Connect
    When it fitsThe same design on Google-native services.

    Private cloud or on-premises

    What we useSpark on Kubernetes, vLLM with open-weight models
    When it fitsEnvironments with no public cloud AI allowed.

    Feasibility first

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

    • Which feeds you receive, in which formats, and how often new layouts appear.
    • Whether a Silver standard or data dictionary exists, and how mappings are kept today.
    • Where the model can be hosted under your security rules, and which data it may see.
    • Which validation rules exist, and where data quality problems are found today.
    • Which standard data models or regulatory specifications you need to meet.
    • Which steps stay manual because the risk of automating them is too high.
    What you receiveA written plan covering the four automations, the model hosting design with its network boundary, the approval points, and a go or no-go recommendation.

    Built before

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

    • Healthcare data standardisation on Databricks and AWS for US payer data: Medallion architecture, SCD Type 2, Great Expectations gates and Snowflake publishing. Pipelines handled 500K+ claims a day, with 85% fewer data quality issues after layered validation.Abacus Insights and Cedar Gate Technologies
    • Standard data models (SAMs) and element-by-element gap analysis for CMS-9115-F and CMS-0057-F interoperability programmes.Healthcare interoperability
    • Parsers for EDI X12 834, 820, 837 and 835 transactions, and seven file formats standardised into analytics-ready tables.Healthcare data engineering

    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.