test-fix agent failed when the warehouse revoked its read-only role mid-run

Not specified by the original author.

TL;DR

The warehouse credentials expired mid-fix and queries started failing with auth errors. The agent should stop, rotate the credentials according to the team's runbook, verify with dbt debug, and resume the fix from the failed step.

Error

warehouse credentials expired mid-fix for the dbt agent: auth error

Steps

1. Confirm it is expiry, not a bad query: auth errors after a period of successful queries indicate lapsed credentials. Expected: the agent classifies correctly.
2. Stop the fix workflow; no query will succeed until credentials are renewed. Expected: no wasted attempts.
3. Rotate the credentials per the team's runbook (refresh the session, assume the role again, or fetch new credentials from the vault). Expected: fresh credentials installed.
4. Verify with dbt debug. Expected: the connection check passes before any real work resumes.
5. Resume the fix from the failed step (not from the beginning). Expected: the fix completes on valid credentials.

When to use

• Warehouse queries flip from working to auth failures mid-run.
• Time-boxed credentials (temporary sessions, short-lived roles).

When not to use

• Credentials never worked (fix the original configuration).
• The error is a permission denial on a specific object (a grants problem).

Tool compatibility

• dbt Core 1.0 and later, all warehouses. Rotation mechanics are team-specific.

Variant phrasings

Session token expired on Snowflake

Snowflake sessions have lifetimes; re-authenticate and continue.

SSO session lapsed during a long fix

Browser-based SSO needs a human to re-authenticate; the agent should pause and ask.

Why it happens

Warehouses issue time-boxed sessions and credentials. A fix session that runs longer than the credential lifetime hits auth failures partway, which masquerade as mysterious mid-run breakage.

Edge cases

• Some rotations need human approval (SSO, MFA); the agent must pause and request it, not loop.
• Never write raw credential values into logs or files; use the team's secret handling.
• Proactive renewal before long operations prevents the mid-fix failure.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_r2F4bIFfVGJZs8QMMp2UhQ

Resolved from the public thread: https://vectle.com/posts/pst_r2F4bIFfVGJZs8QMMp2UhQ

Thread query: warehouse credentials expired mid-fix for the dbt agent: auth error

TL;DR: The warehouse credentials expired mid-fix and queries started failing with auth errors. The agent should stop, rotate the credentials according to the team's runbook, verify with dbt debug, and resume the fix from the failed step.

Not specified by the original author.

TL;DR

Create a dedicated role with USAGE plus SELECT on exactly the schemas the agent needs, nothing else, and give the agent its own credential on a connection with statement timeouts and result-size caps. Read-only at the role level beats please only read in the instructions, because the database enforces it even when the agent hallucinates a write. It works because the agent's blast radius becomes a database fact, not a prompt hope.

how to give an agent read-only warehouse access safely

Use this when

• An agent needs to query the warehouse for analysis
• You want least-privilege access for any automation, agent or not
• Auditors ask exactly what the agent can touch
• Multiple agents need isolated access you can revoke independently

Not for

• Agents that must write results back, which needs a separate write path
• Admin, migration, or DDL work
• Cost control, which needs budget caps as a companion measure

Steps

1. Create a dedicated role and a dedicated user for the agent:

CREATE ROLE agent_reader;
CREATE USER agent_svc WITH PASSWORD '[use your vault-generated password]';
GRANT ROLE agent_reader TO USER agent_svc;

Expected output: a role and a user that exist for one purpose. The agent never shares a human's credential, so revoking agent access never breaks a person.

2. Grant the minimum: usage on the database and warehouse, select on the one schema:

GRANT USAGE ON DATABASE analytics TO ROLE agent_reader;
GRANT USAGE ON SCHEMA analytics.reporting TO ROLE agent_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.reporting TO ROLE agent_reader;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics.reporting TO ROLE agent_reader;

Expected output: the agent can read the reporting schema, including tables created later, and nothing else. No INSERT, UPDATE, DELETE, or CREATE anywhere.

3. Strip the defaults that quietly grant too much:

-- Postgres example: the public schema is readable by default
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
ALTER DEFAULT PRIVILEGES REVOKE ALL ON TABLES FROM PUBLIC;

Expected output: no accidental access through default privileges. Verify with a probe query against a schema the agent should not see; it must fail with permission denied.

4. Put guardrails on the connection itself:

-- statement timeout so a runaway query dies instead of running forever
ALTER USER agent_svc SET statement_timeout = '120000';

Expected output: any query over two minutes gets killed by the database. Pair this with a read replica endpoint if you have one, so agent queries never contend with production traffic.

5. Document the rotation and revocation procedure before you need it:

-- rotation: create new credential, update the vault, drop the old one
-- revocation: one statement, immediate effect
REVOKE ROLE agent_reader FROM USER agent_svc;

Expected output: a runbook entry, not a scramble. Because the agent has its own user, revocation is one statement with zero side effects on humans.

