Data Analyst/ Teradata Developer
Resume:
CYRIL BABY KURIAN
Email: *******@*****.*** Mobile: +1-980-***-****
PROFESSIONAL SUMMARY
8.5+ years of expertise in Analysis, Design, Development, Testing and Implementation of various application systems in diverse domains.
Collaborated with Line of Business (LOB) partners, Data Owners, Subject Matter Experts (SMEs) and business stakeholders to identify and prioritize business requirements, designed and implemented solutions to improve efficiency, reduce risk and support new business.
Proficient in designing and implementing solutions in data warehouse environment and has experience working in applications/systems supporting data generated through OLAP.
Worked in all phases of Waterfall Methodology of Software Development Life Cycle (SDLC) including requirements analysis, system design, data modeling, database design, code development, code review, integration and testing, code migration, system documentation, transitioning to support teams as well as provide production support.
Experienced in creating dimensional and relational data models using Star and Snow Flake Schema, Identifying Facts and Dimensions, Conceptual, Logical and Physical data modeling using IBM Infosphere Data Architect.
Suggest ideas, design and share prototypes and best practices for improving the efficiency of the existing system/process to business team for review and request approval for implementation.
Analyze various profiles/entities built in the application and identify data gaps if any, design and propose cost effective and efficient solutions to fix these data gaps.
Translate the business requirements from the business requirement documents (BRD) into technical specifications and prepare High Level Design (HLD) and Low Level Design (LLD) documents based on these technical specifications and present these design documents to business for review and approval before proceeding with implementation.
Well-versed in developing ad hoc SQL queries to support complex business requirements and has strong data analysis, data profiling and problem solving skills.
Capture the data lineage and transformation logic for each attribute used in the application and prepare detailed end to end mapping document for the system.
Extensive experience implementing solutions using ETL technologies like Informatica PowerCenter, Syncsort DMExpress, Syncsort DMX-h and SAS Data Integration Studio.
Proficient in the Integration of data from various relational data sources like Teradata, MS SQL Server, Oracle and Flat Files into the Staging Layer.
Proficient in implementing DDL, DCL and DML SQL statements in Teradata database along with expertise in Teradata load/unload utilities like BTEQ, FastLoad, MultiLoad, FastExport, TPump, TPT used to load/extract data to/from various types of target/source systems.
Experienced in creating Tables, Views, Primary and Secondary Indexes, defining Join Indexes, using various Joins, Functions, Macros, set operators, creating complex queries in Teradata.
Proficient in query performance monitoring and analysis, SQL query tuning using Explain Plan, Collect Statistics in Teradata.
Worked extensively with slowly changing dimensions and implemented the same.
Exposure to Oracle PL/SQL, creating Tables, Views, Materialized views, Procedures, Functions, Cursors.
Developed Informatica workflows to facilitate ETL process in project as per requirement.
Used Informatica PowerExchange to source data from Legacy Mainframe systems.
Strong Data Warehousing ETL experience of using Informatica PowerCenter Client tools - Mapping Designer, Workflow Manager, Workflow Monitor and Repository Manager.
Experienced in developing Informatica Workflows, Worklets, Sessions and other Tasks, Mapping, Mapplets and using various Transformations.
Experienced in resolving production issues and code defects, monitoring Informatica sessions as well as performance tuning of mappings and sessions.
Have exposure to file processing in HDFS using Syncsort DMX-h Jobs and Tasks.
Developed jobs and tasks in Syncsort DMExpress for sourcing data from various disparate systems.
Used different types of DMExpress/DMX-h tasks like Copy, Join, Merge, Aggregate, Sort and Filter.
Implemented MapReduce jobs in Hadoop cluster using suitable Mapper and Reducer tasks and Partition Keys.
Defined and used Values and Conditions, Lookup Sources, Filters, Source Formats, Target Reformats, External Metadata in DMX-h/DMExpress Tasks and Jobs.
Extensive experience in the design and implementation of various Data Quality checks and data reconciliation checks in Batch processing using UNIX shell scripts.
Have exposure to data processing and creating SAS datasets using SAS DI Studio and Base SAS.
EDUCATION
Bachelor of Technology (Electronics and Communication Engineering), Cochin University of Science and Technology (CUSAT) (Kerala, India), April 2011
TECHNICAL SKILLS
Database
:
Teradata (v13, v14, v15, v16), Oracle 11g, MS SQL Server
ETL Tools
:
Informatica PowerCenter Designer (Source Analyzer, Target Designer, Transformation Developer, Mapplet Designer, Mapping Designer), Informatica PowerCenter Workflow Manager (Task Developer, Worklet Designer, Workflow Designer), Informatica PowerCenter Workflow Monitor, Informatica PowerCenter Repository Manager (v8.x, v9.6.1), Informatica PowerExchange, Syncsort DMExpress v8.1 (Job and Task Editor Tools), SAS Data Integration Studio v4.2, SAS Enterprise Guide v4.3
Job Scheduler
:
Autosys, SAS Management Console v9.2
Programming
:
UNIX Shell Scripting, SQL
Analytics
:
Base SAS
IDE
:
Teradata SQL Assistant (v14.10, v15.10), Teradata Studio Express Eclipse v16.0.2, TOAD, PUTTY, WinSCP
Version Control
:
Tortoise SVN
Data Modeling Tool
:
IBM Infosphere Data Architect (IDA)
Big Data
:
HDFS, MapReduce, Syncsort DMX-h v8.1 (Job and Task Editor Tools)
DOMAIN EXPERIENCE
Anti-Money Laundering
Customer Complaints and Enterprise Allegations
Loans
Security
Insurance
PROJECT EXPERIENCE
Wells Fargo May 2018 – Till Date
Wells Fargo & Company is an American multinational financial services company headquartered in San Francisco, California, with central offices throughout the country. It is the world's second-largest bank by market capitalization and the third largest bank in the U.S. by total assets.
Project
:
CMAIR (Conduct Management Analytics, Insights and Reporting)
Duration
:
July 2018-Till Date
Role
:
Data Analyst/Senior Developer
Location
:
Charlotte, North Carolina, USA
Overview: CMAIR mission is to provide actionable analytics, insights, and reporting on team member misconduct allegations allowing the Enterprise to reduce risk, better serve customers and improve team member experience. Insights are drawn based on analysis performed on the data in Conduct Management Integrated Reporting Platform (CMIRP). CMIRP is a virtual copy of the Enterprise Allegation Platform (EAP), which is an operational data store. The Enterprise Allegations Platform (EAP) establishes an integrated core and common allegation case management system across the enterprise. It is supported by the Allegation Management Policy, common data elements, and allegation processes and procedures that support legal and regulatory requirements.
Tools: Teradata (v16.20), Teradata Studio Express (v16.20), Teradata SQL Assistant (v16.20), SAS
Responsibilities:
Understand, acknowledge and provide prompt response to various ad hoc requests from business teams by using complex SQL queries in Teradata and provide precise, accurate results.
Engage in discussions with data requesters to better understand the purpose of the report and also to request clarifications on data attributes to be included in the report.
Create daily, weekly, monthly and quarterly reports/extracts as per user requirement.
Involved In several critical data acquisition efforts to meet consent order reporting requirements involving federal agencies like the CFPB and OCC.
Provide code logic to the development team for defining complex data attributes for standard reporting views and validate the data in these views to facilitate quicker and consistent reports and dashboards development.
Perform data quality checks on critical reporting fields to identify missing and/or invalid data and share the details with business for data remediation.
Collaborate with Microstrategy and Tableau development teams to help implement reusable dashboards and workbenches.
Engage application support team to better understand certain data elements and use this knowledge while defining attributes for reporting thereby delivering accurate extracts.
Coordinate with Intake team for report review, redaction (if needed), approval and distribution to the user/requester.
Export relevant data sets from Teradata tables into local files using Teradata utilities like Teradata FastExport and share the exported data with business team upon request.
Load data sets shared by business team in Microsoft Excel documents as part of ad hoc requests into Teradata tables using Teradata load utilities like Teradata FastLoad and process this data to address certain business requests.
Build database views and SQLs to provide data to the reporting team for dashboard development as per data requirement.
Create conformed data sets from non-conformed case data from historical legacy source systems to facilitate reporting.
Perform performance tuning on Teradata tables to reduce skew and free up space in database.
Project
:
Historical Complaint Analysis and Remediation
Duration
:
May 2018 – December 2018
Role
:
Data Analyst/Senior Developer
Location
:
Charlotte, North Carolina, USA
Overview: On September 4, 2016, Wells Fargo N.A. (“Wells Fargo” or the “Bank”) entered into a Consent Order with the Consumer Financial Protection Bureau (CFPB) (the “CFPB Consent Order”) and on September 6, 2016, Wells Fargo entered into a Consent Order with the Office of the Comptroller of the Currency (OCC) (the “OCC Consent Order”). Article VIII of both the OCC Consent Order and CFPB Consent Order required Wells Fargo to identify all customers potentially harmed by improper sales practices, to assess the quantity of harm potentially experienced by those customers, and to provide appropriate corresponding remediation.
Tools: Teradata (v15.10), Teradata Studio Express (v16.0.2), Teradata SQL Assistant (v15.10)
Responsibilities:
Engage in discussions/meetings with business teams/stakeholders to identify, prioritize and categorize the requirements for which solutions have to be designed and implemented.
Based on the information obtained through interactions with Line of Business (LOB) partners, Data Owners, Subject Matter Experts (SMEs) and business stakeholders and various project artifacts available, analyze and understand the data that the Line of Business deals with.
Perform data analysis and data profiling to get more insights into the data, domain, platform and project.
Analyze the various profiles/entities built in the application data model and identify data gaps, if any, design and propose cost effective and efficient solutions to fix these data gaps.
Raise questions/concerns on topics requiring clarity after analyzing the Business Requirement Document (BRD) shared by business, maintain document to track the questions raised and assign ownership to these questions, track and follow-up with business on these concerns to closure.
Analyze customer complaints’ and account data from nearly 20 disparate system of records to identify customers potentially harmed by improper sales practices and help business to identify and categorize the complaints into various complaint types based on the taxonomy and conditions defined.
Design and build a centralized repository for complaints data by analyzing and merging customer complaints data from several System of Records (SORs) which have historical data from the past 10 years.
Implement Primary Key roll up for complaints data from this vast centralized repository to help identify and link the comments associated with complaints to the respective parent complaint which is crucial for categorizing each complaint.
Prepare customer remediation file as per the layout defined in the requirements document by combining data from various sources and share the final file securely with business-approved third party partners through Data Transmission Service (DTS).
Capture the data lineage and transformation logic used for each attribute used in the application and prepare detailed mapping document for the system and keep this document in SharePoint repository.
For requirements having no mapping document available, analyze the existing legacy code base used for previous phases of implementation and identify the source tables/views and fields used and the transformation logic applied for each attribute before loading to the target systems and prepare end to end mapping document.
Review this mapping document with Subject Matter Experts (SME), make recommended updates and request business approval for following the same data lineage for future phases of implementations.
Understand, acknowledge and provide prompt response to various ad hoc requests from business teams by using complex SQL queries in Teradata and provide precise, accurate results.
Export relevant data sets from Teradata tables into local files using Teradata unload utilities like Teradata FastExport, TPT and share the exported data with business team upon request.
Load data sets shared by business team in Microsoft Excel documents as part of ad hoc requests into Teradata tables using Teradata load utilities like TPT and Teradata FastLoad and process this data to address certain business requests.
Build database views and SQLs to provide data to the reporting team for dashboard development as per data requirement.
Create test cases and execute validation scripts to validate the data sets prepared and document the validation results after ensuring all the test cases have passed before sharing the data with Quality Assurance (QA) and User Acceptance Testing (UAT) teams.
For test cases which have failed, perform root cause analysis and data profiling to identify the reason for failure and data quality issues and make appropriate rectifications before proceeding further.
Completed various training programs and assessments/evaluations assigned by business to comply with various organization level and LOB level policies and standards as well as to improve domain knowledge.
Bank of America Sept 2012 – May 2018
Bank of America Corporation (abbreviated as BofA) is a multinational banking and financial services corporation headquartered in Charlotte, North Carolina. It is ranked 2nd on the list of largest banks in the United States by assets. As of 2016, Bank of America was the 26th largest company in the United States by total revenue. In 2016, it was ranked #11 on the Forbes Magazine Global 2000 list of largest companies in the world.
Project
:
AML Customer Profile
Duration
:
April 2016 - May 2018
Role
:
Teradata Informatica Developer
Location
:
Charlotte, North Carolina, USA
Overview: Customer profile is a central repository of parties and account information and has been developed as part of the AML regulatory and compliance requirements of the bank. The system contains consolidated data of party, linkages and accounts and hosts several profiles like demographic, relationship, network, finance, risk. Profiling is the recording and classification of behaviors and risks. This usually occurs through finding relation between customers and aggregating information primarily from transaction and demographic data related to the customer. The key objectives of customer profiling are to ensure delivery of consistent customer profiles, linkages and account information, to reduce data redundancy, complexity by providing a strategic customer and account data repository for multiple downstream applications.
Tools: Teradata (v14, v15), Teradata SQL Assistant (v14.10), Informatica PowerCenter (v9.6.1), UNIX, Autosys, Tortoise SVN
Responsibilities:
Analyzed the business requirement document (BRD) and requested more information from business for requirements which need more clarity.
Performed impact analysis to identify the database objects and scripts which will have to be modified or newly created to implement the proposed solution for the requirement.
Discussed the solution approach with the Business Architect and Line of Business partners for implementing the requirements from BRD, created high level (HLD) and low level design documents (LLD) to capture the final design of the proposed solution to be implemented and requested business approval for these design documents.
Used Teradata utilities like FastLoad, FastExport, MultiLoad, TPump, TPT to load/extract data to/from various types of disparate target/source data systems including flat files and Teradata tables.
Developed Teradata BTEQs to transform the data received from source systems and populated various profiles in the application.
Developed Informatica Workflows to source data from flat files and database tables, to perform ETL data processing (using various Informatica transformations) and to load data to retention/stage layer tables.
Implemented various types of Slowly Changing Dimensions (SCDs) in various profiles requiring history of data to be maintained using Teradata BTEQs.
Performed data transformations as per requirement by developing and implementing Data Definition Language (DDL), Data Control Language (DCL) and Data Modification Language (DML) Structured Query Language (SQL) statements in Teradata.
Captured Teradata Metrics to analyze query performance and tuned Teradata queries to improve performance by 'collecting statistics' and analyzing the execution plan provided by 'Explain' functionality.
Defined proper primary indexes (unique and non-unique) while creating table DDLs and created secondary indexes and join indexes for improving query performance.
Developed UNIX shell scripts for file processing, invoking BTEQs and Informatica Workflows to load various application profiles and for implementing data quality and reconciliation checks to ensure data integrity as data flows from various source systems to the retention, staging and target layers in the application.
Determined the dependencies between various profiles within the application, SLAs and dependencies with upstream and downstream applications and scheduled jobs using Autosys Job Scheduling Tool in accordance with these dependencies to facilitate batch processing.
Performed unit testing of the modules developed to validate the data using test scripts and documented the results.
Reviewed the code components/modules developed by other team members as part of peer review, verified the test results captured by them and validated the data to ensure the functionality is implemented as expected.
Provided clarifications and explanations for various questions/concerns raised by Quality Assurance (QA) and/or User Acceptance Testing (UAT) teams during data validation phase.
Exported Informatica workflows into XML files to facilitate migration to UAT and Production environments.
Prepared Detailed Task Schedule (DTS) document to enlist the different teams who have to be involved in the deployment, to depict the schedule to be followed by these teams as well as the various deployment activities to be performed by each team as part of Production Deployment and Release Management Activities.
Created SVN Tags to facilitate code migration to Production environment and coordinated with deployment team to verify the migrated components,
Validated the data loaded in production profiles after the impacted jobs were run by verifying the logs of validation queries shared with deployment team before releasing the data to downstream during production deployment phase.
Project
:
Insider Threat COPS (Centralized Operation Protection Services)
Duration
:
July 2014 - April 2016
Role
:
Teradata Informatica DMX-h/DMExpress Developer
Location
:
Chennai, Tamil Nadu, India
Overview: The objective of this project is to send extracts containing the details of users who have queried databases in various platforms (DEV, UAT, AHP, WCRM, APR and PROD) to the COPS (Centralized Operation Protection Services) team. ‘COPS’ is designed to recognize people and processes which access customer data in an unusual manner. COPS server examines unusual customer data access and user actions in order to recognize and prevent unethical or illegal activities.
Tools: Teradata (v13, V14), Teradata SQL Assistant (v14.10), Informatica PowerCenter (v8.x), UNIX, Autosys, Syncsort DMX-h v8.1, Syncsort DMExpress v8.1, HDFS, Oracle
Responsibilities:
Prepared High Level (HLD) and Low Level Design (LLD) documents based on Business Requirement Document (BRD).
Performed impact analysis to identify the existing code/database components that need to be modified/ new components to be created in order to implement the proposed solution for the requirement.
Developed Syncsort DMX-h Jobs to process and store files in HDFS (Hadoop Distributed File System).
Designed and developed Tasks and Jobs using Syncsort DMX-h/DMExpress Task Editor and Job Editor Tools for sourcing data from various disparate systems and to facilitate ETL data processing.
Implemented MapReduce DMX-h jobs in Hadoop cluster for parallel data processing using Mapper and Reducer DMX-h Tasks and distinguished Mapper and Reducer side Tasks using MapReduce Data Flow Connector.
Identified proper Partition Keys to facilitate efficient implementation of MapReduce Jobs in DMX-h.
Used different types of DMX-h/DMExpress tasks like Copy, Join, Merge, Aggregate, Sort and Filter.
Defined and used Values and Conditions, Lookup Sources, Filters, Source Formats, Target Reformats, External Metadata in DMX-h/DMExpress jobs.
Defined data flow dependencies between various DMX/DMExpress Jobs and Tasks using Data Flow Connectors.
Exported data from Oracle tables to files and Teradata tables using SQOOP.
Developed Informatica Workflows for sourcing data from various source systems and loaded to Teradata tables by invoking FastLoad utility through Teradata Application Programming Interface (API).
Implemented the data transformation logic using Informatica Transformations like Source Qualifier, Expression, Aggregator, Joiner, Lookup, Filter and Router and created the final file for each user environment using FastExport utility available in Informatica Teradata API.
Combined these files into one and transmitted the final file to downstream using SFTP.
Defined parameters and variables in parameter files and utilized these in Mappings, Sessions and Workflows by invoking the parameter file to avoid hard coding of values.
Monitored the execution of Workflows in Informatica Workflow Monitor and analyzed session logs to identify performance bottlenecks and/or errors.
Developed BTEQs to implement data transformation logic and invoked these BTEQs in Informatica Command task as well as in Pre-Session and Post-Session commands.
Captured Teradata platform Metrics and identified queries that were non-compliant as per platform standards by analyzing these performance metrics.
Performed performance tuning for non-compliant queries by using Explain Plan for the query, Collecting Stats and defining and using proper indexes.
Prepared Unit Test Plan and Unit Test Cases, tested and validated the code modules developed.
Implemented batch processing by scheduling Jobs to trigger Informatica Workflows, BTEQs and DMX Jobs using Autosys Job Scheduling Tool in accordance with job dependencies and SLAs (Service Level Agreement).
Prepared documents/artifacts required for production deployment like impacted components list, Test plan, Detailed Task schedule (DTS) and requested QA and UAT sign-off and Teradata Platform Metrics sign-off from the respective teams.
Validated migrated components, provided deployment support and monitored jobs during production deployment.
Project
:
Loans - Serviced By Others (SBO)
Duration
:
July 2013 - July 2014
Role
:
Teradata Informatica Developer
Location
:
Chennai, Tamil Nadu, India
Overview: SBO Loans are the loans that are serviced by others. There are mergers and acquisition that happen in the Bank. When Bank buys these loans, these will be owned by the Bank but still the servicing will be done by the same party who has been servicing before the merger or a different party. These loans which are owned by Bank of America but serviced by others are the ones which flow into SBO system. A user gets a loan from Bank A by pledging some properties as security. The user will get this loan serviced by Bank A or a servicer chosen by Bank A. Bank A might sell these loans to Bank of America in case of financial emergency. Now Bank will own and control the collateral that was pledged by the user.
Tools: Teradata (v13, V14), Teradata SQL Assistant (v14.10), Informatica PowerCenter (v8.x), Informatica PowerExchange, UNIX, Autosys
Responsibilities:
Used various Informatica PowerCenter Designer Transformations like Source Qualifier, Joiner, Lookup, Filter, Router, Aggregator, Expression, Union, Update Strategy, Sorter, Rank, Sequence Generator etc. in Mappings to implement the ETL data transformation logic.
Created Tasks such as Session, E-mail, Command, Decision, Assignment, Timer, Control, Event tasks in Informatica PowerCenter Workflow Manager.
Used Informatica PowerExchange to pull data from Mainframe Legacy systems.
Created Reusable Transformations, Mapplets and Worklets to facilitate code reusability wherever possible.
Created parameter files to define and initialize parameters and variables and invoked these parameter files in sessions and workflows to avoid hardcoding of values.
Improved performance of Informatica Workflows by implementing partitioning (Database partitioning, Pass-through, Round-robin, Hash auto-keys, Hash user keys, Key Range partitioning) for parallel data processing.
Implemented Pushdown Optimization for Transformation/Mapping logic which can be pushed to the source/target database level instead of executing the logic inside Informatica thereby reducing the load on the Integration Service and thus improved the performance of Workflows.
Invoked Teradata utilities like FastLoad, MultiLoad, FastExport, TPump, TPT Teradata utilities using APIs in Informatica PowerCenter for loading/exporting data to/from Teradata tables.
Analyzed Loader Log Files generated after invoking Teradata utilities in Informatica to verify the number of rows read from source, number of rows loaded into the target and the number of rows loaded into the error table.
Developed BTEQs to implement data transformation logic and invoked these BTEQs in Informatica Command task as well as in Pre-Session and Post-Session commands.
Collected Teradata Metrics for the queries to be deployed in Production and identified queries that were non-compliant as per platform standards by analyzing these performance metrics.
Performed performance tuning for non-compliant queries by analyzing the Teradata Execution Plan displayed by the Explain Plan for the query, Collecting Stats as recommended by the Explain plan, defining proper indexes, using volatile and temporary tables and using indexed columns in join conditions and ‘where’ clauses.
Performed data analysis and data profiling using complex SQL in Teradata to find out the root cause for production job failures, identified data quality issues in upstream data and reported the findings to business.
Scheduled Jobs in Autosys Job Scheduling Tool using JIL (Job Information Language) scripts in accordance with job dependencies and SLAs determined to facilitate batch processing in the application.
Developed UNIX shell Scripts to facilitate file processing, for implementing data quality controls and reconciliation checks for the data loaded in various layers in the application, for invoking BTEQs and triggering Informatica Workflows.
Prepared Unit Test Plan and Unit Test Cases, tested and validated the code modules developed.
Performed peer review and verified the components developed by other team members to ensure functionality implementation as expected.
Validated the code and database components migrated to Production, provided deployment support and monitored jobs to ensure successful completion during production deployment.
Project
:
Wholesale Model Banking
Duration
:
September 2012 - July 2013
Role
:
Data Modeler
Location
:
Chennai, Tamil Nadu, India
Overview: The objective of this project is to analyze an existing legacy system based on the reports generated, create data model for the same into a new platform thereby reducing redundancy and improving efficiency and performance.
Tools: UNIX, IBM Infosphere Data Architect, Oracle
Responsibilities:
Developed UNIX shell scripts to identify the key fields, entities and the relationship between these entities used in the legacy system by processing the Mainframe code components used to load data in these legacy systems.
Identified the cardinality between the entities by analyzing the data stored in Mainframe files.
Collaborated with Subject Matter Experts (SMEs) and Line of Business (LOB) Partners to identify the redundant entities and attributes that need to be removed from the legacy system.
Developed revamped and improved Conceptual, Logical and Physical Data Models after several iterations of discussions and reviews with business.
Created data lineage documents to show the mapping between the legacy system attributes and the new data model and also to capture the source to target mapping of attributes in the new system.
Implemented the new data model in Oracle database and developed
Contact this candidate