I created my first data flow from DEAR to BigQuery. It is supposed to insert new customers and update existing customers. The model 'runs successfully' but doesn't record any billed rows, successful rows or error rows. The customer table in BigQuery is not. I have attached a screenshot of my data flow and the look up component. Thanks!
Hello Clea,
Thank you for contacting us.
As we see on your screenshot, there is incorrect lookup in it.
Lookup is a component that matches input records with records from another data source and adds columns of the matched records to the scope.
It is not possible to pull the ID value with a lookup through the ID value of the source and the ID value of the target because this will not give the expected result. You need to specify other values that are contained in the source and target and uniquely identify the record.
Please see this link to know more about lookup settings.
Feel free to contact if you have any additional questions, we will be happy to help.
Best regards,
Yevheniia Bilotserkovska.
Hello Yevheniia, thank you for your response. Is this the correct way to set up the Lookup Component? I tried to run it again with these changes and it still doesn't work
Thanks
Hello Clea,
Thank you for contacting us.
No, these settings are incorrect. Currently, the Result Columns in your case are Name and Status, and Keys is ID. However, the Result Columns must be an ID one.
Keys - the lookup table columns, by which input records and lookup table records are matched.
Result Columns - the columns that will be added to the output records.
Please see an example here:
Please tell us if this helps.
Best regards,
Mariia
Hi Mariia, thanks for your response. Sorry I'm still not quite understanding. In this case it makes sense to use the ID as the key and then include the rest of the columns as results columns. So I'm still not quite sure how to make it work?
Thanks
Clea
Hi Clea,
Thank you for your reply.
To use the Lookup mapping, you need to have a column (or a set of columns) that uniquely identifies a record, other than the primary key or Lookup Column.
In order to detect which operation we need to perform (Insert or Update) we need to check if provided ID (from source) exists in the target or not. For this, we use the ID column as a Result Column. But in order to find it we need to use other columns as Keys. The Parameters section is used to specify the mapping between used Keys and source columns to detect the unique record in the target system.
The settings we provided you above work this way:
1. We execute this query: SELECT ID FROM customers WHERE Name (source)= Name (target) AND Status (source)= Status (target).
2. Then if ID exists we will go to perform Update, if ID is null - to Insert.
Please try these settings and let us know the results.
