Continuing our T-SQL journey, Patrick explores the use of the OVER and RANK functions in combination with common table expressions (CTE) in Azure SQL Database and Azure Synapse Analytics. RANK (Transact-SQL) https://learn.microsoft.com/sql/t-sql/functions/rank-transact-sql?view=sql-server-ver16 SELECT - OVER Clause (Transact-SQL) https://learn.microsoft.com/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16 WITH common_table_expression (Transact-SQL) https://learn.microsoft.com/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16

Your detective journey starts with the Kusto Detective Agency for Azure Data Explorer. This is a really fun way to learn the Kusto Query Languate (KQL)! Patrick gets you started! And, it's FREE! Kusto Detective Agency: https://detective.kusto.io/

Want to know more about a slowly changing dimension? What about the different types? Patrick goes through examples and ties in the surrogate key! Sample: https://github.com/guyinacube/demo-files/blob/master/video%20demos/Synapse/20230124%20-%20SCD%20Demo.sql Handling Type II Dimension with the MERGE Statement https://sqldownsouth.blogspot.com/2010/11/handling-type-ii-dimension-with-merge.html Slowly Changing Dimension https://en.wikipedia.org/wiki/Slowly_changing_dimension Star Schema The Complete Reference https://amzn.to/3QJjw2E

We've had some questions about what a Common Table Expression (CTE) is. Patrick describes what they are and how you can use them in SQL Server or Azure Synapse Analytics. WITH common_table_expression (Transact-SQL) https://learn.microsoft.com/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16 Sample Scripts - https://github.com/guyinacube/demo-files/tree/master/video%20demos/Synapse

We've talked about using a surrogate key in your data warehouse whether that's Azure Synapse Analytics or something else. Patrick looks at why you should consider this even if you aren't using a slowly changing dimension.

Still using the older SQL Profiler? Patrick looks at how you can use the Profiler within Azure Data Studio to capture SQL queries from SQL Server or Azure SQL Database with just a few clicks.

When using Azure Synapse Analytics Serverless Pools, using Azure AD auth is pretty simple. But, what about SQL Auth? How do you get hat working? Patrick shows you how! Control storage account access for serverless SQL pool in Azure Synapse Analytics https://learn.microsoft.com/azure/synapse-analytics/sql/develop-storage-files-storage-access-control?tabs=managed-identity Sample Script: https://github.com/guyinacube/demo-files/blob/master/video%20demos/Synapse/20221206%20-%20Connect%20to%20Serverless%20-%20Security%20Script.sql

You've probably heard the term lakehouse with various services like Azure Synapse Analytics. But what actually is a lakehouse? And why is it different from a data warehouse?

Using notebooks in Azure Synapse Analytics? Do you find yourself wanting to have a dynamic value for use with your pipelines? Patrick shows you how you can accomplish this!