How to Build an AI Context Layer for Your Data Warehouse

Quick answer: to give an AI agent real context about your warehouse, install the Bruin CLI, map your tables into local files with bruin import database, then run bruin ai enhance to fill those files with descriptions, data quality checks, and tags. Both commands are free and open source, they run on your machine, and the result is a folder of plain YAML you can review in a pull request. You do not need to be a Bruin user, and you do not need to move your pipelines.

Every text-to-SQL demo works on the demo schema. Then you point the same agent at a real warehouse with 400 tables, five naming conventions, and a status column that stores integers, and the answers quietly go wrong.

The model is not the problem. The problem is that the agent can see your schema and nothing else. It knows a column is called gmv and typed DECIMAL. It does not know that gmv excludes refunds, that status = 3 means refunded, that orders.total_amount does not reconcile with the line items, or that country_code is ISO alpha-2 rather than a free-text country name.

That missing information is the context layer. This guide builds one in two commands.

What you will have at the end

A directory of YAML files, one per table, that looks like this:

name: ecommerce.orders
type: duckdb.source
description: |-
  One row per customer order placed in the e-commerce shop. Every order links
  back to a customer in `ecommerce.customers` via `customer_id`, and forward to
  its line items in `ecommerce.order_items` via `order_id`.
tags:
  - domain:ecommerce
  - fact_table
  - raw
columns:
  - name: order_id
    type: BIGINT
    description: Surrogate primary key uniquely identifying an order.
    primary_key: true
    checks:
      - name: not_null
      - name: unique
  - name: status
    type: VARCHAR
    description: 'Current lifecycle state of the order. Observed values: pending, shipped, delivered, refunded.'
    checks:
      - name: not_null
      - name: accepted_values
        value:
          - pending
          - shipped
          - delivered
          - refunded

Three things make this useful rather than decorative:

• It is text in git. Reviewable in a PR, greppable, diffable, and readable by any agent that can open a file. No catalog UI, no API, no export step.
• The checks are executable. not_null, unique, and accepted_values are not comments. Bruin can run them against the table, so the documentation gets tested instead of rotting.
• It is yours. Wrong description? Edit the line. There is no metadata service that owns the truth and no vendor that has to agree with you.

Why the schema alone is not enough

An agent writing SQL needs to answer four questions before it writes a line:

1. Which table has this?
2. What does this column actually mean?
3. What values are legal, and what do they encode?
4. How do these tables join?

A raw INFORMATION_SCHEMA dump answers question one, badly, and none of the others. So the agent guesses. Guessing produces SQL that runs, returns a number, and is wrong - which is the worst failure mode available, because nothing errors and someone puts the number in a deck.

The context layer answers all four in a form the agent reads before querying. This is the same reason coding agents work well on code: the metadata and the artifact live in the same repository, in text.

Before you start

You need three things.

1. The Bruin CLI. Open source, Apache 2.0, single binary:

curl -LsSf https://getbruin.com/install/cli | sh

Verify it:

bruin --version

2. An AI coding CLI. bruin ai enhance drives an agent you already have installed. Any one of these works:

Bruin auto-detects what is installed, so the flag is only needed when you have more than one and want to pick.

3. Read access to your warehouse. SELECT on the schema you want to map, plus permission to read its metadata. That is it - no write access, no admin role.

Step 1: create a project

You are not building a pipeline, so skip the platform-specific templates. An empty project is all the context layer needs:

bruin init empty ai-analyst
cd bruin

That gives you a bruin/ folder containing ai-analyst/ - a pipeline.yml with everything commented out, and an assets/ directory waiting to be filled.

Two paths matter for the rest of this guide, and mixing them up is the most common mistake:

• .bruin.yml lives at the project root and holds credentials. It does not exist yet; it is created the first time you add a connection or validate the project, and it is gitignored.
• ai-analyst/ is the pipeline folder. It contains pipeline.yml, and it is the path you pass to import and enhance.

Step 2: connect your warehouse

Add a connection with the interactive wizard:

bruin connections add

It asks for the connection type, a name, and the credentials that type needs, then writes .bruin.yml for you. Or create the file yourself - it is small:

default_environment: default
environments:
  default:
    connections:
      google_cloud_platform:
        - name: "gcp-default"
          project_id: "my-analytics-project"
          service_account_file: "/path/to/service-account.json"
      snowflake:
        - name: "snowflake-prod"
          account: "abc12345.eu-central-1"
          username: "analytics_reader"
          password: "..."
          database: "ANALYTICS"
          warehouse: "COMPUTE_WH"

