Do you need clear dashboards and data analytics but want to avoid investing in expensive proprietary BI tools or maintaining complex ETL processes? In this article, we will show you how to build a simple and cost-effective BI solution using materialised views and Metabase.

Right off the bat, it is important to note that this approach isn’t for everyone. If you deal with terabytes of data or operate a highly complex data environment, you will likely need more robust tools. However, if your data is in the gigabyte range with tens of millions of records, read on – this solution might be exactly what you are looking for. While the approach described here is built on PostgreSQL, the core concept can easily be applied to other database technologies.

Solution Architecture

To ensure the BI layer runs reliably without putting a strain on your production environment, you need to establish a solid data foundation. The key element of this architecture is a standalone warehouse database used exclusively for business intelligence. Data flows into it from replicas of the primary databases using dblink technology. This allows one PostgreSQL database to connect to another and read its data without the need for manual exports.

Instead of traditional ETL processes, materialised views are used to transform and prepare the data. Unlike standard database views, which fetch data from scratch every time a query is run, a materialised view physically stores the query result. This brings two major benefits: analytical queries are significantly faster, and primary databases are spared the burden of repetitive reads. To keep the data fresh, regular updates run automatically in the background using the pg_cron extension.

At the same time, these materialised views act as data marts – pre-prepared data perspectives focused on specific business areas, such as sales, orders, or users. A huge advantage is that they are accessible via standard SQL queries, meaning any BI tool can work with them without requiring special connectors or complex configuration.

Metabase: The BI Tool That Doesn’t Need Developers

For our BI tool, we chose Metabase. Its modern and intuitive user interface allows anyone with a basic understanding of data and SQL to build dashboards. As a result, the people who actually work with the data and know what insights they need can design their own analytical views – without needing to involve developers.

Once your dashboards are ready, they can be integrated directly into your application. Metabase supports embedding, meaning you can generate a signing JWT token to securely authenticate access and display the dashboard straight inside your UI using the Metabase HTML component.

Conclusion: Simple Architecture, Significant Impact

We deployed this solution for a client who had previously been using an expensive proprietary BI tool. The outcome exceeded expectations, proving highly successful both technically and in day-to-day use.

Key benefits for the client included:

• annual costs ten times lower than the previous solution;
• significantly faster response times for analytical queries, even over larger data volumes, thanks to materialised views;
• clear, clean dashboards accessible directly within their administration panel;
• availability for all users without the need to manually generate and distribute reports.

The best feedback came directly from the users themselves. After his first look at the new dashboards, one user remarked that he almost cried tears of joy. We take that as validation that even a relatively simple architecture can have a very tangible, noticeable impact.

Not every BI project needs to start with a massive platform, complex integration processes, and hefty licence fees. If the requirements are well-defined and the data volume aligns with database capabilities, combining PostgreSQL, materialised views, and Metabase can be a highly efficient path to fast, actionable reporting.