Senior ETL Developer to re-envision Med Transport data structures, and integrate new web-based app into Health Benefits SQL data warehouse - #2024PQVQ-01 - May 11 - 1

Job Type: Contract
Positions to fill: 1
Start Date: Jun 01, 2023
Job End Date: Dec 31, 2023
Pay Rate: Hourly: Negotiable
Job ID: 129627
Location: Vancouver
S.i. Systems public health client is seeking a Senior ETL Developer to re-envision Med Transport data structures, and integrate new web-based app into Health Benefits SQL data warehouse. 

6 month contract
Due to the sensitive nature of the information that will be handled by the contractor, it is expected that more than 90% of work will be conducted in the office setting/in-person. Primary Office location will be our downtown office location at 1166 Alberni Street, Vancouver, BC .

Must be COVID Vaccinated

  • 10+ years working knowledge and proven hands-on experience and expertise with:
    • Developing Extract-Transform-Load (ETL) 
    • tuning strategies of long running queries, Data Quality, Data Cleansing.
    • Microsoft SQL Server T-SQL, SQL Server Integration Services (SSIS), and SSMS (SQL Server Management Studio).
    • MS Visual Studio with SSDT.
    • SQL Agent including creating/configuring/monitoring jobs.
    • Data warehousing concepts and principles.
    • MS SQL Server databases, including stored procedures, indexes, joins, merge join, views, common table expressions (CTE), and execution plans.
    • implementation of master data projects.

  • Assess the upstream Medical Transformation Information System data and downstream reporting requirements.
  •  Architect the Medical Transportation Data Mart in  Data warehouse
  • Develop an ETL process from the upstream Medical Transportation system to the downstream Data warehouse.
  • Data warehouse needs and recommendations for solution delivery, including (proposed) solution architecture, delivery plan and 5-year cost forecast.
  • Transition solution and acquired technical/maintenance knowledge to IMIT as the application owner.
  • Prepare MTIS data for integration with other (Data warehouse-held) Health Benefits data

  • For many First Nations people living in rural or remote areas, it is challenging to access health care services. FNHA supports client access to medically necessary health services unavailable in their community. The Medical Transportation program assists clients with the cost of meals, accommodation, and transportation they incur when travelling to medical appointments outside their community. The program is available to status First Nations clients living in British Columbia.
  • FNHA is transforming the Medical Transportation (MT) program through the design/build/deployment of Kucén, a web-based application that will serve as a single point of access for communities to book and coordinate medical transportation for their clients and members. The Kucén system will be available for Funding Agreement holding communities to use, and when the communities onboard to the system, it will replace their use of various legacy MT systems.
  • The Kucén system will collect details associated with all booked medical travel. FNHA will require this data to be collected, transmitted, and integrated into the existing Health Benefits SQL data warehouse. This will require re-envisioning the Medical Transportation data structures and presentations through the Health Benefits data warehouse.
  • You will integrate the new Kucén system into the Health Benefits data warehouse.
  • You will architect the database and build/test the ETLs necessary to batch Kucén-collected data to the data warehouse.
  • Once the database structure and ETLS are established and operational, the resource will develop all support documentation and transition ETL support to FNHA’s IMIT department.