Confirm it works before going further:

bruin connections test --name gcp-default

If you do not know which schema you want, ask the warehouse:

# BigQuery
bruin query --connection gcp-default \
  --query "SELECT schema_name FROM INFORMATION_SCHEMA.SCHEMATA"

# Postgres / Redshift
bruin query --connection postgres-default \
  --query "SELECT schema_name FROM information_schema.schemata
           WHERE schema_name NOT IN ('pg_catalog', 'information_schema')"

# ClickHouse
bruin query --connection clickhouse-default --query "SHOW DATABASES"

Step 3: map your tables with bruin import database

This is the first half of the context layer: turn every table into a file.

bruin import database --connection duckdb-default --schema ecommerce ai-analyst

Imported 3 tables and Merged 0 from data warehouse 'duckdb' (schema: ecommerce) into pipeline 'ai-analyst'

The result is one file per table, organised by schema:

ai-analyst/
├── pipeline.yml
└── assets/
    └── ecommerce/
        ├── customers.asset.yml
        ├── order_items.asset.yml
        └── orders.asset.yml

Each file is a skeleton with the table name, the asset type derived from your connection, and every column with its real database type:

name: ecommerce.orders
type: duckdb.source
description: |-
  Imported table: ecommerce.orders
  Extracted at: 2026-08-21T08:39:43Z

columns:
  - name: order_id
    type: BIGINT
  - name: customer_id
    type: BIGINT
  - name: status
    type: VARCHAR
  - name: total_amount
    type: DECIMAL(23,2)
  - name: ordered_at
    type: TIMESTAMP

Accurate, and still not useful to an agent. That is expected - this step is the inventory, not the knowledge.

Flags worth knowing

Supported sources: Snowflake, BigQuery, PostgreSQL, Redshift, Athena, Databricks, DuckDB, ClickHouse, Azure Synapse, MS SQL Server, and MongoDB.

Start with one schema. Import your most-queried schema, run the next step, read the output, and decide whether you like it before pointing this at 400 tables.

Step 4: generate the context with bruin ai enhance

Now the second half - filling those skeletons with meaning:

bruin ai enhance ai-analyst --model claude-sonnet-5

Set --model on the first run. The CLI's built-in default for Claude Code is claude-sonnet-4-20250514, which has reached end of life, so the bare command currently fails on every asset with There's an issue with the selected model.

You can point it at a whole pipeline folder, a single schema folder, or one file. It processes 5 assets in parallel by default.

Each asset goes through the same stages, and the command narrates them:

[orders.asset.yml] Step 1/4: Filling columns from database...
[ecommerce.orders] Step 2/4: Enhancing asset with AI...
[ecommerce.orders] Step 3/3: Validating asset...
✓ Successfully validated 'ai-analyst/assets/ecommerce/orders.asset.yml', all good.
[ecommerce.orders] ✓ Enhanced 'ecommerce.orders'

(The step numbering in that output is inconsistent, which is cosmetic.) What actually happens per asset:

1. Fill columns - re-reads the schema and adds any column that is missing from the file.
2. AI enhancement - queries the warehouse for column statistics (row counts, null counts, distinct counts, min/max ranges), then hands the schema plus those statistics to your AI CLI to write descriptions, checks, and tags.
3. Format - normalises the YAML.
4. Validate - parses the result. If the AI produced something invalid, the file is reverted rather than left broken.

Then a summary:

  ✓ customers.asset.yml
  ✓ orders.asset.yml
  ✓ order_items.asset.yml

Enhancement complete: 3/3 assets succeeded

Expect a few minutes for 15-20 tables, and 10 minutes or more for 50+.

Flags worth knowing

--system-prompt is the underrated one. It is where your house rules go:

bruin ai enhance ai-analyst --model claude-sonnet-5 \
  --system-prompt "All timestamps are UTC. Revenue columns exclude tax and refunds unless the name says otherwise. Never add a unique check to a column unless the statistics prove distinct_count equals row_count. Prefix every tag with its domain."

What the AI actually wrote

Here is real output from the run above, on the orders table, trimmed for length. Nothing in the input file said anything beyond column names and types:

