.png){kind=logo}
Except
-
DarkLight
Except
-
DarkLight
This article is specific to the following platforms - Snowflake - Redshift - BigQuery - Synapse.
Except Component
The Except component lets users compare two input flows (a primary table and a comparison table) and then output any rows from the primary table that do not also exist in the comparison table.
Because an entire row is compared, only overlapping columns are considered. Otherwise, all rows would always look unique and thus be passed on. Only unique rows are passed to the next component if there exist duplicate rows in the primary input source.
Properties
| Property | Setting | Description |
|---|---|---|
| Name | String | A human-readable name for the component. |
| Cast Types | Select | Choose whether to cast types. Yes: if the same-named column from both inputs has differing input types, Matillion ETL attempts to cast them to a common type. This is not guaranteed to work—please check your data carefully. No: if the same-named column from both inputs has differing input types, Matillion ETL reports an error and will not continue. |
| Primary Table | Select | Select the input source to treat as the primary table. The other input source becomes the secondary (comparison) table. |
Strategy
Generates multiple select queries separated by EXCEPT.
Generates multiple select queries separated by aLEFT JOIN.
Example
This example includes two tables of user data. One table is an updated version of the other. When viewing the table with updated data, it is apparent that some users have changed their details since the details were last recorded. To build compelling data, we wish to find any data relating to users who have recently changed their details. To do this, we will use the Except component.
The below image shows the data and its updated counterpart.
Several users have changed their "plan". Were this a table with millions of users, it would be beneficial to isolate only the updated records and analyse them. However, if we do not mind which field has changed, and only focus on rows being different now compared to before, we can use the Except component to find all rows that have changed.
For the component setup, in this example we select "Yes" on the Cast Types property, because we want to try to reconcile the data rather than skip the record.
Lastly, we take a sample of the data output from Except, and find the unique rows.
