We provide IT Staff Augmentation Services!

Etl Architect Resume

CaliforniA

CAREER OBJECTIVE:

  • To hold a position of responsibility in an organization that gives an opportunity to give my best, by being able to handle the task assigned and fetch the results in the minimum time
  • To associate myself with a professional organization of career development and contribute significantly by utilizing my potential to the fullest extent in making the organization achieve its long term goal

SUMMARY:

  • Over 9 years of IT experience in data analysis, data integration, data modeling, design, development and implementation of data warehousing projects.
  • Expertise in understanding the business requirements and converting captured business requirements to functional and technical specifications.
  • Experience in Dimensional data modeling - designing of Star Schema & Snow Flake Schema.
  • Proficiency in using Informatica 6.x / 7.x / 8.x/9.x, Datastage
  • Good knowledge of Relational databases like Oracle, DB2, SQL Server, MySQL
  • Worked on various Domains like Banking, Marketing, Retail and Finance
  • In-depth database knowledge with good experience in constructing the triggers, tables, functions, stored procedures, packages, indices, table partitioning.
  • Experience in SQL queries for performing complex database functions
  • Experience using data modeling tools like ERWIN, ER Studio & MS Visio
  • Experience in integrating various enterprise application data like Customer, Order, Sales data
  • Extracted data from various sources such as Oracle, DB2, SQL Server, Flat files, Salesforce(SFDC), COBOL
  • In-depth understanding of Data warehousing techniques for data cleansing, slowly changing dimension
  • Extensively worked in identifying integration bottlenecks & performance tuning of ETL Procedures, processes and identifying and resolving performance bottlenecks in various levels like sources, targets, mappings, and sessions.
  • Skilled in Unit Testing, System Integration Testing (SIT) and User Acceptance Testing (UAT)
  • Experience with DAC functionality for Oracle BI suite
  • Conversant with Agile (Scrum) project management methodology.

PROFESSIONAL STRENGTHS:

  • Strong Communication skills with Leadership coupled with strong Programming and Analytical ability
  • Experience in leading a team of developers with a maximum strength of 12 associates
  • Performed as an ETL Architect, BSA, Technical lead and senior developer providing solutions and design ideas
  • Possess excellent client interfacing, leadership and project management skills along with in depth knowledge of Datawarehousing concepts
  • Ability to work friendly in teams with the utmost productivity and at the same time independently taking up the full responsibility
  • Exceptionally good in troubleshooting and problem solving
  • Good motivator and ability to handle multiple tasks

TECHNICAL PROFILE:

ETL Tools: Informatica 9.x, 8.x, Informatica 7.x, Informatica 6.x, Datastage

BI Tools: OBIEE, Cognos 8.x

RDBMS: Oracle 11i, 10g, 9i, 8i, SQL Server 2005, DB2, MS Access

Languages: UNIX Shell Scripting, SQL, COBOL

Modeling Tools: Erwin, ER Studio, MS Visio

Middleware: MQ Series

Operating Systems: UNIX, AIX, Windows

Other Tools: Autosys, Control-M, SQL Developer, SQL Navigator, TOADMicrosoft Visual SourceSafe (VSS), MS Office, DDM, MFT/ Workshop SAP BW, wholesale and retail Banking, Marketing domain, Finance DomainBusiness Communication, Presentation skills, SCRUM

WORK EXPERIENCE:

Confidential, California

ETL Architect

Responsibilities:

  • Elicited and gathered requirements from both user & business through open ended discussions and brainstorming
  • Analyzed business requirements and created Agile stories and tasks for the development
  • Performed data profiling across different systems to identify the data anomalies
  • Designed conceptual, logical and physical data model in building datamart using ERWIN
  • Participated in Agile Project Iteration planning
  • Integrated data from Customer Intelligence at Macys(CIM) which is customer MDM and Order data to perform customer segmentation and personalized marketing
  • Created functional requirements & technical design document
  • Developed handshake process between the source and target systems
  • Prepared ETL guidelines and standards for the project
  • Source to target mapping was developed
  • Performance tuning of SQL queries to retrieve the result set efficiently
  • Optimized and performance tuned the ETL processes using various techniques such as aggregation, filtering, adding or dropping index, partitioning, sorting, adding hints, triggers, pre sql or post sql
  • Responsible for assisting the testing team to develop the test plans and test cases for the system
  • Coordinated with DBA for any database changes
  • Prepared test cases and performed unit test for the ETL jobs
  • Performed code review of ETL jobs
  • Supporting System Integration Testing and User Acceptance Testing
  • Adherence to implementation timelines and timely delivery of product

