Yet today I feel it was 2016 dataders who is the crazy one lol
It's very neat for some types of data to have columns contiguous in memory.
That's not really the purpose; it's really a language-independent format so that you don't need to change it for say, a dataframe or R. It's columnar because for analytics (where you do lots of aggregations and filtering) this is way more performant; the data is intentionally stored so the target columns are continuous. You probably already know, but the analytics equivalent of SQLite is DuckDB. Arrow can also eliminate the need to serialize/de-serialize data when sharing (ex: a high performance data pipeline) because different consumers / tools / operations can use the same memory representation as-is.
You can also store arrow on disk but it is mainly used as in-memory representation.
it's actually many things IPC protocol wire protocol, database connectivity spec etc etc.
in reality it's about an in-memory tabular (columnar) representation that enables zero copy operations b/w languages and engines.
and, imho, it all really comes down to standard data types for columns!