It's Autumn! Prices fall like leaves! - hours only!Up to 80% off on all courses and bundles.-Close
Custom logging
Loading Customers
6. Implement the ETL process
Subscriptions
Congratulations

Instruction

Great! Since we have implemented the custom logging function and already know the structure of the tables we'll be working with, we can move on the next exercise, where we'll implement a new ETL process: loading new customers.

Exercise

Implement a new ETL process that loads customers from the staging area (the stg_customer table) into the dim_customer table.

The new ETL process will be encapsulated in a function named load_customers() that takes no parameters and performs the following steps:

  1. Logs a new message stating that the ETL process started. Using the PERFORM command and log_message() function, it should pass the following arguments:

    Parameter Value
    p_process_name 'load_customers()'
    p_message_text 'ETL process Load Customers Started'
    p_level 'info'
  2. If there is a record for which stg_customer.id and dim_customer.id match, the function should update that existing record in the dim_customer table with the new data coming from the stg_customer table.
  3. Logs a new message stating how many customers have been updated. Using the PERFORM command and log_message() function, pass in the following arguments:

    Parameter Value
    p_process_name 'load_customers()'
    p_message_text 'Updated Customers: {x}'
    p_level 'info'

    Where {x} is the number of updated customers.

  4. For each row where stg_customer.id column is NULL, the function should insert a new record into the dim_customer table.
  5. Logs a new message stating how many new customers have been inserted. Using the PERFORM command and log_message() function, pass in the following arguments:

    Parameter Value
    p_process_name 'load_customers()'
    p_message_text 'Inserted Customers: {x}'
    p_level 'info'

    Where {x} is the number count of inserted customers.

  6. Removes all rows from the stg_customer table.
  7. Logs a new message saying stating that the ETL process finished. Using the PERFORM command and function log_message() function and pass in the following arguments:

    Parameter Value
    p_process_name 'load_customers()'
    p_message_text 'ETL process Load Customers Finished'
    p_level 'info'

Note: The text values in all three columns (title, first_name, last_name) that you're updating/inserting in the dim_customer table should have an initial capital letter.

To retrieve the number of inserted/updated rows, use:

GET DIAGNOSTICS your_variable = ROW_COUNT;

Stuck? Here's a hint!

Use the built-in INITCAP() function to force the first letter of a word to be uppercase.