Environment: Informatica 9.5, Oracle 10g, IBM DB2, MS Visio, SQL Developer, MySQL, Control M, ERWIN

Confidential, California

ETL Architect

Responsibilities:

  • Expertise in gap analysis, as-is and to-be modeling and use these techniques to design process and system improvements to increase productivity and reduce costs.
  • Established strategic partnerships with the business units to develop a solid understanding of the business line; including the business plan, products, and process
  • Strategized project scoping and specifications documents by doing business analysis, project assessment and feasibility determination
  • Facilitated Project Kick off meetings with all stakeholders - defined the objective, reviewed roles and responsibilities, mapped timelines, milestones and communication processes.
  • Led cross-organizational efforts for process improvements such as documentation summarizing the project overview which can be used across business units, end - end testing efforts to ensure application integrity, identifying areas of risk and developing controls and workarounds to address them etc
  • Conducted meetings with key business users to collect requirement and business process information.
  • Gathered Business Requirements and wrote Systems Requirements Document (SRD) by working with the client IT staff, project managers
  • Integrated data from various sources including sales data from Oracle, SQL server, Salesforce (SFDC), DB2, flat files
  • Interacting with the developers on resolving the reported bugs and various technical issues.
  • Identify and make code changes to the impacted modules, Preparation and review of test plans, test data set up preparation and review
  • Deployment and promotion of new releases to production
  • Analyzed OLTP and OLAP system and performed data analysis to identify the affected business areas
  • Preparation of Business Requirement, Functional Data Diagram, Technical specification document and mapping documents
  • Dimensional data modeling using ERWIN
  • Design and development of Informatica mapping, sessions and workflows based on user requirements
  • Created synonyms, stored procedures, packages, triggers, materialized views in Oracle database to handle different business functionalities
  • Created deployment groups for migrating Informatica objects into Test, UAT and Production
  • Extensive SQL Tuning to improve system performance
  • Worked with Informatica for importing metadata from repository, new job categories and creating new data elements
  • Experience in Debugging Informatica mappings for failures and performance tuning at source, target, mappings and sessions
  • Extensively used various Partitioning techniques for improving the system performance
  • Adeptly used Informatica stages like expression, aggregator, lookup, joiner, filter, router, stored procedure, sequence generator to implement various business requirements
  • Migrated Informatica jobs across different environments
  • Prepared Unit test cases and performed Unit testing, involved in System testing and UAT
  • Performed production implementation and production support

Environment: Informatica Power Center 9.0/8.6, Oracle 11i, Salesforce, Tableau, TOAD v10, SQL Developer, ERWIN, Informatica Power exchange, MFT, DB2, Autosys

Confidential, California

Technical lead

Responsibilities:

  • Interacted with the business users to gather the requirements for warehousing the sales and marketing data
  • Preparation of Technical specification document and mapping documents
  • Dimensional data modeling using ERWIN
  • Design and development of Informatica mapping, sessions and workflows based on user requirements
  • Written SQL queries for handling database functionalities
  • Developed Complex Stored procedures for handling the sales representative assignment for the sales and their positions
  • Created deployment groups for migrating Informatica objects into UAT and Production environments
  • Extensive SQL Tuning to improve system performance
  • Scheduling the workflows using DAC provided with Oracle BI Suite
  • Experience using OBIEE reporting with physical and Business model layers
  • Worked with Informatica administrator to create users, to set user privileges, to configure project level settings
  • Debugging Informatica mappings for failures
  • Improved application performance by tuning Informatica mappings
  • Experience with Oracle Index Organized tables
  • Configured ODBC connections in Informatica server
  • Prepared Unit test cases and performed Unit testing
  • Involved in System testing, UAT, production implementation and production support

