https://prefect.io logo
Join Slack
Powered by
<@ULVA73B9P> I'm looking at the '*dbt Model Orches...
# ask-marvin
p
@Marvin I'm looking at the '*dbt Model Orchestration' example in the docs.* This is how I understand it: build_dbt_project effectively download the repository where all the dbt models exist as a zip. (Decompresses it into a temporary folder. effectively cloning it) create_dbt_profiles makes a profiles.yml so dbt can actually run with connection to the database the models will store? I do not understand why this is needed. run_dbt_commands runs the commands with the context of the cloned repository Confusion - why is there a 'create_dbt_profiles' function in the example. For instance, in my dbt repository I already have a profiles.yml which states the connection to snowflake, and the authorisation parameters for snowflake. So why not just use that. Secondly, I have the following snipped of the profiles.yml in that repository: type: snowflake account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}" user: "{{ env_var('SNOWFLAKE_USER') }}" private_key: "{{ env_var('SNOWFLAKE_PRIVATE_KEY') }}" How would I pass these environment variables to the dbt runner when the commands are invoked? I currently have them saved as prefect secrets. Thirdly, I'm unsure how it would look in the UI. I understand logs are passed of the commands to the UI, but how would it highlight dbt errors or warnings in the UI. Ideally for each i.e. 1 out of 233 models running I seperate task in the UI would be amazing, but I feel like it is all bundled into 1 task.
m
✅ thought for 132.7 seconds
Great questions — and your read on the example is mostly right. A few clarifications and concrete ways to wire this up the way you want. References: - Prefect + dbt integration guide - Run dbt with Prefect (example) First: what those example functions are - build_dbt_project: just an example helper that downloads a demo dbt project as a zip and unpacks it. It’s not required if you already have your project available where your code runs. - create_dbt_profiles: another example helper that writes a minimal profiles.yml so the demo project can run. It’s only there because the example downloads a bare project that has no profile. If you already have a profiles.yml, you can skip this entirely. - run_dbt_commands: wraps the modern runner and executes dbt commands in the specified project dir. If you already have profiles.yml - You do NOT need to create one in the flow. Point dbt to your existing file via either: - Set DBT_PROFILES_DIR in the environment to the directory containing profiles.yml, or - Pass it explicitly to the runner settings. Example using your existing profiles.yml
Copy code
from prefect import flow
from prefect_dbt import PrefectDbtRunner, PrefectDbtSettings

@flow
def run_dbt():
    runner = PrefectDbtRunner(
        settings=PrefectDbtSettings(
            project_dir="/path/to/your/dbt/project",
            profiles_dir="/path/to/dir/that/contains/profiles.yml"
        )
    )
    runner.invoke(["deps", "run", "test"])  # or any dbt CLI commands in order

if __name__ == "__main__":
    run_dbt()
Passing your Snowflake env vars (SNOWFLAKE_ACCOUNT, SNOWFLAKE_USER, SNOWFLAKE_PRIVATE_KEY) Since your profiles.yml uses 
env_var('...')
, you just need those env vars present in the process that runs dbt. You’ve got a few good options: - Set them on the worker/deployment environment - Recommended for production. Put SNOWFLAKE_ACCOUNT, SNOWFLAKE_USER, SNOWFLAKE_PRIVATE_KEY in the environment where your Prefect worker runs (VM/container/Kubernetes). dbt will pick them up automatically. - You can also set default environment variables on a Work Pool in Prefect Cloud/Server so every deployment/worker registered to that pool inherits them. - Load them from Prefect Secret blocks in code and export to the environment before invoking dbt
Copy code
import os
from prefect.blocks.system import Secret
from prefect_dbt import PrefectDbtRunner, PrefectDbtSettings

def export_snowflake_env_from_secrets():
    os.environ["SNOWFLAKE_ACCOUNT"] = Secret.load("snowflake-account").get()
    os.environ["SNOWFLAKE_USER"] = Secret.load("snowflake-user").get()
    os.environ["SNOWFLAKE_PRIVATE_KEY"] = Secret.load("snowflake-private-key").get()

def build_runner():
    return PrefectDbtRunner(
        settings=PrefectDbtSettings(
            project_dir="/path/to/your/dbt/project",
            profiles_dir="/path/to/dir/that/contains/profiles.yml"
        )
    )

from prefect import flow
@flow
def run_dbt():
    export_snowflake_env_from_secrets()
    runner = build_runner()
    runner.invoke(["run", "test"])
- Use DBT_* environment variables (optional) - You can set DBT_PROFILES_DIR and DBT_PROJECT_DIR as env vars instead of passing them via settings if you prefer that style. How this looks in the Prefect UI (errors, warnings, and per-model visibility) - With the modern Prefect dbt runner, each dbt node (models, tests, seeds, snapshots) is reflected as its own Prefect task run. You’ll see something like “model my_model” and “test not_null_my_model_id” as separate tasks in the run graph. - Logs from dbt are streamed into those specific tasks. If a model fails, that task shows as Failed with the dbt error details in its logs. - The overall flow run will fail on dbt errors by default; you can change that by initializing the runner with 
raise_on_failure=False
if you need the flow to continue. Common gotchas
- profiles.yml location: dbt looks in ~/.dbt/profiles.yml by default. If your profiles.yml lives in your repo, either set DBT_PROFILES_DIR to that directory or pass 
profiles_dir
to the runner. - Ensure the environment that runs the flow actually has the env vars set (or sets them from Prefect Secrets) before 
runner.invoke(...)
runs. - If you previously used the legacy DbtCoreOperation/DbtCliTask, those typically produce a single Prefect task for the whole run. Switch to the modern runner to get per-model tasks as shown above. If you share how you’re running (local, Docker, K8s) and where your profiles.yml lives, I can suggest the cleanest env/secret setup for your case.
p
@Marvin dev: type: snowflake account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}" user: "{{ env_var('SNOWFLAKE_USER') }}" private_key: "{{ env_var('SNOWFLAKE_PRIVATE_KEY') }}" warehouse: CWC_WH role: DEVELOPER database: DEV schema: "{{ env_var('SNOWFLAKE_USER') }}" threads: 1 client_session_keep_alive: False Encountered exception during execution: TypeError('str expected, not bytes') Traceback (most recent call last): File "/usr/local/lib/python3.11/site-packages/prefect/flow_engine.py", line 989, in run_context yield self File "/usr/local/lib/python3.11/site-packages/prefect/flow_engine.py", line 1634, in run_flow_sync engine.call_flow_fn() File "/usr/local/lib/python3.11/site-packages/prefect/flow_engine.py", line 1009, in call_flow_fn result = call_with_parameters(self.flow.fn, self.parameters) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/prefect/utilities/callables.py", line 210, in call_with_parameters return fn(*args, **kwargs) ^^^^^^^^^^^^^^^^^^^ File "/opt/project/deployments/dbt_run.py", line 109, in run_dbt os.environ["SNOWFLAKE_PRIVATE_KEY"] = Secret.load("snowflake-private-key").get().encode() ~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^ File "<frozen os>", line 684, in setitem File "<frozen os>", line 758, in encode TypeError: str expected, not bytes i tried without .encode and I got: Finished in state Failed('Task run encountered an exception ValueError: Failed to invoke dbt command \'run --target dev --select organisations\': Runtime Error\n Credentials in profile "dev", target "dev" invalid: Runtime Error\n Could not find adapter type snowflake!') In a save_secrets.py file I load the .pem as a secret
Copy code
pem_path = Path(__file__).parent / "snowflake.pem"
if not pem_path.exists():
    raise FileNotFoundError(f"Snowflake PEM file not found at {pem_path}")
