PROFESSIONAL SUMMARY:

• Nine - year IT working experience focusing on business intelligence, ETL, and reporting across various professional domains such as healthcare, Confidential, Confidential, insurance and consume Confidential industry.
• Rich experience in T-SQL development using DQL, DML and DDL to create various UDSP, UDF, views, triggers, CTEs, indexes and ad-hoc T-SQL queries.
• Experience with SDLC, including requirement gathering, designing, development, testing, deployment as well as maintenance.
• Proficiency in data cleansing, data migration, troubleshooting, debugging, performance tuning and error handling in SQL Server, SSIS and SSRS.
• Expertise in ETL (extracting, transforming, and loading) using different data tools, such as SQL, SSIS and Oracle.
• Experience in data modeling, dimensional modeling, data mart design and implementation.
• Extensive knowledge of statistical analytics, data mining, machine learning and predictive modeling using Python, R and SPSS.
• Experience in reporting and data visualization using SSRS, Power BI and Tableau.
• Excellent communication and interpersonal skills, being able to work both collaboratively and independently.
• Experience in leading a team, applying both Scrum and Agile method, allocating workload, assigning tasks, and tracking the project.
• Experience in communicating with business users, understanding and transforming business requirements, and reporting to business users.

TECHNICAL SKILLS:

Programming Languages: T-SQL, MySQL, PostgreSQL

Other Programming Languages: C, C#, Python, R, Visual Basic

Database: SQL Server 2008R 2/2012/2014/2016 , Oracle Database 11g R2, MS Access

BI Tools: SSIS, SSRS, SSAS, SSDT, SQL Profiler, Power BI, Tableau, SSPS, SAS, Google Analytics, Google AdWords

Version Control: TFS (Team Foundation Server), Git

Job Scheduling & Work Automation: SQL Server Job Agent

Big Data & Cloud Service: Hadoop, Azure (Azure Data Factory, Cloud Services, Azure SQL Database)

SDLC: Agile, Scrum

Data Modeling Tools: MS Visio, Erwin, Draw.io

Project Management Tools: Jira, SharePoint, Trello

DWH: Kimball, Inmon, Star Schema, Snowflake Schema

PROFESSIONAL EXPERIENCE:

Confidential, Rockleigh, NJ

Lead ETL/Reporting Developer

Responsibilities:

• Hosted joint application design (JAD) meetings with business users, as well as collaborated and communicated with DBAs and developers to convert business rules and logic to technical documents, including BRD, data mapping documents, data analysis, etc.
• Resolved variety of high impact problems/projects through in-depth evaluation of complex business processes, system processes, and industry standards, with my understanding of technology and the Confidential industry.
• Transformed business requirements into user stories, designed ETL process and selected the error handling and logging methods based on business requirement.
• Declared Scrum as the SDLC methodology and performed the role as the scrum master to monitor the project progress, to educate team members, to clarify the sprint, and to host scrum meetings, both daily and bi-weekly.
• Proposed the recruiting requirements, wrote the job description and participated in recruiting, educating, and evaluating new team members.
• Introduced enterprise data modeling, system mappings, and other interface/asset diagrams to ensure standard vocabulary for data assets, including code business entities and their data relationships.
• Maintained high standards of data quality through the introduction and enforcement of information handling processes and organization of the data integration processes.
• Revised data integration architectures when required to be compatible with changing business needs and client standards so that legal, compliance and operational requirements, such as data security and privacy policies, could be guaranteed.
• Utilized advanced knowledge of system flow and developed standards for coding, testing, debugging, and implemented the accordingly designed KPI system.
• Appended database structure using data modeling, creating and modifying certain tables and columns.
• Created more than 30 stored procedures with variables, dynamic SQL, CTEs, etc., around 20 user defined functions, as well as views, triggers and constraints to define business logic and rules, to support ETL, and to maintain database integrity.
• Wrote complex queries with transactions and DML statements to perform data cleansing, to remove expired data and duplicates, to fetch the latest modified data, and to implement hierarchical structures, along with implementing decent error handling.
• Analyzed, troubleshooted and modified existing stored procedures to implement new features or to fix issues such as incorrect calculation and parameter sniffing issue.
• Enhanced database performance in T-SQL using information in execution plan, including adding indexes (covering indexes, non-clustered indexes, columnstore indexes), updating statistics, recompiling stored procedures, etc.
• Developed slowly changing dimension (SCD) Type 4 with temporal table for incremental load of certain dimensions.
• Performed initial load and incremental load using SSIS and stored procedures, and applied create date and modify date method to do incremental load.
• Directed and reviewed 70+ SSIS packages done by the team members, to implement ETL process, extracting data from multiple sources such as OLTP database, XML files, Excel files and flat files, applying data profiling, data cleansing, data validation and transformation using control flow, and loading it into the destinations.
• Provided directions to assist the team writing C# and VB scripts to send emails about the error data, split files, call stored procedures, validate data, etc.
• Managed SSIS packages in Dev, Test and Production servers with TFS using check in and check out and maintained historical version for every minor change.
• Created 30+ complex SSRS reports, including drill through reports, thrill down reports, linked reports, sub-reports for analysis purpose, using multiple data providers, global variables, expressions, user-defined objects and various charts.
• Developed and implemented ad-hoc reports and other reports with parameters, especially cascading parameters, using SSRS for the flexibility of analytics.
• Participated in UAT on SQL scripts and SSIS/SSRS packages along with business users, and refined the products based on business users’ requirements.

