API>local_db>AWS_RDS

Disclaimer! All data used is for educational purposes only.

ETL pipeline diagram.

Aim of project

By creating a fully working pipeline:

• Familiarise with ETL
  
• Improve python (API,pandas), SQL (triggers&procedures) knowledge
  
• Work with a cloud storage service
  

What does it do?

The data used is electricity prices and weather conditions. The pipeline is fully autonomous: scheduled daily (crontab), electricity price data (.xls) is dowloaded, weather data fetched via an API, and inserted into a local database (Postgres). It is then cleaned and transferred (PL/pgSQL) into 3NF-tables (see ERDs below). Lastly, the clean useful data is migrated to Amazon Web Services' RDS remote database via the foreign-fata wrapper in PL/pgSQL.

Price data ERD.

Weather data ERD

Further improvements/learnings

• Switch from time to event-based triggers
  
• Upload data in batches, not 'for each row'
  
• Prevent SQL injection