Ecosystem: What features would you like to see?

[dataders]

“The most dangerous worldview is the worldview of those have not viewed the world.”
― Alexander von Humboldt

Note: This focus of this discussion is for folks building tools that integrate with dbt-core, not the entire dbt community at large. Perhaps we should start a topic just for that?

Friends in the dataverse ecosystem. In what areas could dbt-core to help better integrate with your product and make it successful?

Stable Python API? Better adapter unit tests? SQL Parser? Jinja3?

I'll post some answers I've heard from the communtiy. Please upvote and comment with more info.

If you don't feel comfortable broadcasting your hopes/dreams/desires, feel free to reach out via private channels.

[dataders]

Cross project lineage (#5244).

[dataders]

Multi-data platform projects. This is the converse of cross-project lineage. Rather than make it easy to stitch multiple dbt projects together, this is about, can I have a single dbt project with one DAG, but where model_B.sql in database B can ref a model that lives in database A.

This is similar to #5073, but different in that in this scenario there is no internal/external distinction, rather that everything becomes "internal"

[nicku33]

This would be great: at MotherDuck we'd love it for getting duckdb into the mix of existing models, but it implies tuple transport across databases via external storage. Is it within the future design scope of dbt to get into that ? Some db's are pretty good at parquet in and out of S3 without much setup.

[jtcohen6]

There's some preliminary conversation / ideation about this over in #5758 (comment)

[dataders]

more standardized Python API. Today, we strongly recommend that folks don't take a dependency on our internal API. But people do anyway. Folks access the Manifest class instead of the manifest.json. I'll add more examples later.

Some ecosystem folks have asked for this.

[lostmygithubaccount]

on this one -- tagging @amirbtb, @ma1ster as well -- what are you looking to use a dbt Python API/SDK for? one of our worries would be users defining the entire project/DAG within the Python SDK. are you envisioning this for programmatically interacting with the project after it's already built, or defining models programmatically via Python?

[amirbtb]

Today I am running dbt in a Google Cloud Run & Cloud Sceduler. I am calling the dbt CLI using a Python subprocess, but I would prefer to run dbt commands directly using a Python library.
I know there is a way to do it since dbt is coded in Python but since you recommend to not use it & it is poorly documented, I've always avoided it.

[dataders]

Actual unit testing. i.e. testing the transformation code not the data output. One component of this is running tests against static data such as csvs and jsons
#4455

[ma1ster]

Definitely using dbt as python library

[wooddar]

Hugo from Hightouch here! Will reply to my own thread if anything else comes up but the top of mind right now is definitely:

• Exposures for Analyses - Our customers are heavily using dbt analyses in their syncs and exposures created referring to analyses are 'broken'
• Other people have mentioned column-level lineage but I would specifically love dbt support for 'schema discovery' where all model columns can be added to the manifest e.g. after dbt compile and not just the ones with metadata.

[jtcohen6]

@wooddar Thanks for weighing in!

Our customers are heavily using dbt analyses in their syncs and exposures created referring to analyses are 'broken'

Is the idea here, being able to "save" a query that gets passed into the exposure? Could you imagine wanting to associate an exposure with a SQL query / .sql file, rather than the indirection of analysis --> exposure?

dbt support for 'schema discovery' where all model columns can be added to the manifest e.g. after dbt compile and not just the ones with metadata.

This information is available from catalog.json (produced by docs generate) today. Is that a no-go? What if a model's full set of columns were available after dbt run (still requires materializing the model in the database)?

[wooddar]

Is the idea here, being able to "save" a query that gets passed into the exposure? Could you imagine wanting to associate an exposure with a SQL query / .sql file, rather than the indirection of analysis --> exposure?

Relating an exposure to some generic SQL file would also be fine as long as it appears appropriately on our customers' lineage graphs once our exposures are generated?

This information is available from catalog.json (produced by docs generate) today. Is that a no-go? What if a model's full set of columns were available after dbt run (still requires materializing the model in the database)?

Interesting, I suppose we could use this though if our customers are using analyses then we would still need to manually inspect their schema...

[morsapaes]

From our side: +1 on cross-project lineage and multi-adapter runs, since it'll be somewhat common for users to deploy Materialize alongside a batch data warehouse! In addition to that, there are a couple of features that would be nice to have:

Multiplexed relations (aka one command = multiple relations)

For specific source types in Materialize, a single CREATE SOURCE command will create a source plus a varying number of sub-sources, leading to something like the following:

      "pg_source"
       /       \
depends_on   depends_on
     /           \
    v             v
 "foo"            "foo2"

There seems to be no easy way to pull these sub-sources into the dbt context. The current workflow is to manually create a bunch of .yml and .sql files to piece things together, but in real-world scenarios a single source might have 100+ sub-sources. That's a lot of files!

Existing issues and discussions we've looked into in the past that seemed promising, but didn't seem to cut it: #5100, #5101, #2716, just hacking the graph context variable (👹).

Runnable exposures

In the same way that it can ingest data from external systems, Materialize can also sink data to external systems. Exposures sound like a great way to model this (with a side of external nodes?), but the initial implementation has a few caveats for our specific needs: 1) it isn't possible to use a custom string as the exposure type; 2) exposures aren't compilable or runnable, and a sink requires that we ship a CREATE SINK statement to Materialize.

