How-To Tutorials - Data Science

DataPro is a weekly, expert-curated newsletter trusted by 120k+ global data professionals. Built by data practitioners, it blends first-hand industry experience with practical insights and peer-driven learning.Make sure to subscribe here so you never miss a key update in the data world. IntroductionModern enterprises are no longer competing on data alone; they’re competing on how quickly and intelligently they can act on it. As AI systems evolve from passive analytics to autonomous decision-makers, traditional data architectures are becoming a critical bottleneck. The rise of systems of action marks a fundamental shift: from storing and analyzing the past to driving real-time decisions, workflows, and outcomes.This article explores how organizations can modernize their data foundations to support agentic AI, real-time context, and scalable intelligence. It breaks down the architectural principles required to unify fragmented data, ensure quality and trust, and enable continuous learning systems that operate at enterprise scale. From unified data access to real-time signal processing and governance, this is a practical guide to building the AI-ready backbone that powers next-generation applications.Building an AI-ready data foundationDelivering on the promise of systems of action requires a new kind of data foundation—one built for speed, context, and adaptability.Agentic AI systems fundamentally differ from traditional systems of record in their operational demands. Where legacy systems focus on capturing and storing historical transactions, systems of action powered by agentic AI require real-time decision-making, dynamic data synthesis, and immediate response capabilities. This shift demands that our data architecture choices move beyond the rigid, siloed structures of traditional enterprise systems.A unified view of core enterprise is essential. It must bring together the diverse data types that autonomous agents rely on (real-time operational signals, contextual documents, vector embeddings) into a single, coherent platform. That platform must be built on flexible data structures that can adapt as agent behaviors evolve.The transition from supporting passive systems of record to enabling active systems of action introduces six critical architectural requirements that distinguish agentic AI infrastructure from legacy approaches:Unified data access to eliminate the complexity of managing multiple disparate datastoresData quality and consistency mechanisms that reduce hallucinations and errors from systems out of syncReal-time context capabilities that enable immediate signal processing for RAG applicationsScalability and performance characteristics that support operational AI rather than only backward-looking analyticsGovernance and security frameworks that protect sensitive information while enabling innovationEfficient model training workflows that optimize data preparation for GenAI applicationsTogether, these elements form the data foundation for autonomous, intelligent systems. As we examine each in the sections ahead, we’ll see how a system of action database departs from traditional data management and enables more intelligent, responsive, and scalable AI applications.What is a system of action?Systems of action are a new class of enterprise application, designed to execute decisions and drive workflows in real time. They enable collaboration between people, AI-assisted users, and AI agents, supporting everything from assisted decision-making to fully autonomous execution.Unlike systems of record, which passively store historical transactions, or systems of insight, which analyze data retrospectively, systems of action operate in the moment. They process dynamic context, trigger decisions, and execute tasks through AI agents. For instance, they might reroute a delayed flight in real time or automatically adjust hospital staffing during a sudden surge.Building systems of action requires more than analytical capabilities. They must ingest streaming signals, reason across unstructured and structured sources, and respond in real time. They require specialized database architectures capable of managing high-velocity, multimodal data streams and supporting complex state transitions over time. Most legacy systems, designed for static, batch-oriented workflows, simply cannot support this kind of continuous intelligence.Figure 3.1: Enterprise system landscape: from system of record to system of actionFigure 3.1 illustrates this evolution across the enterprise landscape. Unlike traditional systems that passively store or retrospectively analyze data, systems of action enable real-time interaction between users, applications, and agents; all powered by a live, adaptable data layer.Unified data access architectureThe foundation of any GenAI system begins with access to diverse, multimodal data, at speed, in formats AI can reason with. Unfortunately, this is also where most enterprises struggle. Traditional enterprise data architectures are fragmented across dozens of incompatible systems, each optimized for narrow use cases. The result is integration pain, access friction, and massive overhead.Modern AI applications demand a fundamental departure: unified access must be treated not as a convenience but as a prerequisite.Today’s models must navigate a wide variety of inputs: text documents, application logs, product catalogs, support transcripts, and streaming sensor data. Relational and legacy systems often store semi-structured data (like JSON or XML) as binary large objects (BLOBs) or character large objects (CLOBs), limiting their usability for AI systems. In these cases, the actual data is hidden inside a single entry and must be extracted and interpreted before it can be reasoned over or acted upon. This was tolerable when the goal was to store and retrieve files. But for GenAI systems, where models need immediate access to both structured and semi-structured data, often in the same query, this format becomes a bottleneck. Even a video can have its own addressable metadata structure, rather than existing solely as an opaque BLOB, illustrates the shift needed to support AI-native reasoning.Beyond the format problem lies a more urgent challenge: fragmentation.An AI application might need to stitch together context from a CRM (customer profiles and account hierarchies), a product catalog (SKU-level details, pricing, availability), a data warehouse (historical transactions), a streaming platform (real-time behavioral signals), and a document store (contracts, support transcripts, policy documents). Each source has its own schema, access pattern, and often its own API. This complexity creates two persistent challenges:Developer integration friction: Each layer introduces its own headaches, from authentication and authorization to schema mismatches, brittle connectors, and inconsistent formatsSystem fragility/maintenance drag: Over time, these integrations accumulate, introducing silent failures, versioning issues, and downstream reliability risks that make innovation slower and more expensiveMongoDB’s document model takes a fundamentally different approach. Instead of forcing diverse data into rigid schemas or hiding it in unreadable blobs, it enables rich, hierarchical data structures that mirror how businesses actually operate [1]. Developers can model a full customer, order, or event in a single document, including nested context, version history, and behavioral attributes. This eliminates the need for complex joins while preserving the relationships critical for effective agentic reasoning.Even more critically, flexible schema design, meaning the ability to store and query data without locking into a rigid blueprint, allows fields and document shapes to adapt as requirements change. This lets data evolve—new attributes can be added without downtime, and new types of signals can be integrated without costly migrations. For AI systems (especially those that learn, adapt, and extend themselves), this agility is essential.This architectural convergence enables structured transactions, real-time signals, and unstructured content together in a single query or operation. Model updates, enrichment jobs, or downstream agent actions can all be triggered directly from the same data platform [2]. That unified model lays the groundwork for sophisticated, AI-native workflows.Perhaps more importantly, unified data access transforms developer productivity. Instead of spending cycles reconciling formats or debugging brittle connectors, teams can focus on building intelligent systems. And, as we’ll see in the sections ahead, everything from data quality and governance to real-time orchestration builds on this foundation.Ensuring data quality and consistencyData quality and consistency are non-negotiable for GenAI solutions. Unlike traditional analytics, where data quality issues might simply yield incorrect reports or delayed insights, poor data quality in AI systems can cause hallucinations, introduce biased outputs, and fundamentally unreliable behavior that undermines user trust and business value.Legacy quality approaches tried to solve this through normalization, deduplication, and validation against external sources. Consider a familiar failure mode: a system validates Joe Miller, 12 High Street, through postal APIs and credit checks, yet fails to distinguish between three different JoeMillers (grandfather, father, and son) at the same address. For entity analytics, where precise relationship mapping matters, this is a critical flaw.In this scenario, an online store might unknowingly treat all three individuals as the same customer, losing the ability to tailor interactions or offers. Relational star schemas exacerbate this problem by fragmenting contextual information across multiple tables. When customer data is split between fact tables, dimension tables, and lookup tables, the rich context that enables accurate entity resolution becomes scattered and difficult to reconstruct.In our Joe Miller example, a document-based approach would maintain separate documents for each individual, complete with detailed demographic information, purchase history, behavioral patterns, and relationship data that enables clear differentiation.Within a document, you can store original values alongside enrichments and enhancements within the same dataset. This approach improves output reliability and reduces hallucinations or contradictory results. When an AI system generates an output, the complete chain of data sources, transformations, and reasoning steps can be traced back through the document structure, enabling both debugging and compliance reporting.This lineage capability proves essential for improving output reliability and reducing hallucinations or contradictory results. When AI models can access not just the current state of data but also its provenance and transformation history, they can make more informed decisions about data reliability and confidence levels. For example, customer service AI might weigh recent direct customer interactions more heavily than older inferred preferences, or flag potential inconsistencies when multiple data sources provide conflicting information.For organizations implementing document-based data quality strategies, MongoDB offers comprehensive best practices, as well as compatibility with industry-leading tooling for data modeling and cataloging that make advanced quality management achievable at scale [3]. When high-quality, lineage-aware data becomes the default, AI systems can deliver results that are accurate, explainable, and trustworthy.Real-time context and RAGThe definition of real-time varies significantly by use case and industry, but the real-time requirements of data in use with GenAI cannot be overstated. Hedge fund trading systems, for example, require millisecond responses, while life insurance underwriting processes measure time in days. While application response times continue to decrease, many architectures use caching layers that create an illusion of real-time performance at the expense of freshness of data.A typical real-time environment follows a simple pattern where an interaction generates a signal that enables immediate interpretation. These signals may originate from diverse sources, such as a retail website recording shopping cart additions, a smart meter transmitting electricity usage, or a pathology lab completing cancer analysis data. All signals, when combined with existing datasets, enable text search, vector search, and LLM processing for reasoning and causal analysis. This applies equally to interactive systems, such as retail shopping carts, and autonomous agentic systems, such as automated insurance claim processing.Real-time integration of signals with metadata, reference data, and historical information generates new knowledge instantaneously. Consider how this has evolved. Traditional rulebased systems might suggest "You ordered a burger, would you like fries?" In contrast, an AI-powered system recognizes patterns such as "You order cat food bi-weekly, always the same brand", and reasons contextually with suggestions such as "Based on your purchase history, you might be interested in our new, healthier formula. Would you like us tosend you a free sample?" The system identifies repeat customers and enhances their experience through reasoning that connects purchase patterns with product recommendations, requiring deeper knowledge about customer preferences and pet characteristics.Figure 3.2: Real-time AI data flowThe architectural flow in Figure 3.2 demonstrates how modern AI applications process realtime signals through a system of action database using an airline passenger assistance scenario. The flow begins with diverse signal sources on the left: Passenger Check-In, Booking Systems,Weather Services, and Flight Status Updates, which feed into Signal Processing and FormatConversion/Real-Time Capture components. These signals are then ingested into the central MongoDB Document Store, which contains Flight Documents, Passenger Vectors, Booking Metadata, and Historical Patterns with Direct Vector Access capabilities.The system processes this data through Atlas Vector Search (finding similar flight disruptions) and LLM Augmentation (generating personalized responses with flight context) to produce three types of intelligent outputs: Re-Booking Confirmations, Personalized Options, and Automated Responses. At the foundation sits the Operational Data Layer (ODL), an architectural pattern that centrally integrates and organizes siloed enterprise data, serving as an intermediary between existing data sources and consuming applications. In this case, the ODL enriches signals with contextual information from passenger records, alternative flights, weather data, and rebooking history.A continuous learning and enrichment feedback loop ensures that every interaction outcome, whether accepted re-bookings or user preferences, flows back to improve future recommendations. The document model enables continuous enhancement without requiring system restructuring, creating a system that grows smarter with each passenger interaction while delivering real-time, context-aware responses vital for modern AI applications.Critically, the feedback loop ensures continuous improvement, ensuring every interaction outcome enriches the system of action database, making future responses more accurate and contextual. This circular flow embodies the key advantage of document-based architectures: the ability to evolve and improve without the schema rigidity that constrains traditional relational systems. The result is a system that grows smarter with each interaction, delivering real-time, context-aware responses that modern AI applications require.Scalability, availability, and performanceHistorically, enterprise data warehouses represented the largest database implementations, with denormalized, column-oriented star schemas designed for analytical queries. These systems perform well with queries such as "Display yogurt sales by region", where large datasets are filtered by specific criteria (region, store, price) to generate insights. The integration of multiple sources led to the development of extract, transform, load (ETL) processes and master data management systems. While these platforms have added machine learning features and now claim to support GenAI capabilities, they remain primarily designed for backward-looking analytical tools, unsuited to real-time, agentic, and causal AI applications.Consider the contrast. A chatbot assisting an airline passenger who missed a connection requires fundamentally different capabilities than answering "How many passengers experienced day-long delays in Frankfurt last year?" The chatbot and its underlying agentic system must address immediate needs, finding available seats, offering mitigation services, and responding empathetically to frustrated passengers. The required data is real-time, context-sensitive, and simply not available from a historic warehouse.To be successful in the request for the passenger, the system needs both real-time seat information access (easy to achieve with an API to the usual booking systems), as well as more important detailed context and information about the passenger and their situation. Is it a family stranded, or a single adult? What other ticket dependencies exist? Can the passenger be rerouted via a different track, or is the best option to stay overnight?This scenario demands that all passenger data reside in an up-to-date system of action database, as real-time interactions fail without current information. As these systems achieve global coverage, non-functional requirements mandate not only 24/7/365 availability but also the ability to handle transaction volume fluctuations from quiet periods to peak travel seasons such as Thanksgiving. Even minimal outages become unacceptable, and caching solutions that simply solve a data availability challenge compromise on data accuracy by introducing data staleness issues.Document-based architectures, such as those provided by MongoDB, offer advantages in specific scenarios for this type of data availability and scalability. Rather than requiring complex joins across multiple tables to reconstruct user context, document models can store complete contextual information in a single, efficiently retrievable record. This approach reduces the computational overhead of context reconstruction while enabling more sophisticated caching and optimization strategies.The performance characteristics of AI workloads also differ significantly from traditional analytical patterns. While analytical queries typically process large volumes of data to generate aggregate results, AI applications often require rapid access to specific, contextually relevant information. This pattern favors architectures optimized for high-concurrency, low-latency access to individual records, rather than bulk processing of large datasets.Governance, security, and complianceGovernance and compliance requirements stem from a fundamental need to protect individuals from flawed decision-making in systems that lack adequate self-regulation. These safeguards exist to prevent real harms, from biased loan approvals to unsafe product recommendations.GenAI faces intense scrutiny regarding accuracy, with media coverage of hallucinations bringing this concern to the forefront. Therefore, transparency in data lineage, reasoning processes, and result interpretation becomes critical for any GenAI solution. The document model in a system of action database enables tracking of all changes, transformations, and actions related to specific datasets. Unlike legacy relational databases, documents offer the flexibility for enhancement and enrichment throughout the process without requiring upfront planning.From a governance perspective, this enables precise and comprehensive tracking of communication and decision-making processes. It facilitates decision auditing and corrective actions when compliance challenges arise, often due to gradual shifts in decision criteria requiring adjustment.Security represents an additional critical dimension. MongoDB’s Queryable Encryption keeps data absolutely protected from unauthorized access. While passenger data may have moderate sensitivity, healthcare provider consultations about potential illnesses require the highest security levels. The system of action database enables transparent security implementation, significantly more challenging when coordinating multiple data sources with potentially incompatible security and policy systems [4].Model training and fine-tuningTraining or fine-tuning models requires large volumes of clean, labeled, and diverse data. The system of action database ensures efficient data curation, sampling, and preprocessing for training pipelines. Data enrichment becomes key, as features such as MongoDB’s aggregation pipeline enable data annotation and continuous analysis of criteria such as minimum or maximum values and moving averages to validate reasoning processes.The subject of data preparation for GenAI is often misunderstood, stemming from the evolution of early AI solutions supporting ML systems (systems that were derived from business intelligence (BI) architectures). This sometimes leads to the mistaken assumption that all data for AI usage and interaction must first be prepared, or readied, in lakes, warehouses, or marts, requiring extensive transformation and data pipeline processing. The resulting data objects are often stored as star schemas with fact tables, each containing hundreds of columns and accompanying dimension tables. Star schemas, a data modeling format originally designed to solve the problem of performant analytics queries executed against relational database objects, introduce the need for complex queries and join operations to extract insight, an architecture still employed by platforms such as Snowflake.Apache Spark object-storage implementations, such as Databricks, offer more complex query capabilities through distributed computing frameworks and in-memory processing, representing a significant advancement over traditional batch processing systems. Both approaches, star schemas and Spark-manipulated object storage files, share a foundation in backward-looking data warehousing, regardless of contemporary terminology such as data lake or lakehouse.These systems are optimized for processing large volumes of homogeneous data aligned along dimensional axes. Real-time access to individual datasets for operational processing falls outside their design parameters. Historically, this was the realm of online transaction processing (OLTP) systems. While transactional logging isn’t central to GenAI data structures, the access patterns remain similar.Often, the example of building models for embeddings is referenced as justification for why the data warehouse must be the source of data for GenAI, but this is misleading. Firstly, many business solutions successfully deploy standard embedding models for PDFs, images, and audio, without the need for custom development. Secondly, and more importantly, the comparison doesn’t hold, as warehouses analyzing quarterly sales have no relevance to point-of-sale operations and transaction booking.ConclusionTo unlock the full potential of AI, enterprises must rethink their data architecture from the ground up. Systems of action represent this new paradigm, where data is not just stored or analyzed, but continuously activated to drive intelligent decisions in real time. Achieving this requires six foundational capabilities: unified data access, high data quality and consistency, real-time context integration, scalable performance, robust governance and security, and efficient model training pipelines.By adopting flexible, document-based architectures and eliminating data fragmentation, organizations can build systems that are not only faster and more responsive but also more trustworthy and adaptable. The result is a living data ecosystem, one that evolves with every interaction, improves decision accuracy, and enables truly autonomous, AI-driven operations.This article is an excerpt from the book Architectures for the Intelligent AI-Ready Enterprise. To explore these concepts in greater depth and learn how to implement them in real-world enterprise environments, readers can explore the full book here: Author BioBoris Bialek has worked in the IT industry since the 1990s and was one of the initial drivers of Linux in Europe, delivering the first SAP port to Linux, conducting the first benchmarks, and securing the first clients. Since then, he has led product and development teams across IBM and FIS, driving innovation for both the end product and development productivity. Boris Bialek joined MongoDB in 2019, igniting a focus on industry solutions based on MongoDB's document model. Promoted to global field CTO and VP of industries, he drives technical design. He works directly with numerous clients, helping them gain the benefits of the MongoDB Atlas data platform. Boris holds a master's in computer science from the Karlsruhe Institute of Technology.Sebastian Rojas Arbulu is an industry solutions specialist at MongoDB, where he collaborates with numerous stakeholders across diverse industries to help customers realize the transformative value of MongoDB through tailored, data-driven solutions, particularly for AI integration. Sebastian also leads his team's content strategy, including numerous additions such as blogs, white papers, magazines, and other thought leadership pieces. With a background in IT consulting, marketing, and digital transformation, among other areas, he has extensive experience in identifying customer needs and developing innovative solutions that prepare data for intelligent applications and unlock new possibilities. He holds a bachelor of business administration degree.Taylor Hedgecock is a strategic program leader and transformation partner who turns vision into velocity. With a career spanning startups to multinationals, she brings a mix of operational rigor, narrative clarity, and cross-functional orchestration. At MongoDB, she has led high-impact programs across AI, partner ecosystems, and services modernization, often serving as the connective tissue between vision and execution. Her work has guided C-level priorities, enabled go-to-market readiness, and driven large-scale change, establishing her as a trusted leader in aligning stakeholders, translating strategy into story, and driving outcomes that last. Taylor currently serves as senior program manager on the industry solutions team, partnering with ISVs and AI innovators to bring next-generation solutions to market. Previously, she was chief of staff for professional services leadership, where she helped launch new offerings and guided modernization strategy, shaping MongoDB's vision for applying AI to its hardest problems.

