Advanced SQL (Rui Pedro Machado etc.)（Z-Library）

【One-Line Pitch】 A practical field guide for data engineers, analysts, and analytics engineers who want to push SQL beyond basic queries into streaming, lakehouse, cloud warehouse, and ML pipeline territory—with hands-on architecture patterns and real-world production advice. 【Book Arc】 - **Opening (~0%–10%)**: The book opens by framing SQL as a full programming interface, not just a query language. It introduces temporal querying (time-travel queries in SQL Server, Snowflake, BigQuery) and window functions, showing how modern SQL engines extend beyond simple SELECT statements. - **Early (~10%–23%)**: Focus shifts to query optimization fundamentals—how cost-based optimizers decide between scans, indexes, and join strategies—and then to materialized views for pre-computing expensive aggregations. The narrative then moves up the stack to data pipeline design, introducing data contracts as a proactive governance mechanism and comparing Lambda vs. Kappa architectures. - **Early (~23%–32%)**: Architectural decision-making takes center stage: traditional RDBMS vs. cloud-native warehouses vs. lakehouses, with emphasis on decoupling storage from compute. The book then covers orchestration principles (model-based, dependency-driven, fail-fast) and a testing matrix (regression, data quality, security, load) before warning against common anti-patterns like tight coupling between pipeline components. - **Middle (~39%–48%)**: Data modeling gets a thorough treatment—comparing 3NF, star schemas, and One Big Tables (OBTs) with concrete trade-offs in integrity, performance, and storage. The book also introduces graph querying (Cypher) as an alternative to tabular joins for relationship-heavy questions, showing when SQL's model falls short. - **Late (~48%–end)**: A capstone scenario: building a complete SQL-centric data platform for a retail example (Customers, Products, Orders) using a Lambda-style architecture. The walkthrough covers cloud infrastructure setup (BigQuery, Iceberg tables, Pub/Sub streaming) and authentication, demonstrating how SQL integrates with modern data engineering tooling end-to-end. 【Key Takeaways】 - **Temporal queries turn databases into time machines** (Early): Using `FOR SYSTEM_TIME AS OF` or cloud-native time-travel features lets you query historical states without manual history tables—critical for audits, debugging bad loads, and compliance. (Early) - **Window functions are the Swiss Army knife of analytical SQL** (Early): Beyond ranking, value window functions (e.g., fetching the second-highest salary per department) let you express ordered analytics cleanly without self-joins or subqueries. (Early) - **Query tuning is about understanding the optimizer's reasoning** (Early): The planner weighs statistics (row counts, selectivity) to choose between sequential scans, index scans, and hash joins—so effective tuning means predicting and influencing those cost-based decisions, not memorizing syntax. (Early) - **Materialized views can turn million-row scans into hundred-row reads** (Early): Pre-computing aggregations like sales by region/month delivers massive performance gains; some warehouses (BigQuery, Snowflake) even rewrite queries transparently to use them. (Early) - **Data contracts shift quality from reactive to proactive** (Early): Codifying schema, permissions, and versioning policies in SQL DDL means producers and consumers share a formal language—breaking changes require negotiation, not silent dashboard failures. (Early) - **Architecture choice is a scalability and resilience decision** (Early): Traditional RDBMS suits small transactional workloads; cloud warehouses/lakehouses decouple storage from compute for elastic scaling; hybrid and multi-cloud patterns bridge legacy investments with modern capabilities. (Early) - **Orchestration should follow your model structure, not rigid schedules** (Early): Let data dependencies drive execution (e.g., staging → intermediate → curated), minimize hardcoding, fail fast with clear logs, and keep orchestration logic separate from transformation logic. (Early) - **Star schemas and OBTs are trade-offs, not dogma** (Middle): 3NF maximizes integrity but complicates queries; star schemas simplify analytical joins with some redundancy; OBTs optimize for speed but cost more storage—choose based on your workload, and modern lakehouses support mixing all three. (Middle) 【Reading Tips】 - **Skim the opening chapters if you're already comfortable with window functions and temporal queries**—the real value starts around the pipeline design and data contract sections (~20% in). - **Deep-read the optimizer section (~10%–13%)** if you've ever wondered why a query is slow; the PostgreSQL join example is a clear mental model for how planners think, and it transfers to other engines. - **Pay close attention to the anti-patterns section (~32%)**—tight coupling between pipeline components is the most common real-world failure, and the examples are immediately recognizable. - **Use the data modeling comparison (~39%–42%) as a decision framework**—the retail scenario (3NF vs. star vs. OBT) is a practical template for evaluating your own schemas. - **The final scenario (~48% onward) is best read as a reference, not a tutorial to follow line-by-line**—the GCP-specific setup (BigQuery, Iceberg, Pub/Sub) is useful for context, but the architectural principles apply broadly. 【Coverage Limits】 The excerpts do not cover ML pipeline specifics, advanced streaming SQL (e.g., Flink SQL syntax), or detailed security testing procedures—these are mentioned but not deeply explored in the available material.