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 build order is correct without you tracking it. The asset name has no schema in front of it, matching every other table in this project.

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.

A finished header looks like this, with 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

Your task

Save the corrected spine query from the interrogation lessons - it lives in queries/agent_v1.sql - as a new asset at pipeline/assets/category_growth.sql, with a header like the one above and the query body below it. Give it a table materialization, the correct depends list, and a description that states what one row represents and which revenue definition it uses.

Then validate, build, and inspect it:

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, bruin run creates the table, and the result is one row per category. 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.

Check your understanding

• What is the difference between a table and a view materialization?
• What does depends do?
• How many rows should category_growth have, and what does one row represent?

Do it with your agent

Say next lesson and your agent teaches this, asks you these questions, then sets the task above. Do it by hand, then say review my work - it checks your work against a rubric and tells you what to fix or marks the lesson done.