Confidential, San Francisco, CA

SQL Developer - Team Lead

Responsibilities:

• Understood and transformed business requirements into user stories, taking the advantage of my solid understanding of Confidential process. Broke the project down into backlogs with user story, chose appropriate time boxes and prioritized them to continuously complete in each cycle time.
• Communicated with business users to follow up with business users to generate consistently updated requirements, to collect feedbacks from business users during UAT, and to understand tickets from business users.
• Specified Agile as the SDLC methodology based on the environment and business demands that the management team was still testing and verifying the best usage of the data warehouse. Hosted weekly meetings to follow up the progress and to keep the whole team on track.
• Declared roles and responsibilities of each members, raised recruitment requirements, clarified job description, and educated new team members.
• Designed and implemented a series of proper regulations, such as coding standard, documentation requirements and governing framework design, to guarantee the data availability, usability, integrity, and security.
• Assessed the overall project workloads, allocated and adjusted tasks to team members, kept tracking the progress, and consulted on solving complicated issues encountered during the process.
• Clarified the performance criteria and the KPI, evaluated the performance of team members, and coached the team to improve.
• Solved sophisticated requirements dealing with different data sources, including SQL Server database from Azure, XML files, or Excel files, to guarantee the data integration.
• Collaborated with front-end developer team to design and create a simple platform that handles information exchange and data transformation between developer and business users, by providing requirements, designing internal process, and participating in UAT.
• Designed and adjusted the ETL process based on the transformed business requirements, clarified the control flow to do ETL, set up the rules of how to do error handling and logging, and declared the deployment method.
• Provided solutions during complicated troubleshooting and debugging process, and offered suggestions of implementing performance tuning.
• Provided recommendations on designing, optimizing, and adjusting the structure of the data warehouse from both ETL process and business analytical perspective. Defined slowly changing dimensions in the data warehouse and chose appropriate SCD types.
• Granted permissions to the team for specific tasks, such as adding indexes, to perform Database backup and recovery, to implement SQL database on-premise and private data Gateway set-up, to set up test server for testing purpose and UAT, and to do data warehouse modeling for data analysis.
• Ensured database stability and integrity by configuring SQL data profiler and SQL Server Job Agent scheduling.
• Performed initial load and incremental load using SSIS and stored procedures, and applied different methods of for incremental load.
• Developed roughly 20 SSIS packages to implement ETL process as required, extracting data from multiple sources, applying data profiling, data cleansing, data validation and transformation using control flow, and loading it into the destinations.

