SUMMARY

• Over 6 years of experience in SQL, ETL, Reporting, and BI development across Financial, Banking, Manufacturing, Insurance, and Healthcare industries.
• Experience in data migration, data validation, data integration, data cleansing, data transformation, and data integrity maintaining across various data sources including OLEDB, flat file, Excel, ODBC, and XML.
• Proficiency in stored procedure designing, user - defined function designing, query optimization, error handling, and performance tuning using TSQL.
• Experience in both internal and external communication and consolidation with project-related stakeholders on both the technical side and business side.
• Hands-on experience of report designing, dashboard designing, and other related visualization tasks using SSRS, Tableau, and Power BI.
• Participation in data modeling, data mart designing, and data warehouse revising both in forward engineering and reverse engineering.
• Ability to analyze business requirements and translate the business requirements into technical solutions.
• Experience with the enterprise data warehouse (EDW) and the data marts of the top-down data warehouse built under Inmon method and with data marts in the bottom-up Kimball method data warehouses.
• Proved ability to learn and merge into new and dynamic environment implementing different technologies and methodologies.

TECHNICAL SKILLS

Databases: SQL Server (2008, 2008R2, 2012, 2014, 2016), Oracle, MS Access

BI tools: SSIS, SSRS, MS Dynamic 365, Power BI, Tableau, SSDT, Informatica, DataStage, Report Builder, ER-Win, MS Visio

DWH: Kimball, Inmon, Star Schema, Snowflake Schema

Languages: T-SQL, Python, R, Hadoop, Spark, Scala, C#, JavaScript

Big Data Architectures: Hadoop, Spark, Hive, Pig

Data Analysis Tools: SAS, SPSS, Arena, Minitab, SAS Enterprise Miner, @Risk

Cloud services: AWS Redshift, Azure (Cloud Data Factory, Azure SQL database)

Platforms: MapReduce, HDFS, TensorFlow

Environment: s: Agile, Scrum, Waterfall, Spiral

Collaboration Tool: SharePoint

Version control: TFS GIT

PROFESSIONAL EXPERIENCE

SQL/ETL developer

Confidential, Mahwah, NJ

Environment: SQL Server 2017, T-SQL, ER-Win, SSIS, SSRS, Power BI

Responsibilities:

• Conducted the JRD (Joint Requirement Development) session and generated documentation such as data mapping documents and business requirement documents.
• Analyzed and modified dimension and fact tables in the data warehouse to adapt to the ETL solutions.
• Created stored procedures and user-defined functions in TSQL, and utilized stored procedures within Execute SQL task and OLEDB command transformation in SSIS packages for the ETL process.
• Expert in writing appropriate stored procedures, user defined functions, and CTEs to fit business logic and business calculation.
• Skills in creating views, temp tables, table variables, subquery and complex triggers to facilitate efficient data manipulation and data consistency.
• Excellent experience of creating constraints, such as key constraint, check constraint, default constraint and not null constraint to keep data integrity.
• Hands-on experience in creating cluster index, non-cluster index, and covering index, etc. to sort and optimize data fetch time.
• Expertise in using aggregate function, window function, analytic function, ranking function, data and type function, and string function to fit complex business calculation.
• Expertise in writing joins and set operators to retrieve data from different tables.
• Proficiency in implementing transaction statements on DML activities and performed locks (exclusive lock, shared lock, etc.) to improve performance.
• Excellent experience of query optimization and performance tuning using SQL profiler and Database Engine Tuning Advisor.
• Implemented error handling methods utilizing event handlers, raise error statements, try/catch blocks, etc. separately in SSIS and TSQL stored procedures.
• Created, tested, deployed, and scheduled 43 SSIS packages for initial and incremental loads in both control flow and data flow levels.
• Consolidated and cleaned data from different sources such as Excel, flat files, XML, and OLEDB utilizing control flow tasks and data flow transformations.
• Optimized the performance of related SSIS package design, queries, and the stored procedures with implementing parallelism with containers in SSIS, inspecting execution plans, adding and modifying indexes, recompiling stored procedure to solve parameter sniffing issues, etc.
• Wrote C# script in order to send customized emails with HTML tables, split large files before ETL, etc.
• Designed customized logging in order to track the abnormal events happened during the package executing process and to help navigate the maintenance team to do error handling and troubleshooting.
• Created solutions to deal with type 2 and type 4 slowly changing dimensions (SCD) with MERGE based stored procedures in control flow and with conditional split and lookup transformation components in the data flow.
• Parameterized ETL packages and stored procedures to increase flexibility and adapt to possible future changes.
• Deployed several SSIS packages in project mode, set up the relevant environment, and passed parameters into packages in SQL Server following rules set by the DBA.
• Conducted various SSRS reports with functions such as the drill-down report, drill-through report, linked report, dynamic matrix (pivot table), and other charts regarding different audiences, different purposes, and different requirements.
• Set up the cascading parameters and multi-value filter for the report in order to improve both the flexibility and the convenience for the report using.
• Created both standard and data-driven monthly subscriptions for the business users, who are responsible to apply the governmental subsidiary and to raise donations from both public and charities.
• Deployed the SSRS report in native mode using the Reporting Services Configuration Manager.
• Used Visual Basic to write expressions in SSRS to achieve tasks such as setting up alternative row colors.

ETL/BI Developer

Confidential, New York, NY

Environment: SQL Server 2016, T-SQL, ER-Win, SSIS, SSRS, DataStage, Power BI Desktop

Responsibilities:

