Ranjan Sahoo

Data Engineering Consultant

Summary:
• 13+ years of total IT experience in the business requirements analysis, designing, coding,
development, testing and implementation of business applications with Azure Data Factory,
Databricks, ADLS, Azure SQL DB, Data Warehouse/Data marts, ETL-Informatica, and client/server
environment applications for HR, Insurance and BFS domain (Capital Markets).
• Currently associated with TCS as Associate Consultant.
• Having experience in handling different kind of product of capital market (FX, FI, MM, Brokerage,
Commission, derivative etc).
• Adept in handling projects efficiently for delivering the required applications in a structured way.
• Gained sufficient knowledge of complete of Agile and spiral methodologies of Software
Development Life Cycle (SDLC) and bug fixing of applications in various environments.
• An effective communicator with excellent relationship building & interpersonal skills; strong
analytical, problem solving & organizational abilities; possess a flexible & detail oriented attitude
• Strong exposure on the Azure Data Bricks, Azure data Factory, ADLS, Azure SQL, Azure Synapse,
Azure Blob, Scala, Python, Informatica Power Center (ETL Tool), ORACLE (SQL, PL/SQL), UNIX Shell
Scripting, Autosys, REPORTING-OBIEE, TABLUE

Skills:
• Software Products • Tools
• Software Languages: • Azure data factory
• Data Bricks (PyScripts) • Informatica Power Center 9x
• Python Scripting • Git Hub
• Azure SQL & PL/SQL • Rapid SQL
• ETL/ Informaica Power centre • SharePoint
• Unix Shell Scripting • SVN
• • Toad
• Databases: • TFS
• Oracle • Service Now
• ADLS •
• Sybase • Reporting Tool:-
• MySql • OBIEE
• • Tableau
• Scheduling Tool: • Cognos
• Autosys • Cloud Technologies:-
• • Azure.
• Operating systems: • Azure Data factory
• Windows • Azure Data Bricks
• HP UNIX • ADLS
• Red hat Linux • General Packages:
• AIX UNIX • MS office 2003/2007/2010
• SSH Tectia •
•
•
Experience:
Project Title1: CBFAT (Wholesale Banking)
Role: Data Engineer
Cloud: Azure Cloud
Technology: Azure Data Factory, Azure Data bricks, ADLS, Azure Synapse, Azure SQL
database, Informatica power centre 10x, UNIX Scripting, Oracle, Autosys
Languages: Pyscript, Data Bricks, PL/SQL
Integration: Azure Devops (CI-CD pipeline)
Database: Azure SQL DB
Team Size: 5
Duration: 2 year
Client: CIBC (Canadian Imperial Bank of Commerce).

The CBFAT WB (Wholesale Banking) is part of the bank CIBC, Canada. It deals with the capital
market trading (CMT), where we have stored the trading/dealing data from various source
system/legacy systems depending upon the region. We received input as file from the different
legacy system, and then we do load to our landing zone using Azure Data Factory pipeline which
stores as it is data in file using, Then the corresponding data goes from incoming to our staging
area using certain validation of Data Bricks notebook where we do data cleansing and validation.
After the data loaded in to staging, we have different notebook designed to load transactional
data to our data warehousing to maintain dimension and fact table on sync. We generally used
md5/scd2 for loading the dimension and fact table, we also have few Azure SQL stored
procedure which is loading the data into DW. Finally after updating the DW, our Cognos jobs are
get triggered and send the daily, monthly, quarterly report to the respective customer. We have
used Azure Devops as an integration tool for CI-CD, using Git hub as a repository and Azure
Devops for story management.

Responsibilities:
 Involved in functional study of the application and Interact with client to understand
Tech Spec.
 Developing new pipeline, dataflow, notebook, python scripting (PyScript) and
implementing business logic to meet customer requirement and satisfaction.
 Understanding and modifying the existing notebook, dataflow, python Scripting etc .
 Completely involved in end to end cloud migratation of Informatica to Azure.
 Scheduling job using the tool called Autosys.
 Create Test Case, Unit testing, functional and Developed Notebook code documentation.
 Interactions with Business Analyst and Dev teams to resolve issue.
 Also working on any production issue on priority.

Project Title2: WB (Wholesale Banking)

Operating System: UNIX
Technology: Informatica 9x, SQL, UNIX, PL/SQL
Languages: PL/SQL
Database: Oracle
Team Size: 4
Duration: 8 year
Client: CIBC (Canadian Imperial Bank of Commerce).

The project WB (Wholesale Banking) is part of the bank CIBC, Canada. It deals with the capital
market trading (CMT), where we have stored the trading/dealing data from various source
 system/legacy systems depending upon the region. We received input as file from the different
legacy system, and then we do load to our incoming table which stores as it is data in file using
the unix shell scripting. Then the corresponding data goes from incoming to our staging area
where we do data cleansing and validation. After the data loaded in to staging, we have one data
mart job which starts to load the staging data in to our data warehousing to maintain dimension
and fact table on sync. We generally used md5/scd2 for loading the dimension and fact table, we
also have few Sybase stored procedure which is loading the data into DW. Finally after updating
the DW, our Cognos jobs are get triggered and send the daily, monthly, quarterly report to the
respective customer. we use Autosys for scheduling the jobs and SVN for deployment activity.

Responsibilities:
• Involved in functional study of the application and Interact with client to understand Tech
Spec.
• Developing new pipeline, dataflow, notebook, python scripting and implementing business
logic to meet customer requirement and satisfaction.
• Understanding and modifying the existing notebook, dataflow, python Scripting etc .
• Completely involved in end to end cloud migratation of Informatica to Azure.
• Scheduling job (Informatia Wf, unix Shell Script, file watcher) using the tool called Autosys.
• Create Test Case, ETL design specifications and Developed ETL code documentation.
• Interactions with Business Analyst and Dev teams to resolve issue.
• Also working on any production issue on priority.
• Running the Jobs/workflows for ETL process.
• Prepared and ran SQL queries to verify Dimensional and Fact tables.
• Created /update scripts for recasting the old structure into new for Business Recast

