In GCP Dataform, the destination table of my workflow is an incremental table. For efficiency and cost reduction purposes, I want this table to be partitioned by date with partition filter requirement set to true. Therefore the following configuration and code :
config {
type: "incremental",
uniqueKey: ["date_country_item_key"],
bigquery: {
partitionBy: "date",
requirePartitionFilter: true,
clusterBy: ['country'],
updatePartitionFilter: "date >= CURRENT_DATE()-4 AND date < CURRENT_DATE()"
},
schema: "ga4",
name: "ww_item_event_counts"
}
SELECT * FROM ${ref('205_aggregate')}
I am running into the following error when running the workflow incremental mode : "Query error: Column name date is ambiguous".
The error does not happen when I remove the partition requirement from the table and suppress the "updatePartitionFilter: "date >= CURRENT_DATE()-4 AND date < CURRENT_DATE()".
Could you help me fix the ambiguity around the date field so that I can keep the partition requirement set to TRUE ?
Please note I have checked the format of the date field in source and target table. It is properly set to a date format.
LinkedIn
Twitter
