SQL and ETL optimization for DWH Developers

SQL and ETL Optimization for DWH Developers
Intended for SQL Developers, DW Developers
Prerequisites Oracle SQL
Duration 1 day
Code EDU_007

Summary

Long-running SQL statements and ETL jobs represent a big problem in production databases. They cause problems like slow response times, database performance issues, and ETL and reporting delay.

A goal of this education is to learn how to optimize SQL statements and ETL jobs and avoid these problems. To reach the goal we need to know how the database works and we must be familiar with different database components and options like partitioning, indexes, analytics functions, database statistics, etc. Then we can apply SQL and ETL optimization techniques and design efficient SQL statements and ETL jobs.

Education is organized as a lecture where optimization techniques are demonstrated on live demo.

Who is the target audience?

  • SQL & ETL Developers
  • Private company employees
  • Those who want to develop themselves in Database, Data Warehouse, and Business Intelligence subjects
  • Data Scientists
  • Those who want to learn more about SQL and ETL optimization and ¬≠develop themselves in this subject

 

What Will You Learn?

  • Basic database architecture
  • What happens in the database when you issue DDL and DML statement
  • Different database option useful for SQL optimization
  • Understand the Execution Plan
  • SQL and ETL optimization technique
  • ETL optimization phases

 

Required Prerequisites

  • Basic knowledge of database, SQL and ETL

 

Agenda

 

Database Basics for SQL and ETL Optimization

Basic database components

How database components work

What happens when DDL is executed

What happens when DML is executed

 

SQL Optimization

SQL Optimization Goals

The Execution Plan Basics

Understanding the Execution Plan

Different Query Types demand different approaches

Why is Database Statistics so important

Importance of Reducing IO

Partitioning

Parallel processing

SQL Hints

Direct Path Load vs Conventional Load

Analytic functions

Indexes

 

ETL Optimization

Temporary tables in ETL jobs

Aggregate tables in ETL jobs

ETL optimization phases