Data Modeler - Data Transformation - D&A

Job Title: Data Modeler - Data Transformation - D&ALocation: Remote ( occasionally travel to Newport Beach, CA)Job Type: FTE or C2H Job Description:Experience: 10–20 years MaxTagline: Enterprise Data Modeling | Snowflake Lakehouse Design | ACORD Standards | Governed Data ProductsTech Stack Snapshot – Mandatory SkillsSnowflake, dbt (data build tool), AWS S3, Matillion, Collibra, Profisee MDM, Python, SQL, Snowflake Data Metric Functions (DMFs), WinAIDM, SnowConvert AI, ACORD Data Model, CI/CD (Azure DevOps), Git, (Insurance Domain)Please collect writeup onACORD Life & Annuity data standards — Policy, Claims, Party, Product, Actuarial domain entitiesInsurance domain knowledge across life insurance, annuities, reinsurance, or employee benefits product linesExposure to Profisee MDM or equivalent enterprise MDM platforms and how golden record schemas integrate into lakehouse layersSnowPro Core or SnowPro Advanced: Data Engineer certificationMust-Have Skills8+ years in enterprise data modeling with 3+ years leading modeling on complex, multi-source data platform programsDeep expertise in logical and physical data modeling — dimensional modeling (star/snowflake schemas), normalized models, and Snowflake-native wide-table patternsHands-on Snowflake experience — schema design, clustering strategies, materialization types, and query performance fundamentalsStrong command of dbt — translating logical model designs into modular dbt model structures, YAML schema definitions, and test coverageExperience integrating data models with a metadata governance platform (Collibra preferred) — business glossary, attribute-level lineage, and data contract definitionDemonstrated ability to produce and maintain data dictionaries, ERDs, and source-to-target mapping documents across multi-domain enterprise programsExperience in a regulated industry (insurance, healthcare, or financial services) with awareness of PII/PHI data handling and compliance requirementsACORD Life & Annuity data standards — Policy, Claims, Party, Product, Actuarial domain entitiesInsurance domain knowledge across life insurance, annuities, reinsurance, or employee benefits product linesExposure to Profisee MDM or equivalent enterprise MDM platforms and how golden record schemas integrate into lakehouse layersSnowPro Core or SnowPro Advanced: Data Engineer certificationRole SummaryWe are looking for a hands-on Data Modelling Technical Lead to own the target-state logical and physical data model for a Fortune 500 insurance and financial services enterprise’s multi-wave, enterprise-scale data modernization program. You will design and govern Snowflake-native dimensional and relational models across 9+ certified data products — spanning Policy, Claims, Finance, Actuarial, and MDM domains — and establish the ACORD-based modeling standards that the full delivery team will build on. Working directly alongside the onshore Technical Architect, you will be the authoritative voice on schema quality, naming conventions, and Collibra-aligned lineage definitions throughout the 18–24 month engagement.Why This Role MattersThe foundation of every certified data product in this program is the data model — get it right and 27 engineers build on a stable, reusable, lineage-ready schema; get it wrong and technical debt compounds across three waves and 80+ source systems. This engagement migrates 372 production database instances into a unified Snowflake cloud-native platform, and the customer’s ACORD-based enterprise model must accommodate insurance domain complexity at scale while remaining dbt-compatible and Collibra-governed. The onshore placement is intentional — direct, synchronous engagement with the customer’s domain SMEs is how modeling decisions get made fast and right.What You'll DoData Model Design & GovernanceDesign and own target-state logical and physical data models for all 9 Wave 1 certified data products: Policy & Contract Management, Premium & Billing, Claims & Benefits, Actuarial & Reserves, Financial Accounting, Agent & Distribution, Customer & Party (MDM), Product Master (RDM), and Compliance & RegulatoryEstablish enterprise modeling standards — naming conventions, schema versioning, referential integrity patterns, and data type governance — across the Snowflake Silver and Gold layersOwn ACORD Life & Annuity data model customization for the customer’s specific domain structure, translating insurance industry standards into implementable Snowflake schemasReview and approve all dbt model definitions, ensuring alignment with the approved logical model and Collibra-registered metadata contractsSnowflake Schema & Medallion ArchitectureDesign Snowflake-native dimensional, normalized, and wide-table schemas optimized for query performance, downstream BI consumption (Tableau, Power BI), and Snowflake Cortex AI workloadsCollaborate with Senior Data Engineers to align physical schema design with Matillion ingestion patterns, dbt transformation layers, and Snowflake Data Metric Function (DMF) quality coverageDefine clustering keys, materialization strategies (tables, views, dynamic tables), and schema partitioning patterns per domain to support the program’s 10–50x query performance improvement targetsDrive source-to-target mapping completeness across 347 SQL Server and 25 Oracle legacy systems, supporting the 7-year historical data migrationCollibra Integration & Data LineageDefine business glossary entities and attribute-level metadata in Collibra corresponding to each certified data product’s physical modelGovern end-to-end lineage registration — source → Matillion ingestion → dbt transformation → Snowflake Gold → Tableau/Power BI — for all modeled entitiesDefine data contracts (agreed schema, SLOs, and quality rules) for each certified data product published to the Gold layerCollaborate with the MDM/Governance Specialist and Technical Architect to ensure Profisee golden record schemas integrate cleanly into the Silver layer dimensional modelTechnical Leadership & StandardsProduce and maintain data dictionaries, ERDs, schema change management procedures, and model versioning documentation as living, version-controlled artifactsConduct model design reviews with data engineers and technical leads before sprint delivery — identifying schema drift before it becomes reworkPartner with the Data Quality / DRE Engineers to anchor DMF-based quality checks to model-level SLO definitions and data contract obligationsLeverage WinWire’s WinAIDM accelerator platform for automated schema generation, source-to-target mapping scaffolding, and transformation layer bootstrappingClient Engagement & Domain CollaborationFacilitate source-to-target mapping workshops with the customer’s domain SMEs across Policy, Claims, Finance, and Actuarial workstreams — onshore proximity enables real-time decisionsTranslate complex business data requirements from the customer’s domain analysts and data stewards into validated, implementable logical modelsSurface modeling trade-offs (denormalization vs. flexibility, performance vs. governance) as clear, decision-ready options for the Technical Architect and program stakeholdersRepresent WinWire’s modeling practice in customer-facing design review sessions and architecture steering committee presentationsWhat Success Looks Like (6–12 Months)Logical and physical data models for all 9 Wave 1 certified data products reviewed, approved, and locked by Month 2 — zero schema rework required during Wave 1 deliveryACORD-based enterprise data model customization documented and adopted as the modeling standard across all delivery workstreamsEnd-to-end Collibra lineage registered for 100% of Gold-layer modeled entities — attribute-level, not just table-levelData dictionary and source-to-target mapping documentation maintained as a current, version-controlled, and team-accessible living artifactAll Wave 1 data products meet 99.9% completeness and 99.5% accuracy SLOs, with quality rules anchored to model-defined data contractsCustomer domain SMEs describe the modeling approach as enterprise-grade, insurance-aware, and audit-traceable