How analytics engineers can use AI in their everyday workflow

This guest post comes from Carolina Moura, an analytics engineer at Indicium.

If you’re an analytics engineer, you’ve likely spent hours documenting dbt models, debugging cryptic errors, or chasing down bugs caused by a stray apostrophe. Complex SQL calculations, cross-platform migrations, and repetitive documentation tasks slow you down. But AI can help.

Used strategically, AI boosts efficiency, reduces errors, and frees up time for high-impact work. In this article, I’ll break down five powerful ways to bring AI into your dbt workflow, without sacrificing quality.

1. Generate dbt documentation

Documenting dbt models is essential for maintaining data governance, but writing descriptions manually is time-consuming and often inconsistent (who hasn’t missed an indentation in a Jinja block?). Large language models (LLMs) makes this process faster and more standardized. Generative models can analyze SQL code to create descriptions for tables and columns, and even suggest data quality tests.

Let’s use the stg_orders.sql model from the Northwind database as an example:

We can use an LLM to automatically generate documentation for the model by prompting something like: “Based on the dbt model code stg_orders.sql, generate the corresponding stg_orders.yml documentation file. Include descriptions for the table and columns. Also, suggest relevant generic tests to ensure data quality, such as null value validation, uniqueness checks, and date range validation.”

Here’s how AI might structure the YAML, with descriptions and tests inferred from the schema:

That said, for columns with business-specific logic, AI may miss important nuances. It’s essential to review and adjust accordingly. Keep an eye out for some common pitfalls, which I’ll cover at the end of the article. dbt also offers dbt Copilot, built directly into dbt Clouds IDE. With dbt Copilot teams can autogenerate documentation by leveraging deep context from your models capturing metadata, relationship, and structure so you get more accurate, reliable outputs with less manual effort.

2. Interpret error logs

Who hasn’t run into an indecipherable error and spent far too long trying to understand the root cause? Logs can be lengthy and hard to interpret, especially when they involve references between models, custom macros, or dbt_project.yml configurations. Instead of analyzing each detail manually, you can use an LLM to interpret the error and suggest a fix.

When running a dbt model, you might encounter this error:

After submitting the error to the LLM with the prompt “Explain this dbt error and suggest a fix”, you might receive a response like this:

In seconds, you’ll get a potential diagnosis and solution, saving time and reducing frustration. And if you don’t want to context switch to an external LLM, dbt Copilot makes it even easier. Built into the dbt Cloud IDE, Copilot lets you highlight an error directly in your SQL, hit Command+B, and automatically generate a prompt to ask for an explanation—so you can diagnose and fix issues without ever leaving your workflow.

3. Convert functions between data warehouses

In data migration projects, adapting functions from one data warehouse to another (e.g. Redshift to Snowflake or BigQuery to Databricks) can be time-consuming. Each platform has its own syntax, operators, and native functions, which require manual adjustments to ensure compatibility. AI can automate much of this process.

Suppose you're migrating this model from Redshift to Snowflake:

You can use a prompt like this to generate a version compatible with Snowflake: “Convert the following dbt model from Redshift to Snowflake, keeping the logic intact.”

The LLM adapts the syntax to the new platform while preserving the original logic, which reduces manual work and human error. This speeds up migrations and helps maintain consistency across environments. And you can do this right in dbt: just highlight a block of SQL from a dbt model, use dbt Copilot, and prompt it to convert the code, without ever leaving your IDE.

4. Optimize dbt models

Beyond syntax conversion, AI can suggest improvements for performance and readability. It can help refactor subqueries into Common Table Expressions (CTEs), reduce duplicated logic, and simplify joins. These optimizations can reduce execution time, improve maintainability, and make your models easier to understand, all without changing the underlying logic.

Here’s an example of a dbt model that could benefit from refactoring:

You can use a prompt like this: “Optimize the following dbt model for performance and readability without changing the logic. Follow SQL code style best practices.”

The AI-generated version might look like this:

For larger projects with complex transformations, small optimizations like these can scale into meaningful gains, not just in performance, but also in clarity and team collaboration.

And you can do this right in dbt Cloud’s IDE: just highlight a block of SQL in-line, use dbt Copilot, and prompt it to optimize for performance, readability, or even apply your custom SQL style guide—all without leaving your workflow.

5. Automate dimensional modeling

Designing a scalable data model is a challenge, especially when working with multiple tables. AI can suggest optimized structures for fact and dimension tables, helping you build more efficient and scalable models.

Using the Northwind database diagram as a reference, you can prompt: “Based on the diagram provided, design a dimensional model.”

You might receive an output like this:

Once the structure is defined, you can also ask the LLM to:

• Create a mapping table between the transactional and dimensional models.
• Generate the corresponding dbt models using the transactional tables as sources.
• Suggest naming conventions and folder structures to organize your dbt project.

Best practices when working with AI

AI can be a powerful ally, but only when used thoughtfully. Here are some key practices for safe, effective usage:

• Always validate AI output: Review any AI-generated code or documentation carefully. It may contain syntax errors or misinterpretations.
• Test before deploying: Run tests on any generated or modified code before pushing to production.
• Keep sensitive data private: Never input confidential data, credentials, or proprietary business logic into AI tools.
• Learn from AI, don't just copy: Use the output as a learning opportunity to understand new techniques and patterns.
• Iterate your prompts: The quality of AI output often depends on how clearly and specifically you ask. Don’t be afraid to refine and experiment.

The AI landscape is evolving fast, and tools are becoming more customizable and integrated into data platforms. If you haven’t started using AI in your daily workflow, now’s a great time to begin. Start small: documentation and error debugging are low-risk areas where mistakes are easy to catch. As you explore more use cases, consider building a prompt library tailored to your common tasks.

And don’t keep it all to yourself. Share prompts and insights with your team helps to foster a culture of learning and experimentation. Over time, you’ll gain a clearer sense of where AI delivers the most value, and how to make the most of it in your work.