Left join or Lookup not retrieving all data from the table

[404c8e6ac806a3c05f90]

Hi! I have a table named S800 which has a column named MATERIAL. I'm trying to use this columns as a key from another table (MATERIAL_2020) which has the same column. I tried both left join (S800 being the left) and a lookup, but both produced the same result: there are some MATERIAL id's that are not matching. I will post some screenshots to clarify.. Both columns have the same data type.

In the screenshot below, I did a select in bigquery to see if there was a value assossiated with the MATERIAL and GRP_MERC.

After I did the lookup/left join, GRP_MERC was not found by trifacta.

What can be the cause of this?

[Trifacta_Alumni · Accepted solution]

Hi @Daniel Sacramento? 

When you're working in the Trifacta transformer grid, you are operating on a sample of your data. This can lead to scenarios like the one you describe, where not all of your join keys are present in the samples associated with each side of the join. If you need to see matching keys for all rows in the transformer grid, you can collect a new sample by following the instructions in this article: https://community.trifacta.com/s/article/how-to-collect-a-new-sample

After you collect your new sample, you will likely see matching keys.

When you run a job on your recipe, Trifacta executes your steps against the full dataset, not against a sample. So your final output will contain the results that you expect.

For more information on sampling, you can read a detailed overview here: https://community.trifacta.com/s/article/sampling

[404c8e6ac806a3c05f90]

Thank you very much,