Question on the system DB. Docs say that

Meltano stores various types of metadata in a project-specific system database

. Does this imply that each project has to have its own system database? They could be the same databases, but different schema, as

you can choose the target schema within that database

. Do they need to be separate schemas then? And, is a project equivalent to a single ELT pipeline? It is not explicitly stated, but a given extractor may be used multiple times, but it appears that the extractor is named after the tap, so only has one config to use. Same with the loader: the postgres loader, for example, points to a specific host, database, schema, and with a username and password (in .env). So does this mean that a Meltano "project" is a single ELT pipeline? Combined the two, and if we have 100 pipelines we will have 100 Meltano projects, and require 100 system databases, or at least 100 separate schemas. I'm hoping this is incorrect 😉 I don't mind 100 projects (git projects), as I would actually prefer that than to have a monorepo with everything in it. However, I'd hope that we can all point to the same system DB to store the job state, etc.

Hi @fred_reimer! I'll address your questions:

Does this imply that each project has to have its own system database?

No, multiple Meltano projects can use the same database to store metadata, elt state, etc.

And, is a project equivalent to a single ELT pipeline?

No. A Meltano project can -and usually does- consist of multiple pipelines from N sources to M destinations.

It is not explicitly stated, but a given extractor may be used multiple times, but it appears that the extractor is named after the tap, so only has one config to use. Same with the loader: the postgres loader, for example, points to a specific host, database, schema, and with a username and password (in .env). So does this mean that a Meltano "project" is a single ELT pipeline?

No, you can "switch" between extractors and loaders of the same type using environment variables. So, even if you have a single

target-postgres

in

meltano.yml

, in your environment you can set different hosts, etc.

# One postgres instance
MY_TAP_SETTING=abc
TARGET_POSTGRES_HOST=<http://mydomain.com|mydomain.com>
TARGET_POSTGRES_USERNAME=first
meltano elt my-tap target-postgres --job_id=pg1

# Another postgres instance
MY_TAP_SETTING=def
TARGET_POSTGRES_HOST=<http://anotherdomain.com|anotherdomain.com>
TARGET_POSTGRES_USERNAME=second
meltano elt my-tap target-postgres --job_id=pg2

Those two jobs will load data from different instances of `my-tap`'s source into different postgres instances simple smile

OK, good that we can use the same system DB. I'm not sure how we will structure the projects. The underlying goal would be to (obviously) not have any secrets in the repo, and use Vault Agent sidecar in a Kubernetes deploy to populate the .env, or anything else required. We would create a service account in k8s, corresponding role in Vault, and be able to pull role-specific data out of Vault, including tap or target credentials, etc. We might be able to "reuse" a git project/container for multiple ELT jobs, but not having done this before I suspect there may be situations where that just does not work well. We will see!