Hello All,
I hope you're doing well.
Role: Lead Data Engineer - SQL Server / ETL / NEIEN XML / Migration
Location: U.S.-based / onshore. Most work may be performed from the Supplier's location, with onsite presence at Virginia DEQ in Richmond, VA, at least 2 days per week as required.
Role Type: Core / Full-time
RFP Number / Solicitation No: SOR # DEQ-260901-01-CAI
Department / Agency: Virginia Department of Environmental Quality
Experience: 8+ years in data engineering; strong experience with SQL Server, Oracle, XML, and ETL
Role Purpose
- Own the data architecture, migration, validation/transformation implementation, regulatory XML generation, reference-data configuration model, and data reconciliation for the BMP Warehouse rebuild. This role is central because the system's mission is to receive, validate, transform, retain, and submit environmental BMP data reliably.
Key Responsibilities
- Analyze the legacy Oracle schema, current BMP Warehouse data dictionary, existing code tables, transformation logic, views/processes, and historical data quality issues; map them to the SQL Server 2022 target model.
- Design the normalized/fit-for-purpose SQL Server data model with the Solution Architect and DEQ DBA, including audit/history, organization ownership, inspections, lifecycle status, transformations, submissions, mappings, and integration metadata.
- Develop the migration/conversion strategy, mapping specifications, cleansing rules, reconciliation approach, trial conversion process, rollback/repair strategy, and final migration plan.
- Implement data validation logic in the appropriate application/data layer for controlled vocabularies, numeric thresholds, date relationships, duplicate detection, cross-field rules, and other DEQ-approved rules.
- Design and implement configurable transformation/reference structures for VACS-to-NEIEN mapping, formulas, Send If conditions, BMP name mappings, NRCS/Clearinghouse crosswalks, land-use/locality/HUC references, credit durations, and practice lifespans.
- Implement NEIEN NPS BMP XML generation aligned to the required schema, including large-batch generation, configurable caps, resubmission support, schema validation, and error diagnostics.
- Support GRTS/GRTS-WPT XML/output requirements and annual progress reporting datasets as finalized with DEQ.
- Implement ETL activity logging with counts/error details, auditability for code-table changes, and reconciliation controls across import, transformed, migrated, and submitted data.
- Optimize SQL queries, indexes, data-access patterns, bulk operations, exports, and transformation performance for expected volumes and concurrency.
- Collaborate with QA to build representative data sets, boundary cases, duplicate scenarios, invalid records, migration reconciliation tests, XML/schema tests, and performance benchmarks.
- Provide data cutover support, production verification, DBA/operations knowledge transfer, and as-built data documentation.
Minimum / Required Profile for Sourcing
- 8+ years of data engineering/database development on transaction-heavy or regulatory systems.
- Expert SQL Server skills: data modeling, T-SQL, stored procedures where appropriate, indexing, query tuning, bulk operations, transaction design, and troubleshooting.
- Strong Oracle experience sufficient to analyze and migrate a legacy Oracle-backed application.
- Hands-on ETL/data transformation experience with complex rules, crosswalks, reference tables, validation, reconciliation, and auditability.
- Strong XML/XSD experience, including schema validation and generation of standards-driven XML payloads.
- Experience designing repeatable data migrations with profiling, cleansing, trial loads, reconciliation, defect correction, and cutover validation.
- Ability to work directly with business analysts/SMEs to translate data rules into deterministic technical logic.
Preferred Skills
- Experience with NEIEN/EPA Exchange Network, environmental reporting XML, GRTS, or comparable regulatory XML submissions.
- Experience with large spreadsheet/file ingestion and provider-facing validation.
- Experience with spatial columns/data and collaboration with GIS systems.
- Experience modernizing Oracle/.NET Framework systems to SQL Server/modern .NET.