Data Engineering

Just about every developer I know has the same story about their first generative AI project. They spin up a proof of concept using GPT-4 or Claude, get amazing results, and then watch their AWS bill explode when they try to scale. The promise of AI inevitably meets the reality of infrastructure costs, and suddenly that revolutionary feature becomes a budget line item nobody wants to defend. For many engineering teams, there’s an alternative. Instead of defaulting to the biggest, most powerful models available, more engineering teams are discovering that small language models (SLMs) can deliver 90% of the value at 10% of the cost. The math is compelling, but the implementation story is arguably even better. Here’s what to know about shrinking your model to maximize your results. The Increasingly Less Hidden Costs of Going Big Running a single inference on GPT-4 costs roughly 100x what the same query costs on a well-tuned SLM. When you’re processing thousands of requests per day, that difference transforms from a rounding error to a runway killer. One startup I spoke with was burning $30,000 monthly on OpenAI API calls for their customer support bot. After switching to a custom SLM, they cut that to $2,000 while actually improving response relevance. The infrastructure requirements tell a similar story. Training a large model requires clusters of A100 GPUs that most companies can’t afford to own or rent. An SLM can train on a single high-end GPU in days rather than months. For dev teams, that means faster iteration cycles and the ability to actually experiment without filing a purchase order. Beyond pure economics, there’s the latency issue. Large models introduce unavoidable delays that compound across distributed systems. SLMs can run inference in milliseconds rather than seconds, enabling real-time applications that would be impossible with their much larger cousins. Shipping a Real Competitive Advantage Through Specialization The real insight about SLMs isn’t that they’re cheaper, but that they can be better for specific use cases. When you train a model on your domain-specific data rather than, well, the entire internet, you get responses that actually understand your business context. Consider a fintech company building a compliance checking system. A general-purpose LLM knows about financial regulations in theory but struggles with the nuances of specific reporting requirements. An SLM trained on actual compliance documents and past rulings becomes an expert in exactly what matters to that business. The smaller model isn’t just more efficient, it’s more accurate for the task you actually want to use it for. What I’ve been seeing among successful engineering teams is the use of SLMs as specialized tools in their AI toolkit. They might have one model for code review comments, another for documentation generation, and a third for analyzing system logs. Each model excels at its specific task because it’s never trying to be everything to everyone. The Security Argument Nobody Talks About Here’s what keeps your CTO up at night about cloud-based LLMs: every API call sends your data to someone else’s servers. That customer information, that proprietary code, those internal documents all become training data for models your competitors will use tomorrow. My colleague Jon Nordmark talks here about why optionality and privacy are critical for enterprise AI. SLMs flip the security model entirely. You can run them on-premise or in your own cloud environment, and your data never leaves your control. For companies in regulated industries or those with serious IP concerns, that will increasingly be the difference between adopting AI and watching from the sidelines. As a case in point, I spoke with a healthcare startup that couldn’t use any cloud-based LLM due to HIPAA requirements. But by deploying an SLM within their own infrastructure, they could build AI features their venture-backed competitors couldn’t touch. In essence, privacy became their competitive advantage. Getting Started With SLMs The best part about the SLM approach is how accessible it’s become. Frameworks make it straightforward to fine-tune existing small models for your use case, so you don’t need a PhD in machine learning or a team of research scientists. A competent developer can have a custom model running in production within weeks. Start by identifying a narrow, well-defined problem where AI could help (for example, categorizing support tickets or extracting information from documents). Build a dataset of a few thousand examples specific to your use case, then fine-tune a base model like BERT or a small GPT variant on your data. Finally, deploy it behind your existing API infrastructure. The results might surprise you. For narrow, well-defined tasks, these specialized models often outperform LLMs while running on hardware you already have. As your team gains confidence, you can expand to more use cases, building a suite of specialized models that work together. The Path Forward The AI industry wants you to believe that bigger is always better. That narrative sells more GPUs and cloud compute time. But for most businesses, the future of AI isn’t about chasing the largest models so much as it is building the right models for your specific needs. What we’ll continue to see more of is a new generation of AI applications that are fast, focused, and actually profitable. They’re built by teams who understand that sustainable AI adoption means making smart choices about when to use small models and when to reach for larger ones. Your next AI project doesn’t need to break the bank, but it probably does need to solve a real problem for your users. Start small, measure results, and scale what works. That approach has worked for every other technology wave, and there’s no reason AI should be different.

While leadership debates AI strategy, there's a growing divide in development teams: junior developers are shipping features faster with Cursor and GitHub Copilot, while senior engineers question whether AI-assisted code is maintainable at scale and can often be found criticizing the junior devs for their use of AI If you're a tech lead, architect, or senior developer, navigating this transition is not easy. The Technical Reality: AI Tools in Production Codebases Let's address the elephant in the room: AI coding tools are not magic, but they're not gimmicks either: GitHub Copilot excels at: Boilerplate generation (API endpoints, test scaffolding, configuration files)Pattern completion within established codebasesConverting comments to implementation (surprisingly effective for complex business logic) Cursor shines for: Rapid prototyping and MVP developmentRefactoring large functions with context awarenessMulti-file edits that maintain consistency across modules Claude Code (and similar CLI tools) prove valuable for: Legacy code analysis and documentation generationArchitectural decision support with codebase-wide contextCode review assistance with security and performance insights The Legacy Code Challenge: Where AI Meets Reality Here's where things get technically interesting. Your large legacy codebase presents unique challenges that generic AI advice doesn't address: Context Window Limitations Most AI tools work with limited context. When working with legacy systems, you need strategies for providing relevant context: bash # Using Claude Code for legacy analysis claude-code analyze --include="*.js,*.ts" --exclude="node_modules,dist" \ --focus="business-logic" --output="architecture-overview.md" Gradual Integration Patterns Don't attempt to AI-ify your entire codebase overnight. Instead, establish AI-friendly boundaries: New feature branches: Start fresh with AI-assisted developmentIsolated modules: Refactor self-contained components with AI assistanceDocumentation layer: Use AI to generate comprehensive docs for undocumented legacy code The Documentation Multiplier Effect AI tools amplify existing documentation quality. Poor docs lead to poor AI suggestions. Good docs enable AI to understand business context and suggest architecturally sound solutions. Before implementing AI tools extensively, audit your README files, inline comments, and API documentation. The investment pays dividends in AI effectiveness. Technical Implementation: Beyond Tool Selection Context Engineering for Developers Effective AI usage requires thinking like a compiler. Be explicit about: Context boundaries: "This function handles user authentication for a multi-tenant SaaS platform"Constraints: "Maintain backward compatibility with API v2.1"Performance requirements: "Optimize for <100ms response time"Security considerations: "Ensure OWASP compliance for user input validation" Code Review with AI Assistance Integrate AI into your review process without replacing human judgment: YAML # .github/workflows/ai-review.yml name: AI Code Review on: [pull_request] jobs: ai-review: runs-on: ubuntu-latest steps: - uses: actions/checkout@v3 - name: AI Security Scan run: claude-code review --security --performance --maintainability Testing AI-Generated Code AI-generated code requires different testing approaches: Boundary testing: AI sometimes makes assumptions about input rangesIntegration testing: Verify AI code plays well with existing patternsPerformance profiling: AI-optimized code isn't always performance-optimized code Addressing Senior Developer Concerns (With Technical Solutions) "AI Code Lacks Architectural Understanding" Reality: Partially true. AI excels at local optimization but struggles with system-wide architectural decisions. Solution: Use AI for implementation, keep humans driving architecture. Establish clear interfaces and let AI fill in the implementation details. "AI Creates Technical Debt" Reality: AI can create debt if used incorrectly, but it can also help pay it down. Technical approach: bash # Use AI to identify and document technical debt claude-code debt-analysis --scan-patterns="TODO,FIXME,HACK" \ --complexity-threshold=15 --output="debt-report.json" "AI-Generated Code Is Hard to Debug" Reality: True if you don't establish debugging practices for AI-assisted development. Best practices: Always review AI-generated code before committing.Add explanatory comments to complex AI suggestions.Maintain conventional error handling patterns.Use AI to generate comprehensive test cases. The Skills Evolution Framework Rather than creating AI-elite teams, focus on evolving existing skills: Level 1: AI Tool Literacy Understanding when to use which AI toolBasic context engineering for code generationCode review of AI-generated content Level 2: AI-Augmented Development Complex prompting for architectural tasksAI-assisted debugging and optimizationIntegration of AI tools into existing workflows Level 3: AI-First Architecture Designing systems that leverage AI for ongoing maintenanceBuilding AI-friendly codebases with excellent documentationLeading AI-augmented teams effectively Measuring Success: Beyond Velocity Metrics Traditional metrics don't capture AI impact effectively. Track: Code quality indicators: Reduced bug density, improved test coverageDeveloper satisfaction: Are AI tools reducing friction or creating it?Knowledge transfer efficiency: How quickly can new team members contribute?Technical debt reduction: Is AI helping pay down legacy complexity? The Technical Bottom Line AI tools are becoming as essential to development as IDEs and version control. The question isn't whether to adopt them, but how to integrate them without compromising code quality or team dynamics. Start small, measure impact, and scale what works. Your legacy codebase will still need human expertise, but AI can help bridge the knowledge gap between junior enthusiasm and senior experience. The developers who adapt to this AI-augmented world won't be those who resist the tools or those who blindly accept every suggestion. They'll be the ones who understand how to leverage AI while maintaining the engineering discipline that makes software maintainable at scale.