We'd just really love to not see any raw DDL statements in a dbt-materialize project, but can definitely live with the current state of having custom source and sink materializations! We wrote some musings around this a while back, and explored some of the proposed options with no luck!

[jtcohen6]

Yes & yes!

As far as a guide for the multiplexed, I have one more discussion to throw your way: #6185 (update: I previously linked to #5686 and #6067, which could still be related older reading)

As exposures go, it is simply so clear to me that they ought to be our way of modeling syncs & sinks, and that they need to actually do a thing. That thing might be SQL (create sink) in Materialize, or a webhook / API call in other cases (#5073 - you know what link this is, you don't even need to hover).

[morsapaes]

Thanks, @jtcohen6! I'll hop on #6185 for comments specific to multiplexed sources. And great that we're aligned when it comes to exposures!

While reading through the helpful links you leave in your trail, I realized that I'd missed #5686. The ideas in there line up really well with an issue we have out for dynamic unions. In the context of dbt, this would be useful to handle the (possible disparate amount of) materialized views that are created as a result of our take on --store-failures (aka continuous testing).

[hovaesco]

Hi @dataders

It would be nice to have improved test coverage for dbt-tests-adapter, like to have ConnectionManager integration testing without the test having to invoke dbt.

[joshuataylor]

I would love to see column types be settable in the YAML, so you can say "this is a number(38,0) DO NOT CHANGE IT". I hate hardcoding the column types in SQL like something::number(38,0) as something (it's a pita).

[sungchun12]

Well, would you look at that. We're working on that as we speak :) #6079

[joshuataylor]

WOOOOOOOOO

I'm happy to help contribute and test the Snowflake portion.

[cpstein]

On the Hightouch side, we have an important request for a bugfix!

The issue is detailed in: dbt-labs/dbt-snowflake#112.

As flagged in the Github issue, truncate and insert commands currently run in different transactions. This is particularly problematic for Hightouch users who use dbt (esp with frequent syncs like every minute) as there is a chance that Hightouch will try to sync after the table has been truncated, but before data has been inserted into it, causing Hightouch to not sync the intended data correctly (including syncing data that should not be synced to a destination!).

[jtcohen6]

@cpstein Thanks for flagging! That issue should only be affecting tables produced by dbt seed (CSV uploads). Is it common for dbt + Hightouch users to schedule syncs of data produced from seeds? That's not something I would have expected, though it is technically possible

[cpstein]

@jtcohen6 Probably not the most common use case, but we have seen a couple cases of this.

[virajmparekh]

Hey all! From Astronomer's perspective:

• I think the biggest thing we'd like to see is a a first class python library. A lot of our customers have to dance around breaking changes with python dependencies in their Airflow DAGs to get things to work, and many have even opted to run each dbt model in its own container to get around this. I don't know if this is possible, but it'd be cool if a single PyPI packaged allowed users to call the cloud api as well as use core.

• Additionally, we'd love an interface to getting lineage events from the core package. We have users that wrap the core package and run a modified command to consume those events - a first class way to support that would be awesome.

Love the public nature of the discussion here!