There’s a lot of talk about which Data Warehouse analytic solution is best (let alone database) – both on-premises servers and cloud. These days of remote working, we’d advise the cloud – and it’s a good time and place to consolidate your various data sources into one place for BI analytics. So what options should you go for? You need to accommodate the technical issues:
- Dimensional Modelling
- Star Schema or alternatives
- Slowly Changing Dimensions (SCD)
- ETL or ELT (Extract/Load/Transform)
- Fully structured or unstructured data
- Source formats – from CSV/TXT to Lakehouse Delta, Parquet, Arrow etc.
Of course, these are just the key issues but their answer, together with requirements, will determine the rest.
DW’s from OLTP & OLAP – WTF?
In the old days of closer database management (where everything went into the database before the mass wave of additional spreadsheets and streamed data), Data Warehouse analytics solutions (DW) used to comprise of a set of read-only SQL Views which were optimised to present the dimensional model to reporting and BI dashboards. Effectively a layer of OLAP (Online Analytics Processing) over the original core OLTP (Online Transactional Processing – ie. the data records being inserted and updated in the database). But both were contending for server CPU and storage resources, hence the boom in separate Data Warehouses. Data Marts are just more targeted Data Warehouse silos for Sales, Inventory, Marketing etc. OLTP still powers on, as data entry and management apps need them, but OLAP has transcended.
Recommendations…
Being a Microsoft Partner, we may advise Azure Synapse Analytics and Analysis Services combined with Data Factory ETL/ELT, or Databricks. Or even AWS Redshift. But we’re just as likely to recommend Snowflake – for easier client management. That’s because we provide maintainable solutions, and don’t want to be your daily DBA or data engineer, and crucially – for budget. You can have a full Data Warehouse for less than £100 per month rather than over £1,000 for just the computing infrastructure, let alone management. Yet you can immediately scale it up when you have billions of records and thousands of users or queries. Snowflake is also much more manageable on it’s billing – less likely for surprise charges due to distributed compute clusters or ELT data ingress/egress loading. But it can be wise (and often unavoidable) to combine cloud platforms – Azure or AWS storage with another platform’s DW. Data pipelining and integration tools like Data Factory and dbt ensure you can switch where necessary.
The beauty of Snowflake (as opposed to many other cloud DWs) is that your account can be built on the same datacentre as your Azure or AWS cloud, for low latency. The best of both worlds!
