Does someone have documentation / a code example h...
# singer-tap-developmentj
jobert_abma
09/27/2021, 7:22 PMDoes someone have documentation / a code example how to copy over RDS/PSQL column comments over to another target (Snowflake, in our case)?
s
stephen_bailey
09/28/2021, 1:27 PMI don't think it's implemented in the Postgres tap and Snowflake target, but theoretically if the tap emits a schema message with
comment: <column_comment>j
jobert_abma
09/29/2021, 12:58 AM@stephen_bailey Thanks for the information, I was thinking along the same lines in case there wasn’t something for this already. We’ll explore this further! For context: our product engineers are responsible for data sensitivity annotations. These annotations are stored in the PGs column comments. We’re planning to replicate those into our data warehouse so that we can anonymize the data according to that schema.
s
stephen_bailey
09/29/2021, 1:00 AMi have been thinking about this problem for a long time, and have annoyed the meltano team before 🙂 . it would be great to have standard vocabulary and implementation so that tap developers can annotate the catalog and data loaders can pass them in a standard format. i know that postgres does not have column tags, but are you thinking of using object tags in snowflake?
stephen_bailey
09/29/2021, 1:01 AMdo your postgres comments look something like:
person address. @PIIj
jobert_abma
09/29/2021, 1:58 AMIt comes from this gem: https://github.com/Hacker0x01/data_classification. We store it in a JSON blob in the comments, like:
Copy code
{
"tags": [
"classification:confidential"
]
}jobert_abma
09/29/2021, 2:01 AMI was thinking to simply copy the comments to Snowflake and then parse them in an Airflow DAG or with dbt (although I’m not sure how trivial table reflection is with dbt). We ingest the entire database into Snowflake and then create an anonymized dataset for our product and engineering members to visualize data in Looker. For other pipelines like machine learning and our “gold” tier data, we use manual code review to avoid sensitive data to end up there (since we’re in full control of that).
jobert_abma
09/29/2021, 2:01 AMEarly thinking though!
a
aaronsteers
09/29/2021, 3:31 PMHi, @jobert_abma. Would you mind logging an issue on this topic if we don't have one already, or perhaps a couple? 😉
A few things we can do:
1. Tap developers can annotate their JSON schema during discovery.
2. Target developers can look for markers in the JSON Schema and use those markers to annotate the target columns.
3. We can optionally start with the
description