<?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 Harsh Kumar on Medium]]></title>
        <description><![CDATA[Stories by Harsh Kumar on Medium]]></description>
        <link>https://medium.com/@h369kr?source=rss-22e004d07de7------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*O_67c_vTA-fezF6d</url>
            <title>Stories by Harsh Kumar on Medium</title>
            <link>https://medium.com/@h369kr?source=rss-22e004d07de7------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 16:07:32 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@h369kr/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[PySpark Interview Series-V: Propagate Premium Flag Across Customer Purchases]]></title>
            <link>https://medium.com/@h369kr/pyspark-interview-series-v-propagate-premium-flag-across-customer-purchases-4c76f8a8d695?source=rss-22e004d07de7------2</link>
            <guid isPermaLink="false">https://medium.com/p/4c76f8a8d695</guid>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Harsh Kumar]]></dc:creator>
            <pubDate>Sun, 08 Jun 2025 07:42:15 GMT</pubDate>
            <atom:updated>2025-06-08T07:42:15.032Z</atom:updated>
            <content:encoded><![CDATA[<p>You are given a table named customer with the following columns:</p><p>cust_id (customer ID)</p><p>product_id (product they purchased)</p><p>is_premium (either &#39;Y&#39; or &#39;N&#39;)</p><blockquote><strong><em>Requirement:</em></strong><em><br> Return the same table (with the same columns), but if a customer </em><strong><em>bought at least one premium product</em></strong><em> (</em><em>is_premium = &#39;Y&#39;), </em><strong><em>then all</em></strong><em> of their transactions should be marked as </em><em>&#39;Y&#39; — meaning, once a customer buys premium, </em><strong><em>everything they buy becomes premium</em></strong><em>.</em></blockquote><pre>from pyspark.sql.types import StructType, StructField, IntegerType, StringType<br><br>schema = StructType([<br>    StructField(&quot;cust_id&quot;, IntegerType(), True),<br>    StructField(&quot;product_id&quot;, StringType(), True),<br>    StructField(&quot;is_premium&quot;, StringType(), True)<br>])<br><br>data = [<br>    (1, &#39;001&#39;, &#39;Y&#39;),<br>    (1, &#39;002&#39;, &#39;Y&#39;),<br>    (1, &#39;003&#39;, &#39;N&#39;),<br>    (2, &#39;002&#39;, &#39;N&#39;),<br>    (2, &#39;003&#39;, &#39;N&#39;),<br>    (2, &#39;004&#39;, &#39;N&#39;),<br>    (2, &#39;005&#39;, &#39;Y&#39;),<br>    (3, &#39;004&#39;, &#39;N&#39;),<br>    (3, &#39;003&#39;, &#39;N&#39;)<br>]<br><br>df = spark.createDataFrame(data, schema)<br><br>display(df)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/593/1*jt041hMraEm1QhQHJctK9w.png" /></figure><p>SQL Approach:</p><pre>-- Step 1: Identify customers who have purchased at least one premium product<br>WITH cte AS (<br>    SELECT cust_id<br>    FROM customer<br>    WHERE is_premium = &#39;Y&#39;<br>    GROUP BY cust_id<br>)<br><br>-- Step 2: Join the original table with the CTE to mark all transactions as premium<br>SELECT <br>    c.cust_id, <br>    c.product_id, <br>    CASE <br>        WHEN c1.cust_id IS NOT NULL THEN &#39;Y&#39;  -- If customer is in CTE, mark as premium<br>        ELSE &#39;N&#39;                              -- Otherwise, keep as non-premium<br>    END AS is_premium<br>FROM customer c<br>LEFT JOIN cte c1 ON c.cust_id = c1.cust_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/258/1*4ptCUjmKtyWD67yN9ORziA.png" /></figure><p>Pyspark Approach:</p><pre># Step 1: Get distinct customers who bought a premium product<br>premium_customers = df.filter(col(&quot;is_premium&quot;) == &#39;Y&#39;) \<br>                      .select(&quot;cust_id&quot;) \<br>                      .distinct() \<br>                      .withColumnRenamed(&quot;cust_id&quot;, &quot;premium_cust_id&quot;)<br><br># Step 2: Left join with original DataFrame on cust_id<br>df_joined = df.join(premium_customers, df.cust_id == col(&quot;premium_cust_id&quot;), how=&quot;left&quot;)<br><br># Step 3: Set is_premium to &#39;Y&#39; if premium_cust_id is not null, else &#39;N&#39;<br>df_final = df_joined.withColumn(<br>    &quot;is_premium&quot;,<br>    when(col(&quot;premium_cust_id&quot;).isNotNull(), lit(&quot;Y&quot;)).otherwise(lit(&quot;N&quot;))<br>).select(&quot;cust_id&quot;, &quot;product_id&quot;, &quot;is_premium&quot;)<br><br># Show the result<br>df_final.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/614/1*jrLjuNptoRVZYmLE9A7OWA.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4c76f8a8d695" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[PySpark Interview Series-IV: Total Grand Slam Titles Winner]]></title>
            <link>https://medium.com/@h369kr/pyspark-interview-series-iv-total-grand-slam-titles-winner-ef32809df1cf?source=rss-22e004d07de7------2</link>
            <guid isPermaLink="false">https://medium.com/p/ef32809df1cf</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[pyspark]]></category>
            <dc:creator><![CDATA[Harsh Kumar]]></dc:creator>
            <pubDate>Mon, 02 Jun 2025 08:24:19 GMT</pubDate>
            <atom:updated>2025-06-02T08:24:19.908Z</atom:updated>
            <content:encoded><![CDATA[<p>Ref: <a href="https://leetcode-cn.com/problems/grand-slam-titles">https://leetcode-cn.com/problems/grand-slam-titles</a></p><p>Write an SQL/pyspark query/script to report the number of grand slam tournaments won by each player. Do not include the players who did not win any tournament.</p><pre>data = [<br>    (1,&#39;Nadal&#39;),<br>    (2,&#39;Federer&#39;),<br>    (3,&#39;Novak&#39;)<br>]<br>schema = [&#39;id&#39;,&#39;name&#39;]<br><br>players = spark.createDataFrame(data, schema)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/339/1*kymW96jaRiorvN_clB-T6A.png" /></figure><pre>champ_data = [<br>    (2017,2,1,1,2),<br>    (2018,3,1,3,2),<br>    (2019,3,1,1,3)<br>]<br>champ_schema = [&#39;year&#39;,&#39;Wimbledon&#39;,&#39;Fr_open&#39;,&#39;US_open&#39;,&#39;Au_open&#39;]<br><br>champ = spark.createDataFrame(data=champ_data,schema=champ_schema)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/875/1*SPu4RrrJPZM0E-E2djNlRA.png" /></figure><p><strong>Output</strong>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/612/1*kO5t8BuSRyYyeFGJAvqq4A.png" /></figure><p><strong>SQL</strong> :</p><pre>with cte as<br>(select <br>year, wimbledon as championship from champ<br>union all<br>select <br>year, Fr_open as championship from champ<br>union all<br>select <br>year, US_open as championship from champ<br>union all<br>select <br>year, Au_open as championship from champ<br>)<br>,cte2 as(<br>select championship, count(1) as champ_count<br>from cte<br>group by championship<br>order by championship)<br><br>select id as player_id, name as player_name, champ_count as grand_slams_count<br>from players <br>join cte2 <br>on players.id = cte2.championship;</pre><p><strong>PySpark</strong>:</p><pre># union_df<br>champ_union = champ.select(&quot;year&quot;, col(&quot;wimbledon&quot;).alias(&quot;winner&quot;)) \<br>    .unionAll(<br>        champ.select(&quot;year&quot;, col(&quot;Fr_open&quot;).alias(&quot;winner&quot;))<br>    ).unionAll(<br>        champ.select(&quot;year&quot;, col(&quot;US_open&quot;).alias(&quot;winner&quot;))<br>    ).unionAll(<br>        champ.select(&quot;year&quot;, col(&quot;Au_open&quot;).alias(&quot;winner&quot;))<br>    )<br><br>#group<br>champ_group = champ_union.groupBy(&quot;winner&quot;) \<br>    .agg(<br>        count(lit(1)).alias(&quot;grand_slams_count&quot;)<br>    ) \<br>    .orderBy(&quot;winner&quot;)<br><br>#join<br>join_df = players.join(<br>    champ_group,<br>    on = players[&quot;id&quot;] == champ_group[&quot;winner&quot;],<br>    how = &quot;inner&quot;<br>)<br><br>#display<br>display(join_df.select(&quot;winner&quot;,&quot;name&quot;,&quot;grand_slams_count&quot;))</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/610/1*gGiH5AAkBhvoRyRg1Cnz9A.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ef32809df1cf" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[PySpark Interview Series-III: Number of Calls and Total Duration]]></title>
            <link>https://medium.com/@h369kr/pyspark-interview-series-iii-number-of-calls-and-total-duration-9492ae463e8b?source=rss-22e004d07de7------2</link>
            <guid isPermaLink="false">https://medium.com/p/9492ae463e8b</guid>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Harsh Kumar]]></dc:creator>
            <pubDate>Sun, 01 Jun 2025 10:34:50 GMT</pubDate>
            <atom:updated>2025-06-01T10:38:16.220Z</atom:updated>
            <content:encoded><![CDATA[<h4>Problem Statement</h4><p>You’re given a calls table with the following columns:</p><p>from_id: caller</p><p>to_id: receiver</p><p>duration: how long the call lasted (in seconds)</p><p>Each row represents one call between two users. However:</p><p>Calls are <strong>bidirectional</strong>, meaning (from_id = 10, to_id = 20) and (from_id = 20, to_id = 10) refer to the <strong>same pair of users</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/580/1*FJWtpOkLYODD0ZJjp0xRxg.png" /></figure><h4><strong>Goal:</strong></h4><p>You want to generate a report that shows:</p><p>Each <strong>unique pair of users</strong></p><p>The <strong>total number of calls</strong> between them</p><p>The <strong>total duration</strong> of all those calls</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/799/1*id3Dg1KUR9Uyth-tThoVbA.png" /></figure><pre>data =[<br>    (10,20,58),<br>    (20,10,12),<br>    (10,30,20),<br>    (30,40,100),<br>    (30,40,200),<br>    (30,40,200),<br>    (40,30,500)<br>]<br>call_df = spark.createDataFrame(data,schema=[&quot;from_id&quot;,&quot;to_id&quot;,&quot;duration&quot;]) <br>call_df.createOrReplaceTempView(&quot;calls&quot;)</pre><p><strong>Sql Approach:</strong></p><pre>WITH cte1 AS (<br>    SELECT <br>        CASE WHEN from_id &lt; to_id THEN from_id ELSE to_id END AS person1,<br>        CASE WHEN from_id &lt; to_id THEN to_id ELSE from_id END AS person2,<br>        duration<br>    FROM calls<br>)<br><br>SELECT <br>    person1, <br>    person2, <br>    COUNT(1) AS call_count, <br>    SUM(duration) AS total_duration<br>FROM cte1<br>GROUP BY person1, person2;</pre><p>Pyspark Approach:</p><pre>from pyspark.sql.functions import col, when, count, sum, lit<br><br># Step 1: Normalize caller-receiver pairs<br># Ensure that person1 is always the smaller ID and person2 is the larger one.<br># This makes (10, 20) and (20, 10) treated as the same pair.<br><br>call_df1 = call_df.withColumn(<br>    &quot;person1&quot;,when(col(&quot;from_id&quot;)&lt;col(&quot;to_id&quot;),col(&quot;from_id&quot;)).otherwise(col(&quot;to_id&quot;))<br>).withColumn(<br>    &quot;person2&quot;,when(col(&quot;from_id&quot;)&lt;col(&quot;to_id&quot;),col(&quot;to_id&quot;)).otherwise(col(&quot;from_id&quot;))<br>)<br><br># Step 2: Select only the normalized pair and duration for aggregation<br><br>call_df2 = call_df1.select(&quot;person1&quot;,&quot;person2&quot;,&quot;duration&quot;)<br><br># Step 3: Group by the normalized pair and calculate:<br># - total_calls: number of records (calls) per pair<br># - total_duration: total time spent on calls per pair<br><br>call_df3 = call_df2.groupBy(&quot;person1&quot;,&quot;person2&quot;).agg(<br>    count(lit(1)).alias(&quot;total_calls&quot;),<br>    sum(col(&quot;duration&quot;)).alias(&quot;total_duration&quot;)<br>)<br><br>display(call_df3)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/799/1*Y9ad99Bs1rGff5ITt_70OA.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9492ae463e8b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[PySpark Interview Series-II: Exchange seats]]></title>
            <link>https://medium.com/@h369kr/pyspark-interview-puzzle-ii-exchange-seats-efba716cfb16?source=rss-22e004d07de7------2</link>
            <guid isPermaLink="false">https://medium.com/p/efba716cfb16</guid>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Harsh Kumar]]></dc:creator>
            <pubDate>Sun, 01 Jun 2025 09:33:54 GMT</pubDate>
            <atom:updated>2025-06-01T10:23:29.139Z</atom:updated>
            <content:encoded><![CDATA[<p>Ref: <a href="https://leetcode.com/problems/exchange-seats/description/">https://leetcode.com/problems/exchange-seats/description/</a></p><p>Table: Seat</p><pre>+-------------+---------+<br>| Column Name | Type    |<br>+-------------+---------+<br>| id          | int     |<br>| student     | varchar |<br>+-------------+---------+<br>id is the primary key (unique value) column for this table.<br>Each row of this table indicates the name and the ID of a student.<br>The ID sequence always starts from 1 and increments continuously.</pre><p>Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.</p><p>Return the result table ordered by id <strong>in ascending order</strong>.</p><p>The result format is in the following example.</p><p><strong>Example 1:</strong></p><pre>Input: <br>Seat table:<br>+----+---------+<br>| id | student |<br>+----+---------+<br>| 1  | Alice   |<br>| 2  | Bob     |<br>| 3  | Charlie |<br>| 4  | David   |<br>| 5  | Eve     |<br>+----+---------+<br>Output: <br>+----+---------+<br>| id | student |<br>+----+---------+<br>| 1  | Bob     |<br>| 2  | Alice   |<br>| 3  | David   |<br>| 4  | Charlie |<br>| 5  | Eve     |<br>+----+---------+<br>Explanation: <br>Note that if the number of students is odd, there is no need to change the last one&#39;s seat.</pre><pre>data = [(1, &#39;Alice&#39;), (2, &#39;Bob&#39;), (3, &#39;Charlie&#39;), (4, &#39;David&#39;), (5, &#39;Eve&#39;)]<br>schema = [&#39;id&#39;,&#39;student&#39;]<br>std_df = spark.createDataFrame(data, schema)<br><br>std_df.createOrReplaceTempView(&quot;students&quot;)</pre><p><strong>SQL Approach</strong></p><pre>select id,<br>case <br>when id % 2 = 1 and id+1 &lt;= (select count(*) from students) then lead(student) over(order by id) <br>when id % 2 ==0 then lag(student) over(order by id)<br>else student<br>end as exc_stud<br>from students</pre><p>Why id+1 &lt;= (select count(*) from students)</p><p>id % 2 = 1: You&#39;re checking if the id is <strong>odd</strong>.</p><p>id + 1 &lt;= (select count(*) from students): This ensures there is a <strong>&quot;next&quot; row</strong> available in the dataset.</p><p>If both are true → Use the <strong>next student</strong> using LEAD().</p><p>If id + 1 &gt; total_rows, then there’s <strong>no next row</strong>, and LEAD(student) would return NULL. This condition avoids that.</p><p><strong>Pyspark Code:</strong></p><pre>count1 = std_df.count()<br>count1<br><br>std_df1 = std_df.withColumn(<br>    &quot;exc_stud&quot;,<br>    when(<br>        (col(&quot;id&quot;) % 2 == 1) &amp; ((col(&quot;id&quot;) + 1) &lt;= count1),<br>        lead(&quot;student&quot;).over(Window.orderBy(&quot;id&quot;))<br>    ).when(<br>        col(&quot;id&quot;) % 2 == 0,<br>        lag(&quot;student&quot;).over(Window.orderBy(&quot;id&quot;))<br>    ).otherwise(col(&quot;student&quot;))<br>)<br><br>std_df1.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/227/1*m1Kp-uJEb2iNwNTbAnhRsA.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=efba716cfb16" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[PySpark Interview Series-I: Grouping Consecutive Rows by Status]]></title>
            <link>https://medium.com/@h369kr/pyspark-interview-puzzle-i-grouping-consecutive-rows-by-status-bb17d415ec43?source=rss-22e004d07de7------2</link>
            <guid isPermaLink="false">https://medium.com/p/bb17d415ec43</guid>
            <dc:creator><![CDATA[Harsh Kumar]]></dc:creator>
            <pubDate>Sun, 01 Jun 2025 07:34:03 GMT</pubDate>
            <atom:updated>2025-06-01T11:10:05.969Z</atom:updated>
            <content:encoded><![CDATA[<p>You’re given a DataFrame containing a sequence of daily event statuses — either &quot;Won&quot; or &quot;Lost&quot;. Your task is to <strong>group consecutive rows with the same status</strong> and report the start and end dates for each such group.</p><pre>data = [<br>    (&quot;2020-06-01&quot;,&quot;Won&quot;),<br>    (&quot;2020-06-02&quot;,&quot;Won&quot;),<br>    (&quot;2020-06-03&quot;,&quot;Won&quot;),<br>    (&quot;2020-06-04&quot;,&quot;Lost&quot;),<br>    (&quot;2020-06-05&quot;,&quot;Lost&quot;),<br>    (&quot;2020-06-06&quot;,&quot;Lost&quot;),<br>    (&quot;2020-06-07&quot;,&quot;Won&quot;)<br>]<br>schema = [&quot;event_date&quot;,&quot;event_status&quot;]<br><br>df = spark.createDataFrame(data, schema)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/360/1*z-vajTwiUSmGR_eWz3MuEg.png" /><figcaption>Output</figcaption></figure><p>🔧 <strong>Solution 1: PySpark</strong></p><p>Step 1: Convert string to date type</p><pre>from pyspark.sql.functions import col, to_date<br>df = df.withColumn(&quot;event_date&quot;, to_date(col(&quot;event_date&quot;)))</pre><p>Step 2: Detect change in event status using lag()</p><pre>from pyspark.sql.window import Window<br>from pyspark.sql.functions import when, lag<br><br>window_spec = Window.orderBy(&quot;event_date&quot;)<br><br>df1 = df.withColumn(<br>    &quot;event_change&quot;, <br>    when(col(&quot;event_status&quot;) != lag(&quot;event_status&quot;).over(window_spec), 1).otherwise(0)<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/386/1*bLPBwMigjqHcFOwRb-xz5w.png" /><figcaption>Detect Change</figcaption></figure><p>Step 3: Generate event group ID using cumulative sum()</p><pre>from pyspark.sql.functions import sum<br><br>df2 = df1.withColumn(&quot;event_sum&quot;, sum(&quot;event_change&quot;).over(window_spec))</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/484/1*iA-YvA0B_b7x8p8rURE6xA.png" /></figure><p>Step 4: Aggregate start and end dates for each group</p><pre>from pyspark.sql.functions import first, last<br><br>df3 = df2.groupBy(&quot;event_status&quot;, &quot;event_sum&quot;) \<br>         .agg(<br>             first(&quot;event_date&quot;).alias(&quot;start_date&quot;),<br>             last(&quot;event_date&quot;).alias(&quot;end_date&quot;)<br>         )</pre><p>🧪 Final PySpark Output</p><pre>df3.select(&quot;event_status&quot;, &quot;start_date&quot;, &quot;end_date&quot;).show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/364/1*GTxFJ1x5TAluITahZvqDCg.png" /></figure><p><strong>🧠 Solution 2: Pure SQL in Databricks</strong></p><pre>df.createOrReplaceTempView(&#39;events&#39;)</pre><pre>WITH cte1 AS (<br>  SELECT <br>    event_status,<br>    event_date,<br>    CASE <br>      WHEN event_status != LAG(event_status) OVER(ORDER BY event_date) THEN 1 <br>      ELSE 0 <br>    END AS event_change<br>  FROM events<br>),<br>cte2 AS (<br>  SELECT *,<br>         SUM(event_change) OVER(ORDER BY event_date) AS event_bucket<br>  FROM cte1<br>)<br>SELECT <br>  event_status,<br>  FIRST(event_date) AS start_date,<br>  LAST(event_date) AS end_date<br>FROM cte2<br>GROUP BY event_status, event_bucket<br>ORDER BY event_bucket</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/626/1*qr535I57rCVM5VjPyD5FUA.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bb17d415ec43" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Understanding the PURGE Clause in the DROP Statement]]></title>
            <link>https://medium.com/@h369kr/understanding-the-purge-clause-in-the-drop-statement-7d76adeb2647?source=rss-22e004d07de7------2</link>
            <guid isPermaLink="false">https://medium.com/p/7d76adeb2647</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[oracle-database]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Harsh Kumar]]></dc:creator>
            <pubDate>Wed, 01 Jan 2025 08:41:14 GMT</pubDate>
            <atom:updated>2025-01-01T08:43:16.771Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*7vhT4eHHtVPKeGWY0DuvXA.jpeg" /></figure><p>When working with databases, the DROP statement is a common way to delete objects such as tables or views. However, in Oracle databases, the PURGE clause adds a powerful twist to how objects are handled during deletion.</p><p><strong>The Basics of </strong><strong>DROP in Oracle</strong></p><p>In Oracle, when you drop a table, it isn’t immediately removed from the database. Instead, it is moved to the <strong>Recycle Bin</strong>, where it can be recovered if needed. This feature provides a safety net against accidental deletions by allowing you to restore the object using a FLASHBACK command.</p><p>Here’s the standard syntax for dropping a table without PURGE:</p><pre>DROP TABLE table_name;</pre><p>This action moves the table to the Recycle Bin, ensuring that the data is not permanently lost.</p><p><strong>What Does </strong><strong>PURGE Do?</strong></p><p>The PURGE clause ensures that the object being dropped is permanently deleted, bypassing the Recycle Bin. Once a table is dropped with PURGE, it <strong>cannot be recovered</strong>, and the space it occupied is immediately freed.</p><p>Here’s the syntax with the PURGE clause:</p><pre>DROP TABLE table_name PURGE;</pre><p><strong>Key Differences Between </strong><strong>DROP and </strong><strong>DROP ... PURGE</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/889/1*W_gMUkqtfH0zLmgPcWpe1A.png" /></figure><p><strong>Why Use </strong><strong>PURGE?</strong></p><ul><li><strong>Irrecoverable Deletion</strong>: If you’re certain that the object is no longer required, PURGE ensures no traces are left.</li><li><strong>Immediate Space Reclamation</strong>: In cases where storage is a concern, PURGE directly releases the space without waiting for the Recycle Bin to clear.</li><li><strong>Data Sensitivity</strong>: For sensitive data, PURGE ensures it cannot be accidentally restored.</li></ul><p><strong>Examples</strong></p><ol><li>Dropping a Table Without PURGE</li></ol><pre>DROP TABLE employees;</pre><ul><li>The employees table moves to the Recycle Bin.</li><li>It can be recovered using:</li></ul><pre>FLASHBACK TABLE employees TO BEFORE DROP;</pre><p>2. Dropping a Table With PURGE</p><pre>DROP TABLE employees PURGE;</pre><ul><li>The employees table is permanently deleted.</li><li>Recovery is not possible.</li></ul><p><strong>Conclusion</strong></p><p>The PURGE clause in Oracle&#39;s DROP statement provides a mechanism for permanent deletion, bypassing the safety of the Recycle Bin. While it’s a powerful tool for efficient space management and handling sensitive data, it should be used cautiously as recovery is not an option.</p><p>When deciding whether to use PURGE, consider your use case carefully: Do you need the safety of the Recycle Bin, or are you certain that the object is no longer required?</p><p>By understanding the nuances of DROP and PURGE, you can manage your database objects more effectively and avoid unnecessary pitfalls.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7d76adeb2647" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Batch Convert IPYNB Files to Python in using Jupyter]]></title>
            <link>https://medium.com/@h369kr/batch-convert-ipynb-files-to-python-in-using-jupyter-451d910ea4b1?source=rss-22e004d07de7------2</link>
            <guid isPermaLink="false">https://medium.com/p/451d910ea4b1</guid>
            <category><![CDATA[cmd]]></category>
            <category><![CDATA[jupyter-notebook]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[bash]]></category>
            <category><![CDATA[ipynb]]></category>
            <dc:creator><![CDATA[Harsh Kumar]]></dc:creator>
            <pubDate>Fri, 26 Jan 2024 16:54:49 GMT</pubDate>
            <atom:updated>2024-02-26T11:19:57.592Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/560/0*yUjpHlY2QZCDRCWa.png" /></figure><p>If you find yourself with a collection of Jupyter notebooks (IPYNB files) on your Windows desktop and want to convert them all to Python files effortlessly, the jupyter nbconvert command can come to your rescue. In this guide, we&#39;ll walk through the process using a command prompt and even create a batch script for a more streamlined experience.</p><h3>Using the Command Prompt:</h3><ol><li>Open the command prompt (cmd) and navigate to the folder containing your IPYNB files using the ‘cd’ command:</li></ol><pre>cd path\to\your\folder</pre><p>Make sure Jupyter is installed and added to your system’s PATH variable.</p><p>2. Use the following command to convert all IPYNB files to Python:</p><pre>for %i in (*.ipynb) do jupyter nbconvert &quot;%i&quot; --to python</pre><ul><li>*For Linux:</li></ul><pre>for file in *.ipynb; do jupyter nbconvert &quot;$file&quot; --to python; done</pre><p>This concise command employs a `for` loop to iterate over each IPYNB file in the folder and executes the `jupyter nbconvert` command for conversion.</p><p>3. Press Enter, and watch as the conversion process unfolds for each IPYNB file in the designated folder.</p><p><strong>Batch Script for Effortless Conversion:</strong></p><p>If you prefer a more automated approach, you can create a batch script. Follow these steps:</p><ol><li>Open a text editor and write the following script:</li></ol><pre>@echo off<br>cd path\to\your\folder<br>for %%i in (*.ipynb) do jupyter nbconvert &quot;%%i&quot; --to python</pre><p>Replace `path\to\your\folder` with the actual path to your folder.</p><p>2. Save the file with a `.bat` extension (e.g., `convert_ipynb_to_python.bat`).</p><p>3. Double-click on the batch script to execute it. Witness the script effortlessly converting all IPYNB files in the specified folder to Python.</p><p>By incorporating this straightforward process into your workflow, you can efficiently convert multiple Jupyter notebooks to Python scripts, saving time and ensuring consistency in your projects.</p><p>Give it a try and enjoy the convenience of batch converting IPYNB files to Python on your Windows machine!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=451d910ea4b1" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Master Your DP-203 Certification Journey in 50 Days: A Comprehensive Guide]]></title>
            <link>https://medium.com/@h369kr/master-your-dp-203-certification-journey-in-50-days-a-comprehensive-guide-46ef2eb28613?source=rss-22e004d07de7------2</link>
            <guid isPermaLink="false">https://medium.com/p/46ef2eb28613</guid>
            <category><![CDATA[dp-203-exam-questions]]></category>
            <category><![CDATA[dp-203-exam-dumps]]></category>
            <category><![CDATA[dp-203-exam]]></category>
            <category><![CDATA[azure-data-engineer]]></category>
            <category><![CDATA[dp-203]]></category>
            <dc:creator><![CDATA[Harsh Kumar]]></dc:creator>
            <pubDate>Sun, 14 Jan 2024 23:20:21 GMT</pubDate>
            <atom:updated>2024-01-14T23:20:21.283Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*FN2vZMHnJIZNMV0v" /><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><strong>Welcome to Your DP-203 Certification Journey!</strong></p><p>Embarking on the DP-203 certification journey is an exciting endeavor that requires dedication and a well-structured plan. This 50-day guide is tailored to help you progress from a novice to an expert in Data Engineering on Microsoft Azure.</p><p><strong>Overview</strong></p><p>This meticulously crafted plan prescribes dedicating one hour each day to your DP-203 certification preparation. Follow the roadmap outlined below to lay a robust foundation, gain hands-on experience, and ace the certification exam.</p><p><strong>Day-wise Plan</strong></p><p><strong>Days 1–10</strong>: <em>Azure Fundamentals</em><br>Start with a solid understanding of Azure cloud concepts. Explore the learning paths available at Microsoft Learn’s [Azure Data Fundamentals](<a href="https://lnkd.in/gPw_bZqG">https://lnkd.in/gPw_bZqG</a>). This foundational knowledge is crucial for mastering data engineering on Azure.</p><p><strong>Days 11–30</strong>: <em>Azure Data Engineering</em><br>Progress to the Azure Data Engineering learning path at Microsoft Learn’s [DP-203 Exam Learning Paths](<a href="https://lnkd.in/g_NQY2gy">https://lnkd.in/g_NQY2gy</a>). Dive deeper into the intricacies of data engineering on Azure.</p><p><strong>Days 31–40</strong>: <em>Hands-On Practice</em><br>Immerse yourself in practical experience by utilizing the resources available on GitHub for DP-203 Azure Data Engineer. Engage with the labs to enhance your hands-on skills. [GitHub — DP-203 Azure Data Engineer Labs](<a href="https://lnkd.in/g7bXiExt">https://lnkd.in/g7bXiExt</a>).</p><p><strong>Days 41–45</strong>: <em>Study Notes and Resources</em><br>Tap into the collective wisdom of those who have successfully passed the exam. Access DP-203 notes and study materials from LinkedIn posts:<br>- [Azure Data Engineer DP-203 Notes by Sourav K](<a href="https://lnkd.in/gYxGbbic">https://lnkd.in/gYxGbbic</a>)<br>- [Azure DP-203 Roadmap by Ajay Kadiyala](<a href="https://lnkd.in/gPbRAvP2">https://lnkd.in/gPbRAvP2</a>)<br>- [Azure Data Engineer Study Note by Neil Bagchi](<a href="https://lnkd.in/gQZgTiBw">https://lnkd.in/gQZgTiBw</a>)</p><p><strong>Days 46–48</strong>: <em>Video Tutorials</em><br>For visual learners, explore the comprehensive [DP-203 Playlist on YouTube](<a href="https://lnkd.in/g29rKFGM">https://lnkd.in/g29rKFGM</a>) for in-depth video tutorials.</p><p><strong>Day 49</strong>: <em>Practice Exams</em><br>Test your knowledge with real exam questions at [ExamTopics for DP-203](<a href="https://lnkd.in/gGMBDJiY">https://lnkd.in/gGMBDJiY</a>). Practice exams are crucial before attempting the actual exam.</p><p><strong>Day 50</strong>: <em>Secret Tip</em><br>Motivate yourself by scheduling the exam before starting your dedicated preparation. This sets a clear deadline and enhances your focus.</p><p><strong>Bonus Tip: Looking for HeadStart</strong></p><p>Follow these steps to kickstart your DP-203 certification journey:</p><p>1. Clone this repository:<br> ```bash<br> git clone <a href="https://github.com/your-username/dp-203-certification-journey.git">https://github.com/your-username/dp-203-certification-journey.git</a><br> cd dp-203-certification-journey<br> ```</p><p>2. Enroll in this UDEMY course:<br> — [DP-203 — Data Engineering on Microsoft Azure](<a href="https://www.udemy.com/course/data-engineering-on-microsoft-azure/">https://www.udemy.com/course/data-engineering-on-microsoft-azure/</a>)</p><p><strong>Final Touch</strong></p><p>1. Access dumps for additional practice:<br> — [CertyIQ — DP-203 Exam Dumps](<a href="https://certyiq.com/papers?provider=microsoft&amp;exam=dp-203&amp;page=1">https://certyiq.com/papers?provider=microsoft&amp;exam=dp-203&amp;page=1</a>)</p><p>2. Enjoy a 50% discount by completing this challenge within 30 days:<br> — [Microsoft Learn Challenge](<a href="https://learn.microsoft.com/en-us/training/challenges?id=3d6cb3b9-f4b7-4d7b-ab23-57faa1762705&amp;wt.mc_id=cloudskillschallenge_3d6cb3b9-f4b7-4d7b-ab23-57faa1762705_30dtli_web_wwl">https://learn.microsoft.com/en-us/training/challenges?id=3d6cb3b9-f4b7-4d7b-ab23-57faa1762705&amp;wt.mc_id=cloudskillschallenge_3d6cb3b9-f4b7-4d7b-ab23-57faa1762705_30dtli_web_wwl</a>)</p><h4><strong><em>You’re Welcome!!</em></strong></h4><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=46ef2eb28613" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Google-managed encryption keys (KMS) and Customer-managed encryption keys (CMEK)]]></title>
            <link>https://medium.com/@h369kr/google-managed-encryption-keys-kms-and-customer-managed-encryption-keys-cmek-5d38f482fbd5?source=rss-22e004d07de7------2</link>
            <guid isPermaLink="false">https://medium.com/p/5d38f482fbd5</guid>
            <category><![CDATA[encryption]]></category>
            <category><![CDATA[gcp]]></category>
            <category><![CDATA[managed-services]]></category>
            <category><![CDATA[encryption-key]]></category>
            <dc:creator><![CDATA[Harsh Kumar]]></dc:creator>
            <pubDate>Sun, 31 Dec 2023 06:56:29 GMT</pubDate>
            <atom:updated>2023-12-31T06:56:29.525Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*B2ukuwWJ5izbmamc" /><figcaption>Photo by <a href="https://unsplash.com/@kommumikation?utm_source=medium&amp;utm_medium=referral">Mika Baumeister</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p><strong>Google-managed encryption keys (KMS)</strong>:</p><ul><li><strong>Definition</strong>: Encryption keys that are created, managed, and stored entirely by Google within its infrastructure.</li><li><strong>Control</strong>: Google has full control over key management operations, including creation, rotation, access, and deletion.</li><li><strong>User involvement</strong>: No setup or configuration required by users. Enabled by default for most GCP services that support encryption.</li><li><strong>Visibility</strong>: Users cannot directly view or manage these keys.</li><li><strong>Compliance</strong>: May not meet the strictest compliance requirements where full key control is mandated.</li></ul><p><strong>Customer-managed encryption keys (CMEK)</strong>:</p><ul><li><strong>Definition</strong>: Encryption keys that are created and managed by customers using Google Cloud Key Management Service (Cloud KMS).</li><li><strong>Control</strong>: Customers have full control over key management, including creation, rotation, access, storage location, and deletion.</li><li><strong>User involvement</strong>: Requires setup and configuration within Cloud KMS.</li><li><strong>Visibility</strong>: Customers have full visibility and control over their keys.</li><li><strong>Compliance</strong>: Can meet stricter compliance requirements due to customer control over keys.</li></ul><p>Here’s a comprehensive comparison of Google-managed encryption keys (KMS) and customer-managed encryption keys (CMEK) within Google Cloud Platform (GCP):</p><p><strong>Key Management:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/764/1*I2hRj67lkfPhLZiJiIyNeg.png" /></figure><p><strong>Control and Compliance:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/771/1*sZzIvYkUXGHT5kRFld3tgA.png" /></figure><p><strong>Ease of Use and Cost:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/765/1*1rFqJgog1e4fcG6CeEsajg.png" /></figure><p><strong>Best Practices for Choosing:</strong></p><p>Use <strong>Google-managed keys (KMS):</strong></p><ul><li>When you need a simple and cost-effective encryption solution.</li><li>When you trust Google to manage your keys securely.</li><li>When you don’t have strict compliance requirements that mandate full control over keys.</li></ul><p>Use <strong>customer-managed keys (CMEK):</strong></p><ul><li>When you need more control over key management and access.</li><li>When you have strict compliance requirements that necessitate full key control.</li><li>When you need to meet data residency or locality requirements.</li><li>When you want to restrict Google’s access to your data.</li></ul><p><strong>Additional Considerations:</strong></p><ul><li>CMEK is not available for all GCP services. Check the list of supported services.</li><li>Using CMEK can add complexity to your key management processes.</li><li>It’s essential to have a robust key management strategy in place, regardless of the key type you choose.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5d38f482fbd5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Cache vs. persist in Spark: Understanding the distinctions]]></title>
            <link>https://medium.com/@h369kr/cache-vs-persist-in-spark-understanding-the-distinctions-1ee7d3e74559?source=rss-22e004d07de7------2</link>
            <guid isPermaLink="false">https://medium.com/p/1ee7d3e74559</guid>
            <category><![CDATA[cache]]></category>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[memory-management]]></category>
            <category><![CDATA[persistent-storage]]></category>
            <dc:creator><![CDATA[Harsh Kumar]]></dc:creator>
            <pubDate>Thu, 21 Dec 2023 09:47:47 GMT</pubDate>
            <atom:updated>2023-12-21T09:47:47.151Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Zc7aSu89G-wpt3-U" /><figcaption>Photo by <a href="https://unsplash.com/@samsungmemory?utm_source=medium&amp;utm_medium=referral">Samsung Memory</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>While persist() and cache() both serve to store data in memory for reuse, they offer distinct features and purposes within Spark:</p><p>1. Default Storage Level:</p><ul><li>cache() is a convenience method that uses the default storage level MEMORY_AND_DISK. This means Spark first tries to store data in memory, but if memory is insufficient, it spills partitions to disk to avoid out-of-memory errors.</li><li>persist() allows you to specify a custom storage level, providing more control over how data is stored. This flexibility is crucial for fine-tuning performance based on your application&#39;s needs and memory constraints.</li></ul><p>2. Explicitness and Readability:</p><ul><li>cache() is a commonly used term for caching data, making code more explicit and readable when that&#39;s the intended behavior.</li><li>persist(), with its ability to specify storage levels, is preferred when you need precise control over persistence strategies.</li></ul><p>3. Backward Compatibility:</p><ul><li>cache() was introduced in earlier Spark versions, making it a familiar method for developers who have been using Spark for a longer time.</li><li>persist() was added later, offering more flexibility but maintaining backward compatibility with cache().</li></ul><p>In summary:</p><ul><li>Use cache() for a convenient way to cache data with a balanced memory-disk approach, often suitable for common use cases.</li><li>Use persist() when you need fine-grained control over storage levels, such as:</li><li>MEMORY_ONLY: Keep only in memory for fast access, but risk OOM errors.</li><li>MEMORY_ONLY_SER: Serialize data for more compact storage in memory.</li><li>MEMORY_AND_DISK_SER: Spill to disk if memory is insufficient, with serialization.</li><li>DISK_ONLY: Store data directly on disk, suitable for large datasets or limited memory.</li></ul><p>Choose the appropriate method based on your application’s requirements and the trade-offs between performance, memory usage, and fault tolerance.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1ee7d3e74559" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>