- 8 +years of experience in building and integrating Large Enterprise Data Warehouse and Data Processing Pipelines.
- Experience in Data Warehousing concepts and Principles of Ralph Kimball
- Experience in System Analysis, design, development and implementation of Relational Database and Data Warehousing Systems using IBM Data Stage 11.5/8.1
- Experienced in Database programming for Data Warehouses (Schemas), proficient in Dimensional Modeling (Star Schema modeling, and Snowflake modeling)
- Experience in both Structured and Object - Oriented Programming Languages such as UNIX Shell Scripting, PL/SQL, Java and Python
- Experience in handling multiple Relational Database Management Databases (RDBMS) Oracle, SQL Server, My SQL, Teradata
- Excellent Experience in Designing, Developing, Documenting, Testing of ETL jobs and mappings in Server and Parallel jobs using Data Stage to populate tables in Data Warehouse and Data marts
- Expert in designing Parallel jobs using various stages like Join, Merge, Lookup, Remove duplicates, Filter, Dataset, Lookup file set, Complex flat file, Modify, Aggregator, XML
- Expert in designing Server jobs using various types of stages like Sequential file, ODBC, Hashed file, Aggregator, Transformer, Sort, Link Partitioner and Link Collector
- Familiar in using highly scalable parallel processing infrastructure using parallel jobs and multiple node configuration files (APT)
- Experienced in integration of various data sources (DB2-UDB, SQL Server, Oracle, Teradata, Netezza, XML and MS-Access, SAS, HDFS and JSON) into data staging area
- Experience in Plug-in Teradata stages like TeraData Load and TeraData Multiload
- Expert in Data Warehousing techniques for Data Cleansing, Slowly Changing Dimension Phenomenon(SCD),Surrogate Key Assignment and CDC(Change Data Capture)
- Experience in implementing Datastage Partition Hash, Modulus, Random, Range, Round Robin and Same
- Expert in working with Data Stage Manager, Designer, Administrator, and Director
- Experience in analyzing the data generated by the business process, defining the granularity, source to target mapping of the data elements
- Proven track record in troubleshooting of Data Stage jobs and addressing production issues like performance tuning and enhancement
- Experience in writing complex Oracle PL/SQL Packages, Stored Procedures, Functions, Triggers and Collections
- Experience in building complex Analytical SQLs and performed data analysis
- Experience IBM High Performance Data Warehouse appliance "Netezza", NZSQL, NZLOAD, NZMIGRATE, Zone Map, Distribution Key Handling, and Optimizing Query Plan
- Experience in batch job management in multiple batch job schedulers such as Autosys, Control-M and TIBCO Ops Console
- Experience in Agile methodology and all Agile Ceremonies including SOS, Daily Standup, Sprint Planning, Retrospective
- Worked with cross functional teams like Architecture, Development, Production and Operation
- Experience in working with offshore and onshore team model
- Ability to grasp any new technology and languages very quickly
ETL Tools: Data Stage IBM Web Sphere Data stage and Quality Stage 8.0, Ascential Data Stage / 7.5.2/5.1/6.0 Profile Stage 7.0, SSIS (SQL server 2005), Data Integrator.
Databases: Oracle 9i/8i, MS SQL Server 2000/7.0/6.5 , MS Access 7.0/2000, SQL Server, Postgress
Development tools and Languages: SQL, C, C++, Unix Shell Scripting, Perl, PL/SQL, oracle
Tools: MS Office 97/professional, SQL*Plus 3.3/8.0, TOAD, Microsoft Visio, Putty, Text Pad, .csv files, Erwin.
Business Intelligence Tools: Business Objects, Brio, SSRS(SQL Server 2005),IBM Cognos 8
BI Testing Tools: Auto Tester, Test Director, Lotus Notes. Operating Systems HP-UX, IBM-AIX 5.3, Windows 95/98/2000/ NT, Sun Solaris, Red-Hat Linux, MS SQL SERVER 2000/2005/2008 & MS Access
ETL Data Stage Developer
- Involved as primary on - site ETL Developer during the analysis, planning, design, development, and implementation stages of projects using IBM Web Sphere software (Quality Stage v8.1, Web Service, Information Analyzer, Profile Stage, WISD of IIS 8.0.1).
- Prepared Data Mapping Documents and Design the ETL jobs based on the DMD with required Tables in the Dev Environment.
- Active participation in decision making and QA meetings and regularly interacted with the Business Analysts &development team to gain a better understanding of the Business Process, Requirements & Design.
- Used Data Stage as an ETL tool to extract data from sources systems, loaded the data into the ORACLE database.
- Designed and Developed Data Stage Jobs to Extract data from heterogeneous sources, applied transform logics to extracted data and Loaded into Data Warehouse Databases.
- Created Data stage jobs using different stages like Transformer, Aggregator, Sort, Join, Merge, Lookup, Data Set, Funnel, Remove Duplicates, Copy, Modify, Filter, Change Data Capture, Change Apply, Sample, Surrogate Key, Column Generator, Row Generator, Etc.
- Extensively worked with Join, Look up (Normal and Sparse) and Merge stages.
- Extensively worked with sequential file, dataset, file set and look up file set stages.
- Extensively used Parallel Stages like Row Generator, Column Generator, Head, and Peek for development and de-bugging purposes.
- Used the Data Stage Director and its run-time engine to schedule running the solution, testing and debugging its components, and monitoring the resulting executable versions on ad hoc or scheduled basis.
- Developed complex store procedures using input/output parameters, cursors, views, triggers and complex queries using temp tables and joins.
- Converted complex job designs to different job segments and executed through job sequencer for better performance and easy maintenance.
- Creation of jobs sequences.
- Maintained Data Warehouse by loading dimensions and facts as part of project. Also worked for different enhancements in FACT tables.
- Created shell script to run data stage jobs from UNIX and then schedule this script to run data stage jobs through scheduling tool.
- Coordinate with team members and administer all onsite and offshore work packages.
- Analyze performance and monitor work with capacity planning.
- Performed performance tuning of the jobs by interpreting performance statistics of the jobs developed.
- Documented ETL test plans, test cases, test scripts, and validations based on design specifications for unit testing, system testing, functional testing, prepared test data for testing, error handling and analysis.
- Participated in weekly status meetings.
- Developed Test Plan that included the scope of the release, entrance and exit criteria and overall test strategy.
- Created detailed Test Cases and Test sets and executed them manually.
Environment: IBM Web Sphere Data Stage 8.1 Parallel Extender, Web Services, Quality Stage 8.1, (Designer, Director, Manager), Microsoft Visio, IBM AIX 4.2/4.1 IBM DB2 Database, SQL Server, IBM DB2, Teradata, ORACLE 11G, Query man, Unix, Windows.
- Worked with Data Architects and Business Analysts to clarify the ambiguous requirements/source to target mappings.
- Developed Parallel Jobs in DataStage Designer to extract data from the Sources Oracle and Complex Flat Files, transform it by applying business rules and Load (Initial/Incremental) it into Stage tables and then to warehouse and mart tables.
- Extensively used different DataStage Stages like Database, Debug (Peek, Row Generator and Column Generator), File (Sequential File and Dataset) and Processing Stages (Copy, Change Capture, Aggregator, Surrogate Key Generator, Transformer, Lookup, Join, Sorter, Remove Duplicates, Pivot Enterprise and Funnel).
- Reduced the development effort by using the Schema File and RCP (Run - time Column Propagation) options to load/unload the Staging, EDW and Mart tables.
- Built utility jobs using UNIX shell scripts to clean the prior run files, setting up the new UNIX directory Structure, archiving the files, data management and data loading techniques.
- Prepared the job scheduling documents to run the Dimensions and Facts loading jobs in the correct order.
- Unit tested the jobs in Development environment by running manually using DataStage Director and verifying the job logs for warnings & errors.
- Exported the jobs from Development to Test environment and then to Production environment using DataStage Designer/Manager.
- Involved in writing the possible Unit test cases and tested number of jobs and resolved the defects in developed jobs
- Participated in Team meetings to discuss about the issues and development status.
- Documentation is done as to facilitate the personnel to understand the load process and in corporate the changes as and when necessary.
Environment: IBM InfoSphere DataStage 8.5.x EE (Parallel Extender, Designer, Director, Administrator), Oracle, Toad, SQL, PL/SQL, Db2, UNIX Shell Scripts, IBM TWS, Linux, Windows 7 Enterprise.
Confidential, Portland, OR
- Involved in building a prototype without mapping documents.
- Involved in modifying UNIX scripts which generated emails to notify the incoming file to the RITS team.
- Creation of Shared containers so that it can be used by other modules of the plan.
- Exporting the jobs to Testing Environment from Development and then to Production.
- Worked closely with Project lead/Manager, Architects, and Data Modelers, System Analyst to understand the business process and functional requirements
- Fixed defects highlighted by testing team.
- Did unit testing of the jobs developed before taking them for UAT and finally Production.
- Modified DataStage wrapper scripts written in Korn scripts.
- Involved in writing Shell scripts for reading parameters from files, invoking DataStage jobs, and FTP files to specific locations.
- Agile methodology was followed to produce the results.
- Performed data transition by using Sequential file, Link collector, Transformer, ODBC, Aggregator, Hash file, Link Partitioner.
- Developed DataStage job sequences used the User Activity Variables, Job Activity, Wait for File stages
- Block SM eligibility was build which involved reading data from flat flies and loading into database.
- Extensively Used the IBM Info sphere Information Server to develop jobs to extract, transform and load data from various source systems into the Data Warehouse.
- Extensively used IBM Info sphere Information Server Director for scheduling the job to run in batch, emailing and online production support for troubleshooting from LOG files
- Used Job Sequencers to run the jobs sequentially and reporting the Status of the Job through Email.
- Exported and imported the Data stage jobs between the production and the development servers
- Involved in S.I.T (System Integration and Testing) and U.A.T (User Acceptance Testing).
- Wrote UNIX (Korn) Shell Scripts to fetch values from tables and pass as parameters. Also worked on scripts to validate the source data received from Argus and PHS, Health Care Providers on the client side
- Extensively used SQL coding for overriding the generated SQL in DataStage and also tested the data loaded into the data base.
- Used Korn Shell scripts for scheduling DS jobs and also with Datastage Scheduler
- Implemented complex logics in Transformer stage like date validation, use of stage variables.
- Unit Testing and Integration testing the individual and extract - transform-load jobs in sequence respectively.
- Developed UNIX shell scripts to automate the Data Load processes to the target Data warehouse.
Environment: IBM Infosphere Information server 8.0.1/IBM Datastage 7.5(server edition), Oracle 10g, SQL Navigator 6.5, SQL/PLSQL, Korn Shell Scripts UNIX Reflections, FTP Client, IBM AIX 5.2/5.3,.Windows XP
Sr. Data stage Developer
- Worked closely with Business analysts and Business users to understand the requirements and to build the technical specifications.
- Involved in all business meetings to understand the existing logic, different member information and agency data to come up with the best IT solution.
- Responsible to create Source to Target (STT) mappings.
- Involved in day to day production support activities.
- Worked on various defects raised by the concerned business teams from various entities.
- Developed and supported the Extraction, Transformation and Load process (ETL) for a data warehouse from various data sources using DataStage Designer and to load the target tables using the DataStage Designer.
- Worked on various stages like Transformer, Join, Lookup, Sort, Filter, Change Capture and Apply stages, Quality Stage.
- Developed parallel jobs using various Development/debug stages (Peek stage, Row generator stage, Column generator stage, Sample Stage) and processing stages (Aggregator, Change Capture, Change Apply, Filter, Sort & Merge, Funnel, Remove Duplicate Stage)
- Designed job sequencer to run multiple jobs with dependency and email notifications.
- Involved in Unit Testing, SIT and UAT Worked with the users in data validations.
- Extensively worked in improving performance of the jobs by avoiding as many transforms as we can.
- Prepared documentation for unit, integration and final end - to - end testing.
- Optimized/Tuned DS jobs for better performance and efficiency
- Responded to customer needs; self-starter and customer service oriented.
- Worked within the team to make appropriate, effective decisions related to project responsibilities and to initiate and follow-up on assigned tasks without supervision.
- Provided support and guidance by creating Release, Deployment & Operation guide documents.
- Involved in Performance tuning of complex queries.
- Developing SQL scripts to facilitate the functionality for various modules.
- Created control M jobs for scheduling.
Environment: IBM InfoSphere DataStage 8.x (Designer, Director) Oracle 10g, Teradata 13, Sequential files, UNIX Shell Scripting