Write My Paper Button

WhatsApp Widget

The Extract, Transform, Load (ETL) process plays a pivotal role in modern data management, especially in sectors where real-time data analysis and reporting are crucial. The banking sector, with its complex data streams

Assessment Task

Introduction

The Extract, Transform, Load (ETL) process plays a pivotal role in modern data management, especially in sectors where real-time data analysis and reporting are crucial. The banking sector, with its complex data streams and regulatory requirements, is one such domain where ETL can significantly enhance efficiency and compliance.

 

This assessment focuses on a case study involving the ETL process in the context of banking liquidity reporting in Indonesia. The case study explores how banks maintain, calculate, and monitor liquidity using the Liquidity Coverage Ratio (LCR) indicator and how the ETL process can be leveraged to automate these tasks.

 

Scenario: ETL Process in Banking Liquidity Reporting

 

Background

Financial regulators across the globe require banks to maintain specific liquidity levels to ensure financial stability and protect consumer interests. The Liquidity Coverage Ratio (LCR) is a key measure used to assess a bank's ability to meet its short-term obligations using its most liquid assets. In the rapidly evolving financial landscape, banks must navigate complex regulatory frameworks, diverse data sources, and stringent timelines. Automation through the ETL process has emerged as a vital solution, facilitating real-time reporting, enhancing data accuracy, and ensuring timely compliance with various international and domestic regulatory bodies.

 

Problem Statement

Despite the importance of the LCR in banking regulation, many banks face challenges in efficiently calculating and reporting this critical metric. Manual processes, disparate data sources, lack of standardization, and the complexity of regulatory requirements lead to delays, inaccuracies, and inefficiencies. Banks are under pressure to adapt to dynamic regulatory environments while maintaining transparency and accuracy in reporting. The existing manual and semi-automated systems often fall short in providing a scalable, robust, and responsive solution, leading to increased risks and operational costs.

 

 

 

 

 

The ETL Solution

The case study proposes an innovative ETL solution tailored to the banking context in Indonesia, designed to overcome the challenges associated with LCR reporting. This comprehensive solution extracts data from various sources, including transaction logs, balance sheets, and external regulatory databases, transforming it into the required format and loading it into a centralized reporting system. The automation not only saves time but also improves accuracy and facilitates real-time monitoring and compliance. The proposed ETL process leverages cutting-edge data integration techniques, employs advanced data validation protocols, and aligns with the specific regulatory requirements of the Indonesian banking sector. By streamlining data flow and enhancing analytical capabilities, this ETL solution contributes to informed decision-making and robust risk management within the banking industry.

 

Part 1: Extract, Transform, and Load (ETL) Process: (50 marks)

 

·       Design an ETL Process: Based on the case study and provided data set, design an ETL process that could be implemented in a real-world banking scenario.

·       Evaluate the ETL Process: Critically assess the ETL process, identify potential limitations, and propose enhancements or alternatives.

·       Using the case study provided in the moodle, discuss the relationships between entities and their cardinalities e.g one-to-one, one-to-many, many-to-many, and optional or mandatory.

·       Use app.diagrams.net tool to draw the conceptual Model entity relationship diagram (ER-Diagram)

·       Implement the ETL Process: Write SQL queries to extract, transform, and load data as per the designed process.

 

Part 2: Normalization: (30 marks)

·       Apply Normalization Techniques: Using the provided data set, apply normalization techniques to optimize the database structure.

·       Analyze Normalized Data: Interpret the normalized data and provide insights into banking liquidity trends.

 

 

 

 

 

 

 

 

 

Assessment Details

 

Part 1: Extract, Transform, and Load (ETL) Process

ER Diagram (20 points)

a.     Based on the case study, develop the Conceptual Model Enhanced Entity-Relationship diagram using app.diagrams.net tool

b.     Discuss the relationships between entities and their cardinalities e.g one-to-one, one-to-many etc, state all your assumptions.

 

Data Extraction (15 points)

Write SQL queries to extract the following information:

a. List of all banks regulated by "Regulator 1."

b. Total liquidity of "Bank A" for January 2021.

 

Data Transformation (20 points)

 

Write SQL queries to transform the extracted data into the following formats:

a. A summary report showing the average LCR for each regulator.

