@Chris White has joined the channel

Also today whether you're in person at Coalesce or virtual, check out @Dylan’s talk on event driven dbt models at 4p Central! https://coalesce.getdbt.com/agenda/when-the-real-world-messes-with-your-schedule-event-driven-dbt-models-for-the-mds

A note from Marvin marvin duck

More 🔥 pics from dbt Coalesce yesterday, 📸 courtesy of @David Abraham

@David Abraham has joined the channel

Hi friends, I have separate git repos for dbt and prefect. I’d like to start using prefect to schedule dbt runs but trying to understand how best to do that. Should I just make prefect first do a

git clone

of the dbt repo? Currently just using dbt cloud to run on a schedule but thinking of moving away from that towards the above solution.

Hey folks, I've a question for all the dbt data model masterminds out there (I hope this is the right channel). I’ve a rather huge source table which is partitioned by a column “loaded_at”. I have an incremental model which reads from this source table. To exclude source table partitions to read, I could make use of the _dbt_max_partitions scripting variable - something like below

{% if is_incremental() %}
    where loaded_at>= coalesce(_dbt_max_partition, '2022-01-01')
  {% else %}

But, the problem is, that in my incremental model I do not partition by “loaded_at” but by a different column (due to use-case demands). So _dbt_max_partition would not help here, as it would simply return the maximum partition value of the model (which I can’t use as filter for the source table). In “native” BigQuery I would simply use a scripting variable as follows

declare max_source_partition timestamp;
set max_source_partition = (select max(loaded_at) as ts from `my_model_table`);
select * from `my_source_table` where loaded_at > max_source_partition

How can one implement such a scenario with dbt? Is there a way to create scripting variables as part of my models? Or do I need to add it as a on-start-hook? Or any better strategies to exclude partitions in my source without having the same column as partition field in my model?

Im looking to execute some

dbt ...

commands from prefect. It seems there is a lot of overhead to getting this working. My steps (my head at least are): • Grab the appropriate dbt docker image • Pull my dbt repo • do a docker run, something like this:

docker run -v /repo/loc/:/src/ -w /src/ image_name run --select ...

I see there are both prefect-docker and prefect-dbt collections. Curious what others have done...

Hello Prefect folks, I have multiple data extraction tasks that are running in separate pipelines. The longes of these can take about 3 hours. I want to create a flow that waits for the last of these to finish running before my dbt tasks run in a separate pipeline. Is there a way to ensure that the extraction tasks finish before the dbt tasks begin? We are using prefect 1.0 and we are not using the dask executor at present. Mostly we need a good enough solution until we can create a long term solution to run tasks or subflows in parallel. Thanks!

Hey! Does anyone some good example to share on how Prefect 2.0 dbt local cli run with Postgres? Having abit of difficulties setting up all the blocks and profiles correctly.

Hi everyone! I am currently trying to implement Prefect with DBT. We have a prefect repo with all organization's data workflows. I'm wondering whether we should put our DBT code inside that Prefect repo or should it have it's own Github repo? What would be the pros and cons? In terms of Developer experience and CI/CD?

Hi all! I'm running into an issue getting started with dbt and Prefect on the CLI. I already have dbt successfully running locally, with a Snowflake connection and profiles.yml file. Using Prefect 2.6.9, dbt 1.3.1 and Python 3.9.15 on Windows running locally. However, when I try to run a simple "dbt debug" command inside a Prefect flow, I get a "NotImplementedError" message (see thread). I've tried two different methods of running the dbt command: one referencing an existing project directory and profiles.yml file, and one manually hardcoding a profiles configuration (see thread for full code). Both give me the same "NotImplementedError". Any ideas what I'm missing?

