book

Advanced SQL

by Rui Machado, Helder Russa, Pedro Esmeriz

July 2026

Intermediate to advanced

412 pages

12h 24m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Includes

Quizzes

Preface
Conventions Used in This BookUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgmentsRui MachadoHélder RussaPedro Esmeriz
I. The Modern SQL
1. Evolution of SQL
Historical Overview: From Relational to Modern SQLStandard SQL-86: Laying the FoundationsStandard SQL-89: Adding Integrity and SecurityStandard SQL-92: Adding Modeling CapabilitiesStandard SQL:1999—Handling Industry NeedsFrom the SQL:2003 Standard to SQL:2008From the SQL:2011 Standard to SQL:2016The Graph Revolution with SQL:2023Transition to Multipurpose SQLSemi-Structured Data Handling in SQLTemporal Data ManagementExtended Analytical and OLAP FunctionsNavigating Graph Structures in SQLDealing with Unstructured Data in SQLIntegrating ML and LLMs in SQL PlatformsSQL for Data TransformationsEmbedded Analytics with DuckDBComparison of SQL Dialects over TimeSummary
2. Advanced SQL Techniques
Recursive Queries, Window Functions, and CTEsCommon Table Expressions and Subquery FactoringRecursive CTEs for Hierarchical and Iterative QueriesAdvanced Window Functions for Analytic QueriesPerformance Optimization StrategiesIndexing and Efficient Data AccessQuery Planning and Cost-Based OptimizationQuery Rewriting, Caching, and Materialized ViewsAvoiding Common SQL Anti-PatternsTuning in Cloud Data Warehouses: BigQuery Versus SnowflakeSummary
3. Architecting SQL-Driven Solutions
Foundational Design Principles for SQL Data PipelinesDesigning Modular SQL Data PipelinesFormalizing Data Agreements: Data ContractsArchitectural Patterns for Data PipelinesBatch Versus Streaming: Choosing the Right SQL Pipeline ApproachTransformation Patterns: ETL, ELT, and StreamingBeyond ETL/ELT and Streaming: Zero-ETL and Data SharingData Mesh and Data ProductsArchitectures for ScalabilityTraditional RDBMS ScalingCloud Native ScalingConcurrency and Isolation in PracticeHybrid and Multi-Cloud PatternsBuilding and Operationalizing Scalable SQL Data PlatformsLayered Architecture: Separating Storage, Transformation, and OrchestrationFrom Design to Production: Scaling SQL Data SolutionsAvoiding Common Architectural Anti-PatternsTight Coupling Between Pipeline ComponentsMonolithic “All-in-One” SQL ScriptsMissing Lineage and MonitoringOver-Coupling to Specific TechnologiesNot Designing for EvolutionSkipping Tests for SQL ModelsIgnoring Governance and SecuritySummary
II. SQL for Data Engineering and Data Science
4. Advanced Data Modeling with SQL
Traditional SQL Data Modeling TechniquesThird Normal Form—Structured, Relational ModelingDimensional Modeling: Star Schema for AnalyticsSnowflake Schemas: Adding Normalization to DimensionsOne Big Table: The Wide Denormalized Table ApproachLakehouse Modeling and ELT WorkflowsSummary of Traditional ModelsKnowledge Graphs and Semantic Modeling: The New Frontier for SQLWhat Is a Knowledge Graph?Graph Technologies: Neo4j and CypherQuerying with Cypher Versus SQLSemantic Models and OntologiesRDF and OWL: The Foundations of Semantic ModelingProperty Graphs and CypherPostgreSQL and GraphDBSQL and Graphs: Complementary, Not CompetingSummary
5. Building Data Engineering Solutions with SQL
Pipeline Overview and Design PrinciplesSetting Up the GCP EnvironmentData Ingestion in a SQL-Centric ArchitectureStreaming Ingestion with Spark Structured Streaming and a Flavor of Apache Flink SQLBatch Ingestion for Bulk DataStorage LayerThe Data LakehouseData Storage and Management with Apache IcebergTransformation and Data Modeling with SQLSilver and Gold Model Design in dbt: Building a Medallion ArchitectureOrchestration and Workflow ManagementCloud Workflows Orchestration PipelineScheduling the WorkflowsAlternative Orchestration OptionsConsume LayerA Stable Interface for Data ConsumptionBridging Semantics to ExecutionSQL-First Semantic ExecutionSemantic Contracts are Testable ContractsOperationalizing the Semantic layerLive Data in Consume LayerSecurity and Governance ConsiderationsIdentity and Execution BoundariesDataset and Schema Boundaries as GovernanceGovernance via dbt (Documentation, Lineage, and Exposures)Security Versus Semantics: Consistent Definitions as ControlSummary
6. SQL in Data Science
Data Processing and AnalysisDataset Statistical DescriptionCorrelation and AssociationRegression from AggregatesOutlier DetectionAdvanced Statistical AnalysisNormality TestingConfidence Intervals: Estimating the MeanTwo-Sample TestsMultiple Groups TestingMachine LearningAdvanced Feature Engineering PatternsModel Creation and TrainingModel Evaluation and ValidationModel Deployment and MonitoringSummary
III. SQL in Emerging Technologies

