Advanced SQL. Implementing Modern Data Solutions and ML Applications Rui Machado, Hélder Russa, Pedro Esmeriz

Dostępność produktu

Produkt został zweryfikowany pod kątem ułatwień dostępu więcej przeczytasz w deklaracji dostępności.

Spis treści książki

• Preface
  • Conventions Used in This Book
  • Using Code Examples
  • OReilly Online Learning
  • How to Contact Us
  • Acknowledgments
    • Rui Machado
    • Hélder Russa
    • Pedro Esmeriz
• I. The Modern SQL
• 1. Evolution of SQL
  • Historical Overview: From Relational to Modern SQL
    • Standard SQL-86: Laying the Foundations
    • Standard SQL-89: Adding Integrity and Security
    • Standard SQL-92: Adding Modeling Capabilities
    • Standard SQL:1999Handling Industry Needs
    • From the SQL:2003 Standard to SQL:2008
    • From the SQL:2011 Standard to SQL:2016
    • The Graph Revolution with SQL:2023
  • Transition to Multipurpose SQL
    • Semi-Structured Data Handling in SQL
    • Temporal Data Management
    • Extended Analytical and OLAP Functions
    • Navigating Graph Structures in SQL
    • Dealing with Unstructured Data in SQL
    • Integrating ML and LLMs in SQL Platforms
    • SQL for Data Transformations
    • Embedded Analytics with DuckDB
  • Comparison of SQL Dialects over Time
  • Summary
• 2. Advanced SQL Techniques
  • Recursive Queries, Window Functions, and CTEs
    • Common Table Expressions and Subquery Factoring
    • Recursive CTEs for Hierarchical and Iterative Queries
    • Advanced Window Functions for Analytic Queries
  • Performance Optimization Strategies
    • Indexing and Efficient Data Access
    • Query Planning and Cost-Based Optimization
    • Query Rewriting, Caching, and Materialized Views
    • Avoiding Common SQL Anti-Patterns
      • Selecting all columns (SELECT *) unnecessarily
      • Not filtering data early
      • Ignoring indexes (or not having the right index)
      • Misusing DISTINCT or UNION
      • Wildcard searches without full-text indexing
      • RBAR (row-by-agonizing-row) processing
      • Not using prepared statements (when executing many similar queries)
      • Large transactions or improper batching
      • Ignoring query plan feedback
      • Neglecting to analyze/update statistics
    • Tuning in Cloud Data Warehouses: BigQuery Versus Snowflake
      • Concurrency and workload isolation
        • BigQuery concurrency and isolation best practices
        • Snowflake concurrency and isolation best practices
      • Cost-optimization strategies
        • BigQuery: Navigating cost-effective analysis
        • BigQuery cost optimization best practices
        • Snowflake: Architecting for cost-aware computation
        • Snowflake cost optimization best practices
      • Monitoring and profiling query performance
        • BigQuery: Monitoring and profiling queries
        • Snowflake: Monitoring and profiling queries
  • Summary
• 3. Architecting SQL-Driven Solutions
  • Foundational Design Principles for SQL Data Pipelines
    • Designing Modular SQL Data Pipelines
      • Modularity and composability
      • Data lineage and dependency management
      • Maintainability and readability
      • Idempotency and rerunnability
      • Automation and orchestration
      • Service-level agreements and data freshness
    • Formalizing Data Agreements: Data Contracts
  • Architectural Patterns for Data Pipelines
    • Batch Versus Streaming: Choosing the Right SQL Pipeline Approach
    • Transformation Patterns: ETL, ELT, and Streaming
    • Beyond ETL/ELT and Streaming: Zero-ETL and Data Sharing
    • Data Mesh and Data Products
  • Architectures for Scalability
    • Traditional RDBMS Scaling
    • Cloud Native Scaling
    • Concurrency and Isolation in Practice
    • Hybrid and Multi-Cloud Patterns
  • Building and Operationalizing Scalable SQL Data Platforms
    • Layered Architecture: Separating Storage, Transformation, and Orchestration
      • Data ingestion
      • Storage and processing
      • Transformation and modeling
        • Staged modeling layers
        • Transformation patterns
        • SQL as a modeling language
      • Orchestration and scheduling
      • Consumption layer
        • Best practices for the consumption layer
        • Connecting back to the architecture
    • From Design to Production: Scaling SQL Data Solutions
      • Supporting layers
        • Observability: Knowing whats working (and whats not)
        • Lineage and discovery: Seeing the forest and the trees
        • Security and access control: Guardrails, not roadblocks
      • Implementing testing strategies
      • Continuous integration and continuous delivery for SQL pipelines
      • Infrastructure as Code (IaC) in scaling and for data platforms
  • Avoiding Common Architectural Anti-Patterns
    • Tight Coupling Between Pipeline Components
    • Monolithic All-in-One SQL Scripts
    • Missing Lineage and Monitoring
    • Over-Coupling to Specific Technologies
    • Not Designing for Evolution
    • Skipping Tests for SQL Models
    • Ignoring Governance and Security
  • Summary
