[Core] new loaded_at_query config for source freshness - dbt-core Issue #8797

[FishtownBuildBot]

Originating from this issue: dbt-labs/dbt-core#8797

new loaded_at_query config for source freshness - the ability to specify custom sql to generate the maxLoadedAt time stamp on the source (vs. the "built-in" one which uses the loaded_at_field - collect_freshness)

example (check this works):

sources:
  - name: my_source
    freshness:
      error_after:
        count: 2
        period: hour 
    loaded_at_query: |
      select from audit_tables
      where table_name = {{ this.identifier }}
      and status = 'completed'

Notes:

• cannot specify both loaded_at_field and loaded_at_query:

sources:
  - name: jaffle_shop
    database: raw
    freshness: 
      error_after: {count: 24, period: hour}
    loaded_at_field: _etl_loaded_at
    loaded_at_query: ...

• if you specify something like this, whatever is "closest" to the table wins:

sources:
  - name: jaffle_shop
    database: raw
    freshness: 
      error_after: {count: 24, period: hour}
    loaded_at_field: _etl_loaded_at
    tables:
      - name: orders
        loaded_at_query: ...

• filter won't work for loaded_at_query