Data Warehouse Specialist

Role : - Enterprise Data Warehouse

Location : - Jackson, MS

Mandatory Requirements │ Scope of Work & Deliverables

Scope of Services

The Contractor shall provide qualified personnel, expertise, and resources to perform the following services :

A. Discovery and Planning The Contractor shall conduct a project kickoff meeting onsite with MDCPS stakeholders on March 2, 2026 (or as

otherwise approved by MDCPS) and confirm working agreements, governance, and sprint cadence.

This phase should include the following :

• Review and operationalize Agency-provided planning and architecture artifacts, confirm assumptions, and document any gaps requiring Agency decisions.
• Develop an Architecture Overview and implementation plan consistent with an Azure Government ADLS Gen2 landing zone and a curated reporting database using a star schema.
• Define and document the preferred secure pattern to move data from the on-premises SQL Server database to Azure Government ADLS Gen2 over VPN (e.g., self-hosted integration runtime or gateway pattern), including encryption in transit, secrets management, and network restrictions.
• Define and document a cross-tenant access approach for Pathways ingestion that enforces least privilege and network controls (e.g., private networking where feasible, approved firewall rules, service

principals / managed identities as applicable, and Key Vault-managed secrets).

and which will be proposed as options.

RBAC via Microsoft Entra ID, logging and auditing, secure networking, vulnerability management, data masking where appropriate, retention, backup / restore, disaster recovery considerations, and change control.

B. Build and Configuration

The Contractor shall implement the Azure Government landing zone ingestion patterns required for both data sources, including folder / container conventions, data lineage considerations, and access controls. In addition, the implementation shall include ingestion pipelines / ELT processes from on-premises SQL Server into the landing zone over VPN using the selected secure pattern, including incremental loads and restorability.

(a)implementing a tested reverse-feed for selected curated tables within the base period, or (b) proposing it as a priced option with clear technical approach, scope boundaries, and test plan. The Contractor’s proposal

shall not omit the reverse-feed requirement.

C. Solution Testing The Contractor shall develop and execute a test plan that includes connectivity testing, security validation, performance testing, data reconciliation, and failure recovery testing. This includes validation and monitoring of VPN-based connectivity and throughput for on-premises ingestion (on-premises SQL Server to ADLS Gen2) and document results, bottlenecks, and recommended tuning. Cross-tenant connectivity and ingestion shall be validated and monitored for the Pathways source (Pathways Azure database to ADLS Gen2) and document least-privilege

access verification, network restriction validation, and ingestion performance.

The testing shall validate encryption in transit and at rest, RBAC / least privilege enforcement, Key Vault usage, logging / auditing configuration, and secure networking controls. This includes Power BI connectivity to the curated Azure database shall be validated representative query performance and refresh behavior. In addition, recovery testing will be included with, at a minimum, the following : restorable loads, transient failures, and restoration from backup for the curated database.

All recovery testing outcomes to be documented. Should the rollback / recoverable feed be implemented in the base period, required testing shall include the reverse- feed path with data validation and integrity testing for the curated outputs. (The format and destination of the

rollback feed TBD)