snowflake_private_key = pem_path.read_text()
secret = Secret(value=snowflake_private_key)
    secret.save('snowflake-private-key', overwrite=True)
then I .encode() when passing to make snowflake connections but when doing this dbt flow: os._environ_["_SNOWFLAKE_ACCOUNT_"] = Secret._load_("snowflake-account")._get_() os._environ_["_SNOWFLAKE_USER_"] = Secret._load_("snowflake-user")._get_() os._environ_["_SNOWFLAKE_PRIVATE_KEY_"] = Secret._load_("snowflake-private-key")._get_()._encode_() _run_dbt_commands_( commands=[ "deps", "run --target dev --select organisations" ], _project_dir_=project_path ) i get the error with .encode But before I get an error saying 'invalid snowflake credentials' the key is 100% correct, as I use it in other flows like this private_key = serialization.load_pem_private_key( _snowflake_private_key_._get_()._encode_(), password=""._encode_() or None, backend=_default_backend_() ) snowflake_conn = snowflake.connector.connect( user=_snowflake_user_._get_(), account=_snowflake_account_._get_(), _private_key_=private_key, warehouse='DEF_WH', role="DEVELOPER", database="ANALYTICS", schema="PROD_MARTS" )
m
✅ thought for 111.3 seconds
Thanks for the detailed context — there are two separate issues here: 1) TypeError: str expected, not bytes - Cause: 
os.environ[...]
only accepts strings. Calling 
.encode()
returns bytes, which raises this error. - Fix: Set env vars as plain strings. Only encode when you pass the key to a library that expects bytes (e.g., 
serialization.load_pem_private_key
), not when setting env. 2) dbt error: “Could not find adapter type snowflake!” - Cause: The dbt Snowflake adapter is not installed in the environment where your flow is running. - Fix: Ensure 
dbt-snowflake
is installed alongside 
dbt-core
(matching versions) in your flow’s runtime image or execution environment. For example: - requirements.txt
Copy code
prefect
    prefect-dbt>=0.5.0
    dbt-core~=1.7
    dbt-snowflake~=1.7
- Or pip install in your image:
Copy code
pip install "dbt-core~=1.7" "dbt-snowflake~=1.7" "prefect-dbt>=0.5.0"
Notes on the Snowflake private key for dbt - dbt reads your profile YAML and passes credentials to the Snowflake adapter. For key pair auth you can use either: - `private_key_path`: path to a PEM file - `private_key`: the PEM contents as a string (multiline is okay if your environment variable preserves newlines) - Since env vars can be finicky with newlines, a reliable pattern is to write the PEM to a temp file and point dbt at it via 
private_key_path
. Recommended changes A) Set env vars as strings (no 
.encode()
), and write the PEM to a temp file for dbt:
Copy code
from prefect.blocks.system import Secret
import os, tempfile

# Set string env vars for dbt
os.environ["SNOWFLAKE_ACCOUNT"] = Secret.load("snowflake-account").get()
os.environ["SNOWFLAKE_USER"] = Secret.load("snowflake-user").get()

# Load the PEM as a string (no encode here)
pem_str = Secret.load("snowflake-private-key").get()

# Option 1 (recommended for dbt): write the key to a temp file and point dbt at it
with tempfile.NamedTemporaryFile("w", delete=False, suffix=".pem") as f:
    f.write(pem_str)
    key_path = f.name

os.environ["SNOWFLAKE_PRIVATE_KEY_PATH"] = key_path

# If you also need to use the Snowflake Python connector directly elsewhere:
# convert to bytes only at the time of use
# private_key_bytes = pem_str.encode("utf-8")
# private_key = serialization.load_pem_private_key(private_key_bytes, password=None, backend=default_backend())
Then, in your dbt profile, prefer `private_key_path`:
Copy code
dev:
  type: snowflake
  account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
  user: "{{ env_var('SNOWFLAKE_USER') }}"
  private_key_path: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PATH') }}"
  warehouse: CWC_WH
  role: DEVELOPER
  database: DEV
  schema: "{{ env_var('SNOWFLAKE_USER') }}"
  threads: 1
  client_session_keep_alive: False
B) If you’d rather keep 
private_key
in the profile: - Set 
os.environ["SNOWFLAKE_PRIVATE_KEY"] = pem_str
(no 
.encode()
). - Keep 
private_key: "{{ env_var('SNOWFLAKE_PRIVATE_KEY') }}"
in the profile. - Be sure your env preserves the newlines in the key. If you see parsing/auth issues, switch to the 
private_key_path
approach above. C) Ensure the dbt Snowflake adapter is available at runtime: - Inside your flow/image, run a quick check:
Copy code
dbt --version
  dbt debug --profile dev --target dev
If the adapter is missing, you’ll see the same “Could not find adapter type snowflake!” message. Useful docs - dbt Snowflake profile config: dbt docs: Snowflake profile - Key pair auth in Snowflake: Snowflake docs: Key pair authentication - Prefect dbt integration: prefect-dbt docs A couple of quick confirmations to help me tailor guidance: - Which image/environment is your flow running in (and does it include 
dbt-snowflake
)?
- Are you invoking dbt via Prefect’s 
prefect-dbt
(e.g., 
DbtCoreOperation
) or your own subprocess wrapper? - Do you want to keep the PEM in an env var, or is writing a temp file acceptable for the dbt step? Once the adapter is installed and the env vars are set as strings (or the key path is provided), both errors should be resolved.
p
@Marvin Thanks for this, this is now working. I can see that for each model, or each test a seperate task shows in the UI. And I can see if it fails or not - great. However, I want to know what happens for parts in dbt where there is a 'warn' next to the model. Specifically for source freshness, I get warn or 'error' now I assume error will error the task, but warn will that make the task yellow? how is it propogated
m
✅ thought for 268.7 seconds
Great to hear it’s working! Short answer: - There’s no “yellow” state in Prefect for dbt warnings. - By default, a dbt “warn” still yields a green Completed task in the UI. - Actual dbt failures/errors will mark the task Failed (red). - For source freshness, “warn” is informational; “error/runtime error” will fail the freshness task and, in the per-node orchestrator, dependent nodes may be skipped (they’ll show as Skipped in the UI). If you want warnings to fail tasks, use dbt’s “treat warnings as errors” flag. With prefect-dbt’s CLI profile you can enable it like this:
Copy code
from prefect_dbt.cli.configs import GlobalConfigs
from prefect_dbt.cli.credentials import DbtCliProfile

