- 9 years of extensive experience with Informatica Power Center 9.x/8.x/7.x, IDQ, Informatica ETL Developer in full life cycle of Software Development (SDLC) of requirements gathering, Analysis, Application Design, Development, Testing, Implementation, System Maintenance, Documentation and support of Data Warehousing applications.
- Databases of experience using Oracle, DB2, MS SQL Server, Teradata, MYSQL, ORACLE 11g/10g, PL/SQL and tuning and Big Data Management Tools like Hive.
- Worked in Agile and Waterfall mode of Software Development methodology.
- Expertise in Data Warehouse/Data mart, ODS, OLTP and OLAP implementations, Analysis, requirements gathering, data modeling, ETL Design, development, System testing, Implementation and production support.
- Expertise in writing complex SQL quires, Oracle, PL/SQL programming, Stored Procedures, Package, Functions, Triggers, Views, Materialized Views, cursors and tuning the SQL query.
- Expertise in writing daily batch/streaming jobs using UNIX shell scripts and developing complex UNIX Shell Scripts for automation of ETL. Korn or Bash Shell scripting experience in a Unix / Linux environment.
- Expertise with Teradata utilities like Fast Load and Multi Load and Tpump and Teradata Parallel transporter and highly experienced in Teradata SQL Programming. Expert in performance tuning and dealing with huge volume of data.
- Proficient in implementing complex business rules through different kinds of Informatica transformations, Workflows/Worklets and Mappings/Mapplets.
- Knowledge in installing, configuring, and using Big Data tools Hadoop ecosystem components like Hadoop Map Reduce, HDFS, Hive, Sqoop, Apache Spark and Kafka.
- Experience in AWS S3, EC2, SNS, SQS setup, Lambda, RDS (MySQL) and Redshift cluster configuration.
- Experience in using Automation Scheduling tools like Autosys and Control - M.
- Knowledge analyzing user requirements, procedures, and problems to automate processing or to improve existing job flows and schedules systems.
- Expertise working on Informatica transformations such as Source Qualifier, Joiner, Filter, Router, Expression, Lookup, Aggregator, Sorter, Normalizer, Update Strategy, Sequence Generator and Stored Procedure transformations.
- Good understanding of Amazon AWS Architecture, EC2, S3 bucket, Redshift & AWS CLI commands.
- Knowledge in developing data processing tasks using pySpark such as reading data from external sources, merge data, perform data enrichment and load in to target data destinations.
- Strong ability to understand, document and communicate technical architectures, standards, best practices, toolsets and solutions.
- Used SQL queries to identify data issues, data fixes, manual extracts, etc.
- Data Researching, Data Collection, Quality Assurance, Analysis and Problem-Solving Skills.
- Designed and developed approaches to acquire data from new sources like Mainframe (DB2), and AS400 (DB2).
- Maintained documentation for all processes implemented.
Tools: Informatica PC 10.x/9.x/8.x, Informatica Power Exchange, ICS, ODI, Informatica Data Quality 10.2.1,10.2.2 and Informatica Analyst tool 10.2.1, SSIS, SSAS, SAP Data services.
Languages: XML, UML, HTML, C, C++, UNIX, Shell Scripting, SQL, PL/SQL, T-SQL, python 3.7, AWS Lambda, Power shell, pySpark.
Database: Oracle 11g/10g/9i/8i/, SQL Server, IBM DB2, MS Access, Teradata, Snowflake, MYSQL, Postgres, Oracle, Hive, SSAS, ANSI SQL, AS400.
Operating System: Windows 98/NT/2000/2003/XP/Vista/7, Sun Solaris 5.8/5.6, HP-UX, DOS, Red Hat Linux, SOAP, UnixAIX5.3/4.3.
Reporting & Data Modeling Tools: Business Objects, Cognos, Erwin Data Modeler, Tableau, Power BI, SSRS
Other Tools: TOAD, SQL Loader, SQL Plus, Query Analyzer, Putty, MS Office (MS Access, MS Excel, MS PowerPoint, MS Word, S3 and Scala, Control-M, AUTOSYS, Salesforce, Azure DB SQL, Big data management Tools.
- Involved meetings with End User and responsible for gathering business requirement.
- Worked on Agile Methodology, participated in daily/weekly team meetings.
- Worked on creating Parallel jobs using various stages like Join, Merge, Lookup, remove duplicates, Filter, Dataset, Lookup file set, Complex flat file, Modify, Aggregator, XML.
- Worked on Informatica Data Quality (IDQ) toolkit, analysis, data cleansing, data matching, data conversion, address standardization, exception handling, reporting and monitoring capabilities of IDQ.
- Update Strategy, Aggregator, Expression, Joiner Transformations and then loaded into data Warehouse using Informatica BDM 10.2.2.
- Involved in Dimensional modeling (Star Schema) of the Data warehouse and used Erwin to design the business process, dimensions and measured facts.
- Extracted the data from the flat files (csv, excel, dat, Json. and other RDBMS databases into staging area and populated onto Data warehouse.
- Worked on different tasks in Workflows like sessions, events raise, event wait, decision, e-mail, command, worklets, Assignment, Timer and scheduling of the workflow.
- Created sessions, configured workflows to extract data from various sources, transformed data, and loading into data warehouse.
- Developed mapping parameters and variables to support SQL override.
- Created mapplets to use them in different mappings.
- Handle Informatica administrator’s responsibilities such as managing connections, create support cases, create accounts as well as document proof-of-concepts connections for MySQL, S3, SQL Server, Snowflake, etc.
- Migrated data from legacy systems SQL server 2000, AS400 to Snowflake and SQL server.
- Extensively used the DQ transformations like Address validator, Exception, Parser, Standardizer, Solid experience in debugging and troubleshooting Sessions using the Debugger and Workflow Monitor.
- Used SQL scripts and AWS resources (Lambda, Step Function, SNS, S3) to automate data migration.
- Worked with multiple divisions throughout the organization to conform with best practices and standards.
- Configured and maintained domain and application services like repository services and integration services.
- Configured and maintained domain and cluster for Informatica Developer 10.2.2.
- Perform Informatica administration functions (user access accounts, roles, privileges, LDAP configuration.)
- Created connections including Relational connection, Native connections, and Application connections.
- Troubleshoot and resolve any platform issues. Installation and upgrade of Informatica from 10.2.1 to 10.2.2 applications, backup, recovery, and security of the infrastructure.
- Used Informatica Power Exchange connector to read DB2 Mainframe Sales system data and load into Salesforce (Sales & Marketing Clouds) using Salesforce Connector.
- Installed and configured Informatica Power Exchange for CDC and Informatica Data Quality (IDQ).
- Created Lambda functions to update and migrate local flat files to S3.
- Wrote UNIX shell Scripts & PMCMD commands for FTP of files from remote server and backup of repository and folder
- Worked on end to end data quality and data governance initiatives utilizing Informatica Data Quality (IDQ) and PowerCenter implementing capabilities around Address validation, Contact Validation, Business Glossary, Data Stewardship, Data Quality and Data Lineage
- Deployed address verification which includes transliteration, parsing, analysis, standardization and enrichment at point of entry and batch modes
- Collaborated with various business and technical teams to gather requirements around data quality rules and propose the optimization of these rules if applicable, then design and develop these rules with IDQ performed data profiling with multiple usage patterns, root cause analysis and data cleansing and develop scorecards utilizing Informatica, Excel and other data quality tools
- Built profiles and scripts (Linux) to execute and test mappings and workflows to implement data stewardship and exception processing
- Developed rules in Data Quality and Deploy in Power center as batch and web services
- Run data quality specific ETL jobs (address standardization and validation, email cleanups, name cleanup, parsing, etc.) utilizing IDQ and PowerCenter
- Worked on Unix based File System good in log monitoring, analyzing and providing remediation steps.
- Worked with informatica support for fixing Informatica Linux server issues.
- Worked on moving S3 folders and buckets to cloud using Python in Lambda. Hands on python development
Environment: Informatica BDM 10.2.1 and 10.2.2, Big Data Quality (BDQ/IDQ), ICS, ETL, Attunity, Shell, SQL Server, DB2, Oracle, Salesforce, AS400,AWS S3, Teradata, Snowflake, Aurora, Hadoop 2.9, Informatica Administrator console, informatica analyst, Postgres SQL, Hive, Linux, shell, Python 3.6, Salesforce.com, Informatica Cloud.
Confidential, Long Island
ETL BI Developer
- Gathered the requirements from End User and translated the business design into technical design.
- Extracted data from various sources like SQL Server, Oracle, Excel, CSV and Flat files from Client servers.
- Extensively used Control Flow items such as For Loop, Foreach loop, Execute Package Task, Execute SQL Task, FTP Task, Script Task, Send Mail Task.
- Worked with transformations like Conditional Split, Lookup, Merge Join, Aggregate, Derived Column, Union All, Multicast Import Column, Export Column, Row Count, Row Sampling, Script Component for creating many SSIS packages.
- Created Variables to pass parameterized values into the package, to makes the package Dynamic.
- Developed, monitored, scheduled and executed SSIS package by using MSVS and SQL Server Agent.
- Extensively used SSIS to implement the Slowly Changing Transformation, to maintain Historical Data in Data warehouse.
- Worked on automating SSIS jobs using SQL Server Job Agent.
- Developed, deployed, and monitored SSIS Packages.
- Worked on Event Handlers to do Event handling on error at the package and task level on error, on warning and configured it using send email task and script task.
- Identified dimensions and Involved in building Facts and Dimension tables.
- Created and deployed cubes in Star and Snowflake schema.
- Created Sub-Reports, Drilldown-Reports, Dynamic sorting, Summary Reports, and Parameterized Reports, Matrix reports, Charts (like, Gantt Chart) based on Business requirements in SSRS.
- Created reports with query parameter to present only the required data to the users and reduce the required data retrieval time.
- Using Talend extracted the information from various disparate data sources including but not limited to Oracle, Netezza, MySQL, Mongo, Hadoop, MSSQL, and flat files and loaded into destination.
- Experience with big data platform (i.e. Hadoop) big data tools such as Apache Spark.
- Proficiency in SQL, big data technologies and working with large data sets.
- Ability to translate business attributes (GUI Labels, Reporting Attributes) into Data Model elements
- Used reverse engineering techniques to find the source tables and fields for modifications.
- Worked on creating PowerBI reports and interactive Dashboard/Visualizations. Experience using filters, parameters and calculated fields on data sets.
- Created Jobs Performance report that queries system tables to track the duration of each job and weekly-average duration using SSRS.
- Designed and created data extracts, supporting SSRS, POWER BI, other visualization tools reporting applications.
- Created Datasets in T-SQL, stored procedures for SSRS. Developed Excel PowerView, PowerQuery and PowerPivot dashboards for data analysis
- Performed Index analysis for tables and came up with more efficient solutions to use Clustered and Non-Clustered Indexes for significant performance boost using index tuning wizard.
- Configure and maintain using Report Manager and Report Server for SSRS, Deployed and Scheduled the Reports in Report Manager
- Created Reports using Charts, Gauges, Tables, matrix. Created Parameterized Report, Dashboard Report, Linked report and Sub Report by Year, Quarter, Month, and Week
- Created Drill Down Reports, Drill Through Report by Region.
Environment: ETL, SSRS, SSIS, SSAS, Oracle E-Business Suite (EBS), Talend 6.2, Netezza 3.0, Hadoop work bench, putty 0.64, Teradata 14.0, Kafka, SQL Server 2014, Netezza, UNIX, Toad, PL/SQL, DB2, PowerBI.
- Worked in Agile development methodology environment and Interacted with the users, Business Analysts for collecting, understanding the business requirements.
- Worked on building the ETL architecture and Source to Target mapping to load data into Data warehouse.
- Involved in the installation and configuration of Informatica Power Center 10.1 and evaluated Partition concepts in Power Center 10.1
- Developed various mappings using Mapping Designer and worked with Aggregator, Lookup, Filter, Router, Joiner, Source Qualifier, Expression, Stored Procedure, Sorter and Sequence Generator transformations.
- Created stored procedures, views, user defined functions and common table expressions in SQL and Hadoop.
- Knowledge of relational DBMS management and structure, including primary, Surrogate Key and foreign key constraints, indexes and database constraints, data encryption, database triggers, stored procedures, SQL Alerts, SQL Trace, Database Links, other features of T-SQL Server as needed.
- Generated underlying data for the reports through SSIS exported cleaned data from Excel Spreadsheets, Text file, MS Access and CSV files to data warehouse.
- Created and Configured Power Exchange CDC Environments listener and Logger.
- Registered CDC tables in Power Exchange Navigator for different source systems and Importing CDC tables in Power Center Designer.
- Worked on change data capture (CDC) by using the MD5 function of Informatica.
- Fluent in T-SQL programming language: creating and managing database objects, stored procedures, triggers, views and user defined functions.
- Worked with external teams on Informatica real time CDC and batch workflows, implementation and testing of features in integrated environment
- Expert in T-SQL query development and analysis: stored procedures, triggers, views, etc. Expert in T-SQL querying for creating data extracts. loaded data sets and translating complex functional and technical requirements into detailed design. also performed analysis of vast data stores and uncover insights.
- Experience in Snowflake advanced concepts like setting up resource monitors and performance tuning
- Experience in Snowflake modelling - roles, schema, databases.
- Expertise in creating simple and parameterized reports and complex reports involving Sub Reports, Matrix/Tabular Reports, Charts and Graphs using SSRS in Business intelligence development studio (BIDS).
- Implemented very large-scale data intelligence solutions around Snowflake Data Warehouse.
- A solid experience and understanding of architecting, designing and operationalization of large-scale data and analytics solutions on Snowflake Cloud Data Warehouse is a must.
- Performs unit testing, QA, and work with business partners to resolve any issues discovered during UAT.
- Reviewed ETL performance and conducts performance tuning as required on mappings / workflows or SQL.
- Created UNIX scripts for parsing and modifying data and experience in using AUTOSYS job scheduler for automation of UNIX shell scripts and batch scheduling.
- Strong experience in importing & exporting database using SQL Server Integrations Services (SSIS) and Data Transformation Services (DTS Packages).
- Built SSIS packages involving ETL process, extracting data from various flat files, Excel files, legacy systems and loading into SQL server.
- Involved in importing the existing Power center workflows as Informatica Cloud Service tasks by utilizing Informatica Cloud Integration.
- Configured MDM Stage tables for data load into MDM Base tables and validate the load, aswell as configuration elastic search to be used on MDM UI.
- Tuned MDM configuration and MDM APIs to meet SLA’s for downstream and upstream systems
- Provided solution design, implements, and supports robust and complex MDM initiatives
- Collaborated with source systems data stewards, system owners and technical personnel for data governance and resolves any data quality or technical issues related to data ingestion
- Experienced on data and KPI’s or views which will be shown on final dashboard.
- Created Teradata External loader connections such as M Load, Upset and Update, Fast load while loading data into the target tables in Teradata Database.
- Strong SQL knowledge and working experience in Teradata Stored Procedures/BTEQ scripts.
- Proficiency in TD load and export utilities like TPT, TPT/MLOAD/TPUMP/BTEQ/FAST EXPORT, Multi load, Schedulers.
- Troubleshoot and diagnose issues related to Informatica Production MDM data loads and transformation
- Has hands-on experience with Informatica MDM Hub configurations - Data modeling & Data Mappings (Landing, staging and Base Objects), Data validation, Match and Merge rules, Active VOS, SIF Framework and MDM User Exits.
- Has created MDM Batch Jobs & set up Security for applications.
- Experience in creating FACT tables. Knowledge of reporting tools especially Tableau would be advantageous.
- Involved in creating tables in Teradata and setting up the various environments like DEV, SIT, UAT and PROD.
Environment: Informatica Developer, C++, Oracle12C, AWS, informatica Cloud, Teradata 14.0, SQL Server 2014,, Autosys Scheduler Tool, UNIX, Toad, PL/SQL, SSIS, SSRS, T-SQL, Power Connect, DB2, Tableau 10.1, Ab Initio, Power Shell, Salesforce, Hadoop, Scoop, Python.