[APP] dbt Cloud

[raphaelvarieras]

Name of app / service dbt Cloud

Link to developer documentation https://docs.getdbt.com/dbt-cloud/api-v2#/

Is lack of support preventing you from building workflows, or do you have a workaround? I would like an action to trigger a job in dbt Cloud. I could maybe figure it out but would prefer to use a Pipedream-managed component.

Are there specific actions, or triggers, you'd like to see for this app? Please let us know here or use the Action and Trigger issue templates to open requests for each!

• Trigger a job is mostly what I'm after

[mroy-seedbox]

We would also like to have an app for DBT Cloud, mostly so that we can store the credentials elsewhere than in environment variables (see duplicate issue #7259).

We're fine with using code steps to communicate with the API.

[mroy-seedbox]

@raphaelvarieras: Here's how to trigger a job run using a Node.js code step. You'll need two environment variables: DBT_ACCOUNT_ID & DBT_API_KEY.

import axios from "axios"

export default defineComponent({
  props: {
    jobId: {
      type: "integer",
      label: "The ID of the job to run",
      min: 0
    }
  },
  async run({ steps, $ }) {
    const url = `https://cloud.getdbt.com/api/v2/accounts/${process.env.DBT_ACCOUNT_ID}/jobs/${this.jobId}/run/`;
    const resp = await axios({
      method: "POST",
      url: url,
      timeout: 5000, // 5 seconds
      data: {
        cause: "Kicked off from Pipedream"
      },
      headers: {
        "Content-Type": "application/json",
        Accept: "application/json",
        Authorization: `Token ${process.env.DBT_API_KEY}`,
      },
      validateStatus: () => true,
    });

    if (resp.status >= 400) {
      console.log(url);
      console.log(resp.data);
      throw new Error(`Request Failed: ${resp.status} ${resp.statusText}`); 
    }

    return resp.data.data;
  }
})

Next, you can poll the API until the job has completed:

import axios from "axios"

export default defineComponent({
  async run({ steps, $ }) {
    const runId = steps.run_job.$return_value.id;
    const url = `https://cloud.getdbt.com/api/v2/accounts/${process.env.DBT_ACCOUNT_ID}/runs/${runId}`;
    const resp = await axios({
      method: "GET",
      url: url,
      timeout: 5000, // 5 seconds
      headers: {
        Accept: "application/json",
        Authorization: `Token ${process.env.DBT_API_KEY}`,
      },
      validateStatus: () => true,
    });

    if (resp.status >= 400) {
      console.log(url);
      console.log(resp.data);
      throw new Error(`Request Failed: ${resp.status} ${resp.statusText}`); 
    }

    if (resp?.data?.data?.is_complete)
      return resp?.data?.data;

    $.flow.rerun(60000) // Poll every minute
  },
})

[mroy-seedbox]

@dylburger: We'd love to contribute actions for DBT Cloud (and Databricks as well). I'll look into how to do that in the next few days. Maybe we'll start with Databricks, since there's already an app for it (but without any actions available so far).

[dannyroosevelt]

@raphaelvarieras @mroy-seedbox we enabled a basic integration with dbt Cloud here: https://pipedream.com/apps/dbt

@mroy-seedbox let me know if you need any guidance on building actions for DBT or Databricks -- our team is happy to support in any way!

[mroy-seedbox]

For DBT Cloud components (triggers & actions), see #7262.

[mroy-seedbox]

@dannyroosevelt: We'd also love to contribute components for Tableau (see #4873) and Talend (see #7265).

If you could create an app for those also, we'll get started soon after!

[mroy-seedbox]

@dannyroosevelt : Since API keys in DBT Cloud are tied to a specific account, it would make sense to also ask for the account ID in the application setup. Otherwise, the account ID must be stored elsewhere, such as in environment variables, but that won't work for published components (and we don't want to repeat the account ID as a prop for every component).

Should I create a new issue for this?

[dannyroosevelt]

Thanks for flagging -- I just added that field back in (we had it then removed it). Can you try connecting your account again? It'll now be accessible via this.dbt.$auth.account_id

[mroy-seedbox]

It works! 👌

Thank you! 🙏

[sergio-eliot-rodriguez]

hi @mroy-seedbox

nice to meet you. my name is Sergio. I collaborate with Pipedream working out the initial, basic integration you see for apps in the app store.

when i tackled dbt Cloud, I noticed there is a Discovery API available too. This is a different to the Administrative APi that was published, this API that's built on top of GraphQL. My test case breaks with 401 error authroized, and I am guessing the trial account I got doesn't work for this API.

I wanted to ask you if we could collaborate for get Discovery API in the Pipedream app store? how does it sound?

The process would be:

1) Pipedream publishes the app to the app store untested. 2) you help us test with your API Token 3)Pipedream makes any changes needed, as per your feedback.

do you think you could engage on this one?

At this point I'm sure my test case should work because dbt has pretty well documentation, and out of my guts I think this is an issue with APi access and the trial.

lt us know please. thanks!

[mroy-seedbox]

Hi @sergio-eliot-rodriguez! 👋

No problem, I can help you with this. I expect everything to work without any issues, since the authentication is the same for both APIs.

The issue is that the Discovery API is only available on the Team & Enterprise plan:

We're on the Team plan, so it should work for us. 👍

[mroy-seedbox]

@sergio-eliot-rodriguez: Actually, I don't think you need to create a new app for this (unless that's your preference). The existing app can communicate with both APIs already (it's just the URL that's different).

I just made it work:

[sergio-eliot-rodriguez]

@mroy-seedbox thanks for the input!

yeah, i try that same query and I get 401 unauthorized, so it must be my trial plan.

you have a point - a different app might not be needed after all, instead, the current app's test request could be further parametrized to take into account the metadata endpoint for Discovery API. I'll put this into the team's attention.