DataPro is a weekly, expert-curated newsletter trusted by 120k+ global data professionals. Built by data practitioners, it blends first-hand industry experience with practical insights and peer-driven learning.Make sure to subscribe here so you never miss a key update in the data world. IntroductionLarge Language Models (LLMs) have transformed the landscape of artificial intelligence, redefining how machines understand and generate human language. From early statistical methods to the breakthrough of Transformer architectures, LLMs such as GPT, BERT, and T5 have unlocked unprecedented capabilities in natural language processing (NLP). As organizations increasingly rely on both structured and unstructured data, a powerful new paradigm is emerging: the integration of LLMs with Graph Machine Learning (GraphML). This combination enables systems to leverage both deep contextual language understanding and rich relational data, paving the way for more accurate, scalable, and intelligent AI applications across domains like search, recommendation systems, and knowledge graphs.LLMs: an overviewIn the rapidly evolving field of artificial intelligence, LLMs have significantly advanced natural language processing (NLP) and understanding. These models, characterized by their extensive number of parameters and trained on large datasets, have demonstrated remarkable capabilities across a wide set of language-related tasks.The journey of language models began with statistical approaches that relied on probabilistic methods to predict word sequences. These early models, while creating the foundations, were limited by their reliance on fixed-size context windows and the inability to capture long-range dependencies. However, as we also discussed in Chapter 4, Unsupervised Graph Learning, with the advent of neural networks, the field has undergone a significant shift, introducing models capable of learning word embeddings. In order to improve the ability to capture long-range dependencies, the initial neural network models were based on the Long-Short Term Memory(LSTM) and Gate-Recurrent Unit (GRU) architecture, which are forms of Recurrent Neural Networks (RNNs). However, a pivotal moment occurred with the introduction of the Transformer architecture by Vaswani et al. in 2017. Unlike its predecessors, the Transformer model utilized self-attention mechanisms, enabling it to consider the entire context of a sentence without the sequential constraints inherent in RNNs. This innovation facilitated the development of models capable of processing and generating text in a more coherent and fluent way.Building upon the Transformer architecture, researchers scaled models to unprecedented sizes, leading to the emergence of LLMs such as OpenAI’s GPT series, Google’s BERT and T5, and more recently, models such as GPT-3 and GPT-4.In a nutshell, training LLMs involves optimizing a large number of parameters on very large datasets. This process, known as pretraining, typically employs unsupervised learning objectives, such as predicting missing words in a sentence (masked language modeling) or forecasting subsequent words (causal language modeling). As a side effect, the pre-training phase lets the model learn and “understand” a language, resulting in a remarkable ability to generalize across various tasks, often achieving state-of-the-art performance. LLMs have demonstrated proficiency in a diverse array of applications, reflecting their versatility and depth of language understanding. Key areas include text generation, language translation, question answering, and summarization, among many others.Given the strengths of LLMs in unstructured text processing and generative tasks, an exciting frontier emerges when we consider their integration with graphs. While LLMs excel in understanding and generating natural language, graphs are particularly powerful for representing and analyzing structured relationships between entities. In the rest of the book, wewill see examples of how we can take advantage of both.Why combine GraphML with LLMs?As we have learned throughout this book, GraphML excels at representing and analyzing structured data such as knowledge graphs, social networks, chemical structures, and so on. It is extremely useful for situations where exploiting relationships between entities is crucial for achieving good performances. However, LLMs are particularly good at interpreting unstructured text, offering generative skills, reasoning, and profound contextual awareness. When it comes to language-based activities such as content creation, question answering, and summarization, they excel.Despite their impressive capabilities, LLMs are not without limitations. One of the most significant challenges is the problem of hallucination, where an LLM generates factually incorrect or misleading information that appears plausible. This is particularly problematic in domains requiring high factual accuracy, such as healthcare, finance, and legal applications. To mitigate hallucinations and enhance the reliability of LLM outputs, Retrieval-Augmented Generation (RAG) has emerged as a powerful technique. RAG works by dynamically retrieving relevant information from an external knowledge source (such as a knowledge graph) at inference time, rather than just relying on pre-trained knowledge. This approach ensures that the model has access to up-to-date and accurate data, grounding answers in verified information rather than generating content purely from its internal representations.Recent advancements highlight how integrating GraphML with LLMs can drive significant innovation, enabling the development of applications that require both rich semantic understanding and relational analysis. For instance:Graph-Augmented Question Answering: LLMs can leverage knowledge graphs to answer domain-specific questions with factual accuracy.Node Embedding Generation: State-of-the-art frameworks such as GraphGPT use LLMs to generate node embeddings directly from textual data, enabling seamless integration with graph structures.Knowledge Graph Construction and Enhancement: Recent applications have shown how LLMs can be used to enrich knowledge graphs, where LLMs are used to extract semantic relationships and entities from text to enhance existing graph data.Therefore, by bridging the gap between structured knowledge and natural language understanding, the synergy between GraphML and LLMs paves the way for more accurate, explainable, and intelligent systems.In the next section, we will explore the state-of-the-art trends in combining GraphML and LLMs, as well as the current challenges.State-of-the-art trends and challengesBefore diving into specific examples, it is crucial to understand the current landscape ofGraphML and LLM integration. According to a recent survey by Jin et al. (https://arxiv.org/ abs/2312.02783, 2024), the application scenario can be categorized into three main scenarios:Pure Graphs: These are graphs that lack associated textual information. Examples include social networks, traffic networks, and protein interaction networks. In such cases, the focus is on leveraging LLMs to process and analyze the structural aspects of the graph data.Text-Attributed Graphs: In these graphs, nodes or edges are enriched with textual attributes. For instance, in academic networks, papers (nodes) come with titles and abstracts, while authors (nodes) have profiles. E-commerce networks also fall into this category, where products (nodes) have descriptions, and user interactions (edges) may include reviews. The challenge here is to effectively combine the textual content with the graph’s structural information.Text-Paired Graphs: This scenario involves graphs that are paired with separate textual descriptions or documents. Unlike text-attributed graphs, where text is embedded within the graph as attributes, text-paired graphs treat the graph and text as distinct but related entities. A pertinent example is molecular graphs accompanied by detailed textual descriptions of their properties. The objective is to align and integrate the information from both the graph structure and the associated text to enhance understanding and analysis.To effectively utilize LLMs in these scenarios, three primary techniques can be used: LLMs as predictors, LLMs as encoders, and LLMs as aligners. Let’s see these approaches one by one.LLMs as predictorsThe simplest and most direct approach is to use LLMs as predictors. In this paradigm, the LLM operates as a tool to infer outcomes directly from graph data. Imagine a scenario where textual information is either minimal or entirely absent (pure graphs). In this case, you can transform the graph data into a format that the LLM can process, such as converting graph structures into sequences or textual descriptions.For instance, consider a simple social network graph where nodes represent people and edges indicate friendships (Figure 12.1). These features can be converted into a textual narrative, such as Alice is linked with Bob. An LLM can then process this narrative to predict new relationships or infer additional attributes about the nodes, such as professional interests or potential connections.Figure 12.1: Examples of how graphs can be converted to text narrativesOnce the data is prepared, the LLM can be fine-tuned or prompted to perform specific tasks. These might include predicting node classifications, such as identifying the role of individuals in a social network, or link predictions, such as forecasting interactions between entities. In molecular research, LLMs as predictors can help determine the properties of chemical compounds based solely on their structural representations.One advantage of this approach is its simplicity: LLMs can be applied directly to graph data without requiring extensive preprocessing or specialized models. However, this simplicity can also be a limitation. Purely structural information might not always be sufficient for complex tasks, particularly when additional contextual or textual data is available but not leveraged. Moreover, scalability and cost must be considered: encoding entire graphs as text can lead to an explosion of sentences, making inference expensive, potentially inefficient, and sometimes impossible (for example, if the maximum number of words an LLM can process at once is too small to contain the whole graph). Performance may also be limited, as this approach is similar to providing an LLM with a structured dataset and expecting accurate predictions without tailored adaptations. For this reason, more complex graph2text formalisms can be designed, incorporating node/edge descriptions into textual narratives while balancing efficiency and accuracy.LLMs as encodersWhen graphs are enriched with textual attributes, the LLM as encoder approach becomes particularly powerful. Here, the LLM is tasked with processing and encoding the textual information associated with nodes or edges, producing meaningful representations that can be integrated with the graph’s structural features. These embeddings are then integrated into the graph through proper algorithms such as graph neural networks, which process the combined representation to perform downstream tasks.This hybrid representation combines the strengths of both modalities, capturing the nuances of text alongside the relationships encoded in the graph. As depicted in Figure 12.2, each node could have attributes, such as a name and a brief bio for a node representing a person, while the edges might be annotated with information about the nature of the relation, e.g., close friend or colleague for a graph representing social networks. These features can be converted into a textual narrative, such as Alice, a software engineer, is close friends with Bob, a data scientist.Figure 12.2: Examples of how LMMs can be used as encoders for node attributesOther examples include academic citation networks, where papers (nodes) come with titles, abstracts, and keywords. An LLM can process these textual attributes to generate embeddings that encapsulate their semantic content. These embeddings are then combined with graph-specific features, such as the citation relationships between papers, to create a unified representation. Similarly, in e-commerce platforms, product descriptions and user reviews can be encoded by LLMs to enhance product similarity graphs or user behavior analysis.It is worth noticing that the process of using LLMs as encoders typically involves fine-tuning the LLM on domain-specific textual data to ensure that the embeddings accurately reflect the requirements of the task.This encoder approach offers significant benefits. By leveraging textual data, it captures context and nuances that purely structural methods might miss. It is particularly effective in scenarios where textual attributes can provide critical insights, such as identifying the themes of academic papers or understanding user preferences in recommendation systems.LLMs as alignersThe goal here is to align and integrate the information from both structure and textual descriptions (or accompanying documents, in the case of text-paired graphs), enabling a comprehensive analysis that leverages the strengths of each. This can be achieved, for example, by finding a shared latent space or a semantic mapping that connects the two modalities. Such an approach might involve designing models that jointly optimize both modalities or using attention mechanisms to focus on the most relevant parts of each input.In more detail, the synergy between textual encoding (handled by the LLM) and graph structure encoding (handled by, for example, a GNN), can be typically in two ways:1. Prediction Alignment: Iterative training where LLMs and GNNs generate pseudo-labels to guide each other’s learning2. Latent Space Alignment: Contrastive learning to align the latent representation of the text and the graph structure in a shared space (e.g., Figure 12.3)Figure 12.3: Graphs and associated texts can be embedded in a shared latent spaceFor example, in molecular research, a molecular graph might represent the structure of a compound, while a textual description provides information about its properties, synthesis, or applications. In this context, an LLM can be used to process the text to extract relevant features and align these with the structural characteristics of the graph, enabling tasks such as property prediction or drug discovery.As you can imagine, this approach is particularly powerful in interdisciplinary fields where graphs and text provide complementary points of view. In computational social science, for instance, social graphs representing interactions between individuals can be aligned with news articles, social media posts, or other textual data to study the spread of information or public sentiment. Similarly, in e-commerce, user behavior graphs can be integrated with textual reviews to improve personalized recommendations.Now that we have a clearer understanding of the LLM and graph landscape, let’s dive into a practical example of how this integration works. We will explore this in the next section.ConclusionIn summary, the convergence of LLMs and GraphML represents a major step forward in building next-generation AI systems that combine natural language intelligence with graph-based relational reasoning. By addressing challenges such as hallucination through techniques like Retrieval-Augmented Generation (RAG), and by applying frameworks where LLMs act as predictors, encoders, and aligners, this hybrid approach opens new possibilities for question answering, recommendation systems, knowledge graph enhancement, and explainable AI.This article is an excerpt from the book Graph Machine Learning – Second Edition. Readers who want to explore these concepts in greater depth, along with practical examples and broader GraphML applications, can continue reading in the book.Author BioAldo Marzullo received an M.Sc. degree in computer science from the University of Calabria (Cosenza, Italy) in September 2016. During his studies, he developed a solid background in several areas, including algorithm design, graph theory, and machine learning. In January 2020, he received his joint Ph.D. from the University of Calabria and Université Claude Bernard Lyon 1 (Lyon, France), with a thesis titled Deep Learning and Graph Theory for Brain Connectivity Analysis in Multiple Sclerosis. He is currently a postdoctoral researcher and collaborates with several international institutions.Enrico Deusebio is currently working as an engineering manager at Canonical, the publisher of Ubuntu, to promote open source technologies in the data and AI space and to make them more accessible to everyone. He has been working with data and distributed computing for over 15 years, both in an academic and industrial context, helping organizations implement data-driven strategies and build AI-powered solutions. He has collaborated and worked with top-tier universities, such as the University of Cambridge, the University of Turin, and the Royal Institute of Technology (KTH) in Stockholm, where he obtained a Ph.D. in 2014. He holds a B.Sc. and an M.Sc. degree in aerospace engineering from Politecnico di Torino.Claudio Stamile received an M.Sc. degree in computer science from the University of Calabria (Cosenza, Italy) in September 2013 and, in September 2017, he received his joint Ph.D. from KU Leuven (Leuven, Belgium) and Université Claude Bernard Lyon 1 (Lyon, France). During his career, he developed a solid background in AI, graph theory, and machine learning with a focus on the biomedical field.

Our Data Engineering Byte Newsletter gives data engineers and practitioners what they often lack today: clear, real-world insights—where every byte tells a story.Subscribe here to stay ahead in data engineeringIntroductionCRUD operations form the backbone of how developers interact with MongoDB databases. Whether you're building a small prototype or scaling a production application, understanding how to create, read, update, and delete data is essential. These four operations allow developers to insert new documents, retrieve existing information, modify stored data, and remove records when they are no longer needed.In MongoDB, CRUD operations can be performed using different tools and programming environments. Developers often begin with mongosh (MongoDB Shell) for direct database interaction and testing queries, while application development typically uses official drivers such as PyMongo for Python. This excerpt introduces the core CRUD concepts and demonstrates how to perform them using both mongosh and the MongoDB Python driver, helping developers understand the fundamental workflows for managing data in MongoDB.MongoDB CRUD operationsCRUD operations are the foundation of interacting with a MongoDB deployment. These operations require you to connect to the MongoDB server before you can query the relevant documents, adjust the specified properties, and subsequently, transmit the data back to the database for updates. Each CRUD operation serves a distinct purpose:The create operation creates and inserts new documents into the databaseThe read operation queries a document or documents in the databaseThe update operation modifies existing documents in the databaseThe delete operation removes documents from the databaseBasic CRUD with mongoshmongosh, also known as the MongoDB Shell, is an environment for hosting MongoDB deployments. It is equivalent to the administration console that relational databases use. You can download mongosh at https://www.mongodb.com/docs/mongodb-shell/install/.This section explains how to connect to your deployment with mongosh, insert a document into a database, query your database for a specified document, update a document, and delete a document. For more in-depth information and examples, you can also refer to the CRUD operations section of the MongoDB database manual at https://www.mongodb.com/docs/manual/crud/.Connecting to MongoDBBefore you can perform CRUD operations, you must connect to your MongoDB deployment. To connect to mongosh, you must specify your deployment connection string as well as any specified parameters.For example, enter a variation of the following code block in your terminal to connect to a MongoDB deployment with mongosh:mongosh "mongodb+srv://mycluster.packt.mongodb.net/myDatabase" --username myUsername --password myPasswordCreating documentsTo create a document in mongosh, you can use the db.collection.insertOne() command. This mongosh command creates a new document and inserts the created document into the specified collection.For example, the following db.collection.insertOne()command creates and inserts a new document into the library collection of the database. The new document has a title field set to Mastering MongoDB 8.0 and an isbn field set to 101:db.library.insertOne( { title: 'Mastering MongoDB 8.0', isbn: '101' } )When you successfully create and insert a document, MongoDB returns a confirmation output that contains the ObjectID value of the new document:{ acknowledged: true, insertedId: ObjectId("652024e7ab44f3bf77788a3d") }You can also use the following mongosh commands to update documents:db.collection.insertMany()db.collection.updateOne() with the upsert: true optiondb.collection.updateMany() with the upsert: true optiondb.collection.findAndModify() with the upsert: true optiondb.collection.findOneAndUpdate() with the upsert: true optiondb.collection.findOneAndReplace() with the upsert: true optionReading documentsRead operations in MongoDB are also called queries. To perform a basic read operation, or query, for a single document, use the db.collection.findOne() method and specify the selection criteria of the document that you want to read.For example, the following operation queries for a document in the library collection that contains the isbn field value of 101:db.library.find( { isbn: '101' } )If the library collection contains a document that matches your specified selection criteria, MongoDB returns an array that contains the document that matches:[ { _id: ObjectId("652024e7ab44f3bf77788a3d"),   title: 'Mastering MongoDB 8.0',   isbn: '101' } ]You can also use the following mongosh commands to read documents:db.collection.findOne()Updating documentsTo update a document, you can use the db.collection.updateOne() command. This command finds a document that matches the specified criteria and updates the found document.For example, the following code finds the document in the library collection that has an isbn field value of 101 and updates that document so that it contains a price field with a value of 30:db.library.updateOne( { isbn: '101' }, { $set: { price: 30 } } )When you successfully update a document, MongoDB returns the following confirmation summary:{ acknowledged: true,   insertedId: null,   matchedCount: 1,   modifiedCount: 1,   upsertedCount: 0 }You can also use the following mongosh commands to update documents:db.collection.updateMany()db.collection.replaceOne()db.collection.findOneAndReplace()db.collection.findOneAndUpdate()db.collection.findAndModify()MongoDB 8.0 also introduces the ability to sort documents within an update operation. If you specify a sort parameter in an operation that uses db.collection.updateOne(), db.collection. replaceOne(), or update, MongoDB sorts documents before updating to be able to select a specific document for the operation when multiple documents match the query.For example, the following update operation sorts all books by price and updates the lowest price book to be on sale for 25% off:db.library.updateOne( { }, [    { $set: { price: { $multiply: ["$price", 0.75] } } } ], { sort: { price: 1 } } )Deleting documentsTo delete a document, you can use the db.collection.deleteOne() command. This command queries for a document that matches specified criteria and deletes the found document.For example, the following operation deletes a document in the library collection that has an isbn field value of 101:db.library.deleteOne( { isbn: '101' } )When you successfully delete a document, MongoDB returns the following output:{    acknowledged: true,   deletedCount: 1 }You can also use the following mongosh commands to delete documents:db.collection.deleteMany()db.collection.remove()db.collection.findOneAndDelete()db.collection.findAndModify()Basic CRUD with the Python driverAs an alternative to mongosh, MongoDB provides official drivers to interact with your MongoDB deployment. For a full list of official MongoDB driver libraries, see https://www.mongodb.com/ docs/drivers/.The following sections walk you through performing CRUD operations with the MongoDB Python driver, PyMongo. PyMongo provides a seamless bridge between the dynamic world of Python programming and the efficient, document-oriented NoSQL database of MongoDB.Installing and connecting to PyMongoYou can use pip to install PyMongo. You must have Python installed prior to installing PyMongo.For example, the following operation installs PyMongo through your terminal:To connect to your MongoDB deployment with PyMongo, you must use your connection string.For example, the following code snippet in a .py file connects to a MongoDB deployment:from pymongo import MongoClient uri = "<connection string>" client = MongoClient(uri) try: client.admin.command('ping') print("Pinged your deployment. You successfully connected to MongoDB!") except Exception as e:     print(e)If you run the preceding code in a .py file and successfully connect to your deployment, MongoDB returns the following confirmation:"Pinged your deployment. You successfully connected to MongoDB!"You can test your connection to your MongoDB deployment on Atlas with the following code block in a .py file. This code block uses the asyncio asynchronous framework:import asyncio from motor.motor_asyncio import AsyncIOMotorClient from pymongo.server_api import ServerApi async def ping_server(): uri = "<connection string>" client = AsyncIOMotorClient(uri, server_api=ServerApi('1')) try: await client.admin.command('ping') print("Pinged your deployment. You successfully connected to MongoDB!") except Exception as e: print(e) asyncio.run(ping_server())If you run the preceding code in a .py file and successfully connect to your deployment, MongoDB returns the following confirmation:"Pinged your deployment. You successfully connected to MongoDB!".Creating documentsTo create and insert documents with PyMongo, use the insert_one command.For example, the code block that follows performs the following operations:Specifies the library collection within the resources databaseDefines a new document that represents a book titled Python and MongoDBInserts the new document into the library databasePrints the ObjectId value of the new documentlibrary = client.resources.library book = {   'isbn': '301', 'name': 'Python and MongoDB', 'meta': {'version': 'MongoDB 8.0'}, 'price': 60 } insert_result = library.insert_one(book) print(insert_result)If you successfully insert a document with this script, MongoDB prints the ObjectId value of the inserted document.Reading documentsYou can read documents from your database with queries.To read a document, specify the condition(s) of the document you want to read with a find_onecommand.For example, the code block that follows performs the following actions:Specifies the library collection in the resources databaseFinds documents with the name value of Python and MongoDB Prints the found documentlibrary = client.resources.library result = library.find_one( {"name": "Python and MongoDB"} ) print (result)Updating documentsTo update a document with PyMongo, use the update_one command. This command finds a document based on specified criteria and updates the found document.For example, the code block that follows performs the following operations:Finds the document where the name field is Advanced MongoDB TechniquesSets the price field of the found document to the value of 75Prints the result of the update_one operation       Prints the updated documentupdate_result = library.update_one(   { "name": "Advanced MongoDB Techniques"}, { "$set": { "price": 75 } } ) print(update_result.raw_result) updated_document = library.find_one(   {"name": "Advanced MongoDB Techniques"} ) print(updated_document)Deleting a documentTo delete a document with PyMongo, use the delete_one command. For example, the code block that follows performs the following operations:Specifies the isbn value of the book to delete as 303Uses the delete_one command to delete a book from the library collection with the desired isbn valuePrints the result of the delete_one operationisbn_to_delete = '303' delete_result = library.delete_one({"isbn": isbn_to_delete}) print(delete_result.raw_result)If the operation successfully deleted the desired document, MongoDB returns the following confirmation:{'n': 1, 'ok': 1.0}The n value indicates the number of documents that MongoDB deleted. In this case, MongoDB deleted one document. The ok value indicates whether the operation caused any errors. The 1.0 value in the preceding confirmation signifies that MongoDB did not encounter an error with this operation.ConclusionCRUD operations are the fundamental building blocks for working with MongoDB. By learning how to connect to a MongoDB deployment and perform create, read, update, and delete operations, developers gain the ability to manage application data efficiently. Tools such as mongosh provide a powerful command-line environment for direct database interaction, while drivers like PyMongo allow seamless integration between MongoDB and programming languages such as Python. Together, these tools enable developers to prototype, test, and build robust data-driven applications.This section provides a practical overview of MongoDB CRUD workflows, illustrating how documents can be inserted, queried, modified, and removed using both the MongoDB Shell and the Python driver.This content is an excerpt from The Official MongoDB Guide, published by Packt and MongoDB, and written by Rachelle Palmer, Jeffrey Allen, Parker Faucher, Alison Huh, Lander Kerbey, Maya Raman, and Lauren Tran.Author BioRachelle Palmer is the Product Leader for Developer Database Experience and Developer Education at MongoDB, overseeing the driver client libraries, documentation, framework integrations, and MongoDB University. She has built sample applications for MongoDB in Java, PHP, Rust, Python, Node.js, and Ruby. Rachelle joined MongoDB in 2013 and was previously the Director of the Technical Services Engineering team, creating and managing the team that provided support and CloudOps to MongoDB Atlas.Jeffery Allen is a Technical Writer at MongoDB, based in the New York City area. He focuses on server documentation and works closely with Product and Engineering teams to develop examples that reflect real-world use cases, especially pertaining to data modeling and the MongoDB query language. Before joining MongoDB, Jeff worked as a full-stack web developer. Jeff also enjoys playing guitar and piano and produces electronic music in his spare time.Parker Faucher is a self-taught Software Engineer with over six years of experience in technical education. He has authored more than 100 educational videos for MongoDB, establishing himself as a knowledgeable resource in database technologies. Currently, Parker focuses on Artificial Intelligence and Search technologies, exploring innovative solutions in these rapidly evolving fields. hen not advancing his technical expertise, Parker enjoys spending quality time with his family and maintains an avid interest in collecting comic books.Lander Kerbey is a Technical Writer at MongoDB, specializing in Atlas Stream Processing. Prior to MongoDB, he worked at InterSystems, documenting various parts of the HealthShare suite, along with their various data analytics tools. His 11 years of experience as an educator informs his approach to documentation, as he strives to create “Aha!” moments for users.Maya Raman is a Technical Writer and sometimes-software engineer at MongoDB. She is passionate about the intersections between the environment, art and literature, technology, and people. She is based in New York City and likes to spend her time hanging out in Prospect Park, even in winter.Lauren Tran is a Technical Writer at MongoDB with a background in Communications and Computer Science. She is on the Server Documentation team and mainly specializes in Information Architecture, Time Series data, and Search. Lauren is passionate about creating accessible and inclusive documentation that caters to diverse audiences. She is based in Chicago following four years in New York City and in her free time, enjoys reading on the beach at Lake Michigan and listening to Taylor Swift music.

DataPro is a weekly, expert-curated newsletter trusted by 120k+ global data professionals. Built by data practitioners, it blends first-hand industry experience with practical insights and peer-driven learning.Make sure to subscribe here so you never miss a key update in the data world. IntroductionAs demand for multilingual AI grows, fine-tuning large language models (LLMs) for under-resourced languages has become a strategic priority. Google’s Gemma competition on Kaggle showcased how open-source LLMs can be adapted for new languages using synthetic data, supervised fine-tuning (SFT), Direct Preference Optimization (DPO), and memory-efficient training techniques.At the same time, the LLM Prompt Recovery challenge pushed the boundaries of prompt engineering—asking competitors to reverse-engineer hidden instructions from model outputs. Together, these competitions reveal a powerful truth: modern LLM performance depends less on scale alone and more on data strategy, alignment methods, and metric-aware optimization.Unlocking global communication with Gemma: finetuning LLMs for new languagesOne of the landmark generative AI competitions on Kaggle was Google’s Unlocking GlobalCommunication with Gemma, an analytics competition with a $150,000 prize pool. Announced in late 2024, this competition invited participants to fine-tune Google’s Gemma 2 LLM for a specific language or cultural domain. The backdrop here is that Gemma is a family of open-source language models (built with the same underlying tech as Google’s Gemini models) that Google released to foster a community-driven ecosystem of language-specific models. By the time of the competition, a “Gemmaverse” of developers had already begun adapting Gemma to languages ranging from Arabic to Zulu. The competition’s goal was to accelerate this trend: each team would pick one of many under-represented languages (or a unique cultural niche of language use) and fine-tune Gemma 2 to excel at it. Competitors documented their approach in Kaggle notebooks, demonstrating improvements in tasks like the model’s language fluency, ability to handle literary traditions or historical texts of that language, and other culturally relevant capabilities.Competition format and dataAs an analytics competition, this wasn’t about submitting a model for automated scoring on a hidden test set. Instead, participants shared notebooks with their fine-tuned Gemma variants and qualitative/quantitative evaluations. Judges (including the Gemma model developers) assessed the submissions on criteria such as innovation, performance improvements, and the insightfulness of the approach. Google provided baseline resources: the base Gemma 2 model (with variants of 2B parameters that could be fine-tuned on Kaggle’s GPUs) and a list of ~70 eligible languages that were considered “under-resourced” in the LLM context. Participants often had to assemble their own fine-tuning datasets for the chosen language—drawing from public text sources or creating synthetic data—since by definition, many of these languages had limited ready-made datasets.Top solutions overviewThe winners of this competition delivered some impressive and instructive solutions. Many leveraged synthetic data generation to overcome data scarcity, creating large corpora of question-answer pairs or translated sentences using existing LLMs.For example, one of the winning teams focused on Italian (a moderately resourced language, but they aimed to push Gemma’s abilities in Italian to a new level). Their approach, as described by team member Stefano Fiorucci, was a “cheap recipe” that combined multiple techniques: Synthetic data generation (with LLM-as-a-judge), Supervised Fine-Tuning (SFT), Direct PreferenceOptimization, and efficient training with a method called Spectrum. In simpler terms, they first used a large model to generate Italian text data (and employed an LLM to judge/filter the quality of this synthetic data), then fine-tuned Gemma on this data (SFT). Next, they applied Direct Preference Optimization (DPO)—a technique related to RLHF—to further align the model’s outputs with human preferences by using a smaller reward model and optimizing the LLM against it. Finally, they used Spectrum, which is a memory-efficient fine-tuning strategy that selects which parts of the model to train, allowing them to fine-tune the 2B parameter model on Kaggle’s limited hardware. This multifaceted pipeline yielded an improved Gemma-It model that performed better in Italian language tasks than the original Gemma. You can find the Kaggle notebook for this solution here: https://www.kaggle.com/code/anakin87/post-training-gemma-for-italian-and-beyond.Another top team, headed by Justin Yang (which placed 2nd in the competition), tackled the task for Traditional Chinese and produced a notable open-source project called Kyara. In their write-up, the team explains that they generated over one million synthetic QA pairs for fine-tuning, plus an additional 150k prompts for preference optimization (Direct Preference Optimization DPO). This massive dataset was created by a method they dub “Retrieve, Rewrite, and Reformulate,” which uses a retrieval-augmented approach to generate question-answer pairs covering a wide range of knowledge. They also translated and paraphrased existing datasets from other languages to enrich the Chinese training data. By the end, they had essentially built a Chinese-centric instruction-following model on top of Gemma. The results, according to their report, showed strong performance compared to the original Gemma models—in other words, their fine-tuned Kyara model could understand and generate Chinese with greater accuracy and cultural relevance than baseline Gemma. Impressively, they released the Kyara model and the huge dataset on Hugging Face for the community, contributing back to the open-source ecosystem.You can find the Kyara model here: https://huggingface.co/datasets/zake7749/kyara-zhsample-1M. Justin’s Kaggle solution notebook can be found here: https://www.kaggle.com/code/ zake7749/kyara-retrieval-augmentation-for-llm-fine-tuning.Fine-tuning Gemma in practice: exampleHow does one actually fine-tune and use an LLM like Gemma in a Kaggle notebook? A common workflow is:1. Prepare a fine-tuning dataset of prompts and outputs.2. Train or fine-tune the model on this data (often using Hugging Face’s Transformers library or Google’s JAX/TPU tools if provided).3. After fine-tuning, load the new model and test it on some examples. Many top teams used PyTorch with Hugging Face Transformers for this process, as it’s a straightforward way to implement custom training loops or use Trainer APIs.Key techniques used in fine-tuningThe top solutions to the Gemma competition highlighted a few recurring themes that are instructive for any generative AI project on Kaggle:Synthetic data generation with LLMs: When real training data is scarce, use a larger LLM (or multiple LLMs) to generate additional data. For example, you might prompt GPT-4 to produce Q&A pairs in the target language, or to translate and rephrase English text into the target language. One team mentioned using an “LLM-as-a-judge” approach—they generated candidate outputs and then used another model (or heuristic) to judge which outputs were high-quality, ensuring that the fine-tuning data was clean and relevant.Supervised Fine-Tuning (SFT): This is the standard next step—take the base model and fine-tune on the supervised dataset (prompt à ideal response pairs). This aligns the model with the task. In practice, teams often had to train for multiple epochs and monitor an evaluation set (if available) to avoid overfitting. Libraries like HuggingFace Transformers make this easier via the Trainer class or peft.Lora for parameter-efficient fine-tuning. However, interestingly, one team reported that they attempted full LLM fine-tuning with LoRA but abandoned it due to poor validation correlation—indicating that not all fine-tuning attempts guarantee success, especially if the evaluation metrics are tricky (more on this in the Prompt Recovery section).Direct Preference Optimization (DPO) or RLHF: Several top entrants went beyond SFT and performed a second stage of tuning to better align the model’s outputs with human preferences. DPO is an approach where, instead of doing full reinforcement learning (which can be complex to implement), one can fine-tune the model to maximize a reward score (like a proxy for human preference) in a simpler, more direct way. To do this, you typically need a reward model—sometimes participants trained a smaller model to act as a judge between outputs, or they reused an existing one. By optimizing Gemma with DPO, teams were essentially making the model’s tone and style more user-friendly and its answers more “helpful” or correct where possible. Google’s Gemma being open meant that participants could experiment with these cutting-edge alignment techniques right on Kaggle.Efficient training and deployment: Handling a multi-billion-parameter model within Kaggle’s constraints (limited GPU RAM and runtime) is non-trivial. Teams used tricks like 4-bit quantization of model weights to reduce memory usage, gradient checkpointing to trade compute for memory, and transferring parts of the training to faster hardware off-platform when allowed. The Italian team’s use of Spectrum and the general use of BitsAndBytes (for 4-bit quantization) are examples of how winners squeezed more performance out of the available resources. In practice, Kaggle kernels with A100 GPUs (if provided) can handle fine-tuning a 2B model, but doing RLHF or DPO on top might require careful memory management. Participants often had to innovate on the engineering side as much as the modeling side.By the end of the competition, Google and the community gained a suite of fine-tuned Gemma models in many languages. This showcased a path towards truly multilingual AI that is not dominated by only high-resource languages. The winning notebooks illustrated how thoughtful data curation and novel training strategies can localize a large model to perform impressively well on niche languages or dialects. For example, one project fine-tuned Gemma for a dialect from Korea’s Jeju Island to help preserve that dialect—something far outside the reach of most commercial models. The competition underscored the practical insights that:• LLMs can be fine-tuned with surprisingly good results using public tools and a bit of creativity• Even without going into low-level model architecture changes, one can achieve significant performance boosts through data and training strategy alone.As Google’s official blog post put it, this collaborative effort helps “build a future where AI transcends language barriers,” and it exemplifies how Kaggle participants are contributing to the frontier of generative AI.While the Gemma competition demonstrated the power of fine-tuning open-source LLMs for multilingual and culturally aware performance, the next challenge shifted the focus inward—to-ward understanding the behavior of LLMs themselves. Instead of asking what these models can produce, the LLM Prompt Recovery competition asked: Can we reverse-engineer the very instructions that shaped those outputs? Let’s now explore how this innovative Kaggle competition reframed the prompt as the central object of prediction.LLM prompt recoveryImagine you have an original piece of text, and then you have a second piece of text that is a transformed version of the first—for example, maybe the second text is a summary of the first, or it’s the first text translated into Shakespearean English, or perhaps it’s the first text with all numerical information removed. In modern NLP, such transformations can be done by prompting an LLM. For instance, you might prompt an LLM: Translate the following passage to French or Rewrite this paragraph in a polite tone. The LLM takes the original and produces the transformed version.The LLM prompt recovery competition asked the reverse: given the original text and the transformed text, recover the prompt that was used to generate the transformation. In other words, participants had to figure out what instruction an LLM had been given. This challenge was a Kaggle Featured Competition held in early 2024, with a hefty $200,000 prize pool and over 2,000 teams participating. It was one of the first competitions to explicitly focus on prompt engineering and LLM behavior as the core problem.Let us go into the details of this competition in the following sections.Competition overviewThe task in LLM Prompt Recovery can be viewed as an inverse problem. Normally, we craft prompts to get outputs from an LLM. Here, we have the outputs (and the inputs) and must deduce the prompt. For example, if the original text is a verbose passage and the transformed text is a short bulleted list, the hidden prompt might have been Summarize the following text in bullet points. If the transformed text is in Spanish, the prompt likely was Translate to Spanish. If the transformed text is a playful version of the original, maybe Rewrite this text in a lighthearted, humorous tone. The challenge is that we are not told what kinds of transformations exist; participants had to infer patterns from data.Examining the dataThe competition dataset consisted of many pairs of texts: (original, transformed), and the goal was to predict the exact text of the prompt that was used to go from original to transformed. Importantly, the prompt was applied using a specific LLM (likely a Gemma model or similar) in a controlled environment. The competition description hinted that Google’s Gemma models were used to generate the transformed text given the prompt. So the transformations were realistic and fluent, not rule-based modifications. Participants were given some training examples with known prompts to learn from, and a test set where they had to predict prompts.One complication: prompts could be phrased in multiple ways (e.g., “Translate to French” vs“Translate this text into French language”). To evaluate predictions objectively, Kaggle defined a special metric called LLM Nerd-Off Sharpened Cosine Similarity. Despite the whimsical name, this metric was essentially a measure of semantic similarity between the predicted prompt and the true prompt, using embeddings. Likely, they embedded both prompts with a language model and computed cosine similarity, then perhaps raised it to a power or scaled it (“sharpened”) to emphasize differences. In short, participants didn’t need to match the prompt exactly word for word; they needed to capture the same meaning. A submission earned a high score if its prompts were semantically very close to the actual prompts used in generation.This evaluation method meant that the task was about capturing the essence of the prompt. If the true prompt was Summarize the article briefly, and a prediction was Give a short summary of the above passage, that should score very high (as it’s semantically equivalent). If a prediction missed the mark (e.g., Translate to French when the prompt was actually Simplify the language), the cosine similarity would be low.This competition encouraged participants to think about how different instructions manifest in text. They had to practically build a system that reads an original and its transformed version, and then outputs a plausible instruction. It’s a bit like playing detective with an AI’s behavior.Evaluating the challenges of the competitionThere are potentially hundreds of possible prompt types. Without additional structure, this is a challenging NLP problem—essentially, natural language understanding combined with some creativity. A straightforward approach would be to fine-tune a sequence-to-sequence model that takes the original and transformed text as input and tries to output the prompt. But doing that naïvely might be tough with limited training data. Also, some prompt types might be extremely rare or ambiguous.To succeed, competitors incorporated external knowledge and clever strategies. They suspected that certain transformation categories existed (such as translation, summarization, tone change, and information extraction). Likely, the competition forum discussions (and perhaps an initial “analysis” notebook by organizers) indicated the scope of tasks.Participants, therefore, had to leverage multiple techniques:NLP heuristics: For example, if the transformed text is much shorter than the original and covers similar content, it’s probably a summarization prompt. If the transformed text is in parentheses or brackets, it’s possible that the prompt asked for something to be extracted.Embedding-based similarity: You can embed the original and transformed text to examine the differences. For instance, if the embedding of the transformed text is close to that of a known French translation of the original, that signals a translation prompt. Kagglers might cluster or classify pairs using embeddings to identify prompt categories.LLMs themselves: Ironically, one could use an LLM to solve this LLM problem. For example, one could feed the original and transformed text into GPT-4 with a prompt like: Given the above original text and its modified version, what instruction was likely given to the model? This might give a very good answer much of the time.Fine-tuning custom models: Many top teams fine-tuned their own smaller LLMs or sequence models on the training data, along with a substantial amount of synthetic data. They simulated the process: take a large amount of text, apply various known prompts using an API (such as OpenAI or a local model) to generate transformed text, and thereby create a large dataset of (original, prompt, transformed) text. This synthetic corpus could then be used to train a model for prompt inversion.The creativity factor was high. In fact, the highest-performing strategies did some non-intuitive things, as we’ll see with the highlighted solution.To read from here (https://www.kaggle.com/suicaokhoailang) exploited the metric by formulating prompts that maximized similarity without necessarily being exact matches (an “adversarial” approach to the scoring metric). Khoi observed that predicting just the first half of the true prompt often yields a higher similarity score than the full prompt. This clever hack suggests that he might generate prompts that cover the key words and phrasing common to many actual prompts, thereby ensuring high embedding overlap. In Khoi’s words, “I think the special token pulls a sentence to some focal point in the embedding space, maybe the center of it. If the sentences are far enough from each other, it’s more likely that the new distance (from point B to point A </s>) is shorter than the old one (base of the triangle). But if the two sentences are already very close to each other, this can hurt performance.”Figure 13.2: Visualization of various distances between prompts and sentencesKhoi used a mix of Mistral 7b and Gemma-7b-1.1-it, and trained them on different datasets. He concatenated the predictions of both models, and the final mixture helped him achieve a winning score. However, this approach, while maximizing the score, might produce prompts that sound incomplete or odd to a human.Third-place solution (team prompt = “don’t say anything”)The third-place team tackled the prompt recovery task with a hybrid strategy combining a robust “mean prompt” baseline with several fine-tuned models (https://www.kaggle.com/competitions/ llm-prompt-recovery/discussion/494621). In essence, they constructed a fixed template prompt(a kind of universal prompt) and then augmented it with dynamic content predicted by models. By blending this optimized static prompt with learned predictions, the team could closely mimic the style and content of the true prompts. All of this was done in a neutral, third-person manner in their final assembled prompts, as required by the competition’s rules.The solution can be viewed as a hybrid ensemble of a prompt template and model predictions. It consists of five main components working in concert:Mean prompt template: A fixed template string (discovered through optimization) that serves as a baseline prompt, into which model-generated phrases are inserted.Full prompt prediction model: A fine-tuned language model (based on Mistral-7B) that attempts to predict the full rewrite prompt for a given sample.Gate classifier: A filtering model that checks whether the predicted prompt from the previous component is credible (i.e., consistent with the given original and rewritten text) and should be used.Tags prediction model: Another language model that predicts auxiliary tags (keywords describing style or instructions) relevant to the rewrite prompt.Clustering mechanism: A strategy that groups test samples into clusters of similar examples and selects the most suitable prompt template for each cluster.In the final inference pipeline, these components interact as follows. For a given test sample, the cluster assignment is first determined based on the sample’s characteristics (more on clustering below). A corresponding prompt template is chosen for that cluster, or a global default template is used if the cluster is uncertain. Then the full prompt model generates a candidate rewrite prompt for the sample, and the tags model produces supplementary keywords (such as a target style or tone). The gate model evaluates the candidate prompt against the original and rewritten text; if the prompt seems clearly incorrect (irrelevant or inconsistent), the system discards it. If it passes the gate, the final submission prompt is constructed by starting with the mean prompt template and inserting the additional predicted words (the tags and/or the full prompt prediction) at a specific position within the template. Notably, they only insert words that are not already present in the template to avoid redundancy. Through experimentation, the team found that the optimal insertion point was after the third word of the mean prompt template (this placement yielded the best validation performance). The result is a single coherent prompt string that includes the general template phrasing plus any unique details predicted by the models. This approach proved very effective, as using the first four components (template and models without clustering) already achieved an SCS of approximately 071 on validation. The clustering step added a small further boost (roughly +0.005)—not enough to reach 0.72, but every bit helped on the leaderboard.The final solution was the mean prompt + tags + full prompt (if passes the gate). The schematic of the solution can be found below:Figure 13.3: Third-place solution schematicBelow, we take a closer look at each component in detail and how they were developed and tuned.Origins of the mean promptLong before any model was trained, the team explored whether a single fixed instruction could, on average, resemble many hidden prompts. They began by producing tens of thousands of candidate instructions using strong external LLMs, such as GPT-3.5 and Gemini. Each candidate was applied to an LLM-generated passage to fabricate a plausible rewritten_text instance. Because the public leaderboard exposed similarity scores for half the test set, the team could estimate how well any individual instruction aligned with real hidden prompts. By iteratively sampling subsets of their synthetic data whose score distribution mirrored that of the public leaderboard, they obtained a surrogate development set. Then, they executed a token-level beam search across a few thousand word candidates. The search objective was simply to maximize average SCS on that surrogate set.The resulting sentence—obscure, repetitive, and studded with the Romanian word lucrarea— would look nonsensical to a human reader, yet it aligned strikingly well with the vectors used in the competition metric. Including lucrarea several times exploited an accidental quirk of the embedding space. This seemingly random token sat near the centroid of many true prompts, so multiplying it within the instruction nudged predictions closer in cosine space. With no modeling at all, the “mean prompt” alone achieved a score of 0.70 SCS and provided a strong backbone for later components. Here is the final mean prompt sentence:improve phrasing text {here we format full prompt prediction + tags} lucrarea tone lucrarea rewrite this creatively formalize discours involving lucrarea anyone emulate lucrarea description send casual perspective information alter it lucrarea ss plotline speaker recommend doing if elegy tone lucrarea more com n paraphrase ss forward this st text redesign poem above etc possible llm clear lucrareaBuilding a full-prompt predictorAlthough the universal instruction covered broad stylistic ground, it could not capture sample-specific details such as “translate to French” or “summarize in bullet points.” For that, the team fine-tuned Mistral-7B Instruct v0.2 under Low-Rank Adaptation (LoRA). They discovered that careful dataset design, rather than exotic training techniques, drove performance.The data pipeline ran backward from the goal. First, external LLMs generated diverse rewrite instructions, each enriched with stylistic hints so that a future model could infer the prompt’s intended effect. Next, the same LLMs paraphrased every instruction several times, yielding clusters of semantically identical yet lexically distinct prompts. For each prompt variant, the team asked an LLM to draft an original_text whose content harmonized with the instruction—ensuring, for instance, that a “formalize this casual email” prompt indeed paired with a casual email.To create the target rewritten_text, they let a quantized Gemma-2B model, accelerated with Unsloth, apply the prompt to the original passage. The outcome was a synthetic triple: original, prompt, rewritten.Because some prompt families now outnumber others, the team embedded every prompt with a Sentence-T5 model, clustered them via HDBSCAN, and subsampled each cluster to an equal size. This balanced curriculum prevented the model from memorizing only the most frequent rewrite patterns. After stripping boilerplate phrases such as “Sure, here is your text,” the dataset contained hundreds of thousands of clean examples. Fine-tuned on this mixture, the Mistral LoRA model achieved approximately 0.62 SCS when asked, zero-shot, to guess the hidden prompt from a genuine competition pair. That raw score was modest but crucial: when its predictions were appended to the mean template, overall similarity jumped markedly.Safeguarding with a gate classifierEven a well-trained predictor occasionally hallucinated. A single rogue instruction—”Translate to Klingon,” say—could drag the composite prompt far from the ground truth and erase gains accrued by the mean template. To defend against such outliers, the team equipped another Mistral instance with a sequence-classification head. Its input concatenated the original passage, a candidate prompt, and the rewritten passage, framed inside a short meta-instruction asking whether the rewriting matched the candidate’s prompt.Positive examples comprised 40 percent of the classifier’s training: genuine triples drawn from the synthetic corpus. Another 20 percent were easy negatives, formed by pairing a random prompt with an unrelated rewritten text. The remaining 40 percent were hard negatives: prompts retrieved from the nearest neighbors of the correct instruction in embedding space, therefore superficially similar yet semantically misaligned.Through this curriculum, the gate learned nuanced distinctions. During inference, it scored each predicted prompt; only those above a conservative threshold graduated to the final composition, while the rest were discarded, leaving the mean template unperturbed. Isolating attributes with a tag modelCertain facets of a rewrite—such as musicality, rhyme, or an imperative to summarize—proved hard for the full-prompt model to express reliably. The team addressed this by training a second causal-LM head, again atop Mistral-7B, whose sole aim was to emit a comma-separated list of keywords describing the transformation. The tag model digested the same original-rewritten pair and produced elements such as “summarize,” “formal tone,” “poem,” or “third-person perspective.” After inference, the system filtered out any tag already present in either the universal template or the accepted full-prompt prediction, then spliced the remaining tokens into the template at a fixed slot—immediately after the third word—found via validation sweep to be optimal. The tag model’s brevity reduced its error surface, allowing it to contribute correct micro-details that nudged SCS upwards.Riding the cluster spectrumThe final refinement sought a middle road between a single template and one-prompt-per-sample prediction. By embedding ground-truth prompts with Sentence-T5 and applying K-Means, the team observed 12 coherent clusters in their local validation set. For each cluster, they re-ran the beam-search optimization to craft a specialized mean prompt. Upon validation, the hypothetical score obtained by always selecting the ideal cluster template approached 0.76 SCS, far exceeding any published leaderboard result, revealing untapped headroom.Realizing that perfect cluster identification was impossible, the team trained yet another multiclass Mistral classifier to label unseen pairs with cluster IDs. They reinforced that decision with an independent heuristic: the predicted prompt from the full-prompt model was embedded and assigned to a cluster by the earlier K-Means centroids. Only when both the classifier and heuristic agreed did the pipeline adopt the cluster-specific template; otherwise, it reverted to the global mean prompt. This cautious protocol trimmed risk yet still delivered roughly five thousandths of a point on cross-validation, enough to matter in a contest decided on the third decimal place.End-to-end inference flowAt test time, each sample passed through a deterministic cascade. Sentence-T5 embeddings were computed to map the pair into a provisional cluster. In parallel, the full-prompt Mistral proposed an instruction, the tag model suggested keywords, and the gate classifier vetted the instruction’s plausibility.The system next chose a base template—cluster-specific when confidently classified, otherwise the global version. Into this template, it stitched, in order, unique tokens from the vetted full prompt and the filtered tag list. Because the universal instruction already contained many high-yield generic phrases, additive tokens were often scarce, resulting in concise yet customized prompts. The composite string then stood as the team’s prediction.Quantitative outcome and qualitative lessonsThe five-stage assembly scored just above 0.71 SCS on the hidden private set, securing third place. Though marginally shy of the winning mark, it demonstrated several principles valuable beyond this singular task. First, metric-aware prompt engineering—especially exploiting embedding artifacts such as lucrarea—can convert a daunting search space into a hill-climb around a sturdy baseline. Second, synthetic data, if diverse and cluster-balanced, can teach an LLM to infer latent instructions even when no labelled ground truth exists. Third, small specialist heads (gate and tag models) can prune and polish the output of a larger generator, providing robustness and incremental gains. Finally, intermediate granularity—here, a dozen clusters—yields a pragmatic compromise between universality and per-sample overfitting.All code, from data generation through LoRA fine-tuning to inference, relied heavily on opensource scaffolding. Yet, the decisive advantage arose less from tooling than from a mindset of relentless empirical probing. The team iterated through thousands of candidate tokens, balanced dozens of clusters, and rejected any enhancement that could not demonstrate reproducible uplift on a public subset that faithfully mirrored the test distribution. By combining a rule-based backbone with discriminative and generative neural modules, they reconstructed hidden promptswith a degree of fidelity previously thought unattainable in a zero-label setting.Although the competition’s idiosyncratic metric may never reappear, the architecture of this solution—static template plus gated generative refinement, augmented by attribute tagging and distribution-aware clustering—offers a portable recipe for tasks where the goal is to reverse-engineer or approximate opaque human instructions. In that sense, the third-place team delivered not merely a leaderboard score but a blueprint for marrying prompt engineering and fine-tuned language models under extreme supervision scarcity.Having explored the complexities of recovering prompts from transformed text using LLMs, we now turn to a different but equally innovative challenge: designing AI assistants that proactively help users with data tasks.ConclusionThe Gemma multilingual fine-tuning competition and the LLM Prompt Recovery challenge highlight the future of generative AI:Synthetic data can overcome language scarcity.Alignment techniques like SFT and DPO significantly improve output quality.Understanding evaluation metrics is critical for competitive performance.Open-source LLMs enable scalable, culturally aware AI development.For practitioners building multilingual systems or refining prompt strategies, these approaches provide a practical blueprint for engineering high-performance, resource-efficient language models.This article is an excerpt from The Kaggle Book, Second Edition by Luca Massaron, IconBojan Tunguz, and IconKonrad Banachewicz. Author BioLuca Massaron is a data scientist with over a decade of experience in transforming data into high-impact, innovative artifacts, solving real-world problems, and generating value for businesses and stakeholders. He is the author of numerous bestselling books on AI, machine learning, and algorithms. Luca is also a 3x Kaggle Grandmaster who reached number 7 in the worldwide user rankings for his performance in data science competitions. Additionally, he is recognized as a Google Developer Expert (GDE) in AI, Kaggle, and the cloud.Bojan Tunguz is the founder and CEO of TabulAI, a start-up focused on applying machine learning and AI to structured-data problems. Before founding TabulAI, he worked at three other machine learning start-ups and most recently at NVIDIA. He holds a PhD in theoretical physics from the University of Illinois and has taught as a professor at three liberal arts colleges.Konrad Banachewicz holds a PhD in statistics from Vrije Universiteit Amsterdam. His academic work focused on extreme dependency modeling in credit risk. In addition to his research activities, he was a tutor and supervised master's students. He transitioned from classical statistics to data mining and machine learning before "data science" became a buzzword. Over the next decade, he tackled quantitative analysis problems in various financial institutions, becoming an expert in the full life cycle of a data product. His work spanned high-frequency trading to credit risk, predicting potato prices, and analyzing anomalies in the performance of large-scale industrial equipment. He is a believer in knowledge sharing and also competes on Kaggle. --This text refers to an out of print or unavailable edition of this title.

Make sure to subscribe our BIPro Newsletter so you never miss a key update in the data world. Join over 35K+ BI lovers and get tips from those who’ve cracked tough data challenges!IntroductionA well-designed semantic model is the foundation of every effective Power BI report. It defines how data tables connect, how calculations behave, and how insights are ultimately presented to users. Without a solid semantic model, even the most visually appealing reports can produce misleading or inconsistent results. In this article, you’ll learn how to create and explore a semantic model in Power BI, understand the Model view, and build relationships that allow data to aggregate correctly across tables, setting the stage for accurate analysis and meaningful business insights. Technical requirementsThe following are needed in order to successfully complete the instructions provided in this chapter:An internet connectionMicrosoft Power BI DesktopChapter 5 Start.pbix downloaded from GitHub at https://github.com/PacktPublishing/Learn-Microsoft-Power-BI_3ECreating a semantic modelThe concept of a semantic model, data model, or dataset is fundamental to Power BI. In short, a semantic model is defined by the tables that are created from Power Query queries, the metadata (data about data) regarding the columns within the tables, and finally, the relationships that are defined between tables. Relationships are needed to connect individual tables to one another. In Power BI, the semantic model is stored within an Analysis Services tabular cube. It is the creation of this semantic model that enables self-service analytics and reporting.In Chapter 4, Connecting to and Transforming Data, we connected to various sources of data (three different Excel files) that in turn created seven different queries, which ultimately resulted in four queries that loaded data tables into our semantic model. We can now stitch those individual tables, along with our previously created data table, into a cohesive semantic model that is used for further analysis.If you are continuing from Chapter 4, continue to use the same Power BI Desktop file after completing that chapter. Otherwise, download and load Chapter 5 Start.pbix as specified in the Technical requirements section of this chapter.Touring the Model viewSo far, we have explored the overall architecture of Power BI Desktop and Power Query Editor. We will now explore the Model view within Power BI Desktop. To switch to the Model view, click the third icon from the top in the Views bar of Power BI Desktop.The Model view is shown in the following screenshot:Figure 5.1 – Model viewThe Model view provides an interface for building our semantic model. It does this by creating relationships between tables, as well as defining metadata for tables and columns. We can even create multiple layouts for our semantic model. The Model view interface is similar to other Desktop views, as shown in Figure 5.1:Header: As shown in Figure 5.1, the Header area is identical to what was described in the Touring the Desktop section of Chapter 3, Up and Running with Power BI Desktop.Ribbon: The Ribbon is nearly identical to what we described in the Touring the Desktop section of Chapter 3, Up and Running with Power BI Desktop, with the notable exception that only three tabs are available—that is, File, Home, and Help. If third-party extensions are installed, the External tools tab is also present.Views: The Views area is identical to what was described in the Touring the desktop section of Chapter 3, Up and Running with Power BI Desktop.Canvas: As mentioned in the Touring the desktop section of Chapter 3, Up and Running with Power BI Desktop, when in the Model view, this area displays layouts of tables within the semantic model, as well as their relationships to one another. A default All tables layout is created automatically.Panes: As described in the Touring the desktop section of Chapter 3, Up and Running with Power BI Desktop, only two panes are present—the omnipresent Data pane and the Properties pane. The Properties pane is exclusive to the Model view and allows us to associate metadata with various fields or columns within the tables of the semantic model. This includes the ability to specify synonyms and descriptions, as well as data types, data categories, and default aggregations or summarizations.Layouts: The Pages area from the Report view is replaced by Layouts. The Layouts area allows us to create multiple layouts or views of the data tables within the model, as well as to rename and reorder the layouts.Footer: As described in the Touring the desktop section of Chapter 3, Up and Running with Power BI Desktop, in the Model view, the Footer area provides various viewing controls, such as the ability to zoom in and out, reset the layout, and fit the model to the current display area.This completes our tour of the Model view. Let’s next see how we can change the layout of our semantic model.Modifying the layoutIn the Model view, we have a default All tables layout, which was created for us automatically by Power BI.To modify this layout, follow these steps:1. Minimize the Data and Properties panes by clicking on the arrow icon in the pane headers (>).2. Click on the tables and drag them closer together. Use the Fit to screen icon at the far right of the Footer to zoom in on the table layout. You should now be able to clearly see the table names and columns in the tables.3. Move the Calendar and People tables next to one another in the top center of the Canvas by clicking on and then dragging and dropping. Place the Budgets and Forecasts and Hours tables underneath these two tables. Use the Fit to screen icon in the footer to zoom in on the tables. Note that we cannot see all of the columns in the Hours table or the Calendar table. A right-hand scroll bar is present on both tables.4. Use the sizing handle in the bottom-right corner of the table to adjust the size so that we can see all of the columns in the table. When finished, your Canvas should look similar to this: Figure 5.2 – Data tables in the modelNow that we have modified the layout such that we can easily see all tables on the screen, let’s continue building our semantic model by defining relationships between the individual tables.Creating and understanding relationshipsNow that we can clearly see our tables and columns, we can create relationships between our tables. Creating relationships between tables allows calculations and aggregations to work across tables so that multiple columns can be used from separate, related tables.For example, once the People table and the Hours table are related to one another, we can use the ID column from the People table and the TotalHoursBilled column from the Hours table. By doing this, TotalHoursBilled aggregates correctly for each user Identifier (ID) in the People table.To create this relationship between the People table and the Hours table, click on the ID column in the People table and drag and drop it onto the EmployeeID column in the Hours table. The New Relationship dialog is displayed, as follows:Figure  – New relationship dialogClick the Save button and note that a line appears on the canvas that connects the People table to the Hours table. This creates a relationship between the ID column in the People table and the EmployeeID column in the Hours table.You can check the columns that are involved in a relationship by hovering your mouse over the relationship line. The line changes slightly and the columns involved in the relationship become shaded.Figure  – Relationship between two tablesIf you notice that ID and EmployeeID are not columns associated with the relationship, hover over the relationship line, right-click it, and choose Delete. Then, try again.Note that the line has 1 next to the People table and * next to the Hours table. This means that this relationship is one-to-many or many-to-one.In other words, there are unique row values in the People table that match multiple rows in the Hours table. This makes sense since each employee submits an Hours report for every day. The designation of 1 (unique) or * (many) defines the cardinality of the relationship between the tables.There are actually four different cardinalities for relationships in Power BI, as outlined here:One-to-one: This means that there are unique values in each table.Many-to-one: This means that there are unique values in one table that match multiple rows in another table.One-to-many: This means that there are unique values in one table that match multiple rows in another table.Many-to-many: This means that neither table has unique values for rows. It is generally good practice to avoid these types of relationships because of their complexity and the amount of processing and resources required.The designation of many-to-one versus one-to-many is simply a matter of which table is defined first within a relationship. In other words, the relationship between our People table and Hours table could be either many-to-one or one-to-many, depending on which table we defined first in our relationship.Note that there is also an arrow icon in the middle of the line that points from People to Hours. This indicates that the People table filters the Hours table, but not vice versa. This is known as the cross-filter direction. Cross-filter directions can be either Single or Both, meaning that the filtering occurs only one way or bidirectionally. In simple semantic models, you generally don’t have to worry about cross-filter direction, but it can become very important if the complexity of the semantic model increases.Finally, note that the line that forms the relationship between the tables is solid. A solid line indicates an active relationship. Inactive relationships can be created between tables, and these are represented as a dashed line. In Power BI, there can only be a single active path between tables.As models become more complex, multiple paths from one table to another can be created. In those cases, one or more of the relationships or pathways become inactive. However, even though the relationship is inactive by default, it can be used within calculations that specify using specific relationships.We can view and modify the relationship definition by double-clicking on the relationship line. This brings up the Edit relationship dialog, which looks nearly identical to the New relationship dialog shown in Figure 5.3.Note that since the Hours table is defined first and the People table is defined second, the Cardinality value of our relationship is Many to one (*:1). Also, note that the relationship is active and that the Cross filter direction value is Single.In many-to-one and one-to-many relationships, a Cross filter direction value of Single means that the one side of the relationship filters the many side. Finally, note that the EmployeeID column in the Hours table and the ID column in the People table are highlighted in gray. This shows us the columns that form a relationship between the two tables. Close the Edit relationship dialog by clicking the Cancel button.Now, let’s create another relationship, this time between our Calendar table and the Hours table. To do this, we can use the Manage relationships functionality, as follows:1. Click on the Home tab of the ribbon. Then, in the Relationships section, choose the Manage relationships button. The Manage relationships dialog is displayed, as shown in the following screenshot:Figure 5.5 – Manage relationships dialogHere, we can see our existing Active relationship between the Hours table and the People table, including the columns involved in the relationship in parentheses. From this dialog, we can Edit or Delete the relationship or have Power BI attempt to Autodetect relationships between tables. Power BI can sometimes autodetect relationships between tables based on the column names and row values.2. Select the + New relationship button. This displays the New relationship dialog, as shown in the following screenshot:Figure 5.6 – Create relationship dialog3. In the first drop-down menu, choose the Hours table. A preview of the table will be displayed.4. Click the Date column.5. Choose Calendar in the second drop-down menu. A preview of this table is displayed.6. Choose the Date column from this table. Power BI detects the appropriate Cardinality and Cross filter direction values. Since there are no other relationships between these tables, Power BI checks the Make this relationship active checkbox.7. Click the Save button to create this relationship. Note that this new relationship now appears in the Manage relationships dialog.8. Click the Close button to close the Manage relationships dialog. In the canvas, there will now be a relationship line linking the Calendar table to the Hours table.Congratulations—you have successfully linked the three separate tables to create a semantic model!For now, we won’t link the Budgets and Forecasts table within our semantic model. This is a good time to save your work.Now that our semantic model is created, let’s explore our semantic model a little closer by creating some visuals.Exploring the semantic modelBefore we move on, let’s do some exploration of our data in order to understand our data, our semantic model, and how this data can ultimately be viewed by end users. To do this, follow these steps:1. Start by clicking on the Report view in the Views bar.2. At the bottom of the report canvas, click the plus (+) icon next to Page 1. This creates a new blank page, Page 2. Double-click Page 2 and rename it Utilization, and then press the Enter key. This changes the name of the page to Utilization.3. Expand the People table in the Data pane by clicking the small arrow to the left of the People table. Check the box next to the Name field. This creates a Table visualization on our report canvas with the names of employees. Use the sizing handles for this visualization to resize the table to take up the entire page.4. Expand the Hours table. Make sure that the table visual is selected and then check the box next to the Hours field. The number of hours reported by each employee is shown next to their name. This occurs because of the relationship between our People table and our Hours table. Because these tables are joined by a relationship based on the ID of each employee, we can use fields from both tables in visualizations. Due to this, the rows in the Hours tables are automatically filtered based on this relationship.Note that when the Table visualization is selected, the Hours and People tables have small checkmarks next to them. This indicates that the visualization contains fields from these tables.5. We know that the Category field of the hours is important, regardless of whether the hours are billable or not. With the table selected, click on the Matrix visualization in the Visualizations pane. This icon is to the immediate right of the highlighted Table visualization. Note that the Build visual pane changes from just displaying a Values field well to now containing field wells for Rows, Columns, and Values. Our Name field is now under Rows, while our Hours field is under Values and shows up as Sum of Hours.6. From the Hours table, click on Category and drag and drop this field into the Columns field well. We can now see a breakdown of the hours for each employee by category.It is now obvious that we can calculate a simple version of our utilization metric by taking the number of Billable hours for each employee and dividing them by the Total number of hours for each employee. Save your work before continuing.ConclusionBuilding a semantic model in Power BI is a critical step in transforming raw data into reliable, self-service analytics. By organizing tables, defining relationships, and understanding how filtering and cardinality work, you enable calculations and visuals to behave as expected across your reports. This foundation not only improves accuracy and performance but also makes your Power BI solutions easier to scale and maintain over time.If you’d like to explore this topic in greater depth with guided examples, best practices, and real-world scenarios, you can learn more in Learn Microsoft Power BI - Third Edition by Greg Deckler, where semantic modeling and calculations are covered as part of a complete, end-to-end Power BI learning journey. This book helps you master self-service BI and become your team’s data hero or launch a new career. You’ll learn how to clean data, build models, and use advanced analytics in Power BI to unlock valuable insights and drive better business decisions.Author BioGreg Deckler is a Vice President at a global consulting services firm. In addition, I am a 7-time Microsoft MVP for Data Platform. As an active member in the Columbus IT community, I founded the Columbus Azure ML and Power BI User Group (CAMLPUG) and have presented at many different conferences and events including Dog Food, SharePoint Saturday, CloudDevelop and M3. I am also the author of Microsoft Hates Greg's Quick Measures (MSHGQM) tool and the associated YouTube channels Microsoft Hates Greg and DAX For Humans.

DataPro is a weekly, expert-curated newsletter trusted by 120k+ global data professionals. Built by data practitioners, it blends first-hand industry experience with practical insights and peer-driven learning.Make sure to subscribe here so you never miss a key update in the data world. IntroductionMost search systems still rely on keyword matching. It works, until users describe intent in natural language and the right answer does not share the same words.Semantic search closes that gap by ranking results based on meaning. It converts both documents and queries into vector embeddings, then retrieves the closest matches using K-Nearest Neighbor search.In this article, I will cover how OpenSearch supports semantic search in practice. That includes the KNN plugin, ML Commons for managing models, and the ingest and search pipelines used to generate and apply embeddings. I will also touch on accuracy versus latency trade-offs and how to evaluate relevance.Semantic searchWe define semantic search as using vector embeddings for the corpus of documents, to find mathematically close neighbors to a vector embedding computed for the user’s query text. Semantic search contrasts with lexical search, in which the query matches terms from the user’s query text to terms in the index. We call this process of finding mathematically close neighbors K-Nearest-Neighbors (KNN).OpenSearch provides KNN through its KNN plugin. The plugin supports storage engines and matching algorithms that index vector embeddings that you provide in your documents. The KNN plugin provides additional Query DSL query APIs for you to send a vector embedding as part of or as a whole query.The KNN plugin supports both exact KNN and approximate KNN. When you use exact KNN, OpenSearch compares the query vector to every embedding on every document that matches the query’s filters and scores and sorts the results based on vector distance. Approximate KNN uses various techniques to reduce the number of vector comparisons. We’ll cover KNN in the following sections. In the next section, you’ll learn about OpenSearch’s ml_commons plugin, which manages local-to-the-cluster Machine Learning (ML) models and provides a connector framework for ML models hosted on other platforms such as Amazon SageMaker or Amazon Bedrock. ML Commons and ML modelsInteraction with ML models is mediated by OpenSearch’s ml_commons plugin. You use ML Commons for loading models onto nodes in your cluster, and for connecting to models in third-party providers who provide APIs for calling them.The history of search includes many machine-learning techniques, and ML Commons supports models that perform different tasks.K-means: An iterative clustering algorithm that builds centroids for similar points.Linear regression: Fits a line to a set of inputs/outputs.Random Cut Forest (RCF): An unsupervised algorithm that learns normal variation in a series of points. OpenSearch’s Anomaly Detection (AD) uses RCF.RCF Summarize: Employs a hierarchical clustering technique that successively merges close points to create a collection of centroids.Localization: Detects anomalous or interesting points, such as spikes, over aggregate data.Logistic regression: Takes an input variable and predicts which of a class of outputs fits that variable.Metrics correlation (experimental): Detects correlations in groups of metric data, where there are anomalies closely related in time.Large language and multi-modal embedding models: These models create vector embeddings for text and images. OpenSearch can deploy several open source embedding models from Hugging Face and can access embedding models hosted on third-party platforms.Text generation models: Th ese models take an input prompt and generate a text response for that prompt.To use OpenSearch’s ML Commons plugin, you’ll set some cluster-wide settings that define where the plugin is deployed, which nodes perform ML processing, which features are enabled, and limits on memory consumed. Refer to os_client_factory.py, in the ch10 folder of the book’s repository to see the settings we used for the examples:allow_registering_model_via_url: Set True when you load models onto the cluster.only_run_on_ml_node: Set True when you want to run ml_commons on dedicated ML nodes. The book’s Docker compose file creates an ML node and sets this setting to True.In the next section, we cover OpenSearch’s Neural Search plugin, which helps simplify accessing and using ML models.OpenSearch’s Neural Search pluginThe Neural Search plugin provides a connector framework that enables communication with ML models, whether they are hosted on an ml node in the OpenSearch cluster, or they are hosted in a third-party model host such as Amazon SageMaker or Amazon Bedrock. The Neural Search plugin works with OpenSearch ingest pipelines and search pipelines, using a model ID that abstracts the underlying location and connection to the model itself.To use the Neural Search plugin for creating vector embeddings, you first define an ingest pipeline. Ingest pipelines are a native OpenSearch construct that enables you to chain together processors for data as OpenSearch ingests that data. There are a variety of processors (40 out of the box, and with additional plugins you can install) that perform functions such as mutating documents to add or remove fields, dropping documents, enriching documents with  Geographic data, extracting text PDF documents or Microsoft  Word documents, and more. In this chapter, you’ll use the text_embedding processor and the sparse_encoding processor.Ingest pipelines, ingest nodes, and Data PrepperIngest pipelines are a convenient way to mutate and enrich your data as OpenSearch processes that data. By default, OpenSearch will use data nodes as the compute resource to perform this processing. Data nodes have their own important use—responding to your queries! To avoid overloading your data nodes, consider deploying nodes with the ingest node role, separate from your data nodes. Th at way, you will localize the resource demands to those nodes. Even better, consider using Data Prepper, a component provided by the OpenSearch project. Data Prepper is a standalone component that can c onnect to many data sources, and perform Extract, Transform, Load (ETL) on your data. Amazon OpenSearch Service provides a managed version of Data Prepper, called Amazon OpenSearch Ingestion, that can greatly simplify collecting, processing, and loading data to OpenSearch or Amazon OpenSearch Service. At the time of writing, Data Prepper does not have a processor that can call out to ML model hosts, so this chapter uses ingest pipelines.Search pipelines similarly provide processors that you can use to orchestrate your query processing. Query processors act on the user’s query to perform tasks such as filtering or generating embeddings. Result processors act on the query’s result set to perform tasks such as normalizing and combining results for hybrid queries, or re-ranking queries.As you work through the examples in the chapter, you’ll see ingest and search processors in action. You’ll also see a new feature of OpenSearch 2.19—ingest and search workflows that let you define ingest and query processing with a simple, declarative syntax.In the next section, you’ll work through exact KNN queries to put the theory into practice.Exact K-Nearest-NeighborWhen you need to get the most accurate results, use exact KNN to query your data. An exact KNN query computes the distance between the search vector and each possible vector result. As with all queries, exact KNN queries scale latency with matches. Exact KNN is great for generating a  gold judgment set, but not fast enough to be the right algorithm for large (million+) datasets.Precision, accuracy, and measuring the quality of search resultsTo figure out how successful the retrieval and ranking are, relevance engineers measure and compare precision and accuracy. Precision is the proportion of search results that are relevant to the query. Accuracy is the proportion of correct results the query returns. Another measure, cumulative gain, measures the aggregate relevance of the results. Most practitioners use a version of cumulative gain, Normalized Discounted Cumulative Gain (NDCG). NDCG includes the position of each result in the overall result, relative to a human-juried, ideal ordering. NDCG is usually relative to the number of results, so you’ll see metrics such as NDCG@10, meaning the gain at result 10. Relevance measures require that you have a “golden set” – a set of queries and results that you know to be correct. You use this golden set as a yardstick and compare your query results to fi gure out how good (or bad) they are. As we mentioned, you can use exact KNN search to prepare a golden set so that you can measure the accuracy you lose when you employ an approximate nearest neighbor search.Code walk-throughTo make the code easier to understand, and to support the various examples in this chapter, we’ve provided several utility modules that we’ll reuse throughout to support the examples. Take a moment to examine the ch10 folder. You’ll find the following:os_client_factory.py: Wraps the authentication and connection details the OpenSearch Python client needs to make API calls.cleanup.py: Use with caution! This utility cleans up models (--models), indices (--indices), and connectors (--connectors) that the example code creates. This can be helpful to reset your cluster, but beware of side effects or accidentally deleting these resources.movie_source.py: Provides a Python generator that produces a line at a time, with data cleaning and enrichment, from the movies file, and a generator that produces a batch that the OpenSearch Python client’s bulk helper sends to OpenSearch.index_utils.py: Provides a mapping and a function that creates an index in OpenSearch.model_utils.py: Provides definitions for models that OpenSearch can host locally, along with code to register and deploy models to the OpenSearch cluster.connection_utils.py: Provides definitions and code to deploy connectors to model hosts.auto_incrementing_counter.py: Provides a convenience class that increments a counter when cast to a string. Used for indicating progress when indexing the movies.Take a moment to examine these scripts. Next, we’ll dive into the code and explain the implementation of exact matching.Exact KNN searchThe exact.py file is the main entry point for the exact KNN example. If you look at that file, you’ll find a number of constants that define the model to use, the index to use, and the ingest pipeline that enables OpenSearch’s Neural Search plugin to create embeddings automatically. Its main function deploys the model to OpenSearch’s ml node and gets it ready for use. Then, it loads the movies in batches of 1,000 to OpenSearch.We’ve employed OpenSearch’s Neural Search plugin to facilitate the creation of vector embeddings for each movie. When you use the Neural Search plugin, you create an OpenSearch ingest pipeline, with a text_embedding processor that invokes the model for each document you ingest. The alternative is to use an offline batch process to transform the source data, adding vector embeddings. If you already have embeddings, you can simply load the data to your OpenSearch index with normal bulk API calls. The Neural Search plugin automates the process of creating embeddings at ingestion and search time.ingest_pipeline_definition = { "description": "Embedding pipeline", "processors": [ { "text_embedding": { "model_id": "", "field_map": { EMBEDDING_SOURCE_FIELD_NAME: EMBEDDING_FIELD_NAME }}}]}This pipeline definition specifies a blank model_id (the code fills that in when it has a model ID) and a field_map, which tells the text_embedding ingest pipeline processor the source field for the text to compute an embedding, and the destination field for the embedding.pipeline_definition = deepcopy(ingest_pipeline_definition) pipeline_definition['processors'][0]['text_embedding'][ 'model_id'] = model_id os_client.ingest.put_pipeline( id=PIPELINE_NAME, body=pipeline_ definition)The code adds the model ID to the pipeline definition and uses the Python client to create the pipeline. To engage the pipeline, you define a default_pipeline in the index’s settings. The call to delete_then_create_index takes the pipeline name and adds it to the index settings in index_utils.py.[exact.py] index_utils.delete_then_create_index( os_client=os_client, index_name=INDEX_NAME, ingest_pipeline_name=PIPELINE_NAME, additional_fields=KNN_FIELDS ) [Index_utils.py] settings['settings']['default_pipeline'] = ingest_pipeline_nameExecute the exact.py script:python exact.pyThe script downloads the Hugging Face all-MiniLM-L12-V2 model to the cluster’s ml node and deploys it. It uses the model ID to create an ingest pipeline that builds encodings for the movie data, using the title, plot, and genre fields, concatenated in the embedding_source field. The indexing takes about five minutes on a MacBook Pro, running Docker Desktop with 16 GiB of dedicated RAM. It then creates an embedding for and executes a hardcoded query, Sci-fi about the force and jedis. You can use the --query command-line argument to run your own queries. Each time you run the script, it deletes and recreates the index. To avoid this lengthy process, use the --skip-indexing command-line argument.Examine script_query. It is a script_score query that uses the built-in knn_score function to compare the query vector (embedded in the script) with the embedding on the document. The score is based on the cosinesimil metric, which compares the cosines of the vectors. The query itself is a match_all query, scoring the vector distance against every document.Even though you didn’t query for star wars, the results include six of the Star Wars franchise movies in the results. That’s because “the force” and “jedis” are frequent terms in Star Wars movie titles and plots. As we highlighted in the Semantic search section of this chapter, these words are common contexts for those movies. You’ll also see that Ninja Strike Force is the third result, an interesting connection from the term “force.” You can imagine that jedis and ninjas are closely related in context as well.In the next section, we cover the combination of filters and vector search. In the section following that, you’ll apply filters to try to retrieve more relevant results.Exact KNN queries with filtersAs the last example illustrated, you sometimes want to filter out irrelevant documents, or filter in relevant documents. The vector embeddings don’t have a direct correlation with the fields of your document that you don’t send to the LLM for embedding. Fields such as the genres field in the movie data have values with their own semantic meanings that only loosely correlate with the plot field. Instead, you apply a filter to a knn query for those fields.There are three methods for applying filters in OpenSearch, and their usage and output depend on the nearest-neighbor method you’re using:Pre-filtering: For exact KNN queries, you can provide a filter as part of the query, and OpenSearch will first filter, then apply vector search to the results.Post-filtering: For approximate nearest neighbor, you can apply a  post-filter. You’ll see examples in the following sections. You can also wrap a knn query inside a bool query with a post-filter.Efficient filtering: For some approximate nearest neighbor engines, OpenSearch provides just-in-time filtering, which applies the filter as it encounters nearest neighbors.You apply a  fi ter to an exact KNN query through a filter clause in the query. Examine the code in exact.py to find the filtered_query_script query body. We’ve changed match_all to a bool query with a filter for sci-fi  movies and a rating of 6.9 or better: "query": { "script_score": { "query": { "bool": { "filter": [ { "term": { "genres.keyword": "Sci-Fi" }}, { "range": {"rating": {"gte": 6.9} }}]}},You can run this query by adding the --filtered parameter to the command line:python exact.py --skip-indexing --filteredThe search results now contain the six Star Wars movies, along with Iron Man. In this case, the filters remove movies that are not in the sci-fi  genre or have low ratings.Exact KNN b rings you the most accurate results but scales poorly for latency.ConclusionSemantic search changes the retrieval problem from term overlap to vector similarity. Done well, it produces results that track user intent more reliably than purely lexical approaches.OpenSearch provides the building blocks to operationalize this. The KNN plugin handles vector indexing and retrieval, ML Commons manages model deployment and connectivity, and the Neural Search plugin helps integrate embedding generation into pipelines.The trade-off is that relevance work becomes more disciplined. Exact KNN is useful for evaluation and building golden sets, while approximate methods are typically required at scale, especially when latency matters.If you want the full, end-to-end walkthrough, including the surrounding architecture choices, implementation details, and the broader context for how these pieces fit together, this article is adapted from The Definitive Guide to OpenSearch by Jon Handler, Soujanya Konka, and Prashant Agrawal. The book goes deeper into production patterns and practical examples you can apply directly to real systems. Author BioJon Handler is a Senior Principal Solutions Architect at Amazon Web Services based in Palo Alto, CA. Jon works closely with OpenSearch and Amazon OpenSearch Service, providing help and guidance to a broad range of customers who have search and log analytics workloads that they want to move to the AWS Cloud. Prior to joining AWS, Jon's career as a software developer included four years of coding a large-scale, ecommerce search engine. Jon holds a Bachelor of the Arts from the University of Pennsylvania, and a Master of Science and a Ph.D. in Computer Science and Artificial Intelligence from Northwestern University.Soujanya Konka is an accomplished Senior Solutions Architect at AWS with over 17 years of experience working with databases and analytics. She focuses on big data services and has worked with customers to migrate, right-size and enhance search using OpenSearch. Prior to joining AWS, Soujanya worked on large scale data warehouse and search migrations to the cloud and building enterprise search catalogs. Her journey has been one of continuous learning, innovation and equipping herself with skills and insights to tackle data challenges.Prashant Agrawal is a seasoned Search Specialist Solutions Architect at AWS based out of Seattle. With over 13 years of invaluable experience in the field of search and log analytics, he brings a wealth of knowledge to every project where he collaborates closely with clients to facilitate seamless migration and fine-tune OpenSearch clusters for optimal performance and cost savings. Beyond his tech prowess, he's an avid explorer, often found immersing himself in travel adventures and discovering new places. In essence, he thrives on the mantra: Eat Travel Repeat, making each journey a delightful experience. Join him on a transformative journey where every search and analytics challenge become a rewarding adventure.

This article is an excerpt from the book, "Unlocking Data with Generative AI and RAG", by Keith Bourne. Master Retrieval-Augmented Generation (RAG), the most popular generative AI tool, to unlock the full potential of your data. This book enables you to develop highly sought-after skills as corporate investment in generative AI soars.IntroductionAs the adoption of Retrieval-Augmented Generation (RAG) continues to grow, its potential to address key security challenges in AI-driven applications is becoming evident. Far from merely introducing risks, RAG offers a robust framework to enhance data protection, ensure accuracy, and maintain transparency in content generation. This article delves into the multifaceted security benefits of RAG, while also addressing the unique challenges it poses and strategies to mitigate them.How RAG can be leveraged as a security solutionLet’s start with the most positive security aspect of RAG. RAG can actually be considered a solution to mitigate security concerns, rather than cause them. If done right, you can limit data access via user, ensure more reliable responses, and provide more transparency of sources.Limiting dataRAG applications may be a relatively new concept, but you can still apply the same authentication and database-based access approaches you can with web and similar types of applications. This provides the same level of security you can apply in these other types of applications. By implementing userbased access controls, you can restrict the data that each user or user group can retrieve through the RAG system. This ensures that sensitive information is only accessible to authorized individuals. Additionally, by leveraging secure database connections and encryption techniques, you can safeguard the data at rest and in transit, preventing unauthorized access or data breaches.Ensuring the reliability of generated contentOne of the key benefits of RAG is its ability to mitigate inaccuracies in generated content. By allowing applications to retrieve proprietary data at the point of generation, the risk of producing misleading or incorrect responses is substantially reduced. Feeding the most current data available through your RAG system helps to mitigate inaccuracies that might otherwise occur.With RAG, you have control over the data sources used for retrieval. By carefully curating and maintaining high-quality, up-to-date datasets, you can ensure that the information used to generate responses is accurate and reliable. This is particularly important in domains where precision and correctness are critical, such as healthcare, finance, or legal applications.Maintaining transparencyRAG makes it easier to provide transparency in the generated content. By incorporating data such as citations and references to the retrieved data sources, you can increase the credibility and trustworthiness of the generated responses.When a RAG system generates a response, it can include links or references to the specific data points or documents used in the generation process. This allows users to verify the information and trace it back to its original sources. By providing this level of transparency, you can build trust with your users and demonstrate the reliability of the generated content.Transparency in RAG can also help with accountability and auditing. If there are any concerns or disputes regarding the generated content, having clear citations and references makes it easier to investigate and resolve any issues. This transparency also facilitates compliance with regulatory requirements or industry standards that may require traceability of information.That covers many of the security-related benefits you can achieve with RAG. However, there are some security challenges associated with RAG as well. Let’s discuss these challenges next.RAG security challengesRAG applications face unique security challenges due to their reliance on large language models (LLMs) and external data sources. Let’s start with the black box challenge, highlighting the relative difficulty in understanding how an LLM determines its response.LLMs as black boxesWhen something is in a dark, black box with the lid closed, you cannot see what is going on in there! That is the idea behind the black box when discussing LLMs, meaning there is a lack of transparency and interpretability in how these complex AI models process input and generate output. The most popular LLMs are also some of the largest, meaning they can have more than 100 billion parameters. The intricate interconnections and weights of these parameters make it difficult to understand how the model arrives at a particular output.While the black box aspects of LLMs do not directly create a security problem, it does make it more difficult to identify solutions to problems when they occur. This makes it difficult to trust LLM outputs, which is a critical factor in most of the applications for LLMs, including RAG applications. This lack of transparency makes it more difficult to debug issues you might have in building an RAG application, which increases the risk of having more security issues.There is a lot of research and effort in the academic field to build models that are more transparent and interpretable, called explainable AI. Explainable AI aims at making the operations of A I systems transparent and understandable. It can involve tools, frameworks, and anything else that, when applied to RAG, helps us understand how the language models that we use produce the content they are generating. This is a big movement in the field, but this technology may not be immediately available as you read this. It will hopefully play a larger role in the future to help mitigate black box risk, but right now, none of the most popular LLMs are using explainable models. So, in the meantime, we will talk about other ways to address this issue.You can use human-in-the-loop, where you involve humans at different stages of the process to provide an added line of defense against unexpected outputs. This can often help to reduce the impact of the black box aspect of LLMs. If your response time is not as critical, you can also use an additional LLM to perform a review of the response before it is returned to the user, looking for issues. We will review how to add a second LLM call in code lab 5.3, but with a focus on preventing prompt attacks. But this concept is similar, in that you can add additional LLMs to do a number of extra tasks and improve the security of your application.Black box isn’t the only security issue you face when using RAG applications though; another very important topic is privacy protection.Privacy concerns and protecting user dataPersonally identifiable information (PII) is a key topic in the generative AI space, with governments a round the world trying to determine the best path to balance user privacy with the data-hungry needs of these LLMs. As this gets worked out, it is important to pay attention to the laws and regulations that are taking shape where your company is doing business and make sure all of the technologies you are integrating into your RAG applications adhere. Many companies, such as Google and Microsoft , are taking these efforts into their own hands, establishing their own standards of protection for their user data and emphasizing them in training literature for their platforms.At the corporate level, there is another challenge related to PII and sensitive information. As we have said many times, the nature of the RAG application is to give it access to the company data and combine that with the power of the LLM. For example, for financial institutions, RAG represents a way to give their customers unprecedented access to their own data in ways that allow them to speak naturally with technologies such as chatbots and get near-instant access to hard-to-find answers buried deep in their customer data.In many ways, this can be a huge benefit if implemented properly. But given that this is a security discussion, you may already see where I am going with this. We are giving unprecedented access to customer data using a technology that has artificial intelligence, and as we said previously in the black box discussion, we don’t completely understand how it works! If not implemented properly, this could be a recipe for disaster with massive negative repercussions for companies that get it wrong. Of course, it could be argued that the databases that contain the data are also a potential security risk. Having the data anywhere is a risk! But without taking on this risk, we also cannot provide the significant benefits they represent.As with other IT applications that contain sensitive data, you can forge forward, but you need to have a healthy fear of what can happen to data and proactively take measures to protect that data. The more you understand how RAG works, the better job you can do in preventing a potentially disastrous data leak. These steps can help you protect your company as well as the people who trusted your company with their data.This section was about protecting data that exists. However, a new risk that has risen with LLMs has been the generation of data that isn’t real, called hallucinations. Let’s discuss how this presents a new risk not common in the IT world.HallucinationsWe have discussed this in previous chapters, but LLMs can, at times, generate responses that sound coherent and factual but can be very wrong. These are called hallucinations and there have been many shocking examples provided in the news, especially in late 2022 and 2023, when LLMs became everyday tools for many users.Some are just funny with little consequence other than a good laugh, such as when ChatGPT was asked by a writer for The Economist, “When was the Golden Gate Bridge transported for the second time across Egypt?” ChatGPT responded, “The Golden Gate Bridge was transported for the second time across Egypt in October of 2016” (https://www.economist.com/by-invitation/2022/09/02/artificialneural-networks-today-are-not-conscious-according-to-douglashofstadter).Other hallucinations are more nefarious, such as when a New York lawyer used ChatGPT for legal research in a client’s personal injury case against Avianca Airlines, where he submitted six cases that had been completely made up by the chatbot, leading to court sanctions (https://www. courthousenews.com/sanctions-ordered-for-lawyers-who-relied-onchatgpt-artificial-intelligence-to-prepare-court-brief/). Even worse, generative AI has been known to give biased, racist, and bigoted perspectives, particularly when prompted in a manipulative way.When combined with the black box nature of these LLMs, where we are not always certain how and why a response is generated, this can be a genuine issue for companies wanting to use these LLMs in their RAG applications.From what we know though, hallucinations are primarily a result of the probabilistic nature of LLMs. For all responses that an LLM generates, it typically uses a probability distribution to determine what token it is going to provide next. In situations where it has a strong knowledge base of a certain subject, these probabilities for the next word/token can be 99% or higher. But in situations where the knowledge base is not as strong, the highest probability could be low, such as 20% or even lower. In these cases, it is still the highest probability and, therefore, that is the token that has the highest probability to be selected. The LLM has been trained on stringing tokens together in a very natural language way while using this probabilistic approach to select which tokens to display. As it strings together words with low probability, it forms sentences, and then paragraphs that sound natural and factual but are not based on high probability data. Ultimately, this results in a response that sounds very plausible but is, in fact, based on very loose facts that are incorrect.For a company, this poses a risk that goes beyond the embarrassment of your chatbot saying something wrong. What is said wrong could ruin your relationship(s) with your customer(s), or it could lead to the LLM offering your customer something that you did not intend to offer, or worse, cannot afford to offer. For example, when Microsoft released a chatbot named Tay on Twitter in 2016 with the intention of learning from interactions with Twitter users, users manipulated this spongy personality trait to get it to say numerous racist and bigoted remarks. This reflected poorly on Microsoft, which was promoting its expertise in the AI area with Tay, causing significant damage to its reputation at the time (https://www.theguardian.com/technology/2016/mar/26/microsoftdeeply-sorry-for-offensive-tweets-by-ai-chatbot).Hallucinations, threats related to black box aspects, and protecting user data can all be addressed through red teaming.ConclusionRAG represents a promising avenue for enhancing security in AI applications, offering tools to limit data access, ensure reliable outputs, and promote transparency. However, challenges such as the black box nature of LLMs, privacy concerns, and the risk of hallucinations demand proactive measures. By employing strategies like user-based access controls, explainable AI, and red teaming, organizations can harness the advantages of RAG while mitigating risks. As the technology evolves, a thoughtful approach to its implementation will be crucial for maintaining trust, compliance, and the integrity of data-driven solutions.Author BioKeith Bourne is a senior Generative AI data scientist at Johnson & Johnson. He has over a decade of experience in machine learning and AI working across diverse projects in companies that range in size from start-ups to Fortune 500 companies. With an MBA from Babson College and a master’s in applied data science from the University of Michigan, he has developed several sophisticated modular Generative AI platforms from the ground up, using numerous advanced techniques, including RAG, AI agents, and foundational model fine-tuning. Keith seeks to share his knowledge with a broader audience, aiming to demystify the complexities of RAG for organizations looking to leverage this promising technology.

This article is an excerpt from the book, Data Cleaning with Power BI, by Gus Frazer. Unlock the full potential of your data by mastering the art of cleaning, preparing, and transforming data with Power BI for smarter insights and data visualizations.IntroductionDiscover the transformative potential of leveraging Azure OpenAI, integrated with ChatGPT functionality, to enhance Power BI's M query capabilities. In this article, we delve into how this powerful combination offers expert guidance, efficient solutions, and insightful recommendations for optimizing data transformation tasks. From generating M queries to streamlining complex transformations, explore how Azure OpenAI with ChatGPT empowers users to boost productivity and efficiency in Power BI.Using OpenAI for M queriesAzure OpenAI, with ChatGPT functionality within it, can be a helpful tool for generating M queries in Power BI by providing suggestions, helping with syntax, and offering insights into data transformation tasks. In the following example, you will learn about how you can leverage the chat playground within OpenAI to improve your productivity and efficiency when writing M queries. We will do this by asking a series of questions directly within Azure OpenAI.Complete the next steps to  follow along with the example in your own environment:1. Click on Deployment on the left-hand side and then select Create new deployment to get started.2. Select a model from the base models, in this case, select gpt-35-turbo, and then name your deployment. In this example, name it CleaningDataOpenAI.3. Select Chat playground from the Azure OpenAI Studio home screen or from the Playground tab on the left of your screen. This should open the following view:Figure 15.4 – Screenshot of the chat playground from within Azure OpenAINow that you have set up an instance of OpenAI, here’s an example conversation taken from OpenAI, where the chat functionality has been used to assist with writing M queries in Power BI:User: Hi, I’m working on a Power BI project, and I need to transform some data using M queries. I have a table with sales data, and I want to calculate the total sales for each product category. Can you help me with the M query for that?ChatGPT: Of course! To calculate the total sales for each product category, you can use the Group By transformation in Power BI’s Power Query Editor. Here’s a sample M snippet for your scenario:let Source = YourDataSource, GroupedTable = Table.Group(Source, {"ProductCategory"}, {{"TotalSales", each List.Sum([Sales]), type number}}) in GroupedTableReplace YourDataSource with the actual name of your data source. This code groups the data by the ProductCategory column and calculates the total sales for each group.User: Thanks! That’s a great starting point. What if I want to filter out products with sales less than $1,000  before calculating the total sales?ChatGPT: You can add a filtering step before the Group By transformation. Here’s an updated version of the M:let Source = YourDataSource, FilteredTable = Table.SelectRows(Source, each [Sales] >= 1000), GroupedTable = Table.Group(FilteredTable, {"ProductCategory"}, {{"TotalSales", each List.Sum([Sales]), type number}}) in GroupedTableThis code filters out rows where sales are less than $1,000 before grouping the data by product category and calculating the total sales.User: Perfect! This is really helpful. One more thing – what if I want to sort the results in descending order based on total sales?ChatGPT: You can add a sorting step using the Table.Sort function. Here’s the modified M:let Source = YourDataSource, FilteredTable = Table.SelectRows(Source, each [Sales] >= 1000), GroupedTable = Table.Group(FilteredTable, {"ProductCategory"}, {{"TotalSales", each List.Sum([Sales]), type number}}), SortedTable = Table.Sort(GroupedTable,{{"TotalSales", Order. Descending}}) in SortedTableThis code sorts the grouped table in descending order based on the “TotalSales” column.This interaction represents a simple example of how those who can leverage the power of tools such as OpenAI and C hatGPT will be able to quickly upskill in areas such as coding. It has to be said, though, that while this is still in its infancy, it’s important to always test and validate the answers provided before implementing them in production. Also, ensure that you take precautions when using the publicly available ChatGPT model to avoid sharing sensitive data publicly. If you would like to use sensitive data or you want to ensure that requests are given within a secured governed environment, make sure to use the ChatGPT model within your own Azure OpenAI instance.In more complex examples, optimizing Power Query transformations could involve efficient interaction with Azure OpenAI. This includes streamlining API calls, managing large datasets, and incorporating caching mechanisms for repetitive queries, ensuring a seamless and performant data cleaning process.As we begin to explore the use cases where this technology can be most effective, there are a number of clear early winners:Optimizing query plans: ChatGPT’s natural language understanding can assist in formulating more efficient Power Query plans. By describing the desired transformations in natural language, users can interact with ChatGPT to generate optimized query plans. This involves selecting the most suitable Power Query functions and structuring transformations for performance gains.Caching strategies for repetitive queries: ChatGPT can guide users in devising effective caching strategies. By understanding the context of data transformations, it can recommend where to implement caching mechanisms to store and reuse intermediate results, minimizing redundant API calls and computations. The following is an example of just this, where I have asked Azure OpenAI to verify and optimize my query from the Power Query Advanced Editor. The model suggested I use the Table.Buffer function to help cache the table in memory and optimize the query.Figure – An example request to OpenAI to help optimize my query for Power Query                                                        Figure – An example response from OpenAI to help optimize my query for Power QueryNow as we highlighted in Chapter 11, M Query Optimization, Table.Buffer can indeed improve the performance of your queries and refreshes, but this really depends on the data you are working with. In the previous example, the model doesn’t take the characteristics, size, or complexity of your data into consideration as it isn’t plugged into your data at this stage. Also linking back to the example you walked through in Chapter 11, the placement of where you add Table.Buffer can really impact how your query performs. In the previous example, if you were connecting to a small dataset, you would likely cause it to run slower by adding the Table.Buffer function as the second variable in the query.Lastly, it’s worth mentioning that how you prompt these models is crucially important. In the previous example, we didn’t specify what type of data source we were using in our query. As such, the model hasn’t provided an insight or overview that using Table.Buffer on a data source supporting query folding will cause it to break the fold. Again, this is not so much of a problem if Table.Buffer is placed at the end of your query for smaller datasets, but it is a problem if you add it nearer to the beginning of the query, like in the previous example.Handling large datasets: Dealing with large datasets often poses a challenge in Power Query. OpenAI models, including ChatGPT, can provide insights into dividing and conquering large datasets. This includes strategies for parallel processing, filtering data early in the transformation pipeline, and using aggregations to reduce computational load.Dynamic query adjustments: ChatGPT’s interactive nature allows users to dynamically adjust queries based on evolving requirements. It can assist in crafting queries that adapt to changing data scenarios, ensuring that Power Query transformations remain flexible and responsive to varied datasets.Guidance on complex transformations: Power Query oft en involves intricate transformations. ChatGPT can act as a virtual assistant, guiding users through the process of complex transformations. It can suggest optimal function compositions, advise on conditional logic placement, and assist in structuring transformations to enhance efficiency. The best example of this can be seen in the following two screenshots of an active use case seen in many businesses. The example begins with a user asking the model for a description of what the query is doing. OpenAI then provides a breakdown of what the query is doing in each step to help the user interpret the code. It helps to break down the barriers to coding and also helps to decipher code that has not been documented well by previous employees.                                                     Figure – An example request to OpenAI to help translate my queryFigure – An example response from OpenAI to help describe my queryError handling strategies: Optimizing Power Query also entails robust error handling. ChatGPT can provide recommendations for anticipating and handling errors gracefully within a query. This includes strategies for logging errors, implementing fallback mechanisms, and ensuring the stability of the overall data preparation process.In this section, you learned how to optimize Power Query transformations with Azure OpenAI efficiently. Key takeaways include using ChatGPT for natural-language-based query planning and effective caching strategies. Insights include handling large datasets through parallel processing, early filtering, and aggregations. This knowledge equips you to streamline and enhance your Power Query processes effectively.In the next section, you will learn about Microsoft  Copilot, how to set up a Power BI instance with Copilot activated, and also how you can use this new AI technology to help clean and prepare your data.ConclusionIn conclusion, Azure OpenAI with ChatGPT presents a game-changing solution for maximizing Power BI's potential. From query optimization to error-handling strategies, this integration streamlines processes and enhances productivity. As users navigate complex data transformations, the guidance provided fosters efficient decision-making and empowers users to tackle challenges with confidence. With Azure OpenAI and ChatGPT, the possibilities for revolutionizing Power BI workflows are endless, offering a glimpse into the future of data transformation and analytics.Author BioGus Frazer is a seasoned Analytics Consultant focused on Business Intelligence solutions. With over 7 years of experience working for the two market-leading platforms, Power BI & Tableau, has amassed a wealth of knowledge and expertise. Gus has helped hundreds of customers to drive their digital and data transformations, scope data requirements, drive actionable insights, and most important of all, cleanse data ready for analysis. Most recently helping to set up, organize and run the Power BI UK community at Microsoft. He holds 6 Azure and Power BI certifications, including the PL-300 and DP-500 certifications. In this book, Gus offers readers invaluable guidance on ingesting, preparing, and cleansing data for analysis in Power BI. --This text refers to an out of print or unavailable edition of this title.

This article is an excerpt from the book, "Microsoft Power BI Performance Best Practices - Second Edition", by Thomas LeBlanc, Bhavik Merchant. Overcome common challenges in data management, visualization, and security with this updated edition of Microsoft Power BI Performance Best Practices, and ramp-up your Power BI solutions, achieve faster insights, and drive better business outcomes.IntroductionOptimizing performance and storage in Power BI and Analysis Services can be a complex task. However, tools like DAX Studio and VertiPaq Analyzer simplify this process by providing insightful metrics and performance-tuning capabilities. This article explores how to leverage these tools to analyze semantic models, identify performance bottlenecks, and optimize DAX queries. We'll discuss key features such as viewing model metrics, capturing and analyzing query traces, and testing optimizations using DAX Studio's query editor.Tuning with DAX Studio and VertiPaq AnalyserDAX Studio, as the name implies, is a tool centered on DAX queries. It provides a simple yet intuitive interface with powerful features to browse and query Analysis Services semantic models. We will cover querying later in this section. For now, let’s look deeper into semantic models.The Analysis Services engine has supported dynamic management views (DMVs) for over a decade. These views refer to SQL-like queries that can be executed on Analysis Services to return information about semantic model objects and operations.VertiPaq Analyzer is a utility that uses publicly documented DMVs to display essential information about which structures exist inside the semantic model and how much space they occupy. It started life as a standalone utility, published as a Power Pivot for an Excel workbook, and still exists in that form today. In this chapter, we will refer to its more recent incarnation as a built-in feature of DAX Studio 3.0.11.It is interesting to note that VertiPaq is the original name given to the compressed column store engine within Analysis Services (Verti referring to columns and Paq referring to compression).Analyzing model size with VertiPaq AnalyzerVertiPaq Analyzer is built into DAX Studio as the View Metrics features, found in the Advanced tab of the toolbar. You simply click the icon to have DAX Studio run the DMVs for you and display statistics in a tabular form. This is shown in the following figure:Figure 6.8 – Using View Metrics to generate VertiPaq Analyzer statsYou can switch to the Summary tab of the VertiPaq Analyzer pane to get an idea of the overall total size of the model along with other summary statistics, as shown in the following figure:Figure 6.9 – Summary tab of VertiPaq AnalyzerThe Total Size metric provided in the previous figure will often be larger than the size of the semantic model on disk (as a .pbix file or Analysis Services .abf backup). This is because there are additional structures required when the model is loaded into memory, which is particularly true of Import mode semantic models.In Chapter 2, Exploring Power BI Architecture and Configuration, we learned about Power BI’s compressed column storage engine. The DMV statistics provided by VertiPaq Analyzer let us see just how compressible columns are and how much space they are taking up. It also allows us to observe other objects, such as relationships.The Columns tab is a great way to see whether you have any columns that are very large relative to others or the entire dataset. The following figure shows the columns view for the same model we saw in Figure 6.9. You can see how from 238 columns, a single column called SalesOrderNumber takes up a staggering 22.40% of the whole model size! It’s interesting to see its Cardinality (or uniqueness) value is about twelve times lower than the next largest column (SalesKey):|Figure 6.10 – Two columns monopolizing the semantic modelIn Figure 6.10, we can also see that Data Type is String for Online Sale-SalesOrderNumber, which was a column suggested by Tabular Editor to have a large dictionary footprint. These statistics would lead you to deduce that this column contains long, unique test values that do not compress well because there is a large cardinality. Indeed, in this case, the column contains a sales order number that is unique to each order plus is not used to group or slice analytical data in a Power BI report well.This analysis may lead you to re-evaluate the need for this level of reporting in the analysis of sales data. You’d need to ask yourself whether the extra storage space and time taken to build compressed columns and potentially other structures is worth it for your business case. In cases of highly detailed data such as this where you do not need detail-level sales order data, consider limiting the analysis to customer-related data such as demographics or date attributes such as year and month.Now, let’s learn about how DAX Studio can help us with performance analysis and improvement.Performance tuning the data model and DAXThe first-party option for capturing Analysis Services traces is SQL Server Profiler. When starting a trace, you must identify exactly which events to capture, which requires some knowledge of the trace events and what they contain. Even with this knowledge, working with the trace data in Profi ler can be tough since the tool was designed primarily to work with SQL Server application traces. The good news is that DAX Studio can start an Analysis Services server trace and then parse and format all the data to show you relevant results in a well-presented way within its user interface. It allows us to both tune and measure queries in a single place and provides features for Analysis Services that make it a good alternative SQL profiler for tuning semantic models.Capturing and replaying queriesThis All Queries command in the Traces section of the DAX Studio toolbar will start a trace against the semantic model you have connected to. Figure 6.11 shows the result when a trace is successfully started:Figure 6.11 – Query trace successfully started in DAX StudioOnce your trace has started, you can interact with the semantic model outside DAX Studio, and it will capture queries for you. How you interact with the semantic model depends on where it is. For a semantic model running on your computer in Power BI Desktop, you would simply interact with the report. This would generate queries that DAX Studio will see. The All Queries tab at the bottom of the tool is where the captured queries are listed in time order with durations in milliseconds. The following figure shows two queries captured when opening the Unique by Account No page from the Slow vs Fast Measures.pbix sample file:Figure 6.12 – Queries captured by DAX StudioThe preceding queries come from a screen that has the same table results in a visual, but two different DAX measures that calculate the aggregation. These measures make one table come back in less than a second while the other returns in about 17 seconds. The following figure shows the page in the report:Figure 6.13 – Tables with the same results but from using different measuresThe following screenshot shows the results of the Performance Analyzer for the tables previously.Observe how one query took over 17 seconds, whereas the other took under 1 second:Figure 6.14 – Vastly different query durations for the same visual resultIn Figure 6.12, the second query was double-clicked to bring the DAX text to the editor. You can modify this query in DAX Studio to test performance changes. We see here that the DAX expression for the UniqueRedProducts_Slow measure was not efficient. We’ll learn a technique to optimize queries soon, but first, we need to learn about capturing query performance traces.Obtaining query timingsTo get detailed query performance information, you can use the Server Timings command shown in Figure 6.11. After starting the trace, you can run queries and then use the Server Timings tab to see how the engine executed the query, as shown in the following figure:Figure 6.15 – Server Timings showing detailed query performance statisticsFigure 6.15 gives very useful information. FE and SE refer to the formula engine and storage engine. The storage engine is fast and multi-threaded, and its job is fetching data. It can apply basic logic such as filtering data to retrieve only what is needed. The formula engine is single-threaded, and it generates a query plan, which is the physical steps required to compute the result. It also performs calculations on the data such as joins, complex filters, aggregations, and lookups. We want to avoid queries that spend most of the time in the formula engine, or that execute many queries in the storage engine. The bottom-left section of Figure 6.15 shows that we executed almost 4,900 SE queries. The list of queries to the right shows many queries returning only one result, which is suspicious.For comparison, we look at timing for the fastest version of the query and we see the following:Figure 6.16 – Server Timings for a fast version of the queryIn Figure 6.16, we can see that only three server engine queries were run this time, and the result was obtained much faster (milliseconds compared to seconds).The faster DAX measure was as follows:UniqueRedProducts_Fast = CALCULATE( DISTINCTCOUNT('SalesOrderDetail'[ProductID]), 'Product'[Color] = "Red" )The slower DAX measure was as follows:UniqueRedProducts_Slow = CALCULATE( DISTINCTCOUNT('SalesOrderDetail'[ProductID]), FILTER('SalesOrderDetail', RELATED('Product'[Color]) = "Red"))TipThe Analysis Services engine does use data caches to speed up queries. These caches contain uncompressed query results that can be reused later to save time fetching and decompressing data. You should use the Clear Cache button in DAX Studio to force these caches to be cleared and get a proper worst-case performance measure. This is visible in the menu bar in Figure 6.11.We will build on these concepts when we look at DAX and model optimizations in later chapters. Now, let’s look at how we can experiment with DAX and query changes in DAX Studio.Modifying and tuning queriesEarlier in this section, we saw how we could capture a query generated by a Power BI visual and then display its text. A nice trick we can use here is to use query-scoped measures to override the measure definition and see how performance differs.The following figure shows how we can search for a measure, right-click, and then pull its definition into the query editor of DAX Studio:Figure 6.17 – The Define Measure option and result in the Query paneWe can now modify the measure in the query editor, and the engine will use the local definition instead of the one defined in the model! This technique gives you a fast way to prototype DAX enhancements without having to edit them in Power BI and refresh visuals over many iterations.Remember that this technique does not apply any changes to the dataset you are connected to. You can optimize expressions in DAX Studio, then transfer the definition to Power BI Desktop/Visual Studio when ready. The following figure shows how we changed the definition of UniqueRedProducts_ Slow in a query-scoped measure to get a huge performance boast:Figure 6.18 – Modified measure giving better resultsThe technique described here can be adapted to model changes too. For example, if you wanted to determine the impact of changing a relationship type, you could run the same queries in DAX Studio before and after the change to draw a comparison.Here are some additional tips for working with DAX Studio:Isolate measure: When performance tuning a query generated by a report visual, comment out complex measures and then establish a baseline performance score. Th en, add each measure back to the query individually and check the speed. This will help identify the slowest measures in the query and visual context.Work with Desktop Performance Analyzer traces: DAX Studio has a facility to import the trace files generated by Desktop Performance Analyzer. You can import trace files using the Load Perf Data button located next to All Queries highlighted in Figure 6.12. This trace can be captured by one person and then shared with a DAX/modeling expert who can use DAX Studio to analyze and replay their behavior. The following figure shows how DAX Studio formats the data to make it easy to see which visual component is taking the most time. It was generated by viewing each of the three report pages in the Slow vs Fast Measures.pbix sample file:Figure 6.19 – Performance Analyzer trace shows the slowest visual in the reportExport/import model metrics: DAX Studio has a facility to export or import the VertiPaq model metadata using .vpax files. These files do not contain any of your data. They contain table names, column names, and measure definitions. If you are not concerned with sharing these definitions, you can provide .vpax files to others if you need assistance with model optimizationConclusionDAX Studio and VertiPaq Analyzer are indispensable tools for anyone working with Power BI or Analysis Services models. From detailed model size analysis to advanced performance tuning, these tools empower users to identify inefficiencies and implement optimizations effectively. By using their robust features, such as the ability to view metrics, trace query performance, and prototype query changes, professionals can ensure their models are both efficient and scalable. Mastery of these tools lays a solid foundation for building high-performing, resource-efficient analytical solutions.Author BioThomas LeBlanc is a seasoned Business Intelligence Architect at Data on the Geaux, where he applies his extensive skillset in dimensional modeling, data visualization, and analytical modeling to deliver robust solutions. With a Bachelor of Science in Management Information Systems from Louisiana State University, Thomas has amassed over 30 years of experience in Information Technology, transitioning from roles as a software developer and database administrator to his current expertise in business intelligence and data warehouse architecture and management.Throughout his career, Thomas has spearheaded numerous impactful projects, including consulting for various companies on Power BI implementation, serving as lead database administrator for a major home health care company, and overseeing the implementation of Power BI and Analysis Service for a large bank. He has also contributed his insights as an author to the Power BI MVP book.Thomas is recognized as a Microsoft Data Platform MVP and is actively engaged in the tech community through his social media presence, notably as TheSmilinDBA on Twitter and ThePowerBIDude on Bluesky and Mastodon. With a passion for solving real-world business challenges with technology, Thomas continues to drive innovation in the field of business intelligence.Bhavik Merchant has nearly 18 years of deep experience in Business Intelligence. He is currently the Director of Product Analytics at Salesforce. Prior to that, he was at Microsoft, first as a Cloud Solution Architect and then as a Product Manager in the Power BI Engineering team. At Power BI, he led the customer-facing insights program, being responsible for the strategy and technical framework to deliver system-wide usage and performance insights to customers. Before Microsoft, Bhavik spent years managing high-caliber consulting teams delivering enterprise-scale BI projects. He has provided extensive technical and theoretical BI training over the years, including expert Power BI performance training he developed for top Microsoft Partners globally.

DataPro is a weekly, expert-curated newsletter trusted by 120k+ global data professionals. Built by data practitioners, it blends first-hand industry experience with practical insights and peer-driven learning.Make sure to subscribe here so you never miss a key update in the data world. This article is an excerpt from the book, "Principles of Data Science", by Sinan Ozdemir. This book provides an end-to-end framework for cultivating critical thinking about data, performing practical data science, building performant machine learning models, and mitigating bias in AI pipelines. Learn the fundamentals of computational math and stats while exploring modern machine learning and large pre-trained models.IntroductionTransfer learning (TL) has revolutionized the field of deep learning by enabling pre-trained models to adapt their broad, generalized knowledge to specific tasks with minimal labeled data. This article delves into TL with BERT and GPT, demonstrating how to fine-tune these advanced models for text classification and image classification tasks. Through hands-on examples, we illustrate how TL leverages pre-trained architectures to simplify complex problems and achieve high accuracy with limited data.TL with BERT and GPTIn this article, we will take some models that have already learned a lot from their pre-training and fine-tune them to perform a new, related task. This process involves adjusting the model’s parameters to better suit the new task, much like fine-tuning a musical instrument:Figure 12.8 – ITLITL takes a pre-trained model that was generally trained on a semi-supervised (or unsupervised) task and then is given labeled data to learn a specific task.Examples of TLLet’s take a look at some examples of TL with specific pre-trained models.Example – Fine-tuning a pre-trained model for text classificationConsider a simple text classification problem. Suppose we need to analyze customer reviews and determine whether they’re positive or negative. We have a dataset of reviews, but it’s not nearly large enough to train a deep learning (DL) model from scratch. We will fine-tune BERT on a text classification task, allowing the model to adapt its existing knowledge to our specific problem.We will have to move away from the popular scikit-learn library to another popular library called transformers, which was created by HuggingFace (the pre-trained model repository I mentioned earlier) as scikit-learn does not (yet) support Transformer models.Figure 12.9 shows how we will have to take the original BERT model and make some minor modifications to it to perform text classification. Luckily, the transformers package has a built-in class to do this for  us called BertForSequenceClassification:Figure 12.9 – Simplest text classification caseIn many TL cases, we need to architect additional layers. In the simplest text classification case, we add a classification layer on top of a pre-trained BERT model so that it can perform the kind of classification we want.The following code block shows an end-to-end code example of fine-tuning BERT on a text classification task. Note that we are also using a package called datasets, also made by HuggingFace, to load a sentiment classification task from IMDb reviews. Let’s  begin by loading up the dataset:# Import necessary libraries from datasets import load_dataset from transformers import BertTokenizer, BertForSequenceClassification, Trainer, TrainingArguments # Load the dataset imdb_data = load_dataset('imdb', split='train[:1000]') # Loading only 1000 samples for a toy example # Define the tokenizer tokenizer = BertTokenizer.from_pretrained('bert-base-uncased') # Preprocess the data def encode(examples): return tokenizer(examples['text'], truncation=True, padding='max_ length', max_length=512) imdb_data = imdb_data.map(encode, batched=True) # Format the dataset to PyTorch tensors imdb_data.set_format(type='torch', columns=['input_ids', 'attention_ mask', 'label'])With our dataset loaded up, we can run some training code to update our BERT model on our labeled data:# Define the model model = BertForSequenceClassification.from_pretrained( 'bert-base-uncased', num_labels=2) # Define the training arguments training_args = TrainingArguments( output_dir='./results', num_train_epochs=1, per_device_train_batch_size=4 ) # Define the trainer trainer = Trainer(model=model, args=training_args, train_dataset=imdb_ data) # Train the model trainer.train() # Save the model model.save_pretrained('./my_bert_model')Once we have our saved model, we can use the following code to run the model against unseen data:from transformers import pipeline # Define the sentiment analysis pipeline nlp = pipeline('sentiment-analysis', model=model, tokenizer=tokenizer) # Use the pipeline to predict the sentiment of a new review review = "The movie was fantastic! I enjoyed every moment of it." result = nlp(review) # Print the result print(f"label: {result[0]['label']}, with score: {round(result[0] ['score'], 4)}") # "The movie was fantastic! I enjoyed every moment of it." # POSITIVE: 99%Example – TL for image classificationWe could take a pre-trained model such as ResNet or the Vision Transformer (shown in Figure 12.10), initially trained on a large-scale image dataset such as ImageNet. This model has already learned to detect various features from images, from simple shapes to complex objects. We can take advantage of this knowledge, fi ne-tuning  the model on a custom image classification task:Figure 12.10 – The Vision TransformerThe Vision Transformer is like a BERT model for images. It relies on many of the same principles, except instead of text tokens, it uses segments of images as “tokens” instead.The following code block shows an end-to-end code example of fine-tuning the Vision Transformer on an image classification task. The code should look very similar to the BERT code from the previous section because the aim of the transformers library is to standardize training and usage of modern pre-trained models so that no matter what task you are performing, they can offer a relatively unified training and inference experience.Let’s begin by loading up our data and taking a look at the kinds of images we have (seen in Figure 12.11). Note that we are only going to use 1% of the dataset to show that you really don’t need that much data to get a lot out of pre-trained models!# Import necessary libraries from datasets import load_dataset from transformers import ViTImageProcessor, ViTForImageClassification from torch.utils.data import DataLoader import matplotlib.pyplot as plt import torch from torchvision.transforms.functional import to_pil_image # Load the CIFAR10 dataset using Hugging Face datasets # Load only the first 1% of the train and test sets train_dataset = load_dataset("cifar10", split="train[:1%]") test_dataset = load_dataset("cifar10", split="test[:1%]") # Define the feature extractor feature_extractor = ViTImageProcessor.from_pretrained('google/vitbase-patch16-224') # Preprocess the data def transform(examples): # print(examples) # Convert to list of PIL Images examples['pixel_values'] = feature_ extractor(images=examples["img"], return_tensors="pt")["pixel_values"] return examples # Apply the transformations train_dataset = train_dataset.map( transform, batched=True, batch_size=32 ).with_format('pt') test_dataset = test_dataset.map( transform, batched=True, batch_size=32 ).with_format('pt')We can similarly use the model using the following code:Figure 12.11 – A single example from CIFAR10 showing an airplaneNow, we can train our pre-trained Vision Transformer:# Define the model model = ViTForImageClassification.from_pretrained( 'google/vit-base-patch16-224', num_labels=10, ignore_mismatched_sizes=True ) LABELS = ['airplane', 'automobile', 'bird', 'cat', 'deer', 'dog', 'frog', 'horse', 'ship', 'truck'] model.config.id2label = LABELS # Define a function for computing metrics def compute_metrics(p): predictions, labels = p preds = np.argmax(predictions, axis=1) return {"accuracy": accuracy_score(labels, preds)} # Define the training arguments training_args = TrainingArguments( output_dir='./results', num_train_epochs=5, per_device_train_batch_size=4, load_best_model_at_end=True, # Save and evaluate at the end of each epoch evaluation_strategy='epoch', save_strategy='epoch' ) # Define the trainer trainer = Trainer( model=model, args=training_args, train_dataset=train_dataset, eval_dataset=test_dataset )Our final model has about 95% accuracy on 1% of the test set. We can now use our new classifier on unseen images, as in this next code block:from PIL import Image from transformers import pipeline # Define an image classification pipeline classification_pipeline = pipeline( 'image-classification', model=model, feature_extractor=feature_extractor ) # Load an image image = Image.open('stock_image_plane.jpg') # Use the pipeline to classify the image result = classification_pipeline(image)Figure 12.12 shows the result of this single classification, and it looks like it did pretty well:Figure 12.12 – Our classifier predicting a stock image of a plane correctlyWith minimal labeled data, we can leverage TL to turn models off the shelf into powerhouse predictive models.ConclusionTransfer learning is a transformative technique in deep learning, empowering developers to harness the power of pre-trained models like BERT and the Vision Transformer for specialized tasks. From sentiment analysis to image classification, these models can be fine-tuned with minimal labeled data, offering impressive performance and adaptability. By using libraries like HuggingFace’s transformers, TL streamlines model training, making state-of-the-art AI accessible and versatile across domains. As demonstrated in this article, TL is not only efficient but also a practical way to achieve powerful predictive capabilities with limited resources.Author BioSinan is an active lecturer focusing on large language models and a former lecturer of data science at the Johns Hopkins University. He is the author of multiple textbooks on data science and machine learning including "Quick Start Guide to LLMs". Sinan is currently the founder of LoopGenius which uses AI to help people and businesses boost their sales and was previously the founder of the acquired Kylie.ai, an enterprise-grade conversational AI platform with RPA capabilities. He holds a Master’s Degree in Pure Mathematics from Johns Hopkins University and is based in San Francisco.

This article is an excerpt from the book, "Apache Airflow Best Practices", by Dylan Intorf, Kendrick van Doorn, Dylan Storey. With practical approach and detailed examples, this book covers newest features of Apache Airflow 2.x and it's potential for workflow orchestration, operational best practices, and data engineering.IntroductionIn this article, we will continue to explore the application of modern “ops” practices within Apache Airflow, focusing on the observation and monitoring of your systems and DAGs after they’ve been deployed.We’ll divide this observation into two segments – the core Airflow system and individual DAGs. Each segment will cover specific metrics and measurements you should be monitoring for alerting and potential intervention.When we discuss monitoring in this section, we will consider two types of monitoring – active and suppressive.In an active monitoring scenario, a process will actively check a service’s health state, recording its state and potentially taking action directly on the return value.In a suppressive monitoring scenario, the absence of a state (or state change) is usually meaningful. In these scenarios, the monitored application sends an active schedule to a process to inform it that it is OK, usually suppressing an action (such as an alert) from occurring.This chapter covers the following topics:Monitoring core Airflow componentsMonitoring your DAGsTechnical requirementsBy now, we expect you to have a good understanding of Airflow and its core components, along with functional knowledge in the deployment and operation of Airflow and Airflow DAGs.We will not be covering specific observability aggregators or telemetry tools; instead, we will focus on the activities you should be keeping an eye on. We strongly recommend that you work closely with your ops teams to understand what tools exist in your stack and how to configure them for capture and alerting your deployments.Monitoring core Airflow componentsAll of the components we will discuss here are critical to ensuring a functioning Airflow deployment. Generally, all of them should be monitored with a bare minimum check of Is it on? and if a component is not, an alert should surface to your team for investigation. The easiest way to check this is to query the REST API on the web server at `/health/`; this will return a JSON object that can be parsed to determine whether components are healthy and, if not, when they were last seen.SchedulerThis component needs to be running and working effectively in order for tasks to be scheduled for execution.When the scheduler service is started, it also starts a `/health` endpoint that can be checked by an external process with an active monitoring approach.The returned signal does not always indicate that the scheduler is working properly, as its state is simply indicative that the service is up and running. There are many scenarios where the scheduler may be operating but unable to schedule jobs; as a result, many deployments will include a canary dag to their deployment that has a single task, acting to suppress an external alert from going off.Import metrics that airflow exposes for you include the following:scheduler.scheduler_loop_duration: This should be monitored to ensure that your scheduler is able to loop and schedule tasks for execution. As this metric increases, you will see tasks beginning to schedule more slowly, to the point where you may begin missing SLAs because tasks fail to reach a schedulable state.scheduler.tasks.starving: This indicates how many tasks cannot be scheduled because there are no slots available. Pools are a mechanism that Airflow uses to balance large numbers of submitted task executions versus a finite amount of execution throughput. It is likely that this number will not be zero, but being high for extended periods of time may point to an issue in how DAGs are being written to schedule work.scheduler.tasks.executable: This indicates how many tasks are ready for execution (i.e., queued). This number will sometimes not be zero, and that is OK, but if the number increases and stays high for extended periods of time, it indicates that you may need additional computer resources to handle the load. Look at your executor to increase the number of workers it can run. Metadata databaseThe metadata database is used to store and track all of the metadata for your Airflow deployments’ previous DAG/task executions, along with information about your environment’s roles and permissions. Losing data from this database can interrupt normal operations and cause unintended consequences, with DAG runs being repeated.While critical, because it is architecturally ubiquitous, the database is also least likely to encounter issues, and if it does, they are absolutely catastrophic in nature.We generally suggest you utilize a managed service for provisioning and operating your backing database, ensuring that a disaster recovery plan for your metadata database is in place at all times.Some active areas to monitor on your database include the following:Connection pool size/usage: Monitor both the connection pool size and usage over time to ensure appropriate configuration, and identify potential bottlenecks or resource contention arising from Airflow components’ concurrent connections.Query performance: Measure query latency to detect inefficient queries or performance issues, while monitoring query throughput to ensure effective workload handling by the database.Storage metrics: Monitor the disk space utilization of the metadata database to ensure that it has sufficient storage capacity. Set up alerts for low disk space conditions to prevent database outages due to storage constraints.Backup status: Monitor the status of database backups to ensure that they are performed regularly and successfully. Verify backup integrity and retention policies to mitigate the risk of data loss if there is a database failure.TriggererThe Triggerer instance manages all of the asynchronous operations of deferrable operators in a deferred state. As such, major operational concerns generally relate to ensuring that individual deferred operators don’t cause major blocking calls to the event loop. If this occurs, your deferrable tasks will not be able to check their state changes as frequently, and this will impact scheduling performance.Import metrics that airflow exposes for you include the following:triggers.blocked_main_thread: The number of triggers that have blocked the main thread. This is a counter and should monotonically increase over time; pay attention to large differences between recording (or quick acceleration) counts, as it’s indicative of a larger problem.triggers.running: The number of triggers currently on a triggerer instance. This metric should be monitored to determine whether you need to increase the number of triggerer instances you are running. While the official documentation claims that up to tens of thousands of triggers can be on an instance, the common operational number is much lower. Tune at your discretion, but depending on the complexity of your triggers, you may need to add a new instance for every few hundred consistent triggers you run.Executors/workersDepending on the executor you use, you will need to monitor your executors and workers a bit differently.The Kubernetes executor will utilize the Kubernetes API to schedule tasks for execution; as such, you should utilize the Kubernetes events and metrics servers to gather logs and metrics for your task instances. Common metrics to collect on an individual task are CPU and memory usage. This is crucial for tuning requests or mutating individual task resource requests to ensure that they execute safely.The Celery worker has additional components and long-lived processes that you need to metricize. You should monitor an individual Celery worker’s memory and CPU utilization to ensure that it is not over- or under-provisioned, tuning allocated resources accordingly. You also need to monitor the message broker (usually Redis or RabbitMQ) to ensure that it is appropriately sized. Finally, it is critical to measure the queue length of your message broker and ensure that too much “back pressure” isn’t being created in the system. If you find that your tasks are sitting in a queued state for a long period of time and the queue length is consistently growing, it’s a sign that you should start an additional Celery worker to execute on scheduled tasks. You should also investigate using the native Celery monitoring tool Flower (https://flower.readthedocs.io/en/latest/) for additional, more nuanced methods of monitoring.Web serverThe Airflow web server is the UI for not just your Airflow deployment but also the RESTful interface. Especially if you happen to be controlling Airflow scheduling behavior with API calls, you should keep an eye on the following metrics:Response time: Measure the time taken for the API to respond to requests. This metric indicates the overall performance of the API and can help identify potential bottlenecks.Error rate: Monitor the rate of errors returned by the API, such as 4xx and 5xx HTTP status codes. High error rates may indicate issues with the API implementation or underlying systems.Request rate: Track the rate of incoming requests to the API over time. Sudden spikes or drops in request rates can impact performance and indicate changes in usage patterns.System resource utilization: Monitor resource utilization metrics such as CPU, memory, disk I/O, and network bandwidth on the servers hosting the API. High resource utilization can indicate potential performance bottlenecks or capacity limits.Throughput: Measure the number of successful requests processed by the API per unit of time. Throughput metrics provide insights into the API’s capacity to handle incoming traffic.Now that you have some basic metrics to collect from your core architectural components and can monitor the overall health of an application, we need to monitor the actual DAGs themselves to ensure that they function as intended.Monitoring your DAGsThere are multiple aspects to monitoring your DAGs, and while they’re all valuable, they may not all be necessary. Take care to ensure that your monitoring and alerting stack match your organizational needs with regard to operational parameters for resiliency and, if there is a failure, recovery times. No matter how much or how little you choose to implement, knowing that your DAGs work and if and how they fail is the first step in fixing problems that will arise.LoggingAirflow writes logs for tasks in a hierarchical structure that allows you to see each task’s logs in the Airflow UI. The community also provides a number of providers to utilize other services for backing log storage and retrieval. A complete list of supported providers is available at https://airflow.apache.org/docs/apache-airflow-providers/core-extensions/logging.html.Airflow uses the standard Python logging framework to write logs. If you’re writing custom operators or executing Python functions with a PythonOperator, just make sure that you instantiate a Python logger instance, and then the associated methods will handle everything for you.AlertingAirflow provides mechanisms for alerting on operational aspects of your executing workloads that can be configured within your DAG:Email notifications: Email notifications can be sent if a task is put into a marked or retry state with the `email_on_failure` or `email_on_retry` state, respectively. These arguments can be provided to all tasks in the DAG with the `default_args` key work in the DAG, or individual tasks by setting the keyword argument individually.Callbacks: Callbacks are special actions that are executed if a specific state change occurs. Generally, these callbacks should be thoughtfully leveraged to send alerts that are critical operationally:on_success_callback: This callback will be executed at both the task and DAG levels when entering a successful state. Unless it is critical that you know whether something succeeds, we generally suggest not using this for alerting.on_failure_callback: This callback is invoked when a task enters a failed state. Generally, this callback should always be set and, in critical scenarios, alert on failures that require intervention and support.on_execute_callback: This is invoked right before a task executes and only exists at the task level. Use sparingly for alerting, as it can quickly become a noisy alert when overused.on_retry_callback: This is invoked when a task is placed in a retry state. This is another callback to be cautious about as an alert, as it can become noisy and cause false alarms.sla_miss_callback: This is invoked when a DAG misses its defined SLA. This callback is only executed at the end of a DAG’s execution cycle so tends to be a very reactive notification that something has gone wrong.SLA monitoringAs awesome of a tool as Airflow is, it is a well-known fact in the community that SLAs, while largely functional, have some unfortunate details with regard to implementation that can make them problematic at best, and they are generally regarded as a broken feature in Airflow. We suggest that if you require SLA monitoring on your workflows, you deploy a CRON job monitoring tool such as healthchecks (https://github.com/healthchecks/healthchecks) that allows you to create suppressive alerts for your services through its rest API to manage SLAs. By pairing this third- party service with either HTTP operators or simple requests from callbacks, you can ensure that your most critical workflows achieve dynamic and resilient SLA alerting.Performance profilingThe Airflow UI is a great tool for profiling the performance of individual DAGs:The Gannt chart view: This is a great visualization for understanding the amount of time spent on individual tasks and the relative order of execution. If you’re worried about bottlenecks in your workflow, start here.Task duration: This allows you to profile the run characteristics of tasks within your DAG over a historical period. This tool is great at helping you understand temporal patterns in execution time and finding outliers in execution. Especially if you find that a DAG slows down over time, this view can help you understand whether it is a systemic issue and which tasks might need additional development.Landing times: This shows the delta between task completion and the start of the DAG run. This is an un-intuitive but powerful metric, as increases in it, when paired with stable task durations in upstream tasks, can help identify whether a scheduler is under heavy load and may need tuning.Additional metrics that have proven to be useful (but may need to be calculated) include the following:Task startup time: This is an especially useful metric when operating with a Kubernetes executor. To calculate this, you will need to calculate the difference between `start_date` and `execution_date` on each task instance. This metric will especially help you identify bottlenecks outside of Airflow that may impact task run times.Task failure and retry counts: Monitoring the frequency of task failures and retries can help identify information about the stability and robustness of your environment. Especially if these types of failure can be linked back to patterns in time or execution, it can help debug interactions with other services.DAG parsing time: Monitoring the amount of time a DAG takes to parse is very important to understand scheduler load and bottlenecks. If an individual DAG takes a long time to load (either due to heavy imports or long blocking calls being executed during parsing), it can have a material impact on the timeliness of scheduling tasks.ConclusionIn this article, we covered some essential strategies to effectively monitor both the core Airflow system and individual DAGs post-deployment. We highlighted the importance of active and suppressive monitoring techniques and provided insights into the critical metrics to track for each component, including the scheduler, metadata database, triggerer, executors/workers, and web server. Additionally, we discussed logging, alerting mechanisms, SLA monitoring, and performance profiling techniques to ensure the reliability, scalability, and efficiency of Airflow workflows. By implementing these monitoring practices and leveraging the insights gained, operators can proactively manage and optimize their Airflow deployments for optimal performance and reliability.Author BioDylan Intorf is a solutions architect and data engineer with a BS from Arizona State University in Computer Science. He has 10+ years of experience in the software and data engineering space, delivering custom tailored solutions to Tech, Financial, and Insurance industries.Kendrick van Doorn is an engineering and business leader with a background in software development, with over 10 years of developing tech and data strategies at Fortune 100 companies. In his spare time, he enjoys taking classes at different universities and is currently an MBA candidate at Columbia University.Dylan Storey has a B.Sc. and M.Sc. from California State University, Fresno in Biology and a Ph.D. from University of Tennessee, Knoxville in Life Sciences where he leveraged computational methods to study a variety of biological systems. He has over 15 years of experience in building, growing, and leading teams; solving problems in developing and operating data products at a variety of scales and industries.

This article is an excerpt from the book, "The Definitive Guide to Google Vertex AI", by Jasmeet Bhatia, Kartik Chaudhary. The Definitive Guide to Google Vertex AI is for ML practitioners who want to learn Google best practices, MLOps tooling, and turnkey AI solutions for solving large-scale real-world AI/ML problems. This book takes a hands-on approach to help you become an ML rockstar on Google Cloud Platform in no time.Introduction While working on an ML project, if we are running a Jupyter Notebook in a local environment, or using a web-based Colab- or Kaggle-like kernel, we can perform some quick experiments and get some initial accuracy or results from ML algorithms very fast. But we hit a wall when it comes to performing large-scale experiments, launching long-running jobs, hosting a model, and also in the case of model monitoring. Additionally, if the data related to a project requires some more granular permissions on security and privacy (fine-grained control over who can view/access the data), it’s not feasible in local or Colab-like environments. All these challenges can be solved just by moving to the cloud. Vertex AI Workbench within Google Cloud is a JupyterLab-based environment that can be leveraged for all kinds of development needs of a typical data science project. The JupyterLab environment is very similar to the Jupyter Notebook environment, and thus we will be using these terms interchangeably throughout the book. Vertex AI Workbench has options for creating managed notebook instances as well as user-managed notebook instances. User-managed notebook instances give more control to the user, while managed notebooks come with some key extra features. We will discuss more about these later in this section. Some key features of the Vertex AI Workbench notebook suite include the following: Fully managed–Vertex AI Workbench provides a Jupyter Notebook-based fully managed environment that provides enterprise-level scale without managing infrastructure, security, and user-management capabilities. Interactive experience–Data exploration and model experiments are easier as managed notebooks can easily interact with other Google Cloud services such as storage systems, big data solutions, and so on. Prototype to production AI–Vertex AI notebooks can easily interact with other Vertex AI tools and Google Cloud services and thus provide an environment to run end-to-end ML projects from development to deployment with minimal transition. Multi-kernel support–Workbench provides multi-kernel support in a single managed notebook instance including kernels for tools such as TensorFlow, PyTorch, Spark, and R. Each of these kernels comes with pre-installed useful ML libraries and lets us install additional libraries as required. Scheduling notebooks–Vertex AI Workbench lets us schedule notebook runs on an ad hoc and recurring basis. This functionality is quite useful in setting up and running large-scale experiments quickly. This feature is available through managed notebook instances. More information will be provided on this in the coming sections. With this background, we can now start working with Jupyter Notebooks on Vertex AI Workbench. The next section provides basic guidelines for getting started with notebooks on Vertex AI. Getting started with Vertex AI Workbench Go to the Google Cloud console and open Vertex AI from the products menu on the left pane or by using the search bar on the top. Inside Vertex AI, click on Workbench, and it will open a page very similar to the one shown in Figure 4.3. More information on this is available in the official  documentation (https://cloud.google.com/vertex-ai/docs/workbench/ introduction).  Figure 4.3 – Vertex AI Workbench UI within the Google Cloud console As we can see, Vertex AI Workbench is basically Jupyter Notebook as a service with the flexibility of working with managed as well as user-managed notebooks. User-managed notebooks are suitable for use cases where we need a more customized environment with relatively higher control. Another good thing about user-managed notebooks is that we can choose a suitable Docker container based on our development needs; these notebooks also let us change the type/size of the instance later on with a restart. To choose the best Jupyter Notebook option for a particular project, it’s important to know about the common differences between the two solutions. Table 4.1 describes some common differences between fully managed and user-managed notebooks: Table 4.1 – Differences between managed and user-managed notebook instances Let’s create one user-managed notebook to check the available options:  Figure 4.4 – Jupyter Notebook kernel configurations As we can see in the preceding screenshot, user-managed notebook instances come with several customized image options to choose from. Along with the support of tools such as TensorFlow Enterprise, PyTorch, JAX, and so on, it also lets us decide whether we want to work with GPUs (which can be changed later, of course, as per needs). These customized images come with all useful libraries pre-installed for the desired framework, plus provide the flexibility to install any third-party packages within the instance. After choosing the appropriate image, we get more options to customize things such as notebook name, notebook region, operating system, environment, machine types, accelerators, and so on (see the following screenshot):  Figure 4.5 – Configuring a new user-managed Jupyter Notebook Once we click on the CREATE button, it can take a couple of minutes to create a notebook instance. Once it is ready, we can launch the Jupyter instance in a browser tab using the link provided inside Workbench (see Figure 4.6). We also get the option to stop the notebook for some time when we are not using it (to reduce cost):  Figure 4.6 – A running Jupyter Notebook instance This Jupyter instance can be accessed by all team members having access to Workbench, which helps in collaborating and sharing progress with other teammates. Once we click on OPEN JUPYTERLAB, it opens a familiar Jupyter environment in a new tab (see Figure 4.7):  Figure 4.7 – A user-managed JupyterLab instance in Vertex AI Workbench A Google-managed JupyterLab instance also looks very similar (see Figure 4.8):  Figure 4.8 – A Google-managed JupyterLab instance in Vertex AI Workbench Now that we can access the notebook instance in the browser, we can launch a new Jupyter Notebook or terminal and get started on the project. After providing sufficient permissions to the service account, many useful Google Cloud services such as BigQuery, GCS, Dataflow, and so on can be accessed from the Jupyter Notebook itself using SDKs. This makes Vertex AI Workbench a one-stop tool for every ML development need. Note: We should stop Vertex AI Workbench instances when we are not using them or don’t plan to use them for a long period of time. This will help prevent us from incurring costs from running them unnecessarily for a long period of time. In the next sections, we will learn how to create notebooks using custom containers and how to schedule notebooks with Vertex AI Workbench. Custom containers for Vertex AI Workbench Vertex AI Workbench gives us the flexibility of creating notebook instances based on a custom container as well. The main advantage of a custom container-based notebook is that it lets us customize the notebook environment based on our specific needs. Suppose we want to work with a new TensorFlow version (or any other library) that is currently not available as a predefined kernel. We can create a custom Docker container with the required version and launch a Workbench instance using this container. Custom containers are supported by both managed and user-managed notebooks. Here is how to launch a user-managed notebook instance using a custom container: 1. The first step is to create a custom container based on the requirements. Most of the time, a derivative container (a container based on an existing DL container image) would be easy to set up. See the following example Dockerfile; here, we are first pulling an existing TensorFlow GPU image and then installing a new TensorFlow version from the source: FROM gcr.io/deeplearning-platform-release/tf-gpu:latest RUN pip install -y tensorflow2. Next, build and push the container image to Container Registry, such that it should be accessible to the Google Compute Engine (GCE) service account. See the following source to build and push the container image: export PROJECT=$(gcloud config list project --format "value(core.project)") docker build . -f Dockerfile.example -t "gcr.io/${PROJECT}/ tf-custom:latest" docker push "gcr.io/${PROJECT}/tf-custom:latest"Note that the service account should be provided with sufficient permissions to build and push the image to the container registry, and the respective APIs should be enabled. 3. Go to the User-managed notebooks page, click on the New Notebook button, and then select Customize. Provide a notebook name and select an appropriate Region and Zone value. 4. In the Environment field, select Custom Container. 5. In the Docker Container Image field, enter the address of the custom image; in our case, it would look like this: gcr.io/${PROJECT}/tf-custom:latest 6. Make the remaining appropriate selections and click the Create button. We are all set now. While launching the notebook, we can select the custom container as a kernel and start working on the custom environment. Conclusion Vertex AI Workbench stands out as a powerful, cloud-based environment that streamlines machine learning development and deployment. By leveraging its managed and user-managed notebook options, teams can overcome local development limitations, ensuring better scalability, enhanced security, and integrated access to Google Cloud services. This guide has explored the foundational aspects of working with Vertex AI Workbench, including its customizable environments, scheduling features, and the use of custom containers. With Vertex AI Workbench, data scientists and ML practitioners can focus on innovation and productivity, confidently handling projects from inception to production. Author BioJasmeet Bhatia is a machine learning solution architect with over 18 years of industry experience, with the last 10 years focused on global-scale data analytics and machine learning solutions. In his current role at Google, he works closely with key GCP enterprise customers to provide them guidance on how to best use Google's cutting-edge machine learning products. At Google, he has also worked as part of the Area 120 incubator on building innovative data products such as Demand Signals, and he has been involved in the launch of Google products such as Time Series Insights. Before Google, he worked in similar roles at Microsoft and Deloitte.When not immersed in technology, he loves spending time with his wife and two daughters, reading books, watching movies, and exploring the scenic trails of southern California.He holds a bachelor's degree in electronics engineering from Jamia Millia Islamia University in India and an MBA from the University of California Los Angeles (UCLA) Anderson School of Management.Kartik Chaudhary is an AI enthusiast, educator, and ML professional with 6+ years of industry experience. He currently works as a senior AI engineer with Google to design and architect ML solutions for Google's strategic customers, leveraging core Google products, frameworks, and AI tools. He previously worked with UHG, as a data scientist, and helped in making the healthcare system work better for everyone. Kartik has filed nine patents at the intersection of AI and healthcare.Kartik loves sharing knowledge and runs his own blog on AI, titled Drops of AI.Away from work, he loves watching anime and movies and capturing the beauty of sunsets.

This article is an excerpt from the book, Cracking the Data Engineering Interview, by Kedeisha Bryan, Taamir Ransome. The book is a practical guide that’ll help you prepare to successfully break into the data engineering role. The chapters cover technical concepts as well as tips for resume, portfolio, and brand building to catch the employer's attention, while also focusing on case studies and real-world interview questions.Introduction In the world of data engineering, SQL is the unsung hero that empowers us to store, manipulate, transform, and migrate data easily. It is the language that enables data engineers to communicate with databases, extract valuable insights, and shape data to meet their needs. Regardless of the nature of the organization or the data infrastructure in use, a data engineer will invariably need to use SQL for creating, querying, updating, and managing databases. As such, proficiency in SQL can often the difference between a good data engineer and a great one. Whether you are new to SQL or looking to brush up your skills, this chapter will serve as a comprehensive guide. By the end of this chapter, you will have a solid understanding of SQL as a data engineer and be prepared to showcase your knowledge and skills in an interview setting. In this article, we will cover the following topics: Must-know foundational SQL concepts Must-know advanced SQL concepts Technical interview questions Must-know foundational SQL concepts In this section, we will delve into the foundational SQL concepts that form the building blocks of data engineering. Mastering these fundamental concepts is crucial for acing SQL-related interviews and effectively working with databases. Let’s explore the critical foundational SQL concepts every data engineer should be comfortable with, as follows: SQL syntax: SQL syntax is the set of rules governing how SQL statements should be written. As a data engineer, understanding SQL syntax is fundamental because you’ll be writing and reviewing SQL queries regularly. These queries enable you to extract, manipulate, and analyze data stored in relational databases. SQL order of operations: The order of operations dictates the sequence in which each of the following operators is executed in a query: FROM and JOIN WHERE GROUP BY HAVING SELECT DISTINCT ORDER BY LIMIT/OFFSET Data types: SQL supports a variety of data types, such as INT, VARCHAR, DATE, and so on. Understanding these types is crucial because they determine the kind of data that can be stored in a column, impacting storage considerations, query performance, and data integrity. As a data engineer, you might also need to convert data types or handle mismatches. SQL operators: SQL operators are used to perform operations on data. They include arithmetic operators (+, -, *, /), comparison operators (>, <, =, and so on), and logical operators (AND, OR, and NOT). Knowing these operators helps you construct complex queries to solve intricate data-related problems. Data Manipulation Language (DML), Data Definition Language (DDL), and Data Control  Language (DCL) commands: DML commands such as SELECT, INSERT, UPDATE, and DELETE allow you to manipulate data stored in the database. DDL commands such as CREATE, ALTER, and DROP enable you to manage database schemas. DCL commands such as GRANT and REVOKE are used for managing permissions. As a data engineer, you will frequently use these commands to interact with databases. Basic queries: Writing queries to select, filter, sort, and join data is an essential skill for any data engineer. These operations form the basis of data extraction and manipulation. Aggregation functions: Functions such as COUNT, SUM, AVG, MAX, MIN, and GROUP BY are used to perform calculations on multiple rows of data. They are essential for generating reports and deriving statistical insights, which are critical aspects of a data engineer’s role. The following section will dive deeper into must-know advanced SQL concepts, exploring advanced techniques to elevate your SQL proficiency. Get ready to level up your SQL game and unlock new possibilities in data engineering! Must-know advanced SQL concepts This section will explore advanced SQL concepts that will elevate your data engineering skills to the next level. These concepts will empower you to tackle complex data analysis, perform advanced data transformations, and optimize your SQL queries. Let’s delve into must-know advanced SQL concepts, as follows: Window functions: These do a calculation on a group of rows that are related to the current row. They are needed for more complex analyses, such as figuring out running totals or moving averages, which are common tasks in data engineering. Subqueries: Queries nested within other queries. They provide a powerful way to perform complex data extraction, transformation, and analysis, often making your code more efficient and readable. Common Table Expressions (CTEs): CTEs can simplify complex queries and make your code more maintainable. They are also essential for recursive queries, which are sometimes necessary for problems involving hierarchical data. Stored procedures and triggers: Stored procedures help encapsulate frequently performed tasks, improving efficiency and maintainability. Triggers can automate certain operations, improving data integrity. Both are important tools in a data engineer’s toolkit. Indexes and optimization: Indexes speed up query performance by enabling the database to locate data more quickly. Understanding how and when to use indexes is key for a data engineer, as it affects the efficiency and speed of data retrieval. Views: Views simplify access to data by encapsulating complex queries. They can also enhance security by restricting access to certain columns. As a data engineer, you’ll create and manage views to facilitate data access and manipulation. By mastering these advanced SQL concepts, you will have the tools and knowledge to handle complex data scenarios, optimize your SQL queries, and derive meaningful insights from your datasets. The following section will prepare you for technical interview questions on SQL. We will equip you with example answers and strategies to excel in SQL-related interview discussions. Let’s further enhance your SQL expertise and be well prepared for the next phase of your data engineering journey. Technical interview questions This section will address technical interview questions specifically focused on SQL for data engineers. These questions will help you demonstrate your SQL proficiency and problem-solving abilities. Let’s explore a combination of primary and advanced SQL interview questions and the best methods to approach and answer them, as follows: Question 1: What is the difference between the WHERE and HAVING clauses? Answer: The WHERE clause filters data based on conditions applied to individual rows, while the HAVING clause filters data based on grouped results. Use WHERE for filtering before aggregating data and HAVING for filtering after aggregating data. Question 2: How do you eliminate duplicate records from a result set? Answer: Use the DISTINCT keyword in the SELECT statement to eliminate duplicate records and retrieve unique values from a column or combination of columns. Question 3: What are primary keys and foreign keys in SQL? Answer: A primary key uniquely identifies each record in a table and ensures data integrity. A foreign key establishes a link between two tables, referencing the primary key of another table to enforce referential integrity and maintain relationships. Question 4: How can you sort data in SQL? Answer: Use the ORDER BY clause in a SELECT statement to sort data based on one or more columns. The ASC (ascending) keyword sorts data in ascending order, while the DESC (descending) keyword sorts it in descending order. Question 5: Explain the difference between UNION and UNION ALL in SQL. Answer: UNION combines and removes duplicate records from the result set, while UNION ALL combines all records without eliminating duplicates. UNION ALL is faster than UNION because it does not involve the duplicate elimination process. Question 6: Can you explain what a self join is in SQL? Answer: A self join is a regular join where a table is joined to itself. This is often useful when the data is related within the same table. To perform a self join, we have to use table aliases to help SQL distinguish the left from the right table. Question 7: How do you optimize a slow-performing SQL query? Answer: Analyze the query execution plan, identify bottlenecks, and consider strategies such as creating appropriate indexes, rewriting the query, or using query optimization techniques such as JOIN order optimization or subquery optimization.  Question 8: What are CTEs, and how do you use them? Answer: CTEs are temporarily named result sets that can be referenced within a query. They enhance query readability, simplify complex queries, and enable recursive queries. Use the WITH keyword to define CTEs in SQL. Question 9: Explain the ACID properties in the context of SQL databases. Answer: ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. These are basic properties that make sure database operations are reliable and transactional. Atomicity makes sure that a transaction is handled as a single unit, whether it is fully done or not. Consistency makes sure that a transaction moves the database from one valid state to another. Isolation makes sure that transactions that are happening at the same time don’t mess with each other. Durability makes sure that once a transaction is committed, its changes are permanent and can survive system failures. Question 10: How can you handle NULL values in SQL? Answer: Use the IS NULL or IS NOT NULL operator to check for NULL values. Additionally, you can use the COALESCE function to replace NULL values with alternative non-null values. Question 11: What is the purpose of stored procedures and functions in SQL? Answer: Stored procedures and functions are reusable pieces of SQL code encapsulating a set of SQL statements. They promote code modularity, improve performance, enhance security, and simplify database maintenance. Question 12: Explain the difference between a clustered and a non-clustered index. Answer: The physical order of the data in a table is set by a clustered index. This means that a table can only have one clustered index. The data rows of a table are stored in the leaf nodes of a clustered index. A non-clustered index, on the other hand, doesn’t change the order of the data in the table. After sorting the pointers, it keeps a separate object in a table that points back to the original table rows. There can be more than one non-clustered index for a table. Prepare for these interview questions by understanding the underlying concepts, practicing SQL queries, and being able to explain your answers. ConclusionThis article explored the foundational and advanced principles of SQL that empower data engineers to store, manipulate, transform, and migrate data confidently. Understanding these concepts has unlocked the door to seamless data operations, optimized query performance, and insightful data analysis. SQL is the language that bridges the gap between raw data and valuable insights. With a solid grasp of SQL, you possess the skills to navigate databases, write powerful queries, and design efficient data models. Whether preparing for interviews or tackling real-world data engineering challenges, the knowledge you have gained in this chapter will propel you toward success. Remember to continue exploring and honing your SQL skills. Stay updated with emerging SQL technologies, best practices, and optimization techniques to stay at the forefront of the ever-evolving data engineering landscape. Embrace the power of SQL as a critical tool in your data engineering arsenal, and let it empower you to unlock the full potential of your data. Author BioKedeisha Bryan is a data professional with experience in data analytics, science, and engineering. She has prior experience combining both Six Sigma and analytics to provide data solutions that have impacted policy changes and leadership decisions. She is fluent in tools such as SQL, Python, and Tableau.She is the founder and leader at the Data in Motion Academy, providing personalized skill development, resources, and training at scale to aspiring data professionals across the globe. Her other works include another Packt book in the works and an SQL course for LinkedIn Learning.Taamir Ransome is a Data Scientist and Software Engineer. He has experience in building machine learning and artificial intelligence solutions for the US Army. He is also the founder of the Vet Dev Institute, where he currently provides cloud-based data solutions for clients. He holds a master's degree in Analytics from Western Governors University.

DataPro is a weekly, expert-curated newsletter trusted by 120k+ global data professionals. Built by data practitioners, it blends first-hand industry experience with practical insights and peer-driven learning.Make sure to subscribe here so you never miss a key update in the data world. IntroductionIn an era dominated by the rise of artificial intelligence, the power and promise of Large Language Models (LLMs) stand distinct. These colossal architectures, designed to understand and generate human-like text, have revolutionized the realm of natural language processing. However, with great power comes great responsibility – the onus of managing, deploying, and refining these models in real-world scenarios. This article delves into the world of Large Language Model Operations (LLMOps), an emerging field that bridges the gap between the potential of LLMs and their practical application.BackgroundThe last decade has seen a significant evolution in language models, with models growing in size and capability. Starting with smaller models like Word2Vec and LSTM, we've advanced to behemoths like GPT-3, BERT, and T5.  With that said, as these models grew in size and complexity, so did their operational challenges. Deploying, maintaining, and updating these models requires substantial computational resources, expertise, and effective management strategies.MLOps vs LLMOpsIf you've ventured into the realm of machine learning, you've undoubtedly come across the term MLOps. MLOps, or Machine Learning Operations, encapsulates best practices and methodologies for deploying and maintaining machine learning models throughout their lifecycle. It caters to the wide spectrum of models that fall under the machine learning umbrella.On the other hand, with the growth of vast and intricate language models, a more specialized operational domain has emerged: LLMOps. While both MLOps and LLMOps share foundational principles, the latter specifically zeros in on the challenges and nuances of deploying and managing large-scale language models. Given the colossal size, data-intensive nature, and unique architecture of these models, LLMOps brings to the fore bespoke strategies and solutions that are fine-tuned to ensure the efficiency, efficacy, and sustainability of such linguistic powerhouses in real-world scenarios.Core Concepts of LLMOpsLarge Language Models Operations (LLMOps) focuses on the management, deployment, and optimization of large language models (LLMs). One of its foundational concepts is model deployment, emphasizing scalability to handle varied loads, reducing latency for real-time responses, and maintaining version control. As these LLMs demand significant computational resources, efficient resource management becomes pivotal. This includes the use of optimized hardware like GPUs and TPUs, effective memory optimization strategies, and techniques to manage computational costs.Continuous learning and updating, another core concept, revolve around fine-tuning models with new data, avoiding the pitfall of 'catastrophic forgetting', and effectively managing data streams for updates. Parallelly, LLMOps emphasizes the importance of continuous monitoring for performance, bias, fairness, and iterative feedback loops for model improvement. To cater to the vastness of LLMs, model compression techniques like pruning, quantization, and knowledge distillation become crucial.How do LLMOps workPre-training Model DevelopmentLarge Language Models typically start their journey through a process known as pre-training. This involves training the model on vast amounts of text data. The objective during this phase is to capture a broad understanding of language, learning from billions of sentences and paragraphs. This foundational knowledge helps the model grasp grammar, vocabulary, factual information, and even some level of reasoning.This massive-scale training is what makes them "large" and gives them a broad understanding of language. Optimization & CompressionModels trained to this extent are often so large that they become impractical for daily tasks.To make these models more manageable without compromising much on performance, techniques like model pruning, quantization, and knowledge distillation are employed.Model Pruning: After training, pruning is typically the first optimization step. This begins with trimming model weights and may advance to more intensive methods like neuron or channel pruning.Quantization: Following pruning, the model's weights, and potentially its activations, are streamlined. Though weight quantization is generally a post-training process, for deeper reductions, such as very low-bit quantization, one might adopt quantization-aware training from the beginning.Additional recommendations are:Optimizing the model specifically for the intended hardware can elevate its performance. Before initiating training, selecting inherently efficient architectures with fewer parameters is beneficial. Approaches that adopt parameter sharing or tensor factorization prove advantageous. For those planning to train a new model or fine-tune an existing one with an emphasis on sparsity, starting with sparse training is a prudent approach.Deployment Infrastructure After training and compressing our LLM, we will be using technologies like Docker and Kubernetes to deploy models scalably and consistently. This approach allows us to flexibly scale using as many pods as needed. Concluding the deployment process, we'll implement edge deployment strategies. This positions our models nearer to the end devices, proving crucial for applications that demand real-time responses.Continuous Monitoring & FeedbackThe process starts with the Active model in production. As it interacts with users and as language evolves, it can become less accurate, leading to the phase where the Model becomes stale as time passes.To address this, feedback and interactions from users are captured, forming a vast range of new data. Using this data, adjustments are made, resulting in a New fine-tuned model.As user interactions continue and the language landscape shifts, the current model is replaced with the new model. This iterative cycle of deployment, feedback, refinement, and replacement ensures the model always stays relevant and effective.Importance and Benefits of LLMOpsMuch like the operational paradigms of AIOps and MLOps, LLMOps brings a wealth of benefits to the table when managing Large Language Models.MaintenanceAs LLMs are computationally intensive. LLMOps streamlines their deployment, ensuring they run smoothly and responsively in real-time applications. This involves optimizing infrastructure, managing resources effectively, and ensuring that models can handle a wide variety of queries without hiccups.Consider the significant investment of effort, time, and resources required to maintain Large Language Models like Chat GPT, especially given its vast user base.Continuous ImprovementLLMOps emphasizes continuous learning, allowing LLMs to be updated with fresh data. This ensures that models remain relevant, accurate, and effective, adapting to the evolving nature of language and user needs.Building on the foundation of GPT-3, the newer GPT-4 model brings enhanced capabilities. Furthermore, while ChatGPT was previously trained on data up to 2021, it has now been updated to encompass information through 2022.It's important to recognize that constructing and sustaining large language models is an intricate endeavor, necessitating meticulous attention and planning.ConclusionThe ascent of Large Language Models marks a transformative phase in the evolution of machine learning. But it's not just about building them; it's about harnessing their power efficiently, ethically, and sustainably. LLMOps emerge as the linchpin, ensuring that these models not only serve their purpose but also evolve with the ever-changing dynamics of language and user needs. As we continue to innovate, the principles of LLMOps will undoubtedly play a pivotal role in shaping the future of language models and their place in our digital world.Author BioMostafa Ibrahim is a dedicated software engineer based in London, where he works in the dynamic field of Fintech. His professional journey is driven by a passion for cutting-edge technologies, particularly in the realms of machine learning and bioinformatics. When he's not immersed in coding or data analysis, Mostafa loves to travel.Medium

Our Data Engineering Byte Newsletter gives data engineers and practitioners what they often lack today: clear, real-world insights—where every byte tells a story.Subscribe here to stay ahead in data engineeringIntroductionChatGPT is an efficient language that may be used in a range of tasks, including the creation of SQL queries. In this article, you will get to know how effectively you will be able to use SQL queries by using ChatGPT to optimize and craft them correctly to get perfect results.It is necessary to have sufficient SQL knowledge before you can use ChatGPT for the creation of SQL queries. The language that the databases are communicating with is SQL. This is meant to be used for the production, reading, updating, and deletion of data from databases. SQL is the most specialized language in this domain. It's one of the main components in a lot of existing applications because it deals with structured data that can be retrieved from tables.There are a number of different SQL queries, but some more common ones include the following:SELECT: It will select data from a database.INSERT: It will insert new data into a database.UPDATE: This query will update the existing data in a database.DELETE: This query is used to delete data from a database.Using ChatGPT to write SQL queriesOnce you have a basic understanding of SQL, you can start using ChatGPT to write SQL queries. To do this, you need to provide ChatGPT with a description of the query that you want to write. After that, ChatGPT will generate the SQL code for you.For example, you could just give ChatGPT the query below to write an SQL query to select all of the customers in your database.Select all of the customers in my databaseFollowing that, ChatGPT will provide the SQL code shown below:SELECT * FROM customers;The customer table's entire set of columns will be selected by this query. Additionally, ChatGPT can be used to create more complex SQL statements.How to Use ChatGPT to Describe Your IntentionsNow let’s have a look at some examples where we will ask ChatGPT to generate SQL code by asking it queries from our side.For Example:We'll be creating a sample database for ChatGPT, so we can ask them to set up restaurant databases and two tables.ChatGPT prompt:Create a sample database with two tables: GuestInfo and OrderRecords. The GuestInfo table should have the following columns: guest_id, first_name, last_name, email_address, and contact_number. The OrderRecords table should have the following columns: order_id, guest_id, product_id, quantity_ordered, and order_date.ChatGPT SQL Query Output:We requested that ChatGPT create a database and two tables in this example. After it generated a SQL query. The following SQL code is to be executed on the Management Studio software for SQL Server. As we are able to see the code which we got from ChatGPT successfully got executed in the SSMS Database software.How ChatGPT Can Be Used for Optimizing, Crafting, and Debugging Your QueriesSQL is an efficient tool to manipulate and interrogate data in the database. However, in particular, for very complex datasets it may be difficult to write efficient SQL queries. The ChatGPT Language Model is a robust model to help you with many tasks, such as optimizing SQL queries.Generating SQL queriesThe creation of SQL queries from Natural Language Statements is one of the most common ways that ChatGPT can be used for SQL optimization. Users who don't know SQL may find this helpful, as well as users who want to quickly create the query for a specific task.For example, you could ask for ChatGPT in the following way:Generate an SQL query to select all customers who have placed an order in the last month.ChatGPT would then generate the following query:SELECT * FROM customers WHERE order_date >= CURRENT_DATE - INTERVAL 1 MONTH;Optimizing existing queriesThe optimization of current SQL queries can also be achieved with ChatGPT. You can do this by giving ChatGPT the query that you want improved performance of and it will then suggest improvements to your query.For example, you could ask for ChatGPT in the following way:SELECT * FROM products WHERE product_name LIKE '%shirt%';ChatGPT might suggest the following optimizations:Add an index to the products table on the product_name column.Use a full-text search index on the product_name column.Use a more specific LIKE clause, such as WHERE product_name = 'shirt' if you know that the product name will be an exact match.Crafting queriesBy providing an interface between SQL and Natural Language, ChatGPT will be able to help with the drafting of complicated SQL queries. For users who are not familiar with SQL and need to create a quick query for a specific task, it can be helpful.For Example:Let's say we want to know which customers have placed an order within the last month, and spent more than $100 on it, then write a SQL query. The following query could be generated by using ChatGPT:SELECT * FROM customers WHERE order_date >= CURRENT_DATE - INTERVAL 1 MONTH AND order_total > 100;This query is relatively easy to perform, but ChatGPT can also be used for the creation of more complicated queries. For example, to select all customers who have placed an order in the last month and who have purchased a specific product, we could use ChatGPT to generate a query.SELECT * FROM customers WHERE order_date >= CURRENT_DATE - INTERVAL 1 MONTH AND order_items LIKE '%product_name%';Generating queries for which more than one table is involved can also be done with ChatGPT. For example, to select all customers who have placed an order in the last month and have also purchased a specific product from a specific category, we could use ChatGPT to generate a query.SELECT customers.*FROM customersINNER JOIN orders ON customers.id = orders.customer_idINNER JOIN order_items ON orders.id = order_items.order_idWHERE order_date >= CURRENT_DATE - INTERVAL 1 MONTHAND order_items_product_id = (SELECT id FROM products WHERE product_name = 'product_name')AND product_category_id = (SELECT id FROM product_categories WHERE category_name = 'category_name');The ChatGPT tool is capable of providing assistance with the creation of complex SQL queries. The ChatGPT feature facilitates users' writing efficient and accurate queries by providing an interface to SQL in a natural language.Debugging SQL queriesFor debugging SQL queries, the ChatGPT can also be used. To get started, you can ask ChatGPT to deliver a query that does not return the anticipated results. It will try to figure out why this is happening.For example, you could ask for ChatGPT in the following way:SELECT * FROM customers WHERE country = 'United States';Let's say that more results than expected are returned by this query. If there are multiple rows in a customer table, or the country column isn't being populated correctly for all clients, ChatGPT may suggest that something is wrong.How ChatGPT can help diagnose SQL query errors and suggest potential fixesYou may find that ChatGPT is useful for diagnosing and identifying problems, as well as suggesting possible remedies when you encounter errors or unexpected results in your SQL queries.To illustrate how ChatGPT could help you diagnose and correct SQL queries, we'll go over a hands-on example.Scenario: You'll be working with a database for Internet store transactions. The 'Products' table is where you would like to see the total revenue from a specific product named "Laptop". But you'll get unexpected results while running a SQL query.Your SQL Query:SELECT SUM(price) AS total_revenue FROM Products WHERE product_name = 'Laptop'; Issue: The query is not providing the expected results. You're not sure what went wrong.ChatGPT Assistance:Diagnosing the Issue:You can ask ChatGPT something like, "What could be the issue with my SQL query to calculate the total revenue of 'Laptop' from the Products table?"ChatGPT’s Response:The ChatGPT believes that the problem may arise from a WHERE clause. It suggests that because the names of products may not be distinctive, and there might be a lot of entries called 'Laptops', it is suggested to use ProductID rather than the product name. This query could be modified as follows:SELECT SUM(price) AS total_revenue FROM Products WHERE product_id = (SELECT product_id FROM Products WHERE product_name = 'Laptop');Explanation and Hands-on Practice:The reasoning behind this adjustment is explained by ChatGPT. In order to check if the revised query is likely to lead to an expected overall profit for a 'Laptop' product, you can then try running it.SELECT SUM(price) AS total_revenue FROM Products WHERE product_id = (SELECT product_id FROM Products WHERE product_name = 'Laptop');We have obtained the correct overall revenue from a 'Laptop' product with this query, which has resolved your unanticipated results issue.This hands-on example demonstrates how ChatGPT can help you diagnose and resolve your SQL problems, provide tailored suggestions, explain the solutions to fix them, and guide you through the process of strengthening your SQL skills by using practical applications.ConclusionIn conclusion, this article provides insight into the important role that ChatGPT plays when it comes to generating efficient SQL queries. In view of the key role played by SQL in database management for structured data, which is essential to modern applications, it stressed that there should be a solid knowledge base on SQL so as to effectively use ChatGPT when creating queries. We explored how ChatGPT could help you generate, optimize, and analyze SQL queries by presenting practical examples and use cases.It explains to users how ChatGPT is able to diagnose SQL errors and propose a solution, which in the end can help them solve unforeseen results and improve their ability to use SQL. In today's data-driven world where effective data manipulation is a necessity, ChatGPT becomes an essential ally for those who seek to speed up the SQL query development process, enhance accuracy, and increase productivity. It will open up new possibilities for data professionals and developers, allowing them to interact more effectively with databases.If you want to learn more about SQL, you can read this book: SQL for Data AnalyticsThis book goes beyond basic SQL and teaches you how to analyze real-world data using advanced techniques like joins, window functions, and statistical analysis. It includes hands-on exercises and case studies to help you build practical, job-ready data analytics skills.Author BioChaitanya Yadav is a data analyst, machine learning, and cloud computing expert with a passion for technology and education. He has a proven track record of success in using technology to solve real-world problems and help others to learn and grow. He is skilled in a wide range of technologies, including SQL, Python, data visualization tools like Power BI, and cloud computing platforms like Google Cloud Platform. He is also 22x Multicloud Certified.In addition to his technical skills, he is also a brilliant content creator, blog writer, and book reviewer. He is the Co-founder of a tech community called "CS Infostics" which is dedicated to sharing opportunities to learn and grow in the field of IT.