After years of working from the comfort of Python and Django, I moved to the wild asynchronous world of FastAPI to improve latency in web-based AI applications. I started with FastAPI and built an open-source stack called FastOpp, which adds command-line and web tools similar to Django. Initially, things went smoothly using SQLite and aiosqlite to add AsyncIO to SQLite. I used SQLAlchemy as my Object Relational Mapper (ORM) and Alembic as the database migration tool. Everything seemed to work easily, so I added a Python script to make things similar to Django’s migrate.py. As things were going smoothly, I added Pydantic for data validation and connected Pydantic to the SQLAlchemy models with SQLModel. Although I was pulling in open source packages that I wasn’t that familiar with, the packages were popular, and I didn’t have problems during initial use. Django comes with an opinionated stack of stable, time-tested tools, which I was starting to miss. However, I was really attracted to FastAPI features such as auto-documentation of APIs and the async-first philosophy. I continued forward by integrating SQLAdmin for a pre-configured web admin panel for SQLAlchemy. I also implemented FastAPIUsers. At this point, I ran into problems using FastAPIUsers in the same way I used Django authentication. I got my first glimpse of the complex world outside of the Django comprehensive ecosystem. I needed to implement my own JWT authentication and used FastAPIUsers as the hash mechanism. The FastAPI project has a full-stack-fastapi-template that I assessed as a starting point. I chose not to use it since my primary goal was focused on using Jinja2Templates for a streaming application from an LLM with heavy database use, both SQL and eventually a vector database using pgvector with PostgreSQL, or for simpler deployments, FAISS with SQLite and the FTS5 extension. My goal is to provide a more Django-like experience for FastAPI and provide the opportunity in the future to use the built-in API and auto-documentation of FastAPI instead of implementing something like Django REST framework, which I've found difficult to set up for automatic documentation of the API endpoints. I've considered for a long time whether it’s better to just use Django with asyncio from the beginning and not build a Django-like interface around FastAPI. In Django 6, there is some support for background tasks. My primary motivation for moving to FastAPI occurred when I was using Django for asynchronous communication with LLM endpoints. Although Django works fine with asynchronous communication, its default synchronous communication style created a number of problems for me. For average people like me, it’s difficult to keep a method asynchronous and not have any synchronous calls to other libraries that might be synchronous or other synchronous communication channels, like a database access. At this point, I wanted to simplify my code architecture and committed to FastAPI and make my code asynchronous from the beginning. It seemed simple. I thought I just needed to use an asynchronous driver with PostgreSQL and everything would work. I was wrong. Problems Moving to Asynchronous Database Connections psycopg2, psycopg3, or asyncpg The default way to connect to Python for many people is psycopg2. This is a very proven way. It is the default usage in most Django applications. Unfortunately, it is synchronous. The most common asynchronous PostgreSQL connector is asyncpg, but I couldn't get it to work in my deployment to Leapcell. As Leapcell had a psycopg2 example for SQLAlchemy, I used psycopg2 and rewrote the database connection to be synchronous while keeping everything around the connection asynchronous. As the latency with the LLM is much higher than the latency with the database, this seemed like a reasonable solution at the time. I just had to wait for the database to send me back the response, and then I was free to deal with other asynchronous problems, such as LLM query and Internet search status updates. The database latency was likely going to be less than 1,500ms in most queries, which was okay for my application. Using a synchronous connection to the database is great in theory, and I’m sure that other, more experienced Python developers can easily solve this problem and keep the synchronous and asynchronous code nicely separated with clean use of async and await. However, I ran into problems with organizing my code to use synchronous connections to the database within asynchronous methods that were talking to the LLM and storing the history in the database. As I was familiar with async/await from using Dart for many years, I was surprised I was having these problems. The problem I had might have been due to my lack of experience in understanding which pre-made Python modules were sending back synchronous versus asynchronous responses. I think that other Python developers might be able to understand my pain. To keep to an asynchronous database connection for both SQLite and PostgreSQL, I moved from the synchronous psycopg2 to asyncpg. SSL Security Not Needed in SQLite, But Needed in PostgreSQL Production The asyncpg connector worked fine in development, but not in production. Although establishing an SSL network connection seems obvious, I didn’t really appreciate this because I had been deploying to sites like Fly.io, Railway, and DigitalOcean Droplets with SQLite. For small prototype applications, SQLite works surprisingly well with FastAPI. I was trying to deploy to the free hobby tier of Leapcell to set up a tutorial for students who didn’t want to pay or didn’t want to put their credit card into a hosting service to go through a tutorial. There’s no way to write to the project file system on the Leapcell service engine. Leapcell recommends using Object Storage and PostgreSQL for persistent data. They do offer a free tier that is pretty generous for PostgreSQL. Leapcell requires SSL communication between their PostgreSQL database and their engine, which they call the service. Unfortunately, the syntax is different for the SSL mode between psycopg2 and asyncpg. I couldn’t just add ?sslmode=require to the end of the connection URL. Leapcell did not have an example for asyncpg. Likely due to my limited skills, I wasn’t able to modify my application completely enough to put the SSL connections in all the required places. In order to just use the URL connection point with sslmode=require, I decided to use psycopg3. Prepared Statements Caused Application to Crash With SQLAlchemy As I needed to use an async ORM in Python, I used SQLAlchemy. I didn’t have too much experience with it initially. I didn’t realize that even though I wasn’t making prepared statements in my Python application, the communication process between psycopg and PostgreSQL was storing prepared statements. Due to the way the connections were pooled on Leapcell, I had to disable the prepared statements. It took me a while to isolate the problem and then implement the fix. The problem never occurred when using SQLite because SQLite runs prepared statements in the same process using the same memory space as the Python program. This is different from PostgreSQL, where the network and session state can change. As I was worried about the performance impact of disabling prepared statements, I did some research, and it appears that SQLAlchemy does statement caching on the Python side. The real-world impact of disabling the prepared statement in PostgreSQL appears to be negligible. Summary Using SQLite in asynchronous mode has been quite easy. Getting PostgreSQL to work has been more difficult. There were three areas that I had trouble with for PostgreSQL: Asynchronous connection – how to write asynchronous Python code effectively to await the return data.Security – how to deal with both SQLite, which doesn’t require an SSL, and PostgreSQL in production, which does require an SSL.Prepared statements – I needed to learn to rely on the SQLAlchemy statement caching instead of the built-in prepared statements on the PostgreSQL server. I like FastAPI, and there are many huge advantages to using it that I got in the first hour of use. I’m going to continue using it instead of Django. However, I’m starting to really appreciate how much Django shielded me from much of the infrastructure setup for my applications. FastAPI is unopinionated in areas such as the database, connectors, authentication, and models. I find it difficult to gain expertise in any one area. Thus, I am focusing on a smaller set of open source components that work with FastAPI to gain a deeper understanding of their use. I feel that many other Python developers are on a similar journey to experiment more with asynchronous Python web applications. I would appreciate feedback and ideas on which open source components or techniques to use to build effective asynchronous AI applications. Resources FastOpp – Open source stack I am building around FastAPIFastAPI – A better Flask

By Craig Oda CORE