name: ecommerce.orders
type: duckdb.source
description: |-
  One row per customer order placed in the e-commerce shop. This is the fact
  table at the heart of the `ecommerce` domain: every order links back to a
  customer in `ecommerce.customers` via `customer_id`, and forward to its
  line items in `ecommerce.order_items` via `order_id`.

  `total_amount` is stored directly on the order rather than derived at query
  time. In the current data it does NOT reconcile with
  `SUM(order_items.quantity * order_items.unit_price)` for the same
  `order_id` - the two are populated independently upstream, so treat
  `total_amount` as the source of truth for order-level revenue reporting
  and `order_items` as the source of truth for line-item analysis.

  This is a raw source table, ingested as-is with no transformations applied.
  Data currently spans 2025-02-01 through 2025-04-25.
tags:
  - domain:ecommerce
  - fact_table
  - raw
  - mutable
domains:
  - ecommerce
  - sales
meta:
  grain: One row per order (order_id is unique)
  update_pattern: Mutable - status changes in place as an order moves through its lifecycle
columns:
  - name: order_id
    type: BIGINT
    description: Surrogate primary key uniquely identifying an order. Used as the join key to ecommerce.order_items.
    primary_key: true
    checks:
      - name: not_null
      - name: unique
  - name: customer_id
    type: BIGINT
    description: Foreign key referencing ecommerce.customers.customer_id. A single customer can place many orders.
    foreign_key:
      table: ecommerce.customers
      column: customer_id
    checks:
      - name: not_null
  - name: total_amount
    type: DECIMAL(23,2)
    description: Total monetary value of the order. Stored independently of order_items and does not always reconcile with the sum of its line items.
    checks:
      - name: not_null
      - name: non_negative

Three things happened there that a schema dump cannot do.

It inferred the join graph - customer_id got a foreign_key block pointing at ecommerce.customers, from naming and cardinality alone.

It found a real data problem. total_amount does not equal the sum of its line items in this dataset. The AI checked, noticed, and wrote the caveat into the description with instructions on which table to trust for which question. An agent reading this will not silently produce two different revenue numbers depending on which table it picked.

It encoded the enum. status got an accepted_values check listing the four states it observed, so both the agent and the pipeline now know what is legal.

The checks it applies

Bruin pre-fetches statistics so check selection is grounded in the data rather than in vibes:

Review it before you trust it

The generated context is a strong first draft written by something that has never spoken to your finance team. Read it.

The unique trap. A column can be unique in today's snapshot and not unique by design. ticker in a quarterly financials table is the classic case: unique in a single-quarter extract, one row per quarter forever after. Bruin only adds unique when the statistics support it, which is exactly why a coincidence can slip through. Delete any unique check that is true by accident.

Business meaning it cannot know. The AI can tell that status has four values. It cannot tell you that refunded is set by a nightly job with a 24-hour lag. Add that yourself.

Silent renames. If two teams both maintain a revenue column with different definitions, the AI will describe each in isolation. Reconciling them is a human decision. This is where a glossary earns its keep: define the entity once and have assets extends it.

Editing is cheap because these are files. Fix the line, commit, move on.

Then prove the documentation is true:

bruin validate ai-analyst

✓ Successfully validated 3 assets across 1 pipeline, all good.

If you have write access to run checks, bruin run executes them against the real tables. A not_null check that fails is documentation caught lying, which is the entire point of making the checks executable.

Keep it fresh

Re-run the same command after a schema change:

bruin ai enhance ai-analyst --model claude-sonnet-5

It is additive, not destructive. Existing descriptions and checks are left alone - a second pass on our demo pipeline reported No changes made. for the table it had nothing to add to, and for the others it only filled in fields it had skipped the first time, such as meta, domains, and primary_key. Nothing was duplicated and nothing human-written was overwritten. Your edits survive.

That property is what makes this CI-friendly. A weekly job that runs import database followed by ai enhance and opens a PR with the diff turns documentation drift into a reviewable change instead of a slow decay. Because the output is YAML, the diff is readable: a new column shows up as a new column, not as a re-rendered catalog page.

Point an agent at it

The context layer is useful the moment it exists - any agent with filesystem access can read assets/. To let it query as well, Bruin ships an MCP server:

bruin mcp

Register it with your agent. For Claude Code:

claude mcp add bruin -- bruin mcp

Or in a mcp.json for Cursor and friends:

{
  "mcpServers": {
    "bruin": {
      "command": "bruin",
      "args": ["mcp"]
    }
  }
}

Now the agent reads the context layer from the repository and runs queries through Bruin's connections, so credentials stay in .bruin.yml and never reach the model. One more useful command while you are here:

bruin ai skills all

The skill name is required - a bare bruin ai skills opens an interactive picker and fails outright when the terminal is not interactive. all installs the full set into .agents/skills and writes an AGENTS.md at the project root, teaching your coding agent how Bruin projects are structured.

