In 2022 – 2023, through a partner company, we worked on the analytical part of a project to optimise government information systems. After migration the data sat in PostgreSQL, and analysts wanted to look at it long before a classic BI project could be ready. Such a project typically means months of tool selection, licensing and data warehouse design. So we built a proof of concept on Apache Superset over the existing database. This article is for IT and data teams considering a similar step: what Superset gives you straight away, where the risks are, and how to turn a prototype into a specification for real BI.

A proof of concept is not a finished BI platform

The point of a proof of concept is to find out which data people really need, who needs it, in what form and how often. It is not finished reporting. Superset suits the job: it is an open-source Apache Software Foundation project under the Apache 2.0 licence, with no licence fees, and it connects directly to an existing relational database. The difference between an exploration tool and a finished BI platform has to be explained to management at the very start. Otherwise the prototype gets judged against criteria it was never meant to meet, or it quietly turns into a production system without operations or backups.

What Superset gives you at once and what needs preparing

For each database engine Superset needs a driver and an SQLAlchemy dialect, and PostgreSQL is among the commonly supported ones. Once a database is connected, analysts get SQL Lab, an SQL editor with a schema browser, query history and saved queries. A query result can be charted directly, but to serve as a lasting source for charts and dashboards the query has to be saved as a dataset. For an analyst who knows SQL, this is usable immediately.

For people without SQL, the datasets matter most. A physical dataset is a registered table or view; a virtual dataset is defined by an SQL query. On top of a dataset Superset offers a thin semantic layer: calculated columns (row-level expressions, no aggregate functions), metrics (aggregations, such as the ratio of two sums), flags marking columns as temporal, filterable or dimensions, and metric certification. Preparing datasets with clearly named columns and metrics was most of the work in our proof of concept.

It pays to keep definitions of key indicators in one place. If every analyst writes their own virtual dataset with their own calculation, within a few weeks there are several different numbers for the same indicator. Logic that is reused belongs in a database view, where it can be versioned and reviewed like code, with Superset metrics defined on top.

Performance: analytics should not load production

Heavy queries on the production database were the first point of friction. Options that help, and their trade-offs:

  • A read-only replica. In PostgreSQL, hot standby allows only reads on the replica. The PostgreSQL documentation warns about conflicts, though: when the replica needs to apply a change from the primary, a long query can be cancelled once the configured delay has passed. The hot_standby_feedback parameter removes cleanup conflicts at the cost of delaying the cleanup of dead rows on the primary. An analytics replica is therefore worth configuring differently from one meant for high availability.
  • Materialised views for the most common questions. They store a query result and are refreshed with REFRESH MATERIALIZED VIEW. Analysts see the state as of the last refresh, and the dashboard should say so.
  • Result caching. Superset does not cache chart data in the default configuration. Caching has to be switched on, and the documentation recommends Redis as the backend. The timeout can be set per chart, dataset or database, otherwise the default applies.
  • Asynchronous queries. Long SQL Lab queries exceed a typical web request timeout. Superset can run them on Celery workers, but it then needs a message broker (the documentation recommends Redis or RabbitMQ) and a results backend, and asynchronous execution is enabled per database. A small proof of concept can often do without it, but large volumes cannot.

Access rights: roles, datasets and rows

Superset ships with the built-in roles Admin, Alpha, Gamma, sql_lab and Public. Admin has all rights. Alpha sees all data sources but cannot manage other people’s access. Gamma is for consumers and sees only the sources it has been granted through another role, and sql_lab opens SQL Lab. Two properties are worth knowing in advance. Access to a database means access to all its datasets. And anyone with access to a dataset also sees the published dashboards that use it, unless dashboard-level access control is enabled.

Row-level security filters are assigned to a dataset and to roles, and Superset adds them to the WHERE clause of the query it generates. For queries written by hand in SQL Lab, however, they apply only when the RLS_IN_SQLLAB flag is enabled, which Superset’s configuration marks as being in testing and off by default. Anyone who should see only some rows should not get SQL Lab on the same database. It is safer to enforce the restriction in the database itself as well: a dedicated read-only database user with access only to an analytical schema or to prepared views.

In the public sector, personal data protection comes on top. Analysts rarely need to identify a specific person, and the data minimisation principle in Article 5 GDPR argues for aggregated or pseudonymised views instead of raw tables. Rights designed before launch can be carried over into the BI project, whereas adding them once users are used to seeing everything is harder.

