This is a collection of the basic fundementals of data warehouseing and ELT architecture I've learned. This repo showcases some schema and SCD assignments I've completed as well as a 'cap stone' project I took the initiative of completing. All of the schemas here were made using draw.io
This is a project I decided to do so I can put the fundementals I learned on display. I decided to do a sales order transaction fact table (within the scope of manufacturing domain) as it reflects something I see day to day. I chose to create a snowflake schema because I work with AS400 quite a bit and I feel like a snowflake schema can better suit this type of program which relies on various methods of reflecting/streaming data, both up and downstream.
Below is the fact table. It's fed from 5 dimension tables and it's purpose is to show the customer, product and other important details that would be included on a business invoice.
- Customer Dim
- Date Dim
- Product Dim
- Freight Dim
- Taxes Dim
All dimensions posess a natural key and a surrogate key. The SKs function as the PK for their own dimension and FK for tables it feeds into.
Note
Some dimensions would benefit from record keeping IE: frieght cost (freight dimension) or the price of raw materials for components (product dimension). Those details were not included in this project as I was trying to focus more on the schema as a whole while still including a bit of info like the attributes of the records.
Outside of developing the actual fact table, the entire branch of the product_Dim was the most challenging part.
The product dimension (and associated dims) needed to be done from a manufacturing-first point of view. Products don't come from nothing. They are made using various components. Those components are created from their own raw materials or other components. On top of all that, they have their own list and retail prices, as well as dimensions. While building this portion of the schema out, I had to constantly modify which dims certain records where placed, often finding I had to create a whole new dim for them. Overall, even though this was the most challenging part, it was also the most fun part.
When designing the schema, I tried to include as many branching dims as possible that would also help facilitate better data driven decisions downstream for multiple departments within an organization. For instance, sales and marketing departments can benefit the most from the showcased dimensions, however, product teams can deep dive into the costing or complexity of components; while the logistics department can monitor fluctuations in freight charges year over year.
Below is a basic transaction table for a students meal card spending. I decided to use a star schema as this was fairly straight forward.
This is a snapshot fact table to see what papers have been published for members of a university faculty. I decided to try a snowflake schema because to me it made sense that publications would be directly related to their authors (faculty) dim tables.
Lastly, this is a basic accumulating snapshot fact table showing when grants were paid to sudents.
Here are two solutions for possible user access layers. The maine warehouse takes data from multiple campuses and displays them 2 ways. The first by campuses and the second by amalgamation of records from all campuses.
Below shows basic knowledge of understanding attributes and the need for consistant data types and governance during the transformation process of an ETL pipeline.
This is a link to a little assignment I did to demonstrate understanding of Type 1 and 2 SCD. This table shows information of faculty members. 'highest degree' can be changed in place, while a record of a faculty member's 'rank' has to be kept for historical analysis. I used excel as I thought this was the easiest way to showcase changes being made to a table.