I have a derived query that joins data from Teradata and AWS Athena sources. Both data sources are updated nightly. The derived view takes about 20minutes to complete. I noticed that the same query returned different results when executed back to back. The Teradata view has about 3K records and the result set of the derived view has a couple million records.
To test, I selected two records from the primary Teradata view and repeatedly executed the query. For three of the ~100 columns in the result set, the query returned either the expected values or NULL. Those three values come a lookup table in Teradata (a 6.7M records customer address view). When I looked at the execution plan, it said "completed = true", "memoryLimitReached = true", and "swapping = false" (I'm not sure if this happened to be taken from an execution with or without the correct values).
How is it that a query can complete without errors, yet produce different results?
I have read all of the available documentation and observed a lot of instructions for changing settings, but little on what those settings actually mean. I don't see anything to explain what I am seeing.