Analytical Data Processing in SQL

Preface How to ask questions Acknowledgments Prerequisites Lab setup The data model used in this book SQL & OLAP Basics Understand your data; it’s the foundation for data processing Relate the data model to the business by understanding facts and dimensions Modifying granularity (aka roll up/group by) is the basis of analytical data processing Understanding what the data represents and the pipeline that generates the data helps you answer most business questions Save time and money by storing data efficiently and reducing data movement (while querying) OLAP DB stores large datasets as chunks of data and processes them in parallel Reduce data movement (data shuffle) by understanding narrow and wide transformations Hash joins are expensive, but Broadcast joins are not Examine how your OLAP DB will process your query, then optimize it Reduce the amount of data to be processed with column-oriented formatting, partitioning, and bucketing Calculate aggregate metrics but keep all the rows, rank rows, and compare values across rows with window functions Window = A set of rows identified by values present in one or more column(s) A window definition has a function, partition (column(s) to identify a window), and order (order of rows within the window) Calculate running metrics with Window aggregate functions Rank rows based on column(s) with Window ranking functions Compare column values across rows with Window value functions Choose rows to apply functions to within a window frame using ROWS, RANGE, and GROUPS Use query plan to decide to use window function when performance matters Write easy-to-understand SQL with CTEs and answer common business questions with sample templates Use CTEs to write easy-to-understand queries and prevent re-processing of data Templates for Deduping, Pivots, Period-over-period (DoD, MoM, YoY) calculations, and GROUPing BY multiple column combinations in one query Appendix: SQL Basics The hierarchy of data organization is a database, schema, table, and columns Use SELECT…FROM, LIMIT, WHERE, & ORDER BY to read the required data from tables Combine data from multiple tables using JOINs (there are different types of JOINs) Generate metrics for your dimension(s) using GROUP BY Use the result of a query within a query using sub-queries Change data types (CAST) and handle NULLS (COALESCE) Replicate IF.ELSE logic with CASE statements Stack tables on top of each other with UNION and UNION ALL, subtract tables with EXCEPT Save queries as views for more straightforward reads Use these standard inbuilt DB functions for common String, Time, and Numeric data manipulation Create a table, insert data, delete data, and drop the table