1) The actual description of what the Except component does seems to be backwards. What the help says is "Compares two input flows and outputs any rows from the primary table that do not exist in the comparison table." However, after you choose the primary table from the drop down, the SQL which is generated shows that what gets selected is everything EXCEPT what matches the rows the primary table.
2) After you've put the Except component onto the canvas, hooked up two other components, and set the primary table option, the Except component will validate and you can check the SQL. However, if you then go and change the "primary table" option and re-validate, the SQL doesn't change. You have to delete the component and recreate the connections in order to get the SQL to change.
3) Last night I had an issue in production where it appears that the Except component randomly switched what it considered the "primary table" when it validated. I'm saying this because this is only the 2nd time this process has run in production, and given the issue I had last night (seemingly creating duplicate records because the except was using the wrong primary table), if the component hadn't changed then there shouldn't have been any data the first night it was run.
Looking for some answers/insight into these problems. If one of the components is randomly changing what it does. that's a big problem.
12 Community Answers
David Lipowitz —
Thanks for writing in. We haven’t heard of any of our users running into the issues you describe. Can you confirm which version of Matillion you’re running? You can do so under the Help → About menu to the upper right of the canvas.
Regarding the points you mention:
In my own testing of the EXCEPT component, the records returned are those that exist in the primary table but not the secondary. I believe this is the correct behavior and matches the description under the Help tab.
I tried changing the primary table from the original table to the secondary: the SQL changed accordingly as did the resulting dataset.
We’re unaware of Matillion altering the values inside components. Is it possible that this value was left pointing to the second table during testing? Is there anyone else in your organization who might have changed the value in question?
The version of Matillion I am using is: 1.35.9 (build 206).
1) In terms of correct behavior, when the 'primary table' parameter is selected, should that correspond to the first result set in SQL or the second result set? In ANSI SQL (and in Snowflake SQL), the except operator keeps everything in the first result set that doesn't exist in the second result set. However, when I choose the primary table parameter, it's that table which is then placed as the second result set in the resulting SQL
2) If possible, I would certainly be willing to do a screen share, so that you can see the SQL not being updated in the component first hand.
3) I'm currently the only person with access to Matillion at my company, so no one else could have changed it. Also, as I stated in my first message, if the functionality of that component had been left in a 'testing' state, it wouldn't have worked at all the first time it ran on in production on Friday, because one of the two tables was completely empty.
I know this all sounds strange, and it initially doesn't make sense, but something is definitely going on.
I was able to recreate this issue in my Snowflake instance so no need to retrieve your logfile or send it to us. I gave the developers what they need and opened a bug report. Will let you know when I hear about their progress on this.
We’re looking into a solution here. In the meantime, and as a work-around, can you please create a SQL component and paste the correct SQL into it? Then you can feed the output from that SQL into the rest of you’re Transformation data flow.
Hopefully that will get you going until we can resolve the bug.
This was why I initially opened this as a community post instead of a straight support ticket. Assuming I wasn't just crazy, I knew that someone else had to be seeing the same issue. Hopefully once the developers get a look at it, we'll get a patch before too long.