Not OP, but for me, the lack of essentially any type system in SQLite makes it a total no-go for storing data long-term or that more than one application needs to access. Date/time being an especially painful footgun in SQLite.

I view SQLite as something a single application can use for storing state/settings/misc operational data instead of directly writing files, especially if the data being stored is relational or needs ACID. As soon as the data itself has meaning and structure per se, you're better off with something that can help enforce and describe the data: rich datatypes, foreign keys that aren't optional, etc.

DuckDB is heavily focused on analytical workflows, so it stores data in a compressed columnar format. So "rewrite one row" is more efficient in SQLite, but "sum all the values in this column" is vastly more efficient in DuckDB.

That's the core of the difference, to me. But the projects also have a pretty different ethos. SQLite has always prioritized simplicity, often at the expense of features, whereas DuckDB's approach to features seems to be "the more the merrier." E.g. the star feature of this new release, client/server mode. Plenty of people would love to see something similar in SQLite, but it doesn't seem likely.