global_configs = GlobalConfigs(warn_error=True)
dbt_cli_profile = DbtCliProfile(
    name="my_profile",
    target="dev",
    target_configs=...,          # your target configs (e.g., Snowflake/BigQuery/Postgres)
    global_configs=global_configs
)
This passes 
--warn-error
to dbt, turning warnings into errors so Prefect will mark those tasks Failed. Helpful refs: - Prefect dbt collection docs: prefect-dbt docs - dbt warn-as-error flag: dbt --warn-error - dbt source freshness behavior: dbt source freshness If you’re using the per-node orchestrator (since you mentioned each model/test is its own task), the above behavior applies per node. If you want, I can share a small example of enabling 
warn_error
with the orchestrator pattern you’re using—just let me know which entrypoint you call today.
p
@Marvin is there a way to programatically (in code) set up an automation. For example I want 1 deployment to trigger by cron, then another deployment to trigger when the first finishes and the third trigger when the second finishes. I know that you can set it up via the UI, but wondering is there a script/command I can run at runtime to set that up. I currently have the whole prefect server and deployment managed via a docker composition, so I could easily run a script or command at docker compose up
m
✅ thought for 415.8 seconds
Yes—there are a few good, fully programmatic ways to set this up in Prefect 3.x. Here are three options you can run at container startup (docker-compose up) to get “A on cron → B after A completes → C after B completes”. Option 1: Define triggers on the deployments in code - Best when you already create deployments in Python. - Give A a cron schedule, then add event-based triggers to B and C that listen for the prior deployment’s Completed event.
Copy code
from uuid import UUID
from prefect import flow, serve
from prefect.events import DeploymentEventTrigger

@flow
def flow_a():
    ...

@flow
def flow_b():
    ...

@flow
def flow_c():
    ...

# A runs on a cron schedule
dep_a = flow_a.to_deployment(
    name="dep-a",
    cron="0 9 * * *",  # 9 AM daily
)

# B runs when A completes
dep_b = flow_b.to_deployment(
    name="dep-b",
    triggers=[
        DeploymentEventTrigger(
            expect={"prefect.flow-run.Completed"},
            match_related={"prefect.resource.name": "flow_a/dep-a"},  # <flow>/<deployment>
        )
    ],
)

# C runs when B completes
dep_c = flow_c.to_deployment(
    name="dep-c",
    triggers=[
        DeploymentEventTrigger(
            expect={"prefect.flow-run.Completed"},
            match_related={"prefect.resource.name": "flow_b/dep-b"},
        )
    ],
)

if __name__ == "__main__":
    serve(dep_a, dep_b, dep_c)
Option 2: Use the CLI to create automations from a YAML file - Great for “infrastructure as code” and easy to run in a container init. - First, get your deployment IDs:
Copy code
prefect deployment ls --output json
- Then create a YAML with two automations (B after A completes, C after B completes): automation_chain.yaml
Copy code
- name: "Trigger B after A completes"
  description: "Start dep-b after dep-a completes"
  enabled: true
  trigger:
    type: event
    posture: Reactive
    expect: ["prefect.flow-run.Completed"]
    threshold: 1
    within: 0
    match:
      prefect.resource.id: "prefect.flow-run.*"
    match_related:
      prefect.resource.name: "flow_a/dep-a"   # <flow>/<deployment>
  actions:
    - type: run-deployment
      source: selected
      deployment_id: "b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2"  # dep-b UUID
      schedule_after: 0

- name: "Trigger C after B completes"
  description: "Start dep-c after dep-b completes"
  enabled: true
  trigger:
    type: event
    posture: Reactive
    expect: ["prefect.flow-run.Completed"]
    threshold: 1
    within: 0
    match:
      prefect.resource.id: "prefect.flow-run.*"
    match_related:
      prefect.resource.name: "flow_b/dep-b"
  actions:
    - type: run-deployment
      source: selected
      deployment_id: "c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3"  # dep-c UUID
      schedule_after: 0
- Apply it at startup:
Copy code
prefect automations create --from-file automation_chain.yaml
Notes: - This creates new automations each time you run it. To avoid duplicates, either: - Make your startup script idempotent (check existing by name and delete/update), or - Use the Python client (Option 3) to upsert by name. Option 3: Use the Python client to create/update automations - Most flexible, lets you check for existing automations and update instead of duplicating. ``` import asyncio from uuid import UUID from prefect.automations import Automation, EventTrigger, Posture from prefect.events.actions import RunDeployment from prefect.client.orchestration import get_client async def upsert_automation(name: str, trigger: EventTrigger, action: RunDeployment): async with get_client() as client: # Try to find by name existing = [a async for a in client.read_automations()] # list all found = next((a for a in existing if a.name == name), None) payload = Automation( name=name, enabled=True, trigger=trigger, actions=[action], ) if found: payload.id = found.id await payload.aupdate()
print(f"Updated automation: {name}") else: await payload.acreate() print(f"Created automation: {name}") async def main(): # Substitute these with your actual deployment UUIDs dep_b_id = UUID("b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2") dep_c_id = UUID("c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3") trigger_b = EventTrigger( posture=Posture.Reactive, expect={"prefect.flow-run.Completed"}, threshold=1, within=0, match={"prefect.resource.id": "prefect.flow-run.*"}, match_related={"prefect.resource.name": "flow_a/dep-a"}, ) action_b = RunDeployment(source="selected", deployment_id=dep_b_id, schedule_after=0) trigger_c = EventTrigger( posture=Posture.Reactive, expect={"prefect.flow-run.Completed"}, threshold=1, within=0, match={"prefect.resource.id": "prefect.flow-run.*"}, match_related={"prefect.resource.name": "flow_b/dep-b"}, ) action_c = RunDeployment(source="selected", deployment_id=dep_c_id, schedule_after=0) await upsert_automation("Trigger B after A completes", trigger_b, action_b) await upsert_automation("Trigger C after B completes", trigger_c, action_c) if name == "__main__": asyncio.run(main()) ``` A few practical tips - Use A’s cron on the deployment itself (via 
to_deployment(cron="...")
or your preferred deployment method). Then use automations to chain B and C. - Match by deployment name as shown (
flow_name/deployment_name
). If you prefer, you can match by the related resource ID of the deployment instead. - In docker-compose, ensure the script/container has 
PREFECT_API_URL
pointed at your server and any credentials (if using Cloud). - If you’ll re-run this on every compose up, favor the Python “upsert” approach to avoid duplicate automations. If you share how you’re creating your three deployments today (Python code, prefect.yaml, or CLI), I can tailor a ready-to-run snippet that wires up the exact names/IDs you’re using.
p
@Marvin I've done Option 1: Define triggers on the deployments in code and I'm yet to test it, but now on the UI when i click on 'automations' tab nothing happens, I could previously load the page. But it seems with these custom automations it will not load
m
✅ thought for 238.0 seconds
Thanks for the heads up — adding deployment triggers should not prevent the Automations tab from loading, so this sounds like either a UI bug or a version mismatch between your SDK (where you created the triggers) and the server/UI image. A few quick checks to isolate the issue: 1) Verify the API for automations is healthy - From your compose host/container:
Copy code
prefect automations ls
prefect automations ls --output json
If these error, please share the traceback — that points to a backend data/validation issue. If they succeed, it’s likely a frontend-only issue. 2) Check versions match - In the container that ran your deployment code:
Copy code
prefect version
- In the server/UI container:
Copy code
prefect version
If they’re far apart, upgrade/pin them to the same 3.x version. Version drift can create records (e.g., trigger schemas) the UI doesn’t understand. 3) Browser and network checks - Try a hard refresh, an incognito window, or a different browser. - Open DevTools (Console + Network) on the Automations tab and see if any request fails (e.g., a 500 with a JSON parse/validation error). If you can, share the failing request path and the error. 4) Server logs - Tail the Prefect server logs while loading the Automations tab to catch any exceptions from the API. Workarounds/unblockers while we debug: - You can still manage automations via CLI even if the UI page is stuck: - List:
Copy code
prefect automations ls --output json
- Inspect one:
Copy code
prefect automations inspect <automation-id-or-name>
- Pause/disable any suspect ones (in case a malformed record is tripping rendering):
Copy code
prefect automations pause --id <id>
- Since you used “Option 1” (deployment triggers in code), you can temporarily re-serve the same deployments without triggers to confirm the UI loads again: - Comment out the 
triggers=[...]
in your 
to_deployment(...)
calls - Re-run your deploy/serve step - Reload the Automations tab If it’s reproducible, I’d love these details to help us pinpoint it: - Prefect version(s) for both server and the environment that created the triggers - A minimal snippet of the 
DeploymentEventTrigger
you used (just the fields/shape) - The failing request/stack trace from the browser console or server logs Once we have that, I can suggest a precise fix or open a bug for the UI if needed.
p
@Marvin i just rebuilt the composition and now i can see the automations. The issue now is that the actual automation doesn't trigger. Here is the json via the UI of the automation: { "type": "event", "match": {}, "match_related": { "prefect.resource.name": "iit_dbt_run/iit_dbt_run" }, "after": [], "expect": [ "prefect.flow-run.Completed" ], "for_each": [], "posture": "Reactive", "threshold": 1, "within": 0 } here is the python of the file _iit_dbt_run_._serve_( name="iit_dbt_run", triggers=[ _DeploymentEventTrigger_( expect={"prefect.flow-run.Completed"}, _match_related_={"prefect.resource.name": "iit_pre_dbt_syncs/iit_pre_dbt_syncs"}, # flow/deployment ) ] ) I did a quick run of iit_pre_dbt_syncs and when it finished it did not trigger the iit_dbt_run
m
✅ thought for 217.6 seconds
Thanks for the details — that helps. Your downstream deployment’s trigger likely isn’t matching the upstream event. Here’s how to get it firing. What’s probably wrong - The related resource match must target the upstream deployment resource as it appears on events. Using flow/deployment combined like flow/deployment is valid in some places, but event matching uses deployment resource labels. - Your JSON from the UI shows the automation created for iit_dbt_run is matching:
Copy code
match_related:
    prefect.resource.name: "iit_dbt_run/iit_dbt_run"