Environment: Informatica Power Center 8.1, Oracle 11i, TOAD v10, SQL Developer, ERWIN 4.0, DAC, Oracle BI Suite, Oracle EBS.

Confidential, California

Technical lead

Responsibilities:

  • Interacted with the business users to gather the requirements for warehousing the data
  • Preparation of Technical specification document and mapping documents
  • Dimensional data modeling using ERWIN
  • Design and development of Informatica mapping, sessions and workflows based on user requirements
  • Written SQL queries for handling database functionalities
  • Developed Complex Stored procedures for handling the sales representative assignment for the sales and their positions
  • Created deployment groups for migrating Informatica objects into UAT and Production environments
  • Extensive SQL Tuning to improve system performance
  • Scheduling the workflows using DAC provided with Oracle BI Suite
  • Experience using OBIEE reporting with physical and Business model layers
  • Worked with Informatica administrator to create users, to set user privileges, to configure project level settings
  • Debugging Informatica mappings for failures
  • Improved application performance by tuning Informatica mappings
  • Experience with Oracle Index Organized tables
  • Configured ODBC connections in Informatica server
  • Prepared Unit test cases and performed Unit testing
  • Involved in System testing, UAT, production implementation and production support

Environment: Informatica Power Center 8.1, Oracle 11i, TOAD v10, SQL Developer, ERWIN 4.0, DAC, Oracle BI Suite, Oracle EBS

Confidential, California

Senior Developer

Responsibilities:

  • Architected, designed and led development to build the Breakout application
  • Interacted with business users in requirements gathering and analyzed the existing Mainframe application
  • Provided technical expertise to develop architecture solution compliant with company standards
  • Enhanced data integrity and accuracy by eliminating data redundancy and allowing end user to obtain accurate results
  • Identified gaps and overlapping functionalities within existing solutions and developed recommendations
  • Preparation of Technical specification document and mapping documents
  • Dimensional data modeling using MS Visio and ERWIN
  • Design and development of Datastage designer, manager and director based on user requirements
  • Used DB2 API, ODBC and SQL Bulk loader to read and write data into different databases
  • Writing SQL queries for handling database functionalities created stored procedures, packages, triggers, materialized views in Oracle database using PL/SQL
  • Created deployment groups for migrating Datastage objects into UAT and Production environments
  • UNIX shell scripts to schedule Informatica workflows
  • Created sequencers to sequentially execute the designed sequences
  • Experience with designer in importing metadata from repository, new job categories and creating new data elements
  • Created
  • Configured ODBC connections in Datastage server
  • Prepared Unit test cases and performed Unit testing
  • Involved in System testing, UAT, production implementation and production support

Environment: Datastage 8.5, Oracle 10g, DB2 Z/OS 7.0, SQL Navigator, MS Visio, Mainframe, COBOL Files, SQL, PL/SQL, SQL server 2005, ERWIN

Confidential, California

Senior Developer

Responsibilities:

  • Extensive interaction with clients on a daily basis to determine their software needs
  • Preparation of Technical specification document, Design and development of Informatica mappings based on user requirements, Unit test cases document
  • E-R modeling and Dimensional data modeling using ER Studio or ERWIN
  • Writing SQL queries for handling database functionalities
  • Prepared and performed Unit testing, System testing, UAT and production implementation
  • Maintenance of existing functionalities and also providing fixes for production problems
  • Used CONTROL-M to schedule batch jobs
  • Proficiently managed and lead a team of developers

Environment: Informatica Power Center 8.5, Oracle 10g, ERWIN 3.6, SQL Navigator, Control-M, UNIX, DDM, Cognos 8.3 BI Suite, ER Studio

Confidential

Senior Developer