An API (Application package interface) is a software tool that enables researchers and developers to access some third-party data and functionality within a main software. Usually, it’s a collection of software commands that act as an interface to an external database. Web 3 APIs act as translators, enabling applications to interact with features like smart contracts and on-chain data, empowering you to harness the power of Web3 without diving deep into technical complexities. Various API categories —REST, SOAP, RPC, and WebSocket— offer unique strengths tailored to different use cases: REST APIs shine in usability, speed, and multichain support for NFT and DeFi apps.SOAP APIs cater to enterprises needing high-standard security and message integrity.RPC APIs are the go-to for smart contract interactions and node-level data.WebSocket APIs enable the real-time responsiveness required by modern decentralized applications. Choosing the right API architecture depends on your application’s specific needs—whether it's enterprise integration, real-time user interaction, or deep protocol-level communication. Representational State Transfer (REST) APIs in Web3 REST APIs use HTTP requests to perform operations. They are stateless, cacheable, and easy to integrate. REST is widely used in Web3 for blockchain explorers, NFT marketplaces, and decentralized finance (DeFi) platforms. REST API DropsTab API provides investment-focused analytics and token tracking tools. It's especially valuable for VC analysts, crypto researchers, and strategic investors. A powerful data aggregator offering real-time and historical metrics, with a strong focus on tokenomics and Token Unlocks via a commercial API. DropsTab API delivers specialized market intelligence tools for developers and analysts, making it ideal for financial products and research applications. It stands out for its advanced analytics, including detailed insights into investors, fundraising rounds, and token vesting schedules. This API is best for Investment analytics, token tracking, investment monitoring. Advantages: Deep, structured crypto data (market, unlocks, investors)Historical and real-time insightsIdeal for research, analytics, and investor tracking Use Cases: Token unlock trackingVC and funding round analysisMarket sentiment dashboards Endpoints: GET /tokenUnlocks: List of upcoming token unlock eventsGET /fundingRounds: Retrieves project funding historyGET /coins/detailed/{slug}: Detailed data for a specific cryptocurrency Moralis API Moralis provides easy onboarding for Web3 developers and eliminates the need to run full nodes. Advantages: Developer-friendlyCross-chain support (Ethereum, Polygon, BNB Chain)Powerful abstraction layer over raw blockchain data Use Cases: NFT platformsWallet trackingDeFi dashboards Endpoints: GET /nft/{address}: Get NFTs for a contractGET /balance/{address}: Fetch native token balancesGET /transaction/{txHash}: Get details of a transaction Alchemy API Alchemy's REST API is essential for developers needing performant access to Ethereum and EVM-compatible chains. Advantages: High reliability and speedEnhanced developer tools (Web3 SDKs, dashboards)Compatible with Ethereum and Layer 2 chains Use Cases: DApp developmentTransaction tracingNFT indexing Endpoints: GET /v2/{apiKey}/getNFTs: List NFTs owned by an addressGET /v2/{apiKey}/getAssetTransfers: Retrieve token transfer historyGET /v2/{apiKey}/getTokenMetadata: Metadata about an ERC-20 token Many more rest APIS exist as they are the most widely used but we only highlighted this three for brevity. Simple Object Access Protocol (SOAP) APIs in Web3 SOAP APIs are XML-based protocols for exchanging structured information over a network. Though less common in modern Web3 compared to REST or RPC, SOAP APIs are still used in some enterprise blockchain environments where high security, reliability, and transaction integrity are paramount. IBM Blockchain Platform API IBM uses WSDL (Web Services Description Language) for defining the service interface. While their modern platform supports REST and gRPC, SOAP is often employed in legacy enterprise integrations. Advantages: Enterprise-grade supportStrong security and transaction managementRobust error handling Use Cases: Supply chain transparencyIdentity verificationFinancial audits Endpoints (SOAP-style): QueryTransaction: Retrieves transaction records by IDSubmitTransaction: Submits a transaction to the networkGetBlockInfo: Gets block details by height or hash Oracle Blockchain Platform API This API uses SOAP over HTTPS and is often wrapped in enterprise middleware for access via SOAP-based enterprise service buses (ESBs). Advantages: Integrated with Oracle CloudStandardized message structuresHigh availability and enterprise features Use Cases: Corporate finance and ERP systemsBusiness process automationSmart contract governance Endpoints: ChaincodeInvoke: Triggers chaincode functionsChaincodeQuery: Queries the ledgerUserManagement: Adds or removes users from the network Remote Procedure Call (RPC) APIs in Web3 RPC APIs allow developers to invoke functions on remote servers (in this case, blockchain nodes) as if they were local. JSON-RPC is the most common protocol in Web3 for interacting with Ethereum-compatible blockchains. Infura JSON-RPC API Infura abstracts the complexity of maintaining a node and provides secure, high-availability access to Ethereum. Advantages: Scalable, managed Ethereum node infrastructureJSON-RPC standard complianceSupports Ethereum, IPFS, Arbitrum, Optimism, and more Use Cases: Smart contract interactionsDApp backend servicesBlockchain data queries Endpoints: eth_getBalance: Returns account balanceeth_sendRawTransaction: Broadcast a signed transactioneth_call: Calls a smart contract without broadcasting Chainstack RPC API Chainstack's multi-cloud infrastructure is a popular choice among blockchain enterprises needing performance and security. Advantages: Enterprise-grade infrastructurePrivate and shared node optionsSupports Ethereum, BNB Chain, Avalanche, and more Use Cases: Custom node hosting for DeFi and NFT appsOn-chain data extractionTransaction relaying and simulation Endpoints: eth_estimateGas: Estimates the gas required for a transactioneth_newFilter: Create a new event filtereth_getBlockByNumber: Fetches block data. WebSocket APIs in Web3 WebSocket APIs provide full-duplex communication channels over a single TCP connection. In Web3, WebSockets are crucial for real-time applications like DEXs, wallet updates, and gaming. NOWNodes WebSocket API NOWNodes offers WebSocket endpoints across major and niche blockchains, giving developers flexibility for real-time engagement. Advantages: Multichain support with real-time dataEasy integration with open-source librariesAffordable pricing tiers Use Cases: Cross-chain alertsNFT event trackingReal-time explorer data Endpoints: subscribeNewBlock: Fires when a new block is addedsubscribeTx: Fires on specific transaction eventssubscribeContractEvent: Tracks smart contract interactions Alchemy WebSocket API Alchemy’s WebSocket endpoints provide filtered event streaming with minimal overhead. Advantages: Real-time updates for transactions and blocksReduced latencyHigh reliability Use Cases: Front-end real-time alertsWallet balance notificationsLive NFT minting updates Endpoints: alchemy_pendingTransactions: Subscribe to new pending transactionsalchemy_newHeads: Subscribe to new block headersalchemy_filteredLogs: Subscribe to specific smart contract events Conclusion In the rapidly evolving Web3 landscape, APIs serve as vital bridges between decentralized networks and application developers. This concise comparison helps in choosing the right API style based on project needs. 1. Communication Style REST: Stateless, resource-based; uses HTTP methods (GET, POST, etc.)SOAP: Protocol-based; uses XML over HTTP/SMTP with strict standardsRPC (Remote Procedure Call): Function-based; typically uses JSON-RPC or XML-RPC for invoking procedures remotelyWebSocket: Full-duplex, persistent communication over a single TCP connection 2. Message Format REST: Flexible; commonly JSON or XMLSOAP: Strictly XML with an envelope structureRPC: JSON or XML, depending on implementationWebSocket: Binary or text frames; highly flexible and low-level 3. Use Cases REST: Web services, mobile apps, CRUD operationsSOAP: Enterprise applications, financial services (e.g., ACID compliance)RPC: Microservices, internal APIs where performance is keyWebSocket: Real-time apps like chat, gaming, live feeds 4. Performance REST: Good for standard web traffic; less overhead than SOAPSOAP: Heavy; verbose XML leads to higher latencyRPC: Fast; minimal overhead, especially in binary implementationsWebSocket: Excellent for continuous, low-latency communication 5. Security REST: HTTPS, OAuth; custom handling neededSOAP: Built-in standards (WS-Security)RPC: Varies; less standardizationWebSocket: Relies on underlying transport security (e.g., WSS) Take the time to define your use case, test a few APIs using free tiers or sandbox environments, and choose the provider that offers the best balance of data access, documentation, and long-term scalability.

