What’s more powerful: Ben Shelton serving down the T or $analytics-engineer modeling your Shopify data?
If you’ve spent any significant amount of time modeling data in the past few years, you’ve likely been knee-deep in a refactor that you now look back on and think, “If only Codex existed back then…”
Maybe it was migrating your subscription vendor on Shopify while trying to maintain the stability of key subscription metrics. Your new vendor doesn’t capture soft deletes on subscription lines, so you are modeling the activity log event stream and tying back to the subscription record (there goes two weeks of your time). Speaking of the activity log, what was “subscription-line-canceled” is now split into “line-paused” and “line-canceled”; you need to update those dependencies (don’t forget about Looker filters!). And what about gift subscriptions? Sure, there’s a migration cutover date, but unless you tie gift subscriptions purchased pre-cutover to redemptions post-cutover, your lead metrics will be heavily inflated.
The throughline: data modeling used to be a slog. It was manual. Heavy. Tedious. Going model-by-model, even line-by-line, to ensure that upstream changes were reflected downstream. During a refactor, dbt helps catch compilation issues, but even so… who wants to take the time to brute-force downstream changes by trial and error?
Luckily, now you don’t have to.
The Agent & Subagents
We built $analytics-engineer, our own Codex-based analytics agent, from our experience refactoring and building greenfield projects for our clients.
Analytics Engineer: the quarterback. Owns the code change from end to end. Understands how to leverage and pass context from subagent to subagent.
$analytics-engineer tackle <ticket link or requirements>.The agent is broken down into 3 subagents, each with its own role and responsibility:
Analytics Developer: the engineer. Leverages your repo’s AGENTS.md file for architecture, SQL style, and business definitions while adhering to modeling and dbt best practices.
Analytics Tester: the accountant. Do you find validation testing painstakingly tedious? With a bit of guidance, you now have a detail-oriented tester that will validate changes to selected columns row by row against production, check downstream aggregates when requested, and report whether the results match the ticket’s requirements.
Analytics Deployer: the rocket ship. The deployer summarizes Git, validation, and lineage changes to populate the pull request to the specs of the repo, with an end goal of facilitating a quick and easy merge by the real analytics engineer in front of the computer screen.
The Agent in Action
For this example, we chose a dbt upgrade: a contained task that exercises the same development, validation, and PR handoffs we use for modeling changes.
Upgrade the project to dbt v2, with human approval required before merge. $analytics-engineer then delegates the upgrade to the Developer.
The Developer pinned dbt 2.0.6 and dbt_utils 1.4.1, updated configuration, and added compatibility CI and rollout instructions. Initial checks caught source freshness settings that needed to move under config. The Developer corrected the syntax while preserving the existing freshness thresholds.
The Tester begins independent compatibility checks.
The Tester then verified parsing and compilation and confirmed that all models in the repo produced equivalent SQL under dbt v1 and v2. Validation focused on code compatibility; production data was not rebuilt or compared.
Validation passes, and the Deployer opens a draft PR for human review.
The Deployer verified that the code matched the tested version, committed and pushed the changes, and opened draft PR #95. It summarized the validation results and remaining rollout steps in the PR description, then confirmed that GitHub CI passed.
The PR documents the upgrade, validation results, and passing compatibility CI.
From branch setup to passing CI, the workflow took approximately 12 minutes. The agent prepared the upgrade, corrected a compatibility issue, and verified equivalent compiled SQL across all models before opening the PR. This was a relatively small repository, but the example shows how development, independent validation, and PR preparation can run from a single request.
Opportunities
While our agent is relatively fast and builds robust models with sufficient checks and balances, we’re still working through the following:
Removing the “human in the loop” without degrading quality. We’re still limited by the human in the loop, as you can only define requirements, review pull requests, and execute change management so fast. A future state is an agent that can autonomously review and merge pull requests at the same standard that we can.
Avoiding known technical pitfalls or cop-outs. Codex has a few technical quirks that enable it to reach the desired end state, but in an unstable way. For example, it has a tendency to deduplicate rows with
ROW_NUMBER()at the end of CTEs. However, Codex should address the upstream duplication issue or refactor upstream lineage rather than use a qualify statement as a cop-out.Building a pull request with the right level of context and tone. We leverage a standard pull request template that helps the reviewer understand the changes (DAG, semantics, structure, etc.), driver behind the changes (business need, cost savings, etc.), and validation (confirming what was expected). While Codex can build detailed pull requests, too much detail can bury the decisions and validation results the reviewer needs to assess.
Closing Thoughts
The biggest change in our workflow is where we spend our time. We’re spending less time implementing repetitive changes and more time defining requirements and reviewing results.
However, there’s still work to do. An agent can produce SQL that compiles while missing the business logic, or hide an upstream problem behind a convenient deduplication clause. Our next step is to turn the mistakes we catch in review into repeatable checks the agent must pass.
If you’ve got a refactor you’ve been putting off, try $analytics-engineer on a clearly scoped ticket. Give it your repo’s conventions, define what a correct result looks like, and review the changes. And don’t forget to let us know what you think!
