![]() If the ETL developer isn’t aware classifications have been added, they won’t know to go and look for them in the sys.sensitivity_classifications view. A data modeler or business analyst might be the person adding and managing the sensitivity classifications. The entire process of adding and managing sensitivity classifications may be invisible to an ETL developer.ETL developers may not have access to the data warehouse in the Azure Portal to see the sensitivity classifications there.There are currently no visual indicators of sensitivity classifications in SSMS or Azure Data Studio. ![]() If you spin up an Azure SQL Data Warehouse with the sample database, you can run this notebook from Azure Data Studio and see the results for yourself. This works the same way.Ĭheck out my SQL notebook for a demonstration of the issue as well as my workaround that I describe below. We would expect an index to be dropped when we drop the related table. This makes sense because sensitivity classifications are objects related to that table. You’ll see that they are dropped when we drop the old dimension table. In the animation above, you’ll first see the load process as described, and then it will replay with sensitivity labels added to the dimension table. Rename the dimension table to something like Dimension_OLDĪnimation of a table load process in Azure SQL DW.Create an upsert table via CTAS with the results of a union of new data from a staging table with existing data from the dimension table.Typically, when using T-SQL to load a dimensional model in Azure SQL DW, we perform the following steps: One issue that is specific to using data classifications in Azure SQL DW is that it is possible to inadvertantly drop your classifications when you are loading your tables using the recommended T-SQL load pattern. Left join sys.all_columns on sys.sensitivity_classifications.major_id = sys.all_columns.object_idĪnd sys.sensitivity_classifications.minor_id = sys.all_lumn_id Be Careful When Loading With CTAS and Rename Left join sys.all_objects on sys.sensitivity_classifications.major_id = sys.all_objects.object_id To view existing data classifications, you can query the sys.sensitivity_classifications view or look in the Azure Portal. WITH (LABEL='Confidential', INFORMATION_TYPE='Contact Info') Data classifications are database objects. You can add data classifications in the Azure Portal or via T-SQL or PowerShell. ![]() You can add the recommended classifications with a simple click of a button. The Azure Portal will even recommend classifications based upon your column names and data types. You can export a report of all labeled columns, and you can see who is querying sensitive columns in your audit logs. This could be an important feature for auditing your storage and use of sensitive data as well as compliance with data regulations such as GDPR. There are built-in classifications, but you can also add custom classifications. They allow you to label columns in your data warehouse with their information type and sensitivity level. Data classifications in Azure SQL DW entered public preview in March 2019. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |