## create database
postgres=> create database sample
postgres=> \c sample
## it will prompt you to enter Postgres login password
## create schemas
sample=> create schema landing;
sample=> create schema dim;
sample=> create schema fact;
## create tables
sample=> create table landing.rawdata (
    name varchar(100)
    ,gender varchar(20)
    ,profession varchar(50)
    ,state varchar(2)
    ,asofdate date
    ,temperature float
    ,pulse int
);
## Import CSV data into Postgres table
sample=> \COPY landing.rawdata FROM '/yourpath/sampleData.csv' DELIMITER ',' CSV HEADER;
## Add a surrogate ID column (surrogate column means, column with sequence of numbers, generally auto generated).
sample=> alter table landing.rawdata add id serial;
## create dimension tables
## Fisrt Query
create table dim.gender as 
select 
    row_number() Over(order by gender) as genderid
    ,gender 
from 
    (select distinct gender from landing.rawdata) t;
## Second Query
create table dim.person as
select 
    row_number() Over(order by name) as personid
   ,name 
from 
    (select distinct name from landing.rawdata) t;
## Third Query
create table dim.profession as
select 
    row_number() Over(order by profession) as professionid
    ,profession 
from 
    (select distinct profession from landing.rawdata) t;
## Fourth Query
create table dim.state as 
select 
    row_number() Over(order by state) as stateid
    ,state 
from 
    (select distinct state from landing.rawdata) t;   
## Build Fact table based on IDs from Dimension Table.
create table fact.user
as
select
    r.id
    ,p.personid
    ,g.genderid
    ,pr.professionID
    ,s.stateID
    ,r.asofdate
    ,r.temperature
    ,r.pulse
from
    landing.rawdata r
    JOIN dim.person as p on r.name = p.name
    JOIN dim.gender as g on r.gender = g.gender
    JOIN dim.profession as pr on r.profession = pr.profession
    JOIN dim.state as s on r.state = s.state;