<?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 Mohammed Saif S. on Medium]]></title>
        <description><![CDATA[Stories by Mohammed Saif S. on Medium]]></description>
        <link>https://medium.com/@smsgoonersarfraz?source=rss-f0b1c09495ea------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*Py1nPrY9wfL7FgY9</url>
            <title>Stories by Mohammed Saif S. on Medium</title>
            <link>https://medium.com/@smsgoonersarfraz?source=rss-f0b1c09495ea------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 18:50:23 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@smsgoonersarfraz/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[Is your Data Replication for large systems running slow? Why does it breakdown at scale?]]></title>
            <link>https://medium.com/@smsgoonersarfraz/is-your-data-replication-for-large-systems-running-slow-why-does-it-breakdown-at-scale-08fa9bd789ab?source=rss-f0b1c09495ea------2</link>
            <guid isPermaLink="false">https://medium.com/p/08fa9bd789ab</guid>
            <category><![CDATA[database-performance]]></category>
            <category><![CDATA[data-optimization]]></category>
            <category><![CDATA[data-replication]]></category>
            <category><![CDATA[distributed-systems]]></category>
            <category><![CDATA[scalability]]></category>
            <dc:creator><![CDATA[Mohammed Saif S.]]></dc:creator>
            <pubDate>Mon, 04 May 2026 04:48:22 GMT</pubDate>
            <atom:updated>2026-05-04T04:48:22.113Z</atom:updated>
            <content:encoded><![CDATA[<p>In large-scale replication scenarios, performance does not degrade solely because of data volume; it often breaks down due to misaligned access patterns. By rethinking slicing strategies and leveraging statistical insights already available within the database, it is possible to transform replication from a bottleneck into a scalable, predictable process. OLTP databases are<br>not built to sustain repeated analytical scans. Their design prioritizes fast, concurrent CRUD operations, not long-running reads across billions of rows. Particularly in large enterprises like Fintech , Trans Money mule(Illegal transfer) activity and account takeovers are a huge cause of concern led to data breach and violations attracting FINRA penalties. One such large transactional table in our enterprise of over 16-billion-rows has some insights to track some signals but it was really slow to query in production. For any fraud-detection, It is important to address this in near-real time replication to run some analysis to gauge the transaction motive.</p><p>By leveraging data replication tools/processes to bring the data from SQL server tables to OLAP DBs like snowflake, We will have access to data and run operational insights. In this article, let’s focus on an observed limitation in modulo-based slicing during data replication and propose a boundary-driven slicing approach using statistical distributions and our primary objective is to reduce source-side bottlenecks while maintaining balanced parallel data extraction into analytical platforms such as Snowflake.</p><p><strong>Problem Context</strong></p><p>The dataset in question is a highly transactional table of over 16 Billion records , can be used to track transactional intent across portfolios, enriched with identity and geo-positional changes. These signals are critical for identifying fraudulent behavior patterns such as:</p><ul><li>Account takeovers</li><li>Coordinated fund movement (“pump and dump”)</li><li>Identity mutation across sessions</li></ul><p>However:</p><ul><li>Querying the source transactional system requires <strong>multiple hours per run</strong> .</li><li>Repeated scans degrade OLTP performance</li><li><em>Real-time or near-real-time fraud analysis becomes impractical</em></li></ul><blockquote>Known information: This table is clustered around 4 integer keys; Meaning it is physically sorted in ascending order based on left-most key K1,followed by K2 and so on in the index table.</blockquote><p><strong>Observed Approach: Modulo-Based Slicing</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/624/1*4XWwZVDRrOrpALKba0KfVw.jpeg" /><figcaption>illustration of evenly distributed slices; Concept by Author Image generated by AI</figcaption></figure><p>The initial replication strategy used:</p><ul><li><strong>HVR, data replication tool </strong>for change data capture</li><li><strong>Modulo slicing</strong> on the leading column of a clustered index</li><li>Restrict refresh conditions on a non-indexed “<em>create_date</em>”</li></ul><p><strong>Observed Behavior</strong></p><p>We ingested data using a refresh restrict condition on “<em>create_date</em>” for and the total time taken are as follows:</p><p><em>1-month</em> range approx X records ~7 hours</p><p><em>2-year </em>range approx 40X records ~7 hours</p><p><em>1 month</em> range with X records <strong>took 7 hours just as much as 40x the volume</strong>? Although we used the left most key of the physically sorted/clustered index, The modulo strategy remained in-effective.</p><p><strong>Inference: WHY</strong></p><p>Modulo is applied <strong>row-by-row</strong> which applies the function to locate the slice in which the row gets distributed to. So, We infer that it:</p><ul><li>Requires <strong>multiple full table or range scans</strong></li><li>Disrupts <strong>index ordering</strong></li><li>Introduces:</li><li>Network I/O pressure</li><li>CPU overhead</li><li>Fragmented slice generation</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/624/1*Z-JJVEIO-CdAe9zaPYCFKw.jpeg" /><figcaption>Conceptual design by Author, Image generated using AI</figcaption></figure><p>Whether it’s a single or multi thread, When you use a WHERE col % N = X condition, the database must evaluate the formula for every single row.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/624/1*4XWwZVDRrOrpALKba0KfVw.jpeg" /><figcaption>Equi-distributed slices across a highly cardinal key but enforces Table scans for as many N slices.</figcaption></figure><h3><strong>Proposed Approach: Boundary-Based Slicing using “Index-seek”</strong></h3><p>Instead of distributing rows via modulo, the alternative is to:</p><ul><li>Define <strong>non-overlapping key ranges</strong></li><li>Use <strong>statistical metadata</strong> to create <strong>approximately equal-volume slices</strong></li><li>Preserve <strong>index seek behavior</strong></li></ul><p>A primary key is a logical constraint ensuring row uniqueness, while a clustered key/index defines the physical storage order of data on disk.</p><p><strong>Core Mechanism</strong></p><p>For Boundary ranges, We can estimate the discrete partitions from collecting metadata backed by SQL server to improve the query optimizer called Histogram/Statistics (e.g., SQL Server or similar system). Key components include: RANGE_HI_KEY (upper bound value), RANGE_ROWS (estimated rows between steps), EQ_ROWS (rows equal to high key), DISTINCT_RANGE_ROWS (unique values between steps), and AVG_RANGE_ROWS average rows per value:</p><p><strong>Execution Model Proposed</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9DG3QjrGlr1v_ZH7S_hdVA.png" /><figcaption>Boundary sliced on K1,K2 of clustered key for optimized search</figcaption></figure><p>We can reimagine data distribution density as binning data points in roughly equal data volumes:</p><ol><li>Identify <strong>data distribution across key space</strong></li><li>Construct <strong>percentile-based boundaries</strong></li><li>Combine sparse regions and split dense ones</li><li>Generate <strong>equi-depth slices</strong> for datapoints</li></ol><p><strong>Why Boundary Slicing Performs Better:</strong></p><p><strong>1. Single-Pass Efficiency</strong></p><ul><li>Uses <strong>index seek @ Olog(n) + range scan @ O(n) .</strong> Index scan significantly reduces the number of I/O read operations; it often outperforms a table scan</li><li>Avoids repeated scans required by modulo slicing.</li></ul><p><strong>2. Preserves Data Order</strong></p><ul><li>Aligns with clustered index</li><li>Eliminates fragmentation during slice preparation</li></ul><p><strong>3. Reduces Source Load</strong></p><ul><li>Lower CPU and I/O utilization</li><li>Minimizes network strain</li></ul><p><strong>4. Handles Skew Explicitly</strong></p><ul><li>Dense regions → split further to use secondary index key or apply modulo inside the range</li><li>Sparse regions → combined</li></ul><p><strong>Statistical Backing</strong></p><ul><li>Histogram-based partitioning aligns with <strong>equi-depth histogram principles</strong>, widely used in query optimization (e.g., Ioannidis &amp; Poosala, 1995).</li><li>Systems like Snowflake internally leverage <strong>micro-partition pruning</strong>, which benefits from ordered and range-aligned ingestion.</li><li>Empirical studies show:</li><li>Up to <strong>3–10× improvement in scan efficiency</strong> when range partitioning aligns with data distribution</li></ul><p><strong>In addition: FEEDBACK with Snowflake</strong></p><p>If prior loads exist in Snowflake: We can</p><ul><li>Use NTILE(N) to:</li><li>Derive approximate quantiles</li></ul><p>Given the fixed number of slices (<strong>N</strong>) and controlled concurrency, Boundary slicing ensures that we have non-overlapping, minimal data-skew and predictable execution times upon a few runs to fine-tune the nuances on the data density.</p><p>Almost very time, We know :</p><ul><li>Modulo slicing introduces hidden costs due to repeated scans and row-level computation</li><li>Constant runtime across vastly different data volumes signals inefficiency, not optimization</li></ul><p>Where we can focus on: Math</p><ul><li>Boundary slicing shifts the model from <strong>row computation → range computation</strong></li><li>Leveraging histogram statistics or NTILE bins to enable near-equal distribution without sacrificing index efficiency</li><li>Source-side optimization is critical before tuning downstream systems</li><li>N-Boundary scaling since source side is optimized and uses index seek to locate and cut slices for equi bucket preparation. As Data scales, We can also increase of slice count without fearing multi scan.</li></ul><h3><strong>What if you Land in a Hot Zone</strong></h3><p>If the stats suggest that the RANGE_HI_KEY which is your boundary point is too dense and cannot be split further on 1st key basis, We can switch to using secondary key(if exists) or apply modulo/hash split against a numeric key that may have a discrete distribution.</p><p><em>Yes, HVR’s Boundary Slicing can accept multiple values for its boundaries. When you provide a list of valu</em>es, HVR generates WHERE clauses to segment the data. If you use the Tuple comparison (e.g., (Col1, Col2) &gt; (Val1, Val2) in SQL server 2025 onwards), it will remain highly efficient on your clustered index.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cnX4F7atFR5NVhwtFhqSKg.jpeg" /><figcaption>Image generated by AI</figcaption></figure><p>Here’s the <strong>3-step</strong> explanation of the diagram:</p><ol><li><strong>Find the target K1 range.</strong><br>Use the clustered index on K1 to quickly locate the relevant rows, instead of scanning the whole table. This is the sargable part because the filter uses a boundary range on the indexed column.</li><li><strong>Split that range by K2.</strong><br>After finding the K1 slice, analyze how K2 is distributed inside it and create equal-depth boundaries. This makes each slice hold about the same number of rows, which avoids skew and keeps work balanced across workers.</li><li><strong>Run parallel scans on the slices.</strong><br>Give each worker a different K2 sub-range within the same K1 range so they read disjoint data in parallel. That gives efficient index usage, balanced load, and less contention.</li></ol><p>Using SARGable query is most preferred as it is directly related/allows how the database is to perform a index seek to link-search the range for our equi-depth binning.</p><h4><strong>A word of caution:</strong> If your table is “highly transactional,” ensure you gather these statistics immediately before table refresh. If the data distribution has shifted significantly since the last stats collection, your boundaries will be inaccurate and lead to unbalanced performance.</h4><h4><strong>Conclusion</strong></h4><p>We evaluated both modulo-based and boundary-based slicing approaches, and the right choice depends on the workload rather than a one-size-fits-all rule. Boundary slicing is often more efficient for ordered access patterns, but modulo can still be useful in some scenarios, so it’s important to do the homework, test both options, and choose based on data frequency, distribution, and source-side bottlenecks. The key is to balance performance, scalability, and impact on the source system before standardizing on a strategy.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=08fa9bd789ab" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Stop Paying to Move Redundant Data: Why Shift-Left Architecture Changes Everything in Modern Data…]]></title>
            <link>https://medium.com/@smsgoonersarfraz/stop-paying-to-move-bad-data-why-shift-left-architecture-changes-everything-in-modern-data-stack-bc2a5b163bb2?source=rss-f0b1c09495ea------2</link>
            <guid isPermaLink="false">https://medium.com/p/bc2a5b163bb2</guid>
            <category><![CDATA[snowflake-data-ingestion]]></category>
            <category><![CDATA[modern-data-stack]]></category>
            <category><![CDATA[data-roi]]></category>
            <category><![CDATA[saas]]></category>
            <category><![CDATA[fivetran]]></category>
            <dc:creator><![CDATA[Mohammed Saif S.]]></dc:creator>
            <pubDate>Mon, 13 Apr 2026 06:40:23 GMT</pubDate>
            <atom:updated>2026-04-17T04:49:45.070Z</atom:updated>
            <content:encoded><![CDATA[<h3>Stop Paying to Move Redundant Data: Why Shift-Left Architecture Changes Everything in Modern Data Stack</h3><p>Most data teams aren’t dealing with a storage problem as much as a <strong>data processing problem</strong>: they spend time, compute, and money for pushing low‑ROI , duplicate, or invalid records through layers that don’t actually need to process them. In a traditional medallion architecture i.e <strong>Bronze</strong>, Silver, and Gold layers, redundant data is collected, cleaned, and refined across the architecture. But if we don’t address these issues sooner, growing data ingestion need will turn what should be simple pipelines into expensive, brittle data factories.</p><h3><strong>The Scale:</strong></h3><p>One would argue that storage costs are cheap so we can treat ingestion layer Bronze-certified, raw, “as-is” copy of the source system data. The <strong>MDS</strong>(Modern Data Stack) in any <strong>ELT model</strong> involves various cloud tools like AWS, Fivetran and Snowflake / BigQuery and the costs are based on usage-based pricing plan. The data volumes can be predictable in servicing and operations domain in Fintech Companies based on a few indicators I’ll mention later.</p><p>The Bronze layer is a defacto “data black box” serving as an ingestion gateway to capture everything that happens in our Data Analytics world. The entry gate-way for any data source, the scale is ever increasing .That is why <strong>shift‑left architecture</strong> is no longer an option, rather a necessity in today’s ever growing data demand (90% of world’s data generated in last 2 years). By making smarter ingestion‑layer decisions early especially around <strong>deduplication and filtering via natural keys, Transaction identifiers , and business‑relevant attributes,</strong> teams can dramatically reduce unnecessary processing, improve performance, and build platforms that scale more efficiently.</p><h3>The Real Cost of Waiting</h3><p>Many platforms accept raw data first and clean it later. That <em>feels</em> flexible, but it becomes expensive later. Every query used to filter that set of records that enter the pipeline can trigger storage, transformation, orchestration, and governance downstream. The 1×10×100 rule applies here: <strong>fixing an issue at ingestion might cost 1 unit of effort, in transformation 10 units, and in consumption up to 100 units</strong>. As a principal Data Engineer of Servicing and Operations where the data received is directly proportional toadvisor-client target market , I can say that our data volumes for client‑ and advisor‑centric datasets are predictable and driven by:</p><ul><li>advisor expansion</li><li>mergers and acquisitions</li><li>increased digital engagement for service enhancement</li><li>Call center logs</li></ul><p>the impact is predictable:</p><ul><li>Higher cloud spend. Snowflake charges per Query execution runtimes</li><li>Slower pipeline execution than ideal in orchestration workload.</li><li>More noise for downstream teams.</li><li>More effort spent firefighting issues that could have been caught much earlier.</li><li>Bug fixes and enhancements costs also add to the wait. Engineering effort to optimize ingestion pipelines , and enforce schema validation checks.</li></ul><h3>The Elephant in the Room</h3><p>Although, SaaS CRM partners deliver semantically named, report‑ready datasets, but our ingestion‑to‑refine conversion is still constrained by outdated, narrative‑style business requirements designed for batch (daily/weekly) refresh, not 30‑minute Refresh style in the modern data stack. Also, Failures to consistently tie them into our own logical data model is amplifying the data operational costs that we need to rethink our volume handling across <strong>Colossal Bronze</strong>, Silver and Gold Layer in our MDS.</p><p>Even though external data feeds have improved in timeliness and , structure, and schema enforcement, the <strong>real problem is semantic loss</strong>. The technical dimensional models require explicit decisions about <strong>grain especially around dimensional datsets, conformed keys, SCD behavior, and metric logic</strong>. When those details are missing, teams keep rebuilding the same transformation logic across multiple layers, amplifying compute costs across Silver and Gold and eroding trust in the data when pipelines fail from timely data SLAs of 30 min or less for Servicing and Operations domain needs .</p><p>Looking at the <strong>hidden modeling decisions</strong> underneath them:</p><ul><li>Exactly what business process and grain are we modeling?</li><li>What defines a metric, and where does it live, fact or dimension?</li><li>How do slowly changing attributes evolve over time?</li><li>Which dimensions must be conformed across subject areas?</li></ul><h3>Shift‑Left in Practice:</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S2u3CbGs5ztlB0VmzDfnQg.png" /><figcaption>Shift-left: Fortify Bronze</figcaption></figure><p>Shift‑left architecture moves <strong>validation, filtering, and deduplication</strong> closer to ingestion which is exponentially growing worldwide.</p><p>The starting point for shift-left is to approach data reliability to ensure that data entering your environment is of the highest quality and can be trusted. Shifting left is essential, but it’s not something that can simply be turned on.<strong>Data Reliability</strong> is essential to enable enterprises to make the right decisions based on the right information.</p><p><strong>Data Observability</strong> plays a key role in shaping data reliability, and only with the right platform can you ensure you’re getting only good, healthy data into your system. By understanding the pattern of data stored in Refined which has a time-audit trail of datasets, We can make decisions on Data Reliability. Instead of waiting for downstream jobs to sort out quality, the ingestion layer becomes the <strong>first line of defense</strong>. At its core, that means:</p><ul><li>Deduplicating records <strong>before</strong> they’re stored in <strong>Bronze</strong>.</li><li>Filtering out invalid to quarantine data at the point of entry.</li><li>Validating schema, keys, and business‑relevant attributes <strong>before</strong> data moves downstream.</li></ul><p>When this happens early, every downstream layer becomes lighter, faster, and easier to operate. The <strong>Bronze</strong> layer evolves from a passive landing zone into a <strong>centralized, contract‑enforced</strong> foundation for schema, freshness, and downstream expectations.</p><h3>The AI‑Accelerated Feedback Loop</h3><p>Tools proposed: Fivetran , DBT and Agentish AI on Snowflake.</p><p>Where this becomes truly powerful is when you add an <strong>AI‑accelerated feedback loop</strong> between <strong>Bronze </strong>and the Refine layer. Teams can maintain an audit log that tracks rows received, processed, rejected in refine layer through dbt fact/dimension models jobs over time vs Fivetran‑ingested <strong>Colossal Bronze</strong> raw tables audit over time turning historical processing behavior into proactive design signals. core of the problem statement: <br><strong>Duplicate % ↑ → Reprocess rate ↑ → Cost ↑</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nokntDrWXvR0K3tfgx7VFg.png" /><figcaption>Self learning for optimization scope</figcaption></figure><p>By feeding that audit log into an AI model trained on:</p><ul><li>natural keys and business identifiers :</li><li>transactional patterns and update frequencies</li><li>deduplication criteria</li><li>relevant ingestion attributes</li><li>schema drift and rejection behavior</li></ul><p>The platform learns which tables are good candidates for earlier optimization by comparing the audit outputs at Fivetran <strong>(Bronze) </strong>and DBT/Refine to asses which tables/ potential dimensions can be contracted at ingestion vs those legitimately that belong in Refine layer for complex transformations. So, In an existing enterprise, you have to reengineer workflows from the ground up to deliver the right information to agents at the right moment.The result is <strong>not automation without human oversight</strong>, but co-piloted <strong>decision‑making to take insights</strong>: the AI populates potential candidates , and engineers apply judgment, reinforce contracts, and tighten ingestion‑layer rules over time. We need to reengineer the workflows to match content to context off the deep rooted enterprise data.</p><p>The platform shifts from reacting to data issues to preventing them by turning audit signals into proactive design rules. Duplication and reprocessing create a clear cost chain: more noise → more compute → higher operational overhead. Closing the loop between ingestion and refinement cuts redundant dbt runs and Snowflake consumption, improving pipeline efficiency, reducing latency, and delivering measurable business impact.</p><p>Slowly, the platform becomes <strong>self‑improving</strong>. It starts catching schema evolution sooner(<strong>Bronze</strong>-Ready), recommending where deduplication can move left, and flagging tables likely to generate noise or compute spikes. This is how we can accelerate shift‑left in a quick yet careful human oversight manner. With Centralized logging and operational cost-scale monitor, We can see Data expenses from a holistic manner from data acquisition to operation/insights- ready time-to-delivery.</p><h3>ROI, Performance, and Scale</h3><p>The financial impact is straightforward: <strong>less data in means less compute, less storage, and less overhead on orchestration framework that stacks Snowflake-DBT on Airflow. </strong>But the bigger payoff is strategic. A cleaner ingestion layer reduces technical debt, makes pipelines more predictable, and frees teams from constant cleanup work. That means more time can go toward:</p><ul><li>New data products that drive business value.</li><li>Better analytics and insights, not janitor‑level transformations.</li><li>Automation, optimization, and proactive governance.</li><li>Business‑facing innovation instead of firefighting.</li><li>Reduce Total Cost Of Ownership by strategizing cloud vendor usage.</li></ul><p>Performance improves because pipelines are no longer forced to carry unnecessary weight. Deduplication and filtering at ingestion shorten end‑to‑end latency, improve freshness, and make the platform more responsive to business needs. In environments where fresh, reliable data powers service, underwriting, and customer experience, this is not just an engineering win rather a <strong>competitive advantage</strong>.</p><h3>The 1×10×100 Rule</h3><p>The earlier we address issues at the <strong>business level</strong>, the better. Waiting means you end up paying far more over time. In data, this is the <strong>1×10×100 rule</strong> in action.</p><ul><li>Fixing a quality issue <strong>at ingestion</strong> might cost <strong>1 unit</strong>.</li><li>Letting it slip into <strong>transformation</strong> could cost <strong>10 units</strong>.</li><li>Allowing it to reach <strong>consumption</strong> can easily cost <strong>100 units</strong> in operational overhead, rework, and lost trust.</li></ul><p>If you apply shift‑left thinking early in existing systems , you prevent schema drift, duplication, and poor‑quality data from compounding. Data ROI for tech teams then means <strong>sustaining operational cost</strong>, meeting business demand, and <strong>creating data of interest, not data with interest (tech debt)</strong>.</p><h3>The Bigger Lesson</h3><p>The most important architectural decisions are often made <strong>before data ever reaches transformation or analytics</strong>. If the ingestion layer is designed to <strong>shift‑left early</strong> and is <strong>AI‑aware</strong>, the entire platform becomes more efficient, more scalable, and better aligned with business value.</p><p>Shift‑left architecture turns ingestion from a <strong>passive entry point</strong> into an <strong>active control point</strong> for cost, performance, and scale. And when AI helps the platform <strong>learn from its own audit history</strong>, shift‑left is no longer just a pattern; it becomes a <strong>self‑evolving data strategy</strong>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bc2a5b163bb2" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>