Not a member of GistPad yet?
Sign Up,
it unlocks many cool features!
- -- Author: [email protected]
- -- Purpose: Flags any ct_product_id whose max fiscal_month is behind the overall max.
- -- A product lagging behind means its financial feed is stale or broken.
- {{
- config(severity='warn')
- }}
- -- Determine the maximum fiscal month available per product id
- WITH PRODUCT_FRESHNESS AS (
- SELECT
- CT_PRODUCT_ID
- , MAX(FISCAL_MONTH) AS MAX_FISCAL_MONTH
- FROM {{ ref('bgs_financial_monthly_fact') }}
- GROUP BY CT_PRODUCT_ID
- )
- SELECT
- CT_PRODUCT_ID
- , MAX_FISCAL_MONTH
- , MAX(MAX_FISCAL_MONTH) OVER () AS MAX_FISCAL_MONTH_OVERALL
- FROM PRODUCT_FRESHNESS
- QUALIFY MAX_FISCAL_MONTH <> MAX(MAX_FISCAL_MONTH) OVER ()
RAW Paste Data
Copied
Advertisement
