You have probably been taking the first steps in data-land for a while: the first dashboards, the first models, the first analyses. Via a Power BI connector, a hand-written script, or an extensive Excel sheet, you retrieve your data. At some point, something breaks down: the data no longer fits in Excel, the connector keeps failing, or you are trying to join data together. The current approach no longer suffices. In short: time for a data warehouse.
But which software should you buy? And is a cloud-native solution best, or is flexibility more important? Commercial or open source? And also: what should it cost?
Step-by-step plan
By far the most important consideration is the question of what you will use it for, now and in the foreseeable future. Setting up and configuring a data warehouse is no small matter. And changing supplier at a later stage is typically a recipe for severe and prolonged headaches. That is why I have developed a step-by-step plan to keep things manageable.
Step 1: the type of data largely determines the possibilities
The vast majority of our clients have data that essentially consists of many large Excel-sheet-like data, or tabular data. Think of data from a webshop like Shopify, a financial package such as Exact Online, or sensor data measuring temperature and humidity in a greenhouse. The data is tabular because we have rows (for example: webshop transactions, journal entries, or greenhouse temperatures), where the columns indicate where something happened, when, and often also by whom. This type of data fits well in an Excel sheet provided there is not too much of it.
Do you recognise yourself in this type of data requirements? Good news: you have virtually unlimited choice in which data warehouse software you want to use - including relational databases that can be deployed as a data warehouse. Why are you fortunate? The costs are very manageable and there are a few clear choices to make.
If your data is primarily tabular in nature, traditional solutions such as Microsoft SQL Server (pricey but widely used), PostgreSQL (the most popular open-source - and therefore free! - database software) and Oracle (very pricey but stunningly robust) are good options. Bottom line: for organisations that do not want to be tied to commercial software, PostgreSQL is a good option; otherwise, you can go for Microsoft or Oracle.
Extra handy: PostgreSQL supports, via numerous plugins, virtually all types of data you can think of. For example, full-text search data and vector data (perfect in combination with generative AI applications).
There are also disadvantages to the above software packages for data warehouses. They are not specifically designed for so-called OLAP purposes (OLAP means Online Analytics Processing), but often for OLTP purposes (Online Transaction Processing). This means that the high data integrity standards sometimes get in the way of speed. You only notice this in practice when you have truly vast amounts of data (hundreds of millions of data points or more). And even then, such systems can be well optimised.
If you are dealing with such enormous volumes of data, you can deploy so-called columnar storage-based data warehouses. ClickHouse, Synapse Analytics and Redshift are examples. These are modern alternatives but do require a somewhat different way of thinking than you are accustomed to with classic databases. We therefore recommend them primarily for more advanced purposes with specific requirements around speed or data volume.
If your data is not primarily tabular in nature but consists of, for example, images, videos, documents and the like, then other systems are suitable. For documents, you might consider Apache Cassandra or a Data Lakehouse. The flexibility of these systems does require a high degree of precision, especially when entering data, so look before you leap.
Step 2: what will you use your data warehouse for?
Do you want real-time analytics on many millions of observations that you want to combine and pull apart in all possible ways? Then a relational database data warehouse (such as PostgreSQL or Oracle) is not suitable. You will quickly end up in the domain of column storage systems.
…but be honest: do you really need this? Do you have those data volumes, and is speed so crucial? If the answer is yes, then column storage data warehouses are certainly your friend.
Most of our clients can manage perfectly well with relational databases as a data warehouse. The aforementioned data integrity and years of experience help here. Finding personnel with experience in such systems is typically easier than for esoteric, new systems. Moreover, the - by now decades-long - foundation on which such relational databases are built is not matched by most systems. If you opt for a newly developed system, investigate carefully how sustainable that investment is. Is it open source with a large community of developers? Or is it a closed product from a 'trendy start-up'?
Step 3: costs and flexibility
The costs for a data warehouse are sometimes very unclear. For example, with Google BigQuery (a superb columnar-oriented system) you do not pay so much for storage or the number of computer processors, but primarily for how much data is analysed per query. That is fine for datasets you rarely use - but why would you invest much effort in storing data you rarely use? So check the pricing models carefully, especially when working in the cloud.
Another cost item is licence fees. Commercial data warehouse software providers often have complicated licence models where the amount of memory, the type of data, the number of processors and such determine what you pay. Just when you are getting going and want to do more with your data warehouse, you suddenly pay (substantially) more in licence fees.
The above strongly conflicts with the data science philosophy of 'investigate everything and keep what is good'. It should be free for analysts to analyse data as often and as much as they want, rather than being penalised for usage.
Besides costs, flexibility is an important consideration. Take SQL Server: if you purchase this package in the cloud, you are quite quickly automatically tied to Azure. A switch to another cloud provider is possible (or sometimes necessary in the case of stricter privacy legislation), but requires a much more expensive licence.
Our advice
Be honest with yourself and critically examine the volume and type of data you want to process. Do not fall into the trap of immediately wanting the newest thing - investigate whether proven techniques are sufficient for your (foreseeable) goals.
…and do not underestimate the power of open-source software.