b. A monthly trend of total transactions (Deposit and Withdrawal) for each bank.

 

Data Loading (15 points)

 

Assume you have a new table Regulator_Summary with columns Regulator and Average_LCR. Write an SQL query to load the transformed data from Exercise 2a into this table.

 

Part 2: Normalization

 

Scenario: Using the provided tables (find below), apply normalization techniques to optimize the database structure.

Bank Reports Table

 

ReportID

BankID

ReportDate

LCR

TotalLiquidity

TotalAssets

1

101

2021-01-31

105.2

1000000

1500000

2

102

2021-01-31

98.7

800000

1200000

3

101

2021-02-28

102.5

950000

1400000

4

103

2021-01-31

99.9

700000

1000000

 

 

 

Banks Table

 

BankID

BankName

Location

Regulator

101

Bank A

City X

Regulator 1

102

Bank B

City Y

Regulator 1

103

Bank C

City Z

Regulator 2

Transactions Table

 

TransactionID

BankID

TransactionDate

TransactionType

Amount

201

101

2021-01-15

Deposit

50000

202

101

2021-01-20

Withdrawal

30000

203

102

2021-01-10

Deposit

40000

204

103

2021-01-05

Deposit

25000

205

101

2021-02-01

Withdrawal

20000

 

First Normal Form (1NF) (5 points)

Convert the Bank_Reports table into First Normal Form (1NF). Explain the steps you have taken.

 

Second Normal Form (2NF) (5 points)

Further normalize the table(s) from Exercise 4 into Second Normal Form (2NF). Explain the steps you have taken.

 

 Third Normal Form (3NF) (10 points)

Further normalize the table(s) from Exercise 5 into Third Normal Form (3NF). Explain the steps you have taken.

 

Interpretation and Analysis (10 points)

Interpret the normalized data and provide insights into the banks' liquidity trends.

Propose a potential use case for the ETL process in improving banking regulations.

 

 

 

 

 

Assessment Format and Submission Guidelines

 

Format: This assessment should be presented as a formal report, focusing on the analysis and design of SQL queries related to the banking scenario. Please adhere to the following guidelines:

  • Structure: Include an introduction, main body (divided into appropriate sections and subsections for the ETL process and normalization), conclusion, and references.

 

  • SQL Queries Section: Include a dedicated section for the SQL queries:

 

    • Write SQL queries to extract the required information as specified in the assessment.
    • Provide explanations for each query, detailing how it meets the specific requirements (e.g., listing banks regulated by "Regulator 1" or calculating total liquidity for "Bank A").
    • Include comments within the SQL code if necessary to explain complex logic.

 

  • Word Limit: The report should be between 1500-2000 words, excluding SQL queries, tables, and references (Harvard Referencing).

 

 

 

 

 

 

 

 

 

 

 

Marking Rubric

 

Criteria (Weight%)

HD (85-100%)

D (75-84%)

C (65-74%)

P (50-64%)

F (0-49%)

Part 1: Extract, Transform, and Load (ETL) Process

 

 

 

 

 

ER-Diagram 20 “(points)

The student demonstrates mastery in developing the Conceptual Model Enhanced Entity-Relationship diagram using app.diagrams.net tool and discusses the relationships between entities and their cardinalities

The student shows proficiency in developing the Conceptual Model Enhanced Entity-Relationship diagram using app.diagrams.net tool and discusses the relationships between entities and their cardinalities

The student exhibits basic skills in developing the Conceptual Model Enhanced Entity-Relationship diagram using app.diagrams.net tool and discusses the relationships between entities and their cardinalities

The student struggles with developing the Conceptual Model Enhanced Entity-Relationship diagram using app.diagrams.net tool and discusses the relationships between entities and their cardinalities

Inadequate understanding in ER-Diagram and in discussing relationships between entities and their cardinalities

Data Extraction (15 points)

The student demonstrates mastery in data extraction, accurately crafting queries and analyzing data.

The student shows proficiency in data extraction, with minor areas for improvement in queries and analysis.

The student exhibits basic skills in data extraction, with some errors in queries and analysis.

The student struggles with data extraction, showing significant errors in queries and limited analysis.

Inadequate understanding and execution of data extraction.