1818792 - Stop exposing fivetran datasets directly via user-facing views

Status: RESOLVED FIXED

(Data Platform and Tools :: General, task)

Description

[Wesley Dawson [:whd]]

The only case I'm aware of where we're doing this is stripe.

In https://mozilla-hub.atlassian.net/browse/DSRE-617 we mostly replaced moz-fx-data-shared-prod.stripe_external with moz-fx-data-bq-fivetran.stripe. As part of that migration we granted direct read access to the latter to workgroup:stripe, since that was what was granted to users of the former. We now reference that directly in user-facing views: https://github.com/mozilla/bigquery-etl/blob/main/sql/moz-fx-data-shared-prod/stripe/itemized_payout_reconciliation/view.sql#L11.

We generally treat fivetran datasets as implementation details/intermediate datasets that are not suitable for long term storage or being exposed to users via user-facing views. As such, we should switch to e.g. copying this data into a dataset in shared-prod, or perhaps syndicating it if we are absolutely certain as to the data's stability.

The current configuration relies implicitly on the user-facing view not being authorized, because the stripe and stripe_derived datasets are currently accessible to workgroup:mozilla-confidential. This can be confusing to users, but is at least a commonly used pattern (e.g telemetry has this configuration for workgroup-confidential doctypes). Any DE/DSRE can reason about the permissions entirely from bqetl metadata. The easiest place to put the data would be to put the copy of the data is stripe_external, but the perhaps more technically correct way to do this is to lock down stripe_derived to workgroup:stripe and then grant table-level workgroup_access to workgroup:mozilla-confidential for all existing tables. Syndicating the data into a workgroup-confidential dataset doesn't prevent a fivetran change from breaking our infrastructure, but it would at least explicitly codify that dependence and make it possible to reason about access issues entirely via bqetl.

Comment 1

[BMO Automation]

Attachment: [mozilla/bigquery-etl] Bug 1818792 - Stop exposing fivetran datasets directly via user-facing views (#3660)