I want to create a dbt model on top of the product table. How would I structure it across bronze, silver, and gold layers, and what would the SQL look like to create a view that selects shop from product?

In a typical bronze/silver/gold architecture, each layer adds a level of transformation and quality.

Bronze – Raw ingestion. You might have a model that simply selects from the source:

-- models/bronze/product_raw.sql
{{ config(materialized='view') }}
SELECT * FROM {{ source('raw', 'product') }}

Silver – Cleansed and enriched data. For example, you could filter out inactive products and add a shop lookup:

-- models/silver/product_clean.sql
{{ config(materialized='view') }}
SELECT
  id,
  name,
  price,
  shop_id,
  CASE WHEN status = 'active' THEN TRUE ELSE FALSE END AS is_active
FROM {{ ref('bronze_product_raw') }}
WHERE status = 'active'

Gold – Business‑ready view. Here you might join with a shop dimension and expose only the columns needed by analysts:

-- models/gold/product_view.sql
{{ config(materialized='view') }}
SELECT
  p.id,
  p.name,
  p.price,
  s.shop_name
FROM {{ ref('silver_product_clean') }} p
JOIN {{ ref('silver_shop_dim') }} s
  ON p.shop_id = s.id

The gold model is the one you expose to downstream users. By separating concerns, you keep each layer small, testable, and maintainable. The SQL shown demonstrates how to create a view that selects the shop name from the product table across the three layers.