Solved

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

  • 14 December 2021
  • 2 replies
  • 100 views

Userlevel 1

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.

icon

Best answer by Dawid 14 December 2021, 21:13

View original

This topic has been closed for comments

2 replies

Userlevel 1

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.

Userlevel 7
Badge +1

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.