• Prepared the complete data mapping document for all the migrated jobs.
• Created T-SQL scripts and 20+ complex stored procedure for data validation and data process tables, and designed T-SQL queries with UDFs, CTEs, table variables, temp tables, etc. to implement business logic.
• Created and altered 30+ tables and views in EMR, departmental and claim data marts. Performed index analysis on tables and provided more efficient solutions to use between different kinds of indexes such as clustered, non-clustered and covering indexes.
• Performed error handling in T-SQL stored procedure using try/catch, raiserror, throw, etc.
• Defined ETL process by creating 80 packages for extracting, cleansing, transforming and loading data fromheterogeneous sources such as Oracle, DB2, Sybase, SAP, SQL Server, PSFT in different format such as flat files, Excel, XML into SQL Server data marts. Utilized DataStage and SSIS ETL tools.
• Performed initial and incremental load from new incoming data in various data sources for dimension tables and fact tables. Utilized Python to automate various tasks. Used type 2 slowly changing dimension to insert data.
• Performed error handling in event handler, implemented custom logging to output error and record package running information, and achieved performance tuning and trouble shooting.
• Processed data profiling and data cleansing with pre-staging and staging table.
• Implemented various types of SSIS Transformation in packages including Aggregate, Merge Join, Lookup, Conditional Split, Row Count and Derived Column etc.
• Migrated data from SQL Server to AWS Redshift with SSIS ZappySys PowerPack plug-in, including the amazon redshift data transfer task, amazon redshift executeSQL task, etc.
• Implemented the master child package technique to manage big ETL project efficiently.
• Achieved performance tuning and trouble shooting in t-sql and SSIS such as rewriting stored procedure, loading data in parallel and batch size, and rescheduling deploy time.
• Performed unit testing on SSIS packages and collaborated with QA during the testing period such as writing some scripts and testing scenarios for QA.
• Worked with senior Architect and DBA to develop data validation procedures and implement logic required business rules.
• Consistently applied security standards in daily development, administration, and/or solutions where applicable and as set for in the Information Security and HIPAA policies.
• Maintained HIPAA transactions specifically 835 and 837 transaction sets, created the ad hoc reports to query the transaction sets.
• Designed different types of reports such as Up-selling and Cross-selling reports using Power BI, such as last five-year sales static KPI report, regional sales dashboard and patient satisfaction granular filtering.
• Scheduled the delivery of Power BI reports in different formats such as PDF, Excel, using standard and did performance tuning to improve the response times and reduce traffic to the report sever.

ETL/DW developer

Confidential

Responsibilities:

• Designed databases (physical and logical) to meet business requirements using ERWIN.
• Create various data marts such as user, contact, delinquency and from scratch by taking into various business process by identifying different dimensions and facts from existing data warehouse.
• Designed new database objects like stored procedures, functions, triggers, indexed, tables and views, table-valued and scalar functions using T-SQL in development environment.
• Created stored procedures for reporting on audit information and error information during each ETL process.
• Handling query optimization and performance tuning using Execution Plan, SQL profiler and database tuning advisor (DTA).
• Create packages with several transformation in each package making sure that the data is cleaned and confirmed before being sent to staging area.
• Complete complex ETL packages to pull data from DB2/Oracle, loading monthly data from Excel to SQL Server.
• Implemented error handling and debugging in SSIS packages using event handler, row redirects, check points and used logging for monitoring the execution.
• Implemented proper package configuration features and finally securing the package by using password and encryption methods.
• Enabled checkpoints on ready implemented transactions to optimize entire ETL strategy so as to start the package execution from latest point of failure.
• Promoted database objects from test/development to production server by coordinating and communicating with production schedules within development team.
• Created different types of reports like drill down, drill through, sub-reports and parameterized reports.
• Designed reports to include charts and logos that complies with organization culture and policies of reporting and documentation.
• Design and develop of reporting data module for Credit Card Marketing Analytics.
• Creating Row level security with power bi and integration with power BI service portal.
• Developed analysis reports and visualization using DAX functions like table function, aggregation function and iteration functions.
• Worked on all types of transformations that are available in Power BI query editor.

ETL/SQL Developer

Confidential

Responsibilities:

• Designed the ‘dimensional model’ of data mart and used Erwin 9.0 to design the business process, grain the data representation, dimensions and facts tables with measured facts.
• Created complex stored procedures to perform index maintenance, data profiling, critical staging procedures for loading data marts and reporting database.
• Optimized and restructured the Approval WorkFlow stored procedure for application by evaluating execution plan, indexing and partitioning large tables.
• Performed index analysis for tables and came up with more efficient solutions to use clustered and non-clustered indexes for significant performance boost.
• Enhanced the functionality and performance of various SQL scripts and stored procedures by using UDFs, CTEs and system stored procedures along with using SQL profiler and DTA.
• Designed, created and implemented complex ETL strategies using SSIS packages that extracted the data from different heterogenous sources such as flat file, Excel and dumped them into a centralized data mart.
• Deployed various types of SSIS transformation in packages including slowly changing dimension, aggregate, lookup, conditional split, mergejoin, derived column etc, and handled erroneous output using event handler.
• Made use of Event Handler, Logging, Breakpoint and Data Viewers for effective debugging of packages.
• Optimized performance of slow executing packages by making changes as control flow and data flow like avoiding blocking transformation, increase buffer size.
• Designed and developed SSIS package template, so similar development practices are followed by all SSIS developers for logging, configurations and error handing.
• Successfully performed and documented unit testing and system testing of the entire ETL process.
• Developed Power BI profit analysis reports and dashboards by products and customers.
• Worked on all kinds of Power BI reports such as yearly, quarterly, monthly and daily, and executed dashboards setups and data manipulation using DAX.