Deployment: the defaults are not production settings

  • The application secret key (SECRET_KEY) signs sessions and encrypts sensitive data in the metadata database. It should be random and kept out of the repository. Since version 2.1 Superset refuses to start with the default key, and it documents a rotation procedure that re-encrypts the stored data.
  • A docker compose installation creates an admin account with the password admin, and the documentation states explicitly that such an installation is not production-ready. Before anyone else gets access, change the default account and use personal accounts, ideally tied to the organisation’s identity management.
  • Metadata database: the default is SQLite, which the documentation strongly discourages for production. Use PostgreSQL or MySQL with backups, because it holds the datasets, metrics, dashboards and permissions.
  • Network: Superset behind a reverse proxy with HTTPS, and the analytical database reachable only from the Superset server, not from the whole network. Leave the Content Security Policy that Superset enables by default switched on.
  • Read-only connection: a database user without write privileges and, in Superset’s database settings, the allow_dml option disabled, which blocks non-SELECT statements such as UPDATE, DELETE and CREATE in SQL Lab. Two independent safeguards are better than one.
  • Embedding dashboards in another application works through Superset’s SDK and a short-lived guest token issued on the server, which can also carry row filters. Allowed domains must be set explicitly, because according to the documentation an empty list allows any origin. A proof of concept rarely needs embedding.

Running the proof of concept and measuring success

  • Questions, not dashboards: collect the concrete questions analysts currently answer with exports, and pick a small number with the highest value and available data.
  • Data inventory: for each question, the tables, keys, data freshness and sensitivity (personal or otherwise protected data).
  • Datasets and metrics: physical datasets over prepared views, columns named in the users’ language, metrics with a description of the calculation; virtual datasets only where a database view makes no sense.
  • Access before data: who gets SQL Lab, who only dashboards, which row filters. Agree this with the data owner before users are invited.
  • Short iterations: regularly show analysts what has been added, and record the questions Superset could not answer, with the reason.
  • An agreed end: the proof of concept has a fixed duration and criteria set in advance, and it ends with a decision rather than drifting on.

Success criteria are agreed before the start, not afterwards. Useful ones: how many of the selected questions can be answered without a manual export, how quickly an analyst gets an answer compared with the previous process, whether users keep coming back after the first week, and which questions remained unanswered and why (missing data, performance, permissions, complexity). The number of dashboards built does not measure success.

In our case, within two weeks analysts had access to data they used to receive as exports once a month. It also became clear which questions were asked most often.

Where Superset is not enough

  • Data governance: Superset is not a data catalogue. Data lineage, ownership and approval of indicator definitions have to be handled elsewhere. Certifying a metric in a dataset only adds a label and does not replace an approval process.
  • Data warehouse: Superset queries whatever database you give it. It does not integrate multiple sources, keep history or clean data. If analysts’ questions need to join systems, that is work for the data layer, not for a visualisation tool.
  • Versioning: dashboards, charts and datasets can be exported and imported as a ZIP of YAML files and stored in a repository. Reviewing changes and promoting them between environments is, however, a process of its own. The documentation for a not yet released version describes version history for charts and dashboards with limited retention, but that does not replace review either.
  • Self-service for people without SQL: with the right permissions they can register an existing table or view as a dataset through the interface, then filter, group and pick metrics in the chart builder. Joining tables and preparing new calculations still require SQL or a prepared data layer. If such people are most of your users, the value depends on the quality of the datasets, not on the tool.
  • Operations: a production Superset means a web server, metadata database, cache, workers, backups and upgrades. Someone has to own them.

From proof of concept to a BI specification

The questions analysts asked most often became, in our case, the basis of the requirements for the real BI project. The output of a proof of concept should therefore be a document, not just a running tool. It should contain:

  • a list of questions ranked by frequency and user group,
  • a glossary of indicators with exact calculations, taken from the dataset metrics,
  • requirements for data freshness, plus the approximate volumes and response times that were acceptable in the prototype,
  • an access model including row filters and the handling of personal data,
  • the sources that were missing from the prototype and the questions left unanswered because of them,
  • a decision on what stays in operation from the prototype and who will run it, or that it will be switched off.

The proof of concept did not replace the project, but it made it more precise, because the specification rested on questions analysts had asked, not on guesses. If you have data in a relational database and people who want to see it, we recommend starting with a time-boxed exploration with clear criteria, access designed up front and a read-only connection. Decide on a large BI project only once you know what you need.