ETL Architect is a high-level business intelligence professional that is a key part of the Data and Analytics Team and will be generally responsible for the dimensional model design, end to end ETL architecture design, ETL development, and implementation of data models and development of the Data Migration and Data Warehousing solutions. The ETL Architect is also responsible for requirements analysis, source data profiling, identifying data quality issues, designing transformation logic to address data quality issues, experienced with the concepts and/or implementation of metadata data capture, data lineage, master data management and reference data management. The ETL architect designs for data warehouse/data mart solutions should include appropriate staging databases to handle change data capture on the source data, error handling and audit balance control mechanisms. The ETL Architect will have extensive interaction with operational team members and leadership, and will be tasked with analysis of business data and analytics requirements, and the transformation of those requirements into analytics solutions. Working with customers and Management to determine priorities as well mentoring of other team members on ETL concepts and strategies is also an integral role of the position. Working with Infrastructure and DBA team members, ETL Architect is also responsible for keeping the ETL Environments updated periodically to the latest version of the ETL software.
Strong analytical and interpersonal skills; demonstrated ability to work with management and customers; proven ability to work independently or as a team member; strong problem solving and troubleshooting skills; initiative in enhancing system productivity, functionality, and performance.
Advanced understanding of Data Migration/Data Warehousing/Data Mart delivery life cycle, from requirements definition to creating source to target mapping documents with transformation rules, Error handling design, data quality, design audit Balance control mechanisms to be able to automate jobs, notify proactively failures, write test cases and create templates to document all aspects of the projects throughout the lifecycle.
Strong experience with concepts of data profiling, identifying and measuring data quality issues, advanced experience with Master data and reference data management techniques to eliminate hard coding within the ETL processes/jobs.
Excellent communication (written, verbal and presentation) and people-facing skills, Proven ability to maintain positive peer, customer relationships, interacting positively and productively with teams across organizational lines. Demonstrates skills in leadership, critical thinking, and consensus and relationship building.
Ability to review, re-engineer and/or tune legacy SQL queries and/or ETL jobs/processes to make them run efficiently and within a load window.
Must be able to function in dynamic environment subject to changes in schedules and priorities, working on multiple projects/ issues concurrently. Responsibilities may require evening and weekend work in response to needs of the systems supported.
Experience in estimation, specification gathering, review code/designs, general planning for execution, monitoring and control.
Hands-on with ability to design, re-design (schemas, data models, entity relationships etc.), and probe and guide developers.
Possess a solid understanding of data warehousing concepts, including relational and multidimensional modeling.
Experience in designing, building, testing, executing and performance tuning of ETL and reporting applications, including automation of ETL jobs for recurring imports; fluent in Microsoft SQL.
Experience in designing and developing database objects, tables, stored procedures, views, and triggers.
Designs end to end ETL architecture for Data Migration, Data Warehousing or Data Mart Solutions using Microsoft SSIS tool. Ensure all ETL Jobs have adequate error handling and audit balance control mechanisms implemented to enable automation.
Stands up a DW/ETL Center of Excellence to document and enforce best practices, standards, naming conventions etc. Creates a checklist to review all solutions developed to ensure adherence to the CoE standards. Mentors and trains staff to hold periodic CoE meetings.
Implement a reference data management system using tools like Microsoft Master Data Management Services to eliminate hardcoding in ETL jobs.
Maintains a service oriented approach with customers, providing follow up and responding to issues/ concerns in a timely manner. Answer staff and client questions in person, via email and via phone. Provide after-hours on-call support on a scheduled basis. Responsible for ensuring service level agreement adherence.
Works with staff to adhere to department/organization policies and procedures, compliance and regulatory agency requirements.
Works with leaders in IT as well as operations to identify data from other source systems to form richer data sets, including the capture of derived data to address gaps in source system repositories.
Promotes and utilizes the data request and development process, including the development of an understanding of a data request, assessment of the appropriate tool to be used to provide the data, creation of a requirements document in partnership with the operational stakeholder and application analyst, development of the data extract and validation of the extract by the operational stakeholder to ensure quality, timeliness and accountability.
Participates in and contributes to the data governance program - specifically, validating data lineage, capturing metadata for all existing and any newly developed assets (ETL Jobs), profile various data sources and address data quality issues, work with source system owners to reduce upstream data entry errors.
Assists with all upgrade, update or enhancement efforts associated with the ETL tool.
In partnership with the Manager of Business Intelligence and Analytics, serves as a mentor and advisor to the BI technical resources and other analysts and provides general project management skills to produce BI views that address customer’s needs.
Builds and maintains SQL scripts, indexes and complex queries for data analysis and extraction.
Troubleshoots problems regarding the databases, applications, and development tools.
Assists with the development and implementation of new data sources into the data warehouse or determine ways to be able to utilize data in other systems to support data and analytics for the organization.
Conducts or coordinate tests to ensure that intelligence is consistent with defined needs.
Responsible for data collection/extraction and developing methods for database design and validation reports.
Prepares and maintains key documents and technical specifications for new and existing solutions.
Documents specifications for business intelligence or information technology (IT) reports, dashboards, or other outputs.
In collaboration with other team members and leadership, supports the evaluation, adoption and implementation of new methods, tools and technologies in order to meet current gaps in the organization-wide data and analytics infrastructure.
Works closely with end-users to identify and explain data and reporting discrepancies.
Prepares and maintains key documents and technical specifications for new and existing solutions.
Provides solution design, perform reviews and participate strategic discussions.
Provides application analysis and data modeling design to collect data or to extract data from various disparate systems.
Monitors incident management queue. Efficiently troubleshoot, resolve, report and close active issues.
Works on additional assignments as delegated by management.
