Sr Data Architect Resume
St Louis, MO
SUMMARY:
- Above 10+ years of experience in Data Architect/Data Modeler/Data Analyst including Data Development, Implementation and Maintenance of databases and software applications.
- Experience working with Agile, Waterfall methodologies, Ralph Kimball and Bill Inmon approaches.
- Experience in Big Data Hadoop Ecosystem in ingestion, storage, querying, processing and analysis of big data.
- Having good knowledge in Normalization and De - Normalization techniques for optimum performance in relational and dimensional database environments.
- Excellent knowledge in Data Analysis, Data Validation, Data Cleansing, Data Verification and identifying data mismatch.
- Performed data analysis and data profiling using complex SQL on various sources systems including Oracle and Teradata.
- Specialization in Data Modeling, Data warehouse design, Building conceptual Architect, Data Integration and Business Intelligence Solution.
- Expert in writing SQL queries and optimizing the queries in Oracle, SQL Server and Teradata.
- Experience in designing Star schema, Snowflake schema for Data Warehouse, ODS architecture.
- Experience in designing Logical, Physical & Conceptual data models for to build the Data Warehouse.
- Strong experience in Data Analysis, Data Migration, Data Cleansing, Transformation, Integration, Data Import, and Data Export through the use of multiple ETL tools such as Ab Initio and Informatica Power Center Experience in testing and writing SQL and PL/SQL statements - Stored Procedures, Functions, Triggers and packages.
- Excellent experience on using Teradata SQL Assistant, Teradata Administrator, PMON and data load/export utilities like BTEQ, Fast Load, Multi Load, Fast Export, and Exposure to Tpump on UNIX/Windows environments and running the batch process for Teradata.
- Extensive experience in supporting Informatica applications, data extraction from heterogeneous sources using Informatica Power Center.
- Experience in automating and scheduling the Informatica jobs using UNIX shell scripting configuring Korn-jobs for Informatica sessions.
- Experience in designing error and exception handling procedures to identify, record and report errors.
- Solid hands on experience with administration of data model repository, documentation in Meta data portals in such as Erwin, ER Studio and Power Designer tools.
- Experience in conducting Joint Application Development (JAD) sessions with SMEs, Stakeholders and other project team members for requirements gathering and analysis.
- Experienced in various databases Design of development and Production environment involving Oracle, SQL server, Netezza, MY SQL, DB2, MS Access, Teradata.
- Experienced working with Excel Pivot and VBA macros for various business scenarios.
- Software Development Life Cycle (SDLC) experience including Requirements, Specifications Analysis/Design and Testing as per the Software Development Life Cycle.
- Solid Excellent experience in creating cloud based solutions and architecture using Amazon Web services (Amazon EC2, Amazon S3, Amazon RDS) and Microsoft Azure.
- Excellent knowledge on creating reports on SAP Business Objects, Webi reports for multiple data providers.
- Excellent experience in writing and executing unit, system, integration and UAT scripts in a data warehouse projects.
- Excellent experience in writing SQL queries to validate data movement between different layers in data warehouse environment.
- Excellent experience in troubleshooting test scripts, SQL queries, ETL jobs, data warehouse/data mart/data store models.
- Extensive knowledge and experience in producing tables, reports, graphs and listings using various procedures and handling large databases to perform complex data manipulations.
- Experience in testing Business Intelligence reports generated by various BI Tools like Cognos and Business Objects.
- Excellent in creating various artifacts for projects which include specification documents, data mapping and data analysis documents.
TECHNICAL SKILLS:
Data Modeling Tools: Erwin Data Modeler 9.7/9.6, Erwin Model Manager, ER Studio v17, and Power Designer.
Programming Languages: SQL, PL/SQL, HTML5, XML and VBA.
Reporting Tools: SSRS, Power BI, Tableau, SSAS, MS-Excel, SAS BI Platform.
Big Data technologies: HBase 1.2, HDFS, Sqoop 1.4, Spark, Hadoop 3.0, Hive 2.3, EC2, S3 Bucket, AMI, RDS
Cloud Platforms: AWS, EC2, EC3, Redshift & MS Azure
OLAP Tools: Tableau 7, SAP BO, SSAS, Business Objects, and Crystal Reports 9
Databases: Oracle 12c/11g, Teradata R15/R14, MS SQL Server 2016/2014, DB2.
Operating System: Windows, Unix, Sun Solaris
ETL/Data warehouse Tools:: Informatica 9.6/9.1, SAP Business Objects XIR3.1/XIR2, Talend, and Pentaho.
Methodologies: RAD, JAD, RUP, UML, System Development Life Cycle (SDLC), Agile, Waterfall Model
WORK EXPERIENCE:
Confidential - St. Louis, MO
Sr Data Architect
Responsibilities:
- As a Data Architect, I am responsible for all data related aspects of a project.
- Developing full life cycle software including defining requirements, prototyping, designing, coding, testing and maintaining software.
- Worked as a Data Modeler/Analyst to generate Data Models using SAP PowerDesigner and developed relational database system.
- Worked on Software Development Life Cycle (SDLC) with good working knowledge of testing, Agile methodology, disciplines, tasks, resources and scheduling.
- Developed logical data models and physical database design and generated database schemas using SAP PowerDesigner.
- Designed and developed architecture for data services ecosystem spanning Relational, NoSQL, and Big Data technologies.
- Designed both 3NF data models for ODS, OLTP systems and dimensional data models using star and snow flake Schemas.
- Worked on master data (entities and attributes) and capture how data is interpreted by users in various parts of the organization.
- Conducted JAD sessions with management, vendors, users and other stakeholders for open and pending issues to develop specifications.
- Developed Data Mapping, Data Governance, Transformation and Cleansing rules for the Master Data Management Architecture.
- Researched and developed hosting solutions using MS Azure for service solution.
- Developed long term data warehouse roadmap and architectures, designs and builds the data warehouse framework per the roadmap.
- Involved in Data profiling in order to detect and correct inaccurate data and maintain the data quality.
- Developed Data Migration and Cleansing rules for the Integration Architecture (OLTP, ODS, DW).
- Implemented Forward Engineering by using DDL scripts and generating indexing strategies.
- Reverse Engineered physical data models from SQL Scripts and databases.
- Worked with Data Analytics, Data Reporting, Ad-hoc Reporting, Graphs, Scales, PivotTables and OLAP reporting.
- Lead Data Governance process with external and internal data providers to ensure timely, accurate and complete data.
- Document data dictionaries and business requirements for key workflows and process points
- Involved in Dimensional modeling (Star Schema) of the Data warehouse and used PowerDesigner to design the business process, dimensions and measured facts.
- Designed ER diagrams and mapping the data into database objects.
- Wrote test plans and test cases in compliance with organizational standards.
- Designed the data warehouse architecture for all the source systems using MS Visio.
- Responsible for different Data mapping activities from Source systems to Teradata.
- Developed and maintained an Enterprise Data Model (EDM) to serve as both the strategic and tactical planning vehicles to manage the enterprise data warehouse. This effort involves working closely with the business.
- Worked with project management, business teams and departments to assess and refine requirements to design/develop BI solutions using MS Azure.
- Developed and implemented data cleansing, data security, data profiling and data monitoring processes.
- Worked with the data team to profile source data and determine source and metadata characteristics.
- Involved in data lineage and Informatica ETL source to target mapping development, complying with data quality and governance standards.
- Designed, developed data integration programs in a Hadoop environment with NoSQL data store Cassandra for data access and analysis.
- Used Pig to extract, write complex data transformations, cleaning and processing of large data sets and storing data in HDFS.
- Wrote and executed unit, system, integration and UAT scripts in a Data Warehouse projects.
- Assisted in defining business requirements and created BRD (Business Requirements Document) and functional specifications documents.
- Involved in Data profiling, Data analysis and data mapping artifacts design.
- Wrote and executed SQL queries to verify that data has been moved from transactional system to DSS, Data warehouse, data mart reporting system in accordance with requirements.
- Worked on PL/SQL collections, index by table, arrays, bulk collect, FOR ALL, etc.
- Generated various reports using SQL Server Report Services (SSRS) for business analysts and the management team.
- Analyzed results from data validation queries to present to user groups.
- Supported development team & QA team during process design and during performance tuning, Test Strategy and test case development.
- Responsible for the data architecture design delivery, data model development, review, approval and Data warehouse implementation.
- Testing responsibilities included unit testing, integration testing, and business acceptance testing.
Environment: SAP PowerDesigner16.6, Agile, MDM, pl/Sql, SSAS, SSRS, ETL, OLTP, SQL Scripts, Big data, NoSQL, MS Visio, MS Azure.
Confidential - Eden Prairie, MN
Data Architect
Responsibilities:
- Developed full life cycle software including defining requirements, prototyping, designing, coding, testing and maintaining software.
- Extensively used Agile methodology as the Organization Standard to implement the data Models.
- Involved in requirement gathering and database design and implementation of star-schema, snowflake schema/dimensional data warehouse using ER/Studio.
- Created dimensional models for the reporting system by identifying required dimensions and facts using ER/Studio.
- Conducted JAD sessions, wrote meeting minutes and also documented the requirements.
- Collected requirements from business users and analyzed based on the requirements.
- Initiated Use Case Analysis using UML, which provided the framework for potential use case deliverables and their inter-relationships.
- Involved in extensive Data validation by writing several complex SQL queries.
- Involved in back-end testing and worked with data quality issues.
- Used reverse engineering for a wide variety of RDBMS, including Microsoft SQL Server, and MySQL to connect to existing database and create graphical representation using ER/Studio.
- Created conceptual and logical models, logical entities and defined attributes, and relationships between the various data objects.
- Developed complex SAS Macros to simplify SAS code and effectively reduce coding time.
- Involved in developing the data warehouse for the database using the Ralph Kimball's Dimensional Data Mart modeling methodology.
- Worked on Data modeling, Advanced SQL with Columnar Databases using AWS
- Migrated reference data from existing product into Informatica MDM hub.
- Worked with Hadoop eco-system covering HDFS, HBase, Yarn and MapReduce.
- Generated comprehensive analytical reports by running SQL queries against current databases to conduct Data Analysis.
- Worked with data compliance teams, Data governance team to maintain data models, Metadata, Data Dictionaries.
- Wrote, tested and implemented Fastload, Multiload, BTEQ scripts, DML and DDL.
- Designed ER diagrams (Physical and Logical using ER/Studio) and mapping the data into database objects.
- Developed and automated multiple departmental Reports using Tableau and MS Excel.
- Used Normalization (1NF, 2NF & 3NF) and de-normalization techniques for effective performance in OLTP and OLAP systems.
- Developed and maintained Data Dictionary to create Metadata Reports for technical and business purpose.
- Worked on Performance Tuning of the database which includes indexes, optimizing SQL Statements.
- Worked on PL/SQL programming Stored Procedures, Functions, Packages and Triggers.
- Designed and developed data mapping and transformation scripts to support data warehouse development and data analytics efforts.
- Developed extract, transform and load (ETL) logic and code in support of data warehouse and analytics operations and maintains related data pipelines.
- Designed and developed a Data Lake using Hadoop for processing raw and processed claims via Hive.
- Executed Hive queries on Parquet tables stored in Hive to perform data analysis to meet the business requirements.
- Performed data analysis using SQL queries.
- Performed ad hoc data analysis with the use of statistical analysis and data mining techniques.
- Implemented Forward engineer physical models to create DDL scripts to implement new databases or database changes.
- Implemented Reverse engineer databases and synchronize data models with actual data implementations.
- Created data dictionaries and business glossaries to document data lineages, data definitions and metadata for all business-critical data domains.
- Developed canonical models and data transformation rules using XML.
- Created and Manage schema objects such as tables, views, indexes, User define function and Store procedures using T-SQL.
- Developed advanced SQL queries with multi-table joins, group functions, sub-queries, set operations and T-SQL stored procedures, user defined functions (UDFs) for data analysis.
- Wrote DDL and DML statements for creating, altering tables and converting characters into numeric values.
- Developed various QlikView Data Models by extracting and using the data from various sources files Excel, Flat Files and Big data.
- Generated multiple ad-hoc Python tools and scripts to facilitate map generation and data manipulation.
- Worked with business analyst to design weekly reports using combination of Crystal Reports.
Environment: ER/Studio, Microsoft SQL Server, MySQL, SAS, Amazon Redshift, MDM, HDFS, HBase, Yarn, MapReduce, OLTP, OLAP, Metadata, MS Excel, QlikView, Crystal Reports
Confidential - Newport Beach, CA
Sr. Data Analyst
Responsibilities:
- Analyze the OLTP Source Systems and Operational Data Store and research the tables/entities required for the project.
- Designing the measures, dimensions and facts matrix document for the ease while designing.
- Created data flowcharts and attribute mapping documents, analyzed the source meaning to retain and provide proper business names following the very stringent FTB's data standards.
- Developed several scripts to gather all the required data from different databases to build the LAR file monthly.
- Developed numerous reports to capture the transactional data for the business analysis.
- Developed complex SQL queries to bring data together from various systems.
- Organized and conducted cross-functional meetings to ensure linearity of the phase approach.
- Collaborated with a team of Business Analysts to ascertain capture of all requirements.
- Created multiple reports on the daily transactional data which involves millions of records.
- Used Joins like Inner Joins, Outer joins while creating tables from multiple tables.
- Created Multi set, temporary, derived and volatile tables in Teradata database.
- Implemented Indexes, Collecting Statistics, and Constraints while creating tables.
- Utilized ODBC for connectivity to Teradata via MS Excel to retrieve automatically from Teradata Database.
- Developed various ad hoc reports based on the requirements
- Designed & developed various Ad hoc reports for different teams in Business (Teradata and Oracle SQL, MS access, MS excel)
- Developed SQL Queries to fetch complex data from different tables in remote databases using joins, database links and formatted the results into reports and kept logs.
- Involved in writing complex SQL queries using correlated sub queries, joins, and recursive queries.
- Delivered the artifacts within the time lines and excelled in the quality of deliverables.
- Validated the data during UAT testing.
- Performing source to target Mapping
- Involved in Metadata management, where all the table specifications were listed and implemented the same in Ab Initio metadata hub as per data governance.
- Developed Korn Shell scripts to parallel extract and process data from different sources simultaneously to streamline performance and improve execution time in a parallel process for better time, resource management and efficiency.
- Used Teradata utilities such as TPT (Teradata Parallel Transporter), FLOAD (Fastload) and MLOAD (Multiload) for handling various tasks.
- Developed Logical data model using Erwin and created physical data models using forward engineering.
Environment: Erwin 8.0, Teradata 13, TOAD, Oracle 10g/11g, MS SQL Server 2008, Teradata SQL Assistant, XML Files, Flat files
Confidential - Portsmouth, NH
Sr. Data Analyst/Data Modeler
Responsibilities:
- As a Data Analyst/Data Modeler I was responsible for all data related aspects of a project.
- Worked with DBAs and the security coordinators to get access to the team members.
- Participated in requirement gathering sessions, conducted JAD sessions with users, subject matter experts and business analysts.
- Developed conceptual model using Erwin based on business requirements.
- Developed and normalized logical and physical data base model using OLTP systems for finance applications.
- Extensively used normalization techniques (up to 3NF).
- Produced functional decomposition diagrams and defined logical data model.
- Involved in redesigning of the existing OLTP systems, modification and designing new requirements in the existing systems.
- Involved in designing the context flow diagrams, structure chart and ER-diagrams.
- Performed forward engineering to create a physical SAS model with DDL, based on the requirements from logical data model.
- Developed the code as per the client's requirements using SQL, PL/SQL and Data Warehousing concepts.
- Wrote T-SQL statements for retrieval of data and Involved in performance tuning of T-SQL queries and Stored Procedures.
- Involved in designing and developing SQL server objects such as Tables, Views, Indexes (Clustered and Non-Clustered), Stored Procedures and Functions in Transact-SQL.
- Involved with data profiling for multiple sources and answered complex business questions by providing data to business users.
- Implemented referential integrity using primary key and foreign key relationships.
- Involved in development and implementation of SSIS, SSRS and SSAS applications.
- Performed extensive data analysis and data validation on Teradata.
- Generated ad hoc SQL queries using joins, database connections and transformation rules to fetch data from legacy oracle and SQL server database systems.
- Assisted Reporting developers in building Reports using Crystal Reports.
- Used Erwin for reverse engineering to connect to existing database and ODS to create graphical representation in the form of Entity Relationships and elicit more information.
- Acted as strong Data Analyst analyzing the data from low level in conversion projects, provided mapping documents between legacy, production and user interface systems.
- Used Model Mart of Erwin for effective model management of sharing, dividing and reusing model information and design for productivity improvement.
- Interacted with client, management and staff to identify and document business needs and objectives, current operational procedures for creating the logical data model.
- Facilitated in developing testing procedures, test cases and User Acceptance Testing (UAT).
- Extensively used SSIS import/export wizard for performing the ETL operations.
Environment: JAD, Erwin7.5, OLTP, 3NF, DDL, T-SQL, SSRS, SSAS, Teradata13, Crystal Reports7.0, ODS, UAT
Confidential
Data Analyst
Responsibilities:
- Gathered and translated business requirements into detailed, production-level technical specifications, new features, and enhancements to existing technical business functionality.
- Used the Waterfall methodology to build the different phases of Software development life cycle.
- Participated in all phases of data mining, data collection, data cleaning, developing models, validation, and visualization and performed Gap analysis
- Demonstrated experience in design and implementation of Statistical models, Predictive models, enterprise data model, metadata solution and data life cycle management.
- Data analysis and reporting using MS Power Point, MS Access and SQL assistant.
- Generated periodic reports based on the statistical analysis of the data using SQL Server Reporting Services.
- Developed SQL scripts for creating tables, Sequences, Triggers, views and materialized views.
- Compiled data from various sources public and private databases to perform complex analysis and data manipulation for actionable results.
- Used and maintained database in MS SQL Server to extract the data inputs from internal systems.
- Interacted with the Client and documented the Business Reporting needs to analyze the data.
- Used SAS for pre-processing data, SQL queries, data analysis, generating reports, graphics, and statistical analyses.
- Migrated database from legacy systems, SQL server to Oracle.
- Performed data analysis, statistical analysis, and generated reports, listings and graphs using SAS tools-SAS/Base, SAS/Macros and SAS graph, SAS/SQL, SAS/Connect, and SAS/Access.
- Developed data mapping documentation to establish relationships between source and target tables including transformation processes using SQL.
- Extensive data cleansing and analysis, using pivot tables, formulas (V-lookup and others), data validation, conditional formatting, and graph and chart manipulation using excel.
- Created pivot tables and charts using worksheet data and external resources, modified pivot tables, sorted items and group data, and refreshed and formatted pivot tables.
- Used advanced Microsoft Excel to create pivot tables, used VLOOKUP and other Excel functions.
- Worked on CSV files while trying to get input from the MySQL database.
- Created functions, triggers, views and stored procedures using MySQL.
- Worked on database testing, wrote complex SQL queries to verify the transactions and business logic.
- Worked on data profiling and data validation to ensure the accuracy of the data between the warehouse and source systems.
- Developed SQL Queries to fetch complex data from different tables in remote databases using joins, database links and Bulk collects.
Environment: Erwin 8.0, SDLC, MS Power Point, MS Access, MS SQL Server2008, SAS, Oracle10g, Microsoft Excel