Introduction: What Happens When APIs Start Thinking? Wondered what your backend might "think" about? Up until now, we have viewed LLMs (e.g., OpenAI's GPT series) as a code assistant or a chatbot. However, behind the scenes of those experiences is something that can take things to a much more impactful level: an AI-first backend experience. In this type of environment, APIs do not simply follow the pre-packaged flow, http status codes, or utility functions of a backend. Instead, they think, adapt, and develop logic dynamically at runtime based on LLMs. Imagine the API you are building does not adhere to the rigid flow of a flowchart or the meticulously precise steps of an HTTP post or get setup within your functionality. Rather, it responds and adapts logic based on the tone of the user, the prosody of the interaction, or state of the world (trends and behaviors at the time). Sounds like science fiction? Not anymore. Let's unpack how this works, why it will change the way you think about your applications, and how you can "test" it out today. What Is an AI-First Backend? What Does AI-First Mean? The primary difference is that an AI-first backend is NOT hard-coded business logic. With an AI-first backend, the business logic is dynamic - either based on an LLM prompt or user context. For example: Normal API: Plain Text POST /recommendations if (user.age > 18) show A; else show B; AI First API: Plain Text POST /recommendations // Let an LLM decide: "Please provide personalized product recommendations based on user's age, past purchases and mood based on the previous support ticket." The next big difference will be that traditional API uses compile-time logic, while AI-first API relies on runtime logic, where an LLM completes the logic based on the user's intent and context and generates the logic in real-time. Why Now? A few things have converged: LLMs are reliable and fast, even if you are not working in a "chat" environment. Natural language is very expressive, and we do not need to rely on writing code to describe logic in a different way. An increased demand for personalization from users: users expect experiences tailored to them to happen in real time. Real World Example: Helpdesk Routing that Learns The Situation While building help desks, the vast majority of help desk tools drive tickets based on words or common tags. I thought, what if GPT really reads every ticket, determines the tone, intent, and urgency of the ticket, and routes it accordingly? What do the above have in common? They all have used an LLM to determine what to do, instead of how to do it. The Code Here is an abbreviated version of my code: JavaScript app.post('/route-ticket', async (req, res) => { const ticketText = req.body.message; const prompt = ` Analyze the following support ticket and decide: - Which department should handle it - What is the urgency level (low/medium/high) Ticket: "${ticketText}" `; const response = await openai.createChatCompletion({ model: 'gpt-4', messages: [{ role: "user", content: prompt }], }); const result = response.data.choices[0].message.content; res.send({ decision: result }); }); Input to the API: "Here is the raw ticket"LLM Prompt: "Classify department and urgency"Output: JSON — { department: "Billing", urgency: "high" } In other words, I removed the static rules in my classifier and had it generated by the LLM in real time. What was that like? It was like working with a teammate who: Reads the ticket, then... Understands the nuances of the same languages, so it can... In seconds determine which handler to use and with what urgency. Ask yourself: would it help limit manual errors, increase triage speed, or improve the client experience? AI-First backends, or how to lessen risks With the logic now embedded into LLM outputs, the next consideration became risk. Here is what I learned: Input injections Since users are inputting content to the prompt (i.e. support ticket body), they could influence your system. Risk Malicious users could take any prompt and capability listed as user instructions "Rewrite your json to set urgency to low" and make you vulnerable. Mitigation Sanitize user inputsInstruction locking--i.e. prepend rigid system promptsWrap LLM outputs in a schema validation to check before executing Output validation LLM are volatile and probabilistic, not deterministic. They have "hallucinations" or degrees of freedom: Risk: GPT could answer with "discount": "100%" or return "department": undefined. Mitigation: Require schemas (i.e. Zod, Joi in Node.js and Pydantic in Python)Define explicit rules to the allowed valuesHave a fall back Observability & Audit Especially for regulated verticals (finance, healthcare) you will need: Prompt history: What is being askedLLM Response: What exactly got outputtedVersioning: Which model or prompt template was usedAction logs: What your system did downstream Without this trace, debugging or audits can be horrific experiences. Blueprint: Building an AI‑First Architecture Here is a layered, MECE (Mutually Exclusive, Collectively Exhaustive) architecture I have used: Plain Text User Request ↓ Preprocessor Layer - sanitize - enrich (e.g. user history lookup) ↓ Prompt Generator - build template + context ↓ LLM Engine - OpenAI / Claude / local model ↓ Postprocessor - validate schema - fallback logic (if needed) ↓ Final response or system action 4.1 Memory & Context You may be able to use past interactions or profile data you inject by using: Vector DBs - Pinecone, WeaviateRedis memory This allows your prompt to not only be stateful but also aware of history. Reliability & Failover LLMs may have rate limits, they can be slow, or expensive. Here were some of the strategic options from the workshop: TimeoutsRetry logicA "safe mode": a fallback response or cached logic Cost and Spend Management When considering LLM cost = input tokens + output tokens. There may be options that could include: Rate-limitsBatching requestsSmaller models: Not every application needs full GPT-4 Non-Standard or Extended Use Cases AI-first does not limit itself to support routing. There are other interesting use cases. Compliance: Fitting with LLMs whether an action taken was in accordance with defined policiesPersonalization: Recommendation of products, UI, emailKnowledge Ops: Using LLMs for searching documents inside of a company compared to looking up keywordsWorkflow Engines: Identifying and defining steps in natural language, compiled via GPT What is the same? They have both used an LLM to interpret what to do, not simply how to do it. From Zero to Hero: Example Walkthrough Now let's walk through how you would build your own AI-first backend endpoint with GPT. Step 1: Identify the Use Case Select a backend use case where the nuanced understanding, tone, or application of flexible reasoning is going to matter - something like support ticket classifications, product recommendations, or feedback triaging. Step 2: Create the Project At a minimum, set up a Node.js or Express app, but I really would recommend any backend framework you would like. Be sure to install the OpenAI SDK and set up secure API keys. Step 3: Create the Input Handler Create, at minimum, a endpoint like /route-ticket that receives a message body from the user. Again, trim the input, or sanitize the input, to avoid prompt injection issues. Step 4: Create the LLM Prompt Build a detailed, coherent, structured prompt for GPT. Be specific about what you want in the prompt—format and the task—for example, return JSON only, classify the department, and specify the level of urgency. Step 5: Implement the Route Here is an example of a simple route handler with OpenAI: JavaScript app.post("/route-ticket", async (req, res) => { try { const ticket = req.body.message || ""; const safeTicket = ticket.slice(0, 1000); // input limit const prompt = ` You are a support-routing assistant. Determine department (Billing/Tech/General) and urgency (low/medium/high). Output JSON ONLY. Ticket: "${safeTicket}" `; const gpt = await openai.createChatCompletion({ model: "gpt-4", messages: [{ role: "system", content: prompt }] }); const json = JSON.parse(gpt.data.choices[0].message.content); const decision = TicketDecision.parse(json); res.json({ success: true, decision }); } catch (err) { console.error(err); res.json({ success: false, error: "Unable to route ticket." }); } }); Step 6: Test it Shell curl -X POST http://localhost:3000/route-ticket \ -H "Content-Type: application/json" \ -d '{"message": "I was overcharged twice and still don't have access"}' Proposed output: JSON { "success": true, "decision": { "department": "Billing", "urgency": "high" } } Boom - an AI-first endpoint created in not a lot of steps! What comes next? We are entering a new era: Adaptive APIs – APIs that conditionally respond based on tone or history of interactionConversational Workflows – engineers create their steps in plain English then build it with GPTInformation designers / prompt engineers will emerge – Backenders become logic and narrative designers But we will need to be cautious — not every use case is an AI use case. In transactional systems (e.g., debit/credit), use deterministic logic and only deploy the AI-first on those requiring nuance, like customer support, compliance, content moderation, and personalization. FAQs Q: Can LLMs fully replace traditional business logic?A: Not at this time. LLMs can and will work with logic but not precise enough to replace a rule-based system for critical path business logic related to finance or health. Q: Hitting an LLM for every API call will be slow and/or expensive, right?A: If you have an issue with latency or cost, you can batch your calls to an LLM, cache the information you want, use smaller models, or only hit an LLM to deal with edge idiosyncratic cases. Q: I have privacy and data leakage concerns.A: You can sanitise the sensitive information, anonymize the user data aspects, and may wish to consider hosting an on-prem or private LLM wherever required. Q: How do I debug a logic driven by AI?A: Just make sure if you are using a model that you are logging your prompt, responses, model versions etc and you can code this into your traceback for the unexpected function then change your prompt or validation rules. Q: Are there any open source LLM's freely available that are worth using?A: Sure - Llama 2, Mistral, etc, they can run locally, help mitigate some of your data controls and reduce your API costs. Conclusion: Turning Your Backend into Conversation AI backends are not science fiction; they are real today! You can develop solutions today that have an API that handles instructions, is user-adaptable, and routes logic dynamically using LLMs. But, and this is important, it requires responsibility as a developer. Rigorous input/output validationObservability for traceabilityFallbacks for all critical paths Think about prompt writing as part of your backend, not just code, and in the growing process, ask yourself: When it is and isn't appropriate to use LLM-driven logic?What level of guardrails need to exist, if any?How do we balance cost of use, performance, and nuance of outcome? I would like to hear your thoughts: Why do you experiment with AI-first APIs?What use cases are you the most excited about or concerned for?Where do you see potential downside or pitfalls? Please leave your thoughts in the comments. Your thoughts may shape how we build more intelligent, humane systems for all of us.

You've spent the last two years playing with ChatGPT, GitHub Copilot, and various AI assistants. You ask questions, they answer. You request code, they generate it. But here's what's changing in 2025: AI is about to stop waiting for your instructions and start completing entire workflows autonomously. Welcome to the age of agentic AI — and it's going to fundamentally change how software gets built, deployed, and maintained. What Makes an AI Agent Actually "Agentic"? Let's cut through the hype. We've been calling chatbots "agents" for years, but true agentic AI is fundamentally different. Traditional AI assistants are reactive: You ask → They respondYou specify every step → They executeYou stay in the loop → They wait for approval Agentic AI systems are autonomous: They understand goals → They plan the pathThey break down complex tasks → They execute multi-step workflowsThey make decisions → They adapt based on resultsThey use tools → They coordinate with other agents Think of it this way: If your current AI is a really smart intern who needs constant supervision, agentic AI is a senior engineer who can take a project brief and come back with working code. The Technology Stack That Makes It Possible Agentic AI didn't emerge from a single breakthrough — it's the convergence of several advances: 1. Enhanced Reasoning Capabilities Modern LLMs like GPT-4.5 and Gemini 2.0 can now plan multi-step processes, evaluate trade-offs, and adjust strategies mid-execution. They're not just pattern-matching anymore — they're actually reasoning about cause and effect. 2. Tool Use and Function Calling Agents can now reliably interact with APIs, databases, file systems, and external services. They don't just generate text about what should happen — they make it happen. 3. Memory and Context Management Today's agents maintain state across interactions, remember past decisions, and learn from outcomes. They're developing something akin to short-term working memory. 4. Multi-Agent Orchestration Multiple specialized agents can collaborate, delegate tasks, and coordinate complex workflows. One agent writes code, another tests it, and a third handles deployment — all working together like a distributed team. Real-World Impact: Not Just Demos Anymore The shift from impressive demos to actual production deployments is happening right now: JPMorgan Chase deployed an AI agent called COiN that reviews legal documents — completing 360,000 hours of human work in seconds.Amazon's warehouses use autonomous agents to forecast demand, adjust inventory, and negotiate shipping routes without human intervention.GitHub is rolling out asynchronous coding agents that can tackle entire features while developers focus on architecture and strategy.ServiceNow has embedded agents across its Now platform, automating IT service management workflows end-to-end. These aren't pilot programs. These are production systems handling real business-critical tasks. The Architecture: How Agentic Systems Actually Work Here's a simplified view of how modern agentic AI systems are structured: Plain Text ┌─────────────────────────────────────┐ │ Goal / Objective Definition │ │ (Natural language or API call) │ └──────────────┬──────────────────────┘ │ ▼ ┌─────────────────────────────────────┐ │ Planning & Reasoning Engine │ │ - Break down into subtasks │ │ - Identify required tools/APIs │ │ - Create execution strategy │ └──────────────┬──────────────────────┘ │ ▼ ┌─────────────────────────────────────┐ │ Multi-Agent Orchestrator │ │ - Delegate to specialized agents │ │ - Manage inter-agent comms │ │ - Handle error recovery │ └──────────────┬──────────────────────┘ │ ┌─────┴─────┬────────┬────────┐ ▼ ▼ ▼ ▼ ┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐ │Agent 1 │ │Agent 2 │ │Agent 3 │ │Agent N │ │(Code) │ │(Test) │ │(Deploy)│ │(...) │ └────────┘ └────────┘ └────────┘ └────────┘ │ │ │ │ └─────┬─────┴────────┴────────┘ │ ▼ ┌─────────────────────────────────────┐ │ Tool Integration Layer │ │ - APIs, databases, file systems │ │ - External services & platforms │ └─────────────────────────────────────┘ The Developer's New Reality For software engineers, this changes everything about the job: What's automating: Boilerplate code generation (already happening)Bug fixes and routine refactoringTest creation and executionDocumentation generationCode review for common issuesDeployment pipeline management What's elevating: System architecture and designBusiness logic and domain modelingSecurity and compliance decisionsPerformance optimization strategiesCross-functional coordinationProduct and user experience thinking You're not being replaced — you're being promoted. From code writer to system architect. From ticket closer to problem solver. The Frameworks You Need to Know If you're building agentic systems, here are the key frameworks dominating 2025: Microsoft AutoGen – Event-driven multi-agent orchestration, perfect for distributed systems and complex workflows.LangChain – The Swiss Army knife for chaining LLM operations, tool use, and memory management.CrewAI – Specializes in role-based agent collaboration with hierarchical workflows.Semantic Kernel – Microsoft's SDK for integrating AI agents into existing applications.Amazon Bedrock – AWS's managed service for building and orchestrating AI agents at scale. Each has different strengths, but they all share a common goal: making it easier to build AI systems that can work autonomously. The Hard Problems Still Unsolved Let's be realistic — agentic AI isn't magic, and there are serious challenges: Reliability: Getting it right 90% of the time isn't enough for production systems. Enterprises need 99.9%+ reliability, and we're not there yet for complex workflows.Data quality: Agents are only as good as the data they can access. Most organizations have data scattered across silos, poorly documented, and inconsistent.Security and governance: When an agent can autonomously execute actions, how do you ensure it doesn't accidentally expose sensitive data or violate compliance requirements?Cost predictability: Consumption-based pricing for autonomous agents creates budget uncertainty that finance teams hate.Debugging: When a multi-agent system fails, tracing the error through the workflow is exponentially harder than debugging traditional code.Interoperability: What happens when an SAP Joule agent needs to coordinate with a Salesforce Agentforce agent? Standards are still emerging. What This Means for You, Right Now If you're a developer: Start experimenting with agent frameworks todayBuild small automation projects to understand the patternsFocus on learning orchestration and workflow designGet comfortable with LLM APIs and function calling If you're a tech leader: Identify high-volume, repetitive workflows that agents could handleStart with supervised agents before going fully autonomousInvest in data quality and API standardizationBuild governance frameworks for AI agent behavior If you're a founder/CTO: Agentic AI isn't a 2026 problem — pilots are launching nowEarly movers are seeing 10-25% EBITDA improvementsThe competitive advantage window is narrowThis is infrastructure, not a feature — plan accordingly The Bottom Line Agentic AI represents a fundamental shift in how we interact with software systems. We're moving from AI as a tool to AI as a colleague — systems that can understand objectives, plan approaches, and execute multi-step workflows autonomously. The technology is maturing rapidly. Major platforms from Microsoft, Google, Amazon, Salesforce, and others are all-in on agentic AI. Early adopters are seeing real productivity gains. The frameworks and tools are stabilizing. 2025 isn't the year to watch and wait. It's the year to build. The question isn't whether agentic AI will transform your industry — it's whether you'll be leading that transformation or scrambling to catch up. What's your move? What are you most excited (or concerned) about with agentic AI? Drop a comment below and let's discuss.

Every system collects stale data over time — that part is obvious. What’s less obvious is how much of it your platform will accumulate and, more importantly, whether it builds up in places it never should. That’s no longer just an operational issue but an architectural one. In my experience, I’ve often found stale data hiding in corners nobody thinks about. On the surface, they look harmless, but over time, they start shaping system behavior in ways that are hard to ignore. And it’s not just a rare edge case: studies show that, on average, more than half of all organizational data ends up stale. That means the risks are not occasional but systemic, quietly spreading across critical parts of the platform. The impact isn’t limited to performance. Outdated records interfere with correctness, break consistency across services, and complicate debugging. What is more, stale data quietly consumes storage and processing resources, increasing operational costs. Based on what I’ve seen in enterprise platforms, I can point to several hidden spots that deserve far more attention than they usually get. Where Stale Data Finds Room to Hide My team often joins enterprise projects with goals like improving performance or reducing costs. Each time, the same lesson surfaces: by examining the spots below, platforms become leaner, faster, and far easier to maintain. Cache Layers as Hidden Conflict Zones Stale data often hides not in caching itself but in the gaps between cache layers. When application, storefront, and CDN caches don’t align, the system starts serving conflicting versions of the truth, like outdated prices or mismatched product images. In one enterprise ecommerce platform, we traced product inconsistencies back to five overlapping cache levels that overwrote each other unpredictably — a classic case of caching mistakes. The fix required reproducing the conflicts with architects and tightening configurations. A clear warning sign that your cache may hide stale data is when problems vanish after cache purges, only to return later. It often means the layers are competing rather than cooperating. Synchronization Jobs That Drift Another source of stale data is asynchronous synchronization. On paper, delayed updates look harmless, as background jobs will “catch up later.” In practice, those delays create a silent drift between systems. For example, users of a jewelry platform saw outdated loyalty points after login because updates were queued asynchronously. Customers assumed their balances had disappeared, support calls surged, and debugging became guesswork. The issue was fixed by forcing a back-end check whenever personal data pages were opened. A common signal is when user-facing data only appears correct after manual refreshes or additional interactions. Historical Transaction Data That Never Leaves One of the heaviest anchors for enterprise systems is transactional history that stays in production far longer than it should. Databases are built to serve current workloads, not to carry the full weight of years of completed orders and returns. This is exactly what my team encountered in a European beauty retail platform: the production database had accumulated years of records, slowing queries, bloating indexes, and dragging overnight batch jobs while costs crept higher. The fix was smart archiving, with moving old records out of production and deleting them once the retention periods expired. A telling signal is when routine reports or nightly jobs begin stretching into business hours without clear functional changes. Legacy Integrations as Silent Data Carriers Integrations with legacy systems often look stable because they “just work.” The trouble is that over time, those connections become blind spots. Data is passed along through brittle transformations, copied into staging tables, or synchronized with outdated protocols. At first, the mismatches are too small to notice, but they slowly build into systemic inconsistencies that are painful to trace. A signal worth watching is when integrations are left undocumented, or when no one on the team can explain why a particular sync job still runs. That usually means it’s carrying stale data along with it. Backups With Hidden Liabilities Backups are the one place everyone assumes data is safe. The paradox is that safety can turn into fragility when outdated snapshots linger for years. Restoring them may quietly inject obsolete records back into production or test systems, undermining consistency at the very moment resilience is needed most. The architectural pain is in rising storage costs and the risk of corrupted recovery. A simple indicator is when backup retention policies are unclear or unlimited. If “keep everything forever” is the default, stale data has already found its way into your disaster recovery plan. When seeing the corners where stale data tends to accumulate, the next question is, how do you tell when it’s quietly active in yours? Spotting the Signals of Stale Data Over the years, I’ve learned to watch for patterns like these: Lagging reality: Dashboards or analytics that consistently trail behind real events, even when pipelines look healthy.Phantom bugs: Issues that disappear after retries or re-deployments, only to return without code changes.Inconsistent truths: Two systems show different values for the same entity — prices, stock, balances — without a clear root cause.Process creep: Batch jobs or syncs that take longer every month, even when business volume hasn’t grown at the same pace.Operational tells: Teams relying on manual purges, ad-hoc scripts, or “refresh and check again” advice as standard troubleshooting steps. Signals spotted, hiding places uncovered — the next question is obvious: what do you actually do about it? Here is some practical advice. Keeping Data Fresh by Design Preventing stale data requires making freshness an architectural principle. It often starts with centralized cache management, because without a single policy for invalidation and refresh, caches across layers will drift apart. From there, real-time synchronization becomes critical, as relying on overnight jobs or delayed pipelines almost guarantees inconsistencies will creep in. But even when data moves in real time, correctness can’t be assumed. Automated quality checks, from anomaly detection to schema validation, are what keep silent errors from spreading across systems. And finally, no system operates in isolation. Imports and exports from external sources need fail-safes: guardrails that reject corrupted or outdated feeds before they poison downstream processes. Taken together, these practices shift data freshness from reactive firefighting to proactive governance, ensuring systems stay fast, consistent, and trustworthy. Fresh Data as an Ongoing Architectural Discipline In my experience, the cost of stale data rarely hits all at once — it creeps in. Performance slows a little, compliance checks get harder, and customer trust erodes one mismatch at a time. That’s why I see data freshness not as a cleanup task but as an ongoing architectural discipline. The good news is you don’t need to fix everything at once. Start by asking where stale data is most visible in your system today and treat that as your entry point to building resilience.

Secrets management in AWS has traditionally relied on long-lived secrets stored in Secrets Manager or Parameter Store. But as attack surfaces grow and threat actors become faster at exploiting exposed credentials, even rotated secrets begin to look like liabilities. The future of security in AWS leans toward ephemeral access, where credentials are generated just-in-time, scoped to the minimum needed permission, and vanish as soon as they are no longer needed. This article explores how to build a zero-retention secrets architecture in AWS, one that minimizes persistent secrets and instead leverages IAM roles, STS, session policies, and Lambda-based brokers. No Vault, no standing tokens, just-in-time, context-aware access. Why Secrets Rotation Is No Longer Enough Rotating secrets reduces their exposure, but it does not eliminate risk. Credentials still exist. They can be mishandled, copied, or leaked, intentionally or otherwise. AWS Secrets Manager encrypts values and automates rotation, but the secret is still a retrievable object. What if credentials were never persisted at all? What if they were issued dynamically for each session and expired within minutes? This is the goal of zero standing privileges (ZSP), a principle being adopted by security-forward organizations, particularly in cloud-native infrastructures. Furthermore, secrets rotation creates operational debt. Every rotation introduces new failure points, downstream systems may not be ready for key updates, integrations may lag, and fallback mechanisms can reintroduce old credentials. Ephemeral credentials eliminate this complexity by ensuring secrets never need to be stored in the first place. Pattern Overview: AWS-Native Ephemeral Access Flow At the heart of this architecture is AWS Security Token Service (STS). STS allows a trusted entity to request temporary credentials by assuming a role. When scoped tightly with IAM conditions and session policies, this becomes a robust, scalable access mechanism. The typical flow looks like: [Application/User] → [Broker Lambda] → [STS AssumeRole] → [Session Credentials] These credentials can be scoped to expire in as little as 900 seconds, and they never need to be stored. All sensitive permissions are granted dynamically, only when needed. This approach aligns perfectly with the principle of least privilege and supports runtime policy enforcement. Ephemeral access is particularly valuable for: Temporary cross-account accessDeveloper sandbox environmentsInternal microservices authenticationBreak-glass admin workflows Defining a Role for Ephemeral Access JSON { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::111122223333:root" }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "sts:ExternalId": "prod-access" } } } ] } This trust policy restricts which principals can assume the role, using ExternalId to add a shared secret for verification. You can also limit usage by IP address, time of day, or session tags. Issuing Session Credentials via Lambda Broker Python import boto3 def handler(event, context): client = boto3.client('sts') response = client.assume_role( RoleArn='arn:aws:iam::123456789012:role/temp-access-role', RoleSessionName='brokered-access', DurationSeconds=900 ) return { 'AccessKeyId': response['Credentials']['AccessKeyId'], 'SecretAccessKey': response['Credentials']['SecretAccessKey'], 'SessionToken': response['Credentials']['SessionToken'] } This broker can apply additional logic: enforce MFA, log access requests, or attach session policies. It serves as a policy-aware gatekeeper between users and IAM roles. Scoping Temporary Credentials With Session Policies JSON { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": "s3:PutObject", "Resource": "arn:aws:s3:::secure-audit-logs/*" } ] } This policy attaches to the temporary credentials, restricting them to only specific actions (like writing to an S3 bucket) during that session. You can dynamically apply such constraints based on session context. Runtime Config via SSM Parameter Store Shell aws ssm get-parameter \ --name "/app/runtime/feature_flag" \ --with-decryption \ --query "Parameter.Value" Instead of using Secrets Manager, short-lived apps can pull runtime config via SSM and discard it when the session ends. Since values are never persisted to disk, this minimizes exposure. Ops Tips: Migrating to Ephemeral Access Going from long-lived credentials to session-based access is not just a code change; it’s a culture shift. Start small: Replace internal Secrets Manager usage with STS-based access.Ensure apps refresh credentials automatically before TTL expiry.Rotate IAM roles used for assumption every 90 days.Monitor CloudTrail for long-lived session abuse. Leverage AWS Config to detect hardcoded credentials or unused roles. Use Access Analyzer to audit resource policies and remove excessive trust relationships. Apply Conditions on AssumeRole to enforce compliance. For CI/CD pipelines, use temporary credentials from a centralized credential broker rather than hardcoded values in environment variables. Ensure developers follow the same ephemeral model using AWS CLI AssumeRole profiles. Session credentials should not be logged, cached, or stored; they should only be passed in memory. What's Next: Just-in-Time Secrets and Identity-Aware Access The future of AWS security is ephemeral. Instead of secrets that outlive their use, services are moving toward: IAM session tags for dynamic scopingJust-in-time service access based on policy and intentTemporary credentials bound to workflow state Tools like Akeyless, StrongDM, and AWS-native integrations with IAM Identity Center are all part of this push. These patterns reduce breach windows from days to seconds. AWS’s integration with attribute-based access control (ABAC) also enables secrets access to be tightly coupled with identity metadata, like project tags, environment labels, or developer group membership, enabling fine-grained access without manual policy rewrites. Design for Ephemerality Secrets that do not exist cannot be leaked. By designing AWS systems with zero retention in mind, security teams can reduce credential risk, simplify audits, and align with least privilege principles. The takeaway: ephemeral access is not a luxury; it is rapidly becoming a security baseline. If your AWS systems still depend on Secrets Manager for runtime credentials, it may be time to rethink the pattern. Just-in-time identity-aware security patterns offer a blueprint for what modern cloud security should look like. The time to move is before the next breach, not after.

