How to create Fact and Dimension tables from denormalized raw data

In data warehousing world there are occasions where developers have to reverse engineer model from flat csv files. This article explains the process with a simple example.

Source: CSV with 5 rows & 7 columns
Database : PostgreSQL
Hosted on : AWS RDS Free Tier

Requirements : psql cli (more info)

Photo by Jan Antonin Kolar on Unsplash

Sample Data

Step 1 : Preview the data

Mac / Linux users can use cat or head command to preview the data

$ cat sampleData.csv | more
$ head sampleData.csv

Windows users can Excel or

c:\samplefolder> type sampleData.csv | more

Step 2 : Login to Postgresql using psql

$ psql -h host-or-servername -d postgres -U yourusername -W
-W is needed as it enforces to enter password

Step 3 : Create Database, Schema & Rawdata table