Senior Database Architect/ SQL / Oracle Subject Matter Expert (SME)

Job Title: Senior Platform Developer

Work Location: Remote

Description:

• Act as the SQL / Oracle Subject Matter Expert (SME), providing authoritative technical analysis across complex legacy relational database environments.
• Perform comprehensive legacy system analysis, including reverse-engineering database schemas, relationships, constraints, indexes, and embedded business logic across SQL Server, Oracle, and MySQL platforms.
• Develop and maintain high-fidelity documentation of current-state ( as-is ) data architectures, including data models, lineage, dependencies, and integration touchpoints.
• Create and validate conceptual, logical, and physical data models in alignment with 3NF and dimensional modeling standards using enterprise modeling tools.
• Author and analyze advanced ANSI SQL to support data profiling, validation, reconciliation, lineage assessment, and performance evaluation.
• Assess data quality, structural integrity, and performance characteristics of legacy databases, document risks, gaps, and optimization opportunities with clear technical recommendations.
• Partner with solution and enterprise architects to support future-state data architecture planning, modernization assessments, and migration strategies.
• Translate detailed legacy-state findings into actionable inputs for architectural decision records (ADRs) and long-term platform roadmaps.
• Communicate complex technical findings clearly and concisely to cross-functional stakeholders through documentation-first deliverables, workshops, and reviews.
• Operate in a documentation-first, analysis-driven role with minimal to no hands-on application development, focusing on technical advisory and architectural enablement.

Primary Responsibilities

Data Modeling & Architecture

• Design and develop conceptual, logical, and physical data models for both greenfield initiatives and legacy system modernization efforts, ensuring alignment with business processes and technical architecture standards.
• Create and maintain Entity-Relationship Diagrams (ERDs) using industry-standard tools such as Erwin and PowerDesigner, including reverse engineering of existing databases to document undocumented or inherited data structures.
• Define entity attributes, primary and foreign keys, relationships, normalization standards, constraints, and referential integrity rules to ensure consistency and data accuracy across systems.

Relational & Dimensional Database Design

• Architect robust relational database schemas for enterprise platforms including Microsoft SQL Server, Oracle, and MySQL, supporting high-volume transactional workloads.
• Design dimensional data models using star and snowflake schemas to enable efficient analytics, reporting, and business intelligence solutions.
• Partner with reporting and analytics teams to ensure data models meet performance, usability, and extensibility requirements.

Data Profiling, Quality & Lineage Analysis

• Conduct comprehensive data profiling and data quality assessments by writing complex ANSI SQL queries to analyze data distributions, detect anomalies, identify data integrity issues, and validate business rules.
• Analyze data relationships across multiple source systems to establish authoritative sources of truth and clearly defined ownership.
• Document data lineage, metadata, and source-to-target mappings to support governance, auditability, and downstream system integration.

Database Performance & Optimization

• Optimize database performance through strategic index design, query tuning, and metadata management across heterogeneous database platforms.
• Diagnose and resolve database design and performance issues by reviewing execution plans, storage strategies, and schema configurations.
• Recommend and implement architectural enhancements and operational improvements to support scalability, reliability, and long-term maintainability.

Key Deliverables

Data Modeling Artifacts

• Comprehensive conceptual, logical, and physical data models, including ERDs with fully defined entities, attributes, relationships, constraints, and business definitions.
• Reverse-engineered models of legacy systems with clear documentation to support modernization and migration initiatives.

Database Implementation Assets Complete database implementation scripts, including:

• DDL for table and schema creation
• Stored procedures, views, functions, and triggers
• Comprehensive indexing strategies aligned with workload patterns

Data Quality & Governance Documentation

• Data quality assessment reports detailing profiling results, baseline metrics, and identified remediation recommendations.
• Data lineage diagrams, metadata repositories, and source-to-target mapping documents to support governance, compliance, and integration efforts.

Performance & Capacity Optimization Deliverables

• Detailed performance optimization reports, including index recommendations, query tuning analyses, and database tuning scripts.
• Capacity planning and scalability analyses to support future growth and evolving business demands.

Required Skills & Expertise

• Strong expertise in data modeling methodologies (conceptual, logical, physical; 3NF; dimensional modeling).
• Advanced proficiency in ANSI SQL and relational database design principles.
• Hands-on experience with SQL Server, Oracle, and MySQL database platforms.
• Extensive experience using Erwin, PowerDesigner, or equivalent modeling tools.
• Proven ability to analyze, optimize, and troubleshoot complex database environments.

Preferred Qualifications

• Experience supporting large-scale government or regulated enterprise systems.
• Familiarity with data governance, metadata management, and compliance frameworks.
• Strong documentation skills with the ability to translate complex technical concepts into clear, consumable artifacts for technical and business stakeholders.