Is it possible to configure Meltano loaders in a way that they optimize loaded tables? Specifically, I am talking about PostgreSQL indexes, Snowflake cluster keys, etc.

I think the answer is no, not without forking the loader and adding whatever capabilities you're interested in. It depends on what you mean by "optimize" though.

This is exactly what I just did in my demo. But, when you implement incremental ELT, in dbt you want to do something like this:

-- Rows to be updated
SELECT ... FROM raw
WHERE raw.id IN ( SELECT id FROM target)
  AND raw.last_updated > ( SELECT max(last_updated) FROM target )

You can design indexes/... in

target

in dbt. But you would have to design indexes/... in

raw

manually after first load, which is dangerous, because e.g. when you decide to replicate the solution in a different infra (e.g. new region), you easily forget to apply the manual optimizations 😉 In GoodData, we implemented custom loaders in the past. We allowed users to define (in a declarative way) such optimizations per table. However, we had to do it only for Vertica 😉 Generally, it makes sense to allow specifying the following features: • Indexes in standard OLTP-like DBs • Clustering, sort columns, ... in clustered columnar MPP DBs • Partitioning

All fair points. I actually use a pre_hook in the models for the "optimized" tables to create an index on the "raw" tables if one doesn't exist. I'd forgotten about that 😝