Data Etl

Aviral Sharma

786-***-**** ********@*****.***

PROFILE

** + years of experience in Data Architecture, Data Quality, Data Engineering, Enterprise Data Warehouse, Relational Data models and System migration strategy.

Conducted large scale Data migration from legacy Mainframe to Microsoft SQL server, Oracle databases while training technology and business users on database application industry standards and conventions. - 8 plus

Expertise in using ETL Tools Informatica Power Center 9.6 (Repository Manager, Designer, Workflow Manager, Workflow Monitor), IDQ, Power Exchange and ETL concepts. -10 + years

Strong analytical and conceptual skills in database design and development using Oracle – 10 plus

Expertise in writing complex, highly-optimized SQL queries across large data sets – 10 plus

10+ years of experience of application integration, RESTful API development, data warehousing, microservices, Object Oriented Programming both on premise and in the cloud.

EXPERIENCE SUMMARY

Hands on seasoned Leader with 15+ years of Data Management experience working on multiple successful Data Warehouse/Data Mart implementations while working in hybrid environments of both legacy and modern solutions.

Extensive experience in creating relational structures for Normalization (2NF, 3NF) of transactional data to reinforce integrity and performance of OLTP systems.

Ability to architect data management enterprise solutions that are in alignment with strategic technology roadmap and emerging industry trends.

Experienced in implementing Data Lake Solutions in Azure cloud and AWS, primarily in Azure cloud

Experience with several Big Data platforms and solutions using Hadoop (Horton works), Spark, HBase, MangoDB, Redshift and Impala

Worked with the technical architects and application designers to understand the data conversion requirements fully and to design the conversion procedures and applications.

Expertise in all aspects of Data Architecture, Data Warehousing, Master Data Management, Data Quality, System Analysis, ETL Architecture.

Expertise on various architectural solutions and delivery services for Data Acquisition, Integration and Enrichment ETL, Meta data, Master Data Management (MDM)

Expertise with industry leading modelling tools such as Erwin, IDA and ER studio

Played key role in outlining enterprise data architecture policies for publishing data models, naming standards and ETL data dictionary.

Experience in converting relational data models to semi-structured data (XML, JSON) or vice versa

Implemented the identified requirement in the canonical, logical, and physical models (xsd or json)

Extensive experience with Data Extraction, Transformation, and Loading (ETL) from disparate data sources like Multiple Relational Databases (Teradata, Oracle, SQL SERVER, DB2), VSAM, XML and Flat Files.

Created and deployed API led design strategy, API usage framework, and project team API enablement guidance as a core member of the service delivery team.

Effectively communicates project expectations, issues, risks, and decisions to project team and business stakeholder related to API implementation

Designed and Developed API design standards, patterns, and best-practices especially Swagger and Open API 3.0, REST, SOAP, MQ, JSON, Microservices etc.

Excellent hands-on experience in using Tableau to slice and dice data for self-service BI. Used dynamic dashboards and histograms for drill down capabilities and optimized reporting

Expertise with integration among various platforms using ESB (Mule Soft), IBM APIC (Rest services)

Actively involved in the development of short-term roadmaps and long-term data management strategies.

Expertise with various data modelling and design patterns around operation data stores and data warehouse.

SKILLS

Data Architecture: Created Enterprise level Data Architecture roadmaps, worked on Large Scale Data models such as Banking and Insurance, data strategy document, conceptual/logical/physical/canonical design models, 3NF, OLAP, OLTP, ODS, Data Mart, Metadata management, Erwin / Visio, data flow diagram, data profiling, defined Enterprise data modeling guidelines, data sourcing & gap analysis, feasibility study and recommendation, implemented partitioning, Kimball/Inmon methodology, source-target mapping, reverse engineering of existing processes, migration of legacy processes into new architecture. Extensively used ERWIN as data modelling tool for logical and physical models, Reverse Engineering, Complete Compare etc.

Databases: Extract and Load from/to various database platforms including DB2, Oracle, SQL server, indexing/partitioning, tuning, performance monitoring and prototyping.

Project Management: Meet & exceeded business goals, critical path management, project management, status updates, achieved key milestones within time/budget leveraging both Agile and Waterfall approaches.

Team Management: Experienced supervising team of 15+ Data Analyst/ ETL Developers/Data Architects/TIBCO developers. Lead and managed project work of large teams of employees and consultants, mentored junior staff, communicated, presented key milestones to executives and senior leadership, made process improvement recommendations, provided support for key decision making throughout implementation.