That would match events related to the iit_dbt_run deployment itself, not the upstream iit_pre_dbt_syncs deployment. So it won’t fire when iit_pre_dbt_syncs completes. What to change - Point the downstream trigger’s match_related at the upstream deployment. - Match by deployment name or by deployment id; both work. Name is simpler, ID is most reliable. Example fixes for your code Option A: Match by upstream deployment name
Copy code
_iit_dbt_run_._serve_(
    _name_="iit_dbt_run",
    _triggers_=[
        _DeploymentEventTrigger_(
            _expect_={"prefect.flow-run.Completed"},
            _match_related_={
                "prefect.resource.name": "iit_pre_dbt_syncs"  # the upstream deployment name only
                # optionally also pin the role:
                # "prefect.resource.role": "deployment"
            },
            # optionally give a small window to tolerate event latency:
            # _within_=timedelta(seconds=10),
        )
    ]
)
Option B: Match by upstream deployment ID (most robust) - First, get the upstream deployment id:
Copy code
prefect deployment ls --output json
- Then set:
Copy code
_match_related_={
    "prefect.resource.id": "prefect.deployment.<UPSTREAM_DEPLOYMENT_UUID>"
}
Important notes - Do not use flow_name/deployment_name in match_related. Event resources use: - prefect.resource.id: "prefect.deployment.<uuid>" - prefect.resource.name: "<deployment-name>" - prefect.resource.role: "deployment" - Ensure the upstream flow run was started from a deployment. If you ran the flow ad-hoc (not via deployment), the Completed event will not include a related deployment resource and your match won’t hit. You can match by flow instead in that case:
Copy code
_match_related_={
      "prefect.resource.name": "iit_pre_dbt_syncs_flow",   # the flow name
      "prefect.resource.role": "flow"
  }
