Etl Tester

**Position: Business Intelligence Specialist - ETL Tester**

**Location: 315 Front Street West, Toronto**

**Security Level: CRJMC**

Must haves:
7+ years in ETL tools such as Microsoft SSIS, stored procedures (Must Have)

2+ Azure Data Lake and Data Warehouse, and building Azure Data Factory pipelines (Must Have)

2+ years Python (Must Have)

Description

General Responsibilities

Responsible for test planning and preparation including full documentation as writing test plan, test cases, and execution of test plan.

Design, develop and implement test plan and test cases from Oracle source to Azure Data Lake - initial load and incremental ETL. Used tools are:
Azure Data Factory (expert knowledge) to validate pipeline from Oracle to Azure Data Lake

Azure Synapse to build stored procedures and read data from data lake

Review the requirements, database tables, and database relationships - Identify gaps and inefficiencies in current production reporting environment and provide recommendations address them in the new platform.

Continue to evolve and design test framework and CDC

Prepare test design artifacts

Analysis of data - physical model mapping from data source to reporting destination.

Understand the requirements. Recommend changes to the Physical model.

Develop the scripts to test physical DB.

Access Oracle DB environments, use SSIS, SQL Server and other development tools for developing solution.

Proactively communicate with business & dev team about test plan, design, communicate and review dependencies and risks.

Development of ETL test strategy and solution based on different set of modules

Understand the Tables and Relationships.

Create test design documents and test cases.

Development and testing of data with Incremental and Full Load.

ETL data from Applications to Data Warehouse

ETL data from Data Warehouse to Data Mart

Perform different methods of testing

Performance Review, data Consistency checks

Troubleshoot performance issues, ETL Load issues, log activity for each Individual package and transformation.

Review Performance of ETL Overall.

End to end Integrated testing for Full Load and Incremental Load

Provide QA sign-off once test cycle is completed for deployment

Knowledge Transfer to Ministry staff, development of documentation on the work completed.

Document share and work on the ETL end to end testing knowledge base, Troubleshooting steps, configuration and scripts review.

Transfer documents, scripts and review of documents

Experience and Skill Set Requirements

Please note this role is part of a Hybrid Work Arrangement and resource(s) will be required to work at a minimum of 3 days per week at the Office Location.

**Experience**:
Design & develop the test plan & test cases for Data warehouse, Datamart & Data Lake projects

Experience in testing data validation scenarios and Data ingestion, pipelines, and transformation processes (e.g.,ETL).

Experience in writing complex SQL queries, ability to determine the types of testing that must be conducted (i.e, data validation, regression, etc.), including evaluating the testability of requirements and create a comprehensive test plan that supports the business and technological solutions being delivered.

An understanding of Azure Data Factory Pipelines & Databricks Notes testing, Cloud data/Data Lake testing

Identification and Test Data Preparation Techniques.

Review and understand Business Requirements/Source-To-Target mapping.

Ability to transform complex Business logic into SQL or PL/SQL queries

Ability to run, read from, debug batch processes

Thorough knowledge of Software Test Life Cycle, Test Case Life Cycle, Defect Life Cycle etc

**Skills**:7+ years in testing ETL projects & Data validation (test plan creation, writing test cases and executing back end testing of ETL pipelines) (Must Have)

2+ years of experience in testing Azure Data Lake and Data Warehouse, and Datamart (Must Have)

2+ years Python/PySpark (nice to have)

Databricks

Synapse (nice to have)

SQL Server

Oracle

Ability to present technical requirements to the business

Evaluation Criteria

Design Documentation and Analysis Skills (45%)

Demonstrated experience in creating both Functional Design Documents (FDD) & Detailed Design Documents (DDD).

Experience in Fit-Gap analysis, system use case reviews, requirements reviews, coding exercises and reviews.

Experience in the development and maintaining a plan to address contract deliverables, through the identification of significant milestones and expected results with weekly status reporting.

Work with the Client & Developer(s) assigned to refine/confirm Business Requirements

Participate in defect fixing, testing support and development activities for ETL tool. Assist with defect fixing and testing support for Power BI reports.

Analyze and document solution complexity and interdependencies by function including providing support for data validation.

Development, Database and ETL experien