Delivery Model

Global Delivery Model (Onsite – Offshore), Client Site

Project Management

Microsoft Project, JIRA, HPPM

Design Tool

Erwin 9.5, Microsoft Visio, Rational IDA, Coolgen, Oxygen, Altova XML Spy, ER studio, Rational Software Architect

Database

DB2, Oracle 9i/10g, SQL Server 2008, Casandra,DynamoDB

ETL

Informatica Power Center 9.6, Informatica Data Quality IDQ, Informatica Cloud, Data Stage 8.7, Informatica PowerExchange, NIFI, SSIS, Alteryx,Talend

Data Quality/Consistency

IBM InfoSphere Info Analyzer, Trillium Data Quality

Reporting

PowerBI,Tableau

Version Management Tools

Microsoft Visual Source Safe, Rational Clear Case

Development Tools

Microsoft Visual Studio, Informatica MDM, Toad, SQL *Plus, Visual Studio, Squirrel, PyCharm, SQL Developer

Languages

SQL, Python

PROFFESIONAL EXPERIENCE

Bank Of America, New Jersey

Role: Sr Data Architect January 2019 - Present

Position will focus on quality data analyses, data base management of the quality data, all associated processes, and Quality Management and Standardized Work applications

Design and implementation of new Data Quality solution.

Responsible for various Data Management fields like Data Modeling, data warehousing, Database Management, data lineage, data replication and ETL

Integration of Data Quality solution with key internal systems (SAP, CRM, Maximo, etc.)

Migration of business rules from existing Data Quality solution (Ataccama) to new Data Quality solution (IBM Infosphere info Analyzer)

Developed complex mappings and transformations using Informatica Power Center tool 9x.

Provide technical depth concerning Informatica’s Data Governance, Data Discovery, Data privacy solutions to ensure client requirements are met. Extracted data from various sources like Oracle DB and TERADATA into JSON files for HFDC load.

Responsible for interaction with business stakeholders, gathering requirements and managing the delivery.

Execution of workshops to collect business rules and validate them with stakeholders

Involved in Data Replication project of Oracle DB as a source MemSql as target by using Oracle goldengate for CDC.

Provided knowledge and guidance in handling disparate data sources from varied databases.

Responsible to develop data architecture to effectively capture, integrate, organize, centralize, and maintain data.

Working in areas of data design, data Management, and data storage.

tools.

Worked with Mainframe (DB2/VSAM) and traditional heterogeneous relational databases like ORACLE and SQL Server. Experience with in-memory data stores (like MemSQL).

Environment: Informatica 9.6, IBM Infosphere Info Analyzer,ERWIN 8.0, DB2, SQL Server 2012, Microsoft Visio, TOAD, Teradata, Oracle 11i, Teradata SQL Assistant, TOAD, Tidal, UNIX, Citrix, JIRA, FIT

Schlumberger, Houston

Role: Data Architect(ETL) JAN 2016 - Dec 2018

Conducted extensive Source System Analysis, Data profiling to understand data anomalies to assess data quality and developed ETL strategies for system integration.

Maintained Source to Target (STM) Mapping for disparate systems and created transformation logic for the development team.

Created Conceptual, Logical, Physical and Canonical model for Legacy Schlumberger Warehouse in Phase – I.

Created Aggregate FACTS and Conformed Dimensions for Oil Platform Grids, Rigs, Chemical Composition, Calculation at Rack Price etc. and for all the incoming Revenue/Financial classes.

Responsible for the design of the data warehouse architecture, ETL processes, Source to Target Mapping and ETL execution plans.

Created Azure Data Factory pipeline to ingest the Flat File, CSV, JSON, PARQUET and ORC file data into AZURE SQL.

Cloud based report generation, development and implementation using SCOPE constructs and Tableau.

Expert in U-SQL constructs for interacting multiple source streams within AZURE Data Lake.

Involved in data analysis, performed data quality checks and prepared data quality assessment report

Designed source target mapping sheets for data loads and transformation

Developed pipelines to transform data using activities like U-SQL scripts on Azure Data Lake Analytics

Transformed data using Hadoop Streaming activity in AZURE Data Factory

Developed Pipelines to load data from on-premises to AZURE cloud database.

Developed Pipelines in Azure data factory using copy activity, Notebook, Hive, U-SQL to load data.

Developed Pipelines in Azure data factory to call Notebooks to transform data for reporting and analytics.

