Feature Request: External Nodes

[tconbeer]

Continuing a conversation started with @jtcohen6 and @drewbanin out here in the open so we can get more input from the community before formally creating an issue.

Describe the Feature:

The dbt DAG is composed of Nodes. Today, those Nodes must be computed inside the data warehouse (which typically limits them to being expressed in SQL, although UDFs provide some work-arounds).

However, it is common for data teams to have data processing jobs that run outside of the warehouse that need to have a smooth handoff with dbt (in order to run before and/or after dbt runs). Such use cases include machine learning, custom Slack notifications, operational analytics, data anonymization, and more.

We propose a new Node type, called an External Node, which is a placeholder in the dbt DAG that can trigger a data processing operation in a system other than the data warehouse. We think this is an extension of and complement to the current trend to bring all types of workloads into a SQL-based workflow.

For an External Node, instead of compiling SQL and sending a query to the data warehouse, dbt compiles a POST request that hits an external server. The server would respond with a URL that could be used to poll the status of the external job. dbt would poll that server and wait until the job completes to run downstream nodes (or times out and the dbt job fails).

There are some important details for this feature, and we're looking for community input on those details:

1. How would dbt authenticate with the external service?
2. How would we configure this external node in dbt?
3. What is the protocol for the requests made by dbt and the responses from the external service?

Describe Alternatives to this Feature

The primary alternative is to use a full-featured data orchestrator, like Airflow, Dagster, or Prefect. These tools do an excellent job for this use case, but can themselves be difficult to deploy and manage, and typically require knowledge (languages, engineering practices, etc.) outside of the analyst skillset. We are not looking to replace orchestrators, but rather obviate the need for one for small teams with simple workflows and needs that are closely tied to dbt.

For those types of teams (that cannot or do not want to deploy an orchestrator) there are several ways to hack together a solution today:

1. Implicit coupling: align schedules, {{ ref() }} hack and/or sources + exposures for documentation purposes. This does not scale to multiple environments and creates maintenance issues down the road
2. Define a UDF/external function/lambda and use that UDF in model SQL. This is not well-supported in all warehouses, and takes considerable engineering and config in the warehouse, so it is not a great experience as an integration with a SaaS product.
3. External jobs poll for changes to database relations, e.g., select max(updated_at) from source, and optionally use dbt Cloud’s API to run downstream jobs. This can work well for organizations that use dbt Cloud, but requires the external service to build this capability natively (or the use of a third-party orchestrator, above), and similarly does not support lower environments well
4. Use a lightweight orchestrator (i.e., a shell script or GitHub Action) to run dbt and the external job in sequence

Who Will this Benefit?

1. dbt users who wish to better integrate their in-warehouse and ex-warehouse workloads, especially teams that do not use or otherwise need an orchestrator
2. Other vendors in the dbt ecosystem who will be able to offer an improved customer experience for dbt users

[tconbeer]

1. Authentication

We propose dbt use an OAuth 2.0 Client Credentials Flow for authentication with the external service. This would require the user to create a "client" in the external service ahead of time and provide a client_id and client_secret in their profiles.yml file. In addition to being a common and standard mechanism for authorizing machine-to-machine access, dbt already supports the Client Credentials Flow for its BigQuery adapter.

dbt would be responsible for fetching the token from the authorization server, including it on requests to the external service, and caching it for re-use (possibly per-run) to avoid having to request numerous tokens in a short window of time.

The required configuration could live in a profiles.yml file (or in the UI in dbt Cloud, eventually). Config should be target-specific, in case different authorization is required by the external service in dev and prod environments. A proposed profiles.yml data structure:

my-profile-name:
  target: dev
  outputs:
    dev:
      type: bigquery
      ...
    prod:
      type: bigquery
      ...
  external_services:
    - name: my_external_service
      dev:
          method: client-credentials
          auth_method: [one of form_post or basic_auth]  # client_id/secret in request body or header
          client_id: [dev client id]
          client_secret: [dev client secret]
          token_uri: [redirect URI]
          extra_headers: {} # additional http headers for the token request
          extra_params: {} # additional request parameters
      prod:
          method: client-credentials
          auth_method: [one of form_post or basic_auth]
          client_id: [prod client id]
          client_secret: [prod client secret]
          token_uri: [redirect URI]
          extra_headers: {}
          extra_params: {}

[sungchun12]

I know this discussion is biasing towards a generalized framework for external services, but the core use case I see among people that use dbt is uniting dbt with the EL workloads synchronously. Assuming the above profile config, I'd want to add an example config like below to refresh my sources.

# add a flag to explicitly run the EL jobs in scope
dbt build --select source:tpch+ --external_services 

# granular flag options for a specific EL job
dbt build --select source:tpch+ --external_services fivetran.job_abc