How to debug quickly 1) Stream events while you run the upstream deployment:
Copy code
prefect event stream --format json
2) Look for a “prefect.flow-run.Completed” event for iit_pre_dbt_syncs. Confirm it has a related resource like:
Copy code
{
  "prefect.resource.id": "prefect.deployment.<uuid>",
  "prefect.resource.role": "deployment",
  "prefect.resource.name": "iit_pre_dbt_syncs"
}
3) Adjust your match_related to exactly match those labels. Optional: increase the window - If you suspect minor delays, add a small within window:
Copy code
_within_=timedelta(seconds=10)
Reactive triggers allow 0, but a few seconds gives breathing room. Recap - Point match_related at the upstream deployment’s resource labels, not the downstream. - Prefer matching by id or by the bare deployment name (not flow/deployment). - Confirm the upstream run emits a Completed event with a deployment-related resource using prefect event stream. If you share the exact upstream deployment name and what you see in the event stream for its Completed event, I’ll give you a copy-paste-correct trigger block.
p
@Marvin please help me with these two errors tell me what this prefect error is
Copy code
2026-03-19T13:05:49.360304849Z   File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2363, in _handle_dbapi_exception
2026-03-19T13:05:49.360307889Z     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
2026-03-19T13:05:49.360310829Z   File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
2026-03-19T13:05:49.360313849Z     self.dialect.do_execute(
2026-03-19T13:05:49.360316689Z   File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 952, in do_execute
2026-03-19T13:05:49.360319769Z     cursor.execute(statement, parameters)
2026-03-19T13:05:49.360322619Z   File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 182, in execute
2026-03-19T13:05:49.360325659Z     self._adapt_connection._handle_exception(error)
2026-03-19T13:05:49.360328519Z   File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 342, in _handle_exception
2026-03-19T13:05:49.360335879Z     raise error
2026-03-19T13:05:49.360338759Z   File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 164, in execute
2026-03-19T13:05:49.360341799Z     self.await_(_cursor.execute(operation, parameters))
2026-03-19T13:05:49.360344758Z   File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 132, in await_only
2026-03-19T13:05:49.360347758Z     return current.parent.switch(awaitable)  # type: ignore[no-any-return,attr-defined] # noqa: E501
2026-03-19T13:05:49.360350778Z            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2026-03-19T13:05:49.360353618Z   File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 196, in greenlet_spawn
2026-03-19T13:05:49.360356638Z     value = await result
2026-03-19T13:05:49.360359448Z             ^^^^^^^^^^^^
2026-03-19T13:05:49.360362258Z   File "/usr/local/lib/python3.11/site-packages/aiosqlite/cursor.py", line 40, in execute
2026-03-19T13:05:49.360365238Z     await self._execute(self._cursor.execute, sql, parameters)
2026-03-19T13:05:49.360368148Z   File "/usr/local/lib/python3.11/site-packages/aiosqlite/cursor.py", line 32, in _execute
2026-03-19T13:05:49.360371398Z     return await self._conn._execute(fn, *args, **kwargs)
2026-03-19T13:05:49.360374288Z            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2026-03-19T13:05:49.360377148Z   File "/usr/local/lib/python3.11/site-packages/aiosqlite/core.py", line 160, in _execute
2026-03-19T13:05:49.360380128Z     return await future
2026-03-19T13:05:49.360382928Z            ^^^^^^^^^^^^
2026-03-19T13:05:49.360385727Z   File "/usr/local/lib/python3.11/site-packages/aiosqlite/core.py", line 63, in _connection_worker_thread
2026-03-19T13:05:49.360388737Z     result = function()
2026-03-19T13:05:49.360391577Z              ^^^^^^^^^^
2026-03-19T13:05:49.360394377Z sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked
2026-03-19T13:05:49.360397357Z [SQL: UPDATE deployment SET last_polled=:last_polled, status=:status, updated=deployment.updated WHERE deployment.id IN (:id_1_1) OR deployment.work_queue_id IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)]
2026-03-19T13:05:49.360400537Z [parameters: {'last_polled': '2026-03-19 13:05:49.334934', 'status': 'READY', 'id_1_1': '8d6e3607-3b21-49e1-b183-e37d6b55f43b'}]
2026-03-19T13:05:49.360407667Z (Background on this error at: <https://sqlalche.me/e/20/e3q8>)
m
✅ thought for 45.9 seconds
Got it — I only see one error in your message. Can you paste the second one too? I’ll cover the one you shared now: What this error is - Root cause: SQLite “database is locked” during a write - It’s raised by SQLAlchemy/aiosqlite while Prefect’s server is updating the deployments table:
Copy code
UPDATE deployment
  SET last_polled=:last_polled, status=:status, updated=deployment.updated
  WHERE deployment.id IN (:id_1_1) ...
- SQLite allows only a single writer at a time. If another transaction has the DB locked (even briefly), concurrent writers will hit this OperationalError. Why it’s happening in Prefect - With Prefect Server on SQLite, multiple background services (API, scheduler, automations, etc.) can write at the same time. Under load or with multiple server instances, SQLite’s single-writer model leads to “database is locked”. - This is not a Prefect-specific bug; it’s a limitation of SQLite under concurrent writes. It can cause missed/slow updates (e.g., deployment status, last_polled) which might explain automations not triggering promptly. How to fix (recommended → most robust) 1) Move the server DB to Postgres (recommended for any multi-user or automation-heavy setup) - In docker-compose, add Postgres and point Prefect to it via PREFECT_API_DATABASE_CONNECTION_URL:
Copy code
services:
  postgres:
    image: postgres:15
    environment:
      POSTGRES_USER: prefect
      POSTGRES_PASSWORD: prefect
      POSTGRES_DB: prefect
    volumes:
      - pgdata:/var/lib/postgresql/data

  prefect:
    image: prefecthq/prefect:3-python3.11
    command: prefect server start
    environment:
      PREFECT_API_DATABASE_CONNECTION_URL: <postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
      PREFECT_SERVER_API_HOST: 0.0.0.0
    ports:
      - "4200:4200"
    depends_on:
      - postgres

volumes:
  pgdata:
- Prefect will run migrations on startup; no extra steps needed. 2) If you must stay on SQLite (dev only) - Ensure only a single Prefect server container is running (no duplicates or scaled replicas). - Avoid sharing the SQLite DB file across containers or network filesystems. - Increase the SQLite busy timeout so writers wait instead of failing immediately: - Set the connection URL with a timeout, e.g.:
Copy code
PREFECT_API_DATABASE_CONNECTION_URL=sqlite+aiosqlite:////root/.prefect/prefect.db?timeout=30.0
- Then restart the server. - As a last resort, restart the Prefect server to clear a stuck lock. Quick sanity checks - Confirm one server instance: - Check docker ps for duplicate Prefect containers. - Tail server logs while you run upstream/downstream deployments to see if locks correlate with missed automations. - If automations still don’t fire, run:
Copy code
prefect event stream --format json
Trigger your upstream deployment and verify you see a “prefect.flow-run.Completed” event with a related resource for the upstream deployment. If it’s missing, your trigger’s match won’t hit. Please share: - The second error you mentioned - Your docker-compose for Prefect + DB (to spot anything that could cause concurrent writes) - Whether you’re using Postgres or SQLite right now I’ll tailor exact changes once I see those.
p
@Marvin COMMAND CREATED STATUS PORTS "bash '\n python ..." 10 minutes ago Up minutes "bash '\n python ..." 10 minutes ago Up 9 minutes "bash '\n python ..." 10 minutes ago Up 9 minutes "bash -C "In python ..." 10 minutes ago Up 9 minutes "bash -c '\n python .." 10 minutes ago Up 9 minutes "bash -c '\n python ..." 10 minutes ago Up 9 minutes "bash ー "\n python ..." 10 minutes ago Exited (0) 9 minutes ago NAMES orchestrator-prefect-test_messaging-1 orchestrator-prefect-iit_post_dbt_jobs_deployment-1 orchestrator-prefect-log_cc_activity_deployment-1 orchestrator-prefect-weekly_maintenance-1 orchestrator-prefect-iit_pre_dbt_sync_deployment-1 orchestrator-prefect-iit_dbt_ run_deployment-1 orchestrator-prefect-secret_loader-1
m
✅ thought for 51.2 seconds
Thanks — that list helps. A few things jump out that likely explain both the “database is locked” and the automation not triggering. 1) Possible deployment name mismatch (why the automation didn’t fire) - I see a container named: - orchestrator-prefect-iit_dbt_ run_deployment-1 Notice the space: “iit_dbt_ run”. If your trigger is matching “iit_dbt_run” (no space), it won’t match the upstream event’s related deployment name. - Action: - Verify the exact names Prefect is using:
Copy code
prefect deployment ls --output json
- Update your trigger to match the exact upstream deployment name, or better, match by deployment ID:
Copy code
_match_related_={
        "prefect.resource.id": "prefect.deployment.<UPSTREAM_DEPLOYMENT_UUID>"
    }
