Save a query as an asset

From ad-hoc to persistent

A query you ran once lives only in your terminal history. An asset is a query saved as a file, so it can be run again, depended on, and reviewed. Bruin stores each asset in pipeline/assets/ with a small header that tells Bruin what it is and what it needs.

An asset header sits at the top of the .sql file, between /* @bruin and @bruin */. It names the asset, sets its type, lists what it depends on, and carries a description. The depends list tells Bruin which tables to build first, so the order is correct without you tracking it.

Table or view

materialization decides how the result is stored. type: table stores the rows, so reads are fast and the numbers are fixed until the next run. type: view stores the query and recomputes it on every read, so it is always current but does no work until queried. This course uses a table with strategy: create+replace, which rebuilds the small local table each run. Tools often call this update rule materialization. The full catalogue of strategies is an advanced-course topic, so keep this one for now.

Save your audited query

Save the corrected query from the interrogation lessons as pipeline/assets/category_growth.sql, with a header like this and your query body below it:

/* @bruin
name: category_growth
type: duckdb.sql
description: >-
  One row per product category, comparing 2023 and 2024 line revenue
  (quantity * net_price). Revenue uses net_price, not unit_price.
depends:
  - orders
  - order_items
  - products
materialization:
  type: table
  strategy: create+replace
@bruin */

-- your audited query goes here

The name is category_growth, with no schema in front of it, matching every other table in this project.

Ask your coding agent

AI Prompt

Turn the query in queries/agent_v1.sql into a Bruin asset at pipeline/assets/category_growth.sql. It should be a table, depend on the correct upstream assets, and carry a description that states what one row represents and which revenue definition it uses.

Run bruin validate before bruin run. Do not commit. Show me the diff.

Checkpoint

Build and inspect the asset:

bruin validate pipeline/pipeline.yml
bruin run pipeline/assets/category_growth.sql
bruin query --connection duckdb-default \
  --description "row count of the new category growth asset" \
  --query "SELECT COUNT(*) AS categories FROM category_growth;"

bruin validate passes and bruin run creates the table. The result has one row per category, and there are eight categories in the data - so expect eight rows, or nine if your query keeps the order lines whose product is missing in an "Unknown" bucket. Confirm the grain matches the description you wrote.