If you would rather follow this as a guided tutorial with per-warehouse setup for BigQuery, Snowflake, Redshift, Databricks, ClickHouse, Postgres, and SQL Server, work through Build an AI Context Layer in Bruin Academy.

The two steps here are steps one and two of building your own AI data analyst. The full path - connections, context, agent setup, and the harder context problems - is written up in the AI data analyst course, and the reasoning behind open-sourcing it is in Building an AI Data Analyst Sucks.

Troubleshooting

error: There's an issue with the selected model

You dropped --model, so the CLI fell back to claude-sonnet-4-20250514, which has reached end of life. Add --model claude-sonnet-5 back, and on Codex or OpenCode use that provider's model name instead. Check that you are on a recent CLI too: bruin --version.

No AI CLI detected

bruin ai enhance needs one of Claude Code, Codex, OpenCode, or Cursor's cursor-agent on your PATH. Install one, confirm with which claude, then re-run.

unknown command "enhance"

The command is bruin ai enhance. The ai subcommand is easy to drop.

Import fails with permission denied

The connection needs SELECT on the target schema and access to its metadata. For BigQuery that means two roles, not one: BigQuery Data Viewer on the dataset plus BigQuery Job User on the project, because both bruin query and ai enhance submit query jobs. Data Viewer alone reads metadata but cannot run a query.

--schemas does nothing

It only works on BigQuery, and it repeats rather than taking a comma-separated list: --schemas raw --schemas analytics. On other warehouses, run --schema once per schema into the same pipeline folder.

It hangs on a large schema

Enhance is doing real work per asset. Lower --concurrency if you are hitting rate limits, split the work by schema folder, and remember you can re-run safely.

Descriptions are confidently wrong

Three fixes, in order of leverage: pass your rules with --system-prompt, edit the file, or write an AGENTS.md telling the agent how to interpret the ambiguous parts.

Where this leaves you

Two commands, a warehouse you can read, and roughly ten minutes of waiting produce a context layer that lives in git, gets reviewed like code, and carries executable checks. Whatever you point at it next - Claude Code, Cursor, a Slack bot, your own agent - starts from what your data means rather than from what its column names look like.

The tooling is open source and runs locally, so the cost of finding out whether your agent gets better is one schema and one afternoon.

FAQ

What is an AI context layer for a data warehouse?

An AI context layer is a machine-readable description of your tables: what each table represents, what every column means, which values are valid, and how tables relate. An AI agent reads it before writing SQL, so it stops guessing at column names and business meaning. With Bruin the context layer is a folder of plain YAML asset files in your git repository, which means you can review it in a pull request and edit it with any text editor.

How do I generate table and column descriptions with AI?

Install the free, open-source Bruin CLI, import your schema with bruin import database --connection <name> --schema <schema> <pipeline-path>, then run bruin ai enhance <pipeline-path>. The enhance command pulls column statistics from your warehouse and uses your local AI CLI - Claude Code, Codex, OpenCode, or Cursor - to write descriptions, data quality checks, and tags directly into each asset file.

Is bruin ai enhance free?

Yes. The Bruin CLI is open source under Apache 2.0, and both bruin import database and bruin ai enhance run locally with no Bruin account, signup, or credit card. You need one AI coding CLI installed and you pay your AI provider for the tokens it uses.

Does bruin ai enhance send my data to an AI provider?

It sends schema plus aggregate column statistics such as row counts, null counts, distinct counts, and min/max ranges, along with the small samples needed to infer things like accepted values. It does not replicate your tables. Everything goes through the AI CLI already installed on your machine, so the traffic path is the one your coding agent already uses.

Which databases does bruin import database support?

Snowflake, BigQuery, PostgreSQL, Redshift, Athena, Databricks, DuckDB, ClickHouse, Azure Synapse, MS SQL Server, and MongoDB. The generated asset type follows the connection, for example sf.source, bq.source, or pg.source.

Do I have to use Bruin for my pipelines to use this?

No. Import and enhance only read warehouse metadata and write YAML into a local folder. Keep dbt, Airflow, Fivetran, or hand-written SQL exactly where they are and use the context layer purely as documentation for your AI agent. If you later want the checks to run on a schedule, that is what bruin run and Bruin Cloud are for.

How is this different from a data catalog?

A catalog stores metadata in a hosted service and exposes it through a UI and an API, which means an agent needs an integration to read it and a human needs a login to fix it. This context layer is text files next to your code: agents read them directly, humans edit them in a PR, and the quality checks are executable rather than descriptive.