https://prefect.io logo
Title
t

Tyler Simpson

05/18/2023, 11:52 PM
Hi everyone! I have been loving Prefect. I am working on adding dbt to my pipelines and after reading the docs and encountering errors in a few different approaches I thought I would come here for help. Adding code in the comments to keep the size down. Thanks in advance!
2
Here are some of the commands I will be running. I can run these dbt run commands in bash in my python venv that my project is in.
dbt run --profiles-dir /c/Users/simps/p3dd_phase_3_due_diligence/project/dbt --target silver --models s_t_jobs_growth --full-refresh
dbt run --profiles-dir /c/Users/simps/p3dd_phase_3_due_diligence/project/dbt --target gold --models g_v_jobs_daily_total g_v_jobs_daily_weekly_monthly_growth --full-refresh
I have tried a few approaches with and without Prefect blocks. I first attempted a dbt Core Operation block but I was unsure of how to format the paths/directories. The same format as the bash dbt run above did not work but this gave different errors: C:\Users\simps\p3dd_phase_3_due_diligence\project\dbt\p3dd I also tried running with prefect shell to avoid dbt build-ins but I received powershell specific errors and couldn't seem to get it to run via bash. I am hoping someone can help provide a basic skeleton code to help me run my commands above or provide insight on altering my approaches.
s

Sean Williams

05/19/2023, 5:24 PM
What errors are you seeing? Have you tried this code snippet from the docs?
from prefect import flow
from prefect_dbt.cli.commands import DbtCoreOperation

@flow
def trigger_dbt_flow() -> str:
    result = DbtCoreOperation(
        commands=["pwd", "dbt debug", "dbt run"],
        project_dir="PROJECT-DIRECTORY-PLACEHOLDER",
        profiles_dir="PROFILES-DIRECTORY-PLACEHOLDER"
    ).run()
    return result

trigger_dbt_flow()
It also looks like you added
\p3dd
to one of your examples, but not others. I wonder if that might be part of the problem
t

Tyler Simpson

05/19/2023, 5:48 PM
@Sean Williams I'll be home later this evening to regenerate the errors and will supply those. I tried that code from the documentation where: commands=["dbt run --profiles-dir /c/Users/simps/p3dd_phase_3_due_diligence/project/dbt --target silver --models s_t_jobs_growth --full-refresh", "dbt run --profiles-dir /c/Users/simps/p3dd_phase_3_due_diligence/project/dbt --target gold --models g_v_jobs_daily_total g_v_jobs_daily_weekly_monthly_growth --full-refresh"] project_dir="/c/Users/simps/p3dd_phase_3_due_diligence/project/dbt/p3dd" profiles_dir="/c/Users/simps/p3dd_phase_3_due_diligence/project/dbt" From memory, in this case, the errors I encountered seemed to be regarding underlying powershell being executed. When I run these dbt cli commands manually I am running them via bash in the console while in my python venv. I wonder if I convert all of the paths to the microsoft directory syntax? IE profiles_dir="C:\Users\simps\p3dd_phase_3_due_diligence\project\dbt" Maybe I can specify bash? Otherwise does my interpretation of the docs make sense (the syntax of my commands, project_dir, profiles_dir)? P.S. the reason I have /p3dd added is because the dbt profiles file is in /dbt/ and the project file is in /dbt/p3dd where p3dd is the name of the project. Thank you!
s

Sean Williams

05/19/2023, 5:55 PM
By specifying a
profiles_dir
and adding a
--profiles-dir
flag in the dbt command, I think you're specifying the directory twice. Tough to say without more info, but I could see that causing the problem. If you change the dbt command to
dbt debug
I'd be curious to see if that changes anything
t

Tyler Simpson

05/19/2023, 5:57 PM
@Sean Williams thank you! I will run the debug and tinker with the windows/linux directory naming convention and get you the error logs when I get home in a few hours.
@Sean Williams I ran the dbt debug as you mentioned
from prefect import flow
from prefect_dbt import DbtCoreOperation

@flow
def trigger_dbt_flow() -> str:
    result = DbtCoreOperation(
        commands=["dbt debug"],
        project_dir="C:\\Users\\simps\\p3dd_phase_3_due_diligence\\project\\dbt\\p3dd",
        profiles_dir="C:\\Users\\simps\\p3dd_phase_3_due_diligence\\project\\dbt"
    ).run()
    return result

if __name__ == '__main__':
    trigger_dbt_flow()
