1. Meltano supports dbt plugins the same way it supports taps and targets, e.g.

dbt-tap-gitlab

. I understand why it works this way based on the previous design goals of Meltano. However I'm not sure it's the best idea to use them from a maintainability perspective: • the transformed tables the plugin generates are hidden in a plugin and not evident by looking at the code. The dbt project isn't just a transform step; it also acts as documentation for what data is ready to be consumed • the conventions of the plugin may not match those of your organization (think column names, data types, etc.) • you don't have control over the materialization method (at least as far as I could tell), you just get whatever the plugin author picked • you don't have control over which models get generated (at least as far as I could tell) Based on this, we decided to go through the process of implementing every model in dbt ourselves. This acts as an indication that the model has been validated and is ready for consumption.

2.

meltano elt --transform=run

runs the entire

my_meltano_project

dbt project, even (possibly expensive) transforms that were not effected by the EL pipeline. To work around this, we deleted

my_meltano_project

and made model subfolders that matched the pipeline name. Then we use the

DBT_MODELS

env var to override the models to be run by setting it to the pipeline name. For example,

DBT_MODELS=gitlab meltano elt --job_id=gitlab --transform=run tap-gitlab target-postgres

. This is effectively the same as

dbt run -m gitlab

.

3. Meltano will run dbt models with

meltano elt --transform=run

, but not seeds, snapshots, etc. We are working around this by running

meltano invoke db seed

when seeds change, and having our scheduler execute

meltano invoke dbt snapshot

after each

meltano elt

.

4. There's a bit of a disconnect by invoking

--transform

as an argument to

elt

- there are actually two data destinations here, but we only specify one. Consider this (common, IMO) use case: - use

tap-mixpanel

to put all raw data on s3, where it is cheap to store and query with Athena - use dbt to aggregate the data from Athena into more managable amounts of useful data to be stored in the data warehouse Conceptually, there are actually 3 data sources here: Mixpanel API -> S3/Athena -> Postgres As far as I can tell, Meltano tries to use the target to determine the destination database that dbt should use, but in this situation the transform destination database is different from it's source. The workaround here I think is to run dbt manually with

meltano invoke dbt

, but we aren't quite there yet so I don't have the exact command. It might make more sense to invoke this command with 3 arguments? e.g. something like

meltano elt [extractor] [loader] [transformer]

or to invoke them as 2 separate commands, e.g.

meltano el

and

meltano t

I appreciate that meltano positions itself as a single point of entry to 1) extraction, 2) loading, 3) transformations (dbt), and 4) orchestrations (airflow), but in doing so I also see the potential for the meltano governed workflow to rigidly adhere to that exact sequence without flexibility towards things like multi-step loading sequences. I’m still learning meltano myself so my own ignorance could very well be a factor here.

@julian_knight - Are you currently treating your whole workflow as an ELTELT sequence ?

It would be interesting to characterize this two-step model a bit further. It’s kind of like a Data Lake + Data Warehouse model.

I guess that makes more sense actually, now that I think about it

That does seem like an interesting approach.

With Meltano's focus on excellent EL, this fits nicely

@julian_knight Some thoughts on the specific things you've brought up: 1. Transform plugins are primarily useful in combination with Model and Dashboard plugins, so that a new Meltano user can go from data to dashboard in moments, just by adding the relevant plugins and opening up the UI. They're much less relevant to data teams like yours, who want more control over their transformations. I already created an issue to add docs on how to use dbt with locally defined models, rather than packaged ones: https://gitlab.com/meltano/meltano/-/issues/2324 2. I'm glad you found

DBT_MODELS

! That's exactly what it's there for 🙂 It will also have to be documented explicitly. 3. Do you think it would make sense for Meltano to run seeds and/or snapshots automatically? 4. As you've found, Meltano currently assumes that the loading and transformation-target database are the same. If that's not always going to be the case, we should definitely support that explicitly and not make it feel like you're fighting against the tool. Having

meltano elt

take an explicit transformer argument is related to what I was pondering here: https://gitlab.com/meltano/meltano/-/issues/2300#note_400291810. I'd love to hear your thoughts there or in a new issue!

I appreciate that meltano positions itself as a single point of entry to 1) extraction, 2) loading, 3) transformations (dbt), and 4) orchestrations (airflow), but in doing so I also see the potential for the meltano governed workflow to rigidly adhere to that exact sequence without flexibility towards things like multi-step loading sequences.

@andrew_stewart We like "Convention over Configuration" for common scenarios, but flexibility is also a priority, as evidenced by the fact that @julian_knight was able to tweak some of the unwanted default behavior using env vars and explicit

meltano invoke

calls 🙂 Meltano is built for and by its community of users, so if multi-step loading sequences are something our users are going to want, we'll find a way to support it and still make it feel intuitive and consistent with the rest of Meltano! The intention is for Meltano and your team's Meltano project to stand the test of time and evolve with your team and the data space, in terms of features and approaches it supports, and specific plugins it integrates with, so whatever it can do today should not be seen as a limit on what it may be able to do tomorrow.

Then Spectrum inside of Redshift to expose the data into Redshift.

@adam_roderick Thanks! I wasn't familiar with Spectrum. I love the elegance of that pipeline. Perhaps we will start migrating to RedShift.

One more thought I had that I forgot to post about: If you want to make database changes that don't fall into a pipeline, you're on your own. Perhaps a table or schema got renamed, or you explicitly wish to add indexes to a particular column. We'll probably set up version-controlled migration e.g. alembic or similar so that we can share and version control additional database changes. This could be something Meltano comes with out-of-the-box support for in the future, given I think it's a pretty common use case.

I think the two-step process we mentioned above is probably a very appropriate way to combine an s3/athena “data lake” step with a more conventional DW loader step.