• II. SQL for Data Engineering and Data Science
• 4. Advanced Data Modeling with SQL
  • Traditional SQL Data Modeling Techniques
    • Third Normal FormStructured, Relational Modeling
    • Dimensional Modeling: Star Schema for Analytics
    • Snowflake Schemas: Adding Normalization to Dimensions
    • One Big Table: The Wide Denormalized Table Approach
    • Lakehouse Modeling and ELT Workflows
    • Summary of Traditional Models
  • Knowledge Graphs and Semantic Modeling: The New Frontier for SQL
    • What Is a Knowledge Graph?
    • Graph Technologies: Neo4j and Cypher
    • Querying with Cypher Versus SQL
  • Semantic Models and Ontologies
    • RDF and OWL: The Foundations of Semantic Modeling
    • Property Graphs and Cypher
    • PostgreSQL and GraphDB
  • SQL and Graphs: Complementary, Not Competing
  • Summary
• 5. Building Data Engineering Solutions with SQL
  • Pipeline Overview and Design Principles
    • Setting Up the GCP Environment
      • Create a GCP project
      • Enable required APIs
      • Create a cloud storage bucket
      • Set up BigLake (Iceberg) metastore
      • Create Pub/Sub topic and subscription
      • Create a BigQuery dataset and BigLake connection
  • Data Ingestion in a SQL-Centric Architecture
    • Streaming Ingestion with Spark Structured Streaming and a Flavor of Apache Flink SQL
      • Pub/Sub to GCS (landing zone)
      • GCS to Iceberg and Spark structured streaming (Bronze layer)
      • Why Spark in a SQL-centric architecture?
      • Apache Flink SQL
    • Batch Ingestion for Bulk Data
      • API to GCS (landing zone)
      • GCS to Iceberg with Spark batch processing (Bronze layer)
  • Storage Layer
    • The Data Lakehouse
      • Open table formats
    • Data Storage and Management with Apache Iceberg
      • Iceberg schema evolution
      • Auditing and recovery: Time travel with SQL
  • Transformation and Data Modeling with SQL
    • Silver and Gold Model Design in dbt: Building a Medallion Architecture
      • Staging with Silver models
      • Building facts and dimensions in the Gold layer
      • Tests and documentation
  • Orchestration and Workflow Management
    • Cloud Workflows Orchestration Pipeline
    • Scheduling the Workflows
    • Alternative Orchestration Options
  • Consume Layer
    • A Stable Interface for Data Consumption
    • Bridging Semantics to Execution
      • Physical models versus semantic models in practice
    • SQL-First Semantic Execution
      • Example: Semantic joins and inferred logic
      • The dbt Semantic layer (MetricFlow)
    • Semantic Contracts are Testable Contracts
    • Operationalizing the Semantic layer
    • Live Data in Consume Layer
  • Security and Governance Considerations
    • Identity and Execution Boundaries
    • Dataset and Schema Boundaries as Governance
    • Governance via dbt (Documentation, Lineage, and Exposures)
      • Lineage and documentation
      • Exposures and ownership
      • Testing and quality rules
    • Security Versus Semantics: Consistent Definitions as Control
  • Summary
