data-engineering-skills

```sql
-- dbt model: stg_customers.sql
-- Transforms raw customer data into a clean staging table

{{
  config(
    materialized='table',
    schema='staging',
    tags=['customer']
  )
}}

WITH source_data AS (
  SELECT
    customer_id,
    first_name,
    last_name,
    email,
    signup_date,
    country,
    CASE
      WHEN email IS NULL THEN 'missing_email'
      ELSE email
    END AS cleaned_email,
    CASE
      WHEN country IN ('USA', 'United States') THEN 'US'
      WHEN country IN ('UK', 'United Kingdom') THEN 'UK'
      ELSE 'Other'
    END AS region
  FROM {{ source('raw', 'customers') }}
),

cleaned_data AS (
  SELECT
    customer_id,
    TRIM(first_name) AS first_name,
    TRIM(last_name) AS last_name,
    cleaned_email,
    signup_date::DATE AS signup_date,
    region,
    CURRENT_TIMESTAMP AS etl_loaded_at
  FROM source_data
  WHERE customer_id IS NOT NULL
)

SELECT * FROM cleaned_data;

-- Data quality test
{% test not_null('customer_id') %}
SELECT * FROM {{ this }}
WHERE customer_id IS NULL;
{% endtest %}

{% test accepted_values('region', ['US', 'UK', 'Other']) %}
SELECT * FROM {{ this }}
WHERE region NOT IN ('US', 'UK', 'Other');
{% endtest %}
```

**Model Structure Explanation:**
This dbt model follows a clean staging pattern by:
1. First creating a CTE (`source_data`) that handles raw data cleaning including null handling for emails and standardizing country codes
2. Then creating a `cleaned_data` CTE that applies additional transformations like trimming whitespace and proper date casting
3. Finally selecting all columns from the cleaned data CTE

**Key Features Implemented:**
- Null handling for critical fields (customer_id, email)
- Data type conversion (signup_date to DATE)
- Standardization of categorical data (country to region)
- Timestamp for tracking load times
- Two data quality tests: one for null values in customer_id and another for valid region values

**Usage Notes:**
This model should be run as part of your dbt project's staging layer. The tests will automatically run during `dbt test` and fail the build if any records violate the constraints. The `etl_loaded_at` field helps track when data was last processed.