Hello everyone! I have a quick (possibly newbie-ish) question about how people have been using [`BigQueryTargetConfigs]`(https://prefecthq.github.io/prefect-dbt/cli/configs/bigquery/#prefect_dbt.cli.configs.bigquery.BigQueryTargetConfigs).

Newbie question: I've wondering how people have used the

BigQueryTargetConfigs

prefect-dbt block .

How do I set my DbtCliProfile to use a SnowflakeTargetConfig? The UI only let’s you select TargetConfig and not a snowflake one

Just researching: • Does anyone have documentation or references on maxing out dbt dbt CORE capabilities before migrating to Prefect?

Hi all! I'm trying to run a dbt library locally using dbt-cli in Prefect. I've noticed an error I tend to get is:

ERROR: Runtime Error
  Credentials in profile "snowflake_dbt_transformations", target "dev" invalid: 'database' is a required propertyDefined profiles:
 - snowflake_dbt_transformations
For more information on configuring profiles, please consult the dbt docs:

<https://docs.getdbt.com/docs/configure-your-profile>

Upon investigation, it seems like the dbtCliProfile passed to the dbtTrigger task gets saved in the

~/.dbt/profiles.yml

first and then used inside the dbtCli shell task. However the problem is the method where it saves the dbtCliProfile into a yaml file does not save the

TargetConfigs

resulting in the profile file being incomplete. Has anyone had an experience with this before and know any workarounds?

Hi! I'm standing up dbt CLI on AWS Fargate using tasks from this template: https://github.com/anna-geller/dataflow-ops I have all of the flows deployed and scheduled using Prefect cloud, and I'm having no problems running the dbt run (it is using the project files from the S3 bucket/folder that's attached to the image), but I'm running into issues when running

dbt docs generate

as the Flow Run is outputting that

Catalog written to /opt/prefect/target/catalog.json

I checked S3 and it's definitely not there, and I'm guessing that the

/opt

is a directory that's tied to the ECS Task from the container? Is there a way that the dbt cli command can output the docs file (specifically index.html into the S3 bucket so we can host our dbt docs as a static site) Flow code is in the 🧵. I really appreciate any help or pointers 🙂

Hi everyone, please how can I run prefect 2.0 Agent within python code that contain the flow?

Hi, this might be a stupid question, but is it possible to schedule queries created with dbt inside Bigquery? We are looking to have views, ... created into Bigquery and would like to know if I can schedule when they run inside a flow. Atm we have them as scheduled queries that run at specific times. Thanks!

Cross posting the announcement of prefect-dbt v0.3.0 https://prefect-community.slack.com/archives/CKNSX5WG3/p1676578412722889

Hi all! Very much starting out trying to orchestrate our dbt commands using prefect but running into a few issues. Issues in images provided - can anyone guide me on why these flows aren't working? First image - no idea why it doesn't like that comma as I've lifted that snippet from prefect-dbt github page. Second image - I'm running a task from within a flow, but it seems to this I'm running a task from within a task?

Hello all, I would like to ask for your recommendations on structuring flows/subflows/tasks. We would like to define as many tasks as possible, so that the radar displays the entire job as meaningfully as possible (without having to jump from flow to flow or subflow; instead, we want to see an overall picture). We run into the problem that dbt_cli itself is also a task (correct me if I'm wrong). What are your recommendations here? How do you proceed with the interaction of Prefect and dbt? Many thanks in advance @Florian Pape, fyi

Hi all, Anyone have any idea where I'm going wrong here? Just can't seem to get this working? Keep getting this error message.

X-posting, see you all in #data-ecosystem

Hi everyone, has anyone of you ever had experience with the following error? The error occurs when executing a prefect flow that runs multiple dbt jobs. We host Prefect ourselves via Docker/Kubernetes. Do you have any ideas on the cause and remedy?

RuntimeError: Command failed with exit code 133:
/tmp/prefect-4zqou1u7: line 1: 299284 Trace/breakpoint trap   (core dumped) dbt build --select dl_nicc_ecom__order_header --profiles-dir /tmp/dbt --project-dir /tmp/dbt
12:14:43 PM
Finished in state Failed('Flow run encountered an exception. RuntimeError: Command failed with exit code 133:\n/tmp/prefect-4zqou1u7: line 1: 299284 Trace/breakpoint trap   (core dumped) dbt build --select dl_nicc_ecom__order_header --profiles-dir /tmp/dbt --project-dir /tmp/dbt\n\n')

Many thanks in advance!

Anyone able to support me here? 🙂

When attempting to use

DbtCoreOperation

we are receiving the following error... This suggests that our dbt cli profile block, which we have defined in dbt Cloud, does not have a name or target or target_configs, but it does..

pydantic.error_wrappers.ValidationError: 3 validation errors for DbtCoreOperation
dbt_cli_profile -> name
  field required (type=value_error.missing)
dbt_cli_profile -> target
  field required (type=value_error.missing)
dbt_cli_profile -> target_configs
  field required (type=value_error.missing)

I was able to use the

profile_dir

parameter to use my local credentials, but this only verifies there aren't obvious problems with the flow code.

prefect-dbt==0.3.1

prefect-snowflake==0.26.0

Any ideas?

More difficulties with

dbtCoreOperation

. I am running...

with DbtCoreOperation(
        commands=["dbt deps", "dbt build " + dbt_build_arg + " --target " + DBT_ENV],
        project_dir="edw-dbt",
        # working_directory="edw-dbt",
        # overwrite_profiles=True,
        profiles_dir="~/.dbt",
        # dbt_cli_profile=dbt_cli_profile,
        stream_output=True,
    ) as dbt_operation:
        dbt_process = dbt_operation.trigger()
        dbt_process.wait_for_completion()
        result = dbt_process.fetch_result()

Currently failing with:

15:35:09.705 | INFO    | Flow run 'furry-carp' - PID 98611 stream output:
09:50:09  Error importing adapter: No module named 'dbt.adapters.snowflake'
15:35:09.706 | INFO    | Flow run 'furry-carp' - PID 98611 stream output:
09:50:09  Encountered an error while reading profiles:
  ERROR: Runtime Error
  Credentials in profile "edw", target "dev" invalid: Runtime Error
    Could not find adapter type snowflake!Defined profiles:
 - edw
For more information on configuring profiles, please consult the dbt docs:

<https://docs.getdbt.com/docs/configure-your-profile>
15:35:09.720 | INFO    | Flow run 'furry-carp' - PID 98611 stream output:
09:50:09  Encountered an error:
Runtime Error
  Could not run dbt

Hi EveryOne ! I'm getting this error with dbtCoreOperation , can anyone tell me what this error means?

Finished in state Failed('Flow run encountered an exception. RuntimeError: PID 17072 failed with return code 1.\n')