- Also make sure the upstream run is started as a deployment (not a manual flow run), otherwise the Completed event won’t include a related deployment and your match won’t hit. - Quick debug:
Copy code
prefect event stream --format json
Then run the upstream deployment. In the Completed event, confirm there’s a related resource like:
Copy code
{
  "prefect.resource.role": "deployment",
  "prefect.resource.name": "iit_pre_dbt_syncs",
  "prefect.resource.id": "prefect.deployment.<uuid>"
}
Adjust your match to exactly match these labels. 2) SQLite “database is locked” (why updates/automations can be flaky) - You’re on SQLite and have multiple long-running containers. While flow-run containers are fine, the lock is from the Prefect API trying to update the deployments table. SQLite allows only one writer at a time; concurrent writes can hit OperationalError and delay/skip updates like last_polled/status, which can affect automations. - Fix options: - Preferred: switch Prefect Server to Postgres in docker-compose:
Copy code
services:
      postgres:
        image: postgres:15
        environment:
          POSTGRES_USER: prefect
          POSTGRES_PASSWORD: prefect
          POSTGRES_DB: prefect
        volumes:
          - pgdata:/var/lib/postgresql/data

      prefect:
        image: prefecthq/prefect:3-python3.11
        command: prefect server start
        environment:
          PREFECT_API_DATABASE_CONNECTION_URL: <postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
          PREFECT_SERVER_API_HOST: 0.0.0.0
        ports:
          - "4200:4200"
        depends_on:
          - postgres

    volumes:
      pgdata:
- If you must stay on SQLite (dev only): - Ensure only one Prefect server container is running. - Don’t share the SQLite file across containers/hosts. - Increase the SQLite busy timeout so writers wait instead of failing:
Copy code
PREFECT_API_DATABASE_CONNECTION_URL=sqlite+aiosqlite:////root/.prefect/prefect.db?timeout=30.0
- Restart the server container after changes. 3) Sanity checks to run now - Confirm you only have a single server instance: - Check for any containers running “prefect server start”; there should be exactly one. - Verify automations exist and are enabled:
Copy code
prefect automations ls --output json
- Stream events while testing:
Copy code
prefect event stream --format json
Then:
Copy code
prefect deployment run "iit_pre_dbt_syncs/<deployment-name-exact>"
If you don’t see a Completed event with a related deployment, your match won’t fire. If you can share: - The output of 
prefect deployment ls --output json
for both upstream and downstream (to confirm exact names and IDs) - Your server’s DB connection URL (env var value) and a quick snippet of your docker-compose for the Prefect server I’ll give you a copy-paste-correct trigger match and any compose tweaks needed.
p
@Marvin this is my docker-compose file. I'm still getting the lock issue even with the timeout env var x-project-image: &prefect-img image: registry.com/prefect:prod_latest x-prefect-common: &prefect-common <<: *prefect-img tty: true stdin_open: true working_dir: /opt/project volumes: _# required for email to send. CREDENTIALS_PATH defaults to ./utils for local dev; set to /opt/credentials/prefect in portainer_ - ${CREDENTIALS_PATH ./utils}/gmail credentials.json/opt/project/utils/gmail_credentials.json:ro - ${CREDENTIALS_PATH ./utils}/gmail token.json/opt/project/utils/gmail_token.json x-prefect-deploy-common: &prefect-deploy-common <<: *prefect-common depends_on: server: {condition: service_healthy} secret_loader: {condition: service_completed_successfully} environment: PYTHONPATH: /opt/project # this is required so that imports into files the deployment folder can be prefect-served from a directory PREFECT_API_URL: http://server:4200/api PREFECT_API_AUTH_STRING: ${PREFECT_SERVER_API_AUTH_STRING} # needed to authorise deployment PREFECT_API_DATABASE_CONNECTION_URL: sqlite+aiosqlite:////root/.prefect/prefect.db?timeout=30.0 services: server: <<: *prefect-img command: prefect server start --host 0.0.0.0 ports: - "4200:4200" environment: PREFECT_PROFILES_ACTIVE: server PREFECT_SERVER_API_AUTH_STRING: ${PREFECT_SERVER_API_AUTH_STRING} PREFECT_API_AUTH_STRING: ${PREFECT_SERVER_API_AUTH_STRING} PREFECT_UI_API_URL: ${DOMAIN_URL_API} PREFECT_SERVER_SERVICES_DB_VACUUM_ENABLED: true PREFECT_API_DATABASE_CONNECTION_URL: sqlite+aiosqlite:////root/.prefect/prefect.db?timeout=30.0 volumes: - prefect_data:/root/.prefect healthcheck: test: [ "CMD-SHELL", "python - <<'PY'\nimport sys, urllib.request, json, time\nurl='http://localhost:4200/api/health'\ntry:\n with urllib.request.urlopen(url, timeout=3) as r:\n sys.exit(0 if r.status==200 else 1)\nexcept Exception:\n sys.exit(1)\nPY" ] interval: 5s timeout: 3s retries: 40 secret_loader: <<: *prefect-common depends_on: server: {condition: service_healthy} command: > bash -c " python utils/save_secrets.py " volumes: - ${CREDENTIALS_PATH ./utils}/snowflake.pem/opt/project/utils/snowflake.pem:ro iit_pre_dbt_sync_deployment: <<: *prefect-deploy-common depends_on: server: {condition: service_healthy} secret_loader: {condition: service_completed_successfully} command: > bash -c " python deployments/iit_pre_dbt_syncs.py " iit_dbt_run_deployment: <<: *prefect-deploy-common command: > bash -c " python deployments/iit_dbt_run.py " iit_post_dbt_jobs_deployment: <<: *prefect-deploy-common command: > bash -c " python deployments/iit_post_dbt_jobs.py " log_cc_activity_deployment: <<: *prefect-deploy-common command: > bash -c " python deployments/log_cc_activity.py " test_messaging: <<: *prefect-deploy-common command: > bash -c " python deployments/test_messaging.py " weekly_maintenance: <<: *prefect-deploy-common command: > bash -c " python deployments/weekly_maintenance.py " volumes: - /var/run/docker.sock:/var/run/docker.sock - ${CREDENTIALS_PATH ./utils}/gmail credentials.json/opt/project/utils/gmail_credentials.json:ro - ${CREDENTIALS_PATH ./utils}/gmail token.json/opt/project/utils/gmail_token.json volumes: prefect_data:
m
✅ thought for 83.5 seconds
Thanks for sharing the compose — this explains why you’re still seeing “database is locked.” What’s happening - You’re running Prefect Server on SQLite with many concurrent writes (scheduler, automations, events, flow-run state updates, etc.). SQLite only allows one writer at a time. Under load, you will routinely hit:
Copy code
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked
- The 
?timeout=30.0
just makes writers wait longer; it does not remove the contention. With Prefect’s background services, it’s common to exceed that window. Two concrete fixes (recommended first) 1) Move the Prefect Server DB to Postgres (recommended) - This eliminates SQLite’s single-writer bottleneck and is the supported option for multi-user/automation-heavy setups. - Compose example (swap your server service to use Postgres):
Copy code
services:
  postgres:
    image: postgres:15
    environment:
      POSTGRES_USER: prefect
      POSTGRES_PASSWORD: prefect
      POSTGRES_DB: prefect
    volumes:
      - pgdata:/var/lib/postgresql/data

  server:
    image: <http://registry.com/prefect:prod_latest|registry.com/prefect:prod_latest>
    command: prefect server start --host 0.0.0.0
    ports:
      - "4200:4200"
    environment:
      PREFECT_PROFILES_ACTIVE: server
      PREFECT_SERVER_API_AUTH_STRING: ${PREFECT_SERVER_API_AUTH_STRING}
      PREFECT_API_AUTH_STRING: ${PREFECT_SERVER_API_AUTH_STRING}
      PREFECT_UI_API_URL: ${DOMAIN_URL_API}
      PREFECT_SERVER_SERVICES_DB_VACUUM_ENABLED: true
      PREFECT_API_DATABASE_CONNECTION_URL: <postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
    depends_on:
      - postgres
    volumes:
      - prefect_data:/root/.prefect

