There is an apparent contradiction at the heart of using AI agents for data work, and resolving it properly is worth an entire article, because the teams that resolve it are quietly getting enormous value while the teams that do not are generating incidents.
The contradiction: data engineering and analytics are disciplines built on determinism. The pipeline must produce the same output from the same input every run. The revenue number must reproduce, to the penny, when the auditor asks. The metric must mean the same thing on every dashboard. Meanwhile, the most powerful new tools in a data professional's kit, the agentic coding harnesses, Claude Code, OpenAI's Codex, Google's Antigravity line, OpenCode, and their peers, are built on language models, which are probabilistic by nature: ask twice, get two phrasings, sometimes two approaches, occasionally two answers.
The resolution is not to avoid the tools, and it is not to hope the models stop being stochastic. It is an architectural principle, old as software and newly urgent: use the agent to author deterministic artifacts, and let the artifacts do the work. The model's creativity lives at development time, where variance is cheap and review catches error. The runtime path, the thing that actually touches your data every night, is code: versioned, tested, reviewed, reproducible, exactly as deterministic as it ever was. Get this boundary right and the harnesses become the largest productivity gain data teams have seen in a decade. Get it wrong, agents improvising in the runtime path, numbers with no provenance, and you have built a very fast way to lose the business's trust.
This article is the full playbook: the artifact-first principle and the determinism ladder that operationalizes it, honest working profiles of the four harnesses named above as data tools specifically, the catalog of techniques, tests as contracts, dry-run gates, schema pinning, semantic layers, golden datasets, that make agent-assisted data work reproducible, the workflow patterns for pipelines, migrations, quality investigations, and analytics, and the anti-patterns that generate the incidents. My biases declared: I work at Dremio, whose MCP server is one of the governed doors these agents can knock on, and Claude Code is my personal daily driver, both of which I will weigh against fairness to the whole field.
The Principle: Agents Author, Artifacts Execute
State the core idea precisely, because everything else derives from it.
A language model invoked at runtime is a nondeterministic component in your data path: same question, potentially different SQL, different approach, different number. A language model invoked at development time is something else entirely: a collaborator producing an artifact, a SQL file, a dbt model, a pipeline script, a test suite, that is then frozen in version control, reviewed like any code, validated by tests, and executed by deterministic engines forever after. The variance happened, and it happened where variance belongs: before the merge, under review, against tests. After the merge, the pipeline is exactly as deterministic as one written by hand, because it is code, and code does not care who typed it.
This is why the coding harnesses, rather than chat interfaces, are the right tools for serious data work: they are built for the artifact workflow. They live in repositories, edit real files, run real commands, execute the tests, and produce diffs and pull requests, which means their entire interaction model already routes the model's output through the deterministic machinery, version control, CI, review, that data engineering trusts. The chat window asks you to copy-paste its suggestion into your world. The harness works inside your world, where the guardrails are.
One clarification before the ladder, because it prevents a common confusion: this principle does not forbid agents from ever running queries. Exploration, an agent reading schemas, sampling data, running diagnostic queries to understand a problem, is legitimate and valuable, and it is read-only reconnaissance in service of authoring. The line the principle draws is at the runtime path and at the numbers the business consumes: what executes on schedule is committed code, and what lands on a dashboard traces to a governed definition, never to an agent's improvisation that morning.
The Determinism Ladder: Five Levels of Trust
Teams adopt these tools along a maturity curve, and naming its levels gives you both a map and a diagnostic.
Level zero is chat-and-paste: asking a model questions about data and transcribing answers. No provenance, no reproducibility, no place in professional work beyond brainstorming, and worth naming only because plenty of organizations are unknowingly running level-zero "analytics" today.
Level one is the improvising agent: a harness connected to the warehouse, running ad hoc queries and reporting findings conversationally. Genuinely useful for exploration and incident diagnosis, dangerous the moment its outputs are treated as answers rather than leads, because the query that produced the number exists only in a session log, if there.
Level two is artifact authorship with human review: the agent writes the pipeline, the model, the query, as files in a branch, a human reviews the diff, and the merged artifact enters the deterministic estate. This is the level where real value begins, and where most productive teams operate today.
Level three adds automated validation: the artifacts carry tests, schema contracts, data quality checks, dry-run gates, and the harness itself runs them in its loop, hooks firing linters and test suites on every edit, CI enforcing them on every commit, so that the human review at level two is spent on intent and design rather than syntax and correctness. This is where the harnesses' machinery, hooks, headless modes, sandboxes, earns its keep, and where this article aims you.
And level four is the maintained estate: scheduled, headless agent runs that watch the deterministic estate and propose changes through the same gated path, the nightly job that triages its own failure and opens a pull request with the fix, the weekly run that refreshes documentation from schema changes, the agent as a tireless junior engineer whose every action still lands as a reviewable artifact. Level four is not futurism, the headless modes make it a cron entry, and its entire safety rests on the discipline of the levels beneath it: the agent proposes, the pipeline of tests and review disposes.
The diagnostic use of the ladder: when an agent-related data incident happens, it is almost always a level violation, level-one improvisation being consumed as if it were level-three truth, and the fix is almost never "ban the tools." It is "climb the ladder."
The Four Harnesses as Data Tools
Now the tools themselves, profiled specifically for data work, because their general coding reputations transfer imperfectly and their configuration for our discipline is where the value hides.
Claude Code is, in my experience and wide practitioner consensus, the deepest fit for the level-three workflow, for three specific reasons. Its hook system is the enforcement point determinism wants: hooks that run your SQL linter on every file edit, execute the relevant dbt tests after every model change, and block any command matching forbidden patterns, policy as machinery rather than hope. Its skills system packages your team's data conventions, naming standards, testing requirements, approved patterns for incremental models, as loadable procedures the agent follows reliably, which is how tribal knowledge becomes enforced knowledge. And its permission model is granular enough to encode the read-versus-write line this article lives on: read-only database access allowed silently, anything touching a write path gated behind approval. Add subagents, one exploring a gnarly schema in its own context while the lead builds, MCP connectivity to warehouses, catalogs, and lakehouse platforms including my employer's, and a headless mode that slots into CI, and the pieces of the deterministic workflow are all first-party. The costs: proprietary, Claude-only, subscription economics, and the general caveat that its depth rewards configuration investment, a team that never writes hooks or skills is buying a fraction of the tool.
OpenAI's Codex brings two distinctive strengths to data work. Its sandboxing is the best default story in the field, OS-level confinement without container ceremony, which matters more in our discipline than most, because "the agent ran a script" should never be one typo from touching production data, and Codex's tiered approvals make the escalation from sandboxed experiment to real execution an explicit, auditable act. And its cloud task mode, delegating work to managed sandboxes that return pull requests, fits the artifact principle natively: the deliverable arrives as a reviewable diff by construction. Its benchmark-leading task completion translates well to the bounded, verifiable tasks data work abounds in, write the migration, make the tests pass, and its MCP support connects it to the same governed data doors. The costs mirror its rival's: the OpenAI ecosystem assumption, and a harness whose configuration culture is younger than its capability.
Google's Antigravity line enters data work with a different center of gravity. Its lineage, succeeding the enormously popular Gemini CLI after this June's consolidation, carries forward the trait data people prized most: enormous context windows, which are not a luxury in our discipline but a working requirement when the task is "understand this four-hundred-table schema and its lineage before touching anything." Wide-schema comprehension, cross-file refactors over sprawling SQL estates, and migration work that must hold two dialects in mind at once are where the long-context advantage is tangible. The ecosystem adjacency, Google Cloud's data stack, BigQuery, Workspace surfaces where analytical outputs often land, makes it a natural fit for shops already in that gravity well. The honest caveats: the platform transition is recent, the free-tier era that drove its predecessor's adoption ended with it, and teams should verify the current terms, tooling, and MCP posture against their needs rather than assuming continuity with the tool they remember.
OpenCode is the open answer, and for data teams it carries two arguments the others cannot. Provider freedom, seventy-five-plus backends including fully local models, is not just economics: for organizations whose data governance forbids schema details or query patterns from leaving the building, a capable harness driving a local model is the difference between adopting these workflows and watching them from outside. And its plan-versus-build agent design, a read-only planning agent distinct from the full-access builder, maps beautifully onto this article's central line: exploration and authoring as separated modes with separated permissions. Open source under MIT, a polished terminal experience, LSP-grade code intelligence, and no vendor's roadmap between you and your workflow. The costs are the open-source classics: assembly required, configuration culture over convention, and the model you bring determines the ceiling, which for the hardest multi-file data refactors still favors the frontier models the commercial harnesses bundle.
The meta-guidance across all four: the harness choice matters less than the configuration discipline, and every one of them can run the level-three workflow. Pick by ecosystem and constraints, then invest in the setup, because an unconfigured frontier harness loses to a well-configured modest one on determinism every single time.
The Techniques Catalog: Making It Reproducible
Here is the toolbox, the specific practices that convert agent-assisted data work from plausible to reproducible, each stated with its mechanism.
Version control is the foundation, totalized. Every artifact the agent touches, SQL, pipeline code, dbt models, configuration, the instruction files that shape the agent itself, lives in git, and every agent session works on a branch. This is not ceremony: the branch-and-diff discipline is what makes the model's variance harmless, because variance that arrives as a reviewable diff is a proposal, and variance that arrives as an executed change is an incident.
Tests are the contract, and the agent runs them. Data tests, dbt tests, quality suites, schema assertions, are the objective function that replaces "looks right" with "is right": uniqueness, referential integrity, accepted ranges, row-count expectations, reconciliation against known totals. The workflow discipline is to make the agent write tests alongside every artifact and run them in its loop, via hooks or explicit instruction, so the agent iterates against truth rather than against its own confidence. A model's SQL is a hypothesis. A passing test suite is a fact.
Dry-run and staging gates keep hypotheses off production. Every serious data stack offers a rehearsal mode, compile-only runs, EXPLAIN plans, execution against staging schemas or table clones, write-audit-publish patterns on the lakehouse side, and the agent's instructions should mandate them: no artifact graduates without a clean dry run, no write path executes outside staging until review. The harnesses' sandboxes contain the compute side of this, and the data side, which schemas the credentials can even see, belongs to the governance section below.
Pin everything that can drift. Deterministic outputs require deterministic inputs: pinned dependency versions in pipeline environments, pinned model versions in the harness configuration where reproducing the authoring context matters, explicit schema contracts so upstream changes break loudly in CI rather than silently in production, and seeded sampling whenever the agent works against data subsets, so the exploration that justified a decision can be re-run and re-examined.
Externalize the numbers into a semantic layer. The single highest-impact determinism technique for analytics: metric definitions, what revenue means, how churn is calculated, live as governed, versioned definitions in a semantic layer, and agents are instructed to query the defined metrics rather than improvising aggregations against raw tables. This converts the worst nondeterminism in the field, three plausible revenue queries with three answers, into a lookup, and the published evidence matches field experience: grounding agents in governed semantics roughly doubles their accuracy on data questions. Declared bias and genuine conviction at once: platforms like Dremio's, with semantic layers served over MCP, exist precisely to be this layer, and whatever vendor provides yours, the architectural point stands.
And keep golden datasets for the agent itself. A small, versioned corpus of representative tasks, schemas, and known-correct outputs, against which you evaluate configuration changes, new skills, new hooks, new models, before rolling them to the team. The agent setup is itself an artifact estate, and it deserves the same regression discipline as the pipelines it helps build.
The Data Team's Instruction File: What Goes in AGENTS.md
Since every harness profiled above reads standing instruction files, and since that file is where a team's determinism discipline becomes enforced rather than remembered, it deserves a concrete treatment: here is what belongs in a data team's AGENTS.md or its equivalents, section by section, in prose you can adapt this afternoon.
Identity and boundaries first: what this repository is, which systems the agent may read, which it may never touch, and the standing rule stated bluntly, all writes land in staging schemas on branches, production is reached only by CI executing merged code. Agents follow explicit prohibitions far more reliably than implied ones, so write the prohibitions.
Conventions second, the tribal knowledge externalized: naming standards for models and columns, the project's layer structure, staging to intermediate to marts or its local equivalent, the incremental-model patterns the team blesses and the ones it has banned with scars to show, dialect specifics, and formatting rules, though the better home for formatting is a hook that enforces it mechanically, with the instruction file simply noting the hook exists.
Validation requirements third, the contract: every model ships with tests, and name the minimum, keys, accepted values, row-count expectations, every change runs the dry-run gate before proposing, every migration artifact pairs with its reconciliation check, and the definition of done is tests passing, not output looking plausible. Instruct the agent to run the validation loop itself and to report results in its summaries, which turns every session log into a small audit document.
Data semantics fourth, the drift killer: where the governed definitions live, the semantic layer, the metrics files, the catalog, and the instruction that analytical questions route through defined metrics rather than improvised aggregations, with new metric needs flagged for definition rather than silently invented. One paragraph here prevents the three-revenue-numbers incident more reliably than any review process.
And escalation last: the conditions under which the agent must stop and ask, schema changes beyond a threshold, anything touching the listed sensitive tables, reconciliation failures it cannot resolve, ambiguity about which definition applies. An agent with clear escalation rules interrupts you at exactly the right moments, and one without them interrupts you either constantly or, worse, never.
Keep the whole thing under a few hundred lines, version it, review changes to it like code, because it is code in the way that matters, and treat its growth as institutional learning: every incident retrospective that ends with a new line in the instruction file is an incident that will not repeat, on any harness, for any team member, human or otherwise.
The Workflow Patterns: Where the Hours Actually Go
Techniques compose into workflows, and five patterns cover most of a data team's agent-assisted week.
Pipeline development is the bread and butter: the agent scaffolds the ingestion or transformation, models, tests, documentation, and configuration together, iterates against the dry-run and test loop until green, and delivers a branch. The human reviews intent and design, the machinery has already reviewed correctness, and the merged result is indistinguishable, on purpose, from well-crafted handwritten work, except that it arrived in an afternoon with better test coverage than most humans write unprompted.
Migration and translation is where the harnesses look most like magic while being most deterministic: dialect-to-dialect SQL translation, warehouse-to-lakehouse moves, legacy pipeline modernization. The pattern that makes it safe is reconciliation-driven: the agent's first deliverable is the validation harness, row counts, aggregate checksums, sampled comparisons between old and new paths, and only then the translated artifacts, iterated until reconciliation passes. Long-context harnesses shine here, holding both estates in mind, and the deliverable is not "the agent says they match," it is a reconciliation report any auditor can re-run.
Quality investigation uses the improvising mode correctly: an anomaly appears, and the agent, on read-only credentials, does the reconnaissance, profiling distributions, checking recent loads, diffing schema versions, tracing lineage, that consumes human hours. Its findings are leads, and the pattern's discipline is that the fix it proposes lands as artifacts: the corrected transformation plus the new test that would have caught the issue, so every investigation permanently hardens the estate.
Documentation and lineage may be the highest-ratio pattern of all: agents generating and refreshing model documentation, column descriptions, lineage summaries, and runbooks from the code and schemas themselves, on a schedule, as pull requests. The chronically undone work of data teams, done continuously, reviewably, and without sighing.
And analytics with provenance closes the loop for the analyst side: questions answered through the semantic layer's governed definitions, exploratory findings promoted into saved, versioned queries and models rather than dying in a session, and every number that escapes to a stakeholder carrying its trace, which definition, which query, which snapshot. The agent accelerates the analysis. The architecture makes it citable.
The Analyst's Version: Taming Exploration Itself
One workflow deserves its own section because it is where analytics has always leaked determinism, agent or no agent: exploratory analysis, the notebook that found the insight and can never quite find it again.
Exploration is legitimately nondeterministic, that is what makes it exploration, and the discipline is not to constrain the wandering but to govern what escapes it. The pattern that works has three moves. First, reproducible wandering: even exploratory sessions run on read-only credentials against named snapshots or seeded samples, so that any promising path can be re-walked, and the harnesses make this nearly free, the session transcript is a record, and an instruction-file line requiring the agent to log every query it ran alongside its findings turns each exploration into a re-runnable script by accident.
Second, the promotion gate, the move that changes everything: an insight that will be shown to anyone gets promoted from exploration to artifact, the winding notebook distilled, by the agent, which is excellent at exactly this distillation, into a clean, parameterized, tested query or model, committed, reviewed, and thereafter the citable source of that number. The notebook was the search. The artifact is the answer, and the two have different jobs, different audiences, and different determinism requirements, which the promotion gate makes structural.
Third, definition capture: when exploration surfaces a metric the business will want again, churn by cohort, activation by channel, the finding routes into the semantic layer as a governed definition rather than living as a clever query in one analyst's branch, which is how exploration compounds into organizational vocabulary instead of organizational folklore. The agent drafts the definition, the humans who own the semantics review it, and the next question about that metric, from any person or any agent, resolves to the same answer.
Analysts sometimes hear this as bureaucracy arriving to ruin the fun, and the lived experience runs opposite: the wandering stays free, the harness absorbs the distillation drudgery that used to make rigor expensive, and the analyst's work stops evaporating, every promoted artifact a permanent brick where a screenshot of a notebook used to be. Determinism, at the analytics layer, is not a constraint on insight. It is what lets insight be believed twice.
Governance: The Line Between Fast and Reckless
All of the above assumes an answer to the question that should precede any harness's first database connection: what, exactly, can this agent touch?
The pattern that works is the same identity discipline applied to human engineers, tightened: agents authenticate as their own principals, never as a borrowed human account, with scopes matched to the ladder, read-only credentials for exploration and investigation, write access confined to staging and development schemas, production writes reserved for the CI system executing reviewed, merged artifacts, which is to say, never held by the interactive agent at all. Short-lived, vended credentials beat long-lived secrets in configuration files, catalog-level governance, the access controls and credential vending of the open catalog world, beats per-tool password sprawl, and every query the agent runs should land in the same audit trail as any user's, because "what did the agent touch" is a question incident review will eventually ask, and the harness session log is not the system of record, the platform's audit is.
MCP is where this becomes practical rather than aspirational: the harnesses reach data through MCP servers, and a well-built data-platform MCP server, my employer's among the entrants, is precisely a governance boundary, authenticating the agent as a principal, enforcing its scopes, exposing semantic definitions alongside raw access, and logging everything. Configure the connection once, correctly, and every workflow in this article inherits the discipline. Skip it, paste an admin connection string into a config file, and no amount of prompt engineering will save you, because governance was never the model's job. It is the door's.
A Worked Example: One Migration, End to End
Compress the whole method into one story, composited from real engagements.
A team must migrate a legacy warehouse's reporting layer, some two hundred SQL views in an aging dialect, onto their Iceberg lakehouse, historically a two-quarter slog. Week one, setup: the repository gets its instruction file, conventions, the staging-only rule, the reconciliation requirement, the harness gets hooks wiring the SQL linter and test runner, read-only credentials to the legacy system and staging credentials to the lakehouse arrive as vended, scoped principals through the MCP connection, and the golden tasks, five representative views with known outputs, validate the setup itself.
Weeks two through five, the loop: the agent proceeds view by view, and per the reconciliation-first pattern, each unit of work is a branch containing the translated model, its tests, and its reconciliation check against the legacy output, iterated headlessly against the dry-run and test gates until green, then queued for human review. The humans, freed from syntax, review design: this view should become two models, that one is dead and should be retired, this translation reveals a legacy bug worth preserving in a comment and fixing in the new path, judgment work, the kind that was always the actual job. A subagent maintains the running migration log and refreshes documentation as models land. Nightly, a scheduled headless run re-executes the full reconciliation suite across everything migrated so far, and its one mid-project catch, an upstream schema drift that broke eleven reconciliations, arrives as a morning report with a proposed fix branch, not as a surprise in month three.
Week six, the finish: two hundred views migrated, every one tested and reconciled, documentation current, audit trail complete, and the cutover is an anticlimax, which is the highest compliment a migration can receive. The team's retrospective line is the one I hear repeatedly and the reason this article exists: the agent did not replace the engineers, it replaced the two quarters, and every number still reproduces to the penny, because nothing nondeterministic ever entered the runtime path.
The Anti-Patterns: How This Goes Wrong
The failure catalog, brief and pointed, because every entry is a real pattern from the field.
The improvised dashboard: an agent in the runtime path, generating the query live on every refresh, numbers that drift between mornings, and no artifact to review when finance disputes Tuesday. The confused principal: the agent running on a human's credentials, its actions indistinguishable in the audit from its operator's, discovered during the incident that makes everyone memorize the word "principal." The untested translation: migration by vibes, "the agent converted it and it looks right," with reconciliation deferred until the legacy system is gone and the discrepancies are unfalsifiable. The context-free number: an agent answer pasted into a deck without its query, its definition, or its snapshot, unreproducible by construction. The unpinned everything: environments, schemas, and samples left floating, so that even the deterministic artifacts stop reproducing, and the agent gets blamed for what drift did. And the configuration-free adoption: a frontier harness deployed with no instruction file, no hooks, no scoped credentials, generating impressive demos and a slow accumulation of exactly the incidents above, until the tools are banned for what the setup never attempted to prevent.
Every one of these has the same cure, and it is the article's thesis read backwards: put the model's variance where variance is safe, put machinery everywhere else, and let the deterministic estate do what it has always done, which is be trusted.
Questions I Hear Most Often
Doesn't setting temperature to zero solve the determinism problem? No, and the question diagnoses the confusion this article exists to fix: sampling settings reduce token-level variance within one call, and they do not make an agent's multi-step behavior reproducible, nor should you want determinism at that layer. The determinism that matters is at the artifact and runtime layer, same pipeline, same input, same output, and that is achieved architecturally, by keeping the model out of the runtime path, not by tuning the dice.
Can I trust agent-written SQL for genuinely complex logic? Trust the process, not the SQL: complex logic is exactly where tests, reconciliation, and review earn their existence, and agent-written SQL that has passed a reconciliation suite against known outputs deserves precisely the same trust as human-written SQL that has passed it, which is the only kind of trust either ever deserved. The honest adjustment is in review attention: agents err confidently and syntactically beautifully, so review verifies semantics against intent, which the tests should be encoding anyway.
Which of the four harnesses should a data team standardize on? Standardize the discipline, not the harness: the instruction conventions, the hook-enforced validation, the credential scoping, and the MCP endpoints travel across all four, and the harness choice then follows ecosystem, model subscriptions you hold, cloud gravity, governance constraints on where data details may flow, with OpenCode's local-model path as the answer to the strictest version of that last constraint. Teams that standardize the portable layer switch harnesses in a week. Teams that standardize a harness rebuild their discipline every switch.
Is level four, scheduled autonomous agents, actually safe for data work? Yes, under one condition that is the whole point: the autonomous agent's write path is the pull request, never the production schema. A nightly agent that investigates, drafts, tests, and proposes is a tireless colleague, and the same agent with production credentials is an unattended nondeterministic process in your data path, which is the thing this entire article is designed to never build. The gate is not the agent's intelligence. It is the pipeline's.
How does this change what data engineers actually do? It concentrates the job into its judgment core: deciding what should exist, reviewing intent and design, encoding standards into the instruction files and tests that steer the machinery, and owning the governance boundaries, while the syntax, scaffolding, translation, and documentation hours compress dramatically. The engineers thriving in this workflow describe the same shift: less typing, more architecture, and a strange new artifact of seniority, the quality of your team's AGENTS.md file.
Where should a team start, concretely, this month? One workflow, full discipline: pick documentation generation or a contained migration, stand up the instruction file, the hooks, the scoped read-only credentials, and the branch-and-review flow, run it for four weeks, and measure artifacts merged, test coverage added, and incidents caused, which should be a positive number, a larger positive number, and zero. That experience, not this article, will convince your skeptics, and the setup it forces you to build is the platform every subsequent workflow inherits.
Closing Thoughts
The stochastic model and the deterministic pipeline are not enemies, and the discipline that reconciles them is neither novel nor mysterious: it is software engineering's oldest separation, development and runtime, applied at the moment it matters most. The agent harnesses give data teams a collaborator of extraordinary breadth at development time, and the estate they help build, versioned, tested, reconciled, governed, remains exactly as deterministic as the discipline enforcing it. That is the whole resolution: creativity where variance is cheap, machinery where trust is dear, and a hard line between them that your instruction files, hooks, credentials, and CI enforce so that no one has to remember it under deadline. The teams working this way are not choosing between AI speed and data trust. They are compounding both, and the gap between them and the teams still debating the contradiction widens every sprint.
If the way this article builds understanding works for you, that is what my books do at full depth. I co-authored Apache Iceberg: The Definitive Guide and Apache Polaris: The Definitive Guide for O'Reilly, with further titles on lakehouse architecture, data engineering, and agentic analytics.
Browse the full collection of my books on data and AI at books.alexmerced.com.
