Transcription
Snowflake for SQL ServerTM UsersPart 1 – Core ConceptsDr Greg LowSQL Down Under Pty er.comFirst edition January 2020The Snowflake logo is a registered trademark of Snowflake Inc.SQL Server is a trademark of Microsoft CorporationCover Awesome image by the amazing Aaron Burden(c/- Unsplash https://unsplash.com/photos/5AiWn2U10cw)This eBook is copyright material and must not be copied, reproduced, transferred, distributed, leased,licensed or publicly performed or used in any way except as specifically permitted in writing by thepublishers, as allowed under the terms and conditions under which it was purchased or provided asstrictly permitted by applicable copyright law. Any unauthorized distribution or use of this text may bea direct infringement of the author’s and publisher’s rights and those responsible may be liable in lawaccordingly.The Snowflake product described in this eBook is itself subject to constant change. The content anddetails described could change at any point in time. We've done our best to make this eBook as errorfree as possible at the time of publication, but we don't promise that it is error free or that anything wedescribe will work for you or continue to work for you.Note from the author:I've worked with databases like SQL ServerTM and Snowflake for decades. This eBook is acompilation of my observations about the differences between the products based upon a series ofblog posts that I have either made or are scheduled to be made as part of my Thursday "Snowflakefor SQL Server Users" series. (On each heading, I've added the publication date and depending uponwhen you read this, some might be in the future).We intend to keep enhancing and upgrading this book. If you have feedback for it, please send that tosnowflakeforsqlserverusersbooks@sqldownunder.com
Need to learn about Snowflake or SQL Server ? SQL Down Under offer online on-demand coursesthat you can take whenever you want. We have many SQL Server courses, and our first Snowflakecourses are being built right now.You can learn with Greg right now !We’re rapidly expanding our list of courses.Check us out now at http://training.sqldownunder.com 2020 Dr Greg Lowhttp://sqldownunder.comPage 2
Contents1Data Warehouses as a Service . 51.1Why Snowflake? . 5Cloud Transformation . 5Data Warehouses . 61.2 Cloud-First Design . 72Cloud First . 7Nice to have a blank slate. 8Core Architecture. 92.1Service Layers . 9Cloud Provider Services . 9Storage Layer . 9Compute Layer . 10Global Services Layer . 102.2 T-Shirt Sizing of Virtual Warehouses. 112.3Concurrent Threads . 11Snowflake Editions . 12Differences in Editions . 12VPS – for Serious Players . 132.4 Case-Sensitivity . 14Object Names . 14Double Quotes . 15Case-Related Comparisons . 15Need for Change . 152.5 Internal Storage in Micropartitions . 163Columnstores . 16Micropartitions . 16Security . 183.1Authentication . 18Password Policy . 18Mutli-Factor Authentication (MFA) . 19Integration/Federation . 19Managing Groups vs Users . 193.2 Role-Based Security . 20Object Ownership . 20Using Roles . 20One at a time . 21DDL commands look odd . 21Schema-Based Security . 21Transferring Ownership . 21Checking current grants. 223.3 Encryption . 234Data In Transit . 23Data At Rest . 24Annual Rekey . 24Business Critical . 24Tables and Programmable Objects. 25 2020 Dr Greg Lowhttp://sqldownunder.comPage 3
4.1Types of Tables . 25CREATE TABLE Variants. 25Table Durability . 264.2 Constraints . 27The Usual Situation . 27Foreign Keys in Data Warehouses . 27Primary and Foreign Keys in Snowflake . 284.3 Data Clustering in Tables . 29Clustering in Snowflake . 29Sometimes you need to cluster . 29Micropartition Pruning . 30A few other notes on Clustering . 304.4 Time Travel . 31SQL Server Temporal Tables . 31Snowflake and Time Travel . 32Query History and Query ID . 32Retention of History . 334.5 Programmable Objects . 345Functions . 34Stored Procedures . 35Triggers are Missing . 35Loading and Exporting Data . 365.1Stages . 365.2Types of Stages . 36File Formats . 38Named File Formats . 39Data Export . 395.3 GET, PUT, and SnowSQL for working with local files . 40SnowSQL . 40SnowSQL Commands . 415.4 Parallelism when loading data from files into tables . 426Breaking up large files . 42File Sizes . 43Recovery . 446.1Fail-Safe . 44What about backup? . 45 2020 Dr Greg Lowhttp://sqldownunder.comPage 4
11.1Data Warehouses as a ServiceWhy Snowflake?Image from Snowflake websiteA few months back, I started noticing that many of our clients had started to mention Snowflake.(www.snowflake.com)In recent years, I've been in lots of planning and architectural meetings where there was already apresumption that AWS was being used rather than Azure. Now while I’m a big fan of Azure, I put thatdown to a great selling job by the AWS people who got corporate IT folk locked into large enterpriseagreements early. And it means that no matter what the technical question is, the answer will besomething that runs on AWS.I still think that Azure offers a far stronger cloud story than AWS but I'm looking at the whole end-toe
If the first time you'd seen SQL Server was to see Azure SQL Database, you'd come away saying how amazing it is. But if you've come from the on-premises product, chances are that you might be already using something that isn't there. You might recall that Microsoft released a version of SQL Server Reporting Services in Azure, and later removed it. They learned that converting an application into a
