Jeffrey Zhao, Software Developer in Seattle, WA, United States
Jeffrey Zhao

Software Developer in Seattle, WA, United States

Member since February 24, 2018
As a senior professional with over 15 years working in the IT field, Jeffrey has in-depth technical knowledge and hands-on experience in the design and implementation of BI, data warehouses, ETL solutions, and data pipelines to satisfy challenging requirements. He's an expert in flexibly customizing ETL solutions with a variety of BI tools/coding languages such as Python, SQL, PostgreSQL, SSIS/SSAS, OBIEE/OBIA, Informatica, Java, and more.
Jeffrey is now available for hire




Seattle, WA, United States



Preferred Environment

Linux, Eclipse, Jupyter Notebook, Informatica

The most amazing...

...thing I’ve built was an ETL framework for a highly custom Java app which was used to load data from multiple source systems to an SAS dataset.


  • Senior Data Engineer

    2019 - 2020
    • Designed and implemented data pipelines upon dataset from data lake into redshift warehouse using python and sql;
    • Created batch and incremental process to refresh data in data warehouse through airflow;
    • Designed and implemented quarterly report with Jupyter Notebook;
    • Designed and implemented ETL processes with AWS data pipeline to load data from S3 into data warehouse;
    • Implemented PySpark scripts to analyze server login attempt log on a spark cluster;
    • Created monitoring functions with AWS Lambda to automatically detect changes of files in S3 folders.
    Technologies: Python, PostgreSQL, AWS, Lambda, Redshift, Spark
  • data engineer

    2017 - 2018
    • Design and implement data pipeline to integrate multiple data sources into data warehouse under Django framework;
    • Synchronize and clean data warehouse through complex sql and python scripts;
    • Provided suggestions about improvements in data capture in the source systems;
    • Designed and implemented reports with sql in metabase;
    • Designed and implemented synchronize framework with python and sql;
    Technologies: python, django, postgresql
  • BI Solutions Architect

    2015 - 2017
    Absolute Security, Inc.
    • Developed a BI strategy roadmap aligned with the core business objectives.
    • Designed and implemented a new DW/BI system that integrated data from different source system including cloud data sources such as NetSuite and Salesforce, a local finance database, and a data lake.
    • Designed and implemented Tableau workbooks that sourced Salesforce data.
    • Designed an ETL framework which involved historical and delta loading, SCDs, batch processes, performance analyses, data quality, and profiling.
    Technologies: SQL Server 2016, SSIS/SSAS/SSRS, Dell Boomi, NetSuite, Salesforce, Java, Python, PL/SQL, Tableau, PostgreSQL, Spring, Sesame Solution
  • ETL Consultant

    2015 - 2015
    BC Hydro
    • Created and supported an ETL solution; including the design, build/coding, unit testing, and review for SMD reporting modules from/to PostgreSQL/Greenplum databases.
    • Implemented a Hadoop data lake to help resolve data retention for large data sets and complex issues to provide users with access to large data sets for business intelligence/analytic purposes.
    Technologies: Java, JSF, PostgreSQL, Oracle PL/SQL, Greenplum, PostgreSQL, Informatica, Hadoop, Spring Batch
  • Senior BI Consultant

    2013 - 2015
    • Led the technical side in the implementation of data warehouse solutions for various ATCO member companies covering data modeling/architecture, data warehouse standards, data quality management, data provisioning and cleansing, data analysis, data migration, ETL process/specification design/implementation, BI best practice promotion, and the design of the BI infrastructure.
    • Worked with the business managers and end users to understand and collect business requirements.
    • Customized an Oracle PO ETL.
    • Designed and implemented customized ETL data models and processes for an Oracle purchase order performance module to replace seeded OBIA PO module and deployed to production.
    • Managed and wrote the reports build/coding and demo using BI Answers and BI Publisher.
    • Monitored production ETL processes; helped to diagnose and resolved pre-existing mapping or performance issues and review designs for optimal ETL load times and report execution times.
    • Worked with the DBA team to troubleshoot issues between development, testing, and production.
    • Implemented and ensured adherence to change control/migration procedures (from development to testing to production).
    • Worked on invoice projects and a subproject (AE invoices and Hyperion planning project).
    • Collected requirements from the client directly and prepared requirement specification documents for final review and approval.
    • Developed a project plan and time estimation with the project manager.
    • Prepared architect design documents and the initial proof of concept for new solutions.
    • Coached junior developers.
    • Designed and implemented BI object security at the category level to allow users to access different category folders based on individual roles.
    • Architected and redesigned the infrastructure layer of the invoice project hosting multiple member companies (distribution, transmission, and more) on BI server where each member company owns a separate invoice ETL loading process while the presentation layer can be hosted on the same OBIEE front-end website. The final implementation significantly improved the performance (reduce the ETL executing time from 45 minutes per day to 2 minutes per day in average) and increased the capacity of the support team to be able to hold more projects for other member companies.
    • Architected and designed BI Publisher to access to various data sources so that the reports created in BI Publisher can be embedded into company content management system to allow user access to all the information from just one spot.
    • Designed and developed a star schema model in OBIEE for a budget project allowing users to drill down and analyze info through multiple dimensions/levels such as time, department, category, project, task, and more.
    • Provided data models and data maps (extract, transform and load analysis) of the data mart and feeder systems in the aggregation effort. Also reversed engineered the data structures of the reporting system.
    • Designed and developed the OBIEE answers analysis and dashboards including, graphs and charts.
    • Directed a team in developing a new budget fact star schema and join with existing dimensions of actual (cost center, service, period, and more) with OBIEE admin tool so that actual and budget data can be analyzed side by side at the front-end analysis tool (answer).
    • Worked on a BI reporting integration project where I collected user requirements via a JAD session to understand business expectations of a new powerful reporting tool.
    • Prepared requirement specification documents and wrote architect design documents.
    • Evaluated the BI reporting tools and delivered proof of concept projects.
    • Designed and implemented the BI Publisher Security Model architecture and integrated BI Publisher with a role-based security model into a WebLogic security model using an external LDAP server for authentication.
    • Managed BI-object security at different levels (row level, column level, and object level) in the OBIEE repository through initial blocks and customized tables.
    • Configured the data source connections in BI Publisher to authorize users who have access to different resources (Oracle, SQL Server, OBIEE analytics, Excel, flat file in certain folders with limited access, XML feed, and more) to generate and consume reports.
    • Architected and designed BI Publisher so that a user can access various data sources so that reports created in BI Publisher can be embed objects into the company content management system to allow user to access to information from one site.
    • Designed and implemented a metadata repository providing a centralized information source for the data models, data maps, processes, documents, contact lists, project calendars and issues.
    • Designed and developed self-service objects in Oracle Discoverer to allow business users create reports by themselves.
    • Developed SQLs to query and manage Informatica metadata.
    • Created a Java application to integrate these SQLs for functions like object tracing and object impact analysis.
    • Designed and implemented a data-quality-management framework as the foundation for CIS prototype project.
    • Designed the CIS data model, a metadata repository providing mappings between all logical models, physical models, systems, databases, processes, procedures, organizations, people, projects, and activities.
    • Managed and was responsible for the ETL and reporting solution design, build/coding, unit testing, and review for CIS reporting modules and Oracle EBS modules for financial, project, and procurement analytics.
    • Enhanced, developed, and tested the BI reports/dashboards and RPD BI layer.
    • Developed an OBIEE merge procedure and standard for the team to standardize the workflow of OBIEE repository migration with lower cost and better efficiency.
    • Promoted BI standards and best practice across the supporting team and provided training for the team.
    • Designed and implemented SQL scripts to test daily incremental loading ETL workflow of OBIA.OBIA and OBIEE Upgrade.
    • Assisted in the migration of OBIA from to and OBIEE 10g to 11g.
    • Designed auto-testing solutions for the upgrading OBIA system with a high efficiency and in a low-cost manner.
    • Performed an analysis of the latest patches of OBIA after an upgrade and applied mandatory patches partially based on business requirement.
    • Handled the direct energy transition data migration by designing and implementing ETL processes to extract and convert a huge volume of records from the mainframe system to SAP system with high performance within a limited time-window, based on business requirements.
    Technologies: Java, JSF, TOAD, OBIEE (Procurement, Finance Analytics)/OBIA, Informatica, PL/SQL, DAC, Hyperion, Oracle EBS
  • ETL/Reporting Consultant

    2011 - 2013
    Flint Energy Services Ltd
    • Designed data models and implemented SSIS/SSRS solutions in the SmartPlant Material system.
    • Worked with DBA in the configuration and administration of designing and developing the ETL process of a data mart project utilizing the Microsoft BI solution;
    • Performed ETL design to ensure that the data transformations, data cleansing, and exception reporting were created according to specifications.
    • Provided training and guidance to the internal technical staff.
    Technologies: Crystal Report, SSAS, SSIS, SSRS, SQL
  • ETL Consultant

    2006 - 2011
    • Worked for Alberta Finance in the role of an Oracle Data Warehouse lead developer.
    • Designed, developed, and implemented Oracle 10g Data Warehouse for Alberta Finance (a strategic information reporting system). SIRS, the very first BI project of Alberta Finance all the ETL processes had to load data from an Oracle transaction system to an Oracle data warehouse and create derived OLAP cubes. The presentation layer includes both OLAP reports and relational reports and utilizes Oracle portal to display them.
    • Designed and implemented a summary reports module with Jasper Report component and DB2 for a mental health reporting system on J2EE Application Server (Websphere 6.1); done on behalf of Alberta Health & Wellness.
    • Created standard BI modules with Java against Db2 database based on non-functional requirement. Defined the conventions, standards, and migration strategy with best practice and mature BI methodology for BI project in AH&W.
    • Designed and implemented ETL Foundation components which provides a framework for executing SAS (and non-SAS) ETL processes including functions and capabilities such as managing the execution of load streams and jobs, collection of command execution metadata (Foundation Operational Metadata) which includes overall stream execution status and error/warning details, common error handling and common recovery via automated rollback and more.
    • Designed, developed and implemented ACCIS's (Alberta Continuing Care Information System) initial release involving Spring, Hibernate, Struts, and EJBs on a J2EE Application Server (WebSphere 6.1) against a Db2 database in an AIX environment.
    • Interpreted business needs and translated them into data and processes that could be automated, including load processes and file maintenance processes.
    • Designed, developed, and implemented the back-end and batch processes using Java and XML.
    • Designed parsing components with Perl script to extract and convert specific format data into relational records and load them into DB2 database; done for AH&W BI pharmaceutical information network usage activity reporting.
    • Designed and implemented ETL processes with SAS Data Integration Studio. Integrated all ETL processed with ETL foundation framework to load data into BIE data warehouse. All done for AH&W BI information products.
    • Provisioned changes to the DSR System in order to meet the business, technical, and operational requirements; done on behalf of AH&W Delivery Site Registry System (DSR).
    Technologies: Oracle Business Intelligent Suite, Oracle Warehouse Builder, Java, Oracle Discoverer, Struts, Java, Tomcat, Hibernate, JasperReport, IBM WebSphere, DB2, AIX
  • Software Engineer

    2004 - 2006
    Betcorp, Inc.
    • Designed the application architecture for a back-end management system used by customer care department to track services provided to customers.
    • Designed data models, process models, and workflow models of existing processes and the proposed solutions using best practice techniques and industry standard process modeling tools.
    Technologies: C#, SSAS, SSIS, SSRS, SQL
  • Java/ETL Developer

    2003 - 2003
    eHealthInsurance, Inc.
    • Designed and developed a revenue recognition system project using Java, Tomcat, JSP, Java Servlet, and Oracle.
    • Designed and implemented ETL components for an RRS system in Java which could automatically upload all kinds of data formats (text, Access, Excel to relational database, and vice versa).
    Technologies: Java, SQL, Strut, Tomcat, Oracle
  • Java Developer

    1999 - 2002
    Alcatel Network Support Systems, Inc.
    • Developed a telecom management information system.
    • Implemented a mobile communication expenses calculation system.
    Technologies: Oracle, Java


  • Online Donation Analysis Tool (Development)

    Per the client's request, I built an online donation analysis tool which will extract donation information from multiple donation sites and present the aggregated data in AWS Redshift through Tableau. The ETL processes are created through AWS Lambda function which saves client's great efforts and costs of setting up and operating a separate ETL server.

  • Absolute Data Warehouse (Development)

    Absolute's data warehouse had to be replaced with a new DW system as Absolute’s ERP system and CRM system were being moved to the cloud.

  • ETL Foundation Components (Development)

    I designed and implemented ETL Foundation components which provides a framework for executing SAS (and non-SAS) ETL processes including functions and capabilities such as managing the execution of load streams and jobs, collection of command execution metadata (foundation operational metadata) which includes overall stream execution status and error/warning details, common error handling and common recovery via automated rollback, and more.

  • OBIA and OBIEE Upgrade (Development)

    I completed the migration of OBIA from to and OBIEE 10g to 11g.

  • Informatica Metadata Analysis (Other amazing things)

    I developed SQLs to query and manage Informatica metadata; implemented the Java application to integrate these SQLs for functions like lineage analysis and impact analysis.


  • Languages

    SQL, Python, Java
  • Tools

    SQL Server BI, Oracle Business Intelligence Enterprise Edition 11g (OBIEE), Informatica ETL, Tableau, SAS Data Integration (DI) Studio, AWS CLI, Oracle Designer, Oracle Warehouse Builder (OWB), Erwin, IBM Cognos
  • Storage

    PostgreSQL, SQL Server Integration Services (SSIS), AWS Data Pipeline Service, AWS S3, Dell Boomi, SAS SQL
  • Paradigms

    Agile Software Development
  • Platforms

    AWS Lambda, Amazon Web Services (AWS), Oracle Data Integrator (ODI), Salesforce
  • Frameworks

    Hadoop, Spark, Django
  • Libraries/APIs

    Salesforce API, jQuery
  • Other



  • Certificate in Applied Data Science with Python
    2017 - 2018
    University of Michigan - Ann Arbor, MI, USA
  • Master's degree in Engineering Management
    2006 - 2009
    University of Alberta - Edmonton, Canada
  • Certificate in Data Warehouse
    2006 - 2006
    Kimball University - Seattle, WA, USA
  • Bachlor's degree in Electronic Engineering
    1995 - 1999
    Shanghai Jiaotong University - Shanghai, China


  • Applied Data Science with Python
    APRIL 2018 - PRESENT
    University of Michigan via Coursera
  • ITIL Foundation Certificate in IT Service Management
    JUNE 2015 - PRESENT
  • Oracle Business Intelligence Foundation Certified Implementation Specialist
    Oracle University
  • CFA (Chartered Financial Analyst) Level I
    CFA Institute
  • Oracle Certified Professional Database Admin (Oracle 10g OCP)
    MAY 2007 - PRESENT
    Oracle University
  • Microsoft Certified Technology Specialist for SQL Server
    APRIL 2005 - PRESENT
  • Sun Microsystems Certified Java Programmer

To view more profiles

Join Toptal
Share it with others