<?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 Nagaraj Tantri on Medium]]></title>
        <description><![CDATA[Stories by Nagaraj Tantri on Medium]]></description>
        <link>https://medium.com/@nagarajtantri?source=rss-721e80e4f538------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*-B5h6J26mrYo_Gp9xs-t7Q.png</url>
            <title>Stories by Nagaraj Tantri on Medium</title>
            <link>https://medium.com/@nagarajtantri?source=rss-721e80e4f538------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 09:13:03 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@nagarajtantri/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 a One-Line PostgreSQL Change Fixed a Silent Concurrent Bug in Our Job Queue]]></title>
            <link>https://nagarajtantri.medium.com/how-a-one-line-postgresql-change-fixed-a-silent-concurrent-bug-in-our-job-queue-b9bd246531cd?source=rss-721e80e4f538------2</link>
            <guid isPermaLink="false">https://medium.com/p/b9bd246531cd</guid>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[concurrency]]></category>
            <category><![CDATA[queue]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Nagaraj Tantri]]></dc:creator>
            <pubDate>Sun, 13 Sep 2026 19:14:19 GMT</pubDate>
            <atom:updated>2026-09-18T15:14:11.892Z</atom:updated>
            <content:encoded><![CDATA[<h3>How We Fixed a Silent Concurrency Bug in Our PostgreSQL Job Queue</h3><p>Our team owned a service that was responsible for calculating metrics from the data. Under the hood, it was a job orchestration system: users submit metric queries, the system compiles them into a DAG of SQL stages, and multiple worker threads poll a PostgreSQL database to pick up stages and execute them against a cloud data warehouse.</p><p>It worked well until it didn’t.</p><p><strong><em>Update (18th Sep 2026)</em></strong>: <em>An earlier version of this post oversimplified how </em><em>ON CONFLICT DO NOTHING interacts with </em><em>REPEATABLE_READ. But I had forgotten to mention with</em><em>REPEATABLE_READ, PostgreSQL throws a serialization error before the </em><em>ON CONFLICT clause is evaluated, so the conflict isn’t silently absorbed. The post has been updated to reflect the full pattern. </em><strong><em>Thanks to a reader for catching this.</em></strong></p><h3>The Load Issue</h3><p>In production, we ran 20,000 to 40,000 queries in BigQuery and were supposed to do that within an SLA of 4 hours. BigQuery offers a concurrency limit and a <a href="https://docs.cloud.google.com/bigquery/docs/query-queues">query queue</a> when you submit a huge number of queries.</p><p>During load testing, we noticed something odd. The same query stage was being executed twice, by different workers, at nearly the same time. The results were identical, but <strong>we were burning double the compute cost and occasionally seeing race conditions when both workers tried to write results back.</strong></p><p>It wasn’t happening for every query, but just often enough to matter at scale.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*R62iXFkGMZMEV2uSliqEEQ.jpeg" /><figcaption>Streamline the execution and reduce the duplicates</figcaption></figure><h3>The Architecture</h3><p>Our system used a common pattern: <strong>database-as-a-queue. </strong>Even though BigQuery offered query queues, our internal state management needed a way to queue before we submit to BigQuery.</p><p>Workers poll a query_stage table looking for stages in BLOCKED status whose dependencies are all DONE. When they find one, they update its status to QUEUED, insert it into a query_stage_queue table, and move on.</p><pre>Worker A: SELECT stages WHERE status = BLOCKED AND all deps DONE → finds stage X<br>Worker A: INSERT INTO query_stage_queue (stage X)<br>Worker A: UPDATE query_stage SET status = QUEUED WHERE id = stage X</pre><p>The architecture looks safe when you picture one worker. But nothing in this flow prevents two workers from picking up the same stage. There’s no lock between the SELECT and the INSERT. If Worker A reads stage X as BLOCKED but hasn’t updated yet, Worker B sees the same state and starts the same work. These workers were independent Docker containers and were using a PostgreSQL table as a queue to pick and push to BigQuery in a batch.</p><h3>The Main Problem</h3><p>We were running all these transactions at PostgreSQL’s default isolation level: READ_COMMITTED.</p><p>Under READ_COMMITTED, each SQL statement within a transaction sees the latest committed data at the moment that statement executes. This means:</p><ol><li>Worker A begins a transaction, reads stage X as BLOCKED</li><li>While Worker A has not yet marked stage X as QUEUED, Worker B begins a transaction and also reads stage X as BLOCKED (A hasn&#39;t committed yet)</li><li>Both workers insert stage X into the queue</li><li>Both workers update the status to QUEUED</li><li>Both proceed to execute the same expensive warehouse query</li></ol><p>There’s no conflict from PostgreSQL’s perspective; both transactions read committed data, and both wrote valid updates. The database did exactly what we asked.</p><h3>The Fix</h3><p>After investigating the duplicate executions and spike in the Queue and BigQuery, we wanted to see what would help us to avoid the above problem.</p><p>The core fix was changing one value: REPEATABLE_READ + try-catch</p><pre>// Before<br>return jdbi.inTransaction(<br>    TransactionIsolationLevel.READ_COMMITTED, <br>    this::queueRunnableStages);</pre><pre>  // After<br>  try {<br>      return jdbi.inTransaction(<br>          TransactionIsolationLevel.REPEATABLE_READ,<br>          this::queueRunnableStages);<br>  } catch (Exception e) {<br>      log.error(&quot;Serialization conflict, skipping cycle&quot;, e);<br>      return List.of();<br>  }</pre><p>Under REPEATABLE_READ, PostgreSQL takes a snapshot of the database at the start of the transaction. Every read within that transaction sees the same snapshot, regardless of what other transactions commit in the meantime. And, crucially, <strong>if you try to update a row that another transaction has already modified since your snapshot, PostgreSQL raises a serialization error rather than silently proceeding.</strong></p><p><em>More in </em><a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-REPEATABLE-READ"><em>PostgreSQL docs</em></a></p><p>Do note that we did this for a batch of queries, which means, at any given time, we would process 100’s of queries to be batched and pushed to BigQuery.</p><p>There’s an important subtlety here. Under REPEATABLE_READ, if two workers start transactions at nearly the same instant and both try to INSERT the same row, the second worker’s INSERT will throw a serialization error. PostgreSQL detects the conflict before the ON CONFLICT clause even gets evaluated. <strong>To handle this gracefully, we wrapped the transaction in a try-catch so the worker catches the serialization exception, logs it, and lets the polling loop move on to the next cycle</strong>.</p><p>Our INSERT looked like this:</p><pre>INSERT INTO query_stage_queue(priority, query_stage_id, project_id)<br>SELECT priority, query_stage_id, project_id<br>FROM to_queue<br>ON CONFLICT (query_stage_id)<br>DO NOTHING<br>RETURNING query_stage_id</pre><p>In the common case, Worker A commits before Worker B starts its transaction. Worker B’s snapshot sees the stage as already QUEUED, the CTE returns zero rows, and there’s nothing to insert. The race is avoided at the read level.</p><p>For the overlap case where both transactions start before either commits, the serialization error fires and the try-catch handles it. By the next poll cycle, the stage is already queued by the winning worker.</p><p>The ON CONFLICT DO NOTHING still earns its place as a belt-and-suspenders safety net for the non-overlapping case, but the primary protection under REPEATABLE_READ is the combination of snapshot isolation (preventing the race in most cases) and application-level error handling (catching the serialization exception when it does fire).</p><h3>What REPEATABLE_READ Is Not</h3><p>A common misconception: REPEATABLE_READ is row-level locking. It&#39;s not.</p><p>Row-level locks (SELECT ... FOR UPDATE) would make Worker B wait until Worker A commits. That works, but it serialises your workers. You lose the parallelism you built the multi-worker architecture for.</p><p>REPEATABLE_READ is <strong>snapshot isolation</strong>. Each transaction works from its own consistent view. Workers don&#39;t block each other. They get deterministic reads, and the database rejects conflicting writes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*M6SR53Li80d88oDVodnFPQ.png" /><figcaption>REPEATABLE_READ + try-catch + ON CONFLICT helped us avoid duplicates in the execution</figcaption></figure><h3>The Trade-off</h3><p>REPEATABLE_READ is not free. The PostgreSQL docs are clear:</p><blockquote>“When an application receives this error message, it should abort the current transaction and retry the whole transaction from the beginning.”</blockquote><p>You need to design for serialization failures. In our case, we wrapped the transaction in a try-catch. When a serialization error fires, the worker logs it and moves on. The polling loop picks up the correct state on the next cycle. No duplicate execution, no user-facing error. But if your architecture doesn’t have a natural retry mechanism like a polling loop, you’ll need explicit retry logic with back-off. Read-only transactions are safe.</p><p>The real cost isn’t CPU or memory. It’s the application complexity of handling conflicts gracefully. A polling architecture with try-catch makes this straightforward. In other architectures, it might not be.</p><h3>The Outcome</h3><p>The fix was four method changes in one file with a try-catch</p><p>The immediate impact was a significant reduction in wasted compute costs; every duplicate execution was a redundant cloud warehouse query burning real money. At our scale (thousands of metric queries processed daily), eliminating duplicates meaningfully reduced warehouse spend.</p><p>More importantly, it fixed a correctness issue. Duplicate executions occasionally produced race conditions on result writes, leading to intermittent failures that were nearly impossible to reproduce in development.</p><h3>Final Words</h3><p>PostgreSQL&#39;s default isolation level is almost always fine. Until you have multiple consumers competing for work from the same table. Then READ_COMMITTED becomes a trap. It looks correct in testing (races are rare at low concurrency) and only breaks under a heavy production load.</p><p>If you’re building a database-polled job queue with concurrent workers, think carefully about your isolation level. REPEATABLE_READ gives you snapshot isolation that prevents most races at the read level. For the cases where two transactions do overlap, your application needs to catch serialization errors, whether through a polling loop that naturally retries, explicit retry logic, or a try-catch that lets the worker move on. ON CONFLICT DO NOTHING adds a useful safety net, but don’t assume it silently absorbs conflicts under REPEATABLE_READ. The serialization check fires first.</p><h3>References</h3><p>A few references from PostgreSQL’s official sources:</p><ul><li><a href="https://www.postgresql.org/docs/7.2/xact-read-committed.html">Read Committed</a>: default isolation level</li><li><a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-REPEATABLE-READ">Repeatable Read</a>: only sees data committed before the transaction began</li><li><a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE">Serializable</a>: serial transaction execution for all committed transactions</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b9bd246531cd" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The One Skill Everyone Needs in the Age of AI]]></title>
            <link>https://nagarajtantri.medium.com/the-one-skill-everyone-needs-in-the-age-of-ai-e4f6040b272b?source=rss-721e80e4f538------2</link>
            <guid isPermaLink="false">https://medium.com/p/e4f6040b272b</guid>
            <category><![CDATA[writing-prompts]]></category>
            <category><![CDATA[future-of-work]]></category>
            <category><![CDATA[technology]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[software-development]]></category>
            <dc:creator><![CDATA[Nagaraj Tantri]]></dc:creator>
            <pubDate>Mon, 23 Feb 2026 22:06:52 GMT</pubDate>
            <atom:updated>2026-02-23T22:06:52.863Z</atom:updated>
            <content:encoded><![CDATA[<p>AI agents are designed to solve your problems, yet we’ve all felt that sting of disappointment when the output arrives. It’s technically what you asked for, but it’s nowhere near what you actually wanted. This is the feeling we all have had and will have if we don’t focus on the one important thing that we should master: <strong>Communication</strong>.</p><h3>The Danger of the “Unspoken Words”</h3><p>One skill is now more important because your unspoken words can be filled in with a ton of words that you don’t mean by the LLMs of the world. After all, that’s the nature of how these LLMs are designed. They are focused on generating a ton of output that can mean anything to any person interpreting them. It’s not just about prompting, but the right way to do it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*XDnDFGskky_pslmi" /><figcaption>Photo by <a href="https://unsplash.com/@markuswinkler?utm_source=medium&amp;utm_medium=referral">Markus Winkler</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3>The Communication Crossroads</h3><p>What’s important is how we can communicate with LLMs! Today, we are at a crossroads where any LLMs we use have a tendency to please the user and would, at any given time and day would try to produce anything that we ask it to do or perform. Now, this means that unless you, as a user, are able to generate a good context for the LLM agent, then you will always fall short of getting any real value.</p><blockquote>We used to have to learn how to <strong>speak to machines</strong> using code. Now, the machine has learned how to <strong>speak our language or read out.</strong> The burden of clarity has shifted from the syntax back to us.</blockquote><h3>How to Improve: The WWE Style</h3><p>Narration helps! Move to a <strong>“Why, What, and Expectations”</strong> flow. To master the WWE approach, you have to flip the script. Don’t just ask for an output; narrate the struggle.</p><ul><li><strong>Why:</strong> Why is this a problem, and how is it hurting your workflow?</li><li><strong>What:</strong> What is the specific asset or result you need?</li><li><strong>Expectations:</strong> What does “perfect” look like?</li></ul><p>Once the agent feels the weight of the context, the What and the Expectations become a surgical strike rather than a shot in the dark.</p><h3>Case Study: From “Task” to “Mission”</h3><blockquote>Don’t give the AI a task; give it a mission.</blockquote><p>Stop asking for features; start describing friction. When you give an agent the ‘weight’ of the problem, you change its objective. It stops trying to match your keywords with generic suggestions and starts engineering a solution that actually solves the pain.</p><p><strong>For example</strong>, imagine you are a Product Owner asking an AI agent to help you draft a user story for a new “Export” button.</p><h4>The “What-First” Mistake</h4><ul><li><strong>Prompt:</strong> <em>“Write a Jira user story for a CSV export button on the analytics dashboard.”</em></li><li><strong>The Result:</strong> You get a generic story: <em>“As a user, I want to export data so I can see it in Excel.”</em> It’s boring, lacks depth, and ignores your specific business constraints.</li></ul><h4>The “WWE” Strategic Approach</h4><ul><li><strong>Why (The Mission):</strong> “Our Enterprise customers are complaining that they can’t include our charts in their board meetings. They need the raw data to plug into their own internal slide templates.”</li><li><strong>What (The Asset):</strong> “Draft a comprehensive Jira user story with detailed Acceptance Criteria for a CSV/Excel export feature.”</li><li><strong>Expectations (The Polish):</strong> “The criteria must include data privacy (GDPR) constraints and handle sets of over 10,000 rows. Use a professional, ‘Ready-for-Dev’ tone. Follow the <em>Given/When/Then</em> format.”</li></ul><h3>The WWE Prompt Placeholder</h3><p>Copy this into your next prompt to take immediate control of the output. Simply fill in the placeholders to bridge the gap between your intent and the agent’s execution</p><pre>### 📝 THE WWE CONTEXT<br>- **WHY:** [The business problem or technical bottleneck we are solving]<br>- **WHAT:** [The specific deliverable: code, RFC draft, or refactor]<br>- **EXPECTATIONS:** [Constraints, tone, or specific &quot;Definition of Done&quot;]</pre><h3>Final Thought</h3><p>The better you communicate with AI, the better you’ll find yourself communicating with your stakeholders. Clarity is a universal language and a requirement today.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e4f6040b272b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[When to Use Git Merge vs Rebase]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://nagarajtantri.medium.com/when-to-use-git-merge-vs-rebase-970f86b6e974?source=rss-721e80e4f538------2"><img src="https://cdn-images-1.medium.com/max/2600/0*Ko3asVwKRCRtaGlL" width="5000"></a></p><p class="medium-feed-snippet">A clear guide to how merge and rebase work, and how to choose the right workflow for your team</p><p class="medium-feed-link"><a href="https://nagarajtantri.medium.com/when-to-use-git-merge-vs-rebase-970f86b6e974?source=rss-721e80e4f538------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://nagarajtantri.medium.com/when-to-use-git-merge-vs-rebase-970f86b6e974?source=rss-721e80e4f538------2</link>
            <guid isPermaLink="false">https://medium.com/p/970f86b6e974</guid>
            <category><![CDATA[git-rebase]]></category>
            <category><![CDATA[git]]></category>
            <category><![CDATA[tech]]></category>
            <category><![CDATA[recommendations]]></category>
            <category><![CDATA[programming]]></category>
            <dc:creator><![CDATA[Nagaraj Tantri]]></dc:creator>
            <pubDate>Thu, 01 Jan 2026 21:30:14 GMT</pubDate>
            <atom:updated>2026-01-01T21:30:14.465Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Should You Abstract SQL?]]></title>
            <link>https://nagarajtantri.medium.com/should-you-abstract-sql-2d3dfb265a14?source=rss-721e80e4f538------2</link>
            <guid isPermaLink="false">https://medium.com/p/2d3dfb265a14</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[product-development]]></category>
            <category><![CDATA[query-builder]]></category>
            <category><![CDATA[relational-algebra]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Nagaraj Tantri]]></dc:creator>
            <pubDate>Mon, 22 Jul 2024 09:39:34 GMT</pubDate>
            <atom:updated>2024-07-22T09:39:34.076Z</atom:updated>
            <content:encoded><![CDATA[<p>In recent years, a plethora of tools have emerged, offering both declarative and imperative methods to construct SQL. Tools like <a href="http://querydsl.com/static/querydsl/latest/reference/html/ch02s03.html"><strong>QueryDSL-SQL</strong></a>, <a href="https://www.jooq.org/doc/3.19/manual-single-page/#sql-building"><strong>jOOQ</strong></a>, <a href="https://calcite.apache.org/"><strong>Apache Calcite</strong></a>, <a href="https://github.com/google/zetasql"><strong>ZetaSQL</strong></a>, <a href="https://github.com/tobymao/sqlglot"><strong>SQLglot</strong></a>, and many others enable users to generate SQL specific to various dialects. The primary reason for leveraging these tools is to build straightforward query builders or Domain Specific Languages (DSLs) that allow stakeholders to query data and produce business reports efficiently.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*WTXAMRPn6wrhFeJa" /><figcaption>Can abstracting a SQL benefit us?</figcaption></figure><h4>How Can We Abstract SQL?</h4><p>There are several ways to abstract SQL, including:</p><ul><li><strong>Stored Procedures</strong></li><li><strong>Query Builders</strong></li><li><strong>ORM (Object-Relational Mapping)</strong></li></ul><blockquote>This discussion will focus on SQL abstraction via Query Builders, which many organizations find useful in supporting team members without a strong SQL background.</blockquote><h4>Understanding the Foundation of SQL</h4><p>To effectively abstract SQL, one must first understand its foundational concepts. SQL, based on relational algebra and tuple relational calculus, comprises various types of statements. Here are the five primitive operators of relational algebra:</p><ul><li><strong>Selection</strong></li></ul><p>Extracts a subset of data based on specific criteria, akin to the WHERE clause in SQL.</p><pre>-- Select all employees from the &#39;employees&#39; table where salary &gt; 50000<br>SELECT * FROM employees WHERE salary &gt; 50000;</pre><ul><li><strong>Projection</strong></li></ul><p>Removes certain columns from a relation, resulting in a new relation with only the specified columns.</p><pre>-- Select only the &#39;name&#39; and &#39;salary&#39; columns from the &#39;employees&#39; table<br>SELECT name, salary FROM employees;</pre><ul><li><strong>Set Union</strong></li></ul><p>Combines the results of two queries, including all unique rows from both.</p><pre>-- Combine employees from &#39;marketing&#39; and &#39;sales&#39; departments<br>SELECT * FROM employees WHERE department = &#39;marketing&#39;<br>UNION<br>SELECT * FROM employees WHERE department = &#39;sales&#39;;</pre><ul><li><strong>Set Difference</strong></li></ul><p>Returns rows present in the first query but absent in the second.</p><pre>-- Find employees in &#39;engineering&#39; department who are NOT in &#39;management&#39;<br>SELECT * FROM employees WHERE department = &#39;engineering&#39;<br>EXCEPT DISTINCT<br>SELECT * FROM employees WHERE department = &#39;management&#39;;</pre><ul><li><strong>Tuple Relational Calculus</strong></li></ul><p>Expresses what data to retrieve without specifying the exact steps, offering a high-level explanation.</p><pre>{ c.name | Customers(c) AND EXISTS o IN orders (o.customer_id = c.customer_id) }</pre><h4>Utilizing These Building Blocks for SQL Abstraction</h4><p>Many libraries and tools, such as <strong>Apache Calcite</strong>, <strong>jOOQ</strong>, <strong>SQLAlchemy</strong>, and <strong>QueryDSL</strong>, enable the construction and optimization of SQL using relational algebra or high-level constructs like typed Java classes. These tools are invaluable for generating efficient SQL queries tailored to specific use cases.</p><h4>Examples of SQL Abstraction Tools</h4><p><strong>jOOQ Example</strong></p><pre>DSLContext create = DSL.using(configuration);<br>Result&lt;Record&gt; result = create.select()<br>                              .from(EMPLOYEES)<br>                              .where(EMPLOYEES.SALARY.gt(50000))<br>                              .fetch();</pre><p><strong>QueryDSL Example</strong></p><pre>QEmployee employee = QEmployee.employee;<br>List&lt;Employee&gt; employees = new JPAQuery&lt;&gt;(entityManager)<br>    .select(employee)<br>    .from(employee)<br>    .where(employee.salary.gt(50000))<br>    .fetch();</pre><p><strong>Apache Calcite Example Using Relational Algebra Nodes</strong></p><pre>FrameworkConfig config = Frameworks.newConfigBuilder()<br>        .build();<br><br>Planner planner = Frameworks.getPlanner(config);<br>RelBuilder builder = RelBuilder.create(config);<br><br>// Build the relational algebra tree<br>RelNode relNode = builder.scan(&quot;employees&quot;)<br>        .filter(builder.call(SqlStdOperatorTable.GREATER_THAN,<br>                builder.field(&quot;salary&quot;),<br>                builder.literal(500000)))<br>        .project(builder.field(&quot;name&quot;), builder.field(&quot;salary&quot;))<br>        .build();<br><br>// Convert the relational algebra tree to SQL<br>SqlPrettyWriter writer = new SqlPrettyWriter(CalciteSqlDialect.DEFAULT);<br>RelToSqlConverter converter = new RelToSqlConverter(CalciteSqlDialect.DEFAULT); <br>SqlNode sqlNode = converter.visitRoot(relNode).asStatement();<br>String sql = writer.format(sqlNode); // Directly format the SQL node<br><br>System.out.println(sql);</pre><h4>Is SQL Abstraction Worth It?</h4><p>The straightforward answer is “yes, maybe”, but the real question lies in the depth and scope of abstraction necessary for your product. Abstracting a language that has been in use for so long is not an ideal game, but it has a few benefits.</p><p>Here are key considerations:</p><ul><li><strong>Stakeholder Needs</strong>: Assess whether your stakeholders prefer a simpler query builder over learning SQL.</li><li><strong>Total Non-Technical Users</strong>: Find out the true number of users you think are going to be benefited with a query builder (#number of stakeholders).</li><li><strong>SQL Dialect Requirements</strong>: Be mindful of dialect-specific constructs that abstraction tools may not fully support.</li></ul><h4>Guiding Principles for SQL Abstraction</h4><ol><li><strong>Limitations and Flexibility</strong>: Provide SQL abstraction with clear limitations, <strong>allowing customers to use SQL directly when needed.</strong></li><li><strong>Robust Documentation</strong>: Ensure thorough documentation, highlighting each operation that abstracts and constructs SQL.</li><li><strong>Practical Examples</strong>: Offer examples that demonstrate the steps from abstraction to SQL, aiding users in understanding the process.</li></ol><h4>Future Trends in SQL Abstraction</h4><p>Emerging tools and technologies are continually enhancing SQL abstraction. Keep an eye on trends like AI-driven for Text2SQL, Query Optimization and advancements in database interoperability.</p><p>A few glimpse:</p><ul><li>Vanna AI: <a href="https://github.com/vanna-ai/vanna?tab=readme-ov-file#train-with-sql">https://github.com/vanna-ai/vanna?tab=readme-ov-file#train-with-sql</a></li><li>Wren AI: <a href="https://www.getwren.ai/">https://www.getwren.ai/</a></li></ul><h4>Conclusion</h4><p>When deciding whether to abstract SQL, ask yourself:</p><ul><li>Is SQL challenging for my team to understand and write?</li><li>Would a query builder tool simplify our workflow?</li><li>Do we need to support SQL dialects with unique constructs (like BigQuery has repeated records that is not SQL standard)?</li></ul><p>By answering these questions, you can make an informed decision on whether SQL abstraction aligns with your project’s goals and if a user is flexible to write such a query and explore.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2d3dfb265a14" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Test In Production — The Ideal Monitoring]]></title>
            <link>https://nagarajtantri.medium.com/test-in-production-the-ideal-monitoring-587b23a541f9?source=rss-721e80e4f538------2</link>
            <guid isPermaLink="false">https://medium.com/p/587b23a541f9</guid>
            <category><![CDATA[monitoring]]></category>
            <category><![CDATA[system-reliability]]></category>
            <category><![CDATA[software-design]]></category>
            <category><![CDATA[sre]]></category>
            <category><![CDATA[production]]></category>
            <dc:creator><![CDATA[Nagaraj Tantri]]></dc:creator>
            <pubDate>Sun, 12 Feb 2023 21:38:17 GMT</pubDate>
            <atom:updated>2023-02-12T21:38:17.252Z</atom:updated>
            <content:encoded><![CDATA[<h3>Test In Production — The Ideal Monitoring</h3><p>Imagine a regression bug (<em>wiki: software bug where a feature that has worked before stops working</em>) in your production system! That is a huge nightmare. The risk keeps rising as the codebase gets more and more contributions from engineers.</p><p>This blog provides a few inputs on monitoring your production system for any regression bug that can be introduced while everyone constantly makes changes.</p><figure><img alt="Growth of Software in Production" src="https://cdn-images-1.medium.com/max/1024/0*jYoqMgcWvweLW74h" /><figcaption>Photo by <a href="https://unsplash.com/it/@emilep?utm_source=medium&amp;utm_medium=referral">Emile Perron</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Let us think of a design wherein a service grows into an ecosystem that does some heavy processing and stores data in a data lake like this</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*MjbjOW9Xmro2UE4f" /><figcaption>A sample service in a production system (something like an ETL)</figcaption></figure><p>Quick overview of the above diagram:</p><ul><li>Data is ingested via the website or a set of IoT devices</li><li>We process the data by cleaning and applying some aggregations</li><li>Store the processed TeraBytes of data in our data lake</li></ul><p>Now imagine a couple of these scenarios:</p><ul><li>Scenario 1 — We introduced a new version of the SDK to connect to our storage layer; in this case, this storage layer is GCS, which now has a rate limit via config. Let us assume this rate limit is applied only if you hit a million requests per minute. Now, Unfortunately, your testing cannot account for this.</li><li>Scenario 2 — We introduced a regex inside our processing; this starts throwing Stackoverflow exceptions while parsing big strings. This could suggest the service keeps constantly getting restarted, and the incoming requests can get rejected.</li><li>Scenario 3 — We introduced a new validation rule for data cleaning before we store it. But this logic was defective, and thus it now reduces the data accuracy.</li></ul><p>In simple terms, the service breaks when integrated with other services or when the data size is massive. So, the natural question that runs through our mind is, Why not write more tests?</p><p>With the microservices world and the terra/petabytes of data storage requirements, it becomes difficult to replicate all those scenarios in a test suite and expect it to pass on every build.</p><h4>What about the unit tests?</h4><p>It’s not enough to write unit tests! You <strong>cannot</strong> unit-test every single method with every possible value. Imagine the ugly mocks you have to place to test service-to-service interactions.</p><h4>What about the integration tests?</h4><p>You can write them, but then consider these:</p><ul><li>The service you are integrating can go down during your tests or would not respond quickly. This means we have a flaky build. We cannot iterate and deploy faster to the production system.</li><li>You are creating test resources that are exclusive to your team. So, you cannot ensure to take into consideration the source data for your system.</li><li>We cannot always produce a StackOverflow exception that keeps your service restarting for all data patterns.</li><li>The production data has a variety of values, like nulls and wildcard characters. We cannot replicate TeraBytes/PetaBytes of the data if you want to test the data integrity.</li></ul><h3>So, what can we do?</h3><h4>Run your tests in the production system!</h4><p>Plan to run live tests on a scheduled basis directly in our production system. This plan is separate from the automated test suites we run as part of our standard automated build tests via your CI/CD. It<strong> should not </strong>be part of your CI/CD pipeline and should run as a separate service within your production system or from outside.</p><p>You need to run a scheduled test to get notified of any anomaly from the service. This anomaly can look like if your service cannot connect to another service. Or it can also surface if the service starts producing an erroneous data record. Or if the user request was rejected when the service keeps restarting.</p><p>The approach you can take is in these simple steps:</p><h4>Time it!</h4><p>We should ensure the full validation is executed in a <strong>finite amount of time</strong>. There needs to be an SLA for the expected turnaround time for this test to be complete.</p><h4>Alert it!</h4><p>The service should warn if results are not matching with your expected outcome. Get alerted even if it takes more than the desired time because there could be an issue if your results are not on time.</p><h4>Monitor it!</h4><p>Keep an eye open for these tests in the production system. Sometimes, we see that these tests are not running anymore; or the scheduler involved in triggering these tests is not doing its job. So ensure to monitor the tests running in the production system regularly.</p><h4>Save it!</h4><p>Keep the tests in a separate repository and ensure to save the result of this monitoring by sharing the report via email every day or week. That brings a lot of confidence and provides visibility into the overall progress.</p><h3>Mechanisms I have used in the past</h3><p>I may not provide detailed input on tools that are best in the market. I can nevertheless point you towards some tools in the market that can assist you in running some tests in the production system and be able to provide a monitoring platform that can alert you if things don’t run as expected. I have personally used these in my previous projects.</p><p><a href="https://www.pingdom.com/real-user-monitoring/"><strong>Pingdom Real User Monitoring</strong></a><strong> </strong>— helps to trigger payload from multiple regions across the globe to your service. Very good if your service reacts differently with different regional users.</p><p><a href="https://docs.sensu.io/sensu-go/latest/"><strong>Sensu Observability Pipeline</strong></a><strong> </strong>— write code to create user requests for your production service and monitor the success of different scenarios.</p><p>There are many more options, but this blog was intended to highlight that production testing is the best bet for ideal monitoring.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=587b23a541f9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[9 Things I Wish I Knew As An Engineer In My Early Career]]></title>
            <link>https://nagarajtantri.medium.com/things-i-wish-i-knew-as-a-pragmatic-engineer-f76d9b24470f?source=rss-721e80e4f538------2</link>
            <guid isPermaLink="false">https://medium.com/p/f76d9b24470f</guid>
            <category><![CDATA[focus]]></category>
            <category><![CDATA[career-advice]]></category>
            <category><![CDATA[engineering]]></category>
            <category><![CDATA[grow]]></category>
            <category><![CDATA[life-lessons]]></category>
            <dc:creator><![CDATA[Nagaraj Tantri]]></dc:creator>
            <pubDate>Mon, 24 Jan 2022 06:34:01 GMT</pubDate>
            <atom:updated>2022-01-24T23:55:59.659Z</atom:updated>
            <content:encoded><![CDATA[<p><strong><em>W</em></strong><em>e always focus on what we want over what we have!</em></p><p>This is exactly what triggered me to think, that I never knew the importance of certain things during my initial career. Over the years I have adopted some of the right focus and this has helped me immensely.</p><p>Always find ways to enjoy your work!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ExpASLg3TQykpDaR" /><figcaption>Photo by <a href="https://unsplash.com/@nickmorrison?utm_source=medium&amp;utm_medium=referral">Nick Morrison</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Here are the items I wish I knew as a Software Engineer!</p><h3><strong>1. The latest technology today will become obsolete tomorrow</strong></h3><p>I started my career with C#, Win Form, WPF, ASP.net, JQuery, IIS7; then moved on to use JavaScript, Python and now Java, React, K8s. I still remember, when I joined my first company, I was hoping I would get a good technology to work and was not thinking on problems to solve, and then eventually ended up thinking, how can I be closer to a specific technology and never thought about, some great challenging problems. Just check the graph below to feel what I said about the programming languages shifting over the years. So I realized I should<strong> focus on solving problems</strong> rather than focusing on finding work on the latest technologies.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/943/0*5f6EfaZMR1gh7aND" /><figcaption><em>% of stackoverflow.com language questions per month</em></figcaption></figure><h3><strong>2. If you really want, you can learn from any engineering task</strong></h3><p>I have had my share of worries, that fixing a bug or integrating some tool were not Core development work, thus eventually ended up neglecting to <strong>learn from an actual task at hand</strong>, for example, if it is an open-source integration, can we learn how these projects are built? or how they apply some simple source code linting or auto-documentation? or how they use design principles in place? While there would be some mundane jobs of fixing bugs; try and envision to make it a complete bug-free and remarkable solution.</p><h3>3. Being asked to fix something manually is an open door for automation</h3><p>There were so many instances where I never tried to see beyond the part of manual interventions. I wish I had planned to automate as much as possible. There are so many ways one can use scripting to automate a mundane task at hand, like using Python or similar scripting, we could automate so much manual work. Working on an automation script is not lame, we can always make things more ecstatic and also push things in Github (it’s a win-win).</p><h3>4. Stop complaining and find solutions</h3><p>There was a time when I have felt bad about working on a code that had loads of bugs and trusts me; it’s no one’s mistake, software lifecycle goes through uncleared tech debts and causes many bugs. But, I forgot one thing, an Engineer should fix or build it, there is no point in complaining, so focus and put all efforts towards fixing those bugs and learn from them.</p><h3>5. Respect everyone, you are not always the smartest person in the room</h3><p>Sometimes, in an attempt to shine and grow, you think that you want to voice your opinion. I have done that and been wrong many times on not researching the right information or telling a senior that they have got it wrong. There are better ways to communicate! like acknowledging their inputs and then stating there could be another possibility for a solution. You should have the right attitude towards everyone’s knowledge and give them the credit for their input. Eventually, I realized I could learn from their wisdom!</p><h3>6. Interact more with leaders in the company</h3><p>During the early days of my career, I always thought that the leaders were not available to interact and that I would not be able to discuss anything with them. This was wrong! Had I thought about discussing the <strong>companies vision</strong> or talking about <strong>their experiences </strong>with different situations, I could have gained better insights into the larger picture. Make an attempt in discussing the vision of the project/team, understand their journey in the company or industry.</p><h3>7. Pay attention to opportunities</h3><p>There was a project which involved Perl Scripting for CI/CD and I ended up thinking I am not learning anything which is the latest in the software tech stack. Had I invested my time to scale the script and reduce bugs, I could have helped the company to solve things faster, could have scaled their entire DevOps pipeline. Eventually, time spent on not taming the opportunities were put into feeling bad and nothing good happened. So always try to see if there is anything that can be done better in your current work and leverage that opportunity!</p><h3><strong>8. Focus on excellence, success comes along</strong></h3><p>I have had my share of thinking that I want to be a great engineer but focused less on learning for the sake of my engineering passion and solving more problems. The famous dialogue from 3 Idiots movie is completely relatable! I fell short of applying such good thoughts, like don’t run behind the thought of, how to be successful, but focus on just learning better engineering practices or solving problems that would eventually give us the right direction and make us happy.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/0*BeSP368PxxYv6H15" /><figcaption>From 3 Idiots Movie — “Success ke peeche mat bhaago, excellence ka peecha bhaago, success jhakk marke tumhare peeche aayega”</figcaption></figure><h3>9. Enjoy Your Work!</h3><p>There is a no bigger evil than thinking you are missing something. <strong>The classic FOMO</strong> of software technologies always lurches around and one should find ways to avoid being in that zone and focus on enjoying the work. I have had my time running behind the knowledge, just to feel good that I am not missing on the technologies. I should have rather focused on scaling my current work and gotten deeper involvement in solving what lies ahead of me. So always focus on learning and work towards your passion to solve problems and Enjoy!</p><h3>Conclusion</h3><p>Experience matters and thus indeed comes the wisdom of doing the right thing. The above items are some things I wished I knew when I started my career, but over the years I have also learnt one more thing, i.e: <strong><em>It’s never late to focus on your passion and enjoy your work!</em></strong> Now with the right focus, it has helped me to do the best things at the right time. Over the years, once I started paying attention, it has helped me to achieve many good things in my career.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f76d9b24470f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Builder Pattern—Design Pattern in Plain English for Developers]]></title>
            <link>https://nagarajtantri.medium.com/builder-pattern-in-plain-english-under-design-pattern-b34501c814aa?source=rss-721e80e4f538------2</link>
            <guid isPermaLink="false">https://medium.com/p/b34501c814aa</guid>
            <category><![CDATA[technology]]></category>
            <category><![CDATA[java]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[tech]]></category>
            <category><![CDATA[design-patterns]]></category>
            <dc:creator><![CDATA[Nagaraj Tantri]]></dc:creator>
            <pubDate>Sun, 16 Jan 2022 08:04:22 GMT</pubDate>
            <atom:updated>2022-01-20T06:57:36.162Z</atom:updated>
            <content:encoded><![CDATA[<p>Lets say you have an user interface to fetch the data for showing an advertisement campaign dashboard, that looks something like this</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bOZDiCspEtruoWD3dumehw.png" /><figcaption>Simple UI to capture the details for a advertising data</figcaption></figure><p>If you observe closely this dashboard has a set of inputs/controls:</p><ul><li><strong>Mandatory parameters</strong> — like Advertiser, Campaign, Start Date &amp; End Date</li><li><strong>Optional parameters </strong>— like Exclude In-Active Users, Limit Data, Budget, Compare with Competitors.</li><li><strong>Button to fetch &amp; Graphs to display</strong> — An action and a result</li></ul><p>Now, think how can we represent this data operation in our code (assume like using Java to store data).</p><p>Immediately, the usual thing that comes to our mind is to represent all the above parameters as a class object and then perform the action. A typical class that looks like:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/61012e8947f9f6618df276f53814f4a4/href">https://medium.com/media/61012e8947f9f6618df276f53814f4a4/href</a></iframe><p>You see the pattern, multiple constructors to ensure that we can handle optional parameters. Also, ensuring to have so many setters to provide access to set the data.</p><h4>What is the drawback/problems of the above usage?</h4><ul><li><strong>Too many Constructors</strong>: Different types of constructors to support optional parameters. For example:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YKt6gjC6p1EIeyE29xDu3Q.png" /><figcaption>Constructors are going to be painful to handle and remember the options</figcaption></figure><ul><li><strong>Object Setters </strong>(too many modifications): Maintaining the setters to allow/not allow parameters becomes very tedious. What if you want to make it immutable? Then again option one, i.e, constructor</li><li><strong>Open/Close principle violation</strong>: The more optional parameters comes along, we need to adjust the object creation logic.</li></ul><h3>Is there a better way?</h3><p>Yes, there is an awesome way and that is the <strong>Builder Pattern!</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zbUshCo6rn9l58thdI-AOQ.png" /><figcaption>Builder Pattern</figcaption></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0673bb0cb26ce2ab39abdede6a3f13b4/href">https://medium.com/media/0673bb0cb26ce2ab39abdede6a3f13b4/href</a></iframe><h4>How does this work?</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*U3HfXzkKYFj80V6VQsUZng.png" /><figcaption>Choose the main constructor and build the object more effectively</figcaption></figure><p>So, as you see above, here’s the main highlight:</p><ul><li><strong>Immutable state</strong>: Once the object data is set, you can’t modify the data</li><li><strong>Optional requirements of an object</strong> can be handled via better functional representations and easier to read</li><li><strong>Open/Close principle</strong> of S.O.L.I.D principles is easy now, all you can do is modify and still is effective to extend the class with more functionality for optional parameters.</li><li><strong>Default values to be added: </strong>It’s also easy to add default values like dontShowInActiveData</li><li><strong>Neater call representation</strong> to ensure we don’t need object reference always</li></ul><p>A builder pattern is very useful if we want to create different representations of a complex object or encapsulate creating and assembling the parts of a complex object in a separate Builder object. Hope this was useful! You can checkout this example in more detail in my GitHub repository:</p><p><a href="https://github.com/tan31989/java-samples-for-tutorials/tree/master/src/main/java/org/java/sample/tutorials/designpatterns/builder">java-samples-for-tutorials/src/main/java/org/java/sample/tutorials/designpatterns/builder at master · tan31989/java-samples-for-tutorials</a></p><p>Thanks for reading!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b34501c814aa" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Minimisation | MiQ’s Data Governance process on TeraBytes of data]]></title>
            <link>https://medium.com/miq-tech-and-analytics/data-minimisation-miqs-data-governance-process-on-terabytes-of-data-a98fffc4d886?source=rss-721e80e4f538------2</link>
            <guid isPermaLink="false">https://medium.com/p/a98fffc4d886</guid>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[data-governance]]></category>
            <category><![CDATA[s3]]></category>
            <category><![CDATA[software-engineering]]></category>
            <dc:creator><![CDATA[Nagaraj Tantri]]></dc:creator>
            <pubDate>Tue, 04 Jan 2022 08:07:15 GMT</pubDate>
            <atom:updated>2022-01-05T00:35:58.082Z</atom:updated>
            <content:encoded><![CDATA[<h3>MiQ’s Data Governance process on TeraBytes of data (Data Minimisation)</h3><p>Data Governance is an important concept concerned with Data Management, wherein, it’s a capability that would enable an organization to ensure high data quality throughout its lifecycle. The odds of not doing good Data Governance can come at a cost of a maximum of €20 million (about £18 million) or 4% of annual global turnover, whichever is greater in <a href="https://www.itgovernance.co.uk/dpa-and-gdpr-penalties">EU GDPR</a> or a penalty of $7,500 for every intentional violation of the law with <a href="https://oag.ca.gov/privacy/ccpa">CCPA</a>.</p><p>When we talk about data quality, it includes a wide range of areas, like availability, usability, consistency, integrity, and data security.</p><p>In this blog, we have outlined how data security under Data governance is done at MiQ, and we call it Data Minimisation.</p><h3>What is Data Minimisation?</h3><p><a href="https://edps.europa.eu/data-protection/data-protection/glossary/d_en">Data Minimisation</a> resonates with minimising the security risk by abstracting or pseudonymising the actual data to remove the risk involved.</p><h3>So how does this work?</h3><p><strong>MiQ ingests close to ~10+ TeraBytes of AdTech data </strong>every day using its <a href="https://medium.com/miq-tech-and-analytics/ingesting-terabytes-of-data-every-day-using-microservices-architecture-at-miq-7ade41256a7f">microservices</a> architecture. When we ingest data of such magnitude, we also ensure to apply data governance while it’s in transit and enable our analytical capabilities. There are <strong>170+ datasets that we onboard </strong>to build our solutions and our business needs to deliver these governed datasets on time.</p><h3>Focus on data security</h3><p>MiQ datasets are required for the AdTech ecosystem, wherein, we onboard the<strong> user’s PII (Personally Identifiable Information) like Device ID, IP Address, Lat-Long, </strong>and<strong> DSP User Cookie ID</strong>, which we then use for our custom solutions and product suite. While this onboarded data is with a user’s consent, we would eventually need to provide the right amount of security for these datasets and ensure they are used effectively.</p><p>For instance, if you are trying to build insights on all of the user’s journey on a website, you ideally don’t need those raw/original user identifiers, as it requires a lot of aggregations (like sum/average and so on).</p><p>Our main focus was to create a <strong>streamlined data ingestion process</strong>, wherein, the data once onboarded needs to be secure and shall only be used for the right purpose. It’s not allowed to be misused or enable any path that can lead to problems. To build this, we have brainstormed extensively on possible solutions and have tried to incorporate the right measures to increase data security.</p><p>An overview of MiQ’s Data Ingestion Platform</p><figure><img alt="MiQ’s Ingestion Service using Data Governance while transferring the data" src="https://cdn-images-1.medium.com/max/1024/0*plTAa42CHc3HlVGi" /><figcaption>MiQ’s Ingestion Service using Data Governance while transferring the data</figcaption></figure><p>For the size of data we ingest in MiQ, we need to handle data security at scale.</p><h3>What does Security at Scale look like?</h3><p>To include security for the user’s PII, we have two main requirements:</p><ul><li><strong>Ensure the user’s PII is pseudonymised/hashed for Insights</strong> — we don’t need the real/original user identifiers for performing insights like aggregation or understanding the user’s ad journey.</li><li><strong>Secure raw user’s PII for </strong><a href="https://en.wikipedia.org/wiki/Targeted_advertising"><strong>Targeted Advertising</strong></a><strong> </strong>(we call this “<strong>Activation</strong>”, a process of user bidding strategy for delivering advertisements) — we need the real/original raw user identifiers to be available to deliver the advertisements to the right audience. This raw data would need to be behind proper security with access control and shall not be easy to access for any other purpose.</li></ul><p>Here are some different technical solutions which we considered:</p><h4>1. Centralized Cache for User PII Mapping</h4><p>This solution involves storing all the user identifiers mapping in a <strong>centralized cache</strong> like Redis, wherein, the key would be a hash of the user identifier and the value is the actual user identifier.</p><p>Advantages:</p><ul><li>There would be one data source for Insights and for our Re-targeting requirements</li><li>We can introduce <strong>better security and governance in one place</strong></li></ul><p>Disadvantages:</p><ul><li><strong>Costly</strong> to store all the data at one centralized cache (size of the cache)</li><li><strong>Ensuring cross-region replication</strong> of this distributed centralized cache (like Redis) would make it very hectic to maintain and, as mentioned, this comes with a high cost.</li><li>The incoming data traffic surge to this cache can mean, this could become a bottleneck if it can’t handle the huge requests. In our case, if we upload 10 Million user identifiers every time, then we need to keep hitting this for every use case</li></ul><h4>2. Separate Copy of User Data</h4><p>We would need to store separate data, one for Insights, which has all the hashed data and another one for the original data which would be required for re-targeting the users.</p><p>Advantages:</p><ul><li><strong>Less hassle</strong> than to store a global lookup for all identifiers in one place</li><li>Storing in object storage like <strong>S3 is cheaper</strong> compared to running a high available cache for all the user mappings. Distributed and easily maintained data.</li><li><strong>Can replicate</strong> the same data for our testing workloads</li></ul><p>Disadvantages:</p><ul><li><strong>Copies of data being stored</strong>, i.e., one for insights and another for Ad targeting.</li><li>Managing the stakeholder expectations to maintain the integrity of hashed data and raw data along with providing the right security for our stakeholders would become challenging.</li></ul><p>We ended up choosing a later solution because of the cost and ease of maintenance.</p><p>Take a look at the flow of how to store a separate copy of the data</p><figure><img alt="The entire MiQ Data Governance process" src="https://cdn-images-1.medium.com/max/971/0*sQ8hhSn-FVAH5Sf2" /><figcaption>The entire MiQ Data Governance process</figcaption></figure><h4><strong>MiQ Data Governance process — Here’s a sneak peek of how the entire process works</strong></h4><ul><li>Load the data into a temporary staging area within S3</li><li>Verify the data, i.e, if there is a need for hashing or pseudonymising, then we apply this logic via a Spark-based template.</li></ul><p>The data minimisation techniques we use are:</p><ul><li><strong>User ID</strong> (64 BigInt) — add a MiQ salt and then apply MD5 hashing on that data.</li><li><strong>Device ID</strong> — this should always be converted to lowercase and then add a MiQ salt while applying SHA-256 hashing post the salt.</li><li><strong>IP Address</strong> — we use our custom logic of finding the Zip Code and other details for the given IP address. We then add a MiQ salt and hash it using MD5 hashing.</li><li><strong>Lat-Long </strong>— truncate them to 2 decimal places to reduce the proximity</li><li><strong>Referral URL</strong> — remove the query parameters that might have PII being passed from the generating website</li></ul><p>Once hashed or modified, we then store it for Insights purposes. If this data is also needed for targeted advertising, then we keep raw/original data separate into another S3 bucket which is hidden for Insights and any other common usages or misuse.</p><h3>The Important Security Aspects that we follow</h3><p>To ensure the raw data is secure (i.e. it should not be accessible to anyone directly), we have ensured that it’s only accessible by our in-house services like <strong>Analytics Studio</strong> and <strong>Trading Lab</strong>. Our stakeholders ensure to use these platforms to act on raw/original data to trigger advertisements.</p><ul><li>The <strong>Analytics Studio</strong>, an internal platform, which is responsible for analytics, ensures that it hides the actual data access and provides a wrapper on top of the data for analytics and re-targeting. <strong>This acts like a clean-rooms solution within MiQ.</strong></li><li>The <strong>Trading Lab</strong>, another proprietary platform, is responsible for allowing our traders to re-target users for advertisements and allows them to use the required users PII data. This platform hides the original data from stakeholders.</li></ul><p>Today, we can apply this process to Terabytes of ingested data and these data pipelines run periodically. Our ability to integrate the <a href="https://en.wikipedia.org/wiki/Extract,_load,_transform">ELT</a> (Extract, Load &amp; Transform) with data governance has been very important and has helped us to achieve better security measures.</p><p>In the future, we are exploring ways to focus more on exploring tools like Apache Ranger or Databricks Delta Lake for trying to reduce the duplication of data in S3. Keep watching this space for more such blogs.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a98fffc4d886" width="1" height="1" alt=""><hr><p><a href="https://medium.com/miq-tech-and-analytics/data-minimisation-miqs-data-governance-process-on-terabytes-of-data-a98fffc4d886">Data Minimisation | MiQ’s Data Governance process on TeraBytes of data</a> was originally published in <a href="https://medium.com/miq-tech-and-analytics">MiQ Tech and Analytics</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Ingesting terabytes of data every day -using Microservices Architecture at MiQ]]></title>
            <link>https://medium.com/miq-tech-and-analytics/ingesting-terabytes-of-data-every-day-using-microservices-architecture-at-miq-7ade41256a7f?source=rss-721e80e4f538------2</link>
            <guid isPermaLink="false">https://medium.com/p/7ade41256a7f</guid>
            <category><![CDATA[data-integration-tools]]></category>
            <category><![CDATA[microservices]]></category>
            <category><![CDATA[cloud]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[data-ingestion]]></category>
            <dc:creator><![CDATA[Nagaraj Tantri]]></dc:creator>
            <pubDate>Tue, 18 May 2021 14:42:36 GMT</pubDate>
            <atom:updated>2021-05-23T23:45:33.933Z</atom:updated>
            <content:encoded><![CDATA[<h3>Ingesting TeraBytes Of Data Every Day — using Microservices Architecture at MiQ</h3><p>Data-driven solutions require faster access to data and this is especially true for an analytics-based solution that uses data at its core. At MiQ, we always focus on this thought and have ensured to treat data as a first-class citizen. Keeping an eye to enrich our ability to ingest data at scale and provide a seamless journey for analytics. At MiQ, on average, we onboard 10+ TB of compressed Adtech data daily into our infrastructure.</p><p>Our journey in building a Batch Data Ingestion solution at MiQ has gone through a couple of iterations on identifying the right set of tools and procedures. Over the years we have adopted a path that is well suited to scale faster. Before we talk about the current setup and the future vision, let us talk about what was available during the initial days.</p><h3>Before — how it was NOT scalable before</h3><p>Our initial usage was purely driven by business needs and was focused on getting the data ingested to analyze. Back then when we started and wanted to pursue data ingestion, we typically had a monolithic approach, i.e. with one service running on an EC2 server, which was using Apache Camel for connecting to source and sink.</p><figure><img alt="Legacy system" src="https://cdn-images-1.medium.com/max/1024/1*xf5Oj5hQX9zFZYhSkE3Atg.png" /><figcaption>MiQ Legacy Feed Service</figcaption></figure><p>In simple terms:</p><ul><li>A standalone java console application, named “Feed Service”, which would read inputs from a properties file.</li><li>It would launch <a href="https://camel.apache.org/manual/latest/walk-through-an-example.html">Apache Camel routes</a> and initialize all source and sink connectors and execute pipelines.</li><li>These routes would connect to some of the important data sources which MiQ uses for data Ingestion like AWS S3, Google Cloud Storage (GCS), SFTP and so on.</li><li>Once it connects, the pipelines were asynchronously triggered via the schedulers to transfer data to the targets like MiQ’s own AWS S3 or AWS Redshift</li></ul><h3>Limitations we faced</h3><ul><li><strong>Standalone service</strong> — a single point of failure</li><li><strong>Non Maintainable Code</strong> — Every pipeline was being customized for the specific use case, which resulted in a lot of unmanageable custom code.</li><li><strong>The restart was a pain</strong> — The <strong>java properties file</strong> was growing and service restart was not easy.</li><li><strong>No Shutdown hook</strong> — There was no support for the shutdown hook in the code, thus whenever we wanted to restart the service, we would eventually have to wait for hours until the existing pipelines would stop.</li><li><strong>Difficulty in debugging</strong> — the logs were bare metal with one log file view for all pipelines, it was difficult to pin down on a specific pipeline and debug the issue. No tool was used to filter the logs over a while and for a given pipeline.</li><li><strong>Slow to market-ready</strong> — every time we received a new request to onboard data, we would face a lot of issues in designing the pipeline/re-using them. We also saw that any code change would introduce a lot of maintenance for older pipelines</li><li><strong>Combined multiple logic of ingestion and processing</strong>: While onboarding the data, there were several nuances of how the data files need to be accessed and the format of data. We ended up having custom processing for some of the pipelines, like column transform and the file format change from JSON to CSV and no common functionality.</li></ul><p>We focused on business deliverables to make sure we could address our clients faster. Later, it caused more issues to scale as we grew in new markets and new clients, for instance, when we started with US and APAC markets, we had to customize our data ingestion per region, which was very cumbersome. We had to change our strategy with onboarding the data and building a scalable solution.</p><h3>Now — how we modeled it</h3><p>While the buzzword was Microservices everywhere, we then focused on getting there eventually. The legacy Feed Service was still operational and we had to formalize a design that would help us achieve faster data ingestion. It took us some time to make the complete transition from legacy to the new scalable service and it’s been a rollercoaster ride, considering there are also challenges when you choose to build microservices and expecting the same to follow the best practices. <strong>We currently have close to 300+ pipelines running in our production</strong> scheduled as hourly/daily/weekly/monthly.</p><figure><img alt="Current Setup Diagram" src="https://cdn-images-1.medium.com/max/1024/1*19qAoj_3qQgDrwEDQGhiZg.png" /><figcaption>Current Setup</figcaption></figure><p><strong>NOTE</strong>: The part of using Processing Service with Qubole, shown in the above diagram is not part of the Data Ingestion requirement, but in true sense the onboarding of data that consists of data wrangling (like data format conversion to Parquet, applying Data Governance), we needed a Processing layer which supports BigData ecosystem. It is our in-house microservice to fulfil this requirement.</p><p>A brief explanation of the above high-level design:</p><ul><li><strong>Microservice Approach</strong> — We have now moved on to make our services adopt <strong>Domain-Driven Design</strong> and create a couple of microservices (Data Ingestion Service/Data Catalog Service) to provide batch ingestion capabilities. This follows the <strong>Event-Driven Architecture</strong> to communicate with other services.</li><li><strong>Abstract tools or wrap it</strong> — The Data Ingestion Service abstracts the tools used for transferring data across multiple sources and target systems. Currently, there are two tools that we use, i.e. <a href="https://github.com/streamsets/datacollector">Streamsets Data Collector</a> and <a href="https://github.com/apache/nifi">Apache NiFi</a>. The way we abstract it out is that we use their API and create pre-determined templates for the pipelines like S3-S3, SFTP-S3 and so on. <strong>The benefit is we don’t have to reinvent the wheel and write the connectors ourselves. </strong>This will also make us agnostic to any tool provider and can always plug-and-play newer tools whenever we find more providers, like <a href="https://cloud.google.com/data-fusion/plugins">Google Cloud Data Fusion</a> or <a href="https://aws.amazon.com/datapipeline/">AWS Data Pipeline</a>.</li><li><strong>Configurable and Extensible</strong> — Everything is configurable via the Pipeline and Catalog entities to ensure the pipeline data transfer is scalable for new use cases. Even tools like Stream sets Data Collector (SDC) can be replaced with Apache NiFi over the required feature which is not supported in SDC.</li><li><strong>Highly Available</strong> — Everything is running in our AWS EKS Kubernetes Cluster. The configurations are now stored in MongoDB as a set of entities, i.e., we use the industry-standard domain entities for denoting pipelines, datasets and schema, for all our data pipelines.</li><li><strong>Privacy and Data Governance at scale</strong> — This structure takes care of overall Data Governance and Data Security while transferring the data. For instance, the moment we onboard the data we ensure to run it through our Processing Service to process and remove PII while storing it in our Infrastructure.</li><li><strong>Separation of concerns — Data Wrangling</strong>: We have ensured to move the heavy lifting of modifying data to the required format and governance using our in-house Processing Service which internally uses BigData platforms like Qubole and Databricks.</li></ul><h3>Limitations faced</h3><ul><li><strong>Non-user Friendly</strong> — considering this service is predominantly API/Event-driven, the stakeholders in MiQ, like Business Analysts, would find it difficult to configure and run the same.</li><li><strong>Data Lake Vision</strong>: to ensure we can build a platform that can streamline the onboarding of data as a <a href="https://en.wikipedia.org/wiki/First-class_citizen">first-class citizen</a>, we need to make sure the data once on-board, is seamlessly integrated with a data preparation tool before anyone wants to perform analytics.</li><li><strong>Integrated Data Health</strong>: today our Data health is not part of the ingestion pipeline and to ensure the integrity of data is maintained we run it as a separate entity.</li></ul><h3>Future — how we intend to shape the future</h3><p>The whole focus moving forward would be to make it more accessible for users running any kind of workload and to make the data onboarding easier, which would include applying data governance, integrating data health and preparing the data while in transit.</p><p>We are looking at our internal tool named <strong>Studio</strong>, which would help us shape any data ingestion being applied seamlessly.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yFPplJTkMSUsOY_7XiHC7g.png" /><figcaption>Future ahead</figcaption></figure><p>Why abstract the underlying tools and provide a UI?</p><ul><li><strong>Ease Accessibility</strong>: We are focusing on making it easy to access and thus providing a user interface and allowing all the stakeholders to build and onboard their pipeline.</li><li><strong>Reducing the technical knowledge for stakeholders</strong>: Our stakeholders are a mix of Business Analysts, Solution Engineers, and also sometimes Product Analysts, who need data from a couple of different data sources/vendors. They need a simplified view and a minimalist approach to connect source and target. For instance, the target storage for big data defaults to <a href="https://en.wikipedia.org/wiki/Apache_Parquet">Parquet</a> post applying the Data Governance. The stakeholders need not worry about choosing the right data format, they should rather be guided over this best practice to store the data.</li><li><strong>Data Lake Vision</strong>: This UI is also being modelled to provide access to the other modules like Data Preparation and other common processing templates. Enabling the onboarded data to be easily accessible for analytics, we focus on this platform to prepare data on arrival.</li></ul><h3>Limitations that we can face</h3><ul><li><strong>Toolset Change Management</strong>: With every change underlying in the toolsets source and target connectors, we can end up with a modification to our stack and fix the UI. There are ways to mitigate this, by checking on backward compatibility and similar approaches.</li><li><strong>Faster new Data Connectors to UI</strong>: The backend Ingestion service allows API for setting up the pipeline between different connectors. The UI is modeled to accept inputs as per the API spec. This can create a bottleneck if the API is not being designed by keeping the UI in focus. The best way to tackle this would be to render the UI based on the contract of backend service, i.e. Generating config-driven dynamic UI components.</li></ul><h3>In Summary</h3><p>Our ability to scale for every market requirement has been more agile and we aim to build a platform that can treat data at its core. This journey of moving from a legacy monolithic service to a better scalable ingestion platform has also inculcated us to think for a better Self-Serve Platform. There have also been many challenges, for instance, in the monolithic approach, we used to process the data. We couldn’t migrate such pipelines easily, so we ended up separating the monolithic pipeline to accept the multi-step process of getting ingested first and then processing it separately. In retrospect, it is a daunting task to build a platform that can address so many different requirements, but it’s a great experience and shows us that every stage of software development is a learning phase.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7ade41256a7f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/miq-tech-and-analytics/ingesting-terabytes-of-data-every-day-using-microservices-architecture-at-miq-7ade41256a7f">Ingesting terabytes of data every day -using Microservices Architecture at MiQ</a> was originally published in <a href="https://medium.com/miq-tech-and-analytics">MiQ Tech and Analytics</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Chaining multiple HTTP API’s via Apache NiFi]]></title>
            <link>https://nagarajtantri.medium.com/chaining-multiple-http-apis-via-apache-nifi-72c4d14c072d?source=rss-721e80e4f538------2</link>
            <guid isPermaLink="false">https://medium.com/p/72c4d14c072d</guid>
            <category><![CDATA[tech]]></category>
            <category><![CDATA[http-request]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-ingestion]]></category>
            <category><![CDATA[apache-nifi]]></category>
            <dc:creator><![CDATA[Nagaraj Tantri]]></dc:creator>
            <pubDate>Fri, 15 Jan 2021 14:08:45 GMT</pubDate>
            <atom:updated>2021-01-19T01:36:08.850Z</atom:updated>
            <content:encoded><![CDATA[<p>May it be streamlining a set of defined steps or any finite automata, automation has become a routine in many organisations. I believe there are many tools and frameworks which help us achieve this. <a href="https://nifi.apache.org/">Apache NiFi</a> is one such tool for any organisation which requires to onboard data and transform the same via a defined workflow.</p><p>Apache NiFi has been around the industry from long and to quote <a href="https://en.wikipedia.org/wiki/Apache_NiFi">Wikipedia</a> on its origin, it has a unique introduction:</p><blockquote><strong>Leveraging the concept of </strong><a href="https://en.wikipedia.org/wiki/Extract,_transform,_load"><strong>Extract, transform, load</strong></a><strong>, it is based on the “<em>NiagaraFiles</em>” software previously developed by the US </strong><a href="https://en.wikipedia.org/wiki/National_Security_Agency"><strong>National Security Agency</strong></a><strong> (NSA), which is also the source of a part of its present name — <em>NiFi</em>. It was </strong><a href="https://en.wikipedia.org/wiki/Open-source_model"><strong>open-sourced</strong></a><strong> as a part of NSA’s technology transfer program in 2014.</strong></blockquote><p>There are various connectors or so-called Processors in Apache NiFi which can be leveraged to orchestrate a set of instruction. In this blog, I will provide inputs over how to automate a data pull strategy via chaining a set of API in Apache NiFI.</p><h4>For this blog please note the tools used:</h4><ul><li>Apache NiFi Version: 1.11.1</li><li>The OS under consideration: MacOS</li></ul><h3>What are we going to build?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kyfgDf2mEQ7YWupESXPpUA.png" /><figcaption>Something like this</figcaption></figure><p>The above diagram shows too many processors, but trust me, it’s basically these below steps:</p><ol><li>Hit a <strong>POST API to get a Token</strong> using a Login and Password</li><li><strong>Consume the token</strong> from step 1 and hit another <strong>POST API to get the Report URL</strong></li><li><strong>Consume the Report URL</strong> from step 2 and <strong>use the same token f</strong>rom step 1 to <strong>get the actual report downloaded via a GET API</strong></li><li>Upload the report to AWS S3</li></ol><h3>How are going to build this?</h3><p>So we need Apache’s <a href="https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.5.0/org.apache.nifi.processors.standard.InvokeHTTP/">InvokeHttp</a> to do the job for hitting the API via POST and GET request.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/728/1*gPsrVC4XvLxO6TTbi5_YBQ.png" /></figure><p><strong>Apache NiFi’s InvokeHttp Processor and it’s current usage</strong></p><p>Apache NiFi has a couple of processors to get API data from any given API provider. For instance, they already had GetHTTP, PostHTTP processors and then later introduced InvokeHttp which has support for Expression Language, support for <a href="https://nifi.apache.org/docs/nifi-docs/html/nifi-in-depth.html#intro:~:text=A%20FlowFile%20is%20a%20data%20record">incoming flowfile</a>. The InvokeHttp processor helps us to trigger a Post API and consume its response. This, however, isn’t straightforward and would require us to configure it right to ensure the post body or headers are passed to the same via a set of supporting processors.</p><p>In order to being, let’s start with step by step:</p><ol><li>Hit a <strong>POST API to get a Token</strong> using a Login and Password</li></ol><p>An InvokeHTTP for POST requires a Body/Headers to be passed via a flowfile and we can generate one using the following:</p><ul><li><strong>GenerateFlowFile</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hA5ZcJsvGgg0CEdofSlDBA.png" /><figcaption>GenerateFlowFile</figcaption></figure><p>This processor generates the required FlowFile (which is to store data for HTTP). The configurations here depict that its size is 1byte to start with and 1 batch size. Do note that the Scheduling tab has been changed to <strong>Run Schedule of 60 mins</strong>. Unless this, it would keep generating flowfile randomly.</p><ul><li><strong>UpdateAttribute</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_Hjnt_Xk6NIxCJbFo2ZQCQ.png" /></figure><p>Now the update attribute is used to add the attributes to the FlowFile. A flowfile has attribute and content. Attributes can be used for HTTP Headers and Contents as HTTP Body. The example here shows how we can add a new key-value pair and then in the next step make it part of flowfile’s content</p><ul><li><strong>AttributeToJSON</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*x4o6UdUbMTdt0hle_mT2bQ.png" /></figure><p>As the previous processor ends up adding it to FlowFile, we now use this processor firstly to CHOOSE the right set (i.e. via <strong>AttributesList</strong>) of attributes and make it part of the Post body via <strong>flowfile-content</strong>. Now, the Login, Password and so on can come into flowfile content.</p><ul><li><strong>InvokeHttp</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZBwxnMbtXreMfAHwCUL-jw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CtthJ5qCfmG8hWHNpKGahQ.png" /></figure><p>The previous flowfile contents are default sent as a Body to the HTTP and thus it would then result in a trigger of an API. The above-mentioned API is from an example of one of our data provider <a href="https://www.thetradedesk.com/us/our-platform/enterprise-apis">TheTradeDesk</a></p><p>2. <strong>Consume the token</strong> from step 1 and hit another <strong>POST API to get the Report URL</strong></p><p>Once I have the API triggered, this API would send the token as part of the response body. So we need to use consume the token as the next step:</p><ul><li><strong>EvaluateJSONPath</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eQOA36E74HPDzZwd4dZdtQ.png" /></figure><p>In order to consume the previous API’s output which is JSON Body, we need to use EvaluateJSONPath processor. The output was as follows with:</p><p>{ “Token”: “&lt;actual_token&gt;” }</p><p>So we ended up with $.Token</p><ul><li><strong>UpdateAttribute</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tu0N6Bx5rz-0Eapwqxb2gw.png" /></figure><p>Once we have the Token evaluated from the response body of the HTTP API, we then need to make it part of the FlowFile to feed it into the next API which requires us to download the file.</p><ul><li><strong>AttributeToJSON</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pGGbtOC9iLvhJJs96XEeqw.png" /></figure><p>To ensure the above token can be sent as an HTTP Header in InvokeHTTP, we should pass the above attributes as an HTTP <strong>header</strong> to the next post API. Thus choosing the Destination as <strong>flowfile-attribute </strong>as expected by <a href="https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.11.4/org.apache.nifi.processors.standard.InvokeHTTP/">InvokeHTTP</a>.</p><ul><li><strong>ReplaceText</strong></li></ul><p>Now comes the most important part for this API, we need to pass the JSON Body to the HTTP Post request as:</p><pre>{<br>  &quot;PartnerIds&quot;: [<br>    &quot;61ikbrz&quot;<br>  ],<br>  &quot;ReportExecutionStates&quot;: [<br>    &quot;Complete&quot;<br>  ],<br>  &quot;SortFields&quot;: [<br>    {<br>      &quot;FieldId&quot;: &quot;ReportStartDateInclusive&quot;,<br>      &quot;Ascending&quot;: &quot;false&quot;<br>    }<br>  ],<br>  &quot;PageStartIndex&quot;: 0,<br>  &quot;ReportScheduleNameContains&quot;: &quot;Report Name&quot;,<br>  &quot;PageSize&quot;: 2<br>}</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bOHeGFWQiAX_oRGypqp5Uw.png" /></figure><p>So ensured to search and replace all the data from existing flowfile&#39;s content body using, .* and then putting the Replacement Value as the above JSON Post Body. This will ensure to update the corresponding flowfile content which is used as a post request to the InvokeHTTP processor.</p><ul><li><strong>InvokeHTTP</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eTvA9-FEgEh6U05Z6Bka1Q.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*J9bG40NQZl0Bl4xVumBGiA.png" /></figure><p>Now do note the above screenshot, we have added the header information gathered from the flowfile’s attribute as TTD-Auth from the previous step of UpdateAttribute. This will ensure the HTTP API’s headers and post body is set as expected.</p><p>3. <strong>Consume the Report URL</strong> from step 2 and <strong>use the same token f</strong>rom step 1 to <strong>get the actual report downloaded via a GET API</strong></p><p>Once the above InvokeHTTP is successful, the mentioned API would return us a response with the file’s Download URL, which is basically an HTTP redirect based download.</p><ul><li><strong>EvaluateJsonPath</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RX_bO1_yOkh9V-Pbeo3Kfw.png" /></figure><p>If you notice carefully, the following processor helps to extract the download URL and add’s it to the <strong>flowfile-attribute </strong>which is the <strong>header of the HTTP </strong>request. The response is a nested JSON which would have the download URL for the file.</p><ul><li><strong>InvokeHTTP</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jGRTnVO99rmxqXOJ_s4_Xw.png" /></figure><p>We then use the ${DownloadURL} key from the previous step and then pass it to be downloaded in the remote URL property.</p><p>4. Upload the report to AWS S3</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sqE4JVMZHSfPDISchFzGjQ.png" /></figure><p>Lastly being downloaded to S3 of our choice.</p><h3>Concluding</h3><p>It is definitely a rollercoaster ride to build something so simple in Apache NiFi, but I am pretty sure a tool of its stature is definitely not for only such tasks and can be used for many other requirements and heavy lifting like S3 data transfer with huge volumes (running in cluster mode), or real-time data ingestion to Kafka and so on. I just wanted to provide an update on how to perform this in Apache NiFi, as for sure I struggled to initially get this automated.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=72c4d14c072d" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>