EXPENSE PROJECT
This is a SQL ETL project that takes raw expense records from various credit cards and produces a final table that can be used for analysis by power BI or tableau.
The raw data originates in a series of CSV files that are imported into simple SQL Server raw tables. The SQL code builds the interim tables, cleans and categorises the data and creates a final table.
The SQL code could just be run as scripts but some of the code is compute heavy and takes some time. So those routines benefit from having been compiled into stored procedures. So for consistency of approach, all the steps will be compiled into stored procedures.
The data is then analysed with Power BI visuals - but the Power BI graphs and data are confidential and can't be shown
The raw data originates in a series of CSV files that are imported into simple SQL Server raw tables. The SQL code builds the interim tables, cleans and categorises the data and creates a final table.
The SQL code could just be run as scripts but some of the code is compute heavy and takes some time. So those routines benefit from having been compiled into stored procedures. So for consistency of approach, all the steps will be compiled into stored procedures.
The data is then analysed with Power BI visuals - but the Power BI graphs and data are confidential and can't be shown
SQL ETL STEPS
STEP 1 - Create the table structures needed for the interim steps and the final table.
| expense_phase01_create_sps_step01_build_tables.sql |
STEP 2 - Import the raw data files and aggregate it into interim tables for each card.
| expense_phase01_create_sps_step02_fill_tables.sql |
STEP 3 - REMOVE TRANSACTIONS THAT ARE NOT RELEVANT - THIS DIFFERS BY CARD
| expense_phase01_create_sps_step03_clean_tables.sql |
STEP 4 - COMBINE CLEANED TABLES
| expense_phase01_create_sps_step04_combine_tables.sql |
STEP 5 - CATEGORISE TRANSACTIONS BASED ON TRANSACTION DESCRIPTION
| expense_phase01_create_sps_step05_categorise_tables.sql |
STEP 6 - MASTER SCRIPT THAT RUNS THE PROCEDURES AND BUILDS FINAL TABLE FOR VISUALS
| expense_phase02_run_sps_step01.sql |