Developed reports on Tableau on top of Views in AZURE SQL

Provided architecture estimates, high level project plans, work forecast and resource assignments to the project management team.

Created logical & physical data model using Rational IDA for both Relational and reporting databases.

Used Oxygen XML developer for modeling inventory xml files into relational model for easy reporting for business needs.

Presented data model, data flow diagram to the steering committee for review.

Conducted work product reviews before publishing the finalized data model.

Collaborated with key stakeholders and team members to help them in their journey of adopting Agile; delivered effective communication of progress including the use of Agile.

Responsible for working with development team throughout the system lifecycle on recommendations on table design, referential integrity, database index optimization, and SQL query design.

Used IDA for designing Physical/Logical data models, reverse/forward engineering etc.

Closely worked with DBA in determining proper indices to fulfill reporting requirements.

Conducted extensive data analysis on SQL Server and initiated Data Governance process for data cleansing along with identifying and standardizing the “List of Values” for LOV dimension.

Performed data analysis, SQL optimization, database tuning, and general database reliability analysis

Developed stored procedures and views for SSRS reporting environment and actively led the testing phase.

Environment: SQL Server Mgt Studio, DB2, InfoSphere Data Architect V9.1.2, Visual Studio 2012, SAS 9.4, MS Visio, Power BI, JSON, Informatica Power Center.

SHELL Corp, Houston

Role: Sr. Enterprise Data Warehouse Architect Oct 2014 – Dec 2015

Lead initiative for Enterprise Data Warehouse, MDM and Application Rationalization projects.

Providing roadmap for migrating architecture from As-Is state to Future state.

Roadmap to migrate data from Unisys Mainframe to IDH and to ODS.

Worked as Data Warehouse Architect to gather requirements, performed source system analysis and identification of key data issues via analysis of the data profiling results and queries.

Extensively used DB2 Analytic functions and hierarchical queries

Closely worked with data stewards in managing member and provider data domains and ensured high levels of data quality, availability and validity.

Created roadmap to replace existing feeds from DB2 system to Data Warehouse by using ODS.

Conducted internal peer reviews with the Architectural team to make sure all the standards are complied.

Created Conceptual, Logical and Physical Model for Data Marts for reporting purposes.

Provided guidance to developers to improve the overall product quality by identifying areas of defect prevention throughout the development process.

Documented business process flow of data within organization and identified the Integration points between various systems.

Identification of ETL tool for loading EDW and explored various ways such as SQL Server Replication and ETL pull.

Provided the means of system integration by understanding the source and target data and build the data mapping.

Experience and knowledge of software design principles and integration patterns in general and of consuming and creating REST and SOAP web services in particular

Worked with Project Managers, ETL developers, Application Developers and BA to layout the plan for end to end solution for data migration to ODS and ultimately to Warehouse.

Environment: Informatica 9.6,Erwin 9.8, DB2, Ab Initio, SQL Server 2018,

BlueCross BlueShield, Chicago Sep 2010- Oct 2014

Role: Senior ETL Informatica Developer

Technicolor's Self-Service Enablement Portal provides our Customers and Third-Party Manufacturers with the tool to Track authorized enablement requests, create ad-hoc enablement data requests & Review Non-EMM & EMM data file formats based on the templates created with associated ShipTo/SoldTo.

Expertise in Informatica - Power Center Designer, Workflow Manager, Workflow Monitor and Repository Manager

Developed complex mappings and transformations using Informatica Power Center tool 9x.

Provide technical depth concerning Informatica’s Data Governance, Data Discovery and Data privacy solutions to ensure client requirements are met. Extracted data from various sources like Oracle DB and TERADATA into JSON files for HFDC load.

Responsible for interaction with business stakeholders, gathering requirements and managing the delivery.

Used Alteryx Designer, Alteryx Server, and the tools Alteryx such as Predictive, Parsing and Transforms.

Designed and developed ETL workflows and datasets in Alteryx to be used by the BI Reporting tool ETL specific for creating datasets.

Involved in loading of data into Teradata from legacy systems and flat files

Parameterized jobs to load from ASDL to ASDW using linked services and configurable table.

Connected Tableau server to publish dashboard to a central location for portal integration.

Resolving design/development issues and interacting with infrastructure support partners (DBA, Sys Admins)

Plan and execute deployments across all environments.

Responsible for developing code according to the technical design of the solution when ETL is required.

Design and Dynamic Access Control (DAC).