Business Analyst

Confidential, Boston, MA

Responsibilities:

• Wrote T-SQL scripts to perform data cleansing, error handling, checking data validation and so on with UDSP, scalar-valued and table-valued functions, aggregate functions, wildcard, CTEs, variables, control flow statements, etc.
• Designed, implemented, and supported the environment with OLTP and OLAP data warehouse for millions of records of patients using views, indexes, constraints, multi-dimensional models and fact tables, star schema and snowflake schema, etc.
• Solved sophisticated requirements dealing with different data sources, including SQL Server database from Azure, XML files, Excel files for customer and insurance information, Access files for achieve data, and flat files for payment statements.
• Built more than 50 SSIS packages to maintain data marts of the company and performed daily incremental loads. Implemented SCD Type 2 for financial data and Type 1 for actuarial data.
• Ensured database stability and integrity by configuring SQL job profiler and SQL Server Job Agent scheduling.
• Resolved tickets from business users and conducted troubleshooting, debugging and performance tuning in both T-SQL and SSIS by enhancing SQL statements and utilizing control flow tasks, data flow components, writing expressions and C# codes, etc.
• Implemented transactions on DML activities and performed locks (exclusive lock, shared lock, etc.) to ensure data integrity and improve performance.
• Documented FRD to determine the design and technical approach and data mapping files to support ETL process.
• Assisted DBA teams to perform Database backup and recovery, SQL database on-premise and private data Gateway set-up, and data warehouse modeling for data analysis and machine learning in databases across IT department, ECRM team, EDS teams, and so on in Azure environment.
• Worked under Agile environment and participated in daily meeting with different teams. Assisted communication between business users and developers with help from actuarial knowledge.

SQL/ETL Team lead

Confidential

Responsibilities:

• Hosted joint application design (JAD) meetings with business users, as well as collaborated and communicated with DBAs and developers to convert business rules and logic to technical documents, including BRD, data mapping documents, data analysis, etc.
• Resolved variety of high impact problems/projects through in-depth evaluation of complex business processes, system processes, and industry standards, with my understanding of technology and the Confidential industry.
• Transformed business requirements into user stories, designed ETL process and selected the error handling methods based on business requirement.
• Declared Scrum as the SDLC methodology and performed the role as the scrum master to monitor the project progress, to educate team members, to clarify the sprint, and to host scrum meetings, both daily and weekly.
• Proposed the recruiting requirements, wrote the job description and participated in recruiting, educating, and evaluating new team members.
• Introduced enterprise data modeling, system mappings, and other interface/asset diagrams to ensure standard vocabulary for data assets, including code business entities and their data relationships.
• Maintained high standards of data quality through the introduction and enforcement of information handling processes and organization of the data integration processes.
• Solved sophisticated requirements dealing with different data sources, including SQL Server database from Azure, XML files, or Excel files.
• Provided solutions during complicated troubleshooting and debugging process, and offered suggestions of implementing performance tuning.
• Provided recommendations on designing, optimizing, and adjusting the structure of the data warehouse from both ETL process and business analytical perspective. Defined slowly changing dimensions in the data warehouse and chose appropriate SCD types.
• Grant permissions to the team for specific tasks, such as adding indexes, to perform Database backup and recovery, to implement SQL database on-premise and private data Gateway set-up, to set up test server for testing purpose and UAT, and to do data warehouse modeling for data analysis.
• Designed and adjusted the ETL process based on the transformed business requirements, clarified the control flow to do ETL, set up the rules of how to do error handling and logging, and declared the deployment method.
• Directed and reviewed 140+ SSIS packages done by the team members, to implement ETL process, extracting data from multiple sources such as OLTP database, XML files, Excel files and flat files, applying data profiling, data cleansing, data validation and transformation using control flow, and loading it into the destinations.

SQL Developer