7. Generative AI in SQL
Generative AI Inside SQLCore Patterns of SQL + GenAIAI-Ready DataWhen SQL Should Run GenAI WorkloadsPrimitives: LLMs, Embeddings, and SearchLLM Functions Inside SQLEmbeddings, Search, and RetrievalPatterns: RAG, Agents, and MCPsGrounded Generation (the “G” in RAG)SQL-Based Agents and MCPsProductionizing Generative AI in the Data WarehouseLogging, Monitoring, and Evaluating GenAI OutputsCost and Performance EngineeringSecurity and Privacy ControlsPractical Deployment ArchitecturesProduction Readiness ChecklistSummary
8. Innovations in SQL Syntax and the Future of SQL
GoogleSQL Pipe SyntaxCore Syntax and Semantics of Pipe QueriesPipe Operators OverviewApplied Pipe SQL by ExamplesReal-World Use Cases and Applications of Pipe SQLBest Practices and Style Guidelines for Pipe SQL SyntaxParallel Innovations in SQL ExpressionPRQL: A Pipelined Query LanguageMalloy: A Semantic Modeling LanguageThe Future of SQLAI and Autonomous DatabasesThe Quantum Horizon: Quantum-Assisted OptimizationDeclarative Governance and PoliciesSummary
Index
About the Authors

Content preview from Advanced SQL

About the Authors

Rui Machado is a cofounder and CTO of Maio Labs, a company focused on building pragmatic AI solutions that empower companies to own their intelligence and use it for competitive advantage.

His background is in information technologies and data science, and he has over a decade of relevant experience in the architecture and implementation of data warehouses, data lakes, and decision support systems in industries such as retail, ecommerce, supply chain, health care, and social networks.

He was formerly a VP of engineering at H&M Group and previously led engineering and analytics teams at Jumia, Nike, and Meta. He is a coauthor of Analytics Engineering with SQL and dbt (O’Reilly).

LinkedIn profile: https://www.linkedin.com/in/rpmachado

Hélder Russa is a director of data engineering at H&M Group, leading the sales and global areas. In this role, he is responsible for developing and optimizing the entire data ecosystem, empowering data science, machine learning, and business intelligence teams with scalable, efficient solutions.

He also recently coauthored Analytics Engineering with SQL and dbt (O’Reilly), a book aimed at demystifying the role of analytics engineers in modern organizations. Through practical insights, the book explores how SQL and dbt can be leveraged to structure, transform, and operationalize data workflows efficiently.

Passionate about data-driven innovation and knowledge sharing, Hélder continues to contribute to the advancement of data engineering, ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9798341627475Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Advanced SQL

by Rui Machado, Helder Russa, Pedro Esmeriz

About the Authors

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.