Created Technical Design Document and detail designed documents.

Created Sessions, Tasks, Workflows and Worklets using Workflow manager.

Worked with Data Modular in developing STAR Schemas

Used TOAD, SQL Developer and SQL Server management Studio to develop and debug procedures and packages.

Involved in developing the Deployment groups for deploying the code between various environment (Dev, QA, and Prod).

Experience developing and supporting complex DW transformations

Excellent understanding of Star Schema, Snowflake and Data Mart, SCD Type 1 and Type 2 Dimensions.

Created pre-SQL and post SQL scripts which need to be run at Informatica level.

Worked extensively with session parameters, Mapping Parameters, Mapping Variables and Parameter files for Incremental Loading

Used Debugger to fix the defects/ errors and data issues.

Expertise in using both connected and unconnected Lookup Transformations.

Extensively worked with various Lookup caches like Static cache, Dynamic cache and Persistent cache.

Develop, test and maintain all ETL maps /scripts and physical data models.

Developed Slowly Changing Dimension such as Type 1 SCD and Type 2 SCD.

Monitored and improved query performance by creating views, indexes, hints and sub queries.

Extensively involved in enhancing and managing Unix Shell Scripts.

Unit Test case preparation and unit testing.

Responsible for creating reports based on the requirements using SSRS 2016

Developed workflow dependency in Informatica using Event Wait Task, Command Wait Task and Email Task.

Environment: IBM Infosphere Info Analyzer,ERWIN 8.0, DB2, SQL Server 2012, Microsoft Visio, TOAD, Informatica 9.6, Teradata, Oracle 11i, Teradata SQL Assistant, TOAD, Tidal, UNIX, Citrix, JIRA, FIT

John Deere, Moline, IL

Role: BI Analyst/Modeler Jan 07 – Sept 2010

Organized JAD sessions with Subject Matter Experts (SMEs), BA’s, ADS for better

understanding of business needs.

Aided in Design development and implementation of Tableau reports.

Worked with Application development team support on development tools such as Oracle Forms/Oracle Reports, Workflow, JDeveloper, OA Framework, Personalization, application environment performance and solution tuning and interaction with Oracle support and service request ownership.

Built comprehensive dashboards and reports using Tableau and SQL Server Reporting Service.

Created rich dashboards using Tableau Dashboard and prepared user stories to create compelling dashboards to deliver actionable insights.

Created Logical and Physical design for Oracle Database including data structures, data dictionary, database schemas.

Worked with the DBA for an Oracle Spatial test instance for testing indexing mechanism in Oracle Spatial.

Designed and implemented innovative, interactive data visualizations and dashboards with Tableau by utilizing advanced visualization techniques along with built-in guided user interface. Developed initial prototypes to be presented for feedback.

Designed a normalized and a conformed star schema design for business reporting needs for AVA. Created a Visual Basic Front UI to interact with C++ setting files for cleaning and extracting road features from an image.

Created entity – attribute definition (data dictionary) report for all the data elements.

Conducted work product inspection and reviews before publishing the finalized data model.

Analyzed source data for map – gap analysis.

Outlined Execution Plan for technical project implementation. Outlined various steps needed by DBA to load/ QA/Test data through the entire lifecycle and across environments.

Tested PL/SQL code.

INDIANIVESH Securities Pvt. Ltd,Mumbai May 2004 – Jan 2007

Data Engineer

Role Involved:

Created process models and data flow diagrams depicting data flow in reinsurance domain.

Reviewed access data structures to understand the access path for generating Schedule F reports needed for balancing reinsurance amounts for state audits.

Wrote SQL queries to generate access reports needed for Schedule F.

Utilized Erwin, MS Project, and used ClearQuest for UML Modeling / Process Modeling.

Supervised a team of 3 developers, 1 business analyst and 2 testers.

Completed metadata modeling

Participated in data sourcing decisions along with Application Architect.

Worked on POC for creating project streams for individual project fragments and integrate them into a Common stream in rational clear case for version control.

Created Logical and Physical data models for feeds layer. (ERWIN)

Interacted with Business users, to resolve any issues with regards to specs and data available.

Identified the required dimensions and measures for the reports.

Built the Star schema data mart with conformed dimensions for policies, fund, and transactions.

EDUCATION

M.S. (Information Technology) at Florida International University

Bachelor’s in Engineering - SP college of Engineering and Technology (Electronics and Communication)

Contact this candidate