Confidential

Responsibilities:

• Actively involved in JRD and JAD to gather business requirements from business users and converted the business rules into technical approaches.
• Assisted in finalizing conceptual model into Chen’s Entity-relational Diagram (ERD) and Crow’s foot ERD.
• Supported physical implementation requirements for dimension and fact tables, applying normalization techniques and adding constraints to ensure database integrity with DML and DDL.
• Performed both initial load and incremental load using SSIS packages.
• Conducted data profiling, data cleansing and data validation in the pre-staging phase and data transformation in the staging phase using control flow and stored procedures.
• Loaded source data from FTP and SFTP servers to SQL Server destination.
• Troubleshooted queries with low performance by checking execution plan, analyzing tracing files from SQL Profiler and so on.
• Deployed SSIS packages and scheduled jobs using SQL Server Job Agent.
• Developed complex stored procedures and views for SSRS reports and Power BI dashboards with the aim of analyzing periodical KPIs and retailers’ sales performance.
• Designed SSRS report layout and wrote complicated expressions and functions to achieve conditional formatting.
• Deployed reports and scheduled report deliveries on a daily, weekly and monthly basis utilizing both standard and data-driven subscriptions. Managed and secured reports by rendering them into Word, Excel and PDF format.
• Shared reports using SharePoint and utilized snapshot and caching to increase the server performance.
• Associated with the team while writing complicated C# and VB scripts to send emails about the error data, split files, call stored procedures, validate data, etc.

Confidential

SQL/Report Developer

Responsibilities:

• Engaged in daily scrum meetings with different teams for cross-function cooperation in agile scrum mode.
• Developed more than 40 SSIS packages to perform ETL, data profiling, data cleansing, data validation and transformations based on the mapping documents and reference tables indicating data cleansing rules.
• Designed complex T-SQL stored procedures to support ETL process and SSRS reports with CTEs, table variables, dynamic SQL, merge, etc.
• Performed performance tuning for SSIS packages by modifying stored procedures, adding containers to run tasks parallelly, replacing nonsynchronous components with synchronous components and so on.
• Handled errors in SSIS with event handler, data viewers, checkpoints, breakpoints, auditing and logging, including both custom logging and built-in logging.
• Created interactive Tableau dashboard of fraud and missing charge detection with various charts such as bar charts, line charts, geographic maps, scatter charts, etc.
• Used parameters, grouping, highlight actions, filters, data blending, calculated field for data preparation of dashboard and worksheets, such as retention rate, promotion rate, transaction volume, etc. in Tableau for different departments.
• Developed analysis reports in SSRS for internal KPI evaluation with line charts, scorecards, drop down list parameters, dual axis, etc.
• Deployed SSRS reports with DBA and generated report subscriptions with shared mode for management and local representatives.
• Attended meetings with test team to perform regular code reviews and with business users to ensure reports on UAT servers were correctly performed.

Confidential

Data Analyst

Responsibilities:

• Engaged in daily scrum meetings with different teams for cross-function cooperation in agile scrum mode.
• Developed more than 40 SSIS packages to perform ETL, data profiling, data cleansing, data validation and transformations based on the mapping documents and reference tables indicating data cleansing rules.
• Designed complex T-SQL stored procedures to support ETL process and SSRS reports with CTEs, table variables, dynamic SQL, merge, etc.
• Performed performance tuning for SSIS packages by modifying stored procedures, adding containers to run tasks parallelly, replacing nonsynchronous components with synchronous components and so on.
• Handled errors in SSIS with event handler, data viewers, checkpoints, breakpoints, auditing and logging, including both custom logging and built-in logging.
• Created interactive Tableau dashboard of fraud and missing charge detection with various charts such as bar charts, line charts, geographic maps, scatter charts, etc.
• Used parameters, grouping, highlight actions, filters, data blending, calculated field for data preparation of dashboard and worksheets, such as retention rate, promotion rate, transaction volume, etc. in Tableau for different departments.
• Developed analysis reports in SSRS for internal KPI evaluation with line charts, scorecards, drop down list parameters, dual axis, etc.
• Deployed SSRS reports with DBA and generated report subscriptions with shared mode for management and local representatives.
• Attended meetings with test team to perform regular code reviews and with business users to ensure reports on UAT servers were correctly performed.