# does NOT run EL jobs in scope, only focuses on the T in ELT, current behavior
dbt build --select source:tpch+ 

# run all EL jobs with all models
dbt build --external_services

# sources.yml 
version: 2


sources:
  - name: tpch
    external_service: fivetran # OR Airbyte, Stitch, etc. essentially the name of the external service for basic authentication
    external_service_params: job_abc # name of the actual extract+load job to run that builds these source tables
    description: Welcome to the dbt Labs demo dbt project! We use the [TPCH dataset](https://docs.snowflake.com/en/user-guide/sample-data-tpch.html) to create a sample project to emulate what a production project might look like!
    database: raw
    schema: tpch_sf001
    tables:
      - name: orders
        description: main order tracking table
        freshness: # make this a warning as this is static data
          warn_after: {count: 6, period: hour}
          # error_after: {count: 12, period: hour}
        loaded_at_field: o_orderdate::timestamp
        
        columns:
          - name: o_orderkey
            description: SF*1,500,000 are sparsely populated
            tests: 
              - unique
              - not_null
                  
          - name: o_custkey
            description: Foreign Key to C_CUSTKEY
            tests:
              - relationships:
                  to: source('tpch', 'customer')
                  field: c_custkey
          - name: o_orderstatus
            description: status code of the order
          - name: o_totalprice
            description: raw price

[jwills]

I am personally sympathetic to this use case; I've often thought about the fact that all of my EL jobs have to finish before my dbt jobs can start running, which is suboptimal since a lot of my tasks that target small and/or rarely updated raw tables should be allowed to start running before some of my large and/or frequently updated tables have finished their EL. In terms of hitting service-level objectives for landing all of the processed data in the data warehouse, not having to block everything on the long-tail EL tasks finishing could be a significant win, esp. if this info was encoded nicely in the dbt run spec, like it is here.

Really interesting idea here @sungchun12, thanks for posting this!

[martanthony]

I'd like to take this suggestion a step further and propse the ability to trigger EL pipelines that feed a particular exposure, perhaps even leveraging some flavour of freshness check to determine whether or not a given pipeline executes for any given source in the DAG, along the lines of dbt build --select +exposure:some_exposure --external_services.

I'm aware that discussion on this sort of feature set starts moving dbt into the realms of an orchestration service, which might or might not be on the product roadmap. If it is, then there might be use cases out there for being able to issue an instruction to dbt along the lines of 'I want to refresh some exposure, so make sure its sources and models are up to date beforehand'; if not, and the orchestration of pre-/post-dbt actions should remain outside of dbt, there might still be use cases for an external orchestrator to make a call on the DAG along the lines of 'I want to refresh some exposure, so what EL pipelines do I need to run to update its sources before I run the models that feed into it?`

[jtcohen6]

I'm very sympathetic to this use case. It's something we see lots of folks asking for, and it's compelling to think about dbt taking a "back-to-front" approach to the problem, rather than having to poll constantly, or wait for an upstream webhook. In both cases, the run cadence is still at the mercy of the ingestion tool, not motivated by when and where you actually want the data (in the exposure!).

My goal in this discussion is to unlock a few compelling, opinionated use cases, with (as Josh put it) clear reference implementations from the outset. I do not want to rebuild Airflow. A totally general-purpose service orchestrator, running at any point in the DAG—the ingestion layer especially—feels a little close to comfort.

It is already possible for dbt to tell an external orchestration tool the set of sources that need refreshing, via:

dbt list --select +exposure:* --resource-type source --output json --output-keys source_name,name,database,schema,identifier

If there is already an Airflow-like thing in the situation, it's trivial enough to take that information, trigger the upstream sync in an EL pipeline, wait until finished, then notify dbt to keep running. If there isn't one, I'm not so sure that building such a capability into dbt would be so trivial.

[jwills]

Hey y'all, spent a few days thinking about this, the ways in which I might use it, and what the best way to go about implementing it would be. I see a lot of relationships with these external nodes and the future features list for exposures, and I could easily imagine wanting to use them in conjunction with one another.

I very much agree with the idea of having a services set of named entities that could be defined inside of the dbt profile (along the lines of the above example) that would be configured and made accessible to the jinja Environment that the dbt code executes within, similar to how the adapter is made available today. A service would be named, and would likely expose the common http methods and an API that looked essentially identical to a requests.Session object, with the constraint that you can't specify the host/port bits of the URLs (those are defined by the configuration of the service inside of profile.yml). Because I suspect different services will want to set themselves up in different ways, there should be a Service contract (akin to the contracts for the different adapter impls) that each cloud service can implement to make themselves configurable as they see fit based on whatever info they want in the profile config.

Beyond that, I'm inclined to leave the rest of the external node stuff more-or-less unspecified for the time being, although it would be great to add a hook option to the top-level exposure object in order to make exposures into a "runnable" object (in a fire-and-forget sort of way) taking advantage of the service hooks. My reason for that is that I don't think there is any upside to being prescriptive about how the external node code should work right now-- e.g., there are so many different things that need to be considered:

• How long should the wait time and polling interval for a request be?
• How should failures be handled? (Timeouts vs. 500 errors, etc., etc.)
• Should we log the output of an external node call/run to a table? If so, what info about the request/response cycle should we log?

...not to mention how different cloud services name parameters, prefer the data to be encoded, etc., etc., and also of course the fact that we should be mindful that end users who are running dbt themselves would almost certainly want to be able to define their own services and integrate dbt with their own, non-public systems, and I think that all of these questions can be handled by a suitable set of macros, and I think we could figure out a good set of patterns to use here by doing a couple of reference implementations (e.g. for Slack, PagerDuty, one of the various cloud-based logging services, etc., etc., etc.)

[jtcohen6]

@jwills I really really agree. I found myself thinking the same thing: exposures, external nodes. It can't just be my latinate brain that wants to see these two things as closely intertwined.

So: for right now I'm very interested in thinking hard about"external orchestration" capabilities, alongside my other long-sought-after desires for exposures (moved to #5106), with the possibility of tackling a subset of that work later this year (~v1.3). I like the idea of making exposures optionally runnable, or really having hooks that are runnable.

What's the target use case? I've been thinking about this mostly as a way to enable external services that are truly "mid DAG": reading in dbt model data out of the database, writing back to the database, for use by downstream dbt models. I've been thinking less about out-of-band orchestration, e.g. custom Slack notifications, where it feels like the best tool that dbt-core can provide is rock-solid reliable structured logging for an orchestration tool (Airflow/Dagster/dbt Cloud/etc) to act upon.

So, if the exposure / external node writes back to a database object:

• How to "ref" that object in downstream dbt models? We need more than just the right DAG ordering here, we need a database.schema.identifier location in the database where the externally produced object is to be found. Or potentially multiple: I understand some services, especially around data science / ML automation, run one "model" that produces multiple objects with different schemas (predictions, model performance characteristics, other metadata, ...)
• Environments. Do we need target-based or env_var-based logic for whether the exposure's hook should actually run? Should we expect the external service to write back to different database locations for dev/staging/prod?

[jwills]

@jtcohen6 on the point about the out-of-band orchestration, I hear you; there is no doubt that a proper orchestrator can create a much richer and accessible version of things like custom Slack/PagerDuty notifications, which is a good argument for not including them as reference impls of services (ala the way dbt-postgres is a reference impl of an adapter) in dbt-core. At the same time, I wouldn't want to disallow the use of services for this sort of out-of-band functionality either-- for better or worse, proper orchestrators take time and/or money to set up, and that cost is probably not one that I want to pay so that I can add a minimal amount of notification/alerting functionality to my data pipelines before I go home and cook dinner and do my taxes and play with my kids.

On the question of ref-ability, I like what @tristanz said below; embedding the use of a service inside of a custom materialization will provide the database.schema.identifier we need for downstream processing. It also looks like the materialization macro has allowed returning multiple output relations for awhile now, although I don't know how to refer to those individual relations in downstream models?

On the question of environments, my feeling is that either target or env_var based configuration for services would work; my sense (not having done this sort of thing before) is that the target based approach is more verbose but flexible and the env_var approach is more concise but possibly a bit obscure? I don't have a sense for how y'all make these sorts of tradeoffs in the project (like if you default one way or the other.) In either case, I think the code required to switch between the two different approaches is small enough relative to the overall effort here that we can defer the decision for later and/or just try both out and see how each feels to work with.

[tconbeer]

I do think @tristanz's materialization-based approach handles environments quite well, since you get support for multiple targets out of the box (with further customization as desired), and dbt will know the fully-qualified relation name when it comes time to ref this thing.

[sungchun12]

@izzye84 FYI

[tristanz]

So first off, I'm a big +1 for external nodes. This would help a lot with ML use cases and smooth out the dbt integration flows for services like Continual. I'm sure the same is true for other services too.

Building on what @jwills says and his proposal, there seems to be a bit of overlap between this idea of external nodes and custom materializations. You could argue that services like FiveTran, PrivacyDynamics, and Continual are all examples of custom materializations that rely on external services. They basically just materialize tables in a fancy way.

In fact, if you need this "internal node" behavior today, the best way to solve it is custom materializations that leverage remote/external UDFs in the underlying platform. This is what dbt_ml project does today, although it's very specific to BigQuery and BigQuery only is just adding support for remote UDFs.

To play out some examples, for Continual there are many different ways you could express ML workflows as a custom materializations, but a stylized example is:

---churn_model.sql
{{ config(materialization="continual", task="train", target="churn" }}
SELECT id, feature1, feature2, churn FROM

---churn_predictions.sql
{{ config(materialization="continual",  task="score", model=ref("churn_model") }}
SELECT id, feature1, feature2 FROM users; 

For PrivacyDynamics, you could do something like:

---anonymous_data.sql
{{ config(materialization="privacy-dynamics",  task="anonymize" }}
SELECT * FROM sensitive_data

Unfortunately, remote UDFs are not universally supported and are difficult to implement across all the different platforms.

If the above makes sense conceptually, then @jwills's proposal would enable much more powerful custom materializations. By giving the Jinga Environment web request superpowers, you can write customer materializations like the above in a cross-platform way without relying on remote UDFs being available.

A critique could be that these platforms are all adding remote UDF support and perhaps time is better spent making it easy to manage UDF code generally.

[jwills]

@tristanz good to see you friend-- this is not an approach I had considered to implementing this kind of logic b/c I had it stuck in my head that dbt materializations were owned by the adapter impl (ala dbt-snowflake, dbt-duckdb, etc.), but what I think you're saying here is that isn't actually true; like it would be entirely possible for someone to create a dbt-package that included a macro like:

{% materialization my-cool-service, default %}
  # do a bunch of service calls, etc., etc.
  {{ return({'relations': [target_relation]}) }}
{% endmaterialization %}

...and implement it in an adapter-independent way (or if necessary, with specialized impls for adapters that had known-good UDF support or whatever) and it would Just Work ™️ . Am I following along with what you're saying correctly?

[tristanz]

Yes, exactly.

[jtcohen6]

Without throwing too much of a spanner into the works...

@tristanz I'm trying to follow the relationship between churn_model and churn_predictions in your example above. Do you see dbt passing along SELECT id, feature1, feature2, churn FROM {{ ref('users') }} as the input to a Continual model, tell Continual to train it, and then to write back with the predictions to analytics.dbt_jcohen.churn_predictions? I'm picturing a request body looking like:

{
    "query": "SELECT id, feature1, feature2, churn FROM analytics.dbt_jcohen.users",
    "target_relation": "analytics.dbt_jcohen.churn_predictions"
}

I've been hesitating to treat "external nodes" as models, even ones with a custom materialization, because they're not actually defining a transformation. I think we should aim to distinguish between "logic-bearing" nodes (models, defined as SQL files) and "pointer nodes" (sources, exposures, defined as configuration). To that end, I really don't like when a model is just a thin wrapper for some hook, operation, or DDL:

-- models/my_model.sql
{{ run_my_external_function() }}

I also think it will be important to clearly call out where in the DAG there's a real external process participating in the flow. This should be more than just a model post-hook (external function call today), or a few lines of code buried in the materialization a la persist_docs. I've been visualizing that as a different node type, and color in the DAG ("exposure orange").

If I had to represent that in pseudo-code:

exposures:
  - name: churn_model    # this uniquely identifies the thing in dbt, does it uniquely identify it in Continual too?
    type: ml
    description: Predicts churn for users
    url: https://continual.ai/myaccount/console/churn_model    # for visibility / debugging
    service: continual   # configured in profiles.yml
    depends_on:
      - ref('users')  # includes relevant columns in ready state, otherwise could be 'prep' model
    owner:
      name: 
      email: 

When I've successfully built the users model in production, dbt would notify Continual that it's ready to train (if appropriate), and to write out predicted values as well, in addition to any metadata.

-- models/downstream_model.sql

select
    user_id,
    churn_probability,
    ntile(churn_probability) as churn_ntile,
    ...   

-- new 'exposure' method (they can't be ref'd today)
-- since Continual writes back multiple tables per "model,"
-- perhaps this could take an argument for which one?
from {{ exposure('churn_model', 'predictions') }}

This feels right to me, but some of the implementation details are definitely fuzzy:

• How does dbt know to "execute" this exposure (not normally an executable node)? I think any exposure with a service defined should be considered executable, and triggered by dbt build. Equivalently, it should be possible to "disable" the service in environments where you don't want it to run, without raising a DAG error because another model's parent is now missing. That could be a bit of env var Jinja logic, or it could be conditional based on whether the continual "service" is defined / enabled in profiles.yml.
• How should dbt instruct Continual where to write the model, or find out from Continual where the model was written? (That's what should be rendered by {{ exposure('churn_model') }}). My instinct is to treat the "target" location of an external services more like snapshots, and less like models: I'm not convinced that dbt actually wants to trigger these external services in all runtime environments, until/unless I've made a change to an upstream resource that I need to test. By default, in dev, I'd just want to use the version of the Continual model last written to the production namespace. Does that sound right, or am I off-base?
• One detail I do like: By defining it as an exposure, in yaml config, all of the request work can happen in Python-land. I super agree with @jwills' comment below about keeping as much of this as possible outside the Jinja environment, where even well-meaning people can find themselves doing unwelcome things. We're very careful today about how we expose the database/adapter connection to the Jinja environment today, and in a way that intentionally abstracts over differences between databases.

All in all, this isn't a hill I'm looking to fall on, so I'm very open to hearing that the model + custom materialization approach feels right-er. It's definitely the quicker and easier way to start MVP'ing.

On the other point Tristan raised:

perhaps time is better spent making it easy to manage UDF code generally

Definitely! I want to take this on, around the same time as "external nodes," as a related initiative. I don't see the two overlapping in most of their behind-the-scenes implementation details, but they may be complementary as tools in the tool-belt available for solving this category of problem.

[jwills]

@jtcohen6 this is great, thank you for a) spelling out what you meant re: envisioning external nodes as ref-able and executable exposures and b) giving me an opportunity to refresh my middle school English class lesson about the differences between alliteration and assonance.

In my reading of your write-up, the ref-able and executable exposure solves a couple of different problems with the materialization-based approach:

1. It avoids the murky semantic questions of what exactly the SQL in an external_node.sql file is doing (or if it should even be there at all?), and
2. It cleanly handles the fact that running an external node can generate multiple ref-able objects.

Two other complications/concerns with an exposure-centric approach that I want to call out in addition to the three you mentioned:

1. In order for the exposure to do all of the work of coordinating with the external service, I think we would need to be fairly prescriptive about how the interactions with the service would work. Like, in my head, the common case here would be that we would need to send off a configured POST request to the external service (possibly with params, or form-data/JSON data, etc.), extract some sort of token back from the POST response, and then poll a second endpoint on some cadence with that token to get the current status of the job-- which means we need to know how to construct that request and then parse its output. And then within that common case, there are still a bunch of decisions we have to make about what sort of timeouts we should set and how we should handle various kinds of connection failures. To make that work, we would either need to be very prescriptive about how these external node endpoints were implemented (i.e., what stuff should be named, where parameters should be passed, etc.), or I we could write the YAML for the exposure in order to specify all of these choices via configuration, but I suspect that it's going to feel a lot like we're doing so much config that we're effectively programming in YAML, which I don't think is what we want. The two ways I can think of handling this are either a) a bunch of macros or b) making the Service abstraction a bit more heavyweight (more akin to an Adapter) so that it can implement more of this request flow logic in Python. Do you have thoughts on either of these options, or perhaps a Third Way?
2. Riffing on 1, the other virtue of the materialization macro strategy was that you could make installing the logic required to handle the request flow installable as a dbt package, which in my head I had thought of as a nicer developer experience (with less possibility for dependency hell esp. as the dbt-utils cross db stuff migrates into core/adapters) than making the Service stuff into something that needed to be pip install'd.

A couple of other stray thoughts: first, having the dbt core roadmap doc is incredibly useful for this discussion because it reminds us that python-models-in-dbt are Coming Soon ™️ and that the approach and feedback that receives may well inform what the right thing to do is for handling external nodes; I don't think that we should prematurely limit ourselves to materializations vs. exposures as the way to do this.

Second, and following on the first point: we do have some time to figure this out, and we should feel very comfortable with the idea that figuring it out is likely going to require writing some throwaway code (in my experience, all design decisions are easy until fingers touch keyboard, and then folks can become very attached to their approach.) This is one area where I think product designers who have focused on making their crit process more positive are working together better than we software folks do, and we should try to go out of our way to emulate the best-of-the-best from the design world.

And with that, I am now very curious to hear other takes from the folks in this discussion, so I will shut up for a bit.

[jwills]

This has been a delightful and invigorating discussion y'all-- many thanks to @tconbeer for kicking things off here.

I feel like the next step here is to do a minimum viable implementation of a Service construct that 1) could issue a specified list of web requests and return the associated responses, 2) would be available for use in the Jinja Environment, and 3) would be configured inside of the profile YAML file.

I think that as a strawman, I would want to have a default Service impl that could be configured entirely in dbt from a suitably specified YAML file and that would only support HTTP basic auth out of the box (if y'all want to twist my arm on supporting OAuth 2.0, please say so now.) The virtue of this approach is that I think we could quickly get a sense of what this felt like to work with before the Services API got locked in in such a way that external dbt packages could do more complex/powerful implementations.

I feel like I see the shape of what this looks like in my head, so I'd be happy to take a crack at doing this strawman on my fork of dbt-core so that we can converse with one another in code as well as prose; I think I could have something substantive for us to discuss by this time next week unless someone would like to volunteer to beat me to it.

[tristanz]

What's the motivation for not allowing host/ports to be specified and being prescriptive about the auth scheme? It seems to expose the protocol implementations to users during configuration and be potentially limiting for service providers. The alternative would be to just expose request.Session without limitations.

I understand the desire to limit access to dbt Cloud internal services/ports, but shouldn't that be done at the container, network, or backend services level? Even limiting hosts doesn't prevent me from pointing my own DNS domains to dbt Cloud internal IPs. And trying to enforce the firewall at the Python level by doing IP lookups or whatever seems fragile and potentially racy.

For instance, I was thinking something more like:

my-profile-name:
  target: dev
  outputs:
    dev:
      type: bigquery
      ...
    prod:
      type: bigquery
      ...
  services:
    continual:
      dev:
        key: your-auth-token
        project: customer_analytics
        environment: dev
      prod:
        key: your-auth-token
        project: customer_analytics
        environment: prod
    slack:
      dev:
        oauth-token: blahblah
        room: dbt-alerts-dev

The dictionary under the services key would be left unspecified and could be consumed in the Environment as desired.

[jwills]

Sorry, is the question to me?

[tconbeer]

I like @tristanz's "less prescriptive is better" approach. It seems to me that auth schemes will have to be defined in the Service itself, so assuming integrations take the form of a dbt package with macros that use the Service (and not a python package, like adapters), then over time we'd want the Service to support more than just basic http auth. But to Tristan's point, it's probably "both" and not "only OAuth"

[tristanz]

@jwills haha, yes, although it's just a general question. I'm wondering why not just expose request.Session to the Environment and be done with it. It's unclear to me what problem is being solved by limiting host/port or specifying the auth scheme and doing so has downsides in terms of flexibility and the end-user experience.

[jwills]

I assume we don't do it for the same reason we don't e.g. make the DWH credentials and a DB connection available to the Environment- 1) to prevent otherwise well-meaning people from inadvertently logging/printing things that are supposed to be secrets and 2) because executing these auth/query flows is easier to do in Python code than in a macro. Doing the auth credentials via the profile config doesn't seem any more onerous (tbh, it seems substantially less onerous) than what folks already have to do in order to e.g. connect to BigQuery or Snowflake.

I think the same principle applies here: we can make the security team happy by keeping secure stuff, well, secure, and I think we can provide a better developer experience than what you would get from doing a bunch of URL construction and request.Session calls inside of a macro.

[jwills]

Okay, so it's been six days since I posted that message above, and I built something. If you happen to be intimately familiar with the internals of dbt, you can take a gander at it here and it might even make sense to you, especially if I tell you that I liberally cribbed from the contracts/ -> config/ -> RuntimeConfig pipeline that is used for parsing/configuring the complex selectors from selectors.yml. It isn't good code, it's not something that I would ever submit as a PR, but it does actually work in a limited form, and it's the start of something in this space.

If you are using my branch above, and you have a dbt project that includes a file named external_services.yml that looks like this:

services:
  - name: slack
    base_url: https://slack.com
    auth:
      token: "{{ env_var('DBT_ENV_SECRET_SLACK_TOKEN') }}"
    endpoints:
      - name: test
        path: /api/api.test
        method: POST
      - name: post_message
        path: /api/chat.postMessage
        method: POST
        args:
          - name: channel
            type: body
            required: true
          - name: text
            type: body
            required: true

...then you can write a macro that looks like this:

{% macro slack_test() %}
{% set resp = service('slack', 'test') %}
{% do log("Got response " ~ resp.text ~ " with status code " ~ resp.status_code, info=True) %}
{% endmacro %}

...and if you execute it using run-operation, you will get back something that looks like this:

(env) jwills@wg-josh-mbp-01 dbt-duckdb-test % dbt run-operation slack_test --profiles-dir .
04:52:06  Running with dbt=1.2.0-a1
04:52:07  Got response {"ok":true,"args":{"token":"*****"},"warning":"missing_charset","response_metadata":{"warnings":["missing_charset"]}} with status code 200

...assuming you have set a DBT_ENV_SECRET_SLACK_TOKEN to a valid Slack token for a workspace you're in, and if you do something like:

{% macro slack_post() %}
{% set resp = service('slack', 'post_message', channel='#general', text='hello from dbt') %}
{% do log("Got response " ~ resp.text ~ " with status code " ~ resp.status_code, info=True) %}
{% endmacro %}

you can annoy your friends and (soon to be former) coworkers by posting bot messages from dbt in #general!

So, there's definitely lots of stuff to hate about this. One thing I hate about it is the idea of having a single file named external_services.yml where all of your external services have to be defined; having written this, I've warmed up to the idea of having a .yml file per service (so there would be slack.yml, continual.yml, privacydynamics.yml, etc.), and it would be possible for those external services to be declared inside of dbt packages at some well-known location (akin to macros or models or one of the other path definitions in the project.yml) so that I could just import the dbt_slack package and I would get a handy service definition and a bunch of macros that were already designed to use that service definition to do useful things for me. I didn't really realize that I was going to like that better until I had already written a big chunk of that code though, so I decided to just roll with it and post my thoughts to that effect here.

And so while there is definitely lots of other stuff I hate about the code (there's no testing/logging, the auth setup is super basic right now, my understanding of typing for dataclasses is pretty limited, having to write an API spec in YAML is...well, actually, that sort of seems like a thing people do nowadays, nevermind then), I should probably shift gears into selling mode and tell you what I like about this approach.

1. For one, I like that I, an analytics engineer, don't have to pip install anything and get into some sort of dependency hell-type nightmare around e.g. conflicting versions of httpx so I can use web services from dbt.
2. I like that I have gone out of my way to not give the Jinja context direct access to requests.Session (just try to get at it! You can't!) which I hope makes it easier for dbtCloud to allow their customers to use whatever web services they choose with minimal verification/security checks and definitely without having to talk to an account executive in order to get it done.
3. I like that I can do compile-time checking on the arguments you pass in to the service to confirm that a) it is a real service that b) supports the endpoint you want with c) the arguments you specified.

And with that, I'll wrap it up. I'd like to reiterate that although my fingers have touched the keyboard, I do not take any criticism of this approach personally; I understand that in trying to split the difference between the needs of the web service vendors and my own imagination of what it is analytics engineers really want out of webhooks in dbt, it's entirely possible that I have created a monstrosity that will neither please nor satisfy anyone. Regardless, this was super-fun to build, and I would do it again in a heartbeat. <3

[jtcohen6]

(a bit more thought-gathering later)

An old question that feels new again: For the "dataset"-producing models (DPMs), what really is the difference between a “service” and an “adapter”?

That is, why shouldn’t there be a dbt-continual adapter, that I can use alongside dbt-snowflake? Continual and Snowflake already know how to talk to each other. if dbt knew how to talk to both of them, wouldn’t the relationship that dbt has with Continual look a lot like the relationship it has with Snowflake? Is Continual just another “execution environment” for logic written in and orchestrated by dbt, which happens to still be executed (indirectly) via Snowflake?

I don’t want to force a comparison where it isn’t warranted, but when I squint, I see a resemblance. Should we look to develop software constructs accordingly?

Why I like this

The most important technical features of adapters feel relevant here:

• They’re written in Python, not Jinja/YAML. That’s the right (real) language for handling requests, retry, authentication (oauth!!), …
• They can interact more directly with dbt classes, including result objects + structured logging events (which you rightly call out above). As core maintainers, we could get cleverer about how to expose these capabilities to user-space code, but it will always be trickier than real Python.

As does the matter of who builds adapters:

• Most users don’t build them. A few dozen people build them, and then users install them as plugins.
• We can have personal relationships with the builders, who help us push forward the standardized interface.
• They can do things that dbt project code can’t, with the intent of supporting code in dbt projects — “making easy things easy” for everyone else.

(^ Granted, a lot of that second section is true for packages, too — and I know several folks in this discussion have imagined Continual, Privacy Dynamics, et al building “trusted” dbt packages that users could install, while still giving end users the ability to define the bonus services they need.)

Some imagination

-- models/upstream_model.sql

{{ config(adapter = 'snowflake') }}

select ...

-- models/continual_model.sql

{{ config(adapter = 'continual') }}

-- the compiled SQL here is shipped off to Continual
-- then Continual writes back to {{ target.schema }}.continual_model
-- just like Snowflake

select col1, col2
from {{ ref('upstream_model') }}
where filter

-- models/downstream_model.sql
{{ config(adapter = 'snowflake') }}

select predictions from {{ ref('continual_model') }}
join {{ ref('another_model') }} ...

To be clear, this doesn't really work today, at least not the way we'd want it to. (One dbt invocation is pretty tightly coupled to one dbt adapter. But multiple adapters can be installed in the same environment, and used in subsequent invocations.) Still, I can imagine, not so far in the future, "joint" runs across Databricks > Snowflake (via common storage layer / metastore), or Trino > Redshift (via Trino magic). So why not Snowflake > Continual > Snowflake?

Maybe it shouldn't be an adapter per se, but a new (peer) plugin construct, with its own interface. But for the DPM use case, we're not talking about an arbitrary hook here—we're talking about running a model, with code/config inputs and a data warehouse output—and that's basically what adapters exist to do!

For one, I like that I, an analytics engineer, don't have to pip install anything and get into some sort of dependency hell-type nightmare around e.g. conflicting versions of httpx so I can use web services from dbt.

This is legit. I don’t think analytics engineers (= dbt end users) should have to worry about this at any point. But should it be in the same way that (hopefully) people using dbt-snowflake don’t have to worry about either? In this vision, it’s something that the plugin developer would have to worry about, though.

Okay, so, all of that being said: Is there still value in the “wicked simple” reference implementation, which is just an HTTP request—no oauth, basic retry, basic timeout? Does that get us the DPM (Continual/Privacy Dynamics/…) use case, or is it really just tuned for the metadata-y CE use case?

[jtcohen6]

(I also know I have to live with the fact that, Continual did once build a dbt adapter for dbt + Snowflake + Continual, and I advised them that a metadata integration was probably a better bet! Apologies for the runaround @tristanz — for what it's worth, I'm not 100% sure that I was wrong then, nor that I'm totally right now.)

[tconbeer]

This comment just landed in Slack: https://getdbt.slack.com/archives/C024L0CBZ0B/p1654178098562219

Looks like layer.ai just took that approach and built a BQ adapter for dbt that also interfaces with their product. At PD, we very briefly explored this approach and shelved it.

[jtcohen6]

Just caught my eye as well :)

I'll be honest, I'm not as big a fan of dbt-layer-bigquery, versus dbt-bigquery + dbt-layer ! But I understand why they took that approach, given current constraints.

[tristanz]

Yeah, we took the dbt-continual-bigquery approach with our initial Continual prototype but decided it was too invasive and wouldn't be compatible with the current limitations of dbt Cloud. The adapter style definitely worked and offers a lot of flexibility. Ideally, there would be a way to do dbt-bigquery + dbt-continual as you say. Here dbt-continual could fully take over a particular model or just be injected as a pre/post hook.

[jtcohen6]

meta comment on how this discussion will/won't translate into real code being merged into dbt-core over the short/medium term

I'm grateful to everyone for their thoughtful comments in this discussion thus far!

Thanks especially to @jwills for some tremendous exploratory work, as a way to feel out what an initial approach to this might look like. Talk is cheap, code is worth a lot—especially as substrate for more conversation :)



As I said in the roadmap, I am interested this year in finding ways to unify lineage that is currently disjointed. Python models are going to be the main event in v1.3. UDFs/functions seem like important complements. I had two initial motivations in mentioning “external nodes” under that same section (with 80% confidence):

• I’d like to offer more capabilities around exposures, which have too long languished from lack of the attention they deserve. At minimum, I'd like to hit some of the straightforward improvements in Future art for exposures #5106 — but I'm open to the idea that it should look like adding interactive capabilities with the thing the exposure stands in for, the actual downstream consumer of those dbt-produced datasets.
• Services like Continual offer an important piece of the story for integrating analytics engineering and ML / data science workflows, especially for folks using data warehouses that don’t natively support Python-language transformation.


I do believe that dbt should eventually gain the capabilities to communicate with “external services.” The question is whether it should gain that ability now. In the threads above, I feel like we've developed useful taxonomies of use cases, distinguishing between (at minimum):


(1) "Dataset-producing models." These run mid-DAG, and feel a lot like models, but crucially happen in execution environments that are external to the analytical warehouses.

(2) "Triggers," or "callable exposures." These are more for metadata integrations and observability; it's the use case best solved for by the simplest implementations here, but I'm less clear on what dbt-core's role here ought to be. Maybe it's defining "request hooks" on exposures, but I'm equally inclined to think that we support this by further investing in the reliability and completeness of our structured logging interface.


So: I'm very open to more examples / compelling use cases that call for "external nodes" of the first type. I've heard Privacy Dynamics + Continual. Where else do people want to be running their models, as a complement to what dbt can already do in the warehouse?

[misteliy]

Hi,
There are many use cases for the (2) "Triggers / callable exposures / webhook" mechanism. The most trivial that we see would be to trigger a tableau extract (yes, still used BI tool in the wild 😄), trigger a talend (yes, a tool that still exists 🤣) pipeline for downstream systems. Currently we are thinking of having UDFs as a "hack" but it would be great to see this feature moving forward...

[jaredchurch]

Agreed here - I'd love to trigger a qlik sense application to do it's load when a certain node in my DAG completes meaning that our downstream BI can get started as soon as the models in the related data mart have been updated rather than having to wait for entire transform to complete before that next step runs.

[misteliy]

Given that this feature is available in dbt cloud…it indicates that it is indeed something people are looking for 😊