Good overview of the architecture patterns for analytical purposes.

There’s an important variation of the mentioned architecture patterns described. One can offload read-only workload to secondary replicas, functionality available at least in SQL Server, MySQL, Oracle and Postgres.

Change Data Capture (CDC) in SQL Server allows to implement incremental updates for the data available in the analytical plane. One architecture that emerged during the past years is pushing the changes every 15-30 minutes to CDM folders in the data lake and the data can be used directly for reporting (e.g. via Power BI) or further processed and prepared via the dedicated and/or serverless SQL pool. A more recent alternative is to synchronize the data to delta lake tables (instead of CDM folders) in the OneLake.

CDC is also available in Oracle, Postgres, and MySQL, though incremental updates is a whole topic on its own.

Microsoft Fabric introduced the concept of shortcut allowing to access data from various cloud sources (incl. Amazon, Dataverse) without moving them around in advance.

Another architecture that can be implemented at least in SQL Server and referred to as Real-time analytics uses an updateable columnstore index on a rowstore table. The columnstore index maintains a copy of the data, so the OLTP and analytics workloads run against separate copies of the data.