A best practice for collecting and publishing data in Google Cloud Platform in 3 steps

The Process

At Crystalloids we work in a structured way to get maximum results in the shortest time.

Preparation phase:

1. Information gathering about the existing architecture and high level goals.
2. Writing backlog together the Product Owner, developers and Commercial Director
3. Backlog refinement to get all necessary details right such as Definition of Done
4. Backlog prioritisation by business importance
5. Estimation of work for the selected user stories

Implementation phase:

1. Design the architecture
2. Implement the solution
3. Demo working software in two sprints of two weeks
4. Document and handover the code and documentation
5. Review customer satisfaction

The Solution

Instead of loading data from a source into BigQuery and directly build reports on these BigQuery tables, the best practice we implemented is to divide this process into three steps:

1. Load

This step only takes data from the source and writes it to BigQuery in its raw form.

No transformations are made on the data, to make sure that you are looking at the same data as was received from the source. It can be useful to not only write it to BigQuery, but also to a file in Google Cloud Storage. For example, when you receive data in JSON format, this can be stored in its purest form.

2. Normalise

This step takes the raw data and makes the transformations that are needed to be able to use the data. Make sure that:

• all data is deduplicated: no transformed table contains two identical rows. 
• all data is actual: only one version of the data is present, the most recent one,
• all data is stored at the lowest granularity (no aggregation of raw data, only storing its lowest level)

3. Publish

Once the data is normalised and all entities are stored in tables, you are ready to start gathering the data that you want to use in your reports or for specific analyses. This should happen in a separate publish step. Here you also have the possibility to combine multiple entities and/or sources.

To orchestrate these steps and make sure that the next step will only be executed when the previous is finished, we used Google Workflows. Within this workflow, each step in the solution is handled by a Cloud function. For this case it was required to load the data from the source on a daily basis. Therefore the first step is triggered by Cloud scheduler, since it allows to trigger the process every day at the same time.