- Almost 8 years of experience in IT industry as an ETL developer with IBM Info Sphere, Data Stage as primary tool.
- Experience in loading and maintaining Data Warehouses and Data Marts using Data Stage 7x/8x/11x ETL processes.
- Extensive exposure to Star, Snowflake Schema and Multidimensional Data Models.
- Experience in extraction, cleansing, integration, loading of data from/to disparate data sources.
- Worked with various source systems like Oracle, SQL Server, flat files into a staging area.
- Hands on experience in parsing the data from XML strings and files using XML stage.
- Strong experience in developing jobs using different stages in Data Stage such as join, merge, lookup, transformer, filter, dataset, modify, aggregator and change capture and slow change dimension.
- Expertise in data warehousing techniques for Slowly Changing Dimension phenomenon.
- Hands on experience in writing, testing and implementation of triggers, procedures and functions at Database level using PL/SQL.
- Extensive experience in loading high volumes of data and performance tuning.
- Involved in various phases of software life cycle, such as requirements definition, functional and technical designs, testing, implementation and production support.
- Avid and quick learner, always ready to learn the available tools to accomplish project tasks.
- Excellent team member with problem - solving and trouble-shooting capabilities, highly motivated, result oriented and an enthusiastic team player.
- Good interpersonal skills, experience in handling communication and interactions between different teams.
ETL Tools: IBM InfoSphere Data Stage 11.3, 8.7, 8.5, 8.1, 7.5.1
Databases: Oracle 11g /10g/ 9i, SQL Server 2014/2008.
Database: ModelingIBM Insurance Information Warehouse industry data model, Microsoft VISIO.
Software: IBM InfoSphere Quality Stage, IBM InfoSphere Information Server Manager, IBM InfoSphere Metadata Workbench, IBM InfoSphere Information Governance Catalog, SQL Developer, Toad, SQL Server Management Studio, Subversion, Autosys, JIRA.
Languages: SQL, PL-SQL, Java, C, UNIX/Linux Shell Scripting
Operating System: Windows 8/7/XP, UNIX, Linux
ETL DataStage Developer
- Interacted with Business Analysts and Data Modelers for better understanding of the business and data models and the different sources and target systems.
- Followed the IBM Insurance Information Warehouse industry data model.
- Worked in the design of job templates (ETL patterns) to specify high-level framework approach.
- Developed and implemented data incremental loads that constitute different subject areas for different Source Systems.
- Designed and developed parallel jobs for loading data from the staging area to the Atomic Warehouse and from the Atomic Warehouse to the Dimensional Warehouse (dimension/fact tables).
- Developed job sequencers to run and control the flow of jobs by subject area and target system.
- Performed Unit testing, Integration testing and Regression testing for jobs developed to ensure that they meet the requirements.
- Integrated parameter sets and environment variables in the development of jobs and followed job flows, audit processes and strategies established by the ETL development team.
- Used extensively different stages such as Transformer, Change Capture, Slow Change Dimension, Shared containers, Remove Duplicates, Funnel, Look Up, Sort, Data Sets, ODBC Connector among others.
Environment: InfoSphere Information Server 11.3 Suite (DataStage and QualityStage, Director, Information Governance Catalog, Information Server Manager), SQL Server 2014, SQL Server Management Studio 2014, Windows 7, UNIX.
ETL DataStage Developer
- Worked closely with Data Architects, Business Analysts and DBAs on the business requirements.
- Created new ETL jobs (Sequence jobs, Parallel jobs) based on the requirements and mapping documents.
- Adapted existing ETL jobs (Sequence jobs, Parallel jobs) according to the requested changes.
- Worked with standardized job parameters, environment variables, job flows, audit processes and strategies established by the development team and data warehouse policies.
- Performed unit testing and debugging of ETL jobs to ensure that requirements were met.
- Analyzed the data loaded into data marts to verify and validate that data was processed and loaded correctly.
- Applied validation rules and constraints using data stage variables and transformations.
- Used several stages like Sequential file, Dataset, Transformer, Join, Funnel, Sort, Remove Duplicates, Lookup, Oracle Enterprise/Connector, XML stage among others during the development process of the DataStage jobs.
- Coded UNIX shell scripts wrappers for ETL processes (calling DataStage jobs).
- Created JIL scripts for control and automation of ETL processes in Autosys.
- Managed the metadata repository of the different information assets including the configurations of Subversion as a source code control management system.
- Reviewed and updated production support documentation for ETL processes.
- Provided support for data quality investigations, problem investigations, fixes, job failures, job/SQL performance improvements.
Environment: InfoSphere Information Server 8.7 Suite (DataStage and QualityStage, Director, MetadataWorkbench, Information Server Manager), Oracle 11g, SQL Developer, Windows 7, UNIX.
- Reviewed and updated technical specification documents for ETL process.
- Developed standard ETL processes to execute pre-ETL and post-ETL processes to ensure smooth transfer of data from heterogeneous source systems to a homogenous target system.
- Involved in developing server and parallel jobs for Source to Stage, Stage to ADS (Atomic Data Store) and ADS to Datamarts.
- Designed Job Sequencers for every project to run in a loop based on the success or failure of individual jobs in the Sequencer.
- Performed the Unit testing for developed jobs to ensure that the requirements were met.
- Worked closely with team lead in process of standardizing job parameters, job flows, audit process and rollback strategies that hold major attention for the successful implementation of DW Incremental.
- Conducted detailed data analysis using Information Analyzer for the multiple sources from various systems.
- Used Quality Stage to coordinate the delivery, consistency, removing data anomalies and spelling errors of the source information.
- Defined and documented the required data validation rules on the source feed files that were used in ETL.
Environment: InfoSphere Information Server 8.1/8.5, Oracle 10g, SQL Server 2005, Shell Scripts, Windows XP, UNIX.
Confidential, Cleveland, OH
- Translated Functional requirements into technical requirements. Designed the ETL Process flow between the source and target.
- Designed the mapping between the source and target columns.
- Used Data stage Designer to extract, cleanse, transform, integrate and load data into the target Oracle tables.
- Designed the ETL Transformation logic for translating the source data.
- Customized UNIX scripts as required for preprocessing steps and to validate input and output data elements, along with DataStage routines.
- Developed job sequences with restart ability, check points and implemented proper failure actions.
- Worked on ETL enhancements and bug fixes as required through proper release process.
- Interacted with Business Analysts Functional requirements and for change requests.
- Created promotion code request forms with user-defined environment variables exported jobs, project folders etc. and passed them to ETL administrator for elevation.
- Created Error Files and Log Tables containing data with discrepancies to analyze and re-process the data.
Environment: IBM InfoSphere Information Server 8.1, Shell Scripts, Windows XP, UNIX.
- Participated in discussions with Project Manager, Business Analysts and Team members on different Business Requirement issues.
- Worked on the existing documents and developed the required technical specification as per the business needs.
- Worked on extracting data from Flat files, SQL Server and Oracle databases.
- Designed ETL jobs by identifying various source files and applying transformations and ETL logic to them.
- Used Toad to create and edit database objects such as tables, views, indexes, constraints etc.
- Developed various jobs using stages like Transformer, Lookup, Datasets, Funnel, Change Capture stage, Change Apply stage and ODBC stage among others.
- Mapped Data Items from Source Systems to the Target System.
- All the jobs were migrated from development to production using DataStage Manager.
- Validation of all the metrics was done in advance before migration to production environment.
- Monitored all the metrics in the production and worked on the issues raised.
- Used Data Stage Director and its run-time engine to schedule running the solution, testing and debugging its components, and monitoring the resulting executable versions.
- Developed UNIX Shell Scripts to trigger the Data Stage jobs through a Master Sequencer and to automate data loading in batch mode.
- Used control-M to schedule the jobs based on prerequisites in and dependencies out of the jobs.
- Participated in the meetings with the teammates, project manager and the end users
Environment: Ascential Data Stage, Oracle.