In our e-commerce project, raw data comes from tables like customers , orders , order_items, products, and payments.

By declaring these tables as sources in dbt, you can:

  • Reference raw data consistently with the source() function

  • Apply tests directly at the ingestion level

  • Monitor freshness to ensure timely updates

  • Provide clear documentation and lineage

Defining Sources

📋
sources.yml 
version: 2

sources:
  - name: ecommerce
    description: "Raw e-commerce data from the transactional system"
    database: raw
    schema: ecommerce
    loader: custom_pipeline
    loaded_at_field: created_at

    freshness:
      warn_after: {count: 6, period: hour}
      error_after: {count: 12, period: hour}

    tables:
      - name: customers
        description: "Customer master data"
        columns:
          - name: customerid
            description: "Primary key"
            tests: [unique, not_null]

      - name: orders
        description: "Order headers with customer and order date"
        columns:
          - name: orderid
            tests: [unique, not_null]
          - name: customerid
            tests:
              - not_null
              - relationships:
                  to: source('ecommerce', 'customers')
                  field: customerid

      - name: order_items
        description: "Products purchased in each order"
        columns:
          - name: order_item_id
            tests: [unique, not_null]
          - name: orderid
            tests:
              - relationships:
                  to: source('ecommerce', 'orders')
                  field: orderid

      - name: products
        description: "Product catalog with price and category"
        columns:
          - name: productid
            tests: [unique, not_null]

      - name: payments
        description: "Payment transactions"
        columns:
          - name: paymentid
            tests: [unique, not_null]
          - name: orderid
            tests:
              - relationships:
                  to: source('ecommerce', 'orders')
                  field: orderid

Validating Source Data

You can configure freshness to monitor how often raw data is updated:

sources:
  - name: ecommerce
    tables:
      - name: customers
        columns:
          - name: customerid
            tests: [unique, not_null]
          - name: email
            tests: [unique, not_null]

This ensures each customerid and email is unique and not null.

Checking Freshness

You can configure freshness to monitor how often raw data is updated:

sources:
  - name: ecommerce
    loaded_at_field: updated_at
    freshness:
      warn_after: {count: 12, period: hour}
      error_after: {count: 24, period: hour}

Run the check with:

dbt source freshness

Advanced Configurations

1. Dynamic schemas per environment

schema: "{% if target.name == 'prod' %}ecommerce_prod{% else %}ecommerce_{{ target.name }}{% endif %}"

2. Quoting settings

quoting:
  database: true
  schema: true
  identifier: false

3. External data sources (e.g., S3)

external:
  location: "s3://ecommerce-data/raw/orders/"
  file_format: "parquet"

Best Practices for Source Management

  • Group sources by business domain (customers, orders, products, payments).

  • Provide clear descriptions for all tables and columns.

  • Apply unique and not null tests to keys.

  • Define freshness thresholds that match your ETL schedule.

  • Keep sources.yml close to staging models for readability.

PreviousNext