1. Data Warehouse

Recommend reading up on Kimball vs Inmon.

Kimball -- centralised storage in a "dimensional model". Uses a star/snowflake schema where the central FACT table (containing the raw transactional data) can be filtered and aggregated based on choices made in dimension tables (edges of the star/snowflake).

demographic account_types product_types
\ / /
\ / /
customer product
\ /
\ /
FACT table (sale $, order quantity, order ID, product ID)

customer, account_types etc are dimensions to filter your low-level transactional data. The schema looks like a snowflake when you add enough dimensions, hence the name.

The FACT table makes "measures" available to the user. Example: Count of Orders. These are based on the values in the FACT table (your big table of IDs that link to dimensions and low-level transactional data).

You can then slice and dice your count of orders by fields in the dimensions.

You could then add Sum of Sale ($) as an additional measure. "Abstract" measures like Average Sale ($) per Order can also be added in the OLTP backend engine.

End users will often be using Excel or Tableau to create their own dashboards / graphs / reports. This pattern makes sense in that case --> user can explore the heavily structured business data according to all the pre-existing business rules.

Pros:

- Great for enterprise businesses with existing application databases

- Highly structured and transaction support (ACID compliance)

- Ease of use for end business user (create a new pivot table in Excel)

- Easy to query (basically a bunch of SQL queries)

- Encapsulates all your business rules in one place -- a.k.a. single source of truth.

Cons

- Massive start up cost (have to work out the schema before you even write any code)

- Slow to change (imagine if the raw transaction amounts suddenly changed to £ after a certain date!)

- Massive nightly ETL jobs (these break fairly often)

- Usually proprietary tooling / storage (think MS SQL Server)

---

2. Data Lake

Throw everything into an S3 bucket. Database table? Throw it into the S3 bucket. Image data? Throw it into the S3 bucket. Kitchen sink? Throw it into the S3 bucket.

Process your data when you're ready to process it. Read in your data from S3, process it, write back to S3 as an "output file" for downstream consumption.

Pros:

- Easy to set up

- Fairly simple and standardised i/o (S3 apis work with pandas and pyspark dataframes etc)

- Can store data remotely until ready to process it

- Highly flexible as mostly unstructured (create new S3 keys -- a.k.a. directories -- on the fly )

- Cheap storage

Cons:

- Doesn't scale -- turns into a "data swamp"

- Not always ACID compliant (looking at you Digital Ocean)

- Very easy to duplicate data

---

3. Data Lakehouse

Essentially a data lake with some additional bits.

EDIT: aims to mitigate against the "data swamp" problem, improve flexibility / performance when changing how some-random-column X is calculated, improved governance and auditability.

A. Delta Lake Storage Format a.k.a. Delta Tables

https://delta.io

EDIT: apparently there are a few more players and not just delta lake (Apache iceberg). But there’s still questions around how open the open source for these things are.

Versioned files acting like versioned tables. Writing to a file will create a new version of the file, with previous versions stored for a set number of updates. Appending to the file creates a new version of the file in the same way (e.g. add a new order streamed in from the ordering application).

Every file -- a.k.a. delta table -- becomes ACID compliant. You can rollback the table to last week and replay e.g. because change X caused bug Y to happen.

AWS does allow you do this, but it was a right ol' pain in the arse whenever I had to deal with massively partitioned parquet files. Delta Lake makes versioning the outputs much easier and it is much easier to rollback.

B. Data Storage Layout

Enforce a schema based on processing stages to get some performance & data governance benefits.

Example processing stage schema: DATA IN -> EXTRACT -> TRANSFORM -> AGGREGATE -> REPORTABLE

Or the "medallion" schema: Bronze -> Silver -> Gold.

Write out the data at each processing stage to a delta lake table/file. You can now query 5x data sources instead of 2x. The table's rarity indicates the degree of "data enrichment" you have performed -- i.e. how useful have you made the data. Want to update the codebase for the AGGREGATE stage? Just rerun from the TRANSFORM table (rather than run it all from scratch). This also acts as a caching layer. In a Data Warehouse, the entire query needs to be run from scratch each time you change a field. Here, you could just deliver the REPORTABLE tables as artefacts whenever you change them.

C. "Metadata" Tracking

See AWS Glue Data Catalog.

Index files that match a specific S3 key pattern and/or file format and/or AWS S3 tag etc. throughout your S3 bucket. Store the results in a publicly accessible table. Now you can perform SQL queries against the metadata of your data. Want to find that file you were working on last week? Run a query based on last modified time. Want to find files that contain a specific column name? Run a query based on column names.

EDIT: also helps with data governance and auditability as all of your files/tables are tracked in a centralised location.

Pros:

- transactional versioning -- ACID compliance and the ability to rollback data over time (I accidentally deleted an entire column of data / calculated VAT wrong yesterday)

- processing-stage schema storage layout acts as a caching layer (only process from the stage where you need to)

- no need for humans to remember the specific path to the files they were working on as files are all indexed

- less chance of creating a "data swamp"

- changes become easier to audit as you can track the changes between versions

Cons:

- Delta lake table format is only really available with Apache Spark / Databricks processing engines (mostly, for now)

- Requires enforcement of the processing-stage schema (your data scientists will just ignore you when you request they start using it)

- More setup cost than a simple data lake

- Basically a move back towards proprietary tooling (some FOSS libs are starting to pop up for delta tables, plus git-based data versioning is a thing)

---

4. Data Mesh

geoduck14's answer on this was pretty good. basically have a data infrastructure team, and them domain-specific teams that spring up as needed (like an infra team looking after your k8s clusters, and application teams that use the clusters). domain specific data team use the data platform provided by the data infrastructure team.

Previously worked somewhere in a "product" team which basically performed this function. They just didn't call it a "data mesh".