I am setting up an analysis to calculate CO2 equivalency for some streams in compliance with local legislation. Emissions factors change periodically, so I wanted to have a system that would return the appropriate emissions factor for a given time period. Rather than store these as time series, I elected to store them in a table, and retrieve them with table lookup.
It works fine - I've attached screenshots of the table design, the lookup query and the resultant SQL.
I can "work around" it by setting default values if no data is found, or by just setting the single value and update it going forward if it changes (and changing it for backfill runs).
But I am curious as to why it isn't working on backfilling. It works fine in the live environment.