Database engineer to set up living cloud database and api for (near-)real time data delivery
Budget: €1,500 – €3,000 EUR
What I am trying to build (NOTE: this is the entire vision, not all included in the project):
I have been trying to build the infrastructure to do academic research on a large volume of economic time-series data. The research would be both nowcasting of economic health and analyses of historic data. So for this, we would need to have a database that is constantly kept up-to-date from different data-sources and that can serve (near-)real time data to researchers. Additionally, we want to be able to integrate the analytics and models that we develop into the API to serve up the results of the models in a timely manner.
I am currently setting up this research stream with a very small team but we would already like to make it scalable, and affordable for the long-term.
Eventually I would like to add a front-end so that I can provide access to the data and our model-outputs to less technical researchers. This does not need to happen in this project but the foundations should be built with this in mind.
High-level idea of the needed elements (not rigid on this and open to suggestions):
- a postgresql database that holds very recent data (e.g. last 30 days only) that would be used to stream data for nowcasting. Hosting this on gcp seems to be the best option.
- a BigQuery database that would hold the full archive. This database would be used to serve up data for macro-analyses that are not as time-sensitive as the nowcasting.
- a FastAPI (or other python-based API framework) built to expose the data and model outputs through both RESTful and websocket api end-points.
- a caching solution to decrease latency of api-calls and reduce load on the database(s).
- a solution to handle situations where multiple models need to run on the data in the background to generate and save their outputs without generating a bottle-neck on the client-side.
Work that has already been completed:
- Data extraction scripts in Python for retrieval of the data from the target sources
- 800+gb of raw data retrieved in Parquet format
- data cleaning scripts in python
- SQL database schema draft
- First draft of the API written in Fast API
Work that needs to be completed:
Phase 1
- Tell me your hobby so I know you read this ad completely.
- Review SQL database schema to identify where improvements can be made (e.g adding indexes, revising pk/fk structure if required etc)
- Creation of PostgreSQL database on Google Cloud (Cloud SQL)
- Creation of BigQuery table to store historic data - partitioned by day
- Terraform file to manage infrastructure changes and service accounts / permissions
- Github repositories to manage source code for each of
- images that get built (e.g source code in src folder and docker file)
- terraform file(s)
- Creation of a data extraction script to remove data from the SQL database which is 30 days old and push to BigQuery (envisioned to run daily to remove previous day’s data push to bucket/bigquery then remove from SQL db)
- Containerise (docker) the data extraction scripts to be stored in good cloud container repository
- Containerise (docker) the Fast API app to be stored again in GCP container repository
- Deploy an instance of the Fast API app on cloud run
- Deploy appropriately sized VMs to run the containers with the data extraction scripts
Phase 2 - upon successful completion of Phase 1
- Add a caching layer to the API services (whether that’s redis, or local in-built caching on Fast API if that is sufficient)
- Deploy / run ML models I build on GCP leveraging serverless / cloud solution where possible
As said in the beginning, I would eventually want to hook this backend up to something like a React front-end. If you can help with this as well, or can involve someone who you can collaborate with to do so, the budget can reflect this.
(I plan on reaching out to plan the interviews the week of the 23th of May)
Kind regards,
Milan
I have been trying to build the infrastructure to do academic research on a large volume of economic time-series data. The research would be both nowcasting of economic health and analyses of historic data. So for this, we would need to have a database that is constantly kept up-to-date from different data-sources and that can serve (near-)real time data to researchers. Additionally, we want to be able to integrate the analytics and models that we develop into the API to serve up the results of the models in a timely manner.
I am currently setting up this research stream with a very small team but we would already like to make it scalable, and affordable for the long-term.
Eventually I would like to add a front-end so that I can provide access to the data and our model-outputs to less technical researchers. This does not need to happen in this project but the foundations should be built with this in mind.
High-level idea of the needed elements (not rigid on this and open to suggestions):
- a postgresql database that holds very recent data (e.g. last 30 days only) that would be used to stream data for nowcasting. Hosting this on gcp seems to be the best option.
- a BigQuery database that would hold the full archive. This database would be used to serve up data for macro-analyses that are not as time-sensitive as the nowcasting.
- a FastAPI (or other python-based API framework) built to expose the data and model outputs through both RESTful and websocket api end-points.
- a caching solution to decrease latency of api-calls and reduce load on the database(s).
- a solution to handle situations where multiple models need to run on the data in the background to generate and save their outputs without generating a bottle-neck on the client-side.
Work that has already been completed:
- Data extraction scripts in Python for retrieval of the data from the target sources
- 800+gb of raw data retrieved in Parquet format
- data cleaning scripts in python
- SQL database schema draft
- First draft of the API written in Fast API
Work that needs to be completed:
Phase 1
- Tell me your hobby so I know you read this ad completely.
- Review SQL database schema to identify where improvements can be made (e.g adding indexes, revising pk/fk structure if required etc)
- Creation of PostgreSQL database on Google Cloud (Cloud SQL)
- Creation of BigQuery table to store historic data - partitioned by day
- Terraform file to manage infrastructure changes and service accounts / permissions
- Github repositories to manage source code for each of
- images that get built (e.g source code in src folder and docker file)
- terraform file(s)
- Creation of a data extraction script to remove data from the SQL database which is 30 days old and push to BigQuery (envisioned to run daily to remove previous day’s data push to bucket/bigquery then remove from SQL db)
- Containerise (docker) the data extraction scripts to be stored in good cloud container repository
- Containerise (docker) the Fast API app to be stored again in GCP container repository
- Deploy an instance of the Fast API app on cloud run
- Deploy appropriately sized VMs to run the containers with the data extraction scripts
Phase 2 - upon successful completion of Phase 1
- Add a caching layer to the API services (whether that’s redis, or local in-built caching on Fast API if that is sufficient)
- Deploy / run ML models I build on GCP leveraging serverless / cloud solution where possible
As said in the beginning, I would eventually want to hook this backend up to something like a React front-end. If you can help with this as well, or can involve someone who you can collaborate with to do so, the budget can reflect this.
(I plan on reaching out to plan the interviews the week of the 23th of May)
Kind regards,
Milan