Teradata Architect and Lead
Resume:
DHARENDRA PILANIA
*.*******@*****.**.**
*******.**@*****.***
SUMMARY
11+ years of IT experience with good Analytical Skills & worked different Roles which includes roles like Teradata Architect, Data Modeling, Data Analyst, Database Designer, Developer (TD Utilities) BI Technical Architect (ETL Mapping),Testing, Implementation of Data Warehouse (DW), Business Intelligence (BI) solutions, using Operational Data Store (ODS), Data Mart (DM), ETL, OLAP on UNIX platforms.
For past few months working on Data Lake in Bigdata. Creation of Tables and loading Teradata tables data into HDFS .
Architect/Technical Lead: 9+ Years of rich experience in Teradata DWH Project with different roles. As an Architect my role is to collect user's requirements & translate them in Technical Design then to Design Teradata schema/Data mart and develop database for information systems projects in EDW. Table creation with appropriate Primary Index, Secondary Index, Join Index, and colleting stats if required. Defining Load strategies by using ETL tool or Teradata Utilities, Space Estimations for Data Mart/Schema/Tables. Performance optimization for Data Mart/Schema/Tables/Procedure/Complex Sqls.
Data Modeler: 4+ years of experience with strong Conceptual, Logical Data & Physical Modeling skills using ERWIN tool, creating data mapping documents, writing functional specifications, Logical Model & Physical Model Data Modeling (Dimensional, Multi-Dimensional) concepts like Star-Schema Modeling, Snowflake Schema Modeling, Fact and Dimension tables for Data Warehousing /Business Intelligence (BI) applications with appropriate Primary Index for even Data distribution.
ETL Development/Lead: 4 years of experience with Informatica Power Center. Worked on complex mapping like all SCD type, used all transformations, created session & Workflows.
Extensive knowledge of writing & tuning Complex SQL queries & PLSQL, working with back end tools such as SQL Assistant for data validation and verification.
I possess a multiple technical background, gained through hands-on experience in all areas of IT and more importantly in Banking, Telecom & Services.
TECHNICAL SKILLS
ROLES
Data Architect, Data Modeler, Data Analyst, SME, BI Technical Architect (Report Development & ETL Mapping), Team Lead, Onsite Coordinator.
Databases
Teradata V12,13,14, Oracle 10g, Greenplum
Data Modelling Tools
Erwin Data-Modeler, Erwin 9.6.1
Big Data Technologies
Hadoop, HIVE, PIG, Sqoop
ETL Tools
Informatica Power Center 9x/8x, BTEQ,MLOAD,FLOAD,TPUMP
Script Languages
PLSQL, SQL, Java Script
Reporting Tools
SQL Assistant, Business Object, Toad, PL/SQL, SQL LOADER, Tableau 8.0.
PM/Defect Tools
JIRA, HP ALM(QC), Servicenow,TCM, TWS tool, Serena
Operating Systems
Windows, Linux, Unix
Domain
Telecom, Banking, Finance, Services
PROFESSIONAL EXPERIENCE
Project: Inflow Outflow Project –Data Lake Sep 2017 – Present
Client: Morgan Stanley
Payroll/Location: Headstrong Services LLC/New York
Role: Technical Lead/Developer
Inflow Outflow Project gives Inflows & Outflows of Money from firm, which we created in Teradata, but users requested to store daily transaction for Trend Analysis, since daily transaction and applying logic to all historical transaction is resource consuming so we decided to push all data to Data Lake (Hadoop).
With this project we are pushing all tables related to Inflow Outflow Project to HDFS and created HIVE partitioned tables.
Responsibilities:
Technical Lead:
Defined requirements into Technical conceptual design & facilitated requirement meetings with business and technical stakeholders to drive final design.
Design the data hub architecture to implement the Technical requirements and map to sub-systems.
Designed the Project architecture with loading sequence with optimized way due to high volume of transactional data.
Created ETL Design, and mapping doc for further development.
Used DIA Tools & Sqoop tool to load data from flat files to HIVE tables.
Preparing Table structures as per HIVE standard
Creation of mapping document for data migration Teradata to Hadoop.
Providing a high level Conceptual Data Model to the Business Users. Create source to target mapping documents involving the Business rules and transformation rules.
Created Tables script with appropriate Partitions.
Providing data sets to the user queries on day to day bases to determine the performance and quality of the test data using HIVE queries.
Environment: Bigdata Hadoop, Hive, Sqoop, Teradata, DIA tool, Excel.
Project: Inflow Outflow Project Nov 2016 – Present
Client: Morgan Stanley
Payroll/Location: Headstrong Services LLC/New York
Role: Data Architect /Technical Lead
Inflow Outflow Project is built for higher Management for giving them view of Assets (Money) going out or coming into Bank Deposits Program and how much money going out or coming in from Outside the Firm and how much going or coming from Internal transaction. So that they can focus on grey areas at account level.by seeing transaction level data for each account against Assets & sub-Asset categories.
By storing historical data they can view trends of inflows and outflows over years. If they see any sudden spike then they can get the reason from transactional data.
Source for this Project was various ODS – Mainframe for transactions/Oracle for Deposits balances & Teradata for accounts attributes.
This project was very complex due to Deposits Settlements timing and warehouse timings. And we had multiple sources.
Apart from this Project I had worked on multiple small Projects which included Tables creation/Stored Procedures Creation/ and Performance Improvement of Procedures and complex SQLs.
Responsibilities:
Technical Lead:
Defined requirements into Technical conceptual design & facilitated requirement meetings with business and technical stakeholders to drive final design.
Prepare documents for Business processes, Technical design processes and post implementation documents
Worked on effort estimations for the project and done space estimation.
Defined the BO Reports layouts
Designed the Project architecture with loading sequence with optimized way due to high volume of transactional data.
Created ETL Design, and mapping doc for further development.
Coding of complex logic procedures, scripts.
Providing a high level Conceptual Data Model to the Business Users. Create source to target mapping documents involving the Business rules and transformation rules.
Create Logical Data Model (LDM) and Physical Data Models (PDM) using Excel Sheet(Table Spec and Column Spec).
Created Tables script with appropriate Primary Index & Secondary Indices.
Providing data sets to the user queries on day to day bases to determine the performance and quality of the test data.
Environment: Teradata, Informatica, Unix,BO, Excel, TD Utilities
Project: High Yield Saving (HYS)/TrueHub Enrichment
Client: Morgan Stanley Feb 2016-Nov 2016
Payroll/Location: Genpact Headstrong Capital Markets / Noida India
Role: Architect / Lead / Modeler
High Yield Savings Project was for Business where Business can offer some CD(Certificate of Deposits) with some higher Interest rates for promoting Client to put new or existing Money for longer Period.
This product is similar for regular Bank deposit Programs.
The purpose of this project is to load all data from ODS system in same format and layout which was already present in Teradata Legacy system. So that we can generate same BO reports which were already be given from some other Legacy system but on different Product.
Responsibilities:
Analyst/ Technical Lead:
Done requirement gathering from Business and prepared High Level Design.
Prepare High Level Design (HLD) Document and co-ordinate with other development teams to develop feed structures.
Design components to meet Technical requirements.
Worked on effort estimations for the project and done space estimation.
Prepare Detailed Design Specifications (DDS), Review and baseline Detailed Design Specifications (DDS).
Map design components with User, and Technical Requirements, i.e. Prepare Requirement Traceability Matrix (RTM).
Prepare Technical and Business processes documents for development, Maintenance, Stabilization.
Providing a high level Conceptual Data Model to the Business Users once the Business Requirements are evaluated and finalized.
Create source to target mapping documents involving the Business rules and transformation rules
Prepare documents for Technical design processes and post implementation documents
Facilitated data requirement meetings with business and technical stakeholders and resolved conflicts to drive decisions.
Reviewing the High Level Design (HLD) Document and Design Specification Documents provided to the ETL technical team once the Logical Data Model is reviewed and approved by the business users.
Further Implementation Logical Data Model and Physical Data Models in Teradata by creating tables with appropriate Primary Index and Secondary Indices.
Providing data sets to the user queries on day to day bases to determine the performance and quality of the test data.
Space Estimations for new tables.
Created ETL Design, and mapping doc for further development
Done Project Estimations
Worked with QA & UAT team for getting their signoffs.
Environment: Teradata, Informatica, Oracle, Unix, BO, TD Utilities.
Project: New Money Offerings(NEMO)
Client: Morgan Stanley Jun 2015-Feb 2016
Payroll/Location: Genpact Headstrong Capital Markets / Noida India
Role: Teradata Architect / Analyst
New Money Offering is a tool where any Financial Advisor can check for Eligible Accounts for High rate Promotions. Banking services generally offers promotions for limited period of time to generate new Money for the firm, but these offerings are restricted to certain Clients depending on their attributes.
Purpose of this Project in Teradata was to give the Eligible accounts feed to NEMO application so that they can show those accounts to their respective Financial Advisors.
So checking Eligibility NEMO application used to send various (approx. 20) condition for Eligibility .and TD is using those condition and fetching eligible clients from warehouse.
This was a complex project due to its design and Eligibility conditions.
Responsibilities:
Architect:
Gathering Business and Specification Requirements from the Business users and also with the Technical team
Created high level & low level technical design document for all business scenarios & conditions.
Prepare High Level Design (HLD) Document and co-ordinate with other development teams to develop feed structures.
Design components to meet Technical requirements.
Prepare Detailed Design Specifications (DDS), Review and baseline Detailed Design Specifications (DDS).
Involved in development phase to create complex mapping between dimension & fact tables for all complex business scenarios.
.Liaise with client on technical architecture related issues and solutions
Designed complex loading strategies and Procedures.
Created ETL Design, and mapping doc for further development
Space Estimation and tables creations
Providing a high level Conceptual Data Model to the Business Users once the Business Requirements are evaluated and finalized.
Create source to target mapping documents involving the Business rules and transformation rules.
Create Physical Data Models (PDM) using Erwin data modeling tool.
Reviewing the High Level Design (HLD) Document and Design Specification Documents provided to the ETL technical team once the Logical Data Model is reviewed and approved by the business users.
Involved in various complex SQL Query tuning and provided tuning recommendations, time/CPU consuming queries. Perform root cause analysis on failures. Resolve performance tuning related issues and queries.
Environment: Teradata, PLSQL, Informatica, SQL, UNIX, Erwin, TD Utilities
Project: AAR (External Asset at MSWM Platform)
Client: Morgan Stanley Jan 2014-Jun 2015
Payroll/Location: Genpact Headstrong Capital Markets / Noida India
Role: Analyst /Lead
MSWM means Morgan Stanley Wealth Management, under this project we loaded External assets from SB and Citi to MSWM Data warehouse. Earlier to this All Company client’s assets were present in different platform and Citi bank side, so this is conversion project where we loaded all external data to Wealth Management platform (Teradata Data warehouse).
Complexity of this project was to insert all external data to existing WM tables which were already present in Teradata warehouse. And changes to all tables to distinguish between core and external assets for all running application so that they don’t impact.
Creation of different level of views on tables so that same table can be used by existing application (only MSWM core data) and other new applications where WM and External data was required.
Responsibilities:
Analyst/Lead:
Gathering Business and Specification Requirements from the Business users for New application and Change in existing applications and also with the Technical team
Meeting with external data source team for feeds and creating mapping documents to match external data with existing data & Tables.
Worked on Project estimations and discussions with Source team for timelines for environments.
Created high level & low level technical design document for all business scenarios & conditions.
Prepare High Level Design (HLD) Document and co-ordinate with other development teams.
Design components to meet Technical requirements.
Space Estimation and tables creations for staging area, and table alters for all impacted tables.
Done Impact analysis for all existing application.
BI Technical Architect:
Involved in various scenarios related to Data Modeling, System/Data Analysis, Design and Development for both OLAP and Data warehousing environments.
Prepare High level external data load strategies for approx. 90 tables
Prepared Technical design doc for existing & New Jobs (Informatica workflow & Procedures) for Development team.
Involved in development phase complex mapping between dimension & fact tables for all complex business scenarios to match WM data .
Involved in various complex SQL Query tuning and provided tuning recommendations, time/CPU consuming queries.
Code review for all components & meeting with Client for weekly status.
Perform root cause analysis on failures. Resolve performance tuning related issues and queries.
Environment: Teradata, Informatica, Unix, Oracle, PLSQ, SQL, TD Utilities like Multiload,fastLoad
Project: Foreign Bank Accounts/Liquidity Hub Feed
Client: Morgan Stanley Mar 2013-Jan 2014
Payroll/Location: Genpact Headstrong Capital Markets / Noida India
Role: Analyst /Lead
This Project was Banking Project where Datawarehousing team required to send Foreign Bank Accounts & Bank Deposits Program feed to Regulators.
Responsibilities:
Analyst/Lead:
Gathering Business and Specification Requirements from the Business users for New feeds and also with the Technical team
Meeting with different DWH team to check availability of data.
Worked on Project estimations and discussions with Source team for timelines for environments.
Created high level & low level technical design document for all business scenarios & conditions.
Prepare High Level Design (HLD) Document and co-ordinate with other development teams.
Design components to meet Technical requirements.
Created mapping document for feeds.
.
ETL (Informatica):
Involved in design phase for to create high level & low level technical design document for all business scenarios.
Involved in development phase to create complex mapping between dimension & fact tables for all complex business scenarios to generate feeds.
Database Performance:
Involved in various complex SQL Query tuning and provided tuning recommendations, time/CPU consuming queries.
Used Explain Plan, creation of new indexes to improve the performance of SQL statements.
Created Join Index for Secondary Indices for query performance for feeds because of its complexity of multi table join and self-Join and Recursive query.
.
Environment: Teradata, TD Utilities, Informatica, Unix, SQL, PLSQL
Project: 3D List various release
Client: Morgan Stanley Jan 2011-Mar 2013
Payroll/Location: Genpact Headstrong Capital Markets / Noida India
Role: Analyst /Developer/Data Modeler
3D List is a UI for Financial Advisors to check their Client information which includes financial info as well as their personal information so that they can advise or wish Clients on appropriate time for building good relations and they can reply instantly to Clients in case of any request from Client.
UI interface was built in .Net and Teradata is used for showing data in UI application.
Which had multiple tabs & pages which different conditions.
Some Detail Tabs-
Birthday Page for Birthdays for FA’s Client he is serving
Top Holdings –
Contact List
Prior Activities
Top Positions
Etc.
Responsibilities :
Analyst:
Gathering Business and Specification Requirements from Business Analyst for new page.
Meeting with different DWH team to check availability of data.
Created mapping doc with requirement and existing data.
Worked on Project estimations and discussions with Source team for timelines for environments.
Created high level & low level technical design document for all business scenarios & conditions.
Prepare High Level Design (HLD) Document and co-ordinate with other development teams.
Design components to meet Technical requirements.
Worked with external Source system for new data requests
Worked with UI team for Stub documents, for filter and sorting columns which are being used in UI
Worked with QA & UAT team for getting and fixing issue and getting signoffs.
.
ETL (Informatica):
Involved in design phase for to create high level & low level technical design document for all business scenarios.
Involved in development phase to create complex mapping between dimension & fact tables for all complex business scenarios to generate feeds.
Data Modeler:
Gathering Business and Specification Requirements from the Business users and also with the Technical team.
Providing a high level Conceptual Data Model to the Business Users once the Business Requirements are evaluated and finalized.
Create source to target mapping documents involving the Business rules and transformation rules.
Created Table spec and Column specification documents for IA Data modeling Team.
Discussion with Data Modeling team for implementation of Column specification into Erwin tool.
Database Performance:
Involved in various complex SQL Query tuning and provided tuning recommendations, time/CPU consuming queries.
Used Explain Plan, creation of new indexes to improve the performance of SQL statements.
Created Join Index for Secondary Indices for query performance for feeds because of its complexity of multi table join and self-Join and Recursive query.
Involved in various complex SQL Query tuning and provided tuning recommendations, time/CPU consuming queries. Perform root cause analysis on failures. Resolve performance tuning related issues and queries
Environment: Teradata, TD Utilities like Multiload,fastLoad, Informatica, Unix, SQL, PLSQL, QC ALM
Project: LENS
Client: Morgan Stanley Jul 2010-Jan 2011
Payroll/Location: Genpact Headstrong Capital Markets / Noida India
Role: Analyst /Developer
LENS is old version of 3D List with few pages
Responsibilities:
Gathering Business and Specification Requirements from Business Analyst for new page.
Meeting with different DWH team to check availability of data.
Created mapping doc with requirement and existing data.
Worked on Project estimations and discussions with Source team for timelines for environments.
Created high level & low level technical design document for all business scenarios & conditions.
Prepare High Level Design (HLD) Document and co-ordinate with other development teams.
Design components to meet Technical requirements.
Worked with external Source system for new data requests
Worked with UI team for Stub documents, for filter and sorting columns which are being used in UI
Worked with QA & UAT team for getting and fixing issue and getting signoffs.
Involved in various complex SQL Query tuning for all reports business case.
Perform root cause analysis on failures. Resolve performance tuning related issues and queries.
Environment: Teradata, TD Utilities, Informatica, Unix, SQL, PLSQL, QC ALM
Project: MACC Redesign (Move Add Change Cancel)
Client: NCR Corporation, Gurgaon India Apr 2009 – Apr 2010
Role: Analyst/SME/Developer
MACC Application was present in Oracle (procedures & Packages) which was used to check NCR product information all over world, MACC stands for Move Add Change Cancel.
Move means any NCR Product which is moving from one site to another site Teradata datawarehouse team need to notify the same by services data in warehouse.
ADD means any NCR Product which is added to any site (to any existing or new customer (banks)) Teradata datawarehouse team need to notify the same by services data in warehouse.
Change means any NCR Product which needs to be replaced based on warranty Teradata datawarehouse team need to notify the same by services data in warehouse.
Cancel means any NCR Product which is by Customer, Teradata datawarehouse team need to notify the same by services data in warehouse.
This Project was already running in Oracle but due to performance issue and licence issue we need to change it to Teradata.
Responsibilities:
Converting Oracle Package and Procedures to Requirement document
Creating mapping doc for all fields required for this conversion .
Meeting with different DWH team to check availability of data.
Created mapping doc with requirement and existing data.
Worked on Project estimations and discussions with Source team for timelines for environments.
Created high level & low level technical design document for all business scenarios & conditions.
Prepare High Level Design (HLD) Document and co-ordinate with other development teams.
Created logical and Physical Data Model using Erwin tool.
Converted Oracle Procedures to Teradata Macros
Worked with QA & UAT team for getting and fixing issue and getting signoffs.
Involved in various complex SQL Query tuning for all reports business case.
Perform root cause analysis on failures. Resolve performance tuning related issues and queries.
Environment: Teradata, Oracle 10g, Unix,SQL Assistant, Erwin and TOAD 8.0,bteq,TD Utilities
Project: SDI/ HR Self Services Project/2009 Management Reporting Project
Client: NCR Corporation, Gurgaon India Dec 2007 – Apr 2009
Role: Analyst/SME/Developer
.
SDI – Service Delivery Information – this was conversion project from Legacy Services data for New Database.
HR Self Services Project – This project was UI interface project built in Java, meant for each employee and Managers to give them view of their employment information.
2009 Management Reporting Project- Under this Project we build new schema and BO reports based on Service data to higher management.
Responsibilities:
Creating mapping doc for all fields required for this conversion.
Meeting with different DWH team to check availability of data.
Created mapping doc with requirement and existing data.
Worked on Project estimations and discussions with Source team for timelines for environments.
Worked with UI team for data masking.
Created Java scripts for providing data to UI team for HR Self Service Project.
Created high level & low level technical design document for all business scenarios & conditions.
Prepare High Level Design (HLD) Document and co-ordinate with other development teams.
Created logical and Physical Data Model using Erwin tool.
Worked on BO tool for BO Reports.
Worked with QA & UAT team for getting and fixing issue and getting signoffs.
Involved in various complex SQL Query tuning for all reports business case.
Environment: Teradata,TD Utilities, Unix, SQL, BO, Erwin
Project: IBM-Bharti – Airtel CRM Project
Client: Airtel, Gurgaon India Jul 2007 – Dec 2007
Role: Operation Executive
eCRM oracle 11i Application (CRM)
Responsibilities:
Loading flat files to Oracle CRM customer Module for New customers.
Written procedures & Functions for new logic in CRM module
Supporting CRM Module for adding new region/area/branch.
Written complex queries for reporting purpose.
Environment: eCRM Oracle 11i app, Oracle SQL, PLSQL, TOAD, SQL Loader
Project: TCI Delivery phase 1
Client: TCI Gurgaon Mar 2006 – May 2007
Role: PLSQL Developer
TCI Delivery is Services Project for Transport firm, under this Project we built new database in Oracle Database.
Responsibilities:
.
Development using HLD using TOAD.
Written procedures & Functions Triggers for Oracle Forms
Written complex SQL for validation of data.
Loaded data using SQL loader.
Written test cases.
CERTIFICATION
Oracle Certified Associate (OCA) as “Oracle Application Server developer” Delhi.
SQL : 94.2%
PLSQL : 98.48%
EDUCATION
Master in Computer Science Application, Rajasthan University, Jaipur, India - 2006
Bachelors of Science, Math, Kurukshetra University, India - 2002
Contact this candidate