Jiayi Li Email and Phone Number

* ETL Pipeline Development: Designed, implemented, and maintained over 30 scalable ETL pipelines using Azure Data Factory, SQL, and Python to process large datasets, ensuring high data integrity and performance.* Data Integration: Centralized diverse data sources (SFTP, Flat File, Excel, structured and unstructured) into a data warehouse, improving data accessibility for business intelligence and analytics teams.* Performance Optimization: Enhanced data processing accuracy and efficiency through performance tuning, indexing strategies, and optimized query designs, leading to faster and more reliable data retrieval.* Advanced SQL Techniques: Employed advanced SQL techniques including joins, subqueries, CTEs, UDFs, stored procedures, and views to test data loading processes and fulfill ad-hoc data requests from business departments.* Pipeline Monitoring & Troubleshooting: Monitored and troubleshot data pipelines for over 20 programs, ensuring accurate and timely data loads for reporting, actuarial, and financial analysis.* Industry Expertise: Applied domain knowledge in various types of insurance (property, auto, catastrophe) to develop industry-specific data solutions.* Requirements Gathering & Collaboration: Partnered with product, engineering, and data analysis teams to define data requirements, build data-driven features, and optimize database performance.* CI/CD & Project Management: Utilized CI/CD tools such as Azure DevOps and GitHub for version control, code review, and efficient task management. Proficient in using DevOps boards and Jira for tracking and organizing daily tasks.

OLAP Database Design: Assisted senior developers in designing a new OLAP database to store 5 years of historical data. Contributed to normalization, data manipulation, and the design of logical, physical, and conceptual data models.Data Quality Assurance: Implemented data quality checks and validation routines, significantly reducing errors and maintaining high standards of data integrity across all processes.Database Schema Development: Developed database schema and objects, including 10 dimension tables and 5 fact tables, for data migration projects. Responsible for performance tuning and error handling to ensure optimal database performance.SSIS Package Development: Created over 35 SSIS packages to implement data migration, integrating source data from more than 50 OLTP tables. Utilized various SSIS transformations (conditional split, derived column, fuzzy lookup) to perform data cleaning and validation for approximately 80GB of raw records before the initial load.Incremental Loading Optimization: Optimized the incremental loading process for 600+ daily transactions by developing complex parent-child packages, improving performance by 30%.Unit Testing & Data Quality Assurance: Conducted unit testing by breaking data pipelines into separate units and implementing dirty sample data to verify data quality. Ensured data integrity throughout the migration process.Business Intelligence & Dashboard Development: Collaborated with business owners to understand key KPIs and translated business needs into interactive Power BI dashboards, enhancing operational efficiency for the client.Data Preparation & Collaboration: Performed data cleaning and preprocessing to prepare datasets for analysis and reporting. Worked closely with data analysts and stakeholders to define data requirements and provide clean, reliable data for analysis.