• 6. SQL in Data Science
  • Data Processing and Analysis
    • Dataset Statistical Description
    • Correlation and Association
    • Regression from Aggregates
    • Outlier Detection
  • Advanced Statistical Analysis
    • Normality Testing
    • Confidence Intervals: Estimating the Mean
    • Two-Sample Tests
      • Two-sample t-test
      • MannWhitney U
    • Multiple Groups Testing
      • One-way ANOVA: A handmade analysis
      • KruskalWallis test
  • Machine Learning
    • Advanced Feature Engineering Patterns
      • Categorical encoding
        • One-hot encoding
        • Frequency encoding
        • Feature hashing
      • Text feature engineering
        • Tokenization
        • Character n-grams
      • Quantile binning
        • Equal-frequency (deciles)
        • Equal-width (deciles)
      • Target encoding (leakage-aware) with K-fold out-of-fold mean
      • Principal component analysis
    • Model Creation and Training
      • Supervised learning models
      • Unsupervised learning models
      • Anomaly detection
      • Recommendation systems
      • Time-series forecasting
      • Hyperparameter tuning
        • A compact example
        • Tuning objectives and multi-objective optimization
        • Best practices and cost considerations
    • Model Evaluation and Validation
      • Evaluation metrics
      • Cross-validation
      • Interpretation of results
    • Model Deployment and Monitoring
      • Making models available for predictions
      • Scheduling batch predictions
      • Model monitoring and retraining strategies
        • Skew
        • Drift
  • Summary
• III. SQL in Emerging Technologies
• 7. Generative AI in SQL
  • Generative AI Inside SQL
    • Core Patterns of SQL + GenAI
      • Inline LLM enrichment
      • Semantic search with embeddings
      • Retrieval-augmented generation (RAG)
      • Text-to-SQL
      • SQL as an agent tool
    • AI-Ready Data
    • When SQL Should Run GenAI Workloads
  • Primitives: LLMs, Embeddings, and Search
    • LLM Functions Inside SQL
      • Generating free-form text
      • Structured outputs
    • Embeddings, Search, and Retrieval
      • Embeddings as semantic join keys
      • Designing an embeddings table
      • Query-time semantic retrieval with vector search
      • Hybrid search (lexical + vector + reranking)
      • Multimodal embeddings
  • Patterns: RAG, Agents, and MCPs
    • Grounded Generation (the G in RAG)
    • SQL-Based Agents and MCPs
      • Building a SQL-generating agent
      • Adding guardrails
        • Read-only verification
        • Required filters in tool signatures
      • Enhancing accuracy with schema context
      • Handling failures gracefully
      • SQL agents via MCP
        • What is MCP?
        • Using postgres-mcp
        • Connecting an agent to MCP
        • When to use MCP versus custom tools
  • Productionizing Generative AI in the Data Warehouse
    • Logging, Monitoring, and Evaluating GenAI Outputs
      • Run lineage and versioned artifacts
      • Core run logs and daily observability
      • Evaluation and review loops
        • Building and evolving eval suites
    • Cost and Performance Engineering
      • Incremental refresh and embedding versioning
      • Cost controls and caching
    • Security and Privacy Controls
    • Practical Deployment Architectures
    • Production Readiness Checklist
  • Summary
• 8. Innovations in SQL Syntax and the Future of SQL
  • GoogleSQL Pipe Syntax
    • Core Syntax and Semantics of Pipe Queries
    • Pipe Operators Overview
      • The SELECT operator
      • The EXTEND operator
      • The SET operator
      • The DROP and RENAME operators
      • The AS operator
      • The WHERE operator
      • The LIMIT operator
      • The AGGREGATE operator
      • The JOIN operator
      • Combining results with UNION, INTERSECT, EXCEPT
    • Applied Pipe SQL by Examples
      • Filtering and selecting
      • Using an intermediate calculation
      • Aggregation and post-filtering
      • Multistep aggregation (eliminating subqueries)
      • Joining after a transformation
    • Real-World Use Cases and Applications of Pipe SQL
    • Best Practices and Style Guidelines for Pipe SQL Syntax
      • Formatting and readability tips
      • Efficiency considerations
      • When not to use pipe syntax
  • Parallel Innovations in SQL Expression
    • PRQL: A Pipelined Query Language
    • Malloy: A Semantic Modeling Language
  • The Future of SQL
    • AI and Autonomous Databases
    • The Quantum Horizon: Quantum-Assisted Optimization
    • Declarative Governance and Policies
  • Summary
• Index

pokaż cały spis treści

ukryj recenzje