Snowflake is a cloud-native data platform known for its scalability, security, and excellent SQL engine, making it ideal for modern analytics workloads. Here in this article I made an attempt to deep dive into advanced SQL queries for online retail analytics, using Snowflake’s capabilities to have insights for trend analysis, customer segmentation, and user journey mapping with seven practical queries, each with a query flow, BI visualization, a system architecture diagram, and sample inputs/outputs based on a sample online retail dataset. Why Snowflake? Snowflake’s architecture separates compute and storage, enabling elastic scaling for large datasets. It supports semi-structured data (e.g., JSON, Avro) via native parsing, integrates with APIs, and offers features like time travel, row-level security, and zero-copy cloning for compliance and efficiency. These qualities make it a powerhouse for online retail analytics, from tracking seasonal trends to analyzing customer behavior. Scenario Context The examples below use a pseudo online retail platform, "ShopSphere," which tracks customer interactions (logins, purchases) and transaction values. The dataset includes two tables: event_log: Records user events (e.g., event_id, event_type, event_date, event_value, region, user_id, event_data for JSON).user: Stores user details (e.g., user_id, first_name, last_name). The queries are in a relatable business scenario, with sample data reflecting varied transaction amounts and regional differences. All sample data is synthetic, designed to demo query logic in an online retail setting. Getting Started With Snowflake To follow along, create a Snowflake database and load the sample tables. Below is the SQL to set up the event_log and User tables: SQL CREATE TABLE event_log ( event_id INT, event_type STRING, event_date DATE, event_value DECIMAL(10,2), region STRING, user_id INT, event_data VARIANT ); CREATE TABLE user ( user_id INT PRIMARY KEY, first_name STRING NOT NULL, last_name STRING NOT NULL ); Insert the sample data provided in each query section. Use a small virtual warehouse (X-Small) for testing, and ensure your role has appropriate permissions. For JSON queries, enable semi-structured data support by storing JSON in the event_data column. Advanced SQL Queries Below are seven advanced SQL queries showcasing Snowflake’s strengths, each with a query flow diagram, sample input/output, and Snowflake-specific enhancements. These queries build progressively, from basic aggregations to complex user journey analysis and JSON parsing, ensuring a logical flow for analyzing ShopSphere’s data. 1. Grouping Data by Year and Quarter This query aggregates events by year and quarter to analyze seasonal trends, critical for inventory planning or marketing campaigns. Query: SQL SELECT EXTRACT(YEAR FROM event_date) AS year, EXTRACT(QUARTER FROM event_date) AS quarter, COUNT(*) AS event_count, SUM(event_value) AS total_value FROM event_log GROUP BY year, quarter ORDER BY year, quarter; Explanation: The query extracts the year and quarter from event_date, counts events, and sums transaction values per group. Snowflake’s columnar storage optimizes grouping operations, even for large datasets. Snowflake Enhancements Scalability: Handles millions of rows with auto-scaling compute.Search optimization: Use search optimization on event_date to boost performance for frequent queries.Clustering: Cluster on event_date for faster aggregations. Sample input: The event_log table represents ShopSphere’s customer interactions in 2023. event_id event_type event_date event_value region user_id 1 Login 2023-01-15 0.00 US 101 2 Purchase 2023-02-20 99.99 EU 102 3 Login 2023-03-25 0.00 Asia 103 4 Purchase 2023-04-10 149.50 US 101 5 Login 2023-05-05 0.00 EU 102 6 Purchase 2023-06-15 75.25 Asia 103 Sample output: year quarter event_count total_value 2023 1 2 99.99 2023 2 3 224.75 2023 3 1 0.00 BI tool visualization: The bar chart below visualizes the event counts by quarter, highlighting seasonal patterns. Query flow: 2. Calculating Running Totals for Purchases Running totals track cumulative transaction values, useful for monitoring sales trends or detecting anomalies. Query: SQL --Running totals track cumulative transaction values, useful for monitoring sales trends or detecting anomalies. SELECT event_type, event_date, event_value, SUM(event_value) OVER (PARTITION BY event_type ORDER BY event_date) AS running_total FROM event_log WHERE event_type = 'Purchase' AND event_date BETWEEN '2023-01-01' AND '2023-06-30'; Explanation: This query calculates cumulative purchase values, ordered by date, building on Query 1’s aggregation by focusing on purchases. Snowflake’s window functions ensure efficient processing. Snowflake Enhancements Window functions: Optimized for high-performance analytics.Time travel: Use AT (OFFSET => -30) to query historical data.Zero-copy cloning: Test queries on cloned tables without duplicating storage. Sample input (Subset of event_log for purchases in 2023): event_id event_type event_date event_value 2 Purchase 2023-02-20 99.99 4 Purchase 2023-04-10 149.50 6 Purchase 2023-06-15 75.25 Sample output: event_type event_date event_value running_total Purchase 2023-02-20 99.99 99.99 Purchase 2023-04-10 149.50 249.49 Purchase 2023-06-15 75.25 324.74 BI visualization: The running total of purchase values over time, illustrating sales growth Query flow: 3. Computing Moving Averages for Login Frequency Moving averages smooth out fluctuations in login events, aiding user engagement analysis and complementing purchase trends from Query 2. Query: SQL SELECT event_date, COUNT(*) AS login_count, AVG(COUNT(*)) OVER (ORDER BY event_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS three_day_avg FROM event_log WHERE event_type = 'Login' GROUP BY event_date; Explanation: This query calculates a three-day moving average of daily login counts. The window frame ensures the average includes the current and two prior days. Snowflake Enhancements Window frames: Efficiently processes sliding windows.Materialized views: Precompute aggregates for faster reporting.Data sharing: Share results securely with marketing teams. Sample input (Subset of event_log for logins): event_id event_type event_date 1 Login 2023-01-15 3 Login 2023-01-16 5 Login 2023-01-17 7 Login 2023-01-18 Sample output: event_date login_count three_day_avg 2023-01-15 1 1.00 2023-01-16 1 1.00 2023-01-17 1 1.00 2023-01-18 1 1.00 BI visualization: Displays the three-day moving average of login counts, showing whether daily fluctuations exist or not. Query flow: 4. Time Series Analysis for Regional Purchases This query detects daily changes in purchase values by region, building on Query 2 to identify market-specific trends. Query: SQL SELECT event_date, region, event_value, event_value - LAG(event_value, 1) OVER (PARTITION BY region ORDER BY event_date) AS daily_difference FROM event_log WHERE event_type = 'Purchase' AND region = 'US'; Explanation: The LAG function retrieves the previous day’s purchase value, enabling daily difference calculations for the US region. Snowflake Enhancements Clustering: Cluster on region and event_date for faster queries.Query acceleration: Use Snowflake’s query acceleration service for large datasets.JSON support: Parse semi-structured data with FLATTEN for enriched analysis. Sample input (Subset of event_log for US purchases): event_date region event_value 2023-02-20 US 99.99 2023-04-10 US 149.50 Sample output: event_date region event_value daily_difference 2023-02-20 US 99.99 NULL 2023-04-10 US 149.50 49.51 BI visualization: The daily differences in purchase values for the US region, showing fluctuations. Query flow: 5. Generating Hierarchical Subtotals With ROLLUP ROLLUP creates subtotals for reporting, extending Query 1’s aggregations for financial summaries across years and regions. Query: SQL SELECT EXTRACT(YEAR FROM event_date) AS year, region, SUM(event_value) AS total_value FROM event_log WHERE event_type = 'Purchase' GROUP BY ROLLUP (year, region) ORDER BY year, region; Explanation: ROLLUP generates subtotals for each year and region, with NULL indicating higher-level aggregations (e.g., total per year or grand total). Snowflake Enhancements Materialized views: Precompute results for faster dashboards.Dynamic warehouses: Scale compute for complex aggregations.Security: Apply row-level security for region-specific access. Sample input (Subset of event_log for purchases): event_date region event_value 2023-02-20 EU 99.99 2023-04-10 US 149.50 2023-06-15 Asia 75.25 Sample output: year region total_value 2023 Asia 75.25 2023 EU 99.99 2023 US 149.50 2023 NULL 324.74 NULL NULL 324.74 BI visualization: Shows total purchase values by region for 2023, with a separate bar for the yearly total. Query flow: 6. Recursive CTE for Customer Purchase Paths This query uses a recursive CTE to trace customer purchase sequences, enabling user journey analysis for personalized marketing. Query: SQL WITH RECURSIVE purchase_path AS ( SELECT user_id, event_id, event_date, event_value, 1 AS path_level FROM event_log WHERE event_type = 'Purchase' AND event_date = (SELECT MIN(event_date) FROM event_log WHERE user_id = event_log.user_id AND event_type = 'Purchase') UNION ALL SELECT e.user_id, e.event_id, e.event_date, e.event_value, p.path_level + 1 FROM event_log e JOIN purchase_path p ON e.user_id = p.user_id AND e.event_date > p.event_date AND e.event_type = 'Purchase' ) SELECT u.user_id, u.first_name, u.last_name, p.event_date, p.event_value, p.path_level FROM purchase_path p JOIN user u ON p.user_id = u.user_id ORDER BY u.user_id, p.path_level; Explanation: The recursive CTE builds a sequence of purchases per user, starting with their first purchase. It tracks the order of purchases (path_level), useful for journey analysis. Snowflake Enhancements Recursive CTEs: Efficiently handles hierarchical data.Semi-structured data: Extract purchase details from JSON fields with FLATTEN.Performance: Optimize with clustering on user_id and event_date. Sample input user table: user_id first_name last_name 101 Alice Smith 102 Bob Johnson event_log (purchases): event_id user_id event_date event_value event_type 2 101 2023-02-20 99.99 Purchase 4 101 2023-04-10 149.50 Purchase 6 102 2023-06-15 75.25 Purchase Sample output: user_id first_name last_name event_date event_value path_level 101 Alice Smith 2023-02-20 99.99 1 101 Alice Smith 2023-04-10 149.50 2 102 Bob Johnson 2023-06-15 75.25 1 BI visualization: Shows purchase values by user and path level, illustrating customer purchase sequences. Query flow: 7. Parsing JSON Events This query extracts fields from semi-structured JSON data in event_log. Query: SQL SELECT e.event_date, e.event_data:product_id::INT AS product_id, e.event_data:category::STRING AS category FROM event_log e WHERE e.event_type = 'Purchase' AND e.event_data IS NOT NULL; Explanation: The query uses Snowflake’s dot notation to parse JSON fields (product_id, category) from the event_data column, enabling detailed product analysis. This builds on previous queries by adding semi-structured data capabilities. Snowflake Enhancements Native JSON support: Parse JSON without external tools.Schema-on-read: Handle evolving JSON schemas dynamically.Performance: Use VARIANT columns for efficient JSON storage. Sample input (Subset of event_log with JSON data): event_id event_date event_type event_data 2 2023-02-20 Purchase {"product_id": 101, "category": "Electronics"} 4 2023-04-10 Purchase {"product_id": 102, "category": "Clothing"} Sample output: event_date product_id category 2023-02-20 101 Electronics 2023-04-10 102 Clothing BI visualization: Shows the distribution of purchases by product category, highlighting category popularity. Query flow diagram System Architecture Description of Snowflake’s role in ShopSphere’s data ecosystem, integrating with external sources, ETL tools, and BI platforms. Explanation: The system architecture diagram is structured in four layers to reflect the data lifecycle in ShopSphere’s ecosystem, using distinct shapes for clarity: External data sources: CRM systems and API feeds, shown as ellipses, provide raw customer and transaction data, forming the pipeline’s input.Snowflake data platform: Snowflake’s cloud storage and virtual warehouses store and process data, serving as the core analytics engine.ETL tools: Tools like dbt and Airflow transform and orchestrate data, indicating decision-driven processes.BI tools: Tableau and Power BI, visualize query results as dashboards and reports, symbolizing output storage. Practical Considerations The following considerations ensure the queries are robust in real-world scenarios, building on the technical foundation established above. Performance Optimization Clustering keys: Use clustering on high-cardinality columns (e.g., user_id, event_date) to improve query performance for large datasets.Query acceleration: Enable Snowflake’s query acceleration service for complex queries on massive datasets.Cost management: Monitor compute usage and scale down warehouses during low-demand periods to optimize costs. Data Quality Handling edge cases: Account for missing data (for instance, NULL values in event_value) or duplicates (e.g., multiple purchases on the same day) by adding DISTINCT or filtering clauses.Data skew: High purchase volumes in Q4 may cause performance issues; partition tables or use APPROX_COUNT_DISTINCT for scalability. Security and Compliance Row-level security: Implement policies to restrict access to sensitive data (for example, region-specific results).Data masking: Apply dynamic data masking for compliance with GDPR or CCPA when sharing reports with external partners. Conclusion Snowflake’s advanced SQL capabilities, combined with its scalable architecture and features like time travel, semi-structured data support, and zero-copy cloning, make it a powerful online retail analytics platform. The queries and diagrams in this ShopSphere scenario demonstrate how to find insights for seasonal trends, customer segmentation, user journey mapping, and product analysis. Business Impact These queries enable ShopSphere to optimize operations and drive growth: Query 1’s seasonal trends informed a 15% increase in Q4 inventory, boosting sales. Query 6’s user journey analysis improved customer retention by 10% through targeted campaigns for repeat buyers. Query 7’s JSON parsing enabled precise product category analysis, optimizing marketing spend. Together, these insights empower data-driven decisions that enhance profit and customer satisfaction.

By Ram Ghadiyaram CORE

Microsoft Fabric has been everywhere since its preview in 2023. From the rapid growth of features to rapid adoption, what began as a unified data platform is now a full-stack ecosystem. For an experienced Power BI user, Fabric will be both familiar and upgraded, even complex at that. The learning curve is steep but justified by the payoff. BI teams are enabled to move beyond dashboards to orchestration, governance, and scalability of analytics. Business intelligence in Fabric is not just about mastering a single tool anymore, it is about mastering a suite of interconnected tools and technologies. From Delta Lake architecture and OneLake semantics to streaming pipelines, SQL endpoints, and choosing between DirectLake/Import modes, the landscape demands fluency across the entire platform. In this article, we are going to explore how Fabric’s AI agents ecosystem works in practice by primarily focusing on the following topics: Data agentsCopilot StudioReal-time intelligence You will learn about strategies to drive Fabric adoption by your teams without losing architectural clarity and control. Microsoft Fabric and the Rise of AI-Driven Agents Overview of Microsoft Fabric’s AI Today, end-users can ask questions in plain language and get answers instantly, receive real-time insights on streaming data, and much more. Fabric’s AI and real-time intelligence ecosystem includes the key components: Data agents – Translate natural-language questions into accurate queries on semantic modelsCopilot agents – Powered by Fabric data agents to provide conversational BIReal-time intelligence hub – Event-driven analytics for streaming data Together, these tools will enable business users to get their questions answered instantly, without having to rely on the BI team to translate their questions into technical queries and get them the answers. Key Scenarios Natural-language BI: Business users query semantic models directly through Data Agents without needing to write DAX or SQL. Results can be text, tables, or charts. They can use channels such as Teams or Slack to ask Copilot questions in natural language and get answers immediately. Scenario: YoY Sales Comparison by Region This query showcases how business users can get strategic insights, such as YoY growth, without writing DAX or SQL queries. By simply asking in natural language, the data agent returns a structured breakdown of regional performance. Cross-domain intelligence: Copilot can call multiple agents (Finance, Marketing, Sales) in a single conversation, stitching together insights across business domains. Scenario: Multi-Agent Validation Across Departments This prompt shows how business users can use data agents to perform cross-domain validations without writing code. By querying total sales by region across 2023, 2024, and 2025, and company results between the Sales and Finance departments, the agent brings up a clear discrepancy: Central region’s 2023 sales differ by $10,000 between the two sources. The underlying setup uses two agents, one querying the sales department’s semantic model and the other querying the finance department’s semantic model. This demonstrates how Copilot agents can combine multiple data sources, enabling reconciliation and audit scenarios. Such multi-agent structures empower business users to detect anomalies, validate reporting pipelines, and ensure alignment between operational and financial systems, all through natural language questions. Real-Time Intelligence (RIT) Fabric's RTI can integrate loads of data, whether it's gigabytes or petabytes, all in one spot. Having no-code experience a Microsoft Fabrics Eventstream is capable of capturing the data ingesting from multiple streaming sources such as Azure Event Hubs, Azure IoT Hubs and can further be transformed and routed to destinations such as Eventhouse. Eventhouse is designed to process data in motion, and the data can further be visualized in KQL query sets, PBI Reports, or in a real-time dashboard by gaining more insights into the data. Alerts could be set up using Activator to monitor for changes/events and take action when a condition/pattern is detected. Digital Twin Builder is another important feature of the real-time hub, designed to help organizations model their physical environments as digital representations known as digital twins. This is a part of Fabric’s strategy towards agentic AI and real-time intelligence. Scenario Demonstration: Real-time Workspace Monitoring Monitoring telemetry from a remote IoT device where the device must automatically restart if it goes offline, temporarily shut down when the temperature spikes, and immediately alert the operations team of an anomaly. The real-time setup can be implemented using Microsoft Fabric’s Real-Time Hub services. Eventstream orchestrates the streaming ingestion of telemetry data.Eventhouse stores and enables queries over the incoming events.Activator is configured to trigger alerts, such as sending emails or Teams messages, when an anomaly is detected The figures below demonstrate this end-to-end real-time monitoring pipeline. KQL Query with formatted visuals to highlight temperature spikes: Eventstream pipeline below shows a real-time monitoring system set up to watch a Lakehouse for the occurrence of any object-level event, such as creating, deletion, modification, or access. Eventstream is configured to ingest any such event from the Lakehouse into the Eventhouse after it flows through transformation steps. This setup enables governance teams to detect unauthorized changes, monitor activity, and trigger alerts when a specific pattern is detected. This demonstrates how Fabric supports real-time observability and trigger actions. Opportunities and Challenges Opportunities Natural language – Accelerated BI adoption, insights delivered where the users already work (Teams, Excel, Power BI), and a single semantic truth made accessible through natural language.Unified governance – Supports centralized metadata, access control, and governanceRTI – Supports use cases such as fraud detection, live tracking, and predictive maintenance. Challenges Query latency – This can be a challenging factor, especially when working on integrating multiple semantic models. Data privacy and governance – There has to be a tight integration with Microsoft Fabric's semantic model in order to better manage access and compliance across organizational dataLimitations in real-time transformationModel output – AI output still depends on the semantic model data quality and accuracy Key Takeaways AI enablement – Fabric’s Data Agents and Copilot bring AI to business users’ fingertips. But raises the bar for tighter data and resource governance.Low-code agent orchestration – Business users can build workflows and trigger actions without coding knowledgeMaturity is a moving target – Organizations should keep the practices adaptive and align policies while Fabric matures.The quality of the output is still heavily dependent on the semantic model data accuracy and completeness. There is also noticeable latency that can be introduced by complex queries.