SUMMARY

• Six - year IT experience focusing on business intelligence, ETL, and reporting across various professional domains such as healthcare, insurance, banking and information technology industry.
• Strong knowledge of T-SQL development, using DQL, DML and DDL to create complex UDSP, UDF, views, triggers, CTEs, indexes and other ad-hoc T-SQL queries.
• Proficiency in data cleansing, migration, troubleshooting, debugging, performance tuning and error handling in SQL Server and related tools, including SSIS and SSRS.
• Expertise in ETL (extracting, transforming, and loading) using different data tools, such as SQL and SSIS (SQL Server Integration).
• Experience with SDLC of SQL Server instance, including requirement gathering, designing, development, testing, deployment as well as maintenance.
• Experience in data modeling, dimensional modeling, data mart design and implementation.
• Experience in reporting and data visualization using SSRS, Power BI and Tableau.
• Extensive knowledge of statistical analytics, data mining, machine learning and predictive modeling using Python, R and SPSS.
• Excellent communication and interpersonal skills, being able to work both collaboratively and independently.

TECHNICAL SKILLS

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

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

Programming Languages: T-SQL, Python, R, C, C#, Visual Basic

Version Control: TFS (Team Foundation Server), Git

Job Scheduling & Work Automation: SQL Server Job Agent

Big Data & Cloud Service: Hadoop, Azure (Azure Data Studio, 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, Edison, NJ

SQL Developer

Responsibilities:

• Attended joint application design (JAD) meetings with business users, as well as collaborated and communicated with DBAs, developers, and analysts to convert business rules and logic to technical documents, including BRD, data mapping documents, data analysis, etc.
• Created 80+ stored procedures with variables, dynamic SQL, CTEs, etc., 30+ 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 hierarchy concepts.
• Analyzed, troubleshooted and modified existing stored procedures to implement new features or to fix issues such as incorrect calculation and parameter sniffing.
• Enhanced database performance in T-SQL using information in execution plan, including adding indexes (covering indexes, non-clustered indexes, column store index), updating statistics, recompiling stored procedures, etc.
• Developed slowly changing dimension (SCD) Type 2 and Type 4 for incremental load of certain dimensions such as patients and physicians using DML such as merge, output, insert, update in stored procedures.
• Built pre-staging tables with DDL to hold intermediate data for data cleansing, data validating, and implemented error handling.
• Performed initial load and incremental load using SSIS and stored procedures, and applied timestamps and checksum method for incremental load.
• Developed 40+ SSIS packages 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.
• Wrote 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 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 reports with parameters, especially cascading parameters, using SSRS for the flexibility of analytics.
• Generated analytical reports for healthcare data such as ICD-10, CPT-4, and other medical record data.
• Performed unit tests and UAT on SQL scripts and SSIS/SSRS packages, and refined the products based on business users’ requirements.

Confidential, Schenectady, NY

BI Developer

Responsibilities:

• 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 60+ views and 100+ 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 50+ 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 Agile 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, Troy, NY

SQL/Software Developer

Responsibilities:

• Wrote complex SQL queries with techniques of wildcard, CTEs, joins, set operators, aggregate functions, window functions, etc. to combine multiple data sources into one single dataset for modeling preparation.
• Developed stored procedures and dynamic SQL to perform data validation, data cleansing, data profiling and data partition to prepare data for modeling.
• Executed scripts written in supported languages including Python and R in SQL Server.
• Implemented machine learning algorithms such as Decision Trees, Random Forest, SVM, Multivariate LSTMs, and time series models such as ARIMA, Exponential Smoothing, Facebook Prophet in order to enhance the accuracy of the software’s predictive analysis feature using different packages in Python and libraries in R.
• Created views and tables with DML to store the results of the forecasting results and model accuracy statistics, so as to display the results in the reports generated by the software.
• Integrated the software with developed new features with Power BI, Azure and Azure Services for testing purpose.
• Assisted the QA to test the accuracy of data value of the production and ensure the production can fulfill the required function.
• Attended meetings with business clients and IT departments to gather requirements from users and ensured UAT was correctly performed.
• Responsible for production support and worked on Jira Scrum for job scheduling.

Confidential

SQL Database Developer

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 60+ billion records of customer information and payment 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 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.
• Executed Agile SDLC methodology and participated in daily meeting with different teams.
• Assisted communication between business users and developers with help from actuarial knowledge.

Confidential

SQL Developer

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.

Confidential

SQL/Report Developer

Responsibilities:

• Engaged in daily scrum meetings with different teams for cross-function cooperation in agile scrum mode.
• Developed 60+ 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.