Staging Model Patterns AI Prompt

Write best-practice staging models for these source systems.

Source systems: {{sources}} (e.g. Postgres, Stripe, Salesforce, Hubspot)
Warehouse: {{warehouse}}
Raw schema: {{raw_schema}}

1. Staging model purpose and rules:
   - One model per source table (1:1 relationship)
   - No joins between source tables in staging
   - No business logic — only technical cleaning
   - Always reference via source() not raw SQL

2. Standard transformations to apply in every staging model:

   Rename to snake_case:
   customerID → customer_id
   CreatedAt → created_at

   Explicit type casting:
   CAST(amount AS NUMERIC) AS amount,
   CAST(created_at AS TIMESTAMP) AS created_at,

   Null handling:
   NULLIF(status, '') AS status,  -- empty string → NULL

   Trim whitespace:
   TRIM(LOWER(email)) AS email,

   Add source metadata:
   _fivetran_synced AS _loaded_at,
   '{{ source_name }}' AS _source,

3. Staging model template:
   WITH source AS (
     SELECT * FROM {{ source('app_db', 'orders') }}
   ),
   renamed AS (
     SELECT
       id AS order_id,
       customer_id,
       CAST(total_amount AS NUMERIC) AS total_amount,
       CAST(created_at AS TIMESTAMP) AS created_at,
       NULLIF(status, '') AS status,
       _fivetran_synced AS _loaded_at
     FROM source
   )
   SELECT * FROM renamed

4. What NOT to do in staging:
   - Do not join to other models
   - Do not filter rows (preserve all source data; filter in marts)
   - Do not apply business logic (e.g. calculating total_with_tax)
   - Do not rename using business terminology (use source system names at this layer)

Return: staging model templates for each source, type casting patterns, source.yml configuration, and anti-pattern list.