<?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 Amit Prabhu on Medium]]></title>
        <description><![CDATA[Stories by Amit Prabhu on Medium]]></description>
        <link>https://medium.com/@amit.sprabhu?source=rss-eedd7a8b6a77------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*klfclp9bRJvxxG6qO61iZA.jpeg</url>
            <title>Stories by Amit Prabhu on Medium</title>
            <link>https://medium.com/@amit.sprabhu?source=rss-eedd7a8b6a77------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 22:30:31 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@amit.sprabhu/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[How We Refresh Razorpay's Data Warehouse 10x Faster with Graphs and Indexes]]></title>
            <link>https://engineering.razorpay.com/how-we-refresh-razorpays-data-warehouse-10x-faster-with-graphs-and-indexes-538abc244703?source=rss-eedd7a8b6a77------2</link>
            <guid isPermaLink="false">https://medium.com/p/538abc244703</guid>
            <dc:creator><![CDATA[Amit Prabhu]]></dc:creator>
            <pubDate>Tue, 14 Jul 2026 14:06:15 GMT</pubDate>
            <atom:updated>2026-07-14T14:06:15.539Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*malmruEkb8MF1xaxpfBmOw.png" /></figure><p>Contributors: <a href="https://medium.com/u/2bf56caf1392">Utkarsh Koppikar</a> <a href="https://medium.com/u/48e3269124e2">Rohan</a></p><h3>Background</h3><p><strong>Razorpay</strong> provides the payment infrastructure for millions of merchants globally. Behind every payment, settlement, and refund is a microservices architecture where each service owns its own database. While this keeps services independent and scalable, it creates a challenge for stakeholders who need to see across those boundaries.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zBXNSlVZDRtcF2LZZGFD3g.png" /></figure><p>The <strong>Data Platform</strong> team manages the infrastructure that bridges this gap. Transactional data flows into the lake via CDC pipelines, ingested onto S3 in Delta Lake, Apache Iceberg, or plain Parquet formats. On top of the lake, we build domain-specific warehouse tables — wide, pre-joined tables that co-locate all the data a consumer needs, queryable via Trino. These power two use cases: <strong>Analytics</strong> (internal dashboards on Tableau and Superset) and <strong>Reporting</strong> (merchants and regulated entities who download structured data exports; Razorpay generates nearly a million such reports per month).</p><p>The warehouse tables that power both use cases are called <strong>Facts</strong>. A <strong>Fact</strong> is a flat denormalised table on S3, produced by joining 10 to 30 microservice tables and materialising the result once. A settlement Fact, for example, merges payments, refunds, adjustments, and card details into a single wide row so that a dashboard or report reads from a single table instead of joining across services in real time. It is closer to a domain-specific materialised view than a classical data warehouse fact table. We maintain over 50 such Facts, and approximately 40% of all merchant reports are served directly from them.</p><p>As data volumes and the number of entities per fact grew, the batch generation pipeline began to show its limits, prompting us to rethink the refresh strategy, the data layout, and how to handle high-cardinality dimensions. The rest of this post covers that journey.</p><h3>The Full Refresh Pipeline: Our Baseline and the Pain</h3><p>The original full-refresh pipeline was straightforward.</p><ol><li><strong>Schedule: </strong>Airflow schedules Spark jobs on EMR daily during off-peak hours, passing a Fact Config from S3—a configuration file listing the tables to join, columns to select, join conditions, and the output format.</li><li><strong>Full Refresh</strong>: The job then reads all source tables from the data lake, performs a full Spark join, and overwrites the entire denormalised table back to S3.</li><li><strong>Notify</strong>: Once complete, it publishes the max(primary_table_updated_at) timestamp to the Reporting Service, which uses it to split queries: the Fact covers the historical window, and TiDB — our warm store, a distributed MySQL-compatible database holding recent transactional data; fills the freshness gap for data not yet materialised into the Fact.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zXA7MRtQFngIMeXwe4OQpQ.png" /></figure><p>This approach created three classes of problems:</p><ol><li><strong>Operational</strong>: Pipelines ran on spot nodes for many hours, making spot loss inevitable. Since the business logic required the full dataset at once, there was no safe checkpoint — a single failure meant restarting from scratch. We eventually chained smaller facts together to create checkpoints.</li><li><strong>Scaling</strong>: Our data has back-dated references — a payment today can reference an order from five years ago, a refund can reference a year-old payment. This forces unbounded joins and full table scans on every secondary table with no safe time window to apply. The Payments fact grew to join over 30 tables and ran for 8–10 hours. Facts initially contained data from 2018, and to manage cost, we reduced fact retention to one year as a stopgap, allowing some reports to fall back to warm stores for older data — but this deepened our TiDB dependency and blocked retention initiatives on the warm store.</li><li><strong>Cost</strong>: Pipelines ran for 70+ hours per day across large EMR clusters. We moved most facts to alternate-day schedules, creating up to 48-hour freshness lags.</li></ol><h3>Previous Attempts and Challenges: The Path to Solution</h3><p>We attacked this problem from both short-term and long-term perspectives. Fact decomposition, revised data retention, alternative-source fallbacks, and alternate-day scheduling served as short-term solutions to maintain stability without affecting the user experience.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E7K6zH_NUtfYnjblWLAgiw.png" /></figure><p>To solve these problems in the long term, we built a streaming pipeline that subscribes to Kafka CDC topics, models the Fact config as a dependency graph, and performs TiDB lookups to produce denormalised rows in near real time. (Details: <a href="https://engineering.razorpay.com/real-time-denormalized-data-streaming-platform-part-1-9f3c730dd9c6">Real-time Denormalised Data Streaming Platform</a>)</p><p>The solution worked for a year but eventually failed for three primary reasons:</p><ol><li><strong>Highly mutable data</strong>: 10–20 change streams joined at 10M+ events per 30-min window drove costs unsustainably high.</li><li><strong>All data in warm store</strong>: Back-dated references forced us to keep all historical data in TiDB, blocking any retention initiative.</li><li><strong>Write amplification</strong>: High-cardinality joins amplified upsert volumes. Since joins were slow on the lake, we had no option but to force all join tables into precomputed facts.</li></ol><h3>The Pivot: Incremental Facts</h3><p><em>The core insight: treat Facts as incrementally maintainable graphs. Instead of regenerating the full table, process only the change events for each entity using the dependency graph to know which related rows to fetch, and a secondary index to know where on the lake to find them.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*u3OWt0a3FXfBcAcw5CdZyg.png" /></figure><p>Consider a real-world payment creation flow, you first create an <strong><em>Order</em></strong> for the product you want to buy, and a <strong>Payment </strong>is made on that order using a <strong>Card </strong>by applying a <strong>Discount </strong>allowed by the ongoing <strong>Offer. </strong>This relationship can be visualised as a graph as shown in the image and this became one of the core insights for the approach.</p><p>Before we into the low level details, we need to understand the following constraints:</p><ol><li><strong>Data Partitioning</strong>: Our data lake on S3 contains source tables in open-source Delta format and partitioned on `<strong>created_date</strong>`.</li><li><strong>Data Indexing: </strong>We use Z-ordering on the primary key and merchant_id columns. Since these columns have high cardinality, this setup is inefficient for lookup queries.</li></ol><p>Three design questions had to be resolved:</p><ol><li><strong>How do you fetch all updates for an entity on T-1 day?</strong></li></ol><p>We built a silver-layer pipeline that creates flattened, deduplicated tables partitioned by updated_date, retaining only the latest change per primary key per day.</p><p><strong>2. How do you know which related rows to fetch for a change event?</strong></p><p>We modelled the Fact config as a traversable in-memory dependency graph, with edges derived from join conditions. Consider a fact that joins five tables:</p><pre>SELECT P.*, O.*, C.*, D.*, O.* <br>FROM payments P <br>LEFT JOIN orders O ON O.id = P.order_id    -- edge: Payments → Orders <br>LEFT JOIN cards  C ON C.id = P.card_id     -- edge: Payments → Cards <br>LEFT JOIN discounts D on D.payment_id = P.id -- edge: Payments → Discounts <br>LEFT JOIN offers O on O.id = D.offer_id  -- edge: Discounts → Offers</pre><p>This produces the graph as shown above. Each edge carries the join predicate as metadata.</p><p>The graph tells us exactly what to fetch for any change event:</p><ul><li><strong>Update on Payments (P1)</strong>: follow edges outward — look up Orders where orders.id = P1.order_id, and Cards where cards.id = P1.card_id. Combine to form a complete denormalised row.</li><li><strong>Update on Orders (O1)</strong>: back-traverse to the root — look up Payments where payments.order_id = O1.id to find the primary keys affected. Cards are not in the path from Orders to the root, so it is skipped.</li></ul><p><strong>3. How do you look up data efficiently on the lake?</strong></p><p>We built a Secondary Index per entity , a small table holding join columns and <strong>created_date</strong> partition values. For example, a payment index table would include id, card_id, order_id, and created_date.</p><p>Because this table has a small sub-set of columns that change infrequently,it is significantly smaller and easier to maintain than the original source table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kj5a7cKgbLmkRFfISp69Fw.png" /></figure><p>The index narrows reads to a handful of partitions, eliminating full table scans. An independent batch pipeline upserts the Secondary Index from the silver layer. This is the core component that removed our dependency on TiDB for historical lookups.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*g2rOPyWerAj82iweVBMZvA.png" /></figure><h3>Incremental Processing using Graph Traversals</h3><p>When a warehouse job is triggered, it parses the Fact configuration to create a dependency graph in memory. It processes updates for each node sequentially, starting from the primary node. Processing logic differs between the primary table and secondary tables. We can therefore broadly classify processing into two phases: the <strong>Primary flow</strong> and the <strong>Secondary flow</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_lG8Ru6xnKVpNpIweg6Cfg.png" /></figure><h3>The Primary Flow:</h3><p>In this flow, we process only the primary table in the graph (payments, in the example above).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6lpxfa0fakCO2baQVQdkmw.png" /></figure><p>For example:</p><ol><li><strong>Fetch</strong> primary table updates from the last checkpoint.</li><li><strong>Join</strong> updates with each secondary index to get created_date partition values; use those to read only the relevant partitions of each source table.</li><li><strong>Stitch</strong> all data into denormalised rows and <strong>upsert</strong> into the target fact, scoping the merge to affected partitions only.</li></ol><h3>The Secondary Flow</h3><p>In this flow, we iterate through all secondary tables in the graph using a level-order traversal and process them sequentially.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_r1fCEIfktiOzp4nf8VvHg.png" /></figure><ol><li><strong>Fetch updates</strong> for the secondary table from the last checkpoint. (e.g offers)</li><li><strong>Back-traverse the graph</strong>: Join with the indexes of all ancestor nodes up to the root (e.g., discounts_index, payments_index for Offers) to enrich each update row with the primary key and partition values needed for the target merge. We don’t need a full source table read, since the required ancestor data is already in their indexes.</li><li><strong>Merge into Fact</strong>: Upsert the partially denormalised rows to the target fact, scoped to affected partitions.</li></ol><h3>Handling High Cardinality and Dimension Tables</h3><p>High-cardinality dimension tables are expensive to denormalise due to write amplification: a single update can fan out to thousands of fact-row rewrites.</p><p>For example, consider a &quot;Payment Links&quot; table in which a single link can accept thousands of payments. If Payment Links is a secondary table and a column is updated, thousands of payments in the Fact table must be updated to reflect that single change. <strong>Hence, not every join belongs in a precomputed fact.</strong></p><p>However, joins on our lake are expensive too because our domain doesn’t support time-bounded joins, which requires a full table scan on secondary tables. We wanted to push down a common predicate to reduce scans on the secondary tables.</p><p>For example, if you are joining payment_links and orders:</p><pre>SELECT *<br>FROM payment_linksLEFT <br>JOIN orders on orders.id = payment_links.order_id<br>WHERE payment_links.merchant_id = m1 and payment_link.created_at between t1 and t2</pre><p>If we run this query on Trino, it will push down filters (merchant_id, created_at) for payment_links, but will result in a full table scan for orders.</p><p>Report generation is usually scoped by merchant_id, which is present in most tables. We decided to use it as a common predicate. Since merchant_id has high cardinality, we cannot partition data on it; instead, we use bucketing on merchant_id with a fixed number of buckets, similar to sharding. We chose Iceberg (V2) as the table format for its native bucketing support.</p><p>Adding merchant_id to both sides of the join condition lets Trino push predicate on both the sides, scanning only the matching bucket:</p><pre>LEFT JOIN orders on orders.merchant_id = payment_links.merchant_id <br>AND orders.id = payment_links.order_id<br>WHERE payment_links.merchant_id = m1 and payment_link.created_at between t1 and t2</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/990/1*er2Yn0Rko-e3f19RMpknWw.png" /></figure><p>This unlocked joins in the data lake. We addressed the remaining gaps by building an Iceberg replication pipeline and tuning non-Fact tables with sort orders and `merchant_id` bucketing. We also used hidden month/day partitioning to reduce merge scans. Finally, we added a query builder in Reporting Service to route joins intelligently. Some joins are served from the precomputed Fact table, while others run between Fact and Iceberg tables at query time.</p><h3>Production Rollout</h3><p>Rolling out to production was more challenging than anticipated. We needed to ensure that no inaccurate data reached our merchants.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oZdY1iGO3QkEtbAseD3Vgg.png" /></figure><p>We followed three phases to ensure accuracy before decommissioning the legacy flow:</p><ol><li><strong>Data reconciliation</strong>: Compared incremental and full-refresh facts record-by-record, resolving checkpointing, out-of-order events, and index lag issues until we hit 100% accuracy.</li><li><strong>Shadow testing</strong>: Ran A/B experiments generating shadow reports from incremental facts in parallel with live reports. Automated nightly comparisons flagged mismatches; we iterated until both were identical.</li><li><strong>Gradual live rollout</strong>: Progressively shifted live traffic to incremental facts with a fallback to the legacy flow, addressing edge cases without merchant impact.</li></ol><p>Moving to a three-pipeline architecture from a single full refresh pipeline naturally raises the chance of a hiccup here and there, so we’ve made the system self-healing. Each pipeline now publishes its own checkpoints, meaning downstream pipelines can see exactly where their upstream counterparts are at and intelligently decide how much data to process if they’re lagging. If we do hit a delay, we automatically fall back to sources like TiDB to keep data fresh in reports. We also have Data Quality checks watching the whole flow; when they spot a mismatch, they flag it on our slack channels, narrowing down the debug scope.</p><h3>Impact</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tJDg3XGxQ0WiRySgoujvzg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uKgTH1xs2kGstk45Ew7hGQ.png" /></figure><p>We migrated the five largest facts to the incremental strategy and the impact numbers are shown below:</p><p>The ~90% reduction in compute time translated to proportional cost savings. Restoring full historical coverage in Facts allowed us to reduce TiDB retention significantly. Runtime joins enabled decomposing large monolithic facts into smaller, domain-specific ones — reducing blast radius and maintenance cost.</p><h3>Lessons Learned</h3><p>1. <strong>Streaming isn’t always the answer: </strong>State-heavy joins at high event volumes eventually become cost-prohibitive. Batch + incremental can win.</p><p>2. <strong>The lake should be queryable, not just archival: </strong>Secondary indexes on the lake eliminated warm-store dependency for historical lookups. Iceberg is adding<a href="https://www.linkedin.com/posts/dipankar-mazumdar_parquet-dataengineering-softwareengineering-activity-7451810741856325632-YaIQ/"> native support for secondary indexes</a> in upcoming releases.</p><p>3. <strong>Not every dimension belongs in a materialised fact: </strong>Runtime joins with bucketed Iceberg tables are cheaper than write amplification at scale.</p><p>4. <strong>Facts are graphs, not tables: </strong>Modelling fact configs as traversable dependency graphs is what unlocks incremental maintenance.</p><h3>Future Roadmap</h3><p>We’ve seen incredible operational and cost improvements from migrating our first five Facts, and it’s clear that the Incremental Strategy is the right path forward. We’re excited to transition the rest of our facts onto this new approach. Alongside this, we’re also diving into these key initiatives to push things even further:</p><ol><li><strong>Real-time secondary index</strong>: We are exploring Apache Flink to merge the index pipeline into CDC ingestion. This approach achieves near-real-time freshness and reduces latency for lookups and ad-hoc queries.</li><li><strong>Extend support to more Sinks</strong>: Applying incremental processing to unlock savings and near-real-time freshness for warehouse tables in other destinations like ClickHouse, TiFlash, etc.</li></ol><p><em>Editor: </em><a href="https://medium.com/u/0ff40057ddde"><em>Parth Sawhney</em></a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=538abc244703" width="1" height="1" alt=""><hr><p><a href="https://engineering.razorpay.com/how-we-refresh-razorpays-data-warehouse-10x-faster-with-graphs-and-indexes-538abc244703">How We Refresh Razorpay&#39;s Data Warehouse 10x Faster with Graphs and Indexes</a> was originally published in <a href="https://engineering.razorpay.com/">Razorpay Engineering</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Real-Time Denormalized Data Streaming Platform (Part-2)]]></title>
            <link>https://engineering.razorpay.com/real-time-denormalized-data-streaming-platform-part-2-97dfff40fd8d?source=rss-eedd7a8b6a77------2</link>
            <guid isPermaLink="false">https://medium.com/p/97dfff40fd8d</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[spark-streaming]]></category>
            <category><![CDATA[denormalization]]></category>
            <category><![CDATA[platform]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Amit Prabhu]]></dc:creator>
            <pubDate>Fri, 21 Apr 2023 07:00:42 GMT</pubDate>
            <atom:updated>2023-05-12T04:17:14.813Z</atom:updated>
            <content:encoded><![CDATA[<p>Contributors: <a href="https://medium.com/u/eedd7a8b6a77">Amit Prabhu</a>, <a href="https://medium.com/u/46f375f4a3c5">Tajinder Chawla</a>, <a href="https://medium.com/u/e305e0f7473d">Harjeet Singh</a></p><p><em>In the last blog, we talked about the real-time denormalization problem, its complexity, challenges, and the initial approach that we took to solve the problem. If you haven’t read Part-1, please read it </em><a href="https://engineering.razorpay.com/real-time-denormalized-data-streaming-platform-part-1-9f3c730dd9c6"><em>here</em></a><em>.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YVhAZ2UR2xQ3j2-sKKTE-w.jpeg" /></figure><h4><strong>Quick Recap</strong></h4><p>As we are building more microservices, data is becoming more normalized and distributed. However, data consumers such as <strong>Reporting and Analytics</strong> need a <strong>holistic and denormalized view</strong> of data to effectively serve our Customers and Analysts.</p><p>A common approach is to run <strong>ETL jobs daily</strong> at <strong>off-peak</strong> hours to build a denormalized table joining data from multiple source tables, to avoid joins during query time. As we are dealing with <strong>highly mutable data</strong>, the denormalized table had to be <strong>refreshed completely</strong>, incurring a <strong>huge cost</strong> and failing to keep up with the growing data size.</p><p>An increase in<strong> report generation time</strong> and <strong>data staleness</strong> has been the major pain points faced by our customers, who have built critical real-time use cases around reports such as Stock Allocation, Payouts to Vendors, Distributors, Bank Settlements, etc. So we moved to a <strong>streaming-based</strong> approach to denormalize the data in near real-time.</p><p>As the data in the source table changes, we have to sync these changes to the denormalized table. This syncing process becomes <strong>challenging</strong> as data size, dimensions, cardinality, and mutability increase, while syncing latency and error margin decrease.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tCg5kcZvHYuyPWdAS2vzaA.png" /><figcaption>High-Level Design</figcaption></figure><p>To solve this problem, we built a spark streaming pipeline that would listen to change events on source tables from Kafka, look up the join tables data from an NRT datastore, and upsert the denormalized data to delta lake on S3.</p><blockquote>In this blog, we will deep dive into the solution, covering the different stages involved while processing: <strong>Pre-Processing</strong>, <strong>Processing in a Micro-Batch</strong>, and handling event records for <strong>Primary Table</strong>, <strong>Secondary Dimension</strong>, and <strong>Unjoined Entities</strong>.</blockquote><h4>Pre-Processing</h4><p>The streaming pipeline is started by passing a fact config as an argument. A <strong>fact config</strong> is a JSON file containing all details required for fact generation. A typical fact config would look like this:</p><pre>{<br> &quot;name&quot;: &quot;denom-table&quot;,<br> &quot;version&quot;: 1,<br> &quot;source-config&quot;: {<br>   &quot;type&quot;: &quot;Kafka&quot;,<br>   &quot;topics&quot;: [<br>     &quot;topic_1&quot;<br>   ],<br>   &quot;batch-duration&quot;: X<br> },<br> &quot;primary_table&quot;: {<br>   &quot;table_name&quot;: &quot;transactions&quot;,<br>   &quot;database&quot;: &quot;DB1&quot;,<br>   &quot;columns&quot;: [<br>     &quot;id&quot;, &quot;updated_at&quot;, &quot;entity_id&quot;, &quot;amount&quot;, &quot;status&quot; ...<br>   ]<br> },<br> &quot;join_tables&quot;: [<br>   {<br>     &quot;table_name&quot;: &quot;payments&quot;,<br>     &quot;columns&quot;: [&quot;id&quot;, &quot;updated_at&quot;, &quot;method&quot; ...],<br>     &quot;join_info&quot;: {<br>       &quot;join_type&quot;: &quot;left_outer&quot;,<br>       &quot;join_condition&quot;: [<br>         &quot;transactions.token_id=payments.id&quot;<br>       ]<br>     }<br>   },<br>   ...<br> ],<br> &quot;lookup-config&quot;: {<br> &quot;DB1&quot;: &quot;DB1-Connection-Details&quot;<br> },<br> &quot;sink-config&quot;: {<br>   &quot;type&quot;: &quot;delta-table&quot;,<br>   &quot;primary_key&quot;: [&quot;transactions_id&quot;],<br>   &quot;partition_key&quot;: [&quot;transactions_created_date&quot;]<br> }<br>}</pre><p>This config JSON contains the following details:</p><ul><li><strong>Source-Config</strong>: Kafka topics to be subscribed for CDC events, micro-batch duration</li><li><strong>Primary-Table</strong>: Primary Table Name, Database, and Columns</li><li><strong>Join-Tables</strong>: List of secondary tables, each containing join type, join conditions, and columns</li><li><strong>Lookup-Config</strong>: Connection details of indexed NRT data store for each database being referred to in the config</li><li><strong>Sink-Config: </strong>Target Sink Type (We support Pinot and Delta Table), Sink-related information such as the primary key and the partition key</li></ul><p>As part of initialization, the streaming application parses the config and creates an in-memory data structure to hold the dependency relationship between the tables, which is called a <strong>dependency graph. </strong>It is an N-ary tree, having the primary table at the root.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/512/1*CyCqoeWua2izYvwUBWglqA.png" /><figcaption>Dependency Graph</figcaption></figure><p>Here is an example dependency graph, where each node in the tree would contain table details, <strong>forward links</strong> to the children or dependent tables, and a <strong>backward link</strong> to the parent table.</p><p>The pre-processing step is a <strong>one-time activity </strong>at the start of the streaming<strong> </strong>whose output is a dependency graph, <strong>re-used</strong> across the micro-batches.</p><h4>Processing in a Micro Batch</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HmP1WxixHC_IM4Gji65g6Q.png" /><figcaption>Event Processing</figcaption></figure><p>A batch of events data is read from Kafka as a <strong>Spark RDD </strong>(Resilient Distributed Dataset), a distributed collection of objects which can be stored and processed from different nodes of a cluster. This RDD would collectively contain event data of all tables defined in the config. The dependency graph is then traversed in a <strong>level order format</strong>, processing each node sequentially.</p><p>Processing of each node involves the following steps:</p><ul><li><strong>Filter</strong> the current table records from the Spark RDD</li><li><strong>Repartition</strong> RDD based on the table’s primary key so that multiple events on the same ID, go to a single partition and then <strong>deduplicate</strong> records, keeping the latest record for each id.</li><li>Handle <strong>joins</strong></li><li>Create a <strong>denormalized data frame</strong> and upsert to <strong>Delta Table</strong> on S3.</li></ul><blockquote>The handling of joins depends on whether the current node is a primary table or a secondary table. Hence, we have categorized the process into two flows: <strong>Primary and Secondary</strong>.</blockquote><h4>The Primary Flow</h4><p>Consider the following example: we have to <strong>process</strong> a list of event records from the <strong>primary table </strong><em>Transactions</em>, which is in the form of a partitioned Spark RDD.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hQxf04sbpaVVtiBUUysuPg.png" /></figure><p>We run a <strong>map function</strong> on the RDD partition, which would do the following,</p><ul><li>Traverse the tree in a <strong>level order</strong> format and lookup records from the <strong>NRT datastore</strong>. We optimized this operation by running <strong>lookups in parallel</strong> for nodes at the same level in the tree as they are <strong>independent</strong>.</li></ul><blockquote><strong>Example</strong>: In the above graph, we look up records for tables in the order of: <strong>Refunds &amp; Payments</strong> in parallel and then <strong>Tokens &amp; Orders</strong> in parallel.</blockquote><blockquote>Let’s consider the lookup for table <strong>Payments</strong>.<br>Here, <strong>Transactions and Payments</strong> are joined using the condition: Transactions.entity_id = Payments.id<br>We fetch a list of distinct values for the column <strong>entity_id</strong> in the current RDD partition and run a <strong>lookup</strong> query on the NRT datastore</blockquote><blockquote><strong>SELECT id,updated_at,token_id,order_id … FROM payments WHERE id in &lt;entity_ids&gt;</strong></blockquote><blockquote>The results from the lookup are stored in a hashmap against the table name. These results are further used to look up records for downstream tables.</blockquote><blockquote>Let’s consider the lookup for table <strong>Orders</strong>.<br>Here, <strong>Payments and Orders</strong> are joined using the condition: Payments.order_id = Orders.id<br>We fetch a list of distinct values for the column <strong>order_id</strong> from the lookup results <strong>stored</strong> in the map against table <strong>Payments</strong>. Then, we run a <strong>lookup</strong> query on the NRT datastore</blockquote><blockquote><strong>SELECT id, updated_at, method, status, … FROM orders WHERE id in &lt;order_ids&gt;</strong></blockquote><ul><li>After <strong>completing</strong> the lookup for all tables, we get a <strong>hashmap</strong> as an output containing table names and the corresponding list of records. These results are <strong>merged</strong> as per the fact config to form denormalized rows.</li></ul><p>The denormalized rows from all RDD partitions, are converted to DataFrame using <strong>Avro Schema</strong> and <strong>upserted</strong> into Delta Table.</p><h4>The Secondary Flow</h4><p>Consider the following example, we have to <strong>process</strong> a list of event records from <strong>secondary table </strong><em>Orders</em>, which is in the form of a partitioned spark RDD.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AN0bqSW_Twd4SBh475Rxig.png" /></figure><p>Here, the changes from table <em>Orders</em> <strong>cannot be propagated</strong> directly to Delta Table. Because the Delta Table is <strong>partitioned</strong> on the <em>created_date</em> column of Transactions and <strong>indexed</strong> using columns of Transactions and others. So we would need the values for the <strong>partition key and primary key to upsert</strong> data to Delta Table efficiently.</p><p>We run a <strong>map function</strong> on the RDD partition, which would do the following,</p><ul><li><strong>Back traverse</strong> the tree to the root node and <strong>lookup</strong> records from the NRT DataStore.</li></ul><blockquote>Ex: In the above graph, we look up records for tables in the order: <strong><em>Payments, Transactions</em></strong>.</blockquote><blockquote>Similar to the primary flow, we use the information of <strong><em>Orders</em></strong> to lookup records for <strong><em>Payments</em></strong>, then we use the information of <strong><em>Payments</em></strong> to lookup records for <strong><em>Transactions</em></strong>.</blockquote><ul><li>After <strong>completing</strong> the lookup for all tables, we get a <strong>hashmap</strong> as an output containing table names and the corresponding list of records. These results are <strong>merged</strong> as per the fact config to form denormalized rows.</li></ul><p>Unlike primary flow, the dependency tree is traversed <strong>partially over a sub-branch</strong>, hence we would get <strong>partial denormalized rows</strong>. This is because <strong>Delta Table</strong> supports <strong>partial row updates</strong>, hence we do not need to reconstruct the entire row by traversing all nodes in the tree. These partial rows contain values for the partition key, primary key, and all columns of table <em>Orders</em>, which is <strong>sufficient</strong> to partially upsert the Delta Table.</p><blockquote>Here the <strong><em>Orders</em></strong> table is <strong>dependent</strong> on the <strong><em>Payments</em></strong> table. While doing a lookup for <strong><em>Payments</em></strong>, if the corresponding entry is <strong>not found</strong> we will not be able to join the records from <strong>Orders</strong>. We call these records <strong>Unjoined Records.</strong> Let’s go through the reason behind unjoined records and how to handle them.</blockquote><h4>Unjoined Records</h4><p>In a normal scenario, <strong>Order</strong> is created first and then <strong>Payment</strong> is made. However, as these events come from different microservices databases, they might <strong>arrive out of order</strong>.</p><p>Here are a few <strong>scenarios</strong> where records from table <strong>Orders</strong> would not join with table <strong>Payments</strong> in the dependency graph above,</p><ol><li>Create-event on Orders arrives first, but the corresponding <strong>Payment entry is not yet synced</strong> to the NRT datastore used for lookup</li><li>Create-event on Payments arrives first, but it has a <strong>null value for the join column</strong> <em>order_id. </em>So the Update-event on Payments with <em>order_id </em>is lagging behind.</li><li>Order was placed, but <strong>payment did not happen</strong>. So the corresponding entry for payment will not arrive at all.</li></ol><p>This <strong>out-of-order</strong> problem can span across micro-batches. We <strong>cannot ignore</strong> these unjoined records, because later when the dependent record from <strong><em>Payments</em></strong> arrives, we expect the denormalized table to have non-null values for columns of table <strong><em>Orders</em></strong>.</p><p>Our initial solution was to write these records to a <strong>Temporary Table</strong>. During the subsequent micro batches, records are read from the Temporary Table and <strong>merged</strong> with incoming Kafka records. This ensures that unjoined records would get processed later sometimes whenever the dependent updates would arrive.</p><p><em>But there is a catch!</em></p><blockquote><strong>How long do you keep processing unjoined records?</strong></blockquote><blockquote>Since we <strong>cannot predict</strong> when the dependent updates would arrive, the unjoined records are re-processed in every batch to see if they would get joined. This increased the <strong>batch processing time</strong>. So we optimized the solution by defining <strong>TTLs</strong> (Time To Live).</blockquote><blockquote><strong>How does TTL help in optimizing the processing of unjoined records?</strong></blockquote><blockquote>We connected with product teams to define TTLs for these entities so that they are <strong>purged</strong> from the Temporary Table when the TTL <strong>expires</strong>. We set a <strong>default</strong> TTL of 2 years for entities that do not have clear SLAs. This ensures unjoined records are re-processed for a <strong>short period of time</strong>.</blockquote><p>However, <strong>most</strong> of our entities fell into the <strong>default category</strong> and as a result, more records started <strong>accumulating</strong> in the Temporary Table. In <strong>scenario-3</strong> described above, where dependent updates would not arrive at all, the unjoined records were processed <strong>unnecessarily</strong> for a long time.</p><p>We evolved this solution to <strong>eliminate</strong> the Temporary Table and process downstream nodes as part of the dependent updates. We will cover this in detail in the next blog.</p><h4>Conclusion</h4><p>In this blog, we <strong>deep dived</strong> into the solution, explaining what happens in a micro-batch and how the <strong>processing</strong> differs between primary and secondary tables. We also mentioned a few <strong>shortcomings</strong> in the solution.</p><p>In the next <a href="https://engineering.razorpay.com/real-time-denormalized-data-streaming-platform-part-3-optimisations-and-monitoring-5f7a58d9d97">part</a>, we will cover,</p><p><strong>Optimizations</strong> we did to address the <strong>shortcomings</strong> of the initial solution and handle the <strong>complexities</strong> discussed in <a href="https://engineering.razorpay.com/real-time-denormalized-data-streaming-platform-part-1-9f3c730dd9c6">Part-1</a>,</p><ul><li>Processing <strong>unjoined</strong> records as part of dependent updates</li><li><strong>Distributed caching</strong> to avoid re-processing similar events</li><li><strong>Parallel processing</strong> of secondary tables</li></ul><p>The reason behind <strong>Delta Table</strong> and how we tuned it for our use case</p><ul><li><strong>Optimizing</strong> Delta Merge</li><li><strong>Z-Order Index</strong> and optimizing index creation</li><li><strong>Tuning</strong> Delta Configurations</li></ul><p><a href="https://engineering.razorpay.com/real-time-denormalized-data-streaming-platform-part-3-optimisations-and-monitoring-5f7a58d9d97">Blog-3</a></p><h4>References</h4><ol><li>Spark-RDD: <a href="https://intellipaat.com/blog/tutorial/spark-tutorial/programming-with-rdds/">https://intellipaat.com/blog/tutorial/spark-tutorial/programming-with-rdds/</a></li><li>Delta Table: <a href="https://docs.delta.io/latest/delta-intro.html">https://docs.delta.io/latest/delta-intro.html</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=97dfff40fd8d" width="1" height="1" alt=""><hr><p><a href="https://engineering.razorpay.com/real-time-denormalized-data-streaming-platform-part-2-97dfff40fd8d">Real-Time Denormalized Data Streaming Platform (Part-2)</a> was originally published in <a href="https://engineering.razorpay.com/">Razorpay Engineering</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>