Project Title3: RP Dev

Client- RBS (Royal Bank of Scotland)
Role- System Engineer
Operating System: UNIX
Technology: Informatica 9x, SQL, UNIX, PL/SQL
Languages: PL/SQL
Database: Oracle
Team Size: 7
Duration: 1 year

Description- This is the project related to finance domain, basically we are dealing with the Risk of
the product/insurance/loan/credit card etc the service provides by bank RBS. We are keeping in
wholesale and retail data of RBS in data warehouse and we are calculating different type of Risk
related to the product of the bank. We have also keeping the facilities provided by bank keeping the
HR data for the employee working on client organization (RBS).

Company: ACS
Project Title4: HR Process
Role: Informatica Developer
Operating System: UNIX
Technology: Informatica 9x, SQL, UNIX, PL/SQL
Languages: PL/SQL
Database: Oracle
Team Size: 8
Duration: 7 month
Client: MERCK, PARKER, CIGNA, METLIFE, JOHNSON&JOHNSON etc.
HR does the job of designing, testing and executing critical processes like Ongoing Census, Elevation,
and Conversion etc. It acts as the upstream for majority of the applications. Whenever a new client
comes into the organisation, HR services perform the raw data load process followed by conversion
(to convert the coming data into our standard format), On-going census (to create the transaction
files), elevation process etc.
Responsibilities:

 Working as an Informatica developer for designing, developing, testing, troubleshooting and

debugging of the business requirements.
 Understanding of existing Business process, project functional and technical specifications.
 Understanding the new functional and Technical Specification and identifying measures,
dimensions
 Effectively communicate with business users and stakeholders.
 Created data model for the new requirements.
 Created documentation for technical and supports point of view.
 Create ETL design specifications and Developed ETL code
 Responsibilities for SQL Scripting.
 Responsible for creating and managing all the ETL development.
 Responsible for unit testing and bug fixing
 Responsible for proving production support for existing process.
 Handled coding as per the requirements specification, code debugging/review/testing &
deployment.
 Understanding the existing database design model and creating Requirements docs for new
enhancements
 Development of new mappings, sessions and implementing joins between the existing tables
and new tables at Universe Level and created test cases
 Understanding mapping table given by the Customers for recast of business and created
mapping tables specific to business logic.
 Created /update scripts for recasting the old structure into new for Business Recast
 Addressed the user end issues.

Project Title5: HR Outbound

Role: Informatica Developer
Operating System: UNIX
Technology: Informatica 9x, SQL, UNIX, PL/SQL
Languages: PL/SQL
Database: Sybase
Team Size: 8
Duration: 10 month
Client: MOTROLA, SONY PICTURE, CIGNA, MOBILITY, XEROX etc.

Scope: Basically here we are providing data / report which are stored in data warehouse to client in
accurate format and expected data of employee by comparing the current data. Updating the
dimension table as per the changes of source systems, Receiving business requirement
documentation from client, produce the o/p as per the mentioned in BRD, we are keeping the tables
as source, then applying the rule and validation which are mentioned in BRD, then produce the
output file as expected by client.

Responsibilities:
 Involved in functional study of the application.
 Writing SQL scripts and Unix Scripts.
 Created and implemented of mapping and sessions as per the DATA MAPPING.
 Debug the mappings at each step during the incorrect load of data in target table.
 Running the Jobs/workflows for ETL process.
 Prepared and ran SQL queries to verify Dimensional and Fact tables.
 Interactions with Business Analyst and Dev teams to resolve issue.
 Create ETL design specifications and Developed ETL code
 Development of new mappings, sessions and implementing joins between the existing tables
and new tables at Universe Level and created test cases
 Understanding mapping table given by the Customers for recast of business and created
mapping tables specific to business logic.
 Created /update scripts for recasting the old structure into new for Business Recast

Project Title6: HR Inbound

Role: Informatica Developer
Operating System: UNIX
Technology: Informatica 9x, SQL, UNIX, PL/SQL
Languages: PL/SQL
Database: Oracle
Team Size: 8
Duration: 7 month
Client: Verizon, Xerox etc.

Scope: Receiving the flat file from client which contains the employees information of our client and
the benefit ,plan used by client’s employee, we are keeping whole history of employee in our data
warehouse, producing payroll process, amount history and work history of employee. We are
loading this employee information to the dimension and fact table which is being used by client for
their human resource management.

Responsibilities:
Created UNIX script for input file validation using header – trailer information.
Created FFVALIDATE UNIX scripts for field validations and excel error report send to client
Created mapping, session, workflow for INFORMATICA Validation.
Created mapping for SSN change process.
Created and implemented of mapping and sessions as per the DATA MAPPING.
Debug the mappings at each step during the incorrect load of data in target table.
Running the Jobs/workflows for ETL process.
Prepared and ran SQL queries to verify Dimensional and Fact tables.
Interactions with Business Analyst and Dev teams to resolve issue.
Create ETL design specifications and Developed ETL code
Development of new mappings, sessions and implementing joins between the existing tables and
new tables at Universe Level and created test cases
Understanding mapping table given by the Customers for recast of business and created mapping
tables specific to business logic.
Created /update scripts for recasting the old structure into new for Business Recast

Education:
• M.C.A. from Khalikote Government College, Odisha in 2010.
• [Link]. (PHYSICS) from Charampa Mahavidyalaya (Fakir Mohan University), Odisha in 2007.