I haven't thought about sqlite as a data interchange format, but I was looking at deploying sqlite as a data lake format some time ago, and found it wanting.
1. Dynamically typed (with type affinity) [1]. This causes problems with there are multiple data generating processes. The new sqlite has a STRICT table type that enforces types but only for the few basic types that it has.
2. Doesn't have a date/time type [1]. This is problematic because you can store dates as TEXT, REAL or INTEGER (it's up to the developer) and if you have sqlite files from > 1 source, date fields could be any of those types, and you have to convert between them.
3. Isn't columnar, so complex analytics at scale is not performant.
I guess one can use sqlite as a data interchange format, but it's not ideal.
One area sqlite does excel in is as a application file format [2] and that's where it is mostly used [3].
1. Dynamically typed (with type affinity) [1]. This causes problems with there are multiple data generating processes. The new sqlite has a STRICT table type that enforces types but only for the few basic types that it has.
2. Doesn't have a date/time type [1]. This is problematic because you can store dates as TEXT, REAL or INTEGER (it's up to the developer) and if you have sqlite files from > 1 source, date fields could be any of those types, and you have to convert between them.
3. Isn't columnar, so complex analytics at scale is not performant.
I guess one can use sqlite as a data interchange format, but it's not ideal.
One area sqlite does excel in is as a application file format [2] and that's where it is mostly used [3].
[1] https://www.sqlite.org/datatype3.html
[2] https://www.sqlite.org/appfileformat.html
[3] https://en.wikipedia.org/wiki/SQLite#Notable_uses