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