Job Description
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).
• Define the Bronze/Silver/Gold (layered) architecture approach for data landing, standardization, and curation: the Contractor shall identify which Lakehouse capabilities will be delivered within the base period
and which will be proposed as options.
• Produce a Security Design Summary addressing data classification and controls for sensitive information (PII and confidential data), including encryption at rest and in transit, Key Vault key/secret management,
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.
• Create a prioritized product backlog and sprint plan aligned to the milestone schedule in Section 3.
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.
• Daily ingestion from the Pathways Azure-hosted reporting database (cross-tenant) into the Agency landingbzone, including incremental loads and secure credential handling via Key Vault.
• Implement transformation and curation processes to produce a reporting-ready star schema in an Azure database service suitable for enterprise reporting (e.g., Azure SQL or equivalent in Azure Government).
• Implement core conformed dimensions and facts required for initial reporting, based on Agency prioritiesband available source data. This modeling should incorporate data from both sources into a common model.
• Data quality and reconciliation checks sufficient to validate record counts, key relationships, and critical measures between source systems and curated outputs.
• Solution must include Power BI connection to the curated Azure database; if an on-premises data gateway or a virtual network data gateway pattern is required in the Agency’s environment, the Contractor shall include configuration and validation as part of the implementation.
• The Contractor shall address the reverse-feed (warehouse-to-on-premises) requirement by either:
(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)