Data Warehouse Subject Matter Expert

Diaconia; Merrifield (VA); On-site; USD 150,000 - 220,000; Full time

Job summary

Diaconia is seeking a senior Data Warehouse Subject Matter Expert to lead the Client's Enterprise Data Modernization Program (Vortex) in a large SQL Server environment. This role guides data warehouse development, ETL sustainment, modeling, and production support across a federal-scale data ecosystem.

The candidate will coordinate with Power BI, SSRS, APIs, AI/ML enrichment, and reporting apps, while driving modernization planning, data quality, security, and release execution.

Qualifications

• Experience leading enterprise data warehouse teams with SQL Server.
• Strong dimensional modeling and ETL development skills.
• Experience in production support, data quality, and release execution.
• Familiarity with reporting tools like Power BI/SSRS/SSAS.

Responsibilities

• Lead EDW development, sustainment, modernization, and production support.
• Provide hands-on technical leadership for EDW design, ETL, ingestion, validation, and performance tuning.
• Maintain Kimball-style dimensional structures and data models.
• Design and maintain SSIS packages, T-SQL scripts, stored procedures, and indexes.
• Coordinate releases, data validation, rollback planning, and documentation.
• Mentor engineers, DBAs, and analysts on EDW standards and best practices.

Skills

SQL Server EDW leadership

Dimensional modeling

ETL development

Data quality

Production support

Education

Bachelor's degree + 10+ years relevant experience

Tools

SSIS

SSRS

SSAS

Power BI

T-SQL

SQL Server

Job description

Full-time

Diaconia is seeking a Data Warehouse Subject Matter Expert to support the Client's Enterprise Data Modernization Program (Vortex). This is a proposal-critical Key Personnel role responsible for leading Enterprise Data Warehouse execution across a large, mission-critical SQL Server-based data environment supporting Client reporting, SearchPlus, APIs, AI/ML enrichment, and mission-user data access.

The successful candidate must be more than a traditional SQL Server DBA. This role requires a senior EDW leader who can guide data warehouse development, ETL sustainment, dimensional modeling, production support, performance tuning, data quality, release execution, modernization planning, and technical coordination across a complex federal data ecosystem.

Key Responsibilities

• Lead the EDW team responsible for development, sustainment, modernization, troubleshooting, and production support of Client's enterprise data warehouse environment.
• Provide hands‑on technical leadership for SQL Server EDW design, dimensional modeling, ETL development, data ingestion, data validation, performance tuning, release support, and operational reliability.
• Maintain and enhance Kimball‑style dimensional structures, including fact tables, dimension tables, star schemas, conformed dimensions, surrogate keys, slowly changing dimensions, and reporting‑ready data models.
• Design, develop, review, troubleshoot, optimise, and maintain SSIS packages, T‑SQL scripts, stored procedures, functions, triggers, indexes, jobs, views, and other EDW database objects.
• Support recurring production releases, including release planning, deployment coordination, regression testing, data validation, rollback planning, documentation, and post‑release verification.
• Lead root‑cause analysis for EDW defects, data quality issues, ETL failures, report discrepancies, performance degradation, job failures, and production incidents.
• Coordinate EDW work with downstream consumers, including Power BI, SSRS, enterprise reporting, SearchPlus/OpenSearch, APIs, AI/ML models, dashboards, and mission‑user applications.
• Support modernization planning for EDW architecture, cloud readiness, metadata, lineage, ELT, data quality, data observability, automation, and API‑enabled data access.
• Develop and maintain source‑to‑target mappings, ETL documentation, data dictionaries, data lineage artifacts, data validation procedures, operational runbooks, and technical design documentation.
• Support performance optimisation across large SQL Server databases, including indexing, partitioning, query tuning, statistics maintenance, execution plan analysis, storage optimisation, and workload monitoring.
• Support high availability, disaster recovery, backup/recovery, failover testing, security patching, access controls, STIG/security compliance, and production database reliability.
• Support Agile or Kanban delivery, including backlog refinement, sprint planning, daily standups, technical estimation, demonstrations, retrospectives, and status reporting.
• Mentor data engineers, ETL developers, DBAs, reporting developers, and analysts on EDW standards, development patterns, data quality practices, and production support expectations.

Minimum Required Qualifications

• Active U.S. government Secret clearance or higher required at proposal submission.
• U.S. Citizenship required.
• SME labor‑category compliant education and experience: Bachelor's degree with 10+ years of relevant experience; less than a Bachelor's degree requires 14+ years of relevant experience; Master's degree or higher may qualify with 8+ years of relevant experience.
• For this role, Diaconia is seeking 15+ years of total database, data warehouse, data engineering, or related data management experience due to the scale and complexity of the Client's Vortex environment.
• 12+ years of hands‑on SQL Server experience.
• Experience supporting large SQL Server databases or enterprise data warehouse environments, preferably 5 TB or larger, billion‑row scale, or hundreds of tables.
• Experience leading or serving as a senior technical SME for an enterprise data warehouse team, data engineering team, ETL team, or production database/data platform team.
• Hands‑on experience with SQL Server EDW development, administration, performance tuning, data modelling, ETL, production support, and release execution.
• Experience with Kimball‑style dimensional modelling, including fact/dimension design, star schemas, conformed dimensions, surrogate keys, slowly changing dimensions, and reporting‑ready data marts.
• Experience with SSIS, SSRS, SSAS, Power BI, T‑SQL, SQL Agent jobs, stored procedures, indexes, views, triggers, functions, and SQL Server performance tools.
• Experience developing, maintaining, or troubleshooting ETL pipelines and data transformation workflows across multiple source systems.
• Experience creating or maintaining source‑to‑target mappings, data dictionaries, ETL specifications, lineage documentation, data validation rules, and technical design documentation.
• Experience working in Agile, Scrum, or Kanban environments with recurring releases and production support responsibilities.
• Experience supporting security, access controls, auditing, data integrity, backup/recovery, HA/DR, and compliance in a federal or similarly regulated production environment.
• Strong written and verbal communication skills, with the ability to explain technical risks, tradeoffs, data quality issues, and modernization recommendations to technical and non‑technical stakeholders.

Preferred Qualifications

• Current or prior federal, law enforcement, public safety, homeland security, intelligence, financial‑crimes, investigative, or sensitive federal mission experience.
• Experience with EDW environments comparable to client Vortex scale: 25+ source systems, 200+ ETL workflows, hundreds of tables, tens of terabytes of data, and recurring monthly or more frequent releases.
• Experience with Python, R, PowerShell, or automation scripting for data validation, ETL support, operational monitoring, testing, or data quality analysis.
• Experience with cloud data modernization using AWS, Azure, Azure Government, SQL Server on cloud infrastructure, cloud storage, data lake, lakehouse, Snowflake, Databricks, Azure Data Factory, AWS Glue, or similar tools.
• Experience with metadata management, data lineage, data quality tooling, data cataloguing, data observability, data governance, or master/reference data management.
• Experience integrating EDW outputs with Power BI, SSRS, Power BI Report Server, enterprise reporting portals, APIs, SearchPlus/OpenSearch, ElasticSearch/OpenSearch indexes, or AI/ML enrichment pipelines.
• Experience supporting FedRAMP, FISMA, NIST, STIG, CUI, Privacy Act, law enforcement sensitive, or similarly controlled data environments.
• Experience with CI/CD, DevSecOps, automated testing, database deployment automation, version control, code review, and release management for database/ETL assets.
• Microsoft SQL Server, Azure Database Administrator, Azure Data Engineer, AWS, Snowflake, Databricks, DAMA, or related certifications.

$150,000 - $220,000