Oddly it seems I am getting a powershell access/security error. I do not use powershell generally I only execute bash locally and in my venv. I am using C:\Users\simps where this appears to be routing to my C:\Users\Tyler Simpson\. It seems that perhaps I need to either force bash to be used or alter the pathing of the powershell. What do you think? $ python test.py 15:32:47.565 | INFO | prefect.engine - Created flow run 'cornflower-raven' for flow 'trigger-dbt-flow' 15:32:47.785 | INFO | Flow run 'cornflower-raven' - PID 25344 triggered with 1 commands running inside the '.' directory. 15:32:48.691 | INFO | Flow run 'cornflower-raven' - PID 25344 stream output: . : File C:\Users\Tyler Simpson\OneDrive\Documents\WindowsPowerShell\profile.ps1 cannot be loaded. 15:32:48.693 | INFO | Flow run 'cornflower-raven' - PID 25344 stream output: The file C:\Users\Tyler 15:32:48.694 | INFO | Flow run 'cornflower-raven' - PID 25344 stream output: Simpson\OneDrive\Documents\WindowsPowerShell\profile.ps1 is not digitally signed. You cannot run this script on the current system. For more information about running scripts and setting execution policy, see about_Execution_Policies at https:/go.microsoft.com/fwlink/?LinkID=135170. At line:1 char:3 + . 'C:\Users\Tyler Simpson\OneDrive\Documents\WindowsPowerShell\profil ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : SecurityError: (:) [], PSSecurityException + FullyQualifiedErrorId : UnauthorizedAccess 15:32:48.722 | INFO | Flow run 'cornflower-raven' - PID 25344 stream output: C:\Users\TYLERS~1\AppData\Local\Temp\prefect-si1q5fye.ps1 : File C:\Users\Tyler Simpson\AppData\Local\Temp\prefect-si1q5fye.ps1 cannot be loaded. The file C:\Users\Tyler Simpson\AppData\Local\Temp\prefect-si1q5fye.ps1 is not digitally signed. 15:32:48.724 | INFO | Flow run 'cornflower-raven' - PID 25344 stream output: You cannot run this script on the current system. For more information about running scripts and setting execution policy, see about_Execution_Policies at https:/go.microsoft.com/fwlink/?LinkID=135170. 15:32:48.726 | INFO | Flow run 'cornflower-raven' - PID 25344 stream output: At line:1 char:1 + C:\Users\TYLERS~1\AppData\Local\Temp\prefect-si1q5fye.ps1 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : SecurityError: (:) [], PSSecurityException + FullyQualifiedErrorId : UnauthorizedAccess 15:32:48.747 | ERROR | Flow run 'cornflower-raven' - Encountered exception during execution: Traceback (most recent call last): File "C:\Users\simps\p3dd_phase_3_due_diligence\project\prefect_pipelines\p3ddenv\lib\site-packages\prefect\engine.py", line 669, in orchestrate_flow_run
@Sean Williams sorry to spam but I was able to resolve the issue. It was related to powershell security. I will provide an explanation in case others come upon this. First, this is the Prefect code that worked. Note I'm in a python venv using a windows OS.
from prefect import flow
from prefect_dbt import DbtCoreOperation

@flow
def trigger_dbt_flow() -> str:
    result = DbtCoreOperation(
        commands=["dbt run --profiles-dir /c/Users/simps/p3dd_phase_3_due_diligence/project/dbt --target silver --models s_t_jobs_growth --full-refresh"],
        project_dir="C:\\Users\\simps\\p3dd_phase_3_due_diligence\\project\\dbt\\p3dd",
        profiles_dir="C:\\Users\\simps\\p3dd_phase_3_due_diligence\\project\\dbt"
    ).run()
    return result

if __name__ == '__main__':
    trigger_dbt_flow()
Powershell security was getting in the way which was resolved quickly and easily. First, opening powershell as an admin.
Get-ExecutionPolicy
If this results in Restricted or AllSigned that is preventing the script execution. In my case it was set to AllSigned
Set-ExecutionPolicy Unrestricted
This then allowed the script to run. Thank you for your assistance!
🎉 1
s

Sean Williams

05/19/2023, 7:54 PM
Glad you got it figured out! Interesting that it worked even when the profiles dir was specified twice
t

Tyler Simpson

05/19/2023, 8:47 PM
@Sean Williams that is pretty interesting, good to know for future uses. Have a great weekend!
:highfive: 1