SUMMARY

• Nearly 8+ years of Intelligent Data Integration and ETL experience in application development with the large Enterprise Data Warehouse, Big Data and Business Intelligence systems.
• Strong Knowledge on contemporary Data Warehousing trends - Ralph Kimball, Bill Inman methodologies, Star Schema, Snowflake schema, ODS, EDW, DM, OLTP, OLAP, SCD and Facts.
• Analyze, validate and refine Business and Functional requirements to implement the strategic Data Warehouse Life Cycle for efficient Business Intelligence trends.
• Develop or enhance new/existing dimensional models in achieving scalable, reliable and optimal physical, logical and conceptual data models.
• Prepare Source to Target Data Mappings, ETL Design and technical specification documents in compliance with the Enterprise Data Governance and Stewardship committee standards.
• Proficient in developing ETL Informatica Mappings, Mapplets, Sessions, Workflows using Power Center tools.
• Experience in consuming SOAP and REST services to invoke ETL flows through Web Services configured via Informatica Power Center Web Services Hub.
• Develop SQL scripts, PLSQL procedures, functions, packages, Linux Scripts and Parameter files that store, retrieve and manipulate data for data analytics.
• Develop reusable code snippets, automated data movement reconciliation and Batch Framework techniques.
• Implement Error and Exception handling mechanism for any fatal and non-fatal errors and enforce recovery techniques.
• Perform Data Validation and Reconcilement techniques for each data movement process and log the variances or results for auditing purpose.
• Implement Data Retention techniques to retain the data for few years and archive the remaining data up to 10 years. Enforce purge mechanism to drop very old data extracts.
• Building Hive tables and running the Spark Scripts within Hadoop, also
• Involving in implementing the batch schedules jobs using Impala, Spark and HiveQL scripts in automating the jobs on an incremental daily basis.
• Implementing Spark SQL to access hive tables into spark for faster processing of data.
• Involving in converting Hive/SQL queries into Spark transformations using Spark RDDs, Python and Scala.
• Experience in FTP/SFTP to move the flat files from one server/application to other using Connect Direct.
• Implement performance tuning techniques at database or ETL by troubleshooting source/target bottlenecks.
• Proficient in leveraging latest SMP and MPP databases like Oracle 12c, Teradata and IBM Netezza in achieving better performance.
• Automate the Deployment process using DevOps tools Jenkins, uDeploy, GitKraken and Artifactory.
• Work with DBA and ETL admins in configuring Databases, Repositories, Operating System Profiles (OSP), Red Hat Linux NAS/SAN servers and automated deployment process.
• Good knowledge on Cloud Integration, Big Data, Hadoop File System, Financial and Insurance markets.

TECHNICAL SKILLS

Data Warehouse: Ralph Kimball and Bill Inman Methodologies, SCD Type 1 or Type 2, Star Schema, Snow Flake Schema, Dimensions, Facts, OLTP, ODS, EDW, DM and OLAP.

Data Model: Dimensional Model and ER Models.

Databases: Oracle 12c.Teradata and Netezza.

Files: Fixed Width Flat Files, Delimited Flat Files and XML.

Languages: SQL, PLSQL, NZSQL and Shell Script

TOOLS: Data Integration Informatica Power Center Tools (10.x or 9.x), Power Center Web Services Hub

Data Model: Erwin and Visio

Database: Teradata Studio, AQT, TOAD, SQL Developer and SQL Plus.

FTP: Connect Direct, Putty, Ultra Edit and WinSCP, Mobaxterm

DevOps Tools: GIT, uDeploy, Artifactory, Jenkins, Tortoise SVN and CA Software Change Manager

Schedulers: ControlM, CA7, CICS Explorer, ESP (CA WA Workstation) and Autosys.

Testing: HP ALM Quality Center

Incident Management: Service Now, Oracle Identity Manager (OIM)

Hadoop Tools: Hive,Impala,Scala and Spark

PROFESSIONAL EXPERIENCE

Confidential, Baltimore, MD

