<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Lionel Nguyen on Medium]]></title>
        <description><![CDATA[Stories by Lionel Nguyen on Medium]]></description>
        <link>https://medium.com/@huynguyen8505?source=rss-65068ab1dd48------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*kudfTtO5VvJ1bAr4slO9bg.jpeg</url>
            <title>Stories by Lionel Nguyen on Medium</title>
            <link>https://medium.com/@huynguyen8505?source=rss-65068ab1dd48------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 22:18:48 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@huynguyen8505/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Deep Dive into The Aha! Framework — A Strategic Path to Agile Product Success]]></title>
            <link>https://huynguyen8505.medium.com/deep-dive-into-the-aha-framework-a-strategic-path-to-agile-product-success-600c8f040aef?source=rss-65068ab1dd48------2</link>
            <guid isPermaLink="false">https://medium.com/p/600c8f040aef</guid>
            <category><![CDATA[agile-methodology]]></category>
            <dc:creator><![CDATA[Lionel Nguyen]]></dc:creator>
            <pubDate>Tue, 02 Dec 2025 06:05:40 GMT</pubDate>
            <atom:updated>2025-12-02T06:05:40.629Z</atom:updated>
            <content:encoded><![CDATA[<h3>Deep Dive into The Aha! Framework — A Strategic Path to Agile Product Success</h3><p>Founded in 2013, Aha! built its own suite of products using an internal methodology refined over a decade of trial and error. Early concepts like the “Minimum Lovable Product” (MLP) — a step beyond the MVP to ensure user love — and “The Responsive Method” for iterative building laid the groundwork.</p><h3>Core Principles</h3><p>The Aha! Framework for product development is guided by three core principles:</p><ul><li><strong>A goal-first approach</strong>: This principle ensures that the entire team is clear on the vision and goals, with all work tied to achieving those specific strategic objectives.</li><li><strong>A focus on building a Minimum Lovable Product (MLP)</strong>: The framework emphasizes creating a product that customers not only use but genuinely love, as opposed to a mere Minimum Viable Product (MVP).</li><li><strong>Iterative improvement</strong>: This involves a continuous process of refining and enhancing the product, balancing long-term planning with agile, ongoing delivery in short cycles.</li></ul><p>This flexible approach ensures teams prioritize work effectively and deliver value to customers by starting with strategy and supporting continuous development.</p><h3>Stages for Product Development</h3><p>The Aha! framework consists of 10 stages for product development. These stages outline a strategic approach to building products, moving from setting high-level goals and gathering ideas to releasing and analyzing functionality.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4169f9c4c893bf3a543a9b2de12c11e8/href">https://medium.com/media/4169f9c4c893bf3a543a9b2de12c11e8/href</a></iframe><p>The Aha! Framework comes with a master whiteboard template. This visual diagram maps all 10 stages, lists the official activities under each, suggests cadences, and allows you to customize best practices + link directly to tools/templates in Aha! Roadmaps.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*YZS9ophjrWeUQWRg" /></figure><p>Additionally, Aha! provides dozens of purpose-built templates (mostly whiteboards, notes, and scorecards) that align perfectly with the activities in each stage.</p><h3>Stage 1: Strategize</h3><p>Focus: Vision and goal-setting templates to align on high-level direction.</p><p>Kick off the framework by defining the product’s “true north.” This stage establishes vision, goals, and high-level initiatives, ensuring all downstream work ladders up to company objectives. It’s the biannual anchor that prevents tactical drift, blending market analysis with stakeholder alignment to set ambitious yet measurable targets.</p><p><strong>Key Activities</strong> (from the full activity breakdown):</p><ul><li>Define product vision: Outline purpose, positioning, and direction.</li><li>Review market conditions: Survey shifts in business models, personas, and competitors.</li><li>Draft product goals: Create SMART (Specific, Measurable, Achievable, Relevant, Time-bound) objectives tied to company strategy.</li><li>Identify key product initiatives: Pinpoint major investment areas.</li><li>Gather feedback: Solicit stakeholder input on drafts.</li><li>Set product strategy: Formalize and share artifacts like positioning statements.</li></ul><p><strong>Best Practices</strong>:</p><ul><li>Align product goals with company-wide OKRs to foster cross-team buy-in.</li><li>Use SWOT analysis early to ground decisions in reality.</li><li>Involve executives sparingly but decisively — focus on inspiration over micromanagement.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*BCsk_LC7Rm6b_TV_" /></figure><h3>Stage 2: Discover</h3><p>Focus: Research planning and insight-capture tools for user validation.</p><p>Shift from assumptions to evidence by immersing in customer realities. This stage validates strategy through direct user engagement, uncovering unmet needs and pain points to ensure you’re solving problems worth solving. It’s the empathy engine that fuels idea generation.</p><p><strong>Key Activities</strong>:</p><ul><li>Define research goals: Capture assumptions and objectives for studies.</li><li>Identify your audience: Build and query a participant database based on personas.</li><li>Schedule customer interviews: Draft outreach, topics, and scripts.</li><li>Conduct customer interviews: Probe challenges and aspirations, recording sessions.</li><li>Analyze transcripts: Extract quotes, sentiments, and patterns.</li><li>Connect insights to work: Link findings to initiatives, ideas, or features.</li></ul><p><strong>Best Practices</strong>:</p><ul><li>Diversify participants beyond evangelists — include skeptics for balanced views.</li><li>Centralize learnings in a shared repository to spot trends across studies.</li><li>Always tie insights back to strategic initiatives to avoid “research for research’s sake.”</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*lsVXLenAofidS6DV" /></figure><h3>Stage 3: Capture</h3><p>Focus: Feedback organization and scoring for idea triage.</p><p>Act as the organization’s idea hub by centralizing feedback from all sources. This stage transforms scattered inputs — customer tickets, sales notes, internal brainstorms — into scored opportunities, ensuring no valuable signal is lost while filtering noise.</p><p><strong>Key Activities</strong>:</p><ul><li>Review customer feedback: Organize portal submissions by themes.</li><li>Review colleague feedback: Curate internal ideas via dedicated channels.</li><li>Score ideas: Apply value metrics (e.g., alignment, impact, effort).</li><li>Promote ideas to features: Elevate top scorers for roadmap consideration.</li></ul><p><strong>Best Practices</strong>:</p><ul><li>Segment feedback by customer type (e.g., enterprise vs. SMB) for nuanced prioritization.</li><li>Foster a “no bad ideas” culture in portals, but enforce transparent scoring to build trust.</li><li>Automate triage where possible to focus humans on high-potential items.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*4rHWESLUmsHb6K8n" /></figure><h3>Stage 4: Explore</h3><p>Focus: Ideation and prototyping templates for concept validation.</p><p>Prototype the possible. Here, raw ideas evolve into visual concepts through collaborative sketching and iteration, testing feasibility before heavy investment. It’s the creative sandbox that bridges capture to concrete planning.</p><p><strong>Key Activities</strong>:</p><ul><li>Whiteboard new user experiences: Brainstorm flows and interactions.</li><li>Create wireframes: Sketch low-fidelity mocks.</li><li>Gather feedback: Share drafts with stakeholders.</li><li>Update wireframes: Incorporate input for refinement.</li></ul><p><strong>Best Practices</strong>:</p><ul><li>Keep sessions time-boxed (e.g., 60 minutes) to maintain energy.</li><li>Involve designers early but empower non-designers to contribute sketches.</li><li>Focus on “what if” questions to encourage bold exploration without judgment.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*IuXCNfaeJFMNH-Mj" /></figure><h3>Stage 5: Plan</h3><p>Focus: Prioritization and roadmap-building for execution alignment.</p><p>Architect the “how” and “when.” This stage refines priorities, scopes work, and builds visual roadmaps, balancing value, effort, and capacity to create a living plan that guides delivery without overcommitting.</p><p><strong>Key Activities</strong>:</p><ul><li>Draft roadmap: Organize features into timelines.</li><li>Scope features: Document needs and estimates.</li><li>Score features: Rank by updated value.</li><li>Update draft roadmap: Adjust for new insights.</li></ul><p><strong>Best Practices</strong>:</p><ul><li>Use a consistent value scorecard (e.g., RICE: Reach, Impact, Confidence, Effort) for objectivity.</li><li>Collaborate with engineering on estimates via planning poker to build realism.</li><li>Build in buffers (10–20%) for unknowns, reviewing capacity monthly.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*pjOMl08RWNUFZqZd" /></figure><h3>Stage 6: Showcase</h3><p>Focus: Communication and feedback tools for stakeholder engagement.</p><p>Rally the troops with transparency. Beyond sharing plans, this stage solicits feedback to refine direction, tailoring communications to audiences for maximum alignment and excitement.</p><p><strong>Key Activities</strong>:</p><ul><li>Share roadmap: Present with context and rationale.</li><li>Gather feedback: Collect and integrate perspectives.</li><li>Complete roadmap: Finalize post-input.</li></ul><p><strong>Recommended Cadences</strong>: Monthly, timed to sprint ends or quarterly gates.</p><p><strong>Best Practices</strong>:</p><ul><li>Customize views: Strategy roll-ups for execs, tactical details for devs.</li><li>Frame feedback as “guided by strategy” to manage scope creep.</li><li>Follow up with “what changed/why” notes to demonstrate responsiveness.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*b1Zu8_tue2sV-aPp" /></figure><h3>Stage 7: Deliver</h3><p>Focus: Execution checklists and sprint tools for building MLPs.</p><p>Turn plans into pixels and code. This execution powerhouse coordinates cross-functional streams — design, dev, ops — to build and ship MLPs iteratively, emphasizing lovability over mere functionality.</p><p><strong>Key Activities</strong>:</p><ul><li>Focus on high-priority features: Define MLP essentials.</li><li>Draft MLP requirements: Compile learnings and visuals.</li><li>Kickoff feature: Align teams via meetings.</li><li>Complete designs: High-fidelity mocks.</li><li>Gather feedback: Iterate on designs.</li><li>Update MLP requirements: Reflect changes.</li><li>Organize sprints: Backlog grooming.</li><li>Develop functionality: Build and integrate.</li><li>Confirm built as designed: QA sign-off.</li><li>Communicate progress: Status updates.</li><li>Ship functionality: Deploy incrementally.</li></ul><p><strong>Best Practices</strong>:</p><ul><li>Prioritize “lovable” elements (e.g., intuitive UX) in MLPs to differentiate from MVPs.</li><li>Sync tools (e.g., Jira + Aha!) for real-time visibility.</li><li>Hold “blameless post-mortems” on delays to foster continuous improvement.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*9KLA0pPtLkElvuYU" /></figure><h3>Stage 8: Document</h3><p>Focus: Knowledge capture templates for scalability and support.</p><p>Capture institutional knowledge to empower scaling. This often-overlooked stage compiles resources for self-service, reducing support tickets and accelerating onboarding.</p><p><strong>Key Activities</strong>:</p><ul><li>Update knowledge base: Revise guides, FAQs, and notes.</li></ul><p><strong>Best Practices</strong>:</p><ul><li>Leverage AI for drafting (e.g., release note generators).</li><li>Tag docs by audience (internal vs. customer-facing).</li><li>Audit quarterly for outdated content.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*f2hr5VFSMXt4kv1a" /></figure><h3>Stage 9: Launch</h3><p>Focus: Go-to-market planning and rollout coordination.</p><p>Ignite adoption with fanfare. Coordinate the symphony of announcements, training, and enablement to translate features into user excitement and revenue.</p><p><strong>Key Activities</strong>:</p><ul><li>Set go-to-market activities: Align on timing and materials.</li><li>Announce new functionality: Deploy campaigns across channels.</li></ul><p><strong>Best Practices</strong>:</p><ul><li>Create a launch playbook with checklists for all functions.</li><li>Personalize messaging (e.g., emails for power users, social for broad reach).</li><li>Prep support surges with role-playing scenarios.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*IihTCqUmKWO8Zzzz" /></figure><h3>Stage 10: Analyze</h3><p>Focus: Metrics and retrospective templates for iteration.</p><p>Close the loop with data-driven reflection. Measure against projections, gauge lovability, and iterate the process itself to compound future wins.</p><p><strong>Key Activities</strong>:</p><ul><li>Assess product value: Track metrics vs. scores.</li><li>Improve overall process: Solicit team retrospectives.</li></ul><p><strong>Best Practices</strong>:</p><ul><li>Blend quantitative (adoption KPIs) with qualitative (NPS surveys).</li><li>Dashboards over spreadsheets for at-a-glance insights.</li><li>Celebrate wins publicly to sustain morale.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*46zD4sIIMgl2zf3r" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=600c8f040aef" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How Data Was Analyzed Historically]]></title>
            <link>https://huynguyen8505.medium.com/how-data-was-analyzed-historically-4450581d334f?source=rss-65068ab1dd48------2</link>
            <guid isPermaLink="false">https://medium.com/p/4450581d334f</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Lionel Nguyen]]></dc:creator>
            <pubDate>Fri, 29 Aug 2025 04:34:58 GMT</pubDate>
            <atom:updated>2025-08-29T04:34:58.237Z</atom:updated>
            <content:encoded><![CDATA[<h3>Before Data Science</h3><p>Long before “data science” emerged, humans analyzed data manually and with rudimentary tools, laying the foundation for modern practices.</p><ul><li><strong>Ancient Times (19,000 BC–Ancient Civilizations)</strong>: Data analysis began with simple tools like the Ishango bone (19,000 BC) for tallying and calculations. Ancient Egyptians conducted censuses for pyramid construction and taxation, using manual records on papyrus. Babylonians and Greeks analyzed astronomical data for calendars and navigation.</li><li><strong>17th–18th Centuries</strong>: John Graunt’s 1663 mortality tables marked early statistical analysis, using manual tabulation to study London’s population trends. Probability theory by Blaise Pascal and Pierre de Fermat enabled risk assessment.</li><li><strong>19th Century</strong>: Statistics formalized with Francis Galton and Karl Pearson’s work on correlation and regression. Frederick Taylor’s time studies analyzed industrial data manually for efficiency. Tools included abacuses and slide rules.</li><li><strong>Early 20th Century (Pre-Computers)</strong>: Manual computations in “computation rooms” at universities, using punch cards and tabulators for large-scale analysis (e.g., censuses). ENIAC (1940s) introduced electronic computation, but analysis remained labor-intensive.</li></ul><p>These methods relied on human ingenuity, with errors common due to manual processes. They set the stage for data science by emphasizing pattern recognition and decision-making from data.</p><h3>The Birth of Data Science and Related Roles</h3><p>Data science and its roles emerged from the convergence of statistics, computing, and big data needs.</p><ul><li><strong>Data Analyst (Born: Mid-20th Century)</strong>: Formalized in the 1960s with computers, but roots in 1663 (Graunt’s analysis) and 19th-century statistics. John Tukey coined “data analysis” in 1962, emphasizing exploratory data work. The role grew with business intelligence tools in the 1970s–1980s.</li><li><strong>Data Science (Born: 1974, Popularized 2001–2012)</strong>: Peter Naur coined “data science” in 1974 as an alternative to computer science. John Tukey foreshadowed it in 1962 with “data analysis.” William S. Cleveland formalized it in 2001. The field gained prominence in 1996 at conferences and 2012 via Harvard Business Review.</li><li><strong>Data Scientist (Born: 2008)</strong>: The role was coined in 2008 by DJ Patil (LinkedIn) and Jeff Hammerbacher (Facebook) to describe professionals handling complex data for product insights.</li><li><strong>Data Engineer (Born: Early 2010s)</strong>: Emerged with big data technologies like Hadoop (2005). The role formalized around 2010–2011 at tech companies (e.g., Facebook), evolving from ETL and database roles in the 1970s (Edgar Codd’s relational model, 1970).</li></ul><p>These births were driven by technological advances: computers in the 1960s for analysts, big data in the 2000s for scientists and engineers.</p><h3>Core Technical Foundations: Building on Mining and Computing</h3><p>Data mining and computing form the bedrock of data science, but they are incomplete without additional layers.</p><ul><li><strong>Data Mining</strong>: This subset focuses on techniques like clustering, classification, and association rules to identify patterns in data. However, mining alone can lead to spurious correlations without statistical validation.</li><li><strong>Computing</strong>: Rooted in computer science, this includes programming (e.g., Python, R), algorithms, and big data tools like Spark or Hadoop for efficient data handling. Yet, computing emphasizes efficiency over insight generation.</li></ul><p>Data science advances these with statistics and machine learning. Statistics provides the mathematical rigor for hypothesis testing, regression, and inference, ensuring insights are reliable. Machine learning extends mining by enabling predictive modeling, but it requires integration with stats to avoid overfitting. Drew Conway’s influential Venn diagram illustrates this: data science sits at the intersection of hacking skills (computing), math and statistics, and substantive expertise, warning that lacking any component can lead to “danger zones” like misleading analyses.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/528/0*gHSX8LqtoHZDv0h4" /><figcaption><a href="https://drewconway.com/zia/2013/3/26/the-data-science-venn-diagram">https://drewconway.com/zia/2013/3/26/the-data-science-venn-diagram</a></figcaption></figure><h3>Equations for Data-Related Fields</h3><h3>Data Science = Data Mining + Computer Science + Statistical Analysis</h3><p>Data science combines “data” (the raw material) with “science” (systematic knowledge creation), relying on mining for patterns, computer science for tools, and statistics for validity, aligning with its interdisciplinary nature since Peter Naur’s 1974 coinage.</p><p><strong>Data</strong> Mining: Extracting patterns from “given” data (from Latin datum, meaning “something given”). This includes techniques like clustering or association rules to uncover insights.</p><p>Computer <strong>Science</strong>: Programming and algorithms (from Latin scientia, meaning “knowledge”) for processing and modeling data, e.g., Python or R.</p><p>Statistical Analysis: Validating insights with statistical methods, essential for rigorous knowledge extraction.</p><h3>Data Analysis = Data Querying + Descriptive Analysis + Business Insight</h3><p>“Data” is the raw input, and “analysis” involves breaking it down to understand trends, rooted in statistical practices since the 19th century.</p><p><strong>Data</strong> Querying: Extracting “given” data (from Latin datum) using tools like SQL.</p><p>Descriptive <strong>Analysis</strong>: Breaking down data (from Greek analusis, meaning “loosening”) with stats like means or correlations.</p><p>Business Insight: Aligning findings with business needs for actionable results.</p><h3>Data Analytics = Data Analysis + Predictive Analytics + Strategic Insight</h3><p>“Data” provides the foundation, and “analytics” extends analysis to strategic, forward-looking applications, prominent since the 1980s big data era.</p><p><strong>Data</strong> Analysis: Core querying and descriptive analysis (from Latin datum and Greek analusis) for historical insights.</p><p>Predictive <strong>Analytics</strong>: Forecasting future trends (from Greek analytika, meaning “science of dissolving”) using lightweight models.</p><p>Strategic Insight: Aligning data with long-term business goals.</p><h3>Data Engineering = Data Pipelines + Software Engineering + Database Systems</h3><p>“Data” reflects the material being processed, and “engineering” denotes the clever construction of systems, with pipelines as the core differentiator since the rise of big data.</p><p><strong>Data</strong> Pipelines: Building systems to process “given” data (from Latin datum) through ETL/ELT workflows.</p><p>Software <strong>Engineering</strong>: Coding and system design (from Latin ingenium, meaning “cleverness”) for scalable infrastructure, e.g., Python or Airflow.</p><p>Database Systems: Managing storage and access for data usability.</p><h3>Analytics Engineering ≈ Data Engineering + Data Analysis + Data Transformation</h3><p>Analytics engineering is a relatively new role that bridges data engineering and data analysis. It focuses on preparing high-quality, accessible data for analysts and business users, often using modern data stack tools</p><p>Data <strong>Engineering</strong>: Building and maintaining data pipelines, similar to a data engineer, but with a focus on enabling analytics rather than broad infrastructure.</p><p>Data <strong>Analysis</strong>: Querying and analyzing data to understand its structure and ensure it meets analytical needs.</p><p>Data Transformation: Using tools like dbt, SQL, or Airflow to transform raw data into analysis-ready formats, often in data warehouses.</p><h3>The relationships among data science, data analysis, data analytics, data engineering</h3><h3>Unique Areas</h3><ul><li><strong>Data Science</strong>: Machine learning (e.g., predictive models like neural networks).</li><li><strong>Data Engineering</strong>: Scalable infrastructure (e.g., Spark, cloud architecture).</li><li><strong>Data Analysis</strong>: Tactical reporting (e.g., historical KPI reports).</li><li><strong>Data Analytics</strong>: Strategic forecasting (e.g., predictive business insights).</li></ul><h3>Key Overlaps:</h3><ul><li><strong>Data Science ∩ Data Analysis</strong>: Data querying (e.g., SQL for exploration).</li><li><strong>Data Science ∩ Data Analytics</strong>: Predictive analytics (shared forecasting focus).</li><li><strong>Data Analysis ∩ Data Analytics</strong>: Descriptive stats and visualization (core to both).</li><li><strong>Data Science ∩ Data Engineering</strong>: Programming (e.g., Python) and data mining (as a subset of data science, used in pipelines).</li><li><strong>All</strong>: Data literacy (basic data handling, e.g., SQL, cleaning).</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4450581d334f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Storage Architectures]]></title>
            <link>https://huynguyen8505.medium.com/data-storage-architectures-6541803262a0?source=rss-65068ab1dd48------2</link>
            <guid isPermaLink="false">https://medium.com/p/6541803262a0</guid>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[data-lakehouse]]></category>
            <category><![CDATA[data-mart]]></category>
            <category><![CDATA[data-lake]]></category>
            <dc:creator><![CDATA[Lionel Nguyen]]></dc:creator>
            <pubDate>Thu, 21 Aug 2025 14:09:47 GMT</pubDate>
            <atom:updated>2025-08-21T14:09:47.485Z</atom:updated>
            <content:encoded><![CDATA[<h3>Definitions and Key Concepts</h3><ul><li><strong>Database</strong>: A structured system for storing, managing, and retrieving data efficiently. Databases can be categorized broadly as operational (OLTP-focused, handling real-time transactions like inserts, updates, and deletes) or analytical (OLAP-focused, for querying large datasets). Common types include relational databases (RDBMS like MySQL or PostgreSQL) for structured data and NoSQL databases (e.g., MongoDB for documents, Cassandra for wide-column stores) for flexible, scalable handling of semi-structured or unstructured data. They emphasize data integrity, consistency (via ACID properties), and quick access for applications.</li><li><strong>Operational Data Store (ODS)</strong>: A hybrid database that consolidates real-time or near real-time data from multiple operational sources (e.g., transactional databases) into a unified view for immediate reporting and decision-making. It acts as an intermediate layer, often feeding data into warehouses or lakes, and supports lightweight analytics without the full transformation of a warehouse. Data here is typically current and integrated but not deeply historical or optimized for complex BI.</li><li><strong>Data Warehouse</strong>: A structured repository designed for querying and analysis, where data from multiple sources is cleaned, transformed (via ETL processes), and organized into schemas (e.g., star or snowflake schemas) for business intelligence (BI) and reporting. It focuses on processed, high-quality data optimized for performance.</li><li><strong>Data Mart</strong>: A smaller, specialized subset of a data warehouse, tailored to a specific business unit or department (e.g., marketing or finance). It contains aggregated or summarized data relevant to that group’s needs, making it faster and more focused than a full warehouse.</li><li><strong>Data Lake</strong>: A centralized repository that stores vast amounts of raw data in its native format, including structured (e.g., databases), semi-structured (e.g., JSON), and unstructured data (e.g., images, videos, logs). It emphasizes low-cost storage and scalability but often lacks strong data governance, leading to potential “data swamps” if not managed well.</li><li><strong>Data Lakehouse</strong>: A hybrid architecture that combines the flexibility and cost-efficiency of a data lake with the data management, governance, and performance features of a data warehouse. It uses open formats like Delta Lake, Apache Iceberg, or Apache Hudi to add ACID transactions, schema enforcement, and BI support directly on raw data stored in the lake.</li></ul><h3>Key Differences</h3><p>Databases and ODS are more transaction-oriented compared to the analytical focus of warehouses, lakes, marts, and lakehouses.</p><h3>OLTP</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QxKBQ8hJL8_ACFsA4cGnhg.png" /></figure><h3>OLAP</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8cLrDuBa3ViasEc32g2coQ.png" /></figure><h3>Examples</h3><ul><li><strong>Database</strong>: An e-commerce platform like Amazon uses PostgreSQL or DynamoDB to handle real-time order placements, inventory updates, and user sessions, ensuring immediate consistency for transactions.</li><li><strong>ODS</strong>: A bank integrates live transaction data from ATMs, online banking, and branches into an ODS (e.g., built on Oracle) for real-time fraud detection reports, before archiving to a warehouse for historical analysis.</li><li><strong>Data Lake</strong>: A media company stores petabytes of user-generated videos, social media logs, and sensor data from IoT devices in an AWS S3-based lake. Data scientists later process subsets for machine learning models to recommend content, without upfront structuring.</li><li><strong>Data Warehouse</strong>: A retail chain like Walmart integrates sales data from stores, online platforms, and suppliers into a Snowflake warehouse. Analysts run SQL queries for inventory reports, trend analysis, and forecasting, ensuring data is clean and consistent.</li><li><strong>Data Mart</strong>: Within the same retail warehouse, the marketing team has a dedicated mart containing only customer demographics, purchase history, and campaign metrics. This allows quick dashboards in Tableau for targeted promotions, without accessing the full enterprise data.</li><li><strong>Data Lakehouse</strong>: A healthcare provider uses Databricks on Azure to store raw patient records, images, and genomic data in a lake format, but applies Delta Lake for versioning and ACID compliance. This enables both AI-driven diagnostics on raw data and BI reporting on structured subsets, all in one system.</li></ul><h3>When to Use</h3><ul><li><strong>Use a Database</strong> when you need fast, reliable transaction processing for applications (e.g., e-commerce, banking apps). Choose RDBMS for structured data with strong consistency or NoSQL for high scalability and flexibility in big data volumes. Not ideal for deep historical analysis.</li><li><strong>Use an ODS</strong> for near real-time operational reporting across systems (e.g., dashboards for current business ops). Use as a bridge between transactional databases and analytical stores when immediate, integrated views are needed but full warehousing is overkill. Avoid for long-term storage or complex ML</li><li><strong>Use a Data Lake</strong> when dealing with massive, diverse raw data that may not have immediate use cases, but you want cheap, scalable storage for future exploration (e.g., big data ingestion for ML or archiving). Ideal for data engineers and scientists in exploratory phases, but avoid if you need strong governance to prevent data quality issues.</li><li><strong>Use a Data Warehouse</strong> for structured analytics, BI, and decision-making where performance and data reliability are critical (e.g., financial reporting or regulatory compliance). Best for organizations with mature data teams needing fast queries on processed data, though it can be expensive for very large volumes.</li><li><strong>Use a Data Mart</strong> when a specific team needs quick access to focused data without the complexity of a full warehouse (e.g., departmental self-service analytics). It’s efficient for smaller-scale operations but relies on an underlying warehouse; not suitable for enterprise-wide needs.</li><li><strong>Use a Data Lakehouse</strong> when you want the best of both worlds: raw data flexibility for AI/ML alongside warehouse-like features for BI and governance (e.g., in hybrid cloud environments). It’s increasingly popular for modern data platforms, especially if you’re transitioning from lakes or warehouses, but requires tools that support open table formats.</li></ul><h3>History of Data Storage in Big Data</h3><ul><li><strong>Early 20th Century (Pre-Digital Era)</strong>: Data storage began with manual methods like punch cards (invented in the 1880s by Herman Hollerith for the U.S. Census) for tabulating information. These were mechanical precursors to machine-readable data, used in early computing for simple storage and processing.</li><li><strong>1950s-1970s (Magnetic and Early Digital Storage)</strong>: Magnetic tapes and drums emerged for computers like IBM’s early mainframes, allowing sequential data access. In 1956, IBM’s RAMAC introduced the first hard disk drive (HDD) with 5 MB capacity. This era focused on operational databases (e.g., relational databases by Edgar Codd in 1970) for transactional storage, laying groundwork for structured data handling.</li><li><strong>1980s-1990s (Data Warehousing and Marts)</strong>: As businesses grew, data warehouses were conceptualized by Bill Inmon (1992) for analytical storage separate from operational databases. Tools like Oracle and Teradata enabled ETL and OLAP. Data marts evolved as subsets for efficiency. This “Big Data Phase 1” emphasized structured analytics amid rising data volumes from digitalization.</li><li><strong>2000s (Big Data Explosion and Data Lakes)</strong>: The internet boom generated unstructured data, leading to Hadoop (2006, inspired by Google’s MapReduce) for distributed storage. Data lakes (coined by James Dixon in 2010) used cheap commodity hardware for massive, varied data. Cloud platforms like AWS S3 (2006) democratized scalable storage.</li><li><strong>2010s-Present (Hybrid and Advanced Architectures)</strong>: Challenges with data lakes (e.g., governance) spurred data lakehouses (popularized by Databricks in 2020). Solid-state drives (SSDs, evolving from 1950s concepts) and cloud-native solutions like Snowflake enabled hybrid models. Today, trends include data meshes (decentralized domains) and AI integration, handling exabytes of data in real-time.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6541803262a0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Creating Icerberg Table in MinIO with Spark]]></title>
            <link>https://huynguyen8505.medium.com/creating-icerberg-table-in-minio-with-spark-e7ed779d08b2?source=rss-65068ab1dd48------2</link>
            <guid isPermaLink="false">https://medium.com/p/e7ed779d08b2</guid>
            <category><![CDATA[minio]]></category>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[iceberg-table]]></category>
            <dc:creator><![CDATA[Lionel Nguyen]]></dc:creator>
            <pubDate>Tue, 19 Aug 2025 06:10:32 GMT</pubDate>
            <atom:updated>2025-08-19T06:10:32.758Z</atom:updated>
            <content:encoded><![CDATA[<h3>Prerequisites</h3><ul><li>Colima: Version 0.7.0 or later for Docker runtime.</li><li>Docker CLI and Docker Compose: For running Docker commands.</li></ul><h3>Project Structure</h3><pre>project/<br>├── notebooks/<br>│   └── iceberg_example.ipynb<br>├── warehouse/<br>└── docker-compose.yml</pre><h3>Set Up the Docker Compose Environment</h3><ul><li>Install Colima: brew install colima</li><li>Install Docker CLI: brew install docker</li><li>Start Colima with sufficient resources for Spark and MinIO: colima start --cpu 6 --memory 12 --disk 20</li></ul><p>Create a docker-compose.yml file with the following content to set up Spark, Iceberg REST catalog, and MinIO:</p><pre>services:<br>  spark-iceberg:<br>    image: tabulario/spark-iceberg<br>    container_name: spark-iceberg<br>    networks:<br>      iceberg_net:<br>    depends_on:<br>      - rest<br>      - minio<br>    volumes:<br>      - ./warehouse:/home/iceberg/warehouse<br>      - ./notebooks:/home/iceberg/notebooks/notebooks<br>    environment:<br>      - AWS_ACCESS_KEY_ID=admin<br>      - AWS_SECRET_ACCESS_KEY=password<br>      - AWS_REGION=us-east-1<br>    ports:<br>      - 8888:8888<br>      - 8080:8080<br>      - 10000:10000<br>      - 10001:10001<br>  rest:<br>    image: tabulario/iceberg-rest<br>    container_name: iceberg-rest<br>    networks:<br>      iceberg_net:<br>    ports:<br>      - 8181:8181<br>    environment:<br>      - AWS_ACCESS_KEY_ID=admin<br>      - AWS_SECRET_ACCESS_KEY=password<br>      - AWS_REGION=us-east-1<br>      - CATALOG_WAREHOUSE=s3://warehouse/<br>      - CATALOG_IO__IMPL=org.apache.iceberg.aws.s3.S3FileIO<br>      - CATALOG_S3_ENDPOINT=http://minio:9000<br>  minio:<br>    image: minio/minio<br>    container_name: minio<br>    environment:<br>      - MINIO_ROOT_USER=admin<br>      - MINIO_ROOT_PASSWORD=password<br>      - MINIO_DOMAIN=minio<br>    networks:<br>      iceberg_net:<br>        aliases:<br>          - warehouse.minio<br>    ports:<br>      - 9001:9001<br>      - 9000:9000<br>    command: [&quot;server&quot;, &quot;/data&quot;, &quot;--console-address&quot;, &quot;:9001&quot;]<br>  mc:<br>    depends_on:<br>      - minio<br>    image: minio/mc<br>    container_name: mc<br>    networks:<br>      iceberg_net:<br>    environment:<br>      - AWS_ACCESS_KEY_ID=admin<br>      - AWS_SECRET_ACCESS_KEY=password<br>      - AWS_REGION=us-east-1<br>    entrypoint: &gt;<br>      /bin/sh -c &quot;<br>      until (/usr/bin/mc alias set minio http://minio:9000 admin password) do echo &#39;...waiting...&#39;; sleep 1; done;<br>      /usr/bin/mc mb minio/warehouse --ignore-existing;<br>      tail -f /dev/null&quot;<br>networks:<br>  iceberg_net:</pre><p>This configuration:</p><ul><li>Uses the tabulario/spark-iceberg image for Spark with Iceberg support.</li><li>Sets up a MinIO server for object storage.</li><li>Configures an Iceberg REST catalog to manage table metadata.</li><li>Maps ports for Jupyter Notebook (8888), Spark UI (8080), and MinIO (9000, 9001).</li><li>Creates a warehouse bucket in MinIO for storing Iceberg data and metadata.</li></ul><h3>Start the Docker Environment</h3><pre>docker compose up -d</pre><p>Wait for the services to start. You can verify:</p><ul><li>MinIO is accessible at <a href="http://localhost:9001/">http://localhost:9001</a> (login with admin/password).</li><li>Jupyter Notebook is accessible at <a href="http://localhost:8888/">http://localhost:8888</a>.</li></ul><h3>Create a New Iceberg Table in the Notebook</h3><p>In the Jupyter Notebook, add the following code to create a new Iceberg table using the NYC Taxi dataset (or any dataset of your choice). This example creates a table, loads sample data, and stores it in the MinIO warehouse bucket.</p><pre># Import required libraries<br>from pyspark.sql import SparkSession<br><br># Configure Spark session<br>spark = SparkSession.builder \<br>    .appName(&quot;IcebergTableCreation&quot;) \<br>    .config(&quot;spark.sql.catalog.demo&quot;, &quot;org.apache.iceberg.spark.SparkCatalog&quot;) \<br>    .config(&quot;spark.sql.catalog.demo.type&quot;, &quot;rest&quot;) \<br>    .config(&quot;spark.sql.catalog.demo.uri&quot;, &quot;http://iceberg-rest:8181&quot;) \<br>    .config(&quot;spark.sql.catalog.demo.io-impl&quot;, &quot;org.apache.iceberg.aws.s3.S3FileIO&quot;) \<br>    .config(&quot;spark.sql.catalog.demo.warehouse&quot;, &quot;s3://warehouse/&quot;) \<br>    .config(&quot;spark.sql.catalog.demo.s3.endpoint&quot;, &quot;http://minio:9000&quot;) \<br>    .config(&quot;spark.sql.extensions&quot;, &quot;org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions&quot;) \<br>    .config(&quot;spark.hadoop.fs.s3a.access.key&quot;, &quot;admin&quot;) \<br>    .config(&quot;spark.hadoop.fs.s3a.secret.key&quot;, &quot;password&quot;) \<br>    .config(&quot;spark.hadoop.fs.s3a.endpoint&quot;, &quot;http://minio:9000&quot;) \<br>    .getOrCreate()<br># Create a database<br>spark.sql(&quot;CREATE DATABASE IF NOT EXISTS demo.nyc&quot;)<br># Create an Iceberg table<br>spark.sql(&quot;&quot;&quot;<br>CREATE TABLE demo.nyc.taxis (<br>    vendor_id BIGINT,<br>    trip_id BIGINT,<br>    trip_distance FLOAT,<br>    fare_amount DOUBLE,<br>    store_and_fwd_flag STRING<br>)<br>USING iceberg<br>PARTITIONED BY (vendor_id)<br>LOCATION &#39;s3://warehouse/nyc/taxis&#39;<br>&quot;&quot;&quot;)<br># Insert sample data<br>spark.sql(&quot;&quot;&quot;<br>INSERT INTO demo.nyc.taxis<br>VALUES <br>    (1, 1000371, 1.8, 15.32, &#39;N&#39;),<br>    (2, 1000372, 2.5, 22.15, &#39;N&#39;),<br>    (2, 1000373, 0.9, 9.01, &#39;N&#39;),<br>    (1, 1000374, 8.4, 42.13, &#39;Y&#39;)<br>&quot;&quot;&quot;)<br># Verify the data<br>spark.sql(&quot;SELECT * FROM demo.nyc.taxis&quot;).show()<br># Stop the Spark session<br>spark.stop()</pre><p>If you want to use a real dataset like the NYC Taxi dataset (~112M rows, 10GB), download it from MinIO’s public dataset or another source and upload it to the warehouse bucket using the MinIO console or mc commands. Then, modify the notebook to read the data (e.g., spark.read.parquet(&quot;s3://warehouse/your_dataset.parquet&quot;).write.saveAsTable(&quot;demo.nyc.taxis&quot;)).</p><h3>Verify the Table in MinIO</h3><ul><li>Open the MinIO console at <a href="http://localhost:9001/">http://localhost:9001</a> and log in with admin/password.</li><li>Navigate to the warehouse bucket. You should see a folder structure like nyc/taxis/ containing:</li><li>data/: Parquet files with the actual table data.</li><li>metadata/: Iceberg metadata files (e.g., <em>.metadata.json, snap-</em>.avro).</li></ul><h3>Clean Up</h3><p>To stop and remove the Docker containers: docker compose down</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e7ed779d08b2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Pulling raw data from the AppsFlyer API with Airflow]]></title>
            <link>https://huynguyen8505.medium.com/pulling-raw-data-from-the-appsflyer-api-with-airflow-ef61389e1b59?source=rss-65068ab1dd48------2</link>
            <guid isPermaLink="false">https://medium.com/p/ef61389e1b59</guid>
            <category><![CDATA[airflow]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[iceberg-table]]></category>
            <category><![CDATA[minio]]></category>
            <dc:creator><![CDATA[Lionel Nguyen]]></dc:creator>
            <pubDate>Sat, 31 May 2025 12:11:11 GMT</pubDate>
            <atom:updated>2025-05-31T12:11:11.111Z</atom:updated>
            <content:encoded><![CDATA[<p>Pull large AppsFlyer in-app event data, save raw CSV to MinIO, process with PySpark into an Iceberg table, and ensure scalability and reliability using Airflow for orchestration.</p><h3>Components</h3><ul><li>Airflow: Orchestrates the pipeline with CeleryExecutor, PostgreSQL metadata database, and Redis for scalability.</li><li>AppsFlyer Puller: A dedicated Docker container streams in-app event data to MinIO, handling large datasets efficiently.</li><li>PySpark with Iceberg: Processes raw data (e.g., deduplication, filtering) and writes to an Iceberg table in MinIO for analytics</li><li>MinIO: Stores raw CSV and Iceberg table data in an S3-compatible bucket.</li><li>Docker: Assumes Airflow, AppsFlyer Puller, PySpark with Iceberg, and MinIO are running in separate Docker containers.</li></ul><h3>Key Features</h3><ul><li>Dynamic DAGs for each app_id, scheduled at 1 AM for yesterday’s data (using {{ prev_ds }}).</li><li>Streaming API responses to handle large datasets.</li><li>Isolated data puller to avoid overloading Airflow.</li><li>Iceberg for schema evolution, efficient queries, and upserts.</li><li>CeleryExecutor for scalable task execution.</li><li>Scalable Spark cluster with two workers.</li><li>Validation task to ensure data quality.</li></ul><h3>Assumptions</h3><ul><li>Data size: 10s of GBs/day per app_id.</li><li>Multiple app_ids (e.g., 5-10 apps).</li><li>Docker host: 8+ CPU cores, 16+ GB RAM.</li><li>AppsFlyer API token and MinIO credentials available.</li><li>Iceberg is preferred for schema flexibility and analytics, as discussed.</li><li>Timezone: UTC (1 AM UTC; adjust if needed).</li></ul><h3>Design Choices</h3><ul><li>Separate Puller Instance: Uses DockerOperator to run appsflyer_puller.py in a dedicated container, streaming data to MinIO to handle 10s of GBs without overloading Airflow.</li><li>Iceberg Table: Stores processed data in an Iceberg table, partitioned by days(event_time) for efficient time-based queries. Iceberg supports schema evolution and upserts, ideal for dynamic in-app event data.</li><li>PostgreSQL: Ensures reliable Airflow metadata storage for production.</li><li>Two Spark Workers: Provides scalability (4 cores, 8 GB total) for large data processing. Configured with high parallelism (32) for GB-scale data.</li><li>Validation Task: Checks Iceberg table for non-empty data to ensure quality.</li></ul><h3>Project Structure</h3><pre>appsflyer_pipeline/<br>├── dags/<br>│   └── appsflyer_dag.py          # Generates DAGs per app_id<br>├── scripts/<br>│   ├── appsflyer_puller.py       # Script to pull AppsFlyer data and save to MinIO<br>│   └── spark_job.py              # PySpark script to process data into Iceberg<br>├── jars/<br>│   └── (Iceberg JAR downloaded)  # Iceberg runtime JAR<br>├── logs/                         # Airflow logs<br>├── plugins/                      # Airflow plugins (optional)<br>├── Dockerfile.puller             # Dockerfile for AppsFlyer puller<br>├── docker-compose.yml            # Docker Compose configuration<br>└── README.md                     # Setup instructions</pre><h3>Docker Setup</h3><pre>version: &#39;3.8&#39;<br>services:<br>  postgres:<br>    image: postgres:13<br>    environment:<br>      - POSTGRES_USER=airflow<br>      - POSTGRES_PASSWORD=airflow<br>      - POSTGRES_DB=airflow<br>    volumes:<br>      - postgres_data:/var/lib/postgresql/data<br>    ports:<br>      - &quot;5432:5432&quot;<br>    healthcheck:<br>      test: [&quot;CMD&quot;, &quot;pg_isready&quot;, &quot;-U&quot;, &quot;airflow&quot;]<br>      interval: 5s<br>      retries: 5<br><br>  redis:<br>    image: redis:6<br>    ports:<br>      - &quot;6379:6379&quot;<br>    healthcheck:<br>      test: [&quot;CMD&quot;, &quot;redis-cli&quot;, &quot;ping&quot;]<br>      interval: 5s<br>      retries: 5<br><br>  airflow-webserver:<br>    image: apache/airflow:2.9.3<br>    environment:<br>      - AIRFLOW__CORE__EXECUTOR=CeleryExecutor<br>      - AIRFLOW__DATABASE__SQL_ALCHEMY_CONN=postgresql+psycopg2://airflow:airflow@postgres:5432/airflow<br>      - AIRFLOW__CELERY__BROKER_URL=redis://redis:6379/0<br>      - AIRFLOW__CORE__LOAD_EXAMPLES=False<br>    volumes:<br>      - ./dags:/opt/airflow/dags<br>      - ./logs:/opt/airflow/logs<br>      - ./plugins:/opt/airflow/plugins<br>    ports:<br>      - &quot;8080:8080&quot;<br>    depends_on:<br>      postgres:<br>        condition: service_healthy<br>      redis:<br>        condition: service_healthy<br>    command: webserver<br><br>  airflow-scheduler:<br>    image: apache/airflow:2.9.3<br>    environment:<br>      - AIRFLOW__CORE__EXECUTOR=CeleryExecutor<br>      - AIRFLOW__DATABASE__SQL_ALCHEMY_CONN=postgresql+psycopg2://airflow:airflow@postgres:5432/airflow<br>      - AIRFLOW__CELERY__BROKER_URL=redis://redis:6379/0<br>      - AIRFLOW__CORE__LOAD_EXAMPLES=False<br>    volumes:<br>      - ./dags:/opt/airflow/dags<br>      - ./logs:/opt/airflow/logs<br>      - ./plugins:/opt/airflow/plugins<br>    depends_on:<br>      postgres:<br>        condition: service_healthy<br>      redis:<br>        condition: service_healthy<br>    command: scheduler<br><br>  airflow-worker:<br>    image: apache/airflow:2.9.3<br>    environment:<br>      - AIRFLOW__CORE__EXECUTOR=CeleryExecutor<br>      - AIRFLOW__DATABASE__SQL_ALCHEMY_CONN=postgresql+psycopg2://airflow:airflow@postgres:5432/airflow<br>      - AIRFLOW__CELERY__BROKER_URL=redis://redis:6379/0<br>      - AIRFLOW__CORE__LOAD_EXAMPLES=False<br>    volumes:<br>      - ./dags:/opt/airflow/dags<br>      - ./logs:/opt/airflow/logs<br>      - ./plugins:/opt/airflow/plugins<br>    depends_on:<br>      postgres:<br>        condition: service_healthy<br>      redis:<br>        condition: service_healthy<br>    command: celery worker<br><br>  spark-master:<br>    image: bitnami/spark:3.5.0<br>    ports:<br>      - &quot;7077:7077&quot;<br>      - &quot;8081:8081&quot;<br>    volumes:<br>      - ./scripts/spark_job.py:/path/to/spark_job.py<br>      - ./jars:/opt/bitnami/spark/jars<br>    environment:<br>      - SPARK_MODE=master<br>    command: &gt;<br>      bash -c &quot;curl -o /opt/bitnami/spark/jars/iceberg-spark-runtime-3.5_2.12-1.4.3.jar \<br>      https://repo1.maven.org/maven2/org/apache/iceberg/iceberg-spark-runtime-3.5_2.12/1.4.3/iceberg-spark-runtime-3.5_2.12-1.4.3.jar &amp;&amp; \<br>      /opt/bitnami/spark/bin/spark-class org.apache.spark.deploy.master.Master&quot;<br><br>  spark-worker-1:<br>    image: bitnami/spark:3.5.0<br>    environment:<br>      - SPARK_MODE=worker<br>      - SPARK_MASTER_URL=spark://spark-master:7077<br>      - SPARK_WORKER_CORES=2<br>      - SPARK_WORKER_MEMORY=4g<br>    volumes:<br>      - ./jars:/opt/bitnami/spark/jars<br>    depends_on:<br>      - spark-master<br>    command: &gt;<br>      bash -c &quot;curl -o /opt/bitnami/spark/jars/iceberg-spark-runtime-3.5_2.12-1.4.3.jar \<br>      https://repo1.maven.org/maven2/org/apache/iceberg/iceberg-spark-runtime-3.5_2.12/1.4.3/iceberg-spark-runtime-3.5_2.12-1.4.3.jar &amp;&amp; \<br>      /opt/bitnami/spark/bin/spark-class org.apache.spark.deploy.worker.Worker spark://spark-master:7077&quot;<br><br>  spark-worker-2:<br>    image: bitnami/spark:3.5.0<br>    environment:<br>      - SPARK_MODE=worker<br>      - SPARK_MASTER_URL=spark://spark-master:7077<br>      - SPARK_WORKER_CORES=2<br>      - SPARK_WORKER_MEMORY=4g<br>    volumes:<br>      - ./jars:/opt/bitnami/spark/jars<br>    depends_on:<br>      - spark-master<br>    command: &gt;<br>      bash -c &quot;curl -o /opt/bitnami/spark/jars/iceberg-spark-runtime-3.5_2.12-1.4.3.jar \<br>      https://repo1.maven.org/maven2/org/apache/iceberg/iceberg-spark-runtime-3.5_2.12/1.4.3/iceberg-spark-runtime-3.5_2.12-1.4.3.jar &amp;&amp; \<br>      /opt/bitnami/spark/bin/spark-class org.apache.spark.deploy.worker.Worker spark://spark-master:7077&quot;<br><br>  minio:<br>    image: minio/minio:RELEASE.2023-06-22T03-12-50Z<br>    ports:<br>      - &quot;9000:9000&quot;<br>    environment:<br>      - MINIO_ROOT_USER=AKIAIOSFODNN7EXAMPLE<br>      - MINIO_ROOT_PASSWORD=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY<br>    volumes:<br>      - minio_data:/data<br>    command: server /data<br>    healthcheck:<br>      test: [&quot;CMD&quot;, &quot;curl&quot;, &quot;-f&quot;, &quot;http://localhost:9000/minio/health/live&quot;]<br>      interval: 30s<br>      timeout: 20s<br>      retries: 3<br><br>  appsflyer-puller:<br>    build:<br>      context: .<br>      dockerfile: Dockerfile.puller<br>    environment:<br>      - MINIO_ENDPOINT=http://minio:9000<br>      - MINIO_ACCESS_KEY=AKIAIOSFODNN7EXAMPLE<br>      - MINIO_SECRET_KEY=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY<br>    depends_on:<br>      - minio<br><br>volumes:<br>  postgres_data:<br>  minio_data:</pre><ul><li>Start Docker containers: docker-compose up -d --build.</li><li>Access Airflow UI at <a href="http://localhost:8080/">http://localhost:8080</a> to trigger the DAG.</li><li>Monitor Spark UI at <a href="http://localhost:8081/">http://localhost:8081</a> for job progress.</li><li>Access MinIO UI at <a href="http://localhost:9000/">http://localhost:9000</a> to verify stored data.</li><li>Initialize Airflow database: docker-compose exec airflow airflow db init.</li><li>Create Iceberg table using a Spark shell or submit job</li></ul><pre>from pyspark.sql import SparkSession<br>  spark = SparkSession.builder \<br>      .appName(&quot;CreateIcebergTable&quot;) \<br>      .config(&quot;spark.hadoop.fs.s3a.endpoint&quot;, &quot;http://minio:9000&quot;) \<br>      .config(&quot;spark.hadoop.fs.s3a.access.key&quot;, &quot;AKIAIOSFODNN7EXAMPLE&quot;) \<br>      .config(&quot;spark.hadoop.fs.s3a.secret.key&quot;, &quot;wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY&quot;) \<br>      .config(&quot;spark.hadoop.fs.s3a.path.style.access&quot;, &quot;true&quot;) \<br>      .config(&quot;spark.hadoop.fs.s3a.impl&quot;, &quot;org.apache.hadoop.fs.s3a.S3AFileSystem&quot;) \<br>      .config(&quot;spark.sql.catalog.appsflyer&quot;, &quot;org.apache.iceberg.spark.SparkCatalog&quot;) \<br>      .config(&quot;spark.sql.catalog.appsflyer.type&quot;, &quot;hadoop&quot;) \<br>      .config(&quot;spark.sql.catalog.appsflyer.warehouse&quot;, &quot;s3a://appsflyer-data/warehouse&quot;) \<br>      .config(&quot;spark.sql.catalog.appsflyer.io-impl&quot;, &quot;org.apache.iceberg.aws.s3.S3FileIO&quot;) \<br>      .getOrCreate()<br><br>  spark.sql(&quot;&quot;&quot;<br>      CREATE TABLE appsflyer.in_app_events (<br>          event_time STRING,<br>          event_name STRING,<br>          event_value STRING,<br>          user_id STRING<br>      ) USING iceberg PARTITIONED BY (days(event_time))<br>  &quot;&quot;&quot;)<br><br>  spark.stop()</pre><h3>Airflow DAG</h3><pre>from airflow import DAG<br>from airflow.providers.docker.operators.docker import DockerOperator<br>from airflow.operators.bash import BashOperator<br>from airflow.operators.python import PythonOperator<br>from airflow.utils.dates import days_ago<br>from datetime import timedelta<br>from pyspark.sql.functions import col<br><br>default_args = {<br>    &#39;owner&#39;: &#39;airflow&#39;,<br>    &#39;depends_on_past&#39;: False,<br>    &#39;email_on_failure&#39;: False,<br>    &#39;email_on_retry&#39;: False,<br>    &#39;retries&#39;: 1,<br>    &#39;retry_delay&#39;: timedelta(minutes=5),<br>}<br><br>APP_ID = &quot;id123456789&quot;<br><br>dag = DAG(<br>    &#39;appsflyer_in_app_events_id123456789&#39;,<br>    default_args=default_args,<br>    description=&#39;Pull AppsFlyer in-app events for id123456789 at 1 AM&#39;,<br>    schedule_interval=&#39;0 1 * * *&#39;,  # 1 AM UTC<br>    start_date=days_ago(1),<br>    catchup=False,<br>)<br><br># Pull data<br>pull_task = DockerOperator(<br>    task_id=&#39;pull_data&#39;,<br>    image=&#39;appsflyer-puller&#39;,<br>    api_version=&#39;auto&#39;,<br>    auto_remove=True,<br>    command=[&#39;python&#39;, &#39;appsflyer_puller.py&#39;, &#39;{{ prev_ds }}&#39;, APP_ID, &#39;{{ conn.appsflyer_api.extra }}&#39;],<br>    environment={<br>        &#39;MINIO_ENDPOINT&#39;: &#39;http://minio:9000&#39;,<br>        &#39;MINIO_ACCESS_KEY&#39;: &#39;AKIAIOSFODNN7EXAMPLE&#39;,<br>        &#39;MINIO_SECRET_KEY&#39;: &#39;wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY&#39;<br>    },<br>    docker_url=&#39;unix://var/run/docker.sock&#39;,<br>    network_mode=&#39;bridge&#39;,<br>    task_retries=3,<br>    dag=dag,<br>)<br><br># Process with Spark<br>spark_submit_cmd = f&quot;&quot;&quot;<br>spark-submit --master spark://spark-master:7077 \<br>    --jars /opt/bitnami/spark/jars/iceberg-spark-runtime-3.5_2.12-1.4.3.jar \<br>    /path/to/spark_job.py \<br>    s3a://appsflyer-data/raw/in_app_events_{APP_ID}_{{ prev_ds }}.csv \<br>    appsflyer.in_app_events \<br>    {APP_ID}<br>&quot;&quot;&quot;<br>process_task = BashOperator(<br>    task_id=&#39;process_data&#39;,<br>    bash_command=spark_submit_cmd,<br>    dag=dag,<br>)<br><br># Validate Iceberg table<br>def validate_iceberg_table(**kwargs):<br>    from pyspark.sql import SparkSession<br>    spark = SparkSession.builder \<br>        .appName(&quot;ValidateIcebergTable&quot;) \<br>        .config(&quot;spark.hadoop.fs.s3a.endpoint&quot;, &quot;http://minio:9000&quot;) \<br>        .config(&quot;spark.hadoop.fs.s3a.access.key&quot;, &quot;AKIAIOSFODNN7EXAMPLE&quot;) \<br>        .config(&quot;spark.hadoop.fs.s3a.secret.key&quot;, &quot;wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY&quot;) \<br>        .config(&quot;spark.hadoop.fs.s3a.path.style.access&quot;, &quot;true&quot;) \<br>        .config(&quot;spark.hadoop.fs.s3a.impl&quot;, &quot;org.apache.hadoop.fs.s3a.S3AFileSystem&quot;) \<br>        .config(&quot;spark.sql.catalog.appsflyer&quot;, &quot;org.apache.iceberg.spark.SparkCatalog&quot;) \<br>        .config(&quot;spark.sql.catalog.appsflyer.type&quot;, &quot;hadoop&quot;) \<br>        .config(&quot;spark.sql.catalog.appsflyer.warehouse&quot;, &quot;s3a://appsflyer-data/warehouse&quot;) \<br>        .config(&quot;spark.sql.catalog.appsflyer.io-impl&quot;, &quot;org.apache.iceberg.aws.s3.S3FileIO&quot;) \<br>        .getOrCreate()<br>    df = spark.table(&quot;appsflyer.in_app_events&quot;).filter(col(&quot;app_id&quot;) == APP_ID)<br>    row_count = df.count()<br>    if row_count == 0:<br>        raise ValueError(f&quot;Iceberg table is empty for app_id {APP_ID}&quot;)<br>    spark.stop()<br><br>validate_task = PythonOperator(<br>    task_id=&#39;validate_iceberg_table&#39;,<br>    python_callable=validate_iceberg_table,<br>    provide_context=True,<br>    dag=dag,<br>)<br><br># Set dependencies<br>pull_task &gt;&gt; process_task &gt;&gt; validate_task</pre><p>Add Airflow connection via UI or CLI</p><pre>docker-compose exec airflow airflow connections add \<br>  --conn-id appsflyer_api \<br>  --conn-type HTTP \<br>  --conn-host https://hq.appsflyer.com \<br>  --conn-extra &#39;{&quot;api_token&quot;: &quot;your_appsflyer_api_token&quot;}&#39;</pre><h3>AppsFlyer Data Puller Script</h3><p>This script runs in a separate Docker container to stream large in-app event data to MinIO.</p><pre>import requests<br>import boto3<br>import os<br>import sys<br>import logging<br><br>logging.basicConfig(level=logging.INFO)<br><br>def pull_and_save_to_minio(date_str, app_id, api_token):<br>    minio_endpoint = os.getenv(&quot;MINIO_ENDPOINT&quot;)<br>    minio_access_key = os.getenv(&quot;MINIO_ACCESS_KEY&quot;)<br>    minio_secret_key = os.getenv(&quot;MINIO_SECRET_KEY&quot;)<br>    bucket_name = &quot;appsflyer-data&quot;<br>    key = f&quot;raw/in_app_events_{app_id}_{date_str}.csv&quot;<br><br>    # Configure MinIO client<br>    s3_client = boto3.client(<br>        &quot;s3&quot;,<br>        endpoint_url=minio_endpoint,<br>        aws_access_key_id=minio_access_key,<br>        aws_secret_access_key=minio_secret_key<br>    )<br><br>    # Pull AppsFlyer data<br>    url = f&quot;https://hq.appsflyer.com/v5/reports/{app_id}/in_app_events&quot;<br>    params = {<br>        &quot;from&quot;: date_str,<br>        &quot;to&quot;: date_str,<br>        &quot;timezone&quot;: &quot;UTC&quot;,<br>        &quot;additional_fields&quot;: &quot;event_time,event_value&quot;<br>    }<br>    headers = {&quot;Authorization&quot;: f&quot;Bearer {api_token}&quot;}<br><br>    try:<br>        with requests.get(url, params=params, headers=headers, stream=True, timeout=300) as response:<br>            response.raise_for_status()<br>            s3_client.upload_fileobj(<br>                Fileobj=response.raw,<br>                Bucket=bucket_name,<br>                Key=key<br>            )<br>        logging.info(f&quot;Saved data to s3://{bucket_name}/{key}&quot;)<br>        return key<br>    except Exception as e:<br>        logging.error(f&quot;Error pulling data for app_id {app_id}: {str(e)}&quot;)<br>        raise<br><br>if __name__ == &quot;__main__&quot;:<br>    if len(sys.argv) != 4:<br>        logging.error(&quot;Usage: python appsflyer_puller.py &lt;date&gt; &lt;app_id&gt; &lt;api_token&gt;&quot;)<br>        sys.exit(1)<br>    date_str, app_id, api_token = sys.argv[1], sys.argv[2], sys.argv[3]<br>    key = pull_and_save_to_minio(date_str, app_id, api_token)<br>    print(key)</pre><pre>FROM python:3.9-slim<br>WORKDIR /app<br>COPY appsflyer_puller.py .<br>RUN pip install requests boto3<br>CMD [&quot;python&quot;, &quot;appsflyer_puller.py&quot;]</pre><h3>PySpark Job with Iceberg</h3><pre>from pyspark.sql import SparkSession<br>import sys<br>from pyspark.sql.functions import col, to_date, lit<br><br>def process_appsflyer_data(input_path, output_table, app_id):<br>    # Initialize Spark session with Iceberg<br>    spark = SparkSession.builder \<br>        .appName(&quot;AppsFlyerInAppEventsProcessing&quot;) \<br>        .config(&quot;spark.hadoop.fs.s3a.endpoint&quot;, &quot;http://minio:9000&quot;) \<br>        .config(&quot;spark.hadoop.fs.s3a.access.key&quot;, &quot;AKIAIOSFODNN7EXAMPLE&quot;) \<br>        .config(&quot;spark.hadoop.fs.s3a.secret.key&quot;, &quot;wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY&quot;) \<br>        .config(&quot;spark.hadoop.fs.s3a.path.style.access&quot;, &quot;true&quot;) \<br>        .config(&quot;spark.hadoop.fs.s3a.impl&quot;, &quot;org.apache.hadoop.fs.s3a.S3AFileSystem&quot;) \<br>        .config(&quot;spark.executor.memory&quot;, &quot;4g&quot;) \<br>        .config(&quot;spark.executor.cores&quot;, &quot;2&quot;) \<br>        .config(&quot;spark.default.parallelism&quot;, &quot;32&quot;) \<br>        .config(&quot;spark.sql.shuffle.partitions&quot;, &quot;32&quot;) \<br>        .config(&quot;spark.sql.catalog.appsflyer&quot;, &quot;org.apache.iceberg.spark.SparkCatalog&quot;) \<br>        .config(&quot;spark.sql.catalog.appsflyer.type&quot;, &quot;hadoop&quot;) \<br>        .config(&quot;spark.sql.catalog.appsflyer.warehouse&quot;, &quot;s3a://appsflyer-data/warehouse&quot;) \<br>        .config(&quot;spark.sql.catalog.appsflyer.io-impl&quot;, &quot;org.apache.iceberg.aws.s3.S3FileIO&quot;) \<br>        .getOrCreate()<br><br>    # Read CSV from MinIO<br>    df = spark.read.option(&quot;header&quot;, &quot;true&quot;).csv(input_path)<br><br>    # Transformations: filter nulls, deduplicate, add app_id and event_date<br>    df_processed = df.filter(col(&quot;event_time&quot;).isNotNull()) \<br>                     .dropDuplicates([&#39;event_id&#39;, &#39;event_time&#39;]) \<br>                     .withColumn(&quot;app_id&quot;, lit(app_id)) \<br>                     .withColumn(&quot;event_date&quot;, to_date(col(&quot;event_time&quot;))) \<br>                     .select(&#39;app_id&#39;, &#39;event_date&#39;, &#39;event_time&#39;, &#39;event_name&#39;, &#39;event_value&#39;, &#39;user_id&#39;)<br><br>    # Write to Iceberg table<br>    df_processed.write.mode(&quot;append&quot;) \<br>                .partitionBy(&quot;app_id&quot;, &quot;event_date&quot;) \<br>                .option(&quot;write.format.default&quot;, &quot;parquet&quot;) \<br>                .saveAsTable(&quot;appsflyer.in_app_events&quot;)<br><br>    spark.stop()<br><br>if __name__ == &quot;__main__&quot;:<br>    if len(sys.argv) != 4:<br>        print(&quot;Usage: spark_job.py &lt;input_path&gt; &lt;output_table&gt; &lt;app_id&gt;&quot;)<br>        sys.exit(1)<br>    input_path = sys.argv[1]<br>    output_table = sys.argv[2]<br>    app_id = sys.argv[3]<br>    process_appsflyer_data(input_path, output_table, app_id)</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ef61389e1b59" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Improve the ingestion pipeline with Kappa Architecture and Appsflyer Push API]]></title>
            <link>https://huynguyen8505.medium.com/improve-the-ingestion-pipeline-with-kappa-architecture-and-appsflyer-push-api-d44e73865c5d?source=rss-65068ab1dd48------2</link>
            <guid isPermaLink="false">https://medium.com/p/d44e73865c5d</guid>
            <category><![CDATA[kappa-architecture]]></category>
            <dc:creator><![CDATA[Lionel Nguyen]]></dc:creator>
            <pubDate>Tue, 27 May 2025 13:23:37 GMT</pubDate>
            <atom:updated>2025-05-27T13:23:37.743Z</atom:updated>
            <content:encoded><![CDATA[<p>AppsFlyer’s Push API can be used as an alternative to the Pull API for ingesting raw data in your pipeline, and it’s well-suited for a Kappa Architecture. Unlike the Pull API, which requires you to fetch data hourly, the Push API streams raw data (e.g., installs, in-app events) to your server-side endpoints in near real-time as events occur. This aligns with Kappa’s streaming focus and can simplify your ingestion pipeline by reducing the need for scheduled API calls.</p><h3>Overview of Appsflyer Push API</h3><ul><li>Functionality: The Push API streams raw data events (e.g., installs, in-app events, SKAdNetwork data) to your specified HTTPS endpoint as JSON messages, triggered by user actions like app installs or in-app events.</li><li>Event Types: Supports organic and non-organic installs, in-app events (up to 52 or all events), and SKAdNetwork attribution events.</li><li>Fields: Includes mandatory fields (app_id, event_name, event_time, IDFA/Advertising ID) and optional fields you select (e.g., media_source, campaign, event_value).</li><li>Data Freshness: Near real-time, with events sent shortly after occurrence, compared to the Pull API’s hourly granularity.</li><li>Authentication: Supports customizable authorization headers with tokens for secure integration.</li><li>Premium Feature: Requires an AppsFlyer enterprise account or API Access package, which may be free for advanced tiers.</li><li>Limitations:</li><li>Does not support custom headers beyond authorization tokens.</li><li>Avoid using for third-party data sharing due to privacy regulations (e.g., CCPA) or media source restrictions.</li><li>If real-time attribution within 5 seconds of app open is needed, the Conversion Data API is recommended instead.</li></ul><h3>Configure Push API in AppsFlyer</h3><ul><li>Go to AppsFlyer dashboard: Integration &gt; API Access &gt; Push API.</li><li>Click Add Endpoint (or Add Postback).</li><li>Set Method: POST.</li><li>Enter your Endpoint URL (e.g., <a href="https://your-endpoint.example.com/appsflyer">https://your-endpoint.example.com/appsflyer</a>).</li><li>Add Authorization Token (e.g., Bearer your_token).</li><li>Select Event Types: Install, In-App Events (e.g., af_purchase, af_login).</li><li>Choose Fields: Include all mandatory and optional fields listed in the contract.</li><li>Save and test the endpoint (avoid using the test button if it fails; contact <a href="/service/mailto:support@appsflyer.com">support@appsflyer.com</a> with app ID and URL if “not safe” error occurs).</li></ul><p>For more information, read <a href="https://support.appsflyer.com/hc/en-us/articles/207034356-Push-API-streaming-raw-data">https://support.appsflyer.com/hc/en-us/articles/207034356-Push-API-streaming-raw-data</a></p><h3>Data Contract for Push API</h3><pre>data_contract:<br>  name: appsflyer_id123456789<br>  version: 1.2.0<br>  description: Raw data for AppsFlyer app_id=id123456789 from Push API, ingested via Kappa Architecture with Kafka, Flink, and Apache Iceberg tables in MinIO, covering non-organic installs and in-app events.<br>  owner:<br>    team: ProductA Data Team<br>    contact: producta-data@company.com<br>  source:<br>    name: AppsFlyer Push API<br>    endpoint: https://hq1.appsflyer.com/api/pushapi/v1.0/app/id123456789<br>    delivery: Near real-time streaming to customer HTTPS endpoint, forwarded to Kafka topics (appsflyer_installs, appsflyer_in_app_events), partitioned by app_id.<br>    parameters:<br>      app_id: id123456789<br>      auth_token: Configured in AppsFlyer dashboard<br>      event_types: [install, in_app_event]<br>      fields:<br>        mandatory: [app_id, event_name, event_time, advertising_id]<br>        optional: [media_source, campaign, campaign_id, adset, adset_id, ad, ad_id, platform, country_code, city, cost_value, cost_currency, attribution_type, placement, device_id, os_version, app_version, sdk_version, event_value, event_revenue, event_revenue_currency, user_id, is_retargeting]<br>    staging:<br>      customer_endpoint: https://your-endpoint.example.com/appsflyer<br>      kafka_topics:<br>        - name: appsflyer_installs<br>          partitions: by app_id<br>          retention: 30 days<br>        - name: appsflyer_in_app_events<br>          partitions: by app_id<br>          retention: 30 days<br>  processing:<br>    pipeline: Kappa Architecture<br>    components:<br>      - name: HTTPS Endpoint<br>        role: Receives Push API JSON messages and forwards to Kafka<br>      - name: Kafka<br>        role: Immutable event log for raw Push API data<br>      - name: Flink<br>        role: Stream processing for validation, transformation, and Iceberg writes<br>      - name: Airflow<br>        role: Monitors pipeline health and quality checks<br>  destinations:<br>    - iceberg_table: local.appsflyer_db.installs_non_organic<br>      location: s3://warehouse/appsflyer_db/installs_non_organic/app_id=id123456789<br>      format: Parquet<br>      catalog: HadoopCatalog<br>      schema:<br>        - name: app_id<br>          type: STRING<br>          required: true<br>          description: AppsFlyer app identifier, fixed to id123456789.<br>          constraints:<br>            - equals: &quot;id123456789&quot;<br>        - name: appsflyer_id<br>          type: STRING<br>          required: true<br>          description: Unique device identifier (advertising_id).<br>          constraints:<br>            - not_null<br>            - pattern: &quot;^[0-9a-fA-F-]+$&quot;<br>        - name: install_time<br>          type: TIMESTAMP<br>          required: true<br>          description: Time of app install in UTC (event_time).<br>          constraints:<br>            - not_null<br>            - valid_timestamp: &quot;yyyy-MM-dd HH:mm:ss&quot;<br>        - name: attributed_touch_time<br>          type: TIMESTAMP<br>          required: false<br>          description: Time of ad interaction.<br>          constraints:<br>            - valid_timestamp: &quot;yyyy-MM-dd HH:mm:ss&quot;<br>        - name: media_source<br>          type: STRING<br>          required: true<br>          description: Source of the install (e.g., googleadwords_int).<br>          constraints:<br>            - not_null<br>        - name: campaign<br>          type: STRING<br>          required: false<br>          description: Campaign name.<br>        - name: campaign_id<br>          type: STRING<br>          required: false<br>          description: Campaign ID.<br>        - name: adset<br>          type: STRING<br>          required: false<br>          description: Adset name.<br>        - name: adset_id<br>          type: STRING<br>          required: false<br>          description: Adset ID.<br>        - name: ad<br>          type: STRING<br>          required: false<br>          description: Ad name.<br>        - name: ad_id<br>          type: STRING<br>          required: false<br>          description: Ad ID.<br>        - name: platform<br>          type: STRING<br>          required: true<br>          description: Platform (e.g., iOS, Android).<br>          constraints:<br>            - not_null<br>            - enum: [&quot;iOS&quot;, &quot;Android&quot;]<br>        - name: country_code<br>          type: STRING<br>          required: false<br>          description: ISO country code (e.g., US).<br>          constraints:<br>            - pattern: &quot;^[A-Z]{2}$&quot;<br>        - name: city<br>          type: STRING<br>          required: false<br>          description: City of the user.<br>        - name: cost_value<br>          type: DOUBLE<br>          required: false<br>          description: Cost of the install.<br>          constraints:<br>            - min: 0.0<br>        - name: cost_currency<br>          type: STRING<br>          required: false<br>          description: Currency of the cost (e.g., USD).<br>          constraints:<br>            - pattern: &quot;^[A-Z]{3}$&quot;<br>        - name: attribution_type<br>          type: STRING<br>          required: false<br>          description: Type of attribution (e.g., click, impression).<br>          constraints:<br>            - enum: [&quot;click&quot;, &quot;impression&quot;, &quot;view&quot;]<br>        - name: placement<br>          type: STRING<br>          required: false<br>          description: Placement name.<br>        - name: device_id<br>          type: STRING<br>          required: false<br>          description: Device ID (e.g., IDFA, GAID).<br>        - name: os_version<br>          type: STRING<br>          required: false<br>          description: OS version.<br>        - name: app_version<br>          type: STRING<br>          required: false<br>          description: App version.<br>        - name: sdk_version<br>          type: STRING<br>          required: false<br>          description: AppsFlyer SDK version.<br>        - name: install_date<br>          type: DATE<br>          required: true<br>          description: Derived date from install_time.<br>          constraints:<br>            - not_null<br>      partitioning:<br>        fields: [app_id, install_date, platform]<br>        description: Partitioned for product-specific and time-based queries.<br>      quality_rules:<br>        - rule: completeness<br>          description: At least 95% of records must have non-null app_id, appsflyer_id, install_time, and platform.<br>          threshold: 0.95<br>        - rule: uniqueness<br>          description: Combination of appsflyer_id and install_time must be unique within app_id.<br>          fields: [app_id, appsflyer_id, install_time]<br>        - rule: valid_country_code<br>          description: country_code must match ISO 3166-1 alpha-2 format if present.<br>          field: country_code<br>          pattern: &quot;^[A-Z]{2}$&quot;<br>        - rule: kafka_partition_key<br>          description: app_id must match Kafka partition key.<br>          field: app_id<br>          constraint: equals_kafka_partition_key<br>    - iceberg_table: local.appsflyer_db.in_app_events_non_organic<br>      location: s3://warehouse/appsflyer_db/in_app_events_non_organic/app_id=id123456789<br>      format: Parquet<br>      catalog: HadoopCatalog<br>      schema:<br>        - name: app_id<br>          type: STRING<br>          required: true<br>          description: AppsFlyer app identifier, fixed to id123456789.<br>          constraints:<br>            - equals: &quot;id123456789&quot;<br>        - name: appsflyer_id<br>          type: STRING<br>          required: true<br>          description: Unique device identifier (advertising_id).<br>          constraints:<br>            - not_null<br>            - pattern: &quot;^[0-9a-fA-F-]+$&quot;<br>        - name: event_name<br>          type: STRING<br>          required: true<br>          description: Name of the event (e.g., af_purchase).<br>          constraints:<br>            - not_null<br>            - enum: [&quot;af_purchase&quot;, &quot;af_login&quot;, &quot;af_level_achieved&quot;]<br>        - name: event_time<br>          type: TIMESTAMP<br>          required: true<br>          description: Time of the event in UTC.<br>          constraints:<br>            - not_null<br>            - valid_timestamp: &quot;yyyy-MM-dd HH:mm:ss&quot;<br>        - name: event_revenue<br>          type: DOUBLE<br>          required: false<br>          description: Revenue associated with the event.<br>          constraints:<br>            - min: 0.0<br>        - name: event_revenue_currency<br>          type: STRING<br>          required: false<br>          description: Currency of the revenue (e.g., USD).<br>          constraints:<br>            - pattern: &quot;^[A-Z]{3}$&quot;<br>        - name: event_value<br>          type: MAP&lt;STRING, STRING&gt;<br>          required: false<br>          description: JSON key-value pairs (e.g., {&quot;item_id&quot;: &quot;123&quot;}).<br>          constraints:<br>            - valid_json: &quot;Parsed as valid JSON string&quot;<br>        - name: install_time<br>          type: TIMESTAMP<br>          required: false<br>          description: Time of app install.<br>          constraints:<br>            - valid_timestamp: &quot;yyyy-MM-dd HH:mm:ss&quot;<br>        - name: media_source<br>          type: STRING<br>          required: true<br>          description: Source of the install or event.<br>          constraints:<br>            - not_null<br>        - name: campaign<br>          type: STRING<br>          required: false<br>          description: Campaign name.<br>        - name: campaign_id<br>          type: STRING<br>          required: false<br>          description: Campaign ID.<br>        - name: platform<br>          type: STRING<br>          required: true<br>          description: Platform (e.g., iOS, Android).<br>          constraints:<br>            - not_null<br>            - enum: [&quot;iOS&quot;, &quot;Android&quot;]<br>        - name: country_code<br>          type: STRING<br>          required: false<br>          description: ISO country code (e.g., US).<br>          constraints:<br>            - pattern: &quot;^[A-Z]{2}$&quot;<br>        - name: user_id<br>          type: STRING<br>          required: false<br>          description: Customer user ID.<br>        - name: device_id<br>          type: STRING<br>          required: false<br>          description: Device ID (e.g., IDFA, GAID).<br>        - name: is_retargeting<br>          type: BOOLEAN<br>          required: false<br>          description: True if event is from a retargeting campaign.<br>        - name: event_date<br>          type: DATE<br>          required: true<br>          description: Derived date from event_time.<br>          constraints:<br>            - not_null<br>      partitioning:<br>        fields: [app_id, event_date, platform]<br>        description: Partitioned for product-specific and time-based queries.<br>      quality_rules:<br>        - rule: completeness<br>          description: At least 95% of records must have non-null app_id, appsflyer_id, event_name, event_time, and platform.<br>          threshold: 0.95<br>        - rule: uniqueness<br>          description: Combination of appsflyer_id and event_time must be unique within app_id.<br>          fields: [app_id, appsflyer_id, event_time]<br>        - rule: valid_event_value<br>          description: event_value must be valid JSON if present.<br>          field: event_value<br>          constraint: valid_json<br>        - rule: valid_country_code<br>          description: country_code must match ISO 3166-1 alpha-2 format if present.<br>          field: country_code<br>          pattern: &quot;^[A-Z]{2}$&quot;<br>        - rule: kafka_partition_key<br>          description: app_id must match Kafka partition key.<br>          field: app_id<br>          constraint: equals_kafka_partition_key<br>  sla:<br>    delivery: Near real-time via Push API, streamed to HTTPS endpoint, and processed through Kafka and Flink.<br>    latency: Data available in Iceberg tables within 5 minutes of event_time.<br>    freshness: Data reflects installs/events up to the current minute.<br>    reprocessing: Historical data can be replayed from Kafka (30-day retention) for corrections or schema updates.<br>  governance:<br>    change_management:<br>      process: Schema changes, new fields, or updates to quality rules must be approved by ProductA Data Team and communicated 7 days in advance.<br>      notification_channel: producta-data@company.com<br>      reprocessing: Supported via Kafka log replay and Iceberg schema evolution.<br>    access_control:<br>      read: [ProductA Analytics, Data Science Team]<br>      write: [ProductA Data Team]<br>      storage: MinIO bucket s3://warehouse/appsflyer_db/* with RBAC, partitioned by app_id.<br>    monitoring:<br>      tools: Airflow for pipeline health, Flink job monitoring, Great Expectations for quality checks.<br>      alerts: Notify producta-data@company.com on quality rule violations or pipeline failures.<br>    privacy_compliance:<br>      description: Ensure compliance with CCPA and media source restrictions; do not share user-level data with third parties without consent.</pre><h3>Pipeline Architecture</h3><h3>Components</h3><ul><li>HTTPS Endpoint:</li><li>Hosted on AWS Lambda, GCP Cloud Functions, or a custom server.</li><li>Receives POST requests with JSON payloads from AppsFlyer.</li><li>Forwards events to Kafka topics.</li><li>Kafka:</li><li>Topics: appsflyer_installs, appsflyer_in_app_events, partitioned by app_id.</li><li>Retention: 30 days, per contract.</li><li>Flink:</li><li>Reads from Kafka, validates against the contract, transforms data (e.g., derive install_date), and writes to Iceberg.</li><li>Iceberg:</li><li>Tables: installs, in_app_events, etc., partitioned by app_id, install_date/event_date, platform.</li><li>Airflow:</li><li>Monitors endpoint health, Flink jobs, and quality checks (no fetching).</li></ul><pre>[AppsFlyer Push API] --&gt; [HTTPS Endpoint] --&gt; [Kafka Topics]<br>                                                    |<br>                                                [Flink Processor]<br>                                                    |<br>                                              [Iceberg Tables on MinIO]</pre><h3>Receive Data from Push API then forward to Kafka topics</h3><pre>from flask import Flask, request<br>from kafka import KafkaProducer<br>import json<br><br>app = Flask(__name__)<br>producer = KafkaProducer(<br>    bootstrap_servers=[&#39;localhost:9092&#39;],<br>    value_serializer=lambda v: json.dumps(v).encode(&#39;utf-8&#39;)<br>)<br>@app.route(&#39;/appsflyer&#39;, methods=[&#39;POST&#39;])<br>def receive_push():<br>    try:<br>        data = request.json<br>        app_id = data.get(&#39;app_id&#39;)<br>        event_type = data.get(&#39;event_name&#39;)<br>        topic = &#39;appsflyer_installs&#39; if event_type == &#39;install&#39; else &#39;appsflyer_in_app_events&#39;<br>        producer.send(topic, key=app_id.encode(&#39;utf-8&#39;), value=data)<br>        return &#39;&#39;, 200<br>    except Exception as e:<br>        print(f&quot;Error: {e}&quot;)<br>        return &#39;&#39;, 500<br>if __name__ == &#39;__main__&#39;:<br>    app.run(host=&#39;0.0.0.0&#39;, port=443, ssl_context=(&#39;cert.pem&#39;, &#39;key.pem&#39;))</pre><h3>Process with Flink</h3><pre>import json<br>from datetime import datetime<br>from pyflink.common.serialization import SimpleStringSchema<br>from pyflink.common.typeinfo import Types<br>from pyflink.datastream import StreamExecutionEnvironment<br>from pyflink.table import StreamTableEnvironment, EnvironmentSettings<br>from pyflink.table.types import DataTypes<br>from pyflink.table.descriptors import Schema, Kafka, Json<br>from pyflink.table import TableDescriptor<br><br># Initialize environment<br>env = StreamExecutionEnvironment.get_execution_environment()<br>env_settings = EnvironmentSettings.new_instance().in_streaming_mode().build()<br>t_env = StreamTableEnvironment.create(env, environment_settings=env_settings)<br># Add Iceberg and Hadoop dependencies<br>env.add_jars(<br>    &quot;file:///path/to/iceberg-flink-runtime-1.17-1.4.0.jar&quot;,<br>    &quot;file:///path/to/hadoop-aws-3.3.4.jar&quot;,<br>    &quot;file:///path/to/aws-java-sdk-bundle-1.12.262.jar&quot;<br>)<br># Configure Flink catalog and MinIO<br>t_env.get_config().get_configuration().set_string(<br>    &quot;execution.checkpointing.interval&quot;, &quot;10000&quot;<br>)<br>t_env.execute_sql(&quot;&quot;&quot;<br>    CREATE CATALOG local WITH (<br>        &#39;type&#39;=&#39;iceberg&#39;,<br>        &#39;catalog-type&#39;=&#39;hadoop&#39;,<br>        &#39;warehouse&#39;=&#39;s3://warehouse&#39;,<br>        &#39;property-version&#39;=&#39;1&#39;,<br>        &#39;hadoop.fs.s3a.endpoint&#39;=&#39;http://minio:9000&#39;,<br>        &#39;hadoop.fs.s3a.access.key&#39;=&#39;admin&#39;,<br>        &#39;hadoop.fs.s3a.secret.key&#39;=&#39;password&#39;,<br>        &#39;hadoop.fs.s3a.path.style.access&#39;=&#39;true&#39;<br>    )<br>&quot;&quot;&quot;)<br>t_env.use_catalog(&quot;local&quot;)<br># Define Kafka source for installs<br>t_env.create_temporary_table(<br>    &quot;kafka_installs&quot;,<br>    TableDescriptor.for_connector(&quot;kafka&quot;)<br>        .schema(<br>            Schema.new_builder()<br>                .column(&quot;app_id&quot;, DataTypes.STRING())<br>                .column(&quot;appsflyer_id&quot;, DataTypes.STRING())<br>                .column(&quot;install_time&quot;, DataTypes.TIMESTAMP(3))<br>                .column(&quot;attributed_touch_time&quot;, DataTypes.TIMESTAMP(3))<br>                .column(&quot;media_source&quot;, DataTypes.STRING())<br>                .column(&quot;campaign&quot;, DataTypes.STRING())<br>                .column(&quot;campaign_id&quot;, DataTypes.STRING())<br>                .column(&quot;adset&quot;, DataTypes.STRING())<br>                .column(&quot;adset_id&quot;, DataTypes.STRING())<br>                .column(&quot;ad&quot;, DataTypes.STRING())<br>                .column(&quot;ad_id&quot;, DataTypes.STRING())<br>                .column(&quot;platform&quot;, DataTypes.STRING())<br>                .column(&quot;country_code&quot;, DataTypes.STRING())<br>                .column(&quot;city&quot;, DataTypes.STRING())<br>                .column(&quot;cost_value&quot;, DataTypes.DOUBLE())<br>                .column(&quot;cost_currency&quot;, DataTypes.STRING())<br>                .column(&quot;attribution_type&quot;, DataTypes.STRING())<br>                .column(&quot;placement&quot;, DataTypes.STRING())<br>                .column(&quot;device_id&quot;, DataTypes.STRING())<br>                .column(&quot;os_version&quot;, DataTypes.STRING())<br>                .column(&quot;app_version&quot;, DataTypes.STRING())<br>                .column(&quot;sdk_version&quot;, DataTypes.STRING())<br>                .build()<br>        )<br>        .option(&quot;topic&quot;, &quot;appsflyer_installs&quot;)<br>        .option(&quot;properties.bootstrap.servers&quot;, &quot;localhost:9092&quot;)<br>        .option(&quot;properties.group.id&quot;, &quot;appsflyer_kappa_push&quot;)<br>        .option(&quot;scan.startup.mode&quot;, &quot;latest-offset&quot;)<br>        .format(<br>            Json()<br>                .fail_on_missing_field(False)<br>                .json_schema(<br>                    &quot;&quot;&quot;<br>                    {<br>                        &quot;type&quot;: &quot;object&quot;,<br>                        &quot;properties&quot;: {<br>                            &quot;app_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;appsflyer_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;install_time&quot;: {&quot;type&quot;: &quot;string&quot;, &quot;format&quot;: &quot;date-time&quot;},<br>                            &quot;attributed_touch_time&quot;: {&quot;type&quot;: &quot;string&quot;, &quot;format&quot;: &quot;date-time&quot;},<br>                            &quot;media_source&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;campaign&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;campaign_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;adset&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;adset_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;ad&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;ad_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;platform&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;country_code&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;city&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;cost_value&quot;: {&quot;type&quot;: &quot;number&quot;},<br>                            &quot;cost_currency&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;attribution_type&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;placement&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;device_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;os_version&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;app_version&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;sdk_version&quot;: {&quot;type&quot;: &quot;string&quot;}<br>                        }<br>                    }<br>                    &quot;&quot;&quot;<br>                )<br>        )<br>        .build()<br>)<br># Define Kafka source for in-app events<br>t_env.create_temporary_table(<br>    &quot;kafka_in_app_events&quot;,<br>    TableDescriptor.for_connector(&quot;kafka&quot;)<br>        .schema(<br>            Schema.new_builder()<br>                .column(&quot;app_id&quot;, DataTypes.STRING())<br>                .column(&quot;appsflyer_id&quot;, DataTypes.STRING())<br>                .column(&quot;event_name&quot;, DataTypes.STRING())<br>                .column(&quot;event_time&quot;, DataTypes.TIMESTAMP(3))<br>                .column(&quot;event_revenue&quot;, DataTypes.DOUBLE())<br>                .column(&quot;event_revenue_currency&quot;, DataTypes.STRING())<br>                .column(&quot;event_value&quot;, DataTypes.MAP(DataTypes.STRING(), DataTypes.STRING()))<br>                .column(&quot;install_time&quot;, DataTypes.TIMESTAMP(3))<br>                .column(&quot;media_source&quot;, DataTypes.STRING())<br>                .column(&quot;campaign&quot;, DataTypes.STRING())<br>                .column(&quot;campaign_id&quot;, DataTypes.STRING())<br>                .column(&quot;platform&quot;, DataTypes.STRING())<br>                .column(&quot;country_code&quot;, DataTypes.STRING())<br>                .column(&quot;user_id&quot;, DataTypes.STRING())<br>                .column(&quot;device_id&quot;, DataTypes.STRING())<br>                .column(&quot;is_retargeting&quot;, DataTypes.BOOLEAN())<br>                .build()<br>        )<br>        .option(&quot;topic&quot;, &quot;appsflyer_in_app_events&quot;)<br>        .option(&quot;properties.bootstrap.servers&quot;, &quot;localhost:9092&quot;)<br>        .option(&quot;properties.group.id&quot;, &quot;appsflyer_kappa_push&quot;)<br>        .option(&quot;scan.startup.mode&quot;, &quot;latest-offset&quot;)<br>        .format(<br>            Json()<br>                .fail_on_missing_field(False)<br>                .json_schema(<br>                    &quot;&quot;&quot;<br>                    {<br>                        &quot;type&quot;: &quot;object&quot;,<br>                        &quot;properties&quot;: {<br>                            &quot;app_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;appsflyer_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;event_name&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;event_time&quot;: {&quot;type&quot;: &quot;string&quot;, &quot;format&quot;: &quot;date-time&quot;},<br>                            &quot;event_revenue&quot;: {&quot;type&quot;: &quot;number&quot;},<br>                            &quot;event_revenue_currency&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;event_value&quot;: {&quot;type&quot;: &quot;object&quot;},<br>                            &quot;install_time&quot;: {&quot;type&quot;: &quot;string&quot;, &quot;format&quot;: &quot;date-time&quot;},<br>                            &quot;media_source&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;campaign&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;campaign_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;platform&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;country_code&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;user_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;device_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;is_retargeting&quot;: {&quot;type&quot;: &quot;boolean&quot;}<br>                        }<br>                    }<br>                    &quot;&quot;&quot;<br>                )<br>        )<br>        .build()<br>)<br># Process Installs with SQL<br>t_env.sql_query(&quot;&quot;&quot;<br>    INSERT INTO appsflyer_db.installs<br>    SELECT<br>        app_id,<br>        appsflyer_id,<br>        install_time,<br>        attributed_touch_time,<br>        media_source,<br>        campaign,<br>        campaign_id,<br>        adset,<br>        adset_id,<br>        ad,<br>        ad_id,<br>        platform,<br>        country_code,<br>        city,<br>        cost_value,<br>        cost_currency,<br>        attribution_type,<br>        placement,<br>        device_id,<br>        os_version,<br>        app_version,<br>        sdk_version,<br>        CAST(install_time AS DATE) AS install_date,<br>        CURRENT_TIMESTAMP AS ingestion_time<br>    FROM kafka_installs<br>    WHERE<br>        app_id = &#39;id123456789&#39;<br>        AND appsflyer_id IS NOT NULL<br>        AND platform IN (&#39;iOS&#39;, &#39;Android&#39;)<br>        AND media_source IS NOT NULL<br>&quot;&quot;&quot;)<br># Process In-App Events with SQL<br>t_env.sql_query(&quot;&quot;&quot;<br>    INSERT INTO appsflyer_db.in_app_events<br>    SELECT<br>        app_id,<br>        appsflyer_id,<br>        event_name,<br>        event_time,<br>        event_revenue,<br>        event_revenue_currency,<br>        event_value,<br>        install_time,<br>        media_source,<br>        campaign,<br>        campaign_id,<br>        platform,<br>        country_code,<br>        user_id,<br>        device_id,<br>        is_retargeting,<br>        CAST(event_time AS DATE) AS event_date,<br>        CURRENT_TIMESTAMP AS ingestion_time<br>    FROM kafka_in_app_events<br>    WHERE<br>        app_id = &#39;id123456789&#39;<br>        AND appsflyer_id IS NOT NULL<br>        AND event_name IN (&#39;af_purchase&#39;, &#39;af_login&#39;, &#39;af_level_achieved&#39;)<br>        AND platform IN (&#39;iOS&#39;, &#39;Android&#39;)<br>        AND media_source IS NOT NULL<br>&quot;&quot;&quot;)<br># Execute<br>t_env.execute(&quot;AppsFlyer Kappa Push Pipeline SQL&quot;)</pre><h3>Conclusion</h3><p>AppsFlyer’s Push API is a viable and recommended alternative to the Pull API for your Kappa Architecture pipeline, offering near real-time streaming that reduces orchestration complexity and enhances data freshness. The updated data contract for app_id=id123456789 supports Push API integration, maintaining compatibility with Iceberg schemas and product-specific rules. The pipeline uses an HTTPS endpoint to receive events, Kafka for streaming, and Flink for processing, with Airflow monitoring health.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d44e73865c5d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Kappa Architecture]]></title>
            <link>https://huynguyen8505.medium.com/kappa-architecture-92e22363b686?source=rss-65068ab1dd48------2</link>
            <guid isPermaLink="false">https://medium.com/p/92e22363b686</guid>
            <category><![CDATA[kappa-architecture]]></category>
            <dc:creator><![CDATA[Lionel Nguyen]]></dc:creator>
            <pubDate>Tue, 27 May 2025 13:20:01 GMT</pubDate>
            <atom:updated>2025-05-27T13:24:23.061Z</atom:updated>
            <content:encoded><![CDATA[<p>Kappa Architecture is a data processing framework designed for handling large-scale, real-time data streams in a unified way, simplifying the complexities of the earlier Lambda Architecture. It was proposed by Jay Kreps as an alternative that eliminates the need for separate batch and streaming layers.</p><h3>Core Principles</h3><ul><li>Single Processing Pipeline: Unlike Lambda Architecture, which maintains separate batch (historical) and speed (real-time) layers, Kappa Architecture uses a single streaming pipeline to process both real-time and historical data.</li><li>Immutable Log: Data is stored as an immutable, append-only log (e.g., in Apache Kafka) that serves as the source of truth. This log retains raw events indefinitely or for a long retention period.</li><li>Stream Processing: All data processing (transformations, aggregations, validations) is done via a stream processing engine (e.g., Apache Flink, Apache Spark Streaming, or Kafka Streams), which can reprocess historical data from the log if needed.</li><li>Unified Output: Processed data is written to a serving layer (e.g., Iceberg tables, databases) for querying, with the ability to recompute results by replaying the log.</li></ul><h3>Key Components</h3><ul><li>Data Source: External systems (e.g., AppsFlyer Pull APIs) producing events.</li><li>Event Log: A distributed log like Kafka to store raw events.</li><li>Stream Processor: A tool like Flink or Spark Streaming to process events in real-time or reprocess historical data.</li><li>Serving Layer: Storage for processed data (e.g., Iceberg tables on MinIO) accessible for analytics.</li><li>Orchestration: Tools like Apache Airflow to manage API calls and pipeline scheduling.</li></ul><h3>Kappa vs. Lambda Architecture</h3><h3>Lambda Architecture</h3><ul><li>Batch Layer: Processes historical data (e.g., daily Hadoop jobs).</li><li>Speed Layer: Handles real-time data (e.g., Storm or Spark Streaming).</li><li>Serving Layer: Merges batch and speed results for queries.</li><li>Drawbacks: Complexity in maintaining two pipelines, potential inconsistencies between batch and speed layers.</li></ul><h3>Kappa Architecture</h3><ul><li>Eliminates the batch layer, using a single streaming pipeline.</li><li>Simplifies maintenance but requires a robust stream processor and long-retention log.</li><li>Ideal for scenarios where reprocessing historical data is feasible via the log.</li></ul><h3>Benefits of Kappa Architecture</h3><ul><li>Simplicity: Single pipeline reduces code duplication and operational overhead.</li><li>Real-Time: Optimized for near real-time processing, suitable for AppsFlyer’s hourly Pull API data.</li><li>Reprocessability: Historical data can be replayed from the log to fix errors or recompute results.</li><li>Scalability: Handles large-scale data with distributed systems like Kafka and Flink.</li></ul><h3>Challenges</h3><ul><li>Log Retention: Requires significant storage for long-term retention of raw data in Kafka.</li><li>Reprocessing Cost: Replaying large historical datasets can be resource-intensive.</li><li>Latency: Stream processing may introduce slight delays compared to batch processing for large datasets.</li><li>Complexity in Setup: Requires expertise in stream processing tools and managing distributed systems.</li></ul><h3>Kappa Architecture-based ingestion pipeline for AppsFlyer raw data</h3><p>Kappa Architecture fits well because:</p><ul><li>Real-Time Needs: The Pull API allows hourly data fetching, aligning with Kappa’s streaming focus.</li><li>Unified Processing: A single pipeline can handle data for all app_id values and report types, validating against product-specific contracts.</li><li>Reprocessing: Iceberg’s schema evolution and Kafka’s log enable reprocessing if contracts or schemas change.</li><li>Scalability: Multiple products’ data can be partitioned by app_id in Kafka and Iceberg.</li></ul><h3>Components</h3><ul><li>Data Source: AppsFlyer Pull API, fetched hourly via a Python script or Airflow DAG.</li><li>Event Log: Kafka topics (e.g., appsflyer_installs, appsflyer_in_app_events) partitioned by app_id.</li><li>Stream Processor: Flink job to validate data, transform it (e.g., derive install_date), and write to Iceberg.</li><li>Serving Layer: Iceberg tables (installs, in_app_events, etc.) in MinIO, partitioned by app_id, install_date/event_date.</li><li>Orchestration: Airflow to schedule API calls and monitor the pipeline.</li></ul><pre>[AppsFlyer Pull API] --&gt; [Python/Airflow Fetcher] --&gt; [Kafka Topics]<br>    |                                                   |<br>    |-----------------------------------------------[Flink Processor]<br>                                                        |<br>                                                    [Iceberg Tables on MinIO]</pre><h3>Fetch Data from Pull API then forward to Kafka topics</h3><ul><li>Use a Python script or Airflow DAG to call AppsFlyer APIs for each app_id and report type hourly.</li><li>Write raw data (CSV/JSON) to Kafka topics.</li><li>Topics: appsflyer_installs, appsflyer_in_app_events, etc., partitioned by app_id.</li><li>Retention: Set to a long period (e.g., 30 days) or indefinite, depending on storage capacity.</li></ul><pre>import requests<br>from kafka import KafkaProducer<br>import json<br>from datetime import datetime, timedelta<br>from airflow import DAG<br>from airflow.operators.python import PythonOperator<br><br># Kafka Producer<br>producer = KafkaProducer(<br>    bootstrap_servers=[&#39;localhost:9092&#39;],<br>    value_serializer=lambda v: json.dumps(v).encode(&#39;utf-8&#39;)<br>)<br>def fetch_appsflyer_data():<br>    app_ids = [&quot;id123456789&quot;, &quot;id987654321&quot;]<br>    api_token = &quot;your_api_token&quot;<br>    endpoints = [<br>        &quot;https://hq1.appsflyer.com/api/raw-data/export/app/installs_report/v5&quot;,<br>        &quot;https://hq1.appsflyer.com/api/raw-data/export/app/in_app_events_report/v5&quot;<br>    ]<br>    from_date = (datetime.now() - timedelta(hours=1)).strftime(&quot;%Y-%m-%d %H:%M:%S&quot;)<br>    to_date = datetime.now().strftime(&quot;%Y-%m-%d %H:%M:%S&quot;)<br>    for app_id in app_ids:<br>        for endpoint in endpoints:<br>            params = {<br>                &quot;api_token&quot;: api_token,<br>                &quot;from&quot;: from_date,<br>                &quot;to&quot;: to_date,<br>                &quot;timezone&quot;: &quot;UTC&quot;,<br>                &quot;additional_fields&quot;: &quot;placement,device_id,os_version,app_version,sdk_version&quot;<br>            }<br>            response = requests.get(f&quot;{endpoint}?app_id={app_id}&quot;, params=params)<br>            if response.status_code == 200:<br>                topic = &quot;appsflyer_&quot; + endpoint.split(&quot;/&quot;)[-2].replace(&quot;_report&quot;, &quot;&quot;)<br>                lines = response.text.splitlines()<br>                for line in lines[1:]:<br>                    record = {&quot;app_id&quot;: app_id, &quot;data&quot;: line}<br>                    producer.send(topic, key=app_id.encode(&#39;utf-8&#39;), value=record)<br>            else:<br>                print(f&quot;Failed for {app_id}, {endpoint}: {response.status_code}&quot;)<br># Airflow DAG<br>with DAG(&#39;appsflyer_kappa_ingestion&#39;, schedule_interval=&#39;@hourly&#39;, start_date=datetime(2025, 5, 26)) as dag:<br>    fetch_task = PythonOperator(<br>        task_id=&#39;fetch_appsflyer_data&#39;,<br>        python_callable=fetch_appsflyer_data<br>    )</pre><h3>Process with Flink</h3><ul><li>Read from Kafka topics.</li><li>Validate data against the product-specific data contract (e.g., non-null appsflyer_id, valid country_code).</li><li>Transform data (e.g., parse event_value JSON, derive install_date).</li><li>Write to Iceberg tables, partitioned by app_id, install_date/event_date.</li></ul><pre>import json<br>from datetime import datetime<br>from pyflink.common.serialization import SimpleStringSchema<br>from pyflink.common.typeinfo import Types<br>from pyflink.datastream import StreamExecutionEnvironment<br>from pyflink.table import StreamTableEnvironment, EnvironmentSettings<br>from pyflink.table.types import DataTypes<br>from pyflink.table.descriptors import Schema, Kafka, Json<br>from pyflink.table.expressions import col, lit<br>from pyflink.table import TableDescriptor<br><br># Initialize environment<br>env = StreamExecutionEnvironment.get_execution_environment()<br>env_settings = EnvironmentSettings.new_instance().in_streaming_mode().build()<br>t_env = StreamTableEnvironment.create(env, environment_settings=env_settings)<br># Add Iceberg and Hadoop dependencies<br>env.add_jars(<br>    &quot;file:///path/to/iceberg-flink-runtime-1.17-1.4.0.jar&quot;,<br>    &quot;file:///path/to/hadoop-aws-3.3.4.jar&quot;,<br>    &quot;file:///path/to/aws-java-sdk-bundle-1.12.262.jar&quot;<br>)<br># Configure Flink catalog and MinIO<br>t_env.get_config().get_configuration().set_string(<br>    &quot;execution.checkpointing.interval&quot;, &quot;10000&quot;<br>)<br>t_env.execute_sql(&quot;&quot;&quot;<br>    CREATE CATALOG local WITH (<br>        &#39;type&#39;=&#39;iceberg&#39;,<br>        &#39;catalog-type&#39;=&#39;hadoop&#39;,<br>        &#39;warehouse&#39;=&#39;s3://warehouse&#39;,<br>        &#39;property-version&#39;=&#39;1&#39;,<br>        &#39;hadoop.fs.s3a.endpoint&#39;=&#39;http://minio:9000&#39;,<br>        &#39;hadoop.fs.s3a.access.key&#39;=&#39;admin&#39;,<br>        &#39;hadoop.fs.s3a.secret.key&#39;=&#39;password&#39;,<br>        &#39;hadoop.fs.s3a.path.style.access&#39;=&#39;true&#39;<br>    )<br>&quot;&quot;&quot;)<br>t_env.use_catalog(&quot;local&quot;)<br># Define Kafka source for installs<br>t_env.create_temporary_table(<br>    &quot;kafka_installs&quot;,<br>    TableDescriptor.for_connector(&quot;kafka&quot;)<br>        .schema(<br>            Schema.new_builder()<br>                .column(&quot;app_id&quot;, DataTypes.STRING())<br>                .column(&quot;appsflyer_id&quot;, DataTypes.STRING())<br>                .column(&quot;install_time&quot;, DataTypes.TIMESTAMP(3))<br>                .column(&quot;attributed_touch_time&quot;, DataTypes.TIMESTAMP(3))<br>                .column(&quot;media_source&quot;, DataTypes.STRING())<br>                .column(&quot;campaign&quot;, DataTypes.STRING())<br>                .column(&quot;campaign_id&quot;, DataTypes.STRING())<br>                .column(&quot;adset&quot;, DataTypes.STRING())<br>                .column(&quot;adset_id&quot;, DataTypes.STRING())<br>                .column(&quot;ad&quot;, DataTypes.STRING())<br>                .column(&quot;ad_id&quot;, DataTypes.STRING())<br>                .column(&quot;platform&quot;, DataTypes.STRING())<br>                .column(&quot;country_code&quot;, DataTypes.STRING())<br>                .column(&quot;city&quot;, DataTypes.STRING())<br>                .column(&quot;cost_value&quot;, DataTypes.DOUBLE())<br>                .column(&quot;cost_currency&quot;, DataTypes.STRING())<br>                .column(&quot;attribution_type&quot;, DataTypes.STRING())<br>                .column(&quot;placement&quot;, DataTypes.STRING())<br>                .column(&quot;device_id&quot;, DataTypes.STRING())<br>                .column(&quot;os_version&quot;, DataTypes.STRING())<br>                .column(&quot;app_version&quot;, DataTypes.STRING())<br>                .column(&quot;sdk_version&quot;, DataTypes.STRING())<br>                .build()<br>        )<br>        .option(&quot;topic&quot;, &quot;appsflyer_installs&quot;)<br>        .option(&quot;properties.bootstrap.servers&quot;, &quot;localhost:9092&quot;)<br>        .option(&quot;properties.group.id&quot;, &quot;appsflyer_kappa&quot;)<br>        .option(&quot;scan.startup.mode&quot;, &quot;earliest-offset&quot;)<br>        .format(<br>            Json()<br>                .fail_on_missing_field(False)<br>                .json_schema(<br>                    &quot;&quot;&quot;<br>                    {<br>                        &quot;type&quot;: &quot;object&quot;,<br>                        &quot;properties&quot;: {<br>                            &quot;app_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;appsflyer_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;install_time&quot;: {&quot;type&quot;: &quot;string&quot;, &quot;format&quot;: &quot;date-time&quot;},<br>                            &quot;attributed_touch_time&quot;: {&quot;type&quot;: &quot;string&quot;, &quot;format&quot;: &quot;date-time&quot;},<br>                            &quot;media_source&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;campaign&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;campaign_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;adset&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;adset_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;ad&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;ad_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;platform&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;country_code&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;city&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;cost_value&quot;: {&quot;type&quot;: &quot;number&quot;},<br>                            &quot;cost_currency&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;attribution_type&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;placement&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;device_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;os_version&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;app_version&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;sdk_version&quot;: {&quot;type&quot;: &quot;string&quot;}<br>                        }<br>                    }<br>                    &quot;&quot;&quot;<br>                )<br>        )<br>        .build()<br>)<br># Define Kafka source for in-app events<br>t_env.create_temporary_table(<br>    &quot;kafka_in_app_events&quot;,<br>    TableDescriptor.for_connector(&quot;kafka&quot;)<br>        .schema(<br>            Schema.new_builder()<br>                .column(&quot;app_id&quot;, DataTypes.STRING())<br>                .column(&quot;appsflyer_id&quot;, DataTypes.STRING())<br>                .column(&quot;event_name&quot;, DataTypes.STRING())<br>                .column(&quot;event_time&quot;, DataTypes.TIMESTAMP(3))<br>                .column(&quot;event_revenue&quot;, DataTypes.DOUBLE())<br>                .column(&quot;event_revenue_currency&quot;, DataTypes.STRING())<br>                .column(&quot;event_value&quot;, DataTypes.MAP(DataTypes.STRING(), DataTypes.STRING()))<br>                .column(&quot;install_time&quot;, DataTypes.TIMESTAMP(3))<br>                .column(&quot;media_source&quot;, DataTypes.STRING())<br>                .column(&quot;campaign&quot;, DataTypes.STRING())<br>                .column(&quot;campaign_id&quot;, DataTypes.STRING())<br>                .column(&quot;platform&quot;, DataTypes.STRING())<br>                .column(&quot;country_code&quot;, DataTypes.STRING())<br>                .column(&quot;user_id&quot;, DataTypes.STRING())<br>                .column(&quot;device_id&quot;, DataTypes.STRING())<br>                .column(&quot;is_retargeting&quot;, DataTypes.BOOLEAN())<br>                .build()<br>        )<br>        .option(&quot;topic&quot;, &quot;appsflyer_in_app_events&quot;)<br>        .option(&quot;properties.bootstrap.servers&quot;, &quot;localhost:9092&quot;)<br>        .option(&quot;properties.group.id&quot;, &quot;appsflyer_kappa&quot;)<br>        .option(&quot;scan.startup.mode&quot;, &quot;earliest-offset&quot;)<br>        .format(<br>            Json()<br>                .fail_on_missing_field(False)<br>                .json_schema(<br>                    &quot;&quot;&quot;<br>                    {<br>                        &quot;type&quot;: &quot;object&quot;,<br>                        &quot;properties&quot;: {<br>                            &quot;app_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;appsflyer_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;event_name&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;event_time&quot;: {&quot;type&quot;: &quot;string&quot;, &quot;format&quot;: &quot;date-time&quot;},<br>                            &quot;event_revenue&quot;: {&quot;type&quot;: &quot;number&quot;},<br>                            &quot;event_revenue_currency&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;event_value&quot;: {&quot;type&quot;: &quot;object&quot;},<br>                            &quot;install_time&quot;: {&quot;type&quot;: &quot;string&quot;, &quot;format&quot;: &quot;date-time&quot;},<br>                            &quot;media_source&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;campaign&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;campaign_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;platform&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;country_code&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;user_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;device_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;is_retargeting&quot;: {&quot;type&quot;: &quot;boolean&quot;}<br>                        }<br>                    }<br>                    &quot;&quot;&quot;<br>                )<br>        )<br>        .build()<br>)<br># Process Installs<br>installs_table = t_env.from_path(&quot;kafka_installs&quot;)<br>validated_installs = installs_table.filter(<br>    (col(&quot;app_id&quot;) == &quot;id123456789&quot;) &amp;<br>    col(&quot;appsflyer_id&quot;).is_not_null() &amp;<br>    col(&quot;platform&quot;).in_([&quot;iOS&quot;, &quot;Android&quot;]) &amp;<br>    col(&quot;media_source&quot;).is_not_null()<br>).select(<br>    col(&quot;app_id&quot;),<br>    col(&quot;appsflyer_id&quot;),<br>    col(&quot;install_time&quot;),<br>    col(&quot;attributed_touch_time&quot;),<br>    col(&quot;media_source&quot;),<br>    col(&quot;campaign&quot;),<br>    col(&quot;campaign_id&quot;),<br>    col(&quot;adset&quot;),<br>    col(&quot;adset_id&quot;),<br>    col(&quot;ad&quot;),<br>    col(&quot;ad_id&quot;),<br>    col(&quot;platform&quot;),<br>    col(&quot;country_code&quot;),<br>    col(&quot;city&quot;),<br>    col(&quot;cost_value&quot;),<br>    col(&quot;cost_currency&quot;),<br>    col(&quot;attribution_type&quot;),<br>    col(&quot;placement&quot;),<br>    col(&quot;device_id&quot;),<br>    col(&quot;os_version&quot;),<br>    col(&quot;app_version&quot;),<br>    col(&quot;sdk_version&quot;),<br>    col(&quot;install_time&quot;).cast(DataTypes.DATE()).alias(&quot;install_date&quot;)<br>)<br># Write to Iceberg (installs)<br>validated_installs.execute_insert(&quot;appsflyer_db.installs&quot;)<br># Process In-App Events<br>events_table = t_env.from_path(&quot;kafka_in_app_events&quot;)<br>validated_events = events_table.filter(<br>    (col(&quot;app_id&quot;) == &quot;id123456789&quot;) &amp;<br>    col(&quot;appsflyer_id&quot;).is_not_null() &amp;<br>    col(&quot;event_name&quot;).in_([&quot;af_purchase&quot;, &quot;af_login&quot;, &quot;af_level_achieved&quot;]) &amp;<br>    col(&quot;platform&quot;).in_([&quot;iOS&quot;, &quot;Android&quot;]) &amp;<br>    col(&quot;media_source&quot;).is_not_null()<br>).select(<br>    col(&quot;app_id&quot;),<br>    col(&quot;appsflyer_id&quot;),<br>    col(&quot;event_name&quot;),<br>    col(&quot;event_time&quot;),<br>    col(&quot;event_revenue&quot;),<br>    col(&quot;event_revenue_currency&quot;),<br>    col(&quot;event_value&quot;),<br>    col(&quot;install_time&quot;),<br>    col(&quot;media_source&quot;),<br>    col(&quot;campaign&quot;),<br>    col(&quot;campaign_id&quot;),<br>    col(&quot;platform&quot;),<br>    col(&quot;country_code&quot;),<br>    col(&quot;user_id&quot;),<br>    col(&quot;device_id&quot;),<br>    col(&quot;is_retargeting&quot;),<br>    col(&quot;event_time&quot;).cast(DataTypes.DATE()).alias(&quot;event_date&quot;)<br>)<br># Write to Iceberg (in_app_events)<br>validated_events.execute_insert(&quot;appsflyer_db.in_app_events&quot;)<br># Execute<br>t_env.execute(&quot;AppsFlyer Kappa Pipeline&quot;)</pre><h3>Changes to the Data Contract</h3><p>The updated contract reflects the following changes to align with the Kappa Architecture</p><ul><li>Source Update: Changed from Data Locker to Pull API with Kafka as the event log, specifying hourly fetches and staging in Kafka topics (appsflyer_installs, appsflyer_in_app_events).</li><li>SLA Update: Adjusted to reflect near real-time streaming (data available within 1 hour of install_time or event_time) instead of daily batches.</li><li>Processing Details: Added references to Kafka and Flink in the pipeline description, emphasizing streaming validation and transformation.</li><li>Quality Rules: Retained product-specific validations (e.g., event_name enum for id123456789) and added a rule for Kafka partition key (app_id) consistency.</li><li>Governance: Clarified reprocessing capabilities via Kafka log replay and Iceberg schema evolution.</li><li>Destinations: Kept Iceberg tables (installs, in_app_events) with app_id partitioning, ensuring compatibility with the existing schema.</li></ul><pre>data_contract:<br>  name: appsflyer_id123456789<br>  version: 1.1.0<br>  description: Raw data for AppsFlyer app_id=id123456789 from Pull API, ingested via Kappa Architecture with Kafka, Flink, and Apache Iceberg tables in MinIO, covering installs and in-app events.<br>  owner:<br>    team: ProductA Data Team<br>    contact: producta-data@company.com<br>  source:<br>    name: AppsFlyer Pull API<br>    endpoints:<br>      - https://hq1.appsflyer.com/api/raw-data/export/app/installs_report/v5<br>      - https://hq1.appsflyer.com/api/raw-data/export/app/in_app_events_report/v5<br>    delivery: Hourly API calls, streamed to Kafka topics (appsflyer_installs, appsflyer_in_app_events), partitioned by app_id.<br>    parameters:<br>      app_id: id123456789<br>      api_token: Provided via secure configuration<br>      additional_fields: placement,device_id,os_version,app_version,sdk_version<br>    staging:<br>      kafka_topics:<br>        - name: appsflyer_installs<br>          partitions: by app_id<br>          retention: 30 days<br>        - name: appsflyer_in_app_events<br>          partitions: by app_id<br>          retention: 30 days<br>  processing:<br>    pipeline: Kappa Architecture<br>    components:<br>      - name: Kafka<br>        role: Immutable event log for raw API data<br>      - name: Flink<br>        role: Stream processing for validation, transformation, and Iceberg writes<br>      - name: Airflow<br>        role: Orchestrates hourly API calls and monitors pipeline<br>  destinations:<br>    - iceberg_table: local.appsflyer_db.installs<br>      location: s3://warehouse/appsflyer_db/installs/app_id=id123456789<br>      format: Parquet<br>      catalog: HadoopCatalog<br>      schema:<br>        - name: app_id<br>          type: STRING<br>          required: true<br>          description: AppsFlyer app identifier, fixed to id123456789.<br>          constraints:<br>            - equals: &quot;id123456789&quot;<br>        - name: appsflyer_id<br>          type: STRING<br>          required: true<br>          description: Unique device identifier.<br>          constraints:<br>            - not_null<br>            - pattern: &quot;^[0-9a-fA-F-]+$&quot;<br>        - name: install_time<br>          type: TIMESTAMP<br>          required: true<br>          description: Time of app install in UTC.<br>          constraints:<br>            - not_null<br>            - valid_timestamp: &quot;yyyy-MM-dd HH:mm:ss&quot;<br>        - name: attributed_touch_time<br>          type: TIMESTAMP<br>          required: false<br>          description: Time of ad interaction.<br>          constraints:<br>            - valid_timestamp: &quot;yyyy-MM-dd HH:mm:ss&quot;<br>        - name: media_source<br>          type: STRING<br>          required: true<br>          description: Source of the install (e.g., googleadwords_int).<br>          constraints:<br>            - not_null<br>        - name: campaign<br>          type: STRING<br>          required: false<br>          description: Campaign name.<br>        - name: campaign_id<br>          type: STRING<br>          required: false<br>          description: Campaign ID.<br>        - name: adset<br>          type: STRING<br>          required: false<br>          description: Adset name.<br>        - name: adset_id<br>          type: STRING<br>          required: false<br>          description: Adset ID.<br>        - name: ad<br>          type: STRING<br>          required: false<br>          description: Ad name.<br>        - name: ad_id<br>          type: STRING<br>          required: false<br>          description: Ad ID.<br>        - name: platform<br>          type: STRING<br>          required: true<br>          description: Platform (e.g., iOS, Android).<br>          constraints:<br>            - not_null<br>            - enum: [&quot;iOS&quot;, &quot;Android&quot;]<br>        - name: country_code<br>          type: STRING<br>          required: false<br>          description: ISO country code (e.g., US).<br>          constraints:<br>            - pattern: &quot;^[A-Z]{2}$&quot;<br>        - name: city<br>          type: STRING<br>          required: false<br>          description: City of the user.<br>        - name: cost_value<br>          type: DOUBLE<br>          required: false<br>          description: Cost of the install.<br>          constraints:<br>            - min: 0.0<br>        - name: cost_currency<br>          type: STRING<br>          required: false<br>          description: Currency of the cost (e.g., USD).<br>          constraints:<br>            - pattern: &quot;^[A-Z]{3}$&quot;<br>        - name: attribution_type<br>          type: STRING<br>          required: false<br>          description: Type of attribution (e.g., click, impression).<br>          constraints:<br>            - enum: [&quot;click&quot;, &quot;impression&quot;, &quot;view&quot;]<br>        - name: placement<br>          type: STRING<br>          required: false<br>          description: Placement name (Pull API only).<br>        - name: device_id<br>          type: STRING<br>          required: false<br>          description: Device ID (e.g., IDFA, GAID).<br>        - name: os_version<br>          type: STRING<br>          required: false<br>          description: OS version.<br>        - name: app_version<br>          type: STRING<br>          required: false<br>          description: App version.<br>        - name: sdk_version<br>          type: STRING<br>          required: false<br>          description: AppsFlyer SDK version.<br>        - name: install_date<br>          type: DATE<br>          required: true<br>          description: Derived date from install_time.<br>          constraints:<br>            - not_null<br>      partitioning:<br>        fields: [app_id, install_date, platform]<br>        description: Partitioned for product-specific and time-based queries.<br>      quality_rules:<br>        - rule: completeness<br>          description: At least 95% of records must have non-null app_id, appsflyer_id, install_time, and platform.<br>          threshold: 0.95<br>        - rule: uniqueness<br>          description: Combination of appsflyer_id and install_time must be unique within app_id.<br>          fields: [app_id, appsflyer_id, install_time]<br>        - rule: valid_country_code<br>          description: country_code must match ISO 3166-1 alpha-2 format if present.<br>          field: country_code<br>          pattern: &quot;^[A-Z]{2}$&quot;<br>        - rule: kafka_partition_key<br>          description: app_id must match Kafka partition key.<br>          field: app_id<br>          constraint: equals_kafka_partition_key<br>    - iceberg_table: local.appsflyer_db.in_app_events<br>      location: s3://warehouse/appsflyer_db/in_app_events/app_id=id123456789<br>      format: Parquet<br>      catalog: HadoopCatalog<br>      schema:<br>        - name: app_id<br>          type: STRING<br>          required: true<br>          description: AppsFlyer app identifier, fixed to id123456789.<br>          constraints:<br>            - equals: &quot;id123456789&quot;<br>        - name: appsflyer_id<br>          type: STRING<br>          required: true<br>          description: Unique device identifier.<br>          constraints:<br>            - not_null<br>            - pattern: &quot;^[0-9a-fA-F-]+$&quot;<br>        - name: event_name<br>          type: STRING<br>          required: true<br>          description: Name of the event (e.g., af_purchase).<br>          constraints:<br>            - not_null<br>            - enum: [&quot;af_purchase&quot;, &quot;af_login&quot;, &quot;af_level_achieved&quot;] # Product-specific<br>        - name: event_time<br>          type: TIMESTAMP<br>          required: true<br>          description: Time of the event in UTC.<br>          constraints:<br>            - not_null<br>            - valid_timestamp: &quot;yyyy-MM-dd HH:mm:ss&quot;<br>        - name: event_revenue<br>          type: DOUBLE<br>          required: false<br>          description: Revenue associated with the event.<br>          constraints:<br>            - min: 0.0<br>        - name: event_revenue_currency<br>          type: STRING<br>          required: false<br>          description: Currency of the revenue (e.g., USD).<br>          constraints:<br>            - pattern: &quot;^[A-Z]{3}$&quot;<br>        - name: event_value<br>          type: MAP&lt;STRING, STRING&gt;<br>          required: false<br>          description: JSON key-value pairs (e.g., {&quot;item_id&quot;: &quot;123&quot;}).<br>          constraints:<br>            - valid_json: &quot;Parsed as valid JSON string&quot;<br>        - name: install_time<br>          type: TIMESTAMP<br>          required: false<br>          description: Time of app install.<br>          constraints:<br>            - valid_timestamp: &quot;yyyy-MM-dd HH:mm:ss&quot;<br>        - name: media_source<br>          type: STRING<br>          required: true<br>          description: Source of the install or event.<br>          constraints:<br>            - not_null<br>        - name: campaign<br>          type: STRING<br>          required: false<br>          description: Campaign name.<br>        - name: campaign_id<br>          type: STRING<br>          required: false<br>          description: Campaign ID.<br>        - name: platform<br>          type: STRING<br>          required: true<br>          description: Platform (e.g., iOS, Android).<br>          constraints:<br>            - not_null<br>            - enum: [&quot;iOS&quot;, &quot;Android&quot;]<br>        - name: country_code<br>          type: STRING<br>          required: false<br>          description: ISO country code (e.g., US).<br>          constraints:<br>            - pattern: &quot;^[A-Z]{2}$&quot;<br>        - name: user_id<br>          type: STRING<br>          required: false<br>          description: Customer user ID.<br>        - name: device_id<br>          type: STRING<br>          required: false<br>          description: Device ID (e.g., IDFA, GAID).<br>        - name: is_retargeting<br>          type: BOOLEAN<br>          required: false<br>          description: True if event is from a retargeting campaign.<br>        - name: event_date<br>          type: DATE<br>          required: true<br>          description: Derived date from event_time.<br>          constraints:<br>            - not_null<br>      partitioning:<br>        fields: [app_id, event_date, platform]<br>        description: Partitioned for product-specific and time-based queries.<br>      quality_rules:<br>        - rule: completeness<br>          description: At least 95% of records must have non-null app_id, appsflyer_id, event_name, event_time, and platform.<br>          threshold: 0.95<br>        - rule: uniqueness<br>          description: Combination of appsflyer_id and event_time must be unique within app_id.<br>          fields: [app_id, appsflyer_id, event_time]<br>        - rule: valid_event_value<br>          description: event_value must be valid JSON if present.<br>          field: event_value<br>          constraint: valid_json<br>        - rule: valid_country_code<br>          description: country_code must match ISO 3166-1 alpha-2 format if present.<br>          field: country_code<br>          pattern: &quot;^[A-Z]{2}$&quot;<br>        - rule: kafka_partition_key<br>          description: app_id must match Kafka partition key.<br>          field: app_id<br>          constraint: equals_kafka_partition_key<br>  sla:<br>    delivery: Near real-time via Pull API, fetched hourly, and streamed through Kafka and Flink.<br>    latency: Data available in Iceberg tables within 1 hour of install_time or event_time.<br>    freshness: Data reflects installs/events up to the current hour.<br>    reprocessing: Historical data can be replayed from Kafka (30-day retention) for corrections or schema updates.<br>  governance:<br>    change_management:<br>      process: Schema changes, new fields, or updates to quality rules must be approved by ProductA Data Team and communicated 7 days in advance.<br>      notification_channel: producta-data@company.com<br>      reprocessing: Supported via Kafka log replay and Iceberg schema evolution.<br>    access_control:<br>      read: [ProductA Analytics, Data Science Team]<br>      write: [ProductA Data Team]<br>      storage: MinIO bucket s3://warehouse/appsflyer_db/* with RBAC, partitioned by app_id.<br>    monitoring:<br>      tools: Airflow for pipeline orchestration, Flink job monitoring, and Great Expectations for quality checks.<br>      alerts: Notify producta-data@company.com on quality rule violations or pipeline failures.</pre><h3>Enforcing Data Contracts</h3><p>Each app_id has a contract (e.g., for id123456789) specifying schemas and quality rules. The Flink job validates data against these:</p><ul><li>Schema Validation: Ensure fields match the contract (e.g., non-null appsflyer_id).</li><li>Quality Rules: Check completeness (e.g., 95% non-null platform) and uniqueness (e.g., appsflyer_id + install_time).</li><li>Product-Specific Rules: Apply app_id-specific rules (e.g., valid event_name values).</li></ul><p>Invalid records are written to s3://warehouse/invalid_data/id123456789/.</p><h3>Best Practices</h3><ul><li>Kafka Retention: Set retention based on reprocessing needs (e.g., 30 days).</li><li>Contract Management: Store contracts in a Git repository, with one per app_id.</li><li>Monitoring: Use Airflow to monitor API failures and Flink job health.</li><li>Security: Secure Kafka and MinIO with TLS and RBAC.</li></ul><h3>Conclusion</h3><p>Kappa Architecture simplifies the AppsFlyer ingestion pipeline by using a single streaming pipeline with Kafka, Flink, and Iceberg. The proposed pipeline fetches data hourly via Pull APIs, validates it against per-app_id contracts, and stores it in Iceberg tables on MinIO, partitioned by app_id.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=92e22363b686" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Lambda Architecture for Appsflyer ingestion pipeline]]></title>
            <link>https://huynguyen8505.medium.com/lambda-architecture-for-appsflyer-ingestion-pipeline-a6701e875e9f?source=rss-65068ab1dd48------2</link>
            <guid isPermaLink="false">https://medium.com/p/a6701e875e9f</guid>
            <category><![CDATA[lambda-architecture]]></category>
            <dc:creator><![CDATA[Lionel Nguyen]]></dc:creator>
            <pubDate>Tue, 27 May 2025 13:16:43 GMT</pubDate>
            <atom:updated>2025-05-27T13:16:43.660Z</atom:updated>
            <content:encoded><![CDATA[<p>The Lambda Architecture combines batch and streaming layers to provide a robust system for handling historical and real-time data, with a unified serving layer.</p><h3>Overview</h3><p>Lambda Architecture organizes data processing into three layers:</p><ul><li>Batch Layer: Processes historical data in bulk (daily, using Pull API) to compute accurate, comprehensive results stored in Iceberg tables.</li><li>Speed Layer: Handles real-time data (Push API) to provide low-latency, incremental updates, also stored in Iceberg.</li><li>Serving Layer: Merges batch and speed layer data into unified Iceberg tables for querying, partitioned by app_id.</li></ul><h3>Why Lambda Architecture?</h3><ul><li>Accuracy: Batch layer ensures complete, corrected historical data.</li><li>Low Latency: Speed layer delivers real-time insights (e.g., campaign performance).</li><li>Unified Views: Serving layer combines both for consistent analytics.</li><li>Fit for AppsFlyer: Pull API suits daily batch processing; Push API enables real-time streaming.</li></ul><h3>Efficient approach for AppsFlyer data pipeline</h3><ul><li>Daily Batch Processing (Pull API):</li><li>Purpose: Powers a daily performance dashboard with comprehensive, accurate metrics (e.g., installs, in-app events, revenue) for each app_id.</li><li>Strengths: Ensures data completeness, handles corrections (e.g., delayed attributions), and supports complex aggregations (e.g., daily revenue by campaign).</li><li>Fit: Ideal for historical analysis and reporting, as Pull API provides stable, bulk data.</li><li>Real-Time Streaming (Push API):</li><li>Purpose: Drives a monitoring dashboard with near real-time metrics (e.g., current installs, active users) and comparative metrics (e.g., user retention, today’s performance vs. yesterday).</li><li>Strengths: Low-latency insights for immediate action (e.g., campaign adjustments), enhanced by referencing batch data for context.</li><li>Fit: Push API’s real-time streaming aligns with monitoring needs.</li><li>No Unified Serving Layer:</li><li>Rationale: Separate dashboards (performance vs. monitoring) reduce complexity, as you don’t need to merge batch and streaming data.</li><li>Implication: Each pipeline writes to distinct Iceberg tables (batch_* and stream_*), with the streaming pipeline querying batch tables for metrics like retention.</li><li>Streaming Using Batch Data:</li><li>Metrics:</li><li>User Retention: Calculate retention (e.g., day-1 retention) by checking if users from yesterday’s batch (installs) are active today in streaming data.</li><li>Today vs. Yesterday: Compare today’s streaming metrics (e.g., installs, revenue) with yesterday’s batch aggregates.</li><li>Strengths: Enriches real-time monitoring with historical context, leveraging Iceberg’s query capabilities.</li><li>Tailored Dashboards: Performance dashboard (batch) focuses on accuracy; monitoring dashboard (streaming) prioritizes speed.</li><li>Simplified Architecture: No serving layer reduces merge complexity and storage overhead.</li><li>Flexibility: Streaming can leverage batch data for advanced metrics without duplicating processing logic.</li><li>Scalability: Partitioning by app_id supports multiple products.</li><li>Challenges: Requires efficient joins between streaming (Flink) and batch (Iceberg) data, careful schema alignment, and handling data delays.</li><li>Cross-Layer Dependencies: Streaming metrics (e.g., retention) depend on batch data availability, requiring timely batch processing.</li><li>Data Consistency: Schema differences between batch and streaming tables could cause errors in metrics calculations.</li><li>Latency in Batch: Daily batch must complete before streaming metrics can reference it (e.g., by 1 AM for yesterday’s data).</li><li>Complexity in Streaming: Joining streaming data with batch tables in Flink adds processing overhead.</li></ul><h3>Pipeline Components</h3><ul><li>Data Sources:</li><li>Pull API: Fetched daily for historical data (e.g., /installs_report/v5, /in_app_events_report/v5).</li><li>Push API: Streams events to an HTTPS endpoint in near real-time.</li><li>Batch Layer:</li><li>Source: Pull API (/installs_report/v5, /in_app_events_report/v5).</li><li>Tools: Spark for processing, Airflow for scheduling.</li><li>Output: Iceberg tables (installs, in_app_events).</li><li>Schedule: Daily at 12 AM +07.</li><li>Speed Layer:</li><li>Source: Push API to HTTPS endpoint.</li><li>Tools: Kafka for event log, Flink for streaming, HTTPS endpoint (e.g., Flask).</li><li>Output: Iceberg tables (stream_installs, stream_in_app_events).</li><li>Metrics: Joins with batch tables for retention and comparisons.</li><li>No Serving Layer: Each layer’s tables are queried directly by their respective dashboards.</li><li>Storage: MinIO (s3://warehouse/appsflyer_db/), with Iceberg tables partitioned by app_id, install_date/event_date, platform.</li><li>Orchestration: Airflow schedules batch jobs and monitors both layers.</li><li>Contract: One per app_id, defining schemas, quality rules, and SLAs for both layers.</li><li>Dashboards:</li><li>Performance Dashboard: Queries batch tables for daily metrics (e.g., total installs, revenue by media_source).</li><li>Monitoring Dashboard: Queries streaming tables for real-time metrics and batch tables for historical context.</li></ul><pre>[Pull API] --&gt; [Airflow/Spark Batch] --&gt; [Batch Iceberg Tables] --&gt; [Daily Performance Dashboard]<br>[Push API] --&gt; [HTTPS Endpoint] --&gt; [Kafka] --&gt; [Flink Speed] --&gt; [Stream Iceberg Tables] --&gt; [Monitoring Dashboard]<br>                                        ^                             |<br>                                        |---------------------------- [Queries Batch Tables]</pre><h3>Table Structure and Schema</h3><p>To support Lambda Architecture, we’ll define Iceberg tables for each layer:</p><ul><li>Batch Layer: Stores daily processed data (installs, in_app_events).</li><li>Speed Layer: Stores real-time data (stream_installs, stream_in_app_events). Includes ingestion_time for real-time tracking.</li></ul><p>All tables are partitioned by app_id, install_date/event_date.</p><h3>Data Contract</h3><pre>data_contract:<br>  name: appsflyer_id123456789<br>  version: 1.0.0<br>  description: Raw data for AppsFlyer app_id=id123456789 using Pull API (daily batch for performance dashboard) and Push API (real-time streaming for monitoring dashboard) in a Lambda-inspired architecture, stored in Apache Iceberg tables on MinIO.<br>  owner:<br>    team: ProductA Data Team<br>    contact: producta-data@company.com<br>  sources:<br>    - name: AppsFlyer Pull API<br>      endpoints:<br>        - https://hq1.appsflyer.com/api/raw-data/export/app/installs_report/v5<br>        - https://hq1.appsflyer.com/api/raw-data/export/app/in_app_events_report/v5<br>      delivery: Daily batch fetches at 00:00 +07, staged in MinIO, processed by Spark.<br>      parameters:<br>        app_id: id123456789<br>        api_token: Provided via secure configuration<br>        additional_fields: placement,device_id,os_version,app_version,sdk_version<br>      staging: s3://warehouse/staging/batch/id123456789<br>    - name: AppsFlyer Push API<br>      endpoint: https://hq1.appsflyer.com/api/pushapi/v1.0/app/id123456789<br>      delivery: Real-time streaming to HTTPS endpoint, forwarded to Kafka, processed by Flink.<br>      parameters:<br>        app_id: id123456789<br>        auth_token: Configured in AppsFlyer dashboard<br>        event_types: [install, in_app_event]<br>        fields:<br>          mandatory: [app_id, event_name, event_time, advertising_id]<br>          optional: [media_source, campaign, campaign_id, adset, adset_id, ad, ad_id, platform, country_code, city, cost_value, cost_currency, attribution_type, placement, device_id, os_version, app_version, sdk_version, event_value, event_revenue, event_revenue_currency, user_id, is_retargeting]<br>      staging:<br>        customer_endpoint: https://your-endpoint.example.com/appsflyer<br>        kafka_topics:<br>          - name: appsflyer_installs<br>            partitions: by app_id<br>            retention: 7 days<br>          - name: appsflyer_in_app_events<br>            partitions: by app_id<br>            retention: 7 days<br>  processing:<br>    pipeline: Lambda-Inspired Architecture<br>    components:<br>      - name: Batch Layer<br>        tools: [Spark, Airflow]<br>        role: Daily processing of Pull API data for performance dashboard<br>      - name: Speed Layer<br>        tools: [HTTPS Endpoint, Kafka, Flink]<br>        role: Real-time processing of Push API data for monitoring dashboard, querying batch tables for metrics<br>      - name: Airflow<br>        role: Schedules batch jobs, monitors pipeline health<br>    dependencies:<br>      - description: Speed layer queries batch tables (installs, in_app_events) for metrics like user retention and today vs. yesterday comparisons.<br>  destinations:<br>    - layer: batch<br>      dashboard: Performance Dashboard<br>      tables:<br>        - iceberg_table: local.appsflyer_db.installs<br>          location: s3://warehouse/appsflyer_db/installs/app_id=id123456789<br>          format: Parquet<br>          catalog: HadoopCatalog<br>          schema:<br>            - name: app_id<br>              type: STRING<br>              required: true<br>              constraints:<br>                - equals: &quot;id123456789&quot;<br>            - name: appsflyer_id<br>              type: STRING<br>              required: true<br>              constraints:<br>                - not_null<br>                - pattern: &quot;^[0-9a-fA-F-]+$&quot;<br>            - name: install_time<br>              type: TIMESTAMP<br>              required: true<br>              constraints:<br>                - not_null<br>                - valid_timestamp: &quot;yyyy-MM-dd HH:mm:ss&quot;<br>            - name: attributed_touch_time<br>              type: TIMESTAMP<br>              required: false<br>            - name: media_source<br>              type: STRING<br>              required: true<br>              constraints:<br>                - not_null<br>            - name: campaign<br>              type: STRING<br>              required: false<br>            - name: campaign_id<br>              type: STRING<br>              required: false<br>            - name: adset<br>              type: STRING<br>              required: false<br>            - name: adset_id<br>              type: STRING<br>              required: false<br>            - name: ad<br>              type: STRING<br>              required: false<br>            - name: ad_id<br>              type: STRING<br>              required: false<br>            - name: platform<br>              type: STRING<br>              required: true<br>              constraints:<br>                - not_null<br>                - enum: [&quot;iOS&quot;, &quot;Android&quot;]<br>            - name: country_code<br>              type: STRING<br>              required: false<br>              constraints:<br>                - pattern: &quot;^[A-Z]{2}$&quot;<br>            - name: city<br>              type: STRING<br>              required: false<br>            - name: cost_value<br>              type: DOUBLE<br>              required: false<br>              constraints:<br>                - min: 0.0<br>            - name: cost_currency<br>              type: STRING<br>              required: false<br>              constraints:<br>                - pattern: &quot;^[A-Z]{3}$&quot;<br>            - name: attribution_type<br>              type: STRING<br>              required: false<br>              constraints:<br>                - enum: [&quot;click&quot;, &quot;impression&quot;, &quot;view&quot;]<br>            - name: placement<br>              type: STRING<br>              required: false<br>            - name: device_id<br>              type: STRING<br>              required: false<br>            - name: os_version<br>              type: STRING<br>              required: false<br>            - name: app_version<br>              type: STRING<br>              required: false<br>            - name: sdk_version<br>              type: STRING<br>              required: false<br>            - name: install_date<br>              type: DATE<br>              required: true<br>              constraints:<br>                - not_null<br>          partitioning:<br>            fields: [app_id, install_date, platform]<br>          quality_rules:<br>            - rule: completeness<br>              description: 95% of records must have non-null app_id, appsflyer_id, install_time, platform.<br>              threshold: 0.95<br>            - rule: uniqueness<br>              description: Unique appsflyer_id and install_time within app_id.<br>              fields: [app_id, appsflyer_id, install_time]<br>            - rule: valid_country_code<br>              description: country_code must match ISO 3166-1 alpha-2 format if present.<br>              field: country_code<br>              pattern: &quot;^[A-Z]{2}$&quot;<br>        - iceberg_table: local.appsflyer_db.in_app_events<br>          location: s3://warehouse/appsflyer_db/in_app_events/app_id=id123456789<br>          format: Parquet<br>          catalog: HadoopCatalog<br>          schema:<br>            - name: app_id<br>              type: STRING<br>              required: true<br>              constraints:<br>                - equals: &quot;id123456789&quot;<br>            - name: appsflyer_id<br>              type: STRING<br>              required: true<br>              constraints:<br>                - not_null<br>                - pattern: &quot;^[0-9a-fA-F-]+$&quot;<br>            - name: event_name<br>              type: STRING<br>              required: true<br>              constraints:<br>                - not_null<br>                - enum: [&quot;af_purchase&quot;, &quot;af_login&quot;, &quot;af_level_achieved&quot;]<br>            - name: event_time<br>              type: TIMESTAMP<br>              required: true<br>              constraints:<br>                - not_null<br>                - valid_timestamp: &quot;yyyy-MM-dd HH:mm:ss&quot;<br>            - name: event_revenue<br>              type: DOUBLE<br>              required: false<br>              constraints:<br>                - min: 0.0<br>            - name: event_revenue_currency<br>              type: STRING<br>              required: false<br>              constraints:<br>                - pattern: &quot;^[A-Z]{3}$&quot;<br>            - name: event_value<br>              type: MAP&lt;STRING, STRING&gt;<br>              required: false<br>              constraints:<br>                - valid_json<br>            - name: install_time<br>              type: TIMESTAMP<br>              required: false<br>            - name: media_source<br>              type: STRING<br>              required: true<br>              constraints:<br>                - not_null<br>            - name: campaign<br>              type: STRING<br>              required: false<br>            - name: campaign_id<br>              type: STRING<br>              required: false<br>            - name: platform<br>              type: STRING<br>              required: true<br>              constraints:<br>                - not_null<br>                - enum: [&quot;iOS&quot;, &quot;Android&quot;]<br>            - name: country_code<br>              type: STRING<br>              required: false<br>              constraints:<br>                - pattern: &quot;^[A-Z]{2}$&quot;<br>            - name: user_id<br>              type: STRING<br>              required: false<br>            - name: device_id<br>              type: STRING<br>              required: false<br>            - name: is_retargeting<br>              type: BOOLEAN<br>              required: false<br>            - name: event_date<br>              type: DATE<br>              required: true<br>              constraints:<br>                - not_null<br>          partitioning:<br>            fields: [app_id, event_date, platform]<br>          quality_rules:<br>            - rule: completeness<br>              description: 95% of records must have non-null app_id, appsflyer_id, event_name, event_time, platform.<br>              threshold: 0.95<br>            - rule: uniqueness<br>              description: Unique appsflyer_id and event_time within app_id.<br>              fields: [app_id, appsflyer_id, event_time]<br>            - rule: valid_event_value<br>              description: event_value must be valid JSON if present.<br>              field: event_value<br>              constraint: valid_json<br>            - rule: valid_country_code<br>              description: country_code must match ISO 3166-1 alpha-2 format if present.<br>              field: country_code<br>              pattern: &quot;^[A-Z]{2}$&quot;<br>    - layer: speed<br>      dashboard: Monitoring Dashboard<br>      tables:<br>        - iceberg_table: local.appsflyer_db.stream_installs<br>          location: s3://warehouse/appsflyer_db/stream_installs/app_id=id123456789<br>          format: Parquet<br>          catalog: HadoopCatalog<br>          schema:<br>            # Same as installs, adding ingestion_time<br>            - name: ingestion_time<br>              type: TIMESTAMP<br>              required: true<br>              constraints:<br>                - not_null<br>          partitioning:<br>            fields: [app_id, install_date, platform]<br>          quality_rules:<br>            # Same as batch<br>            - rule: kafka_partition_key<br>              description: app_id must match Kafka partition key.<br>              field: app_id<br>              constraint: equals_kafka_partition_key<br>        - iceberg_table: local.appsflyer_db.stream_in_app_events<br>          location: s3://warehouse/appsflyer_db/stream_in_app_events/app_id=id123456789<br>          format: Parquet<br>          catalog: HadoopCatalog<br>          schema:<br>            # Same as in_app_events, adding ingestion_time<br>            - name: ingestion_time<br>              type: TIMESTAMP<br>              required: true<br>              constraints:<br>                - not_null<br>          partitioning:<br>            fields: [app_id, event_date, platform]<br>          quality_rules:<br>            # Same as batch<br>            - rule: kafka_partition_key<br>              description: app_id must match Kafka partition key.<br>              field: app_id<br>              constraint: equals_kafka_partition_key<br>  sla:<br>    batch:<br>      delivery: Daily via Pull API, processed by Spark at 00:00 +07.<br>      latency: Data available in batch tables by 01:00 AM +07 next day.<br>      freshness: Reflects data up to previous day.<br>    speed:<br>      delivery: Real-time via Push API, streamed through Kafka and Flink.<br>      latency: Data available in streaming tables within 5 minutes of event_time.<br>      freshness: Reflects events up to current minute.<br>    metrics:<br>      user_retention: Day-1 retention calculated in speed layer using yesterday’s batch installs and today’s streaming events.<br>      performance_comparison: Today’s streaming installs/revenue compared to yesterday’s batch aggregates.<br>  governance:<br>    change_management:<br>      process: Schema changes or quality rule updates approved by ProductA Data Team, communicated 7 days in advance.<br>      notification_channel: producta-data@company.com<br>      reprocessing: Batch layer recomputes historical data; speed layer supports 7-day Kafka replay.<br>    access_control:<br>      read: [ProductA Analytics, Data Science Team]<br>      write: [ProductA Data Team]<br>      storage: MinIO bucket s3://warehouse/appsflyer_db/* with RBAC, partitioned by app_id.<br>    monitoring:<br>      tools: Airflow for orchestration, Flink job monitoring, Great Expectations for quality checks.<br>      alerts: Notify producta-data@company.com on violations or failures.<br>    privacy_compliance:<br>      description: Comply with CCPA and media source restrictions; no third-party data sharing without consent.</pre><h3>Pipeline Implementation</h3><h3>Process daily Pull API data for the performance dashboard</h3><pre>from pyspark.sql import SparkSession<br>from pyspark.sql.functions import to_date, col<br>from datetime import datetime, timedelta<br>import requests<br><br>spark = SparkSession.builder \<br>    .appName(&quot;AppsFlyerBatch&quot;) \<br>    .config(&quot;spark.jars.packages&quot;, &quot;org.apache.iceberg:iceberg-spark-runtime-3.4_2.12:1.4.0,org.apache.hadoop:hadoop-aws:3.3.4&quot;) \<br>    .config(&quot;spark.sql.catalog.local&quot;, &quot;org.apache.iceberg.spark.SparkCatalog&quot;) \<br>    .config(&quot;spark.sql.catalog.local.type&quot;, &quot;hadoop&quot;) \<br>    .config(&quot;spark.sql.catalog.local.warehouse&quot;, &quot;s3://warehouse&quot;) \<br>    .config(&quot;spark.hadoop.fs.s3a.endpoint&quot;, &quot;http://minio:9000&quot;) \<br>    .config(&quot;spark.hadoop.fs.s3a.access.key&quot;, &quot;admin&quot;) \<br>    .config(&quot;spark.hadoop.fs.s3a.secret.key&quot;, &quot;password&quot;) \<br>    .getOrCreate()<br>def fetch_and_process(app_id, endpoint, table_name):<br>    api_token = &quot;your_api_token&quot;<br>    yesterday = (datetime.now() - timedelta(days=1)).strftime(&quot;%Y-%m-%d&quot;)<br>    params = {&quot;api_token&quot;: api_token, &quot;from&quot;: yesterday, &quot;to&quot;: yesterday, &quot;timezone&quot;: &quot;UTC&quot;}<br>    response = requests.get(f&quot;{endpoint}?app_id={app_id}&quot;, params=params)<br>    if response.status_code == 200:<br>        lines = response.text.splitlines()<br>        data = [[app_id] + line.split(&quot;,&quot;) for line in lines[1:]]<br>        df = spark.createDataFrame(data, schema=[&quot;app_id&quot;, &quot;appsflyer_id&quot;, &quot;install_time&quot;, &quot;attributed_touch_time&quot;, &quot;media_source&quot;, &quot;campaign&quot;, &quot;campaign_id&quot;, &quot;adset&quot;, &quot;adset_id&quot;, &quot;ad&quot;, &quot;ad_id&quot;, &quot;platform&quot;, &quot;country_code&quot;, &quot;city&quot;, &quot;cost_value&quot;, &quot;cost_currency&quot;, &quot;attribution_type&quot;, &quot;placement&quot;, &quot;device_id&quot;, &quot;os_version&quot;, &quot;app_version&quot;, &quot;sdk_version&quot;])<br>        df = df.withColumn(&quot;install_date&quot;, to_date(&quot;install_time&quot;)) \<br>               .filter(col(&quot;app_id&quot;) == app_id)<br>        df.writeTo(f&quot;local.appsflyer_db.{table_name}&quot;).append()<br>    else:<br>        print(f&quot;Failed for {app_id}: {response.status_code}&quot;)<br># Airflow DAG<br>from airflow import DAG<br>from airflow.operators.python import PythonOperator<br>with DAG(&#39;appsflyer_batch&#39;, schedule_interval=&#39;0 0 * * *&#39;, start_date=datetime(2025, 5, 26)) as dag:<br>    process_installs = PythonOperator(<br>        task_id=&#39;process_installs&#39;,<br>        python_callable=lambda: fetch_and_process(&quot;id123456789&quot;, &quot;https://hq1.appsflyer.com/api/raw-data/export/app/installs_report/v5&quot;, &quot;installs&quot;)<br>    )</pre><h3>Process real-time Push API events for the monitoring dashboard, calculating metrics using batch data</h3><pre># HTTPS Endpoint (Flask)<br>from flask import Flask, request<br>from kafka import KafkaProducer<br>import json<br><br>app = Flask(__name__)<br>producer = KafkaProducer(bootstrap_servers=[&#39;localhost:9092&#39;], value_serializer=lambda v: json.dumps(v).encode(&#39;utf-8&#39;))<br>@app.route(&#39;/appsflyer&#39;, methods=[&#39;POST&#39;])<br>def receive_push():<br>    data = request.json<br>    app_id = data.get(&#39;app_id&#39;)<br>    topic = &#39;appsflyer_installs&#39; if data.get(&#39;event_name&#39;) == &#39;install&#39; else &#39;appsflyer_in_app_events&#39;<br>    producer.send(topic, key=app_id.encode(&#39;utf-8&#39;), value=data)<br>    return &#39;&#39;, 200<br>if __name__ == &#39;__main__&#39;:<br>    app.run(host=&#39;0.0.0.0&#39;, port=443, ssl_context=(&#39;cert.pem&#39;, &#39;key.pem&#39;))</pre><pre>import json<br>from datetime import datetime<br>from pyflink.common.serialization import SimpleStringSchema<br>from pyflink.common.typeinfo import Types<br>from pyflink.datastream import StreamExecutionEnvironment<br>from pyflink.table import StreamTableEnvironment, EnvironmentSettings<br>from pyflink.table.types import DataTypes<br>from pyflink.table.descriptors import Schema, Kafka, Json, FileSystem<br>from pyflink.table.expressions import col, lit<br>from pyflink.table import TableDescriptor<br><br># Initialize environment<br>env = StreamExecutionEnvironment.get_execution_environment()<br>env_settings = EnvironmentSettings.new_instance().in_streaming_mode().build()<br>t_env = StreamTableEnvironment.create(env, environment_settings=env_settings)<br># Add Iceberg and Hadoop dependencies<br>env.add_jars(<br>    &quot;file:///path/to/iceberg-flink-runtime-1.17-1.4.0.jar&quot;,<br>    &quot;file:///path/to/hadoop-aws-3.3.4.jar&quot;,<br>    &quot;file:///path/to/aws-java-sdk-bundle-1.12.262.jar&quot;<br>)<br># Configure Flink catalog and MinIO<br>t_env.get_config().get_configuration().set_string(<br>    &quot;execution.checkpointing.interval&quot;, &quot;10000&quot;<br>)<br>t_env.execute_sql(&quot;&quot;&quot;<br>    CREATE CATALOG local WITH (<br>        &#39;type&#39;=&#39;iceberg&#39;,<br>        &#39;catalog-type&#39;=&#39;hadoop&#39;,<br>        &#39;warehouse&#39;=&#39;s3://warehouse&#39;,<br>        &#39;property-version&#39;=&#39;1&#39;,<br>        &#39;hadoop.fs.s3a.endpoint&#39;=&#39;http://minio:9000&#39;,<br>        &#39;hadoop.fs.s3a.access.key&#39;=&#39;admin&#39;,<br>        &#39;hadoop.fs.s3a.secret.key&#39;=&#39;password&#39;,<br>        &#39;hadoop.fs.s3a.path.style.access&#39;=&#39;true&#39;<br>    )<br>&quot;&quot;&quot;)<br>t_env.use_catalog(&quot;local&quot;)<br># Define Kafka source for installs<br>t_env.create_temporary_table(<br>    &quot;kafka_installs&quot;,<br>    TableDescriptor.for_connector(&quot;kafka&quot;)<br>        .schema(<br>            Schema.new_builder()<br>                .column(&quot;app_id&quot;, DataTypes.STRING())<br>                .column(&quot;appsflyer_id&quot;, DataTypes.STRING())<br>                .column(&quot;install_time&quot;, DataTypes.TIMESTAMP(3))<br>                .column(&quot;attributed_touch_time&quot;, DataTypes.TIMESTAMP(3))<br>                .column(&quot;media_source&quot;, DataTypes.STRING())<br>                .column(&quot;campaign&quot;, DataTypes.STRING())<br>                .column(&quot;campaign_id&quot;, DataTypes.STRING())<br>                .column(&quot;adset&quot;, DataTypes.STRING())<br>                .column(&quot;adset_id&quot;, DataTypes.STRING())<br>                .column(&quot;ad&quot;, DataTypes.STRING())<br>                .column(&quot;ad_id&quot;, DataTypes.STRING())<br>                .column(&quot;platform&quot;, DataTypes.STRING())<br>                .column(&quot;country_code&quot;, DataTypes.STRING())<br>                .column(&quot;city&quot;, DataTypes.STRING())<br>                .column(&quot;cost_value&quot;, DataTypes.DOUBLE())<br>                .column(&quot;cost_currency&quot;, DataTypes.STRING())<br>                .column(&quot;attribution_type&quot;, DataTypes.STRING())<br>                .column(&quot;placement&quot;, DataTypes.STRING())<br>                .column(&quot;device_id&quot;, DataTypes.STRING())<br>                .column(&quot;os_version&quot;, DataTypes.STRING())<br>                .column(&quot;app_version&quot;, DataTypes.STRING())<br>                .column(&quot;sdk_version&quot;, DataTypes.STRING())<br>                .build()<br>        )<br>        .option(&quot;topic&quot;, &quot;appsflyer_installs&quot;)<br>        .option(&quot;properties.bootstrap.servers&quot;, &quot;localhost:9092&quot;)<br>        .option(&quot;properties.group.id&quot;, &quot;appsflyer_speed&quot;)<br>        .option(&quot;scan.startup.mode&quot;, &quot;latest-offset&quot;)<br>        .format(<br>            Json()<br>                .fail_on_missing_field(False)<br>                .json_schema(<br>                    &quot;&quot;&quot;<br>                    {<br>                        &quot;type&quot;: &quot;object&quot;,<br>                        &quot;properties&quot;: {<br>                            &quot;app_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;appsflyer_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;install_time&quot;: {&quot;type&quot;: &quot;string&quot;, &quot;format&quot;: &quot;date-time&quot;},<br>                            &quot;attributed_touch_time&quot;: {&quot;type&quot;: &quot;string&quot;, &quot;format&quot;: &quot;date-time&quot;},<br>                            &quot;media_source&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;campaign&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;campaign_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;adset&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;adset_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;ad&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;ad_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;platform&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;country_code&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;city&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;cost_value&quot;: {&quot;type&quot;: &quot;number&quot;},<br>                            &quot;cost_currency&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;attribution_type&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;placement&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;device_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;os_version&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;app_version&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;sdk_version&quot;: {&quot;type&quot;: &quot;string&quot;}<br>                        }<br>                    }<br>                    &quot;&quot;&quot;<br>                )<br>        )<br>        .build()<br>)<br># Define Kafka source for in-app events<br>t_env.create_temporary_table(<br>    &quot;kafka_in_app_events&quot;,<br>    TableDescriptor.for_connector(&quot;kafka&quot;)<br>        .schema(<br>            Schema.new_builder()<br>                .column(&quot;app_id&quot;, DataTypes.STRING())<br>                .column(&quot;appsflyer_id&quot;, DataTypes.STRING())<br>                .column(&quot;event_name&quot;, DataTypes.STRING())<br>                .column(&quot;event_time&quot;, DataTypes.TIMESTAMP(3))<br>                .column(&quot;event_revenue&quot;, DataTypes.DOUBLE())<br>                .column(&quot;event_revenue_currency&quot;, DataTypes.STRING())<br>                .column(&quot;event_value&quot;, DataTypes.MAP(DataTypes.STRING(), DataTypes.STRING()))<br>                .column(&quot;install_time&quot;, DataTypes.TIMESTAMP(3))<br>                .column(&quot;media_source&quot;, DataTypes.STRING())<br>                .column(&quot;campaign&quot;, DataTypes.STRING())<br>                .column(&quot;campaign_id&quot;, DataTypes.STRING())<br>                .column(&quot;platform&quot;, DataTypes.STRING())<br>                .column(&quot;country_code&quot;, DataTypes.STRING())<br>                .column(&quot;user_id&quot;, DataTypes.STRING())<br>                .column(&quot;device_id&quot;, DataTypes.STRING())<br>                .column(&quot;is_retargeting&quot;, DataTypes.BOOLEAN())<br>                .build()<br>        )<br>        .option(&quot;topic&quot;, &quot;appsflyer_in_app_events&quot;)<br>        .option(&quot;properties.bootstrap.servers&quot;, &quot;localhost:9092&quot;)<br>        .option(&quot;properties.group.id&quot;, &quot;appsflyer_speed&quot;)<br>        .option(&quot;scan.startup.mode&quot;, &quot;latest-offset&quot;)<br>        .format(<br>            Json()<br>                .fail_on_missing_field(False)<br>                .json_schema(<br>                    &quot;&quot;&quot;<br>                    {<br>                        &quot;type&quot;: &quot;object&quot;,<br>                        &quot;properties&quot;: {<br>                            &quot;app_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;appsflyer_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;event_name&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;event_time&quot;: {&quot;type&quot;: &quot;string&quot;, &quot;format&quot;: &quot;date-time&quot;},<br>                            &quot;event_revenue&quot;: {&quot;type&quot;: &quot;number&quot;},<br>                            &quot;event_revenue_currency&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;event_value&quot;: {&quot;type&quot;: &quot;object&quot;},<br>                            &quot;install_time&quot;: {&quot;type&quot;: &quot;string&quot;, &quot;format&quot;: &quot;date-time&quot;},<br>                            &quot;media_source&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;campaign&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;campaign_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;platform&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;country_code&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;user_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;device_id&quot;: {&quot;type&quot;: &quot;string&quot;},<br>                            &quot;is_retargeting&quot;: {&quot;type&quot;: &quot;boolean&quot;}<br>                        }<br>                    }<br>                    &quot;&quot;&quot;<br>                )<br>        )<br>        .build()<br>)<br># Process Installs<br>installs_table = t_env.from_path(&quot;kafka_installs&quot;)<br>validated_installs = installs_table.filter(<br>    (col(&quot;app_id&quot;) == &quot;id123456789&quot;) &amp;<br>    col(&quot;appsflyer_id&quot;).is_not_null() &amp;<br>    col(&quot;platform&quot;).in_([&quot;iOS&quot;, &quot;Android&quot;]) &amp;<br>    col(&quot;media_source&quot;).is_not_null()<br>).select(<br>    col(&quot;*&quot;),<br>    col(&quot;install_time&quot;).cast(DataTypes.DATE()).alias(&quot;install_date&quot;),<br>    lit(datetime.now().isoformat()).cast(DataTypes.TIMESTAMP(3)).alias(&quot;ingestion_time&quot;)<br>)<br># Write to Iceberg (stream_installs)<br>validated_installs.execute_insert(&quot;appsflyer_db.stream_installs&quot;)<br># Process In-App Events<br>events_table = t_env.from_path(&quot;kafka_in_app_events&quot;)<br>validated_events = events_table.filter(<br>    (col(&quot;app_id&quot;) == &quot;id123456789&quot;) &amp;<br>    col(&quot;appsflyer_id&quot;).is_not_null() &amp;<br>    col(&quot;event_name&quot;).in_([&quot;af_purchase&quot;, &quot;af_login&quot;, &quot;af_level_achieved&quot;]) &amp;<br>    col(&quot;platform&quot;).in_([&quot;iOS&quot;, &quot;Android&quot;]) &amp;<br>    col(&quot;media_source&quot;).is_not_null()<br>).select(<br>    col(&quot;*&quot;),<br>    col(&quot;event_time&quot;).cast(DataTypes.DATE()).alias(&quot;event_date&quot;),<br>    lit(datetime.now().isoformat()).cast(DataTypes.TIMESTAMP(3)).alias(&quot;ingestion_time&quot;)<br>)<br># Write to Iceberg (stream_in_app_events)<br>validated_events.execute_insert(&quot;appsflyer_db.stream_in_app_events&quot;)<br># Day-1 Retention<br># Load yesterday&#39;s batch installs<br>yesterday_installs = t_env.sql_query(&quot;&quot;&quot;<br>    SELECT appsflyer_id, install_date<br>    FROM appsflyer_db.installs<br>    WHERE app_id = &#39;id123456789&#39; AND install_date = DATE_SUB(CURRENT_DATE, 1)<br>&quot;&quot;&quot;)<br># Join with today&#39;s streaming events<br>retention_table = validated_events.join(<br>    yesterday_installs,<br>    (validated_events.appsflyer_id == yesterday_installs.appsflyer_id) &amp;<br>    (validated_events.event_date == lit(datetime.now().date()).cast(DataTypes.DATE()))<br>).select(<br>    lit(1).alias(&quot;retained_user&quot;)<br>)<br># Aggregate retention<br>retention_metric = retention_table.group_by().aggregate(<br>    retention_table.retained_user.count().alias(&quot;retained_count&quot;)<br>).select(<br>    col(&quot;retained_count&quot;) / yesterday_installs.count().alias(&quot;day1_retention&quot;)<br>)<br># Output retention (e.g., to Kafka or console)<br>retention_metric.execute().print()<br># Today vs. Yesterday Comparison<br># Yesterday&#39;s batch metrics<br>yesterday_metrics = t_env.sql_query(&quot;&quot;&quot;<br>    SELECT COUNT(*) as install_count, COALESCE(SUM(cost_value), 0.0) as total_cost<br>    FROM appsflyer_db.installs<br>    WHERE app_id = &#39;id123456789&#39; AND install_date = DATE_SUB(CURRENT_DATE, 1)<br>&quot;&quot;&quot;)<br># Today&#39;s streaming metrics<br>today_metrics = validated_installs.filter(<br>    col(&quot;install_date&quot;) == lit(datetime.now().date()).cast(DataTypes.DATE())<br>).group_by().aggregate(<br>    validated_installs.app_id.count().alias(&quot;install_count&quot;),<br>    validated_installs.cost_value.sum().alias(&quot;total_cost&quot;)<br>)<br># Compare<br>comparison = today_metrics.cross_join(yesterday_metrics).select(<br>    col(&quot;today.install_count&quot;).alias(&quot;today_installs&quot;),<br>    col(&quot;yesterday.install_count&quot;).alias(&quot;yesterday_installs&quot;),<br>    (col(&quot;today.install_count&quot;) / col(&quot;yesterday.install_count&quot;)).alias(&quot;install_ratio&quot;),<br>    col(&quot;today.total_cost&quot;).alias(&quot;today_cost&quot;),<br>    col(&quot;yesterday.total_cost&quot;).alias(&quot;yesterday_cost&quot;),<br>    (col(&quot;today.total_cost&quot;) / col(&quot;yesterday.total_cost&quot;)).alias(&quot;cost_ratio&quot;)<br>)<br># Output comparison (e.g., to Kafka or console)<br>comparison.execute().print()<br># Execute<br>t_env.execute(&quot;AppsFlyer Speed Layer&quot;)</pre><h3>Metrics Calculation</h3><ul><li>User Retention (Day-1): Percentage of users who installed yesterday (batch) and performed an event today (streaming).</li></ul><pre>SELECT COUNT(DISTINCT s.appsflyer_id) / COUNT(DISTINCT b.appsflyer_id) as day1_retention<br>FROM stream_in_app_events s<br>JOIN installs b<br>ON s.appsflyer_id = b.appsflyer_id<br>WHERE s.app_id = &#39;id123456789&#39; AND s.event_date = CURRENT_DATE<br>  AND b.app_id = &#39;id123456789&#39; AND b.install_date = CURRENT_DATE - INTERVAL 1 DAY</pre><ul><li>Today vs. Yesterday Performance: Compare today’s installs/revenue (streaming) with yesterday’s (batch).</li></ul><pre>SELECT<br>    t.install_count as today_installs,<br>    y.install_count as yesterday_installs,<br>    t.install_count / y.install_count as install_ratio<br>FROM (<br>    SELECT COUNT(*) as install_count<br>    FROM stream_installs<br>    WHERE app_id = &#39;id123456789&#39; AND install_date = CURRENT_DATE<br>) t<br>CROSS JOIN (<br>    SELECT COUNT(*) as install_count<br>    FROM installs<br>    WHERE app_id = &#39;id123456789&#39; AND install_date = CURRENT_DATE - INTERVAL 1 DAY<br>) y</pre><h3>Best Practices</h3><ul><li>Timeliness: Ensure batch jobs complete by 1 AM +07 for streaming metrics.</li><li>Performance: Cache batch data in Flink or use Iceberg’s partitioning for efficient joins.</li><li>Scalability: Partition Kafka and Iceberg by app_id for parallel processing.</li><li>Security: Use HTTPS for Push API endpoint, RBAC for MinIO, and secure API tokens.</li><li>Compliance: Filter user-level data to comply with CCPA and media source restrictions.</li><li>Monitoring: Use Airflow for pipeline health, Great Expectations for quality checks.</li><li>Schema Alignment: Maintain consistent schemas across batch and streaming tables.</li></ul><h3>Conclusion</h3><p>The proposed Lambda Architecture pipeline uses AppsFlyer’s Pull API a performance dashboard and Push API for a monitoring dashboard, with streaming leveraging batch data for metrics like user retention and performance comparisons, can optimize for both comprehensive historical insights and immediate monitoring needs. The Lambda-inspired pipeline uses Spark/Airflow for batch, Flink/Kafka for streaming, and Iceberg tables on MinIO. The data contract ensures governance and consistency.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a6701e875e9f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Creating a data contract for AppsFlyer raw data]]></title>
            <link>https://huynguyen8505.medium.com/creating-a-data-contract-for-appsflyer-raw-data-69e0132aa766?source=rss-65068ab1dd48------2</link>
            <guid isPermaLink="false">https://medium.com/p/69e0132aa766</guid>
            <category><![CDATA[data-contract]]></category>
            <dc:creator><![CDATA[Lionel Nguyen]]></dc:creator>
            <pubDate>Sat, 24 May 2025 10:16:01 GMT</pubDate>
            <atom:updated>2025-05-24T10:16:01.209Z</atom:updated>
            <content:encoded><![CDATA[<p>Creating a data contract for AppsFlyer raw data in the context of the proposed Apache Iceberg tables stored in MinIO involves defining a formal agreement that specifies the structure, quality, and expectations for the data ingested from AppsFlyer’s raw data endpoints (e.g., installs, in-app events, uninstalls, etc., separated into organic and non-organic tables). A data contract ensures that data producers (e.g., AppsFlyer APIs or Data Locker) and consumers (e.g., analytics teams, data pipelines) agree on the schema, semantics, quality, and governance of the data. This is particularly important for AppsFlyer data, given its complexity (e.g., JSON fields like event_value, nullable campaign fields) and the separation of organic and non-organic datasets.</p><h3>What is a Data Contract?</h3><p>A data contract is a documented agreement between data producers and consumers that defines:</p><ul><li>Schema: The structure of the data (fields, types, constraints).</li><li>Semantics: The meaning and expected values of each field.</li><li>Quality Rules: Expectations for data completeness, accuracy, and consistency.</li><li>Governance: Ownership, versioning, and change management processes.</li><li>SLAs: Timeliness, availability, and delivery guarantees.</li><li>Access and Security: Who can access the data and how it’s protected.</li></ul><p>For AppsFlyer raw data, the contract ensures that data from endpoints like /installs_report/v5 or Data Locker aligns with the Iceberg tables’ schemas and meets analytical needs (e.g., product performance analysis).</p><h3>Key Considerations for AppsFlyer Data Contract</h3><p>Based on the <a href="https://support.appsflyer.com/hc/en-us/articles/208387843-Raw-data-field-dictionary">AppsFlyer raw data field dictionary</a> and <a href="https://dev.appsflyer.com/hc/reference/overview-5">API reference</a>, the data contract must address:</p><ul><li>Multiple Endpoints: Separate schemas for installs, in-app events, uninstalls, reinstalls, and re-engagements, with further splits for organic and non-organic data.</li><li>Field Variability: Non-organic data includes campaign fields (e.g., media_source, campaign_id), while organic data omits them. Some fields (e.g., placement) are specific to Data Locker or Pull API.</li><li>Nested Data: The event_value field in in-app events is a JSON string (parsed as a MAP in Iceberg), requiring specific parsing rules.</li><li>Data Quality: Issues like missing appsflyer_id, invalid timestamps, or inconsistent country_code formats need validation.</li><li>Timeliness: AppsFlyer data may be delivered via Data Locker (delayed) or Pull API (near real-time), affecting SLAs.</li><li>Schema Evolution: Iceberg’s schema evolution supports adding fields, but the contract must define how changes are communicated and applied.</li></ul><h3>Sample Data Contract</h3><p>Below is a data contract for a single app_id (e.g., id123456789), covering installs and in_app_events. It can be replicated for other app_id values, with product-specific customizations (e.g., unique event_name lists). The contract is written in a structured format (e.g., YAML or JSON-like) for clarity and can be extended to other tables (installs_organic, in_app_events_organic, etc.). It assumes data is ingested from AppsFlyer’s Pull API and processed into Iceberg tables in MinIO.</p><pre>data_contract:<br>  name: appsflyer_id123456789<br>  version: 1.0.0<br>  description: Raw data for AppsFlyer app_id=id123456789 from Pull API, stored in Apache Iceberg tables in MinIO, covering installs and in-app events.<br>  owner:<br>    team: ProductA Data Team<br>    contact: producta-data@company.com<br>  source:<br>    name: AppsFlyer Pull API<br>    endpoints:<br>      - https://hq1.appsflyer.com/api/raw-data/export/app/installs_report/v5<br>      - https://hq1.appsflyer.com/api/raw-data/export/app/in_app_events_report/v5<br>    delivery: Programmatic API calls, staged in MinIO at s3://warehouse/staging/id123456789<br>    parameters:<br>      app_id: id123456789<br>      api_token: Provided via secure configuration<br>  destinations:<br>    - iceberg_table: local.appsflyer_db.installs<br>      location: s3://warehouse/appsflyer_db/installs/app_id=id123456789<br>      format: Parquet<br>      catalog: HadoopCatalog<br>      schema:<br>        - name: app_id<br>          type: STRING<br>          required: true<br>          description: AppsFlyer app identifier, fixed to id123456789.<br>          constraints:<br>            - equals: &quot;id123456789&quot;<br>        - name: appsflyer_id<br>          type: STRING<br>          required: true<br>          description: Unique device identifier.<br>          constraints:<br>            - not_null<br>            - pattern: &quot;^[0-9a-fA-F-]+$&quot;<br>        - name: install_time<br>          type: TIMESTAMP<br>          required: true<br>          description: Time of app install in UTC.<br>          constraints:<br>            - not_null<br>            - valid_timestamp: &quot;yyyy-MM-dd HH:mm:ss&quot;<br>        - name: attributed_touch_time<br>          type: TIMESTAMP<br>          required: false<br>          description: Time of ad interaction.<br>        - name: media_source<br>          type: STRING<br>          required: true<br>          description: Source of the install (e.g., googleadwords_int).<br>          constraints:<br>            - not_null<br>        - name: campaign<br>          type: STRING<br>          required: false<br>          description: Campaign name.<br>        - name: campaign_id<br>          type: STRING<br>          required: false<br>          description: Campaign ID.<br>        - name: adset<br>          type: STRING<br>          required: false<br>          description: Adset name.<br>        - name: adset_id<br>          type: STRING<br>          required: false<br>          description: Adset ID.<br>        - name: ad<br>          type: STRING<br>          required: false<br>          description: Ad name.<br>        - name: ad_id<br>          type: STRING<br>          required: false<br>          description: Ad ID.<br>        - name: platform<br>          type: STRING<br>          required: true<br>          description: Platform (e.g., iOS, Android).<br>          constraints:<br>            - not_null<br>            - enum: [&quot;iOS&quot;, &quot;Android&quot;]<br>        - name: country_code<br>          type: STRING<br>          required: false<br>          description: ISO country code (e.g., US).<br>          constraints:<br>            - pattern: &quot;^[A-Z]{2}$&quot;<br>        - name: city<br>          type: STRING<br>          required: false<br>          description: City of the user.<br>        - name: cost_value<br>          type: DOUBLE<br>          required: false<br>          description: Cost of the install.<br>          constraints:<br>            - min: 0.0<br>        - name: cost_currency<br>          type: STRING<br>          required: false<br>          description: Currency of the cost (e.g., USD).<br>          constraints:<br>            - pattern: &quot;^[A-Z]{3}$&quot;<br>        - name: attribution_type<br>          type: STRING<br>          required: false<br>          description: Type of attribution (e.g., click, impression).<br>          constraints:<br>            - enum: [&quot;click&quot;, &quot;impression&quot;, &quot;view&quot;]<br>        - name: placement<br>          type: STRING<br>          required: false<br>          description: Placement name (Pull API only).<br>        - name: device_id<br>          type: STRING<br>          required: false<br>          description: Device ID (e.g., IDFA, GAID).<br>        - name: os_version<br>          type: STRING<br>          required: false<br>          description: OS version.<br>        - name: app_version<br>          type: STRING<br>          required: false<br>          description: App version.<br>        - name: sdk_version<br>          type: STRING<br>          required: false<br>          description: AppsFlyer SDK version.<br>        - name: install_date<br>          type: DATE<br>          required: true<br>          description: Derived date from install_time.<br>          constraints:<br>            - not_null<br>      partitioning:<br>        fields: [app_id, install_date, platform]<br>      quality_rules:<br>        - rule: completeness<br>          description: At least 95% of records must have non-null app_id, appsflyer_id, install_time, and platform.<br>          threshold: 0.95<br>        - rule: uniqueness<br>          description: Combination of appsflyer_id and install_time must be unique.<br>          fields: [appsflyer_id, install_time]<br>        - rule: valid_country_code<br>          description: country_code must match ISO 3166-1 alpha-2 format if present.<br>          field: country_code<br>          pattern: &quot;^[A-Z]{2}$&quot;<br>    - iceberg_table: local.appsflyer_db.in_app_events<br>      location: s3://warehouse/appsflyer_db/in_app_events/app_id=id123456789<br>      format: Parquet<br>      catalog: HadoopCatalog<br>      schema:<br>        - name: app_id<br>          type: STRING<br>          required: true<br>          description: AppsFlyer app identifier, fixed to id123456789.<br>          constraints:<br>            - equals: &quot;id123456789&quot;<br>        - name: appsflyer_id<br>          type: STRING<br>          required: true<br>          description: Unique device identifier.<br>          constraints:<br>            - not_null<br>            - pattern: &quot;^[0-9a-fA-F-]+$&quot;<br>        - name: event_name<br>          type: STRING<br>          required: true<br>          description: Name of the event (e.g., af_purchase).<br>          constraints:<br>            - not_null<br>            - enum: [&quot;af_purchase&quot;, &quot;af_login&quot;, &quot;af_level_achieved&quot;] # Product-specific<br>        - name: event_time<br>          type: TIMESTAMP<br>          required: true<br>          description: Time of the event in UTC.<br>          constraints:<br>            - not_null<br>            - valid_timestamp: &quot;yyyy-MM-dd HH:mm:ss&quot;<br>        - name: event_revenue<br>          type: DOUBLE<br>          required: false<br>          description: Revenue associated with the event.<br>          constraints:<br>            - min: 0.0<br>        - name: event_revenue_currency<br>          type: STRING<br>          required: false<br>          description: Currency of the revenue (e.g., USD).<br>          constraints:<br>            - pattern: &quot;^[A-Z]{3}$&quot;<br>        - name: event_value<br>          type: MAP&lt;STRING, STRING&gt;<br>          required: false<br>          description: JSON key-value pairs (e.g., {&quot;item_id&quot;: &quot;123&quot;}).<br>          constraints:<br>            - valid_json: &quot;Parsed as valid JSON string&quot;<br>        - name: install_time<br>          type: TIMESTAMP<br>          required: false<br>          description: Time of app install.<br>        - name: media_source<br>          type: STRING<br>          required: true<br>          description: Source of the install or event.<br>          constraints:<br>            - not_null<br>        - name: campaign<br>          type: STRING<br>          required: false<br>          description: Campaign name.<br>        - name: campaign_id<br>          type: STRING<br>          required: false<br>          description: Campaign ID.<br>        - name: platform<br>          type: STRING<br>          required: true<br>          description: Platform (e.g., iOS, Android).<br>          constraints:<br>            - not_null<br>            - enum: [&quot;iOS&quot;, &quot;Android&quot;]<br>        - name: country_code<br>          type: STRING<br>          required: false<br>          description: ISO country code (e.g., US).<br>          constraints:<br>            - pattern: &quot;^[A-Z]{2}$&quot;<br>        - name: user_id<br>          type: STRING<br>          required: false<br>          description: Customer user ID.<br>        - name: device_id<br>          type: STRING<br>          required: false<br>          description: Device ID (e.g., IDFA, GAID).<br>        - name: is_retargeting<br>          type: BOOLEAN<br>          required: false<br>          description: True if event is from a retargeting campaign.<br>        - name: event_date<br>          type: DATE<br>          required: true<br>          description: Derived date from event_time.<br>          constraints:<br>            - not_null<br>      partitioning:<br>        fields: [app_id, event_date, platform]<br>      quality_rules:<br>        - rule: completeness<br>          description: At least 95% of records must have non-null app_id, appsflyer_id, event_name, event_time, and platform.<br>          threshold: 0.95<br>        - rule: uniqueness<br>          description: Combination of appsflyer_id and event_time must be unique.<br>          fields: [appsflyer_id, event_time]<br>        - rule: valid_event_value<br>          description: event_value must be valid JSON if present.<br>          field: event_value<br>        - rule: valid_country_code<br>          description: country_code must match ISO 3166-1 alpha-2 format if present.<br>          field: country_code<br>          pattern: &quot;^[A-Z]{2}$&quot;<br>  sla:<br>    delivery: Near real-time via Pull API, fetched hourly.<br>    latency: Data available within 1 hour of event_time or install_time.<br>    freshness: Data reflects events/installs up to the current hour.<br>  governance:<br>    change_management:<br>      process: Schema changes or updates must be approved by ProductA Data Team and communicated 7 days in advance.<br>      notification_channel: producta-data@company.com<br>    access_control:<br>      read: [ProductA Analytics, Data Science Team]<br>      write: [ProductA Data Team]<br>      storage: MinIO bucket s3://warehouse/appsflyer_db/* with RBAC.</pre><h3>Steps to Create and Enforce the Data Contract</h3><h3>Define the Contract</h3><ul><li>Document: Store the contract in a versioned format (e.g., YAML or JSON) in a repository (e.g., Git) or a contract management tool like Open Data Contract Standard (ODCS).</li><li>Fields:</li><li>Schema: Map fields to the Iceberg table schemas, specifying types, nullability, and constraints (e.g., not_null, pattern for regex).</li><li>Semantics: Describe each field’s purpose based on AppsFlyer’s field dictionary ().</li><li>Quality Rules: Define rules for completeness (e.g., 95% non-null appsflyer_id), uniqueness, and format validation (e.g., ISO country codes).</li><li>SLA: Specify delivery (e.g., daily Data Locker dumps) and latency (e.g., 24-hour delay).</li><li>Governance: Outline ownership, change processes, and access controls.</li></ul><h3>Validate Data Against the Contract</h3><p>Use Spark to validate data before writing to Iceberg tables:</p><pre>import requests<br>from pyspark.sql import SparkSession<br>from pyspark.sql.types import *<br>from pyspark.sql.functions import to_date, col<br><br>ICEBERG_VERSION = &quot;1.4.0&quot;<br>HADOOP_AWS_VERSION = &quot;3.3.4&quot;<br>PACKAGES = f&quot;org.apache.iceberg:iceberg-spark-runtime-3.4_2.12:{ICEBERG_VERSION},org.apache.hadoop:hadoop-aws:{HADOOP_AWS_VERSION}&quot;<br><br># Initialize Spark<br>spark = SparkSession.builder \<br>    .appName(&quot;AppsFlyerPullAPI_id123456789&quot;) \<br>    .config(&quot;spark.jars.packages&quot;, PACKAGES) \<br>    .config(&quot;spark.sql.extensions&quot;, &quot;org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions&quot;) \<br>    .config(&quot;spark.sql.catalog.local&quot;, &quot;org.apache.iceberg.spark.SparkCatalog&quot;) \<br>    .config(&quot;spark.sql.catalog.local.type&quot;, &quot;hadoop&quot;) \<br>    .config(&quot;spark.sql.catalog.local.warehouse&quot;, &quot;s3://warehouse&quot;) \<br>    .config(&quot;spark.hadoop.fs.s3a.endpoint&quot;, &quot;http://minio:9000&quot;) \<br>    .config(&quot;spark.hadoop.fs.s3a.access.key&quot;, &quot;admin&quot;) \<br>    .config(&quot;spark.hadoop.fs.s3a.secret.key&quot;, &quot;password&quot;) \<br>    .config(&quot;spark.hadoop.fs.s3a.path.style.access&quot;, &quot;true&quot;) \<br>    .getOrCreate()<br><br># Schema for installs<br>schema = StructType([<br>    StructField(&quot;app_id&quot;, StringType(), False),<br>    StructField(&quot;appsflyer_id&quot;, StringType(), False),<br>    StructField(&quot;install_time&quot;, TimestampType(), False),<br>    StructField(&quot;attributed_touch_time&quot;, TimestampType(), True),<br>    StructField(&quot;media_source&quot;, StringType(), False),<br>    StructField(&quot;campaign&quot;, StringType(), True),<br>    StructField(&quot;campaign_id&quot;, StringType(), True),<br>    StructField(&quot;adset&quot;, StringType(), True),<br>    StructField(&quot;adset_id&quot;, StringType(), True),<br>    StructField(&quot;ad&quot;, StringType(), True),<br>    StructField(&quot;ad_id&quot;, StringType(), True),<br>    StructField(&quot;platform&quot;, StringType(), False),<br>    StructField(&quot;country_code&quot;, StringType(), True),<br>    StructField(&quot;city&quot;, StringType(), True),<br>    StructField(&quot;cost_value&quot;, DoubleType(), True),<br>    StructField(&quot;cost_currency&quot;, StringType(), True),<br>    StructField(&quot;attribution_type&quot;, StringType(), True),<br>    StructField(&quot;placement&quot;, StringType(), True),<br>    StructField(&quot;device_id&quot;, StringType(), True),<br>    StructField(&quot;os_version&quot;, StringType(), True),<br>    StructField(&quot;app_version&quot;, StringType(), True),<br>    StructField(&quot;sdk_version&quot;, StringType(), True)<br>])<br><br># Fetch data for app_id=id123456789<br>app_id = &quot;id123456789&quot;<br>api_token = &quot;your_api_token&quot;<br>endpoint = &quot;https://hq1.appsflyer.com/api/raw-data/export/app/installs_report/v5&quot;<br>from_date = &quot;2025-05-23&quot;<br>to_date = &quot;2025-05-24&quot;<br><br>params = {<br>    &quot;api_token&quot;: api_token,<br>    &quot;from&quot;: from_date,<br>    &quot;to&quot;: to_date,<br>    &quot;timezone&quot;: &quot;UTC&quot;,<br>    &quot;additional_fields&quot;: &quot;placement,device_id,os_version,app_version,sdk_version&quot;<br>}<br>response = requests.get(f&quot;{endpoint}?app_id={app_id}&quot;, params=params)<br>data = []<br>if response.status_code == 200:<br>    lines = response.text.splitlines()<br>    for line in lines[1:]:  # Skip header<br>        fields = line.split(&quot;,&quot;)<br>        data.append([app_id] + fields)<br>else:<br>    print(f&quot;Failed to fetch data for {app_id}: {response.status_code}&quot;)<br><br># Create DataFrame<br>df = spark.createDataFrame(data, schema)<br>df = df.withColumn(&quot;install_date&quot;, to_date(&quot;install_time&quot;))<br><br># Validate against contract<br>valid_df = df.filter(<br>    (col(&quot;app_id&quot;) == &quot;id123456789&quot;) &amp;<br>    col(&quot;appsflyer_id&quot;).isNotNull() &amp;<br>    col(&quot;install_time&quot;).isNotNull() &amp;<br>    col(&quot;media_source&quot;).isNotNull() &amp;<br>    col(&quot;platform&quot;).isin(&quot;iOS&quot;, &quot;Android&quot;) &amp;<br>    (col(&quot;country_code&quot;).isNull() | col(&quot;country_code&quot;).regexp(&quot;^[A-Z]{2}$&quot;)) &amp;<br>    (col(&quot;cost_value&quot;).isNull() | col(&quot;cost_value&quot;) &gt;= 0.0)<br>)<br><br># Write to Iceberg<br>valid_df.writeTo(&quot;local.appsflyer_db.installs&quot;).append()<br><br># Log invalid records<br>invalid_df = df.join(valid_df, [&quot;app_id&quot;, &quot;appsflyer_id&quot;, &quot;install_time&quot;], &quot;leftanti&quot;)<br>invalid_df.write.mode(&quot;append&quot;).parquet(f&quot;s3://warehouse/invalid_data/{app_id}/installs/&quot;)</pre><h3>Enforce the Contract</h3><ul><li>Pipeline Integration: Embed contract validation in your ETL pipeline using Spark, dbt, or Airflow.</li><li>Monitoring: Use tools like Great Expectations or Apache Griffin to monitor data quality against the contract’s rules.</li><li>Alerts: Set up alerts (e.g., via Slack or email) if quality thresholds (e.g., 95% completeness) are violated.</li></ul><h3>Manage Schema Evolution</h3><p>Iceberg Schema Evolution: Add new fields to Iceberg tables as AppsFlyer introduces them:</p><pre>ALTER TABLE local.appsflyer_db.installs ADD COLUMN new_metric STRING</pre><p>Contract Updates: Update the contract version (e.g., 1.0.0 to 1.1.0) and notify consumers via the governance process.</p><h3>Store and Share the Contract</h3><ul><li>Storage: Store the contract in a Git repository or a contract management platform.</li><li>Access: Share the contract with consumers (e.g., Analytics Team) via documentation portals or data catalogs like Apache Polaris.</li><li>MinIO Security: Ensure MinIO buckets (s3://warehouse/appsflyer_db/*) are secured with RBAC, limiting write access to the Data Engineering team.</li></ul><h3>One Data Contract vs. One per Table</h3><h3>One Data Contract per Table</h3><ul><li>Description: Create a separate data contract for each Iceberg table (e.g., installs, in_app_events, etc.) for each product’s app_id.</li><li>Pros:</li><li>Granularity: Each table’s contract precisely defines its schema, quality rules, and partitioning (e.g., install_date, platform), tailored to the specific report type and organic/non-organic context.</li><li>Product-Specificity: Contracts can reflect differences in data characteristics per app_id (e.g., different event_name values or custom event_value fields for each product).</li><li>Query Optimization: Aligns with Iceberg’s table structure, making it easier to validate data for specific tables during ingestion.</li><li>Governance: Easier to manage schema evolution and quality rules per table, especially if products have unique requirements.</li><li>Cons:</li><li>Proliferation: With multiple products and report types, you could end up with many contracts (e.g., 5 report types × 2 for organic/non-organic × N products = 10N contracts), increasing maintenance overhead.</li><li>Redundancy: Common fields (e.g., appsflyer_id, platform) and quality rules are repeated across contracts, leading to duplication.</li><li>When to Use: Best if products have significantly different data structures, event types, or quality requirements, or if you need fine-grained control over each table’s data.</li></ul><h3>One Data Contract for AppsFlyer Producer</h3><ul><li>Description: Create a single data contract for the AppsFlyer producer, encompassing all report types (installs, in-app events, etc.) and organic/non-organic data across all products, with app_id as a distinguishing field or table partition.</li><li>Pros:</li><li>Simplicity: A single contract reduces maintenance overhead, especially with many products.</li><li>Consistency: Standardizes schema, quality rules, and SLAs across all products and report types, simplifying governance.</li><li>Scalability: Easier to onboard new products by adding their app_id to the contract without creating new documents.</li><li>Unified Validation: A single contract can define shared validation logic (e.g., for appsflyer_id, country_code) applied across all tables.</li><li>Cons:</li><li>Complexity: The contract must account for schema variations across report types and organic/non-organic data, potentially becoming unwieldy.</li><li>Less Specificity: May not capture product-specific nuances (e.g., unique event_value fields for a specific app).</li><li>Ingestion Challenges: Requires logic to route data to the correct Iceberg table based on app_id and report type.</li><li>When to Use: Best if products have similar data structures and quality needs, and you want to streamline contract management.</li></ul><h3>One Data Contract per app_id</h3><ul><li>Description: Each product’s app_id (e.g., id123456789, id987654321) gets a single data contract that defines the schemas, quality rules, SLAs, and governance for all Iceberg tables associated with that product (e.g., installs, in_app_events). Each contract encapsulates the data from all AppsFlyer Pull API endpoints for that app_id.</li><li>Pros:</li><li>Product-Specific Customization: Each contract can be tailored to a product’s unique data characteristics, such as specific event_name values, custom event_value fields, or product-specific quality rules (e.g., expected revenue ranges for a premium app vs. a free app).</li><li>Isolation: Isolates data governance per product, making it easier to manage product-specific requirements, ownership, or access controls (e.g., different teams for each app).</li><li>Simplified Analysis: Consumers can focus on a single contract per product, reducing complexity when analyzing data for a specific app.</li><li>Schema Flexibility: Allows for product-specific schema variations (e.g., additional fields unique to one app’s events) without affecting other products.</li><li>Cons:</li><li>Contract Proliferation: With N products, you’ll have N contracts, each covering multiple tables (e.g., 4–8 tables per app_id for installs, in-app events, etc.). This increases maintenance overhead, especially if schemas are similar across products.</li><li>Redundancy: Common fields (e.g., appsflyer_id, platform, country_code) and quality rules (e.g., non-null checks, ISO country code validation) are duplicated across contracts, requiring updates to multiple documents for shared changes.</li><li>Management Complexity: Adding a new report type (e.g., reinstalls) requires updating all N contracts, which can be cumbersome.</li><li>Ingestion Overhead: Each app_id’s data must be validated against its specific contract, potentially requiring separate ingestion pipelines per product.</li><li>When to Use: Scenarios where products have significantly different data characteristics (e.g., unique event schemas) or governance requirements (e.g., different teams managing each app).</li></ul><h3>Best Practices for AppsFlyer Data Contract</h3><ul><li>Granularity: Tailor each app_id contract for product-specific event_name values or quality rules.</li><li>Validation: Use Spark or dbt to enforce constraints (e.g., regex for country_code, uniqueness for appsflyer_id). Use a shared validation library to apply common rules (e.g., country_code regex) across contracts.</li><li>Versioning: Increment contract versions for schema changes and maintain backward compatibility using Iceberg’s schema evolution.</li><li>Documentation: Reference AppsFlyer’s field dictionary for field descriptions and constraints.</li><li>Governance: Establish a clear process for schema changes, involving stakeholders like the Analytics Team.</li><li>Security: Restrict MinIO access per app_id using RBAC.</li><li>Monitoring: Regularly audit data quality and SLA compliance using tools like Great Expectations or custom Spark jobs.</li></ul><h3>Conclusion</h3><p>A data contract for AppsFlyer raw data ensures alignment between producers (AppsFlyer APIs) and consumers (analytics pipelines) by defining schemas, quality rules, and governance for Iceberg tables in MinIO. The sample contracts for id123456789 cover key aspects like schema, partitioning, quality, and SLAs, demonstrates how to define product-specific requirements while referencing shared tables. To implement, validate data in your ingestion pipeline, enforce governance, and monitor quality.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=69e0132aa766" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Lakehouse with Iceberg and MinIO]]></title>
            <link>https://huynguyen8505.medium.com/data-lakehouse-with-iceberg-and-minio-ca6b8d22b799?source=rss-65068ab1dd48------2</link>
            <guid isPermaLink="false">https://medium.com/p/ca6b8d22b799</guid>
            <category><![CDATA[iceberg-table]]></category>
            <category><![CDATA[data-lakehouse]]></category>
            <dc:creator><![CDATA[Lionel Nguyen]]></dc:creator>
            <pubDate>Sat, 24 May 2025 10:11:03 GMT</pubDate>
            <atom:updated>2025-05-24T10:11:03.343Z</atom:updated>
            <content:encoded><![CDATA[<p>Apache Iceberg is an open-source table format designed for large-scale data analytics, offering features like schema evolution, ACID transactions, and time travel. When paired with MinIO, a high-performance object storage system, Iceberg leverages MinIO’s S3-compatible storage for both data and metadata, making it an excellent choice for building data lakehouse.</p><h3>Understanding Datasets in Apache Iceberg</h3><p>A dataset in Apache Iceberg refers to the collection of data stored in a table, typically in columnar formats like Parquet or ORC, managed by Iceberg’s metadata layer. Unlike traditional file-based storage, Iceberg organizes datasets as tables, providing a structured, queryable interface with features like ACID compliance and schema evolution.</p><ul><li>Data Files: The actual data is stored in files (e.g., 2_12_0.parquet) in MinIO buckets. These files contain the records users query and are written by processing engines like Apache Spark, Flink, or Dremio.</li><li>Metadata: Iceberg maintains metadata files (e.g., v1.metadata.json, manifest lists, and manifest files) in MinIO to track table schema, partitions, snapshots, and file locations. This metadata enables efficient querying, time travel, and schema evolution without scanning all data files.</li><li>Snapshots: Each table state is captured as a snapshot, allowing you to query historical data (time travel) or roll back to a previous state. Snapshots are stored in MinIO and referenced in metadata files.</li></ul><p>In MinIO, the dataset is stored in a bucket (e.g., s3://warehouse/db/table), with data files in a data folder and metadata in a metadatafolder. For example, a table named installs in the appsflyer_db database might have a structure like:</p><pre>s3://warehouse/<br>├── appsflyer_db/<br>│   ├── installs/<br>│   │   ├── data/<br>│   │   │   ├── app_id=id123456789/<br>│   │   │   │   └── install_date=2025-05-24/<br>│   │   │   │       └── 00001.parquet<br>│   │   │   └── app_id=id987654321/<br>│   │   └── metadata/<br>│   │       ├── v1.metadata.json<br>│   │       ├── snap-123456.avro<br>│   │       └── version-hint.text<br>│   ├── in_app_events/<br>│   │   ├── data/<br>│   │   │   └── app_id=id123456789/<br>│   │   │       └── event_date=2023-08-01/<br>│   │   │           └── 00002.parquet<br>│   │   └── metadata/<br>│   ├── uninstalls/<br>│   │   ├── data/<br>│   │   │   └── app_id=id123456789/<br>│   │   │       └── uninstall_date=2023-08-01/<br>│   │   └── metadata/<br>│   ├── reinstalls/<br>│   │   ├── data/<br>│   │   │   └── app_id=id123456789/<br>│   │   │       └── reinstall_date=2023-08-01/<br>│   │   └── metadata/<br>│   ├── reengagements/<br>│   │   ├── data/<br>│   │   │   └── app_id=id123456789/<br>│   │   │       └── event_date=2023-08-01/<br>│   │   └── metadata/</pre><h3>Understanding Schema in Apache Iceberg</h3><p>The schema in Apache Iceberg defines the structure of a table, including column names, data types, and whether fields are required or optional. Iceberg’s schema is stored in the metadata file (v1.metadata.json) and supports full schema evolution, meaning you can add, rename, or modify columns without rewriting data files.</p><h3>Schema Definition</h3><p>A schema is a JSON-like structure specifying fields and their types. For example:</p><pre>{<br>  &quot;type&quot;: &quot;struct&quot;,<br>  &quot;schema-id&quot;: 0,<br>  &quot;fields&quot;: [<br>    {&quot;id&quot;: 1, &quot;name&quot;: &quot;appsflyer_id&quot;, &quot;required&quot;: false, &quot;type&quot;: &quot;string&quot;},<br>    {&quot;id&quot;: 2, &quot;name&quot;: &quot;platform&quot;, &quot;required&quot;: false, &quot;type&quot;: &quot;string&quot;}<br>  ]<br>}</pre><p>This schema defines a table with two columns: appsflyer_id (string) and platform (string), both optional.</p><h3>Schema Evolution</h3><p>Iceberg allows schema changes (e.g., adding a column) without affecting existing data. For example, adding a column fare_amountupdates the metadata, and new records include the new column with default values for older data.</p><h3>Storage in MinIO</h3><p>The schema is stored in the metadata folder of the table’s directory in MinIO, ensuring it’s accessible to all compatible engines (Spark, Dremio, etc.).</p><h3>Setting Up MinIO for Apache Iceberg</h3><p>MinIO acts as the S3-compatible storage layer for both data and metadata. To use MinIO with Iceberg, you need to configure a MinIO bucket and set up a catalog to track tables.</p><h3>MinIO Setup</h3><ul><li>Deploy MinIO using Docker or a standalone installation. For example, a Docker Compose setup might include:</li></ul><pre>services:<br>  minio:<br>    image: minio/minio<br>    environment:<br>      - MINIO_ROOT_USER=admin<br>      - MINIO_ROOT_PASSWORD=password<br>    ports:<br>      - &quot;9000:9000&quot;<br>      - &quot;9001:9001&quot;<br>    command: [&quot;server&quot;, &quot;/data&quot;, &quot;--console-address&quot;, &quot;:9001&quot;]</pre><ul><li>This sets up MinIO with an admin user and password.</li><li>Create a bucket (e.g., warehouse) for Iceberg tables:</li></ul><pre>mc mb minio/warehouse<br>mc policy set public minio/warehouse</pre><ul><li>This ensures the bucket is accessible for Iceberg operations.</li></ul><h3>Catalog Configuration</h3><p>Iceberg uses a catalog to manage table metadata. Common catalogs include:</p><ul><li>Hadoop Catalog: Stores metadata in MinIO alongside data.</li><li>JDBC Catalog: Uses a database (e.g., PostgreSQL) for metadata, with data in MinIO.</li><li>REST Catalog: Uses MinIO for metadata storage, offering a modern approach.</li></ul><p>For simplicity, use the Hadoop Catalog with MinIO:</p><pre>spark.sql.catalog.local = &quot;org.apache.iceberg.spark.SparkCatalog&quot;<br>spark.sql.catalog.local.type = &quot;hadoop&quot;<br>spark.sql.catalog.local.warehouse = &quot;s3://warehouse&quot;<br>spark.hadoop.fs.s3a.endpoint = &quot;http://minio:9000&quot;<br>spark.hadoop.fs.s3a.access.key = &quot;admin&quot;<br>spark.hadoop.fs.s3a.secret.key = &quot;password&quot;<br>spark.hadoop.fs.s3a.path.style.access = &quot;true&quot;</pre><h3>Defining a Database with Multiple Tables</h3><p>In Apache Iceberg, a database is a logical namespace (e.g., nyc or noaa) that groups multiple tables. Each table is stored in a separate directory within the MinIO bucket, with its own data and metadata. Below is a step-by-step guide to define a database with multiple tables using Spark and MinIO.</p><h3>Step 1: Create a Database</h3><p>Create a database named appsflyer_db in the catalog:</p><pre>CREATE DATABASE IF NOT EXISTS local.appsflyer_db</pre><p>This creates a namespace appsflyer_db in the catalog, with metadata stored in MinIO at s3://warehouse/appsflyer_db.</p><h3>Step 2: Define Multiple Tables</h3><p>Create multiple tables within the appsflyer_db database, each with its own schema and optional partitioning. For example, create tables installs and in_app_events.</p><h4>Table 1: installs</h4><pre>CREATE TABLE local.appsflyer_db.installs (<br>    app_id STRING COMMENT &#39;Unique AppsFlyer app identifier (e.g., id123456789)&#39;,<br>    appsflyer_id STRING COMMENT &#39;Unique device identifier&#39;,<br>    install_time TIMESTAMP COMMENT &#39;Time of app install&#39;,<br>    attributed_touch_time TIMESTAMP COMMENT &#39;Time of ad interaction&#39;,<br>    media_source STRING COMMENT &#39;Source of the install (e.g., googleadwords_int)&#39;,<br>    campaign STRING COMMENT &#39;Campaign name&#39;,<br>    campaign_id STRING COMMENT &#39;Campaign ID&#39;,<br>    adset STRING COMMENT &#39;Adset name&#39;,<br>    adset_id STRING COMMENT &#39;Adset ID&#39;,<br>    ad STRING COMMENT &#39;Ad name&#39;,<br>    ad_id STRING COMMENT &#39;Ad ID&#39;,<br>    platform STRING COMMENT &#39;Platform (e.g., iOS, Android)&#39;,<br>    country_code STRING COMMENT &#39;ISO country code&#39;,<br>    city STRING COMMENT &#39;City of the user&#39;,<br>    cost_value DOUBLE COMMENT &#39;Cost of the install&#39;,<br>    cost_currency STRING COMMENT &#39;Currency of the cost&#39;,<br>    attribution_type STRING COMMENT &#39;Type of attribution (e.g., click, impression)&#39;,<br>    placement STRING COMMENT &#39;Placement name (Data Locker/Pull API only)&#39;,<br>    device_id STRING COMMENT &#39;Device ID (e.g., IDFA, GAID)&#39;,<br>    os_version STRING COMMENT &#39;OS version&#39;,<br>    app_version STRING COMMENT &#39;App version&#39;,<br>    sdk_version STRING COMMENT &#39;AppsFlyer SDK version&#39;,<br>    install_date DATE COMMENT &#39;Derived date of install_time&#39;<br>)<br>PARTITIONED BY (app_id, install_date)<br>LOCATION &#39;s3://warehouse/appsflyer_db/installs&#39;<br>TBLPROPERTIES (<br>    &#39;format-version&#39;=&#39;2&#39;,<br>    &#39;write.format.default&#39;=&#39;parquet&#39;,<br>    &#39;write.metadata.delete-after-commit.enabled&#39;=&#39;true&#39;<br>)</pre><ul><li>Schema: Defines columns for installs.</li><li>Partitioning: Partitions by day based on the install_time column for efficient querying.</li><li>Location: Stores data and metadata in s3://warehouse/appsflyer_db/installs.</li></ul><h4>Table 2: in_app_events</h4><pre>CREATE TABLE local.appsflyer_db.in_app_events (<br>    app_id STRING COMMENT &#39;Unique AppsFlyer app identifier (e.g., id123456789)&#39;,<br>    appsflyer_id STRING COMMENT &#39;Unique device identifier&#39;,<br>    event_name STRING COMMENT &#39;Name of the event (e.g., af_purchase)&#39;,<br>    event_time TIMESTAMP COMMENT &#39;Time of the event&#39;,<br>    event_revenue DOUBLE COMMENT &#39;Revenue associated with the event&#39;,<br>    event_revenue_currency STRING COMMENT &#39;Currency of the revenue&#39;,<br>    event_value MAP&lt;STRING, STRING&gt; COMMENT &#39;JSON key-value pairs for event parameters&#39;,<br>    install_time TIMESTAMP COMMENT &#39;Time of app install&#39;,<br>    media_source STRING COMMENT &#39;Source of the install or event&#39;,<br>    campaign STRING COMMENT &#39;Campaign name&#39;,<br>    campaign_id STRING COMMENT &#39;Campaign ID&#39;,<br>    platform STRING COMMENT &#39;Platform (e.g., iOS, Android)&#39;,<br>    country_code STRING COMMENT &#39;ISO country code&#39;,<br>    user_id STRING COMMENT &#39;Customer user ID&#39;,<br>    device_id STRING COMMENT &#39;Device ID (e.g., IDFA, GAID)&#39;,<br>    is_retargeting BOOLEAN COMMENT &#39;True if event is from a retargeting campaign&#39;,<br>    event_date DATE COMMENT &#39;Derived date of event_time&#39;<br>)<br>PARTITIONED BY (app_id, event_date)<br>LOCATION &#39;s3://warehouse/appsflyer_db/in_app_events&#39;<br>TBLPROPERTIES (<br>    &#39;format-version&#39;=&#39;2&#39;,<br>    &#39;write.format.default&#39;=&#39;parquet&#39;,<br>    &#39;write.metadata.delete-after-commit.enabled&#39;=&#39;true&#39;<br>)</pre><ul><li>Schema: Defines columns for in-app events details.</li><li>Partitioning: Partitions by day based on the event_time column for efficient querying.</li><li>Location: Stores data in s3://warehouse/appsflyer_db/in_app_events.</li></ul><h3>Step 3: Ingest Data into Tables</h3><p>Load data into the tables using Spark. For example:</p><pre>import requests<br>from pyspark.sql import SparkSession<br>from pyspark.sql.types import *<br>from pyspark.sql.functions import to_date, col<br><br>ICEBERG_VERSION = &quot;1.4.0&quot;<br>HADOOP_AWS_VERSION = &quot;3.3.4&quot;<br>PACKAGES = f&quot;org.apache.iceberg:iceberg-spark-runtime-3.4_2.12:{ICEBERG_VERSION},org.apache.hadoop:hadoop-aws:{HADOOP_AWS_VERSION}&quot;<br><br># Initialize Spark<br>spark = SparkSession.builder \<br>    .appName(&quot;AppsFlyerPullAPI_id123456789&quot;) \<br>    .config(&quot;spark.jars.packages&quot;, PACKAGES) \<br>    .config(&quot;spark.sql.extensions&quot;, &quot;org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions&quot;) \<br>    .config(&quot;spark.sql.catalog.local&quot;, &quot;org.apache.iceberg.spark.SparkCatalog&quot;) \<br>    .config(&quot;spark.sql.catalog.local.type&quot;, &quot;hadoop&quot;) \<br>    .config(&quot;spark.sql.catalog.local.warehouse&quot;, &quot;s3://warehouse&quot;) \<br>    .config(&quot;spark.hadoop.fs.s3a.endpoint&quot;, &quot;http://minio:9000&quot;) \<br>    .config(&quot;spark.hadoop.fs.s3a.access.key&quot;, &quot;admin&quot;) \<br>    .config(&quot;spark.hadoop.fs.s3a.secret.key&quot;, &quot;password&quot;) \<br>    .config(&quot;spark.hadoop.fs.s3a.path.style.access&quot;, &quot;true&quot;) \<br>    .getOrCreate()<br><br># Schema for installs<br>schema = StructType([<br>    StructField(&quot;app_id&quot;, StringType(), False),<br>    StructField(&quot;appsflyer_id&quot;, StringType(), False),<br>    StructField(&quot;install_time&quot;, TimestampType(), False),<br>    StructField(&quot;attributed_touch_time&quot;, TimestampType(), True),<br>    StructField(&quot;media_source&quot;, StringType(), False),<br>    StructField(&quot;campaign&quot;, StringType(), True),<br>    StructField(&quot;campaign_id&quot;, StringType(), True),<br>    StructField(&quot;adset&quot;, StringType(), True),<br>    StructField(&quot;adset_id&quot;, StringType(), True),<br>    StructField(&quot;ad&quot;, StringType(), True),<br>    StructField(&quot;ad_id&quot;, StringType(), True),<br>    StructField(&quot;platform&quot;, StringType(), False),<br>    StructField(&quot;country_code&quot;, StringType(), True),<br>    StructField(&quot;city&quot;, StringType(), True),<br>    StructField(&quot;cost_value&quot;, DoubleType(), True),<br>    StructField(&quot;cost_currency&quot;, StringType(), True),<br>    StructField(&quot;attribution_type&quot;, StringType(), True),<br>    StructField(&quot;placement&quot;, StringType(), True),<br>    StructField(&quot;device_id&quot;, StringType(), True),<br>    StructField(&quot;os_version&quot;, StringType(), True),<br>    StructField(&quot;app_version&quot;, StringType(), True),<br>    StructField(&quot;sdk_version&quot;, StringType(), True)<br>])<br><br># Fetch data for app_id=id123456789<br>app_id = &quot;id123456789&quot;<br>api_token = &quot;your_api_token&quot;<br>endpoint = &quot;https://hq1.appsflyer.com/api/raw-data/export/app/installs_report/v5&quot;<br>from_date = &quot;2025-05-23&quot;<br>to_date = &quot;2025-05-24&quot;<br>params = {<br>    &quot;api_token&quot;: api_token,<br>    &quot;from&quot;: from_date,<br>    &quot;to&quot;: to_date,<br>    &quot;timezone&quot;: &quot;UTC&quot;,<br>    &quot;additional_fields&quot;: &quot;placement,device_id,os_version,app_version,sdk_version&quot;<br>}<br>response = requests.get(f&quot;{endpoint}?app_id={app_id}&quot;, params=params)<br>data = []<br>if response.status_code == 200:<br>    lines = response.text.splitlines()<br>    for line in lines[1:]:  # Skip header<br>        fields = line.split(&quot;,&quot;)<br>        data.append([app_id] + fields)<br>else:<br>    print(f&quot;Failed to fetch data for {app_id}: {response.status_code}&quot;)<br><br># Create DataFrame<br>df = spark.createDataFrame(data, schema)<br>df = df.withColumn(&quot;install_date&quot;, to_date(&quot;install_time&quot;))<br><br># Write to Iceberg table<br>df.writeTo(&quot;local.appsflyer_db.installs&quot;).append()</pre><p>Data files (e.g., Parquet) are written to the respective table directories in MinIO, and metadata files are updated to track the new snapshots.</p><ul><li>Data Locker: Configure AppsFlyer to deliver raw data reports to a MinIO bucket (e.g., s3://warehouse/raw_appsflyer_data/installs).</li><li>Pull API: Use the Pull API to fetch data programmatically and stage it in MinIO before ingestion.</li><li>Event Value Parsing: For in_app_events, parse event_value JSON strings into a MAP using Spark’s from_json function.</li><li>Incremental Updates: Use MERGE INTO to handle updates or late-arriving data:</li></ul><pre>MERGE INTO local.appsflyer_db.installs t<br>USING (SELECT * FROM staging.id123456789.installs) s<br>ON t.appsflyer_id = s.appsflyer_id AND t.install_time = s.install_time<br>WHEN MATCHED THEN UPDATE SET *<br>WHEN NOT MATCHED THEN INSERT *</pre><h3>Step 4: Query the Tables</h3><p>With separate tables, you can run targeted queries for each report type, leveraging Iceberg’s partitioning.</p><p>Installs by Media Source</p><pre>SELECT media_source, COUNT(*) as install_count<br>FROM local.appsflyer_db.installs<br>WHERE app_id = &#39;id123456789&#39; AND install_date &gt;= &#39;2025-05-01&#39;<br>GROUP BY media_source</pre><p>Revenue from In-App Events</p><pre>SELECT event_name, SUM(event_revenue) as total_revenue<br>FROM local.appsflyer_db.in_app_events<br>WHERE app_id = &#39;id123456789&#39; AND event_date &gt;= &#39;2025-05-01&#39;<br>GROUP BY event_name</pre><p>Uninstall Rate by Campaign</p><pre>SELECT i.campaign, COUNT(DISTINCT u.appsflyer_id) as uninstall_count, COUNT(DISTINCT i.appsflyer_id) as install_count,<br>       COUNT(DISTINCT u.appsflyer_id) / COUNT(DISTINCT i.appsflyer_id) as uninstall_rate<br>FROM local.appsflyer_db.installs i<br>LEFT JOIN local.appsflyer_db.uninstalls u ON i.appsflyer_id = u.appsflyer_id<br>GROUP BY i.campaign</pre><p>Re-engagement Effectiveness</p><pre>SELECT media_source, COUNT(*) as reengagement_count<br>FROM local.appsflyer_db.reengagements<br>WHERE event_date &gt;= &#39;2023-08-01&#39; AND is_retargeting = true<br>GROUP BY media_source</pre><p>Iceberg’s metadata and partitioning optimize these queries by scanning only relevant data files in MinIO.</p><h3>Step 5: Manage Schema Evolution</h3><p>Add a new column to the installs table:</p><pre>ALTER TABLE local.appsflyer_db.installs ADD COLUMN new_metric STRING</pre><p>This updates the metadata in s3://warehouse/appsflyer_db/installs/metadata, and existing records have NULL for new_metric until new data is added.</p><h3>Best Practices for Multiple Tables</h3><ul><li>Partitioning: Use partitioning (e.g., by date or tenant ID) for frequently queried columns to improve performance.</li><li>Catalog Choice: Use a REST Catalog with MinIO for metadata to avoid external dependencies like PostgreSQL, simplifying deployment.</li><li>Bucket Organization: Organize tables in MinIO under a single bucket with a clear directory structure (e.g., s3://warehouse/db_name/table_name).</li><li>Security: Secure MinIO with TLS and policy-based access controls to restrict table access.</li><li>Maintenance: Regularly compact data files and expire old snapshots to optimize storage and performance:</li></ul><pre>CALL local.system.expire_snapshots(&#39;appsflyer_db.installs&#39;, &#39;2023-01-01&#39;)</pre><h3>Conclusion</h3><p>Apache Iceberg with MinIO provides a powerful, scalable solution for managing datasets in a data lakehouse. Datasets are stored as data files in MinIO, managed by Iceberg’s metadata for efficient querying and versioning. Schemas define table structures and support evolution, stored in MinIO’s metadata folder. Defining a database with multiple tables involves creating a namespace, defining table schemas with optional partitioning, and ingesting data using tools like Spark. By following best practices, you can build a robust, high-performance data architecture suitable for large-scale analytics.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ca6b8d22b799" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>