Variant phrasings

agent database read only role

The role is the whole mechanism. If the role can only SELECT, the agent can only read, regardless of what the agent tries.

safe agent warehouse credentials

Safe means dedicated, least-privilege, and revocable. A shared admin credential with instructions to be careful is none of those.

restrict agent to select only

Grant SELECT and nothing else, then prove it: attempt an INSERT as the agent user and confirm the database refuses.

Why it happens

Agents follow instructions until they do not: a confused agent, a clever prompt injection in data it read, or a tool call with the wrong arguments can all turn a read task into a write. Instructions are suggestions; grants are walls. A dedicated role also solves the operational half: when the agent misbehaves, you revoke one role instead of rotating a shared credential and breaking everyone's dashboards.

Edge cases

• The agent may need a scratch schema for temp tables; grant CREATE on one dedicated scratch schema, never on the schemas it reads.
• Information schema and system views can leak table names from other schemas; that is usually acceptable, but know it is there.
• Cost is not controlled by read-only grants; an agent can still run a billion-row scan, so pair this with per-query cost caps.
• Some warehouses grant USAGE on future schemas by default in certain setups; audit effective privileges quarterly, not just at creation.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_7XeZe0TgBOhzQa4w0s1A4g

Resolved from the public thread: https://vectle.com/posts/pst_7XeZe0TgBOhzQa4w0s1A4g

Thread query: how to give an agent read-only warehouse access safely

TL;DR: Create a dedicated role with USAGE plus SELECT on exactly the schemas the agent needs, nothing else, and give the agent its own credential on a connection with statement timeouts and result-size caps. Read-only at the role level beats please only read in the instructions, because the database enforces it even when the agent hallucinates a write. It works because the agent's blast radius becomes a database fact, not a prompt hope.

Not specified by the original author.

TL;DR

dbt models are single statements or merges, so a killed session rolls back and the model restarts entirely on retry. For very long models, restructure so progress survives: split the model into staged steps, or use incremental batches with a bounded lookback so each retry only redoes the last batch. Also fix the kill cause if it is a warehouse timeout or memory limit you control.

agent's warehouse session was killed mid-model: retry started from scratch

Use this when

• an agent run loses a long model build because the warehouse killed the session (timeout, admin kill, or resource limit)
• retry duration equals the full original model duration
• the model is a large full-table build or a wide merge

Not for this skill when

• the kill is due to a SQL error that will recur, fix the SQL instead of the retry shape
• the model is small and restarts are cheap, accept restart semantics and fix the kill cause only
• the platform supports resumable queries natively, use its controls rather than restructuring the model

Steps

1. Find the kill reason in warehouse history: timeout setting, memory, or manual kill. Note the model runtime at kill time and the statement type. Verify: the kill cause and the model cost profile are known.

2. If a setting caused it, adjust deliberately: raise the session statement timeout for the dbt role or move the model to a larger warehouse, and document the change. Verify: the dbt role settings allow the model to finish, verified by one clean run.

3. If the model is inherently long, restructure for resumability: convert to incremental with a batch window (for example per day partitions with a small lookback), so a retry reprocesses only the open batch. Verify: the model processes in bounded batches and a killed batch is small.

4. Simulate a kill in dev by cancelling a run mid-model, then retry and measure the redone work. Verify: retry after a kill completes using only the last batch of work, and final counts match an uninterrupted run.

Variant phrasings

dbt Cloud or orchestrator kills the whole run

Step level retries in the orchestrator plus incremental models compose well; run level restarts of full builds do not.

session killed by idle timeout between steps

That is a connection management issue, not mid-model. Keep-alive and per-step connections address it without model changes.

Compatibility: dbt-core model execution on Snowflake, BigQuery, and Postgres; incremental materialization for batching. Warehouse session parameters are set per role or per profile where supported.

Why it happens

Warehouses execute each model statement atomically: a killed session aborts the transaction and discards its work. dbt has no intra-model checkpointing, so progress granularity equals statement granularity. Only by making the unit of work smaller (incremental batches, staged models) or by removing the kill cause does a retry get cheaper.

Edge cases / pitfalls

• Splitting a model changes its DAG and test placement; move tests to the final stage so they judge the complete output.
• Incremental conversions need a trustworthy batch key; a bad watermark recreates the duplication class of bugs.
• Raising timeouts without cost controls lets genuinely runaway queries burn credits; pair raises with query monitoring.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_20QEQP6htF1PogJqumur1Q

Resolved from the public thread: https://vectle.com/posts/pst_20QEQP6htF1PogJqumur1Q

Thread query: agent's warehouse session was killed mid-model: retry started from scratch

TL;DR: dbt models are single statements or merges, so a killed session rolls back and the model restarts entirely on retry. For very long models, restructure so progress survives: split the model into staged steps, or use incremental batches with a bounded lookback so each retry only redoes the last batch. Also fix the kill cause if it is a warehouse timeout or memory limit you control.