Responsibilities:

  • Interacted with Business users to gather requirements for building the Global Data Warehouse
  • Responsible for design, development and extraction of data from PeopleSoft tables for employee data and flat files from Non Employee Information Management (NEIM) for Contractors
  • Modified existing architecture to in corporate the Bank One Employee data during the merger
  • Tracked project status using multiple linked levels of granularity
  • Adherence to implementation timelines and timely delivery of product
  • Responsible for playing a strategic roll by developing the team into a group of highly technical engineers, in parallel implementing procedures and actions which will act as proactive measures in providing value to our customers
  • Data modeling using ERWIN and TOAD
  • Developed stored procedures, functions, triggers
  • Built UNIX scripts for triggering the database procedures, performing FTP and archiving the files
  • Developed new ETL (Informatica) mappings to support new enhancements
  • Scheduling jobs in AUTOSYS by creating UNIX scripts to trigger the ETL jobs and configuring the AUTOSYS calendar
  • Used Shareplex to capture changes in the Oracle database to mirror the fail over server
  • Used FTP and NDM processes to transmit files to outbound applications
  • Unit testing - Test case identification and preparation
  • Involved in system testing, UAT and production implementation
  • Deployed packages, folders and content store from a development environment to test and then to production environments
  • Solving of Help Desk Issues about the Confidential PeopleSoft DW
  • Test case preparation and testing the ETL mappings.
  • Involved in batch monitoring and resolved Production Issues

Environment: Oracle 9i, Informatica 7.1, DB2, Autosys, UNIX, Shareplex, TOAD, VSS, ERWIN

Confidential, NY

Senior Developer

Responsibilities:

  • Interacted with business users to gather the requirements
  • Involved in the Data Warehouse data modeling based on the client requirements
  • Designed and developed ETL process using Informatica tool
  • Developed mappings, sessions, workflows and workflow tasks based on the user requirement and scheduled the workflows in workflow manager and monitored the load status in the workflow monitor
  • Created UNIX Shell scripts to automate the batch process.
  • Responsible for configuring the Workflow manager, Repository Server Administration console, Power Center Server, Database connection
  • Performance tuning of sources, targets, mappings and SQL queries in the transformations
  • Created Connected, Unconnected and Dynamic lookup transformation for better performance and increased the cache file size based on the size of the lookup data.
  • Created reusable transformations and Mapplets and used with various mappings.
  • Done various optimization techniques in Aggregator, Lookup and Joiner transformation.
  • Developed and Implemented Informatica parameter files to filter the daily data from the source system.
  • Implemented type 2 slowly changing dimensions.
  • Used Informatica debugging techniques to debug the mappings and used session log files and bad files to trace errors occurred while loading.
  • Created Oracle Stored Procedure to implement complex business logic for good performance and called from Informatica using Stored Procedure transformation.
  • Creating Test cases for Unit Test, System Integration Test and UAT to check the data.
  • Responsible for migrating the mappings and sessions from development repository to production repository box, creating shortcuts to share the repository objects.
  • Used various Oracle Index techniques like B*tree, bitmap index to improve the query performance and created scripts to update the table statistics for better explain plan
  • Created Materialized view to summarize the data based the user requirement to improve the Business Objects report queries performance.
  • Responsible for loading data into warehouse using Oracle SQL Loader for history data.
  • Coordinating with the client and gathering the user requirements.
  • Designed and developed ETL process using Informatica.
  • Performance tuning of sources, targets, mappings and SQL queries in the transformations.
  • Created reusable transformations and used in various mappings.
  • Responsible for migrating the folders or mappings and sessions from development to production environment.
  • Responsible for scheduling the workflow based on the nightly load
  • Used various debugging techniques to debug the mappings
  • Developed Informatica parameter files to filter the daily source data
  • Created Oracle Stored Procedure to implement complex business logic for good performance and called from Informatica using Stored Procedure transformation
  • Creating Test cases for Unit Test, System Integration Test and UAT to check the data quality.

Environment: Informatica Power Center 6.1, Oracle 9i, SQL Server 2000, SQL, MS Visio, Windows 2000, AIX

Hire Now