Sr. ETL Developer/ETL Designer

Responsibilities:

• Analyzing, Validating and Refining Business Requirements, Functional and Non-Functional requirements, defining development scope and technical specification documents.
• Performing deep dive analysis on upstream systems to identify the Key Performance Indicators (KPI), transformation rules, data load strategy and data gaps in order to accomplish project data requirements.
• Developing or enhancing dimensional models in compliance with the Data Governance and Stewardship standards and fit it into Enterprise Architecture.
• Preparing Source to Target Mappings (STM) for each data movement process from heterogeneous sources to staging and then to Data Warehouse and Data Marts.
• Designing each ETL process with complete data load and refresh strategy, data reconcilement and validation techniques, Error Logging and Restart ability/Recover Strategy, Data Retention and Archival/Purge mechanism.
• Designing Batch Framework with complete job statistics that include Job Name, status of the job, load frequency, source/target data volume, execution start and end dates, dependency and error details, etc.
• Developing Informatica Mappings, Sessions and Workflows for each data movement process that include Source to Work (Truncate and Load), Work to Stage (Insert/Persistent) and Work to DIM/FACT (Change Data Capture).
• Extracting data from many different data sources including legacy systems or existing databases and loading the data into Hadoop.
• Importing copybooks from mainframes to Informatica Power center
• Responsible for moving data from those to Hadoop cluster environment including the necessary data reconciliation process.
• Data Integration in application development by integrating Oracle, SQL Server, Teradata databases, flat files and XML for Enterprise Data Integration applications.
• Implementing Error Handling in relational databases to track each rejected record to identify the bad data and error messages.
• Building Hive tables and running the Spark Scripts within Hadoop, also involving in converting Hive/SQL queries into Spark transformations using Spark RDDs, Python and Scala.
• Coding PLSQL procedures, triggers, packages and DB components (Stored Procedures) which can be leveraged in multiple applications and avoid repetitive code.
• Preparing Database Scripts (DDL, DML, DCL and TCL), Linux/Shell scripts and Scheduling Batch Scripts.
• Conducting Code Reviews with ETL and DB Admins in order to comply with the Industry Best Practices and Development Standards.
• Performing Data Quality Checks and Data profiling to cleanse the source data using Informatica IDQ tool.
• Perform troubleshooting on long running jobs and identify the opportunities to optimize the performance.
• Creating Journal Entries after successful reconciliation of balances, accounts from the General Ledger.
• Scheduling the Informatica Workflows or jobs using ControlM scheduler based on operational calendar.
• Implementing Data Masking techniques to protect Personally Identifiable Information (PII) from the users.

Environment: Informatica Power Center Tools 10.1, Power Center Web Services Hub, Teradata, Hadoop Hive, Spark, Impala, Scala, Pig, Map reduce Oracle 12c, SQL, LINUX, Flat Files, Teradata Studio, Toad, Connect Direct, Putty, GIT, Artifactory, uDeploy, CA7 scheduler, Oracle Identity Manager, HP ALM Quality Center, Ultra Edit, WinSCP and Service now.

Confidential, Columbus, OH

Sr. ETL Developer

Responsibilities:

• Analyze business requirement, define development scope and develop technical specification documents.
• Code, test, modify, debug, document, and implement ETL workflows using Informatica Power Center tools.
• Solid experience with data modeling and database design for operational as well as analytical systems.
• Develop scripts to automate the execution of ETL Workflows/mappings using shell scripts under UNIX environment.
• Adhere to IT Control Policies throughout design, development and testing and in corporate Corporate Architectural Standards into application design specifications.
• Participate in code & design reviews and ensure that all solutions are aligned to pre-defined architectural specifications; identify/troubleshoot application code-related issues; and review and provide feedback to the final user documentation.
• Develop Batch Framework through Perl scripts which set Informatica Environment, Configure Database Connections, Trigger Informatica workflows, Update Job Status tables and create daily log files.
• Extensive experience in writing PLSQL blocks including Stored Procedures, Functions, Triggers and Packages with Cursors, CLOB, BLOB and LONG datatypes. Developed error handling and data retention procedures.
• Developed Change Data Capture flows for incremental loads, PLSQL blocks that perform gather stats, data archiving and purging during DB maintenance window.
• Version and revision control practices and procedures for all ETL components including Informatica Workflows, DB scripts, Parameter files and configuration files thru Tortoise SVN and CA Software Change Manager.
• Develop ESP Jobs, Events and applications through CA WA Workstation and CICS Explorer tools.
• Develop Data Quality scripts that will validate the source and target systems after loading/generating the data for downstream systems.

