1

I know that in ADX I can't update an existing row because it's an append-only system. I want to add a specific row with a condition: if there is no other row inside the table with the same values on certain columns.

I came up with this logic but I think this can be done much simpler:

.set-or-append target_table <|
let exists_row_count = old_table | where field1 == value1 and field2 == value2 | count()
let appended_row = case(exists_row_count == 0, <the whole record>, <empty record or null>)
appended_row

*need to mention that I get the value1, value2, and when I'm using a logic app here and I can iterate on each and every new record that I want to insert into the table, and of course, that the record is in tabular form.

Harel Yacovian
  • 137
  • 1
  • 11

1 Answers1

1

You can create a Materialized View on top of your original table, and dedup by the columns you choose.

Slavik N
  • 4,705
  • 17
  • 23
  • Thanks, I know that but still, I remain with the original table that has a large amount of data – Harel Yacovian Feb 01 '22 at 09:16
  • 1
    You can set a very short retention policy on the original table (say, 3 days), so that you won't have to keep the original data for longer. As for the Materialized View, you can define a much larger retention policy on it. – Slavik N Feb 01 '22 at 18:07
  • Thanks, the question is what will happen to the materialized view once the retention policy period is over and data is being removed from the source table? does it remove the data also from the materialized view? – Harel Yacovian Feb 02 '22 at 17:55
  • 1
    The data in the MV won't be affected when old data is deleted from the source table. The MV only updates based on new data that arrives to the source table. – Slavik N Feb 02 '22 at 22:42
  • Great, this solves my issue. – Harel Yacovian Feb 03 '22 at 07:44