Article
How to Use AI in Analytics Engineering Without Damaging the Repo
AI can make analytics engineering work feel much faster.
It can explain unfamiliar SQL, draft dbt models, suggest tests, summarise pull requests, and turn technical notes into documentation. Used well, it removes friction from the mechanical parts of the work.
Used poorly, it creates a silent tax: fast, highly plausible, over-engineered code that sails through basic checks but leaves a legacy of technical debt for the next human engineer to untangle.
The useful position is simple:
AI speeds up the mechanical work. The analytics engineer still owns grain, definitions, business rules, tests, reconciliation, access boundaries, and production judgement.
That distinction matters because analytics engineering is not just about producing SQL. It is about creating trusted model layers that make business metrics reusable, explainable, and safe to use.
AI can help with that work. It should not be allowed to own it.
The six high-leverage domains for AI acceleration
The most useful AI use cases in analytics engineering do not start with “write me a query.” They start where teams lose time: unfamiliar systems, inherited logic, unclear model boundaries, weak tests, thin documentation, and rapid onboarding to new platforms.
1. Upstream and legacy technical debt
AI is useful when the raw material is messy, unfamiliar, or inherited from another team.
It can summarise schemas, logs, API payloads, event streams, webhook bodies, legacy SQL, CTE chains, old stored procedures, and undocumented transformation jobs. Those are not separate problems in practice. They are two versions of the same problem: the analytics team is being asked to trust code or data contracts it did not design.
A lot of reporting breakages do not come from a bad analyst query. They come from upstream change: a product event changes shape, a CRM field is renamed, an API starts sending a new nested object, or a nullable field quietly becomes important. AI can compare those changes against source definitions and staging assumptions faster than a human scanning payloads by hand.
The decision still belongs to the analytics engineer. A new field may be harmless. A changed field may be a breaking contract. A legacy filter may be an obsolete workaround or the only thing keeping a board metric aligned with finance. AI can surface the assumption. It cannot decide whether the assumption should survive.
2. Development and architecture
AI can draft staging models, intermediate models, marts, YAML documentation, and first-pass test files from a clear model contract. That is useful, but the architectural value is not the draft itself. The value is having a fast second pair of eyes on model shape.
The important questions are architectural: whether a join creates fanout, whether the grain is mixed, whether the model is trying to serve too many decisions, whether the logic belongs in staging or a mart, and whether the semantic layer should own the metric instead of another table.
The Architecture Reality Check: A model can be syntactically perfect, pass its data tests, and still be an architectural disaster if it is placed at the wrong level of the directed acyclic graph (DAG). AI optimises for the query. Humans must optimise for the system.
Bad model boundaries create long-term drag. They duplicate logic, hide ownership, create reconciliation work, and make the next change harder than it should be. AI can help see the options. It does not get to be the architect.
3. Quality assurance and reconciliation
AI can suggest dbt tests, custom business-rule checks, freshness checks, edge cases, data-diff queries, and reconciliation queries. It can also summarise where mismatches occur when a refactor changes the output.
This is where AI can materially improve team velocity. A human reviewer should not have to reverse-engineer every discrepancy from scratch if AI can isolate that the variance only appears for one market, one date range, one account type, or one late-arriving event pattern.
But the final question is not “did the tests pass?” The final question is whether the evidence proves the number is safe for the decision. Passing tests is not the same as Reporting Trust. Tests are evidence. The analytics engineer still owns whether that evidence is enough.
4. Performance and pull request flow
AI can point at suspicious scans, repeated CTEs, missing partition filters, avoidable joins, and materialisation choices that deserve a closer look. It can also make pull requests easier to review by summarising diffs, explaining SQLFluff or dbt failures, and flagging missing documentation or tests.
That does not mean AI understands the warehouse bill, the freshness requirement, or the downstream usage pattern. A suggestion to materialise a model may be technically reasonable and operationally wrong. A suggestion to add clustering may sound plausible but have no measurable benefit.
Performance work still needs evidence: query plans, actual usage, cost behaviour, and business priority.
5. Documentation and stakeholder translation
AI is strong at turning rough technical material into readable documentation. It can draft model descriptions, column descriptions, caveats, release notes, metric explanations, and stakeholder summaries.
This is one of the best uses of AI because analytics teams often under-document good work. The caution is obvious: documentation is not true because it is well written. It is true because it matches the model, the source, the caveats, and the business agreement.
6. Learning acceleration
AI is useful when a team needs to get oriented in an unfamiliar stack. It can explain warehouse features, BI terminology, orchestration concepts, platform-specific tradeoffs, and common failure modes.
That matters in real delivery. Analytics engineering teams move between Snowflake, BigQuery, Databricks, Looker, Power BI, dbt, Dataform, SQLMesh, Airflow, Dagster, and whatever else the client or employer has already bought. AI can help an engineer ask better questions sooner.
The important part is not pretending to be an expert. AI can help you get oriented. Production judgement still comes from checking the actual system in front of you.
The operational boundary
For analytics engineering work, the boundary is clearer when AI is mapped across five operational phases.
| Phase | AI Can Help With | Human Must Own |
|---|---|---|
| Discovery & Requirements | Schema summaries, profiling queries, payload drift detection, requirement questions | Source meaning, metric owner, business decision, caveats |
| Development & Architecture | SQL scaffolds, DAG boundary suggestions, refactor options, performance checks | Grain, business logic, model boundaries, materialisation tradeoffs |
| Quality Assurance | dbt tests, lint summaries, PR summaries, data-diff queries, reconciliation analysis | Which checks prove trust, variance sign-off, merge approval |
| Governance & Docs | Documentation drafts, release notes, semantic-layer wording, safe-use notes | Approved definitions, access rules, caveats, stakeholder wording |
| Operations | Log summaries, incident hypotheses, discrepancy isolation, comms drafts | Priority, root cause, production decision, stakeholder impact |
This table is deliberately short. If the framework becomes too detailed, it starts to fail as a working tool. The point is not to list everything AI can do. The point is to know where AI accelerates work and where human accountability remains.
The senior counter-intuition: guard against AI debt
Anyone can use AI to write SQL faster.
A senior analytics engineer uses AI to avoid creating work the team should not have to maintain.
That is the counter-intuition. AI is not only a way to produce code. It is also a way to interrogate whether the code should exist in that form at all.
In a healthy analytics repo, velocity is not measured by how much SQL gets generated. It is measured by how quickly the team can make a change, understand the impact, prove the number still means what it should mean, and leave the system easier to operate than before.
AI can help with that, but it can also undermine it.
This is the part teams should take seriously. If AI scaffolding creates verbose CTE chains, unnecessary abstractions, inconsistent style, or code nobody can explain, the team is not moving faster. It is borrowing against future maintainability.
Velocity means nothing if readability degrades.
The consultant’s position
When I evaluate a data platform, my rule for AI leverage is straightforward: AI is allowed to accelerate the work, but it is not allowed to blur accountability.
That means AI-generated SQL is a first draft. It still needs review, tests, reconciliation, and a normal pull request process before it goes near production.
It means AI can help explain a legacy model, but it cannot decide which metric definition the business should use. It can suggest where logic might sit in the DAG, but it cannot own the architecture. It can draft documentation, but it cannot approve caveats. It can summarise a reconciliation variance, but it cannot sign off whether that variance is acceptable.
It also means AI needs governed inputs. I would rather expose approved metrics through a semantic layer, API, or MCP-style tool than give an assistant raw warehouse access and hope it invents the right joins.
The principle is not anti-AI. It is pro-accountability.
AI can draft, compare, summarise, and challenge. It cannot be accountable for the business number.
What AI must not own
There are some decisions AI should not own in analytics engineering.
If a decision affects trust, cost, access, architecture, or business meaning, a named human still owns it.
Specifically, AI must never be allowed to own:
- Defining business metrics or choosing the correct data grain.
- Deciding source-of-truth ownership or final DAG architecture.
- Approving production code or choosing materialisation strategies without cost and freshness context.
- Signing off on reconciliation variances or approving sensitive-data access.
- Inventing caveats, bypassing governed semantic layers, setting repository style through local prompt drift, or adding complexity just because it can generate code quickly.
Guardrails for using AI in analytics engineering
The practical guardrails are straightforward.
Do not:
- Paste sensitive raw data into public AI tools.
- Let AI infer business definitions from column names alone.
- Accept AI-generated joins without checking grain.
- Accept performance advice without checking warehouse behaviour and query plans.
- Let AI choose model boundaries without architectural review.
- Accept generated code that solves a problem through sheer verbosity.
- Allow local prompt drift to bypass the project style guide.
- Expose raw tables to AI tools when governed marts or semantic layers exist.
- Treat AI-generated documentation as true without review.
- Let AI hide uncertainty.
Do:
- Start with a human-owned model contract.
- Keep generated code simple, idiomatic, and readable.
- Use tests, data-diff checks, and reconciliation for important changes.
- Check upstream schema and payload changes against source contracts.
- Keep access governed.
- Record caveats and assumptions.
- Make SQL and metric definitions inspectable.
What to do next
Start with one analytics engineering workflow where AI is already being used informally.
Do not begin by asking whether the team can generate more SQL. Ask:
- Which part of the workflow is AI accelerating?
- Which decisions still need named human ownership?
- Which source contracts, model contracts, tests, and reconciliations protect the work?
- Where could AI-generated bloat damage maintainability?
- Which governed models or semantic-layer definitions should AI be allowed to use?
AI can be genuinely useful in analytics engineering. But it should make the reporting system more inspectable, not more mysterious.
For related reading, start with AI across the Data Value Chain, Why AI needs a semantic layer, and AI will not fix untrusted reporting. If you want the modelling context behind this, read What analytics engineering is really for and CI/CD for dbt and SQL projects.
For repeatable prompts, review points, expected outputs, and human-accountability checks across this workflow, see the AI Analytics Engineering Workflow Kit.