> For the complete documentation index, see [llms.txt](https://docs.datasuite.vn/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.datasuite.vn/data-platform/index/development/testing-validation.md).

# Testing & Validation

Ensuring data quality is critical for reliable analytics. This guide covers testing strategies, validation techniques, and monitoring approaches for the DataSuite ETL pipeline.

## DBT Testing Framework

### Built-in Tests

```yaml
# models/schema.yml
version: 2

models:
  - name: fact_sale
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - date_key
            - territory_key
            - product_key
            - salesorderid
    columns:
      - name: sale_key
        tests:
          - unique
          - not_null
      - name: total_due
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 1000000
```

### Custom Tests

Create `tests/fact_sale_data_integrity.sql`:

```sql
-- Test that fact_sale totals match source data
SELECT 
    'Row count mismatch' as test_name,
    source_count,
    fact_count,
    ABS(source_count - fact_count) as difference
FROM (
    SELECT COUNT(*) as source_count
    FROM {{ source('bronze_layer', 'sales_orders') }}
) source
CROSS JOIN (
    SELECT COUNT(*) as fact_count  
    FROM {{ ref('fact_sale') }}
) fact
WHERE ABS(source_count - fact_count) > 100
```

## Data Quality Monitoring

### Quality Checks in ClickHouse

```sql
-- Data freshness check
SELECT 
    'bronze_layer' as layer,
    MAX(_ingested_at) as last_update,
    CURRENT_TIMESTAMP() - MAX(_ingested_at) as lag_minutes
FROM bronze_layer.sales_orders

UNION ALL

SELECT 
    'gold_layer' as layer, 
    MAX(created_at) as last_update,
    CURRENT_TIMESTAMP() - MAX(created_at) as lag_minutes
FROM gold_layer.fact_sale;
```

## Pipeline Validation

### End-to-End Testing Script

Create `scripts/validate-pipeline.sh`:

```bash
#!/bin/bash

echo "🔍 DataSuite ETL Pipeline Validation"
echo "===================================="

# Test data ingestion
echo "Testing data ingestion..."
MYSQL_COUNT=$(docker exec mysql mysql -uroot -ppassword adventureworks -e "SELECT COUNT(*) FROM Sales.SalesOrderHeader;" -s -N)
CH_COUNT=$(curl -s -u admin:clickhouse123 http://localhost:8123/ -d "SELECT COUNT(*) FROM bronze_layer.sales_orders FORMAT TabSeparated")

echo "MySQL records: $MYSQL_COUNT"
echo "ClickHouse records: $CH_COUNT"

# Run DBT tests
echo "Running DBT tests..."
cd /path/to/dbt/project
dbt test

echo "Validation complete!"
```

## Automated Quality Monitoring

### Data Quality Dashboard

Monitor key metrics:

* Row counts at each layer
* Data freshness (time since last update)
* Test pass/fail rates
* Pipeline execution times
* Error rates and patterns

### Alerting Rules

Set up alerts for:

* Data pipeline failures
* Significant row count changes (>10%)
* Data freshness exceeding thresholds
* Test failures
* Performance degradation

## Next Steps

With testing and validation in place, your DataSuite ETL pipeline is production-ready. Consider:

1. Setting up automated monitoring dashboards
2. Implementing data lineage tracking
3. Adding more sophisticated data quality rules
4. Creating data catalog documentation

Your pipeline now has comprehensive quality assurance to ensure reliable, accurate analytics data.