Hi Anybody has issues using credentials in DbtShellTask in P

Question

Hi Anybody has issues using credentials in DbtShellTask in Prefect Cloud I m working with BigQuery Do I need to pass the keyfile param inside dbt kwargs Or if I have a GCP CREDENTIALS Secret this is not need

Anna Geller · Answer

Here is one way of approaching it using Prefect Secrets and `dbt kwargs` ``` postgres user = PrefectSecret POSTGRES USER postgres pass = PrefectSecret POSTGRES PASS db credentials = get dbt credentials postgres user postgres pass dbt run = dbt command= dbt run task args= name DBT Run dbt kwargs=db credentials ``` full example <https www prefect io blog flow of flows orchestrating elt with prefect and dbt >

Anna Geller · Answer

`GCP CREDENTIALS` can be tackled the same way

Mateo Merlo · Answer

Thanks < Anna Geller> Something like this ``` task def get dbt credentials gcp json return keyfile gcp json task def format command target env return f dbt build target target env with Flow DBT flow as flow target env = Parameter target env required=True credentials = PrefectSecret GCP CREDENTIALS db credentials = get dbt credentials credentials dbt run = dbt command=format command target env task args= name DBT Run dbt kwargs=db credentials ```

Mateo Merlo · Answer

I will try it

Anna Geller · Answer

this looks good the only issue you need to be careful about is whether dbt expects the contents of the GCP service account or just a path to it the Prefect Secret GCP CREDENTIALS expects the contents of a SA

Mateo Merlo · Answer

Yes that is exactly the issue because I know that DBT require a path

Mateo Merlo · Answer

Should I create a Docker image with this file inside and use an ENV variable in the Dockerfile

Mateo Merlo · Answer

we are working with a custom image to run the flows with Vertex

Anna Geller · Answer

I can t dive deeper into this atm but this repo may help you I did a flow of flows example with dbt + GCP here using a local agent but this should work the same way with Docker agent <https github com anna geller prefect monte carlo tree workshop flows> I didn t have to specify any credentials other than setting the SA path on the dbt profile

Mateo Merlo · Answer

Sure Thanks simple smile

Anna Geller · Answer

please keep us posted here about your progress I think using the same approach setting the path on your dbt profile and coping or mounting the SA file to your container should work

Mateo Merlo · Answer

In case I get the secret from Prefect Cloud should I be able to paste this content in a new file right I think that will be easier to do And then use that path file as the keyfile param

Anna Geller · Answer

dbt seems to support multiple auth methods so you d need to experiment which option works best for you hard to say sorry I m not more useful here

Mateo Merlo · Answer

Yes the main trade off with mounting the file in the Docker image is that probably is going to work when I build the image locally where I have the SA file but we are using cloudbuild to build and push the image to Google Registry so in that case we will probably need to use AWS Secrets Manager and I honestly I have not to much idea about it

Mateo Merlo · Answer

no problem Anna Thanks for your help

Joshua Greenhalgh · Answer

< Mateo Merlo> where are you running your flows and agent

Joshua Greenhalgh · Answer

You can it seems specify the individual parts of the keyfile directly in the profile yml gt <https prefect community slack com archives CL09KU1K7 p1650625646905089 thread ts=1650371481 279719 amp cid=CL09KU1K7> So maybe you could template in the secret bits from prefect secrets I am running DBT on k8s I created a k8s secret file and then mounted that into the pods environment via the agent default template if you are running on k8s would be happy to discuss how I did that

Mateo Merlo · Answer

Thanks for your response < Joshua Greenhalgh> Yes I saw your thread as well

Mateo Merlo · Answer

We are using Vertex and docker

Mateo Merlo · Answer

```flow storage = GCS bucket= influsense test bucket secrets= GCP CREDENTIALS flow executor = LocalDaskExecutor scheduler= threads flow run config = VertexRun image= europe west1 docker pkg dev bi staging 1 309112 my image my image latest labels= vertex ```

Mateo Merlo · Answer

Google Cloud Storage

Mateo Merlo · Answer

Did you solve it using the individual parts or just getting the entire file from k8s secret

Joshua Greenhalgh · Answer

I just mounted the entire file

Mateo Merlo · Answer

Would you mind to share how you did it I ve already been working with Kubernetes and running the agent but I ve never used secrets

Joshua Greenhalgh · Answer

Of course

Joshua Greenhalgh · Answer

Is the terraform ok

Mateo Merlo · Answer

Sure

Joshua Greenhalgh · Answer

```data sops file dbt bq user service account key source file = secret data dbt bq user sa key json resource kubernetes secret dbt bq user service account key metadata name = dbt bq user service account key namespace = prefect data = dbt bq user sa key json = jsonencode data sops file dbt bq user service account key data ``` The `sops file` bit is just so I can keep the file in a git repo encrypted basicly `data sops file dbt bq user service account key` is the full key for the service account The I have the following agent template ```apiVersion batch v1 kind Job spec template spec containers name flow imagePullPolicy Always volumeMounts mountPath service account key name dbt bq user service account key volumes name dbt bq user service account key secret secretName dbt bq user service account key``` And my profile which is baked into my image looks like ```mbk dwh outputs dev dataset mbk dwh fixed retries 1 location EU method service account priority interactive project data analytics mbk threads 1 timeout seconds 300 type bigquery keyfile service account key dbt bq user sa key json```

Mateo Merlo · Answer

Thanks so much

Mateo Merlo · Answer

I m currently using AWS Codebuild to build and push the docker image so I will probably need to do something similar with AWS Secrets Manager to build the image from the cd pipeline

Mateo Merlo · Answer

you run all the terraform commands locally right

Mateo Merlo · Answer

How do you point to this file in the KubernetesRunConfig

Joshua Greenhalgh · Answer

I don t need to specify anything in the run config since it just exists on the pod at ` service account key dbt bq user sa key json` all I have to do is bake the profile into the image with a ref to that location

Joshua Greenhalgh · Answer

I try to run the terraform on CI most of the time doesn t always work that way however

Mateo Merlo · Answer

I mean how do you say to Prefect that the flow show use that image

Joshua Greenhalgh · Answer

Ahh ok ```run config=KubernetesRun image=JOB IMAGE NAME labels= DEFAULT AGENT LABEL ```

Joshua Greenhalgh · Answer

I am always just using `latest`

Mateo Merlo · Answer

Thanks so much < Joshua Greenhalgh> I will try first work with the individual parts of the keyfile and if I have not success then I would move to Kubernetes

Mateo Merlo · Answer

I think that use a task that read the GCP CREDENTIALS variable and create a new file with that content can be a good approach as well

Mateo Merlo · Answer

And then use that as the keyfile inside profiles yml

Joshua Greenhalgh · Answer

So I started out using vertex but its pretty expensive if you are only running one gke autopilot cluster the flat fee is discounted so you only pay for the compute you use

Joshua Greenhalgh · Answer

k8s is complicated of course but I don t think its insurmountable

Mateo Merlo · Answer

Totally agree With one cluster you mean the agent that will be running there right