dataset: datasource/database/schema/sales_transactions

variables:
  FRESHNESS_HOURS:
    default: 24

checks:
  - schema:    
      allow_extra_columns: false
      allow_other_column_order: false
  - row_count:
      threshold:
        must_be_greater_than: 0  
  - freshness:
      column: order_date
      threshold:
        unit: hour
        must_be_less_than_or_equal: ${var.FRESHNESS_HOURS}
	- failed_rows:
      name: "order_date must not be in the future"
      qualifier: order_date_not_future
      expression: order_date > CURRENT_TIMESTAMP    
  - failed_rows:
      name: "No duplicate line items per order"
      qualifier: dup_order_product
      query: |
        SELECT order_id, product_id
        FROM sales_transactions
        GROUP BY order_id, product_id
        HAVING COUNT(*) > 1
      threshold:
        must_be: 0      
  - failed_rows:
      name: "net_amount must equal (quantity * unit_price) - discount_amount"
      qualifier: net_amount_formula
      expression: net_amount <> ((quantity * unit_price) - discount_amount)     
  - failed_rows:
      name: "discount cannot exceed gross amount"
      qualifier: discount_le_gross
      expression: discount_amount > (quantity * unit_price)
   
columns:
  - name: order_id
    data_type: string
    checks:
      - missing:
      - invalid:
          valid_min_length: 1
          valid_max_length: 64         
  - name: customer_id
    data_type: string
    checks:
      - missing:
  - name: product_id
    data_type: string
    checks:
      - missing:     
  - name: quantity
    data_type: integer
    checks:
      - missing:
      - invalid:
          name: "Quantity must be positive"
          valid_min: 1        
  - name: unit_price
    data_type: float
    checks:
      - missing:
      - invalid:
          valid_min: 0          
  - name: channel
    data_type: string
    checks:
      - missing:
      - invalid:
          name: "Allowed sales channels"
          valid_values:
            - online
            - store
            - marketplace
            - wholesale
            - mobile_app