updateFinalTable
Generate a SQL statement to copy new data from the raw table into the final table.
Responsible for:
Pulling new raw records from a table (i.e. records with null _airbyte_loaded_at)
Extracting the JSON fields and casting to the appropriate types
Handling errors in those casts
Merging those typed records into an existing table
Updating the raw records with SET _airbyte_loaded_at = now()
Implementing classes are recommended to break this into smaller methods, which can be tested in isolation. However, this interface only requires a single mega-method.
Parameters
the suffix of the final table to write to. If empty string, writes to the final table directly. Useful for full refresh overwrite syncs, where we write the entire sync to a temp table and then swap it into the final table at the end.
The latest _airbyte_extracted_at for which all raw records with that timestamp have already been typed+deduped. Implementations MAY use this value in a _airbyte_extracted_at > minRawTimestamp filter on the raw table to improve query performance.
often the data coming from the source can be faithfully represented in the destination without issue, and using a "CAST" expression works fine, however sometimes we get badly typed data. In these cases we can use a more expensive query which handles casting exceptions.