volumes:
  pgdata:
  prefect_data:
Notes: - Prefect Server will run DB migrations automatically on startup. - If you need to preserve historical data from SQLite, there’s no built-in migration from SQLite → Postgres; most users start fresh (or export/import selectively). 2) If you must stay on SQLite (dev-only) - Keep only one server container. - Keep the DB file on a local Docker volume (not NFS/remote FS) — you already do. - Remove 
PREFECT_API_DATABASE_CONNECTION_URL
from all non-server services. Only the server should know about the DB connection; clients should only talk to 
PREFECT_API_URL
. In your compose, these deploy/test containers set the DB URL needlessly:
Copy code
PREFECT_API_DATABASE_CONNECTION_URL: sqlite+aiosqlite:////root/.prefect/prefect.db?timeout=30.0
Please remove that from every service except 
server
. - Optionally, try WAL mode to reduce writer stalls (still not as good as Postgres): - Add a tiny init step to set PRAGMAs inside the server before it starts:
Copy code
command: bash -lc "
      python - <<'PY'
import sqlite3, os
db='/root/.prefect/prefect.db'
os.makedirs('/root/.prefect', exist_ok=True)
con=sqlite3.connect(db)
con.execute('PRAGMA journal_mode=WAL;')
con.execute('PRAGMA synchronous=NORMAL;')
con.commit(); con.close()
PY
      && prefect server start --host 0.0.0.0
    "
- This helps some workloads, but won’t eliminate locks under concurrent writes. Why remove the DB URL from non-server services? - Those containers should only call the Server API (`PREFECT_API_URL=http://server:4200/api`). Pointing them at a SQLite file path does nothing helpful and can accidentally initialize their own local SQLite files if anything attempts a direct DB connection in-process. It won’t fix the server’s locking and can add confusion. Quick sanity checklist - Exactly one server process is running (no duplicate “prefect server start” containers). - Only the server has `PREFECT_API_DATABASE_CONNECTION_URL`; other services have only 
PREFECT_API_URL
(and auth). - If staying on SQLite, apply WAL PRAGMAs and keep the timeout; expect occasional locks under load anyway. - Best: switch to Postgres; this will resolve the lock errors. If you want, paste: - The exact lock trace with timestamps from the server container logs
- The output of 
docker ps
filtered to the server I’ll double-check there’s only a single server and confirm no other process is touching the SQLite file. If you’d like, I can also provide a ready-to-use compose override for Postgres tailored to your current file.
p
@Marvin i'm still getting the error with sqlite method. Also I think unrelated, i'm getting Can't connect to Server API at https://prefect.cwc-data.uk/api. Check that it's accessible from your machine. and all my deployments are timing out. What is it in the docker-compose thats refusing the connection? It was working before I added a new deployment so unsure what I changed in the docker-compose to cause this block of comunication
m
✅ thought for 92.0 seconds
Thanks for the compose. You’ve got two separate issues here: A) SQLite “database is locked” keeps happening B) Your deployment containers are trying to talk to https://prefect.cwc-data.uk/api instead of http://server:4200/api Let’s fix B first (it’s causing timeouts), then circle back to A. B) “Can’t connect to Server API at https://prefect.cwc-data.uk/api” What this means - That error is emitted by the Python client when PREFECT_API_URL resolves to https://prefect.cwc-data.uk/api in that container. In your compose, clients should be using the internal URL http://server:4200/api. Why this can happen even with your compose - A Prefect profile baked into your base image may be setting PREFECT_API_URL to your domain and is winning because your service didn’t actually get the env you expect. - Or a service is not inheriting the x-prefect-deploy-common anchor you think it is. - Or the env var is set, but something inside the container is overriding it (e.g., code calling load_profile). Verify what the container actually sees Run these in one of the failing deployment containers (exec into it):
Copy code
printenv | grep PREFECT_
prefect config view
curl -sSf <http://server:4200/api/health>
curl -sSf <https://prefect.cwc-data.uk/api/health>
prefect profile ls
- If prefect config view shows PREFECT_API_URL = https://prefect.cwc-data.uk/api, your env is not applied or a profile is overriding. Env vars should take precedence; if they’re missing, the profile wins. Concrete fixes - Ensure every deployment service inherits x-prefect-deploy-common so they get: - PREFECT_API_URL=http://server:4200/api - PREFECT_API_AUTH_STRING (matching the server) - Remove PREFECT_UI_API_URL from client containers (it’s only for the UI; yours is only on the server, which is fine). - If your image has a default profile that points to your domain, either: - Set PREFECT_PROFILES_ACTIVE: default in all client services, or - Explicitly export PREFECT_API_URL in each client service (which you do) and confirm it’s present with printenv. - If your code calls load_profile("cloud") or similar, remove it; prefer the env var. A) SQLite “database is locked” What’s happening - Prefect Server writes frequently (scheduler, events, automations). SQLite’s single-writer model causes routine lock contention under this load; timeout=30 only makes writers wait, it doesn’t remove the bottleneck. Critical compose changes - Only the server should have PREFECT_API_DATABASE_CONNECTION_URL. In your compose, it’s set for ALL services via x-prefect-deploy-common:
Copy code
PREFECT_API_DATABASE_CONNECTION_URL: sqlite+aiosqlite:////root/.prefect/prefect.db?timeout=30.0
Remove that line from x-prefect-deploy-common entirely. Clients should not know about the DB; they only hit the API:
Copy code
PREFECT_API_URL: <http://server:4200/api>
- Keep the DB URL only on the server service. Best fix (recommended) - Switch the server to Postgres; this eliminates the lock errors:
Copy code
services:
  postgres:
    image: postgres:15
    environment:
      POSTGRES_USER: prefect
      POSTGRES_PASSWORD: prefect
      POSTGRES_DB: prefect
    volumes:
      - pgdata:/var/lib/postgresql/data

  server:
    image: <http://registry.com/prefect:prod_latest|registry.com/prefect:prod_latest>
    command: prefect server start --host 0.0.0.0
    ports: ["4200:4200"]
    environment:
      PREFECT_SERVER_API_AUTH_STRING: ${PREFECT_SERVER_API_AUTH_STRING}
      PREFECT_API_AUTH_STRING: ${PREFECT_SERVER_API_AUTH_STRING}
      PREFECT_UI_API_URL: ${DOMAIN_URL_API}
      PREFECT_API_DATABASE_CONNECTION_URL: <postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
    depends_on: [postgres]
    volumes:
      - prefect_data:/root/.prefect

volumes:
  pgdata:
  prefect_data:
If you must stay on SQLite (dev-only) - Ensure exactly one server container is running. - Keep DB on a local Docker volume (you already do). - Consider enabling WAL mode (still not a silver bullet): ``` command: bash -lc " python - <<'PY' import sqlite3, os
db='/root/.prefect/prefect.db' os.makedirs('/root/.prefect', exist_ok=True) con=sqlite3.connect(db) con.execute('PRAGMA journal_mode=WAL;') con.execute('PRAGMA synchronous=NORMAL;') con.commit(); con.close() PY && prefect server start --host 0.0.0.0 "
Copy code
Action checklist for you
1) Remove PREFECT_API_DATABASE_CONNECTION_URL from x-prefect-deploy-common (all client services).
2) Rebuild/restart. Then in a client container:
printenv | grep PREFECT_API_URL prefect config view curl -sSf http://server:4200/api/health
Copy code
Confirm it points at <http://server:4200/api> and health returns 200.
3) If you still see the domain URL in config view, a profile in your image is overriding. Run:
prefect profile ls prefect profile inspect <name>
Copy code
Then set in the service:
PREFECT_PROFILES_ACTIVE: default ``` or explicitly override PREFECT_API_URL again (env should win). 4) Strongly consider switching the server to Postgres to end the lock errors. If you paste: - The output of “prefect config view” and “printenv | grep PREFECT_” from a failing container - The output of “curl -sSf http://server:4200/api/health” I’ll pinpoint exactly what’s overriding your API URL and provide a minimal patch.
p
@Marvin aha! it's a bug It works fine, if I do not put , retries=1, _retry_delay_seconds_=1800 in the flow decorator for flow functions that are served
m
✅ thought for 122.5 seconds
Good find — but I don’t think this is a Prefect bug. Adding flow-level retries changes the state timeline and emitted events, which can make your automation appear “stuck” if you’re listening for Completed. What’s likely happening - Your downstream automation expects “prefect.flow-run.Completed”. - With 
retries=1, retry_delay_seconds=1800
on the upstream flow: - If the first attempt fails, the flow run enters AwaitingRetry and is scheduled 30 minutes later. - There is no “Completed” event until the retry actually runs and succeeds. - So your automation won’t fire until that final success event is emitted. - When you remove retries, your run either: - Succeeds quickly → Completed fires → automation triggers; or - Fails (no retries) → there’s still no Completed event, but you’re not waiting 30 minutes so it looks different. How to confirm quickly 1) Check the upstream run’s final state in UI (or CLI):
Copy code
prefect flow-run ls --name <your-run-name>
   prefect flow-run inspect <flow-run-id>
If it’s “AwaitingRetry” or “Scheduled” for the retry, your Completed event hasn’t happened yet. 2) Stream events while testing:
Copy code
prefect event stream --format json
- Start the upstream deployment. - Watch for “prefect.flow-run.Completed” for it. - If you only see “prefect.flow-run.AwaitingRetry” (and later a new “Started”), that’s why the downstream didn’t fire yet. If you want the chain to trigger only on success (recommended for A→B→C), keep your trigger expecting Completed and let the upstream finish retrying first. If you want to trigger earlier (e.g., on first failure scheduled for retry) - You can broaden the trigger to include the retry state, but beware of duplicate triggers: - Adding multiple event names can cause multiple downstream runs (on AwaitingRetry and on final Completed). - If you truly need that, consider a more advanced trigger design (e.g., sequence/compound with “after” and a larger “within”) or separate automations with guardrails. Safer alternatives - Keep flow-level success chaining: expect only “prefect.flow-run.Completed” and be aware of the retry delay. - Or move retries into specific tasks inside the upstream flow so the flow is more likely to complete in the first run. - Or shorten 
retry_delay_seconds
while you’re testing to prove the chain works. Also double-check your trigger match - Make sure you’re matching the upstream deployment exactly (by name or, better, by id). Example:
Copy code
DeploymentEventTrigger(
      expect={"prefect.flow-run.Completed"},
      match_related={"prefect.resource.id": "prefect.deployment.<UPSTREAM_UUID>"},
      # optionally give a bit of window:
      # within=timedelta(seconds=10),
  )
- Verify what the event actually contains (from the event stream). Related deployment appears as:
Copy code
{
  "prefect.resource.role": "deployment",
  "prefect.resource.name": "<deployment-name>",
  "prefect.resource.id": "prefect.deployment.<uuid>"
}
One more thing: your earlier SQLite “database is locked” can delay state updates and event processing - Even if not the primary cause here, it can make this harder to observe in real time. - Strongly consider switching the server to Postgres to eliminate lock contention, as noted earlier. Docs for reference - Automations and triggers: Automations - Event stream CLI: Event stream - Deployment triggers in code: Deployment triggers If you can share: - The upstream run’s final state and its event payload from 
prefect event stream
, - Your exact 
DeploymentEventTrigger
block, I can confirm the matching and suggest a dedup-safe trigger if you truly need to fire on retry scheduling.
p
@Marvin I found the issue, so I have the UI behind a reverse proxy, and when I rebuilt the composition with the new prefect image, it needed reauthentication with user password. However, on the browser that it worked previously, it was just a black screen when I tried to load the prefect UI. I used another browser and the UI loaded asking for the user and password. After authgenticating on the other browser, the original browser worked fine Is thios something to do with cookies?
m
✅ thought for 42.9 seconds
Yes — this is almost certainly auth state cached in the browser (cookies/localStorage) and/or stale cached UI assets. What likely happened - After you rebuilt with a new Prefect image, the server started requiring re-auth (you have PREFECT_SERVER_API_AUTH_STRING/PREFECT_API_AUTH_STRING set). - One browser still had a stale auth token or cached assets. The UI loaded the old bundle, attempted API calls with the old Authorization value, got 401s, and didn’t render the login prompt (black screen). - A fresh browser had no cached state, so it correctly prompted for the API auth string. Once you authenticated there, your server was fine — the original browser just needed its local state cleared. How to fix when it happens - Do a hard refresh with cache clear or explicitly clear site data for your Prefect domain: - Chrome: Settings → Privacy → Cookies and other site data → See all site data → search your domain → Remove - Or DevTools → Application tab → Clear storage → Clear site data - If you want a quick nuke from DevTools Console on that tab:
Copy code
localStorage.clear();
  sessionStorage.clear();
  caches?.keys?.().then(keys => keys.forEach(k => caches.delete(k)));
Then reload the page to re-prompt for auth. - Try an incognito window as a test; if it works there, it’s definitely cached state. Reverse proxy considerations - Ensure the proxy forwards Authorization to /api: - NGINX example:
Copy code
proxy_set_header Authorization $http_authorization;
- Keep UI static assets and API behavior consistent; if you protect both with Basic Auth at the proxy, browsers will cache those creds per-origin — changing them can cause odd behavior until site data is cleared. - Confirm PREFECT_UI_API_URL matches the external URL you’re serving (and that the proxy routes /api correctly). General tips - If you rotate PREFECT_SERVER_API_AUTH_STRING, all browsers will need to re-auth; expect to clear site data or at least re-enter credentials. - When upgrading images, a hard refresh prevents mismatches between cached JS bundles and the new API. If you run into it again, open DevTools → Network: - Look for 401/403 on /api/* requests. - Check Console for errors about fetching assets (CORS or 401 on JS files). Share any error lines you see there and I can confirm if it’s a cached auth vs. proxy header forward issue.
48 Views
Open in Slack
PreviousNext
Powered by