Environment: Informatica Power Center Tools 9.6.1 Hot Fix, Oracle, PL/SQL, SQL, LINUX, Flat Files, XML, Perl, Toad, Putty, Tortoise SVN, CA Software Change Manager, CICS Explorer, ESP (CA WA Workstation), HP ALM Quality Center, Ultra Edit, WinSCP and Service now.

Confidential, Columbia, MD

ETL Informatica developer

Responsibilities:

• Requirement gathering discussing with business users and stakeholders. Converting functional requirements into technical requirement by creating ETL Specification Document.
• Reviewing Business Requirement documents and getting a signoff from Business team on these documents before starting development.
• Creating ETL mapping designs using Informatica Power Center 9.6.1-Source Analyzer, Designer, Workflow manager, Workflow monitor, Repository Manager. Adopted agile development methodology.
• Implemented slowly changing Dimension (SCD Type 1) to load Dimension Tables.
• Extensively worked with Salesforce UI and imported SFDC objects into Informatica 9.6.1 for data transformation. Good understanding of Salesforce SOQL queries, workbench and SFDC transformations.
• Experience migrating Informatica repository from 9.1 to 9.6 by creating temporary repository and migrating repository objects to temporary repository.
• Worked with various Salesforce objects such as User, Opportunity, Opportunity Line Item, Process Instance, Process Instance Node, Process Instance Step, Process Instance Work Item, Process Node, and Product.
• Responsible for end-to-end implementation of ETL solution including development, debugging, unit testing, SIT, deployment, scheduling, post-production bug fixing.
• Participated in UAT team meetings for Release Sign Offs.
• De-normalized product data using referential integrity to be used in the dimensional star model.
• Deployed Informatica mappings and workflows along with DDL scripts to development, testing and production environments.
• Used basic UNIX commands to verify session logs on UNIX server.
• Performed reverse engineering to create logical/physical data models for the data warehouse using ERWin data modeling tool.
• Used HP ALM to create unit test scenarios and to create project milestones.
• Created Informatica mapplets and used those in various mappings to retrieve employee key and date/time keys.
• Experience upgrading repository contents from new domain.
• Experience disabling current repository and creating repository service pointing to temporary repository.
• Mentored a small team of developers at offshore and delegated work to them.
• Performance tuning on Informatica mappings to reduce run time and retrieve data faster. Created indexes on Oracle tables and tuned SQL queries to optimize query performance.
• Altered mappings to implement enhancements as part of new business requirements.
• Experience enabling new repository service and migrating contents of old repository to new one.
• Used Sequence generator transformation for Surrogate keys acting as PK in the dimension table.
• Brief exposure to SAP Business Objects and Tableau to generate several on-demands and scheduled reports.
• Created E-mail notification tasks using post-session scripts.
• Used Workflow Manager to Schedule workflows and linked sessions according to job dependency.

Environment: Informatica 9.5,Oracle SQL developer, UNIX, Google Drive, Putty, File Zilla, ER win, HP ALM, Rally.

Confidential, Hartford, CT

ETL Developer

Responsibilities:

• Interact with business analysts and end users to understand requirements and convert business specifications to technical artifacts and determine most efficient design solution across multiple integration projects
• Adhere to architecture standards, risk management and security policies, leading presentations in architecture review, strategic technology directions, best practice development (eg, estimating models), mentoring less experienced team members, code reviews
• Design, analyze, develop, code, test, debug and document programming to satisfy business requirements
• Develop Change Data Capture flows using Informatica, Oracle, UNIX (Korn Shell scripts), and PL/SQL blocks.
• Thoroughly perform unit testing ensuring minimal code defects out of build phase into system test phase
• Good communication skills and solid knowledge on software delivery lifecycle in large Warehouse environment
• Implement Data Quality techniques for each incremental load that captures changes through Change Data Capture mechanism from the upstream systems.
• Extensive Hands on experience designing and developing all Data Integration flows using Informatica.
• Well versed in the following data domains: Master Data, Operational Data, Analytical Data, Unstructured Data
• Complete code reviews for ETLs and related components, and complete documentation of issues identified and action items.
• Correct testing defects and support all testing, including but not limited to: Development Integration Testing, System Testing, User Acceptance Testing, End-to-End Testing, and Performance Testing.
• Provide resolution to an extensive range of complicated ETL-related problems, proactively and as issues surface.
• Work with initiative leads, business areas, technical leads, architects, subject matter experts, developers, technical support groups and test teams to deliver value to the business.

Environment: Informatica Power Center 9.5.1, Teradata, Oracle, PL/SQL, SQL, SQL Loader, LINUX, Flat Files, Toad, Putty, Tortoise SVN, Autosys, HP ALM Quality Center and Ultra Edit.

Confidential, Hartford, CT

ETL Developer

Responsibilities:

• Create data mapping and workflow using Informatica Power Center to extract, transform and load data into the target reporting environment.
• Conform to Informatica workflows and mapping standards so that development is done in a consistent manner across all development resources (e.g., no hard coding, leverage of mapplet / reuse concept).
• Develop SCD Type 1, Type 2 using Change Data Capture to maintain history of transactions for Incremental.
• Tune and optimize ETL jobs for performance and throughput. Interact with data modelers and DBAs on data architecture optimization for ETL performance.
• Provide development support, including walk through approvals, to other ETL application resources to insure that standards are followed and optimized workflow is implemented.
• Advocate and enforce secure coding principles, as defined coding policies, to insure we are in compliance with all defined standards.
• Assist in documenting and maintaining operational procedures in the form of an operations manual for all ETL workflows.
• Writing Data Quality validation procedures to enable data completeness strategy for accurate information for BI
• Assist with troubleshooting efforts for ETL job issues related to pre-production and production.
• Leverage Data Quality as a mandatory process step for profiling data prior to downstream transformation.
• Contribute to the design and development of Informatica master data management components of the solution in support of both member and provider master indexes.
• Lead rules development for creation of a golden record for the most accurate domain content across member and provider possible.
• Design and develop test plans for ETL unit testing and integration testing.
• Provide to the support team and customers.

Environment: Informatica Power Center 8.6/9.1, Oracle, SQL Server, PL/SQL, SQL Server Management Studio, SQL Developer, LINUX, Flat Files, Toad, Putty, Telnet, Tortoise SVN, Autosys and HP ALM Quality Center.

Confidential

ETL Consultant

Responsibilities:

• Analyzing Business Requirements, Functional and Non Functional requirements to design and prepare technical specification documents.
• Validating the source to target mappings, identify transformation rules to implement data conversion techniques.
• Preparing ETL solution design documents describing the data load and data fresh strategy. Data Quality checks and Error Handling techniques.
• Designing and creating the data warehouse and all related extraction, transformation and load of data functions.
• Writing oracle queries, procedures and functions to store, retrieve and manipulate the data for enterprise applications.
• Creating Informatica workflows and mappings to load history and incremental data using Change Data Capture mechanism.
• Creating Unit Test Plans, Unit Test Cases, Minus Queries, Data Validation scripts and producing Unit Test Results.
• Coding UNIX scripts to access, find and modify the delimited flat files to do SFTP/FTP to other files share locations.

Environment: Informatica 8.6, Oracle, UNIX, SQL Plus, Putty, Flat Files and HP ALM.