Solved: Save incremental history - Page 2

rhenriquez94 · 10-13-2024 06:10 PM

Hello, I am new to Dataform and I need your help to enter this data. I want to make sure that the history is maintained, that is, that each day is recorded in a new row without overwriting the previous

config {

    type: "incremental",

    uniqueKey: ["dt"]

}

pre_operations {

  DECLARE event_timestamp_checkpoint DEFAULT (

    ${when(incremental(),

    `SELECT max(Fecha_informe) FROM ${self()}`,

    `SELECT timestamp("2000-01-01")`)}

  )

}

INSERT INTO

  `DEL_INFORME_COBRANZA.historico_kpi` ( Fecha_informe,

    No_vencido,

    Menor_31_dias,

    Entre_31_60_dias,

    Entre_61_90_dias,

    Mayor_90_dias,

    snapshot_date )

    

SELECT

  dt AS Fecha_informe,

  SUM(CASE

      WHEN Rango_Mora_Comb = "No vencido" THEN Importe_CLP

      ELSE 0

  END

    ) AS No_vencido,

  SUM(CASE

      WHEN Rango_Mora_Comb = "Menor 31 dias" THEN Importe_CLP

      ELSE 0

  END

    ) AS Menor_31_dias,

  SUM(CASE

      WHEN Rango_Mora_Comb = "Entre 31 y 60 dias" THEN Importe_CLP

      ELSE 0

  END

    ) AS Entre_31_60_dias,

  SUM(CASE

      WHEN Rango_Mora_Comb = "Entre 61 y 90 dias" THEN Importe_CLP

      ELSE 0

  END

    ) AS Entre_61_90_dias,

  SUM(CASE

      WHEN Rango_Mora_Comb = "Mayor a 90 dias" THEN Importe_CLP

      ELSE 0

  END

    ) AS Mayor_90_dias,

  CURRENT_DATE() AS snapshot_date

FROM

  ${ref("informe_partidas")}

WHERE

  Estado_Partida = 'Abierta'

GROUP BY

  dt

EDIT; When executing the flow it gives the following error:

mars124

Try something like this:

config {
    type: "incremental",
    protected: true
}

SELECT
  CURRENT_DATE() AS snapshot_date,
  SUM(CASE
      WHEN Rango_Mora_Comb = "No vencido" THEN Importe_CLP
      ELSE 0
  END
    ) AS No_vencido,
  SUM(CASE
      WHEN Rango_Mora_Comb = "Menor 31 dias" THEN Importe_CLP
      ELSE 0
  END
    ) AS Menor_31_dias,
  SUM(CASE
      WHEN Rango_Mora_Comb = "Entre 31 y 60 dias" THEN Importe_CLP
      ELSE 0
  END
    ) AS Entre_31_60_dias,
  SUM(CASE
      WHEN Rango_Mora_Comb = "Entre 61 y 90 dias" THEN Importe_CLP
      ELSE 0
  END
    ) AS Entre_61_90_dias,
  SUM(CASE
      WHEN Rango_Mora_Comb = "Mayor a 90 dias" THEN Importe_CLP
      ELSE 0
  END
    ) AS Mayor_90_dias
FROM ${ref("informe_partidas")}

${when(incremental(),
`WHERE Estado_Partida = 'Abierta' AND CURRENT_DATE() > (SELECT MAX(snapshot_date) FROM ${self()})`,
`WHERE Estado_Partida = 'Abierta'`)}

View solution in original post