Data Acquisition (ETL) Analyst

Business Services - Services d’affaires Ottawa, Ontario


Position at Canadian Medical Protective Association


Working closely with all CMPA departments, the Business Strategy and Analytics department leads the CMPA’s key enterprise management processes. The core activities of the Business Strategy and Analytics department include strategic planning, operational planning, performance reporting, and day-to-day monitoring. Enterprise risk management, market intelligence, data management, and decision support influence each of these activities and are at the center of the process.

The Business Intelligence team oversees all infrastructure that supports the data analysis and data management of CMPA business information. The team maintains an enterprise data warehouse that provides historical, current, as well as predictive views of business operations. The team services all internal users with traditional report development and distribution, complex custom ad-hoc information requests, and supports the needs of other analytics teams by providing and regularly updating data subsets.


Working within the Business Strategy and Analytics department, and reporting to the Supervisor, Business Intelligence, the Data Acquisition (ETL) Analyst demonstrates expert level skills related to ETL tools. The incumbent is primarily responsible for designing and writing the scripts required to extract, transform, clean, and move data for loading into the data warehouse. Additionally, the Data Acquisition (ETL) Analyst is responsible for business intelligence tools development and support; data warehouse administration; documentation; and, leading data warehouse design, architecture, and metadata. The incumbent also provides coaching and mentoring to others on ETL usage and development of ETL code while also collaborating with the Application Integration and Development team on projects to support data acquisition and movement.


ETL Development

  • Evaluates the tools (computerized and other) currently in use and those that are available to ensure efficiency and identifies improvements that can be implemented.
  • Provides insight and input into the logical data/business models and physical database designs.
  • Designs, constructs, and automates the extraction, transformation, and load processes, as well as creates supporting documentation and technical specifications. This includes moving ETL mappings and workflows to the production environment and scheduling workflows.
  • Identifies and supports the resolution and documentation of data issues.
  • Provides guidance to users during user acceptance testing and documents any as-yet-undiscovered deficiencies for correction prior to release.
  • Monitors and manages the ETL processes, including troubleshooting and resolving any workflow errors.
  • Adapts ETL processes to accommodate changes in source systems and new business user requirements.
  • Provides end-user support which includes helping to identify appropriate data for users to query, resolving data access problems, etc.
Business Intelligence Development, Design and Support
  • Researches and evaluates Business Intelligence and Data Management tools and provides recommendations.
  • Leads the design of the data structures of the warehouse.
  • Leads the technical architecture of the data warehouse, including physical components and their functionality; evaluating, selecting, testing and optimizing software and hardware products; capacity planning; etc.
  • Creates tables, views, or materialized views to facilitate the loading of data into the staging database and creates indexes to facilitate the extraction of data into the data warehouse. 
Other Activities
  • Provides user training on new systems prior to system release.
  • Develops sections and/or provides input into the project plan and schedule throughout the life cycle of a project and when unforeseen issues arise.
  • Remains vigilant of industry and technological changes to improve knowledge and working skills related to areas of expertise and new technologies.
  • Assists other members of the Business Strategy and Analytics and Information Technology Services teams to ensure smooth and timely integration of new technologies.
  • Four-year Bachelor’s Degree in computer science or related field of study
  • Minimum seven years of relevant experience with a minimum of three years developing ETL directly supporting BI related projects or operations
  • Professional designation as a Certified Business Intelligence Professional, Certified Data Management Professional, or Certified Data Professional is considered an asset
  • Strong understanding of data warehousing methodologies
  • Previous experience using, administering, and installing/upgrading Informatica PowerCenter is required
  • Strong understanding of business processes (Finance, Customer Relationship Management (CRM), Risk Management, and Corporate Performance Measurement) is highly desirable
  • Experience developing and delivering successful Business Intelligence solutions that include relational databases is essential. Solutions which include relational databases, Extract Transform and Load (ETL) tools, reporting, analytics, data mining and ERP applications is highly desirable
  • Experience working with cloud based technologies; Workday and/or SugarCRM experience is an asset
  • Experience working in cross-functional teams in a project based environment
  • Experience with Web Service API’s for integration purposes, REST/SOAP based web service development experience is an asset
  • Strong verbal and written communication skills in English
  • Expert level data extraction, processing, program construction, and testing skills
  • Strong SQL coding skills are essential; Oracle, PL/SQL and Unix scripting experience are considered an asset
  • “Power User” competency with Business Intelligence Online Analytical Processing and reporting tools and applications (Cognos Analytics, Crystal, SAS, Excel, etc.) is highly desirable
  • Strong interpersonal skills and the ability to collaborate with users to meet their needs
  • Ability to analyze and provide creative and innovative solutions to business problems
  • Ability to maintain high productivity amidst competing priorities and tight timelines
  • Experience interacting with all levels within an organization is highly desirable
  • This is a term opportunity (approximately 2 year term) and benefits eligible
  • The Data Acquisition (ETL) Analyst position is classified at Level 9 - range is $79,108 to $102,905
  • Selected candidates may be required to complete a skills assessment
  • Posting will close on Friday, June 21st at 4:00pm