Building a Modern Data Stack: From Spreadsheets to Scalable Insights

dbt, Fivetran, big query, tableau

SQL, MongoDB, MailChimp, Exact, Google Analytics

De Energiebespaarders

tl;dr

I built an integrated data platform using modern tools, spanning from ingestion of various data sources to warehousing and transformation of the data, and finally, visualising insights via dashboards.

problem

Prior to implementing the data platform, the company relied on spreadsheets for data analysis. This meant directors and managers spent countless hours manually copying and pasting data, writing formulas in Google Sheets, and wrestling with version control issues—time that could have been better spent on strategic decision-making.

With the cooperation of project stakeholders, I conducted a research spike to identify the ideal tooling flow for the client, considering in-house technical expertise, existing infrastructure, and budget constraints.

Some of the key aspects I optimised for included:

• The company was using GCP for much of its infrastructure. Tools within or easily integrated with GCP were preferred.
• There were few data-literate employees in the company, meaning only a limited group of experts would be using the tools.
• The data was complex and described intricate business processes, making raw data prone to misinterpretation.

solution

I opted to use the following tools:

data ingestion

I selected Fivetran to ingest data from a variety of sources. While this is a paid service it greatly outweighs the cost of developers' time to set up and maintain the necessary integrations.

data transformation

dbt is the de facto standard for data transformation. It allows the data analysts to write SQL transformations that are easy to understand and maintain.

data warehousing

I opted for BigQuery to warehouse the data. This solution fits in well with the company's existing GCP infrastructure and is a powerful tool for the data analysts.

data visualization

Tableau was selected for a couple of reasons:

• The company already had some experience with using it
• It is compatible with the hardware available to the company (i.e. on Macs, which PowerBI is not)
• There was no need to democratise data analysis as small group people in the company would realistically be using the tools, so tools like Looker were not a good fit

Data flow diagram, showing how data moves from source to chart

After securing buy-in from the management team, I implemented the stack end to end, working closely with the data analysts who would eventually use the tools in their daily workflow.

I also prepared training materials and documentation to help the team leverage the tools to their full potential.

results

A modern data architecture that streamlined reporting.

With this new setup, managers and directors are no longer bogged down by data wrangling—they can now focus on steering the business forward, confident in the accuracy and accessibility of their data.