daanalytics

-- pii_name_tag
CREATE OR REPLACE TAG frostbyte_tasty_bytes.raw_customer.pii_name_tag
    ALLOWED_VALUES 'First Name', 'Last Name'
    COMMENT = 'PII Tag for Name Columns'
;

daanalytics

CALL ASSOCIATE_SEMANTIC_CATEGORY_TAGS(
   'customer_loyalty',
    EXTRACT_SEMANTIC_CATEGORIES('customer_loyalty')
);

daanalytics

select f.key::varchar as column_name
,      f.value:"privacy_category"::varchar as privacy_category  
,      f.value:"semantic_category"::varchar as semantic_category
,      f.value:"extra_info":"probability"::number(10,2) as probability
,      f.value:"extra_info":"alternates"::variant as alternates
from   table(FLATTEN(EXTRACT_SEMANTIC_CATEGORIES('customer_loyalty')::VARIANT)) AS f
;

daanalytics

{
  "BIRTHDAY_DATE": {
    "extra_info": {
      "alternates": [],
      "probability": "1.00"
    },
    "privacy_category": "QUASI_IDENTIFIER",
    "semantic_category": "DATE_OF_BIRTH"
 ...
 ...

daanalytics

select EXTRACT_SEMANTIC_CATEGORIES('customer_loyalty') as esc_customer_loyalty
;

daanalytics

SELECT * 
FROM   information_schema.packages 
WHERE  language = 'python' 
AND    package_name in  ('faker', 'pandas')
;

daanalytics

SELECT distinct package_name
FROM   information_schema.packages 
WHERE  language = 'python' 
;

daanalytics

 # Where clause for the Fully Qualified Table Name
 wc_full_qual_table_name = database_name + '.' + schema_name + '.' + table_name
        
 sql_quality_metrics = f"""SELECT *
                           FROM   QUALITY_ASSURANCE.QUALITY_CHECK.DATA_QUALITY_METRICS
                           WHERE  FULL_QUAL_TABLE_NAME = '{wc_full_qual_table_name}'
                           ; """
     
 st.write("Data Quality Metrics for table: " + table_name)

daanalytics

# Call Snowflake Procedure when button is clicked
if st.sidebar.button('Run Quality Assurance'):
    cursor = ctx.cursor()
    cursor.execute(f"CALL QUALITY_ASSURANCE.QUALITY_CHECK.DATA_QUALITY('{database_name}', '{schema_name}', '{table_name}')")
    st.sidebar.success('Procedure has been executed successfully.')
    cursor.close()    

daanalytics

 # Select Map type to show
 st.sidebar.title("Select a table to examine")

 # Set Variables for Snowflake Procedure
 database_name = st.sidebar.text_input('Database Name')
 schema_name = st.sidebar.text_input('Schema Name')
 table_name = st.sidebar.text_input('Table Name')