Is there a simple way to always filter out where _fivetran_deleted = 'true'?

We are using Fivetran to sync our production data with BigQuery. However, when data is deleted in production, it still shows up in our Looker explores; there’s a field called _fivetran_deleted and rather than removing deleted records, the records are simply marked ‘true’ in this field to indicate that they’re deleted.

We have asked them if there’s a way they can just set it to remove deleted fields, but it sounds like the answer from them is that we need to filter them out manually.

Is there a simple way to do this in our base views? We don’t want ANY records in Looker that are marked as deleted, and I’m afraid this is going to be extremely cumbersome.

Solved Solved
0 2 522
1 ACCEPTED SOLUTION

Dawid
Participant V

You would need to use sql_always_where parameter in your explores. This would always apply a WHERE clause to all queries in those explores

https://docs.looker.com/reference/explore-params/sql_always_where

Unfortunately it would be very repetetive but there’s nothing you can do on a connection or model level.

You could also transform your fivetran data in BigQuery and apply these restrictions in your data model, that would save you from managing these Looker parameters in all your explores.

View solution in original post

2 REPLIES 2

Dawid
Participant V

You would need to use sql_always_where parameter in your explores. This would always apply a WHERE clause to all queries in those explores

https://docs.looker.com/reference/explore-params/sql_always_where

Unfortunately it would be very repetetive but there’s nothing you can do on a connection or model level.

You could also transform your fivetran data in BigQuery and apply these restrictions in your data model, that would save you from managing these Looker parameters in all your explores.

Thank you @Dawid, that’s kind of what I thought I might hear but I was hoping anyway! Might see about managing this in BQ - thanks for that suggestion.

Top Labels in this Space
Top Solution Authors