Confidential

SQL Developer

Responsibilities:

• Collaborated and Communicated with BA, end users and developers to translate from business understandings in BRD to technical languages in FRD and to ensure business and system requirements are met.
• Coded dynamic SQL in stored procedures to increase the flexibility and functionality.
• Maintained T-SQL stored procedures, including troubleshooting and performance tuning using execution plan, SQL Profiler and Database Engine Tuning Advisor.
• Performed error handling using control flow statements and created both system error message using system variables and custom error messages using functions like THROW and RAISERROR.
• Created constraints (primary key constraints, foreign key constraints, unique key constraints, check constraints, etc.) to implement business rules and to conduct data validation using T-SQL.
• Wrote UDSPs and UDFs to define certain actuarial rules and calculations.
• Created staging tables with DDL to store data for transformation and complex calculated data.
• Developed roughly 20 views and more than 40 stored procedures to provide dataset for both SSIS packages and SSRS reports based on business requirements, such as statistical summary and transaction records.
• Designed and implemented around 30 SSIS packages for extracting, cleansing, validating and transforming and loading data from heterogeneous sources (RDBMS, XML files, flat files, etc.) into proper data marts.
• Utilized Execute Package Task to set up parent-child packages and applied sequence and foreach loop containers.
• Parameterized connection managers and checkpoint files. Deployed packages using project deployment model on different servers.
• Scheduled jobs for packages to perform both initial load and incremental load using SQL Server Job Agent.
• Collaborated with offshore QAs during the testing period to perform unit testing on SSIS packages.
• Created both ad-hoc and regular SSRS reports with parameters, charts, tables, matrices, lists, gauges, indicators and so on.
• Set up caching, snapshot and subscription to enhance the performance and increase convenience.
• Added BI projects and SQL Server instances to TFS for version control and repository maintenance.
• Designed documentations for readability of code and understanding of packages with data dictionaries, data mappings and package annotations.
• Engaged in Scrum development methodology. Communicated with product control team, front-end engineers and back-end developers for the end-to-end service for clients and managements.

Confidential

SQL Developer

Responsibilities:

• Wrote more than 70 stored procedures to provide values for datasets and parameters in SSRS reports.
• Created complex SQL queries with techniques like subquery, wildcard, join, CTE, view, etc. to combine various tables into a single result set, and to perform business calculations.
• Used SSRS to summarize the financial accounting information for each demander and supplier, such as current inventory (raw materials, work-in-process, and finished goods), the total debt amount, account payable, account receivable, realized cash flows, etc.
• Generated cached and snapshot reports on existing customer financial information and set subscriptions on data-driven reports as required by business development department.
• Designed and created 10+ report templates, including list, matrix, cascading parameters, multi-option parameters, drill through, sub report, column charts, line charts, and pie charts, to show the short- and long-term profit & loss rate on the products in the business portfolios in SSRS.
• Developed roughly 20 ad-hoc reports with complex T-SQL scripts based on various user requirements.
• Scheduled report deliveries on a daily, weekly, monthly, quarterly basis utilizing the standard and data-driven subscriptions in native mode.
• Supported Marketing department to integrate all the product information in visualized posts and interactive dashboards by using T-SQL and SSRS.
• Maintained 20+ reports in SSRS, including troubleshooting and performance tuning in stored procedures and report components.
• Participated in designing destination tables in SQL Server database and implementing ETL processes to migrate data from third-party files to data marts.
• Identified and documented approved SSRS report standards and best practices for tech team.
• Attended meetings with test team and tech team lead to perform regular code review and to ensure reports on UAT servers were correctly performed.