<?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 sathish kumar srinivasan on Medium]]></title>
        <description><![CDATA[Stories by sathish kumar srinivasan on Medium]]></description>
        <link>https://medium.com/@sathishdba?source=rss-369246c21caf------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*dmbNkD5D-u45r44go_cf0g.png</url>
            <title>Stories by sathish kumar srinivasan on Medium</title>
            <link>https://medium.com/@sathishdba?source=rss-369246c21caf------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 00:46:50 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@sathishdba/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[Pandas 3 and the Dedicated String Data Type by Default]]></title>
            <link>https://medium.com/@sathishdba/pandas-3-and-the-dedicated-string-data-type-by-default-1a4adba0eb7b?source=rss-369246c21caf------2</link>
            <guid isPermaLink="false">https://medium.com/p/1a4adba0eb7b</guid>
            <dc:creator><![CDATA[sathish kumar srinivasan]]></dc:creator>
            <pubDate>Mon, 02 Mar 2026 08:49:20 GMT</pubDate>
            <atom:updated>2026-03-02T08:49:20.240Z</atom:updated>
            <content:encoded><![CDATA[<p><em>A Deep Dive into What Changed, Why It Matters, and How It Improves Your Code</em></p><p>With the release of <strong>Pandas 3</strong>, one of the most impactful and long-anticipated changes is the shift to a <strong>dedicated string data type (</strong><strong>str) as the default for text data</strong>, replacing the long-standing use of the generic object dtype.</p><p>At first glance, this might seem like a small internal adjustment. In reality, it represents a meaningful architectural improvement that enhances:</p><p>✅ Type safety</p><p>✅ Consistency</p><p>✅ Interoperability with Apache Arrow</p><p>✅ Performance (when PyArrow is installed)</p><p>✅ Memory efficiency (when PyArrow is installed)</p><p>✅ Predictability in string operations</p><p>In this article, we’ll explore:</p><ol><li>Why object dtype was problematic</li><li>What the new str dtype brings</li><li>How Pandas 3 changes behavior by default</li><li>Real-world code examples</li><li>Performance and memory improvements</li><li>Arrow-backed string arrays</li><li>Migration tips</li></ol><p>This guide assumes you’re comfortable with Pandas basics.</p><h3>1. The Problem with object dtype</h3><p>For years, Pandas stored text data using:</p><pre>dtype: object</pre><p>Example in Pandas 2.x:</p><pre>import pandas as pd<br><br>df = pd.DataFrame({<br>    &quot;name&quot;: [&quot;Alice&quot;, &quot;Bob&quot;, &quot;Charlie&quot;]<br>})<br><br>print(df.dtypes)</pre><p>Output:</p><pre>name    object<br>dtype: object</pre><p>Why is this a problem?</p><p>Because object is not a string type.</p><p>It is a generic container for arbitrary Python objects. That means a column with dtype object could contain:</p><ul><li>Strings</li><li>Integers</li><li>Floats</li><li>Lists</li><li>Mixed types</li></ul><p>Example:</p><pre>df = pd.DataFrame({<br>    &quot;data&quot;: [&quot;Alice&quot;, 42, None, 3.14]<br>})<br><br>print(df.dtypes)</pre><p>Still:</p><pre>data    object<br>dtype: object</pre><p>This creates serious problems for data pipelines:</p><ul><li><strong>Ambiguous type information</strong>: You can’t tell from the dtype whether a column is actually strings or mixed objects</li><li><strong>Silent type inconsistencies</strong>: No warnings when non-string values slip into text columns</li><li><strong>Unpredictable behavior</strong>: String operations may fail or behave unexpectedly with mixed types</li><li><strong>Harder debugging</strong>: When operations fail downstream, the root cause is hard to trace</li><li><strong>Less efficient</strong>: Python object containers don’t allow for optimized array operations</li></ul><p>These issues are particularly problematic in production pipelines where a single mixed-type column can cause cascading failures downstream.</p><h3>2. Enter the Dedicated str dtype</h3><p>In Pandas 3, <strong>string columns are inferred as </strong><strong>str dtype by default</strong>.</p><p>Now:</p><pre>df = pd.DataFrame({<br>    &quot;name&quot;: [&quot;Alice&quot;, &quot;Bob&quot;, &quot;Charlie&quot;]<br>})<br><br>print(df.dtypes)</pre><p>Output:</p><pre>name    str<br>dtype: object</pre><p>The new str dtype is:</p><ul><li><strong>Backed by PyArrow</strong> if PyArrow is installed (for better performance and memory efficiency)</li><li><strong>Falls back to NumPy object-dtype</strong> if PyArrow is not installed (maintains compatibility)</li><li><strong>Enforces string-only storage</strong> (attempting to set non-string values raises an error)</li><li><strong>Uses </strong><strong>np.nan as the missing value sentinel</strong>, following the same missing value semantics as other default dtypes</li></ul><p>This small difference changes behavior throughout your code, but deliberately in a conservative way to minimize migration friction.</p><h3>3. What Makes the New str Type Better?</h3><h4>✅ 1. True Type Safety</h4><p>With object, you could mix types accidentally.</p><p>With str, Pandas enforces string-only data:</p><pre>s = pd.Series([&quot;Alice&quot;, &quot;Bob&quot;], dtype=&quot;str&quot;)<br><br>s[1] = 42   # Raises an error</pre><p>Error:</p><pre>TypeError: Cannot set non-string value into string array</pre><p>You now get type consistency.</p><p>This eliminates subtle bugs where non-string values slip into text columns. In production systems, catching these errors early prevents data corruption and unexpected failures.</p><h4>✅ 2. Consistent Missing Value Handling</h4><p>With object, missing values could be represented as:</p><ul><li>None</li><li>np.nan</li><li>pd.NA</li></ul><p>This inconsistency was confusing and error-prone.</p><p>With the new str dtype:</p><ul><li>Missing values are always np.nan</li></ul><p>Example:</p><pre>import pandas as pd<br><br>s = pd.Series([&quot;Alice&quot;, None], dtype=&quot;str&quot;)<br>print(s)<br>print(s.isna())</pre><p>Output:</p><pre>0    Alice<br>1      NaN<br>dtype: str<br><br>0    False<br>1     True<br>dtype: bool</pre><p>The use of np.nan is deliberate. By following the same missing value semantics as other default dtypes (lowercase int64, float64, bool), Pandas 3 minimizes migration friction. Existing code that checks for np.nan continues to work as expected.</p><p><strong>Important distinction</strong>: This is different from the older StringDtype (accessed via dtype=&quot;string&quot;), which uses pd.NA. The new default str dtype uses np.nan for better backward compatibility.</p><h4>✅ 3. More Reliable String Operations</h4><p>String methods work more reliably because type safety prevents problematic data from entering the column in the first place.</p><p>Old behavior (object):</p><pre>s = pd.Series([&quot;Alice&quot;, None, 42, &quot;Bob&quot;])<br>print(s.str.upper())</pre><p>This could behave unexpectedly or raise errors because the column contains mixed types.</p><p>With str dtype:</p><pre>s = pd.Series([&quot;Alice&quot;, None, &quot;Bob&quot;], dtype=&quot;str&quot;)<br>print(s.str.upper())</pre><p>Output:</p><pre>0    ALICE<br>1      NaN<br>2      BOB<br>dtype: str</pre><p>String operations are more predictable not because the operations themselves changed, but because the input data is now guaranteed to be clean — all values are either strings or np.nan. The release notes indicate the new dtype &quot;mostly behaves as NumPy object would,&quot; so the fundamental behavior is similar to before, but now protected by type safety.</p><h3>4. More Predictable Behavior in Comparisons</h3><p>Old behavior with object dtype:</p><pre>s = pd.Series([&quot;1&quot;, &quot;2&quot;, &quot;3&quot;])<br>print(s == 2)</pre><p>With object, unpredictable type coercions could occur.</p><p>With str dtype:</p><pre>s = pd.Series([&quot;1&quot;, &quot;2&quot;, &quot;3&quot;], dtype=&quot;str&quot;)<br>print(s == 2)</pre><p>Expected output:</p><pre>0    False<br>1    False<br>2    False<br>dtype: bool</pre><p>The new str dtype should produce consistent cross-type comparisons that treat string &quot;2&quot; as distinct from integer 2. However, since the release notes indicate the dtype &quot;mostly behaves as NumPy object would,&quot; verify this behavior in your environment before relying on it for critical logic, as edge cases may exist.</p><h3>5. CSV Reading Improvements in Pandas 3</h3><p>Before Pandas 3:</p><pre>df = pd.read_csv(&quot;data.csv&quot;)<br>print(df.dtypes)</pre><p>Text columns → object</p><p>Now in Pandas 3:</p><pre>df = pd.read_csv(&quot;data.csv&quot;)<br>print(df.dtypes)</pre><p>Text columns → str</p><p>The dtype now clearly indicates these are string columns, not mixed objects. This makes your code more self-documenting and eliminates the need for manual type specification in many cases.</p><h3>6. Memory Efficiency and Performance (When PyArrow Is Installed)</h3><p>The str dtype benefits significantly from Arrow backing when PyArrow is available.</p><h3>How It Works</h3><p>When PyArrow is installed:</p><pre>import pandas as pd<br><br>data = [&quot;text_&quot; + str(i) for i in range(1_000_000)]<br>s = pd.Series(data, dtype=&quot;str&quot;)<br><br>print(s.dtype)<br>print(f&quot;Memory usage: {s.memory_usage(deep=True) / 1024**2:.2f} MB&quot;)</pre><p>Output:</p><pre>str<br>Memory usage: 28.34 MB</pre><p>(Note: The specific memory value shown is illustrative. Your actual results will depend on string lengths, system configuration, and PyArrow version.)</p><p>When PyArrow is <strong>not</strong> installed:</p><pre># Falls back to NumPy object-dtype<br>print(f&quot;Memory usage: {s.memory_usage(deep=True) / 1024**2:.2f} MB&quot;)</pre><p>Output (approximate):</p><pre>Memory usage: 45.78 MB</pre><p>With PyArrow backing, you typically see meaningful memory savings. The Arrow backend provides:</p><p>✅ Faster string operations (SIMD optimizations)</p><p>✅ Better compression for storage</p><p>✅ Improved interoperability with other Arrow-based tools</p><p>✅ More efficient memory usage compared to object dtype</p><h3>Performance Comparison Example</h3><p>Let’s benchmark a simple string operation with PyArrow installed:</p><pre>import time<br><br># Object dtype<br>s_obj = pd.Series(data, dtype=&quot;object&quot;)<br><br>start = time.time()<br>result_obj = s_obj.str.contains(&quot;9999&quot;)<br>time_obj = time.time() - start<br><br># New str dtype (backed by PyArrow)<br>s_str = pd.Series(data, dtype=&quot;str&quot;)<br><br>start = time.time()<br>result_str = s_str.str.contains(&quot;9999&quot;)<br>time_str = time.time() - start<br><br>print(f&quot;Object dtype:  {time_obj:.4f}s&quot;)<br>print(f&quot;str dtype:     {time_str:.4f}s&quot;)<br>if time_obj &gt; 0:<br>    print(f&quot;Speedup:       {time_obj / time_str:.1f}x&quot;)</pre><p>Typical output (with PyArrow, illustrative):</p><pre>Object dtype:  0.3421s<br>str dtype:     0.0642s<br>Speedup:       5.3x</pre><p>(These are approximate values for illustration. Your results will vary based on data size, system specs, and PyArrow version. Without PyArrow installed, performance improvements will be minimal since the dtype falls back to NumPy object-dtype.)</p><h3>7. Arrow-Backed String Arrays</h3><p>The new str dtype automatically uses Apache Arrow as its backend when PyArrow is installed. This provides seamless benefits without requiring code changes.</p><p>You can also explicitly specify the older StringDtype for different behavior:</p><pre># New default str dtype (uses np.nan for missing values)<br>s1 = pd.Series([&quot;Alice&quot;, None], dtype=&quot;str&quot;)<br><br># Older StringDtype (uses pd.NA for missing values)<br>s2 = pd.Series([&quot;Alice&quot;, None], dtype=&quot;string&quot;)</pre><p><strong>When to use which:</strong></p><ul><li>str (default): Use this for most cases. It&#39;s the new default, has Arrow optimization when available, and maintains backward compatibility with existing code that expects np.nan.</li><li>string: Use the older StringDtype if you specifically need pd.NA missing values or are integrating with the nullable extension type ecosystem (Int64, Float64, boolean).</li></ul><h3>8. GroupBy and Aggregation Improvements</h3><p>Grouping on string columns now works more reliably with clear dtype information.</p><pre>df = pd.DataFrame({<br>    &quot;city&quot;: pd.Series([&quot;NY&quot;, &quot;LA&quot;, &quot;NY&quot;, None], dtype=&quot;str&quot;),<br>    &quot;value&quot;: [10, 20, 30, 40]<br>})<br><br>print(df.groupby(&quot;city&quot;, dropna=False).sum())</pre><p>Missing values are handled consistently as np.nan, making grouping behavior predictable and familiar to users of other default dtypes.</p><h3>9. Cleaner Type Detection</h3><p>Before:</p><pre>df.select_dtypes(include=[&quot;object&quot;])</pre><p>Would include strings, mixed types, and other objects indiscriminately.</p><p>Now, you can filter for string columns. The exact syntax for select_dtypes() may vary depending on how Pandas 3 exposes the str dtype as a selector. Test the following in your environment:</p><pre># Try one of these approaches<br>df.select_dtypes(include=[&quot;str&quot;])<br># or<br>df.select_dtypes(include=[str])<br># or use a more explicit helper<br>df.select_dtypes(include=[pd.StringDtype()])</pre><p>The goal is to identify actual string columns more explicitly than the catch-all object selector.</p><h3>10. Migration Tips</h3><p>If you’re upgrading to Pandas 3:</p><h4>✅ 1. Understand the new str dtype behavior</h4><p>The new str dtype uses np.nan for missing values, not pd.NA. This follows the same pattern as other default dtypes. Note that the nullable extension type Int64 (capitalized) behaves differently:</p><pre># Default dtypes use np.nan for missing values<br>df[&quot;strings&quot;] = pd.Series([&quot;a&quot;, None], dtype=&quot;str&quot;)        # np.nan<br>df[&quot;floats&quot;] = pd.Series([1.0, None], dtype=&quot;float64&quot;)     # np.nan<br><br># Nullable extension types use pd.NA for missing values<br>df[&quot;ints&quot;] = pd.Series([1, None], dtype=&quot;Int64&quot;)           # pd.NA<br>df[&quot;bools&quot;] = pd.Series([True, None], dtype=&quot;boolean&quot;)     # pd.NA</pre><p>Understanding this distinction helps you choose the right dtype for your use case.</p><h4>✅ 2. Check dtype assumptions in your code</h4><p>Some older code may check:</p><pre>if df[&quot;col&quot;].dtype == &quot;object&quot;:</pre><p>Update to:</p><pre>if pd.api.types.is_string_dtype(df[&quot;col&quot;]):</pre><p>This is more future-proof and semantically correct.</p><h4>✅ 3. Install PyArrow for performance benefits</h4><p>To get the full performance and memory benefits of the new str dtype:</p><pre>pip install pyarrow</pre><p>Without PyArrow, the str dtype falls back to NumPy object-dtype, so you won&#39;t see significant performance improvements.</p><h4>✅ 4. Handle pickled DataFrames from Pandas 2.x — IMPORTANT CAVEAT</h4><p>If you’re loading pickled DataFrames from Pandas 2.x and need to convert object columns to the new str dtype, be very careful with the migration.</p><p>The naive approach can cause data corruption:</p><pre>df = pd.read_pickle(&quot;old_data.pkl&quot;)<br><br># ⚠️ CAUTION: This may corrupt NaN values!<br># In older Pandas, .astype(str) calls Python&#39;s str() on each element,<br># converting NaN to the literal string &quot;nan&quot;<br>string_cols = df.select_dtypes(include=[&quot;object&quot;]).columns<br>df[string_cols] = df[string_cols].astype(&quot;str&quot;)  # RISKY</pre><p>Before conversion, verify that .astype(&quot;str&quot;) in Pandas 3 handles missing values correctly (converting them to np.nan rather than the string &quot;nan&quot;). <strong>Test this thoroughly in a non-production environment first.</strong></p><p>A safer approach is to handle nulls explicitly:</p><pre>df = pd.read_pickle(&quot;old_data.pkl&quot;)<br>string_cols = df.select_dtypes(include=[&quot;object&quot;]).columns<br><br># Safer: explicitly preserve null handling<br>for col in string_cols:<br>    # Only convert non-null values, preserve nulls<br>    df[col] = df[col].apply(lambda x: x if pd.isna(x) else str(x), meta=(&#39;object&#39;, &#39;object&#39;))<br>    df[col] = df[col].astype(&quot;str&quot;)</pre><p>Or, if you’re confident the data is already string-only, you can use:</p><pre># If the object column contains only strings (no mixed types)<br>df[string_cols] = df[string_cols].astype(&quot;str&quot;)</pre><p>But verify this assumption first. When in doubt, inspect a sample:</p><pre># Before converting, check what&#39;s actually in the column<br>print(df[string_cols].dtypes)<br>print(df[string_cols].head(10))<br>print(df[string_cols].apply(lambda x: type(x[0]) if len(x) &gt; 0 else None))</pre><h4>✅ 5. Distinguish between str and string dtype</h4><p>The old StringDtype (dtype=&quot;string&quot;) is still available and uses pd.NA:</p><pre># Old StringDtype - uses pd.NA, part of nullable ecosystem<br>s_string = pd.Series([&quot;a&quot;, None], dtype=&quot;string&quot;)<br>print(s_string[1])  # &lt;NA&gt;<br><br># New str dtype - uses np.nan, part of default dtype family<br>s_str = pd.Series([&quot;a&quot;, None], dtype=&quot;str&quot;)<br>print(s_str[1])  # nan</pre><p>For most cases, the new str dtype is the better choice. Use string only if you specifically need nullable extension type semantics.</p><h4>✅ 6. When to Keep object dtype</h4><p>Not every use case benefits from the new str dtype. Use object when you have:</p><ul><li>Truly heterogeneous columns (mixing strings, integers, objects)</li><li>Complex Python objects (lists, dicts, custom classes) stored in a column</li><li>Need for maximum backward compatibility with very old code</li></ul><p>For normal text data, str is the superior choice.</p><h3>11. Why This Change Is a Big Deal</h3><p>This shift represents a meaningful modernization:</p><ul><li><strong>Eliminates ambiguity</strong>: You now know when a column contains strings, not just “some Python objects”</li><li><strong>Improves type safety</strong>: Prevents accidental mixing of types in string columns</li><li><strong>Enables optimization</strong>: Arrow backing allows performant operations when available</li><li><strong>Maintains compatibility</strong>: By using np.nan instead of introducing pd.NA, existing code continues to work</li><li><strong>Future-proofs your code</strong>: Arrow integration opens doors to interoperability with DuckDB, Polars, and other modern data tools</li></ul><p>It reduces one of the biggest sources of confusion in Pandas: the inability to distinguish between “a column that happens to contain Python objects” and “a column of strings.”</p><h3>Final Thoughts</h3><p>The move to a dedicated str dtype as the default in Pandas 3 is a pragmatic improvement, not a revolutionary overhaul. It:</p><ul><li>Clarifies intent (string columns are now explicitly typed as strings)</li><li>Improves type safety (prevents accidental type mixing)</li><li>Enables performance gains when PyArrow is available</li><li>Maintains backward compatibility through the use of np.nan</li><li>Positions Pandas for better integration with the Arrow ecosystem</li></ul><p>For data engineers, analysts, and ML practitioners, this change makes your data pipelines clearer and more robust — without requiring significant code changes.</p><p>If you’re still thinking of strings as just “object columns,” it’s time to update that mental model.</p><p>In Pandas 3, strings are explicitly typed citizens.</p><p>And that’s a meaningful step forward.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1a4adba0eb7b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Pandas 3.0’s Biggest Shift: Copy-on-Write Becomes the Only Mode]]></title>
            <link>https://medium.com/@sathishdba/pandas-3-0s-biggest-shift-copy-on-write-becomes-the-only-mode-87a1870a144f?source=rss-369246c21caf------2</link>
            <guid isPermaLink="false">https://medium.com/p/87a1870a144f</guid>
            <dc:creator><![CDATA[sathish kumar srinivasan]]></dc:creator>
            <pubDate>Mon, 02 Mar 2026 07:27:31 GMT</pubDate>
            <atom:updated>2026-03-02T07:27:31.777Z</atom:updated>
            <content:encoded><![CDATA[<p>With <strong>pandas 3.0</strong>, one of the library’s most significant architectural changes becomes official: <strong>Copy-on-Write (CoW) is now the only mode of operation</strong>.</p><p>This is not a minor implementation detail — it fundamentally changes how pandas handles slicing, assignment, and mutation. For most users, this means safer code with fewer surprises.</p><p>For years, pandas users had to live with one of the library’s most frustrating behaviors: sometimes an operation returned a view, sometimes it returned a copy, and the difference was not always obvious. That uncertainty led to warnings, subtle bugs, and defensive programming patterns that made code harder to reason about.</p><p>Pandas 3.0 simplifies that model dramatically.</p><p>The new rule is clear:</p><blockquote><strong><em>Any DataFrame or Series produced by indexing behaves as if it were a copy. Modifying it will not modify its parent.</em></strong></blockquote><p>Internally, pandas may still share memory for performance. But when a write occurs, it creates a private copy as needed to preserve that copy-like behavior.</p><p>The key shift is not about memory mechanics.<br> It’s about a stronger, clearer API contract.</p><h3>1. The Old Problem: Views vs Copies Were Hard to Reason About</h3><p>In older pandas versions, slicing a DataFrame could return either:</p><ul><li>A view of the original data</li><li>A copy of the data</li></ul><p>The problem wasn’t that both concepts existed. The problem was that users often couldn’t reliably determine which one they had.</p><h4>Example: Pre-3.0 Ambiguity</h4><pre>import pandas as pd<br><br>df = pd.DataFrame({&quot;A&quot;: [1, 2, 3], &quot;B&quot;: [4, 5, 6]})<br>subset = df[df[&quot;A&quot;] &gt; 1]<br>subset[&quot;B&quot;] = 0</pre><p>In older pandas versions:</p><ul><li>You might get a SettingWithCopyWarning</li><li>The assignment might or might not affect df</li><li>Behavior could depend on internal optimization details</li></ul><p>This unpredictability made pandas harder to trust in large codebases. A line that looked harmless could introduce subtle bugs in ETL pipelines, analytics workflows, or feature engineering.</p><h3>2. What Copy-on-Write Actually Means</h3><p>Under Copy-on-Write, pandas establishes a much clearer rule:</p><blockquote><em>Any DataFrame or Series produced by indexing behaves as if it were a copy of the original in the user-facing API.</em></blockquote><p>That means:</p><ul><li>Modifying the derived object will <strong>not</strong> modify its parent</li><li>Mutation is isolated</li><li>Behavior is deterministic</li></ul><p>Internally, pandas may share memory for efficiency. This happens invisibly — users never need to think about it. The API contract is what matters.</p><p>When a write occurs, it creates a private copy if needed to preserve that independent behavior.</p><p>The mental model becomes:</p><blockquote><strong><em>Derived objects behave like independent copies. Writes trigger isolation when necessary.</em></strong></blockquote><p>You no longer need to reason about “view vs copy.” That distinction is no longer part of the public API model.</p><h3>3. How Behavior Changes in Pandas 3.0</h3><p>Let’s look at common patterns.</p><h3>A. Modifying a Slice</h3><pre>df = pd.DataFrame({&quot;A&quot;: [1, 2, 3], &quot;B&quot;: [4, 5, 6]})<br>subset = df[df[&quot;A&quot;] &gt; 1]<br><br>subset[&quot;B&quot;] = 0</pre><h4>In pandas 3.0:</h4><ul><li>subset behaves as an independent object</li><li>Modifying subset does <strong>not</strong> modify df</li><li>No ambiguity</li><li>No warning</li></ul><h4>Result</h4><pre>df<br>#    A  B<br># 0  1  4<br># 1  2  5<br># 2  3  6<br><br>subset<br>#    A  B<br># 1  2  0<br># 2  3  0</pre><p>This eliminates one of the most persistent sources of confusion in earlier versions.</p><p>✅ No accidental mutation<br>✅ No warnings<br>✅ Fully predictable behavior</p><h3>B. Chained Assignment</h3><p>Chained assignment has historically been one of the biggest sources of confusion in pandas:</p><pre>df[df[&quot;A&quot;] &gt; 1][&quot;B&quot;] = 0</pre><p>In older versions:</p><ul><li>Sometimes worked</li><li>Sometimes failed</li><li>Often raised SettingWithCopyWarning</li></ul><h4>In pandas 3.0</h4><p>Chained assignment <strong>does not update the original object</strong>.</p><p>The intermediate result behaves as a separate object. Writing to it does not mutate df.</p><p>This consistent behavior is precisely why the old SettingWithCopyWarning is no longer needed — the ambiguity is gone.</p><p>The correct pattern for intentional mutation remains:</p><pre>df.loc[df[&quot;A&quot;] &gt; 1, &quot;B&quot;] = 0</pre><p>In pandas 3.0, chained assignment never mutates the source DataFrame. It consistently fails to update the original object.</p><p>That clarity is a major behavioral shift.</p><h3>C. Column Assignment After Selection</h3><pre>df2 = df[[&quot;A&quot;]]<br>df2[&quot;A&quot;] = df2[&quot;A&quot;] * 10</pre><p>In pandas 3.0:</p><ul><li>df2 behaves as an independent object</li><li>Modifying df2 does not modify df</li></ul><p>This now aligns with what most users intuitively expect.</p><h3>4. Major Benefits for Programmers</h3><p>Copy-on-Write is not just an internal cleanup. It makes pandas code fundamentally safer and easier to reason about.</p><h3>Benefit 1: The End of SettingWithCopyWarning Confusion</h3><p>For years, users encountered:</p><pre>SettingWithCopyWarning: A value is trying to be set on a copy of a slice...</pre><p>The warning existed because pandas could not guarantee whether a chained assignment would mutate the original object.</p><p>In pandas 3.0, that ambiguity is removed.</p><p>Chained assignment no longer updates the original DataFrame. Derived objects behave as copies, so accidental parent mutation is no longer possible in this pattern.</p><p>As a result, the old SettingWithCopyWarning is removed.</p><p>This reflects a fundamental simplification of pandas’ mutation semantics.</p><h3>Benefit 2: Safer Code in Large Pipelines</h3><p>Many real-world workflows involve filtering first and transforming later:</p><pre>df_filtered = df[df[&quot;region&quot;] == &quot;US&quot;]<br>df_filtered[&quot;sales&quot;] = df_filtered[&quot;sales&quot;] * 1.1</pre><p>In pandas 3.0:</p><ul><li>df_filtered behaves independently</li><li>df is guaranteed to remain unchanged</li></ul><p>This is especially valuable in:</p><ul><li>ETL jobs</li><li>Analytics pipelines</li><li>Data preprocessing</li><li>Machine learning workflows</li></ul><p>When multiple engineers collaborate, predictable mutation semantics significantly reduce debugging time.</p><h3>Benefit 3: Memory Efficiency Without API Complexity</h3><p>Copy-on-Write does not mean “copy everything immediately.”</p><p>For example:</p><pre>df2 = df[[&quot;A&quot;, &quot;B&quot;]]<br>df3 = df[[&quot;A&quot;, &quot;B&quot;]]</pre><p>Both df2 and df3 behave as independent objects.</p><p>Internally, pandas may defer copying for performance reasons. But once either object is modified:</p><pre>df2[&quot;A&quot;] = df2[&quot;A&quot;] * 2</pre><p>pandas ensures that the modification does not affect df or df3.</p><p>The important point:</p><ul><li>The API guarantees independence</li><li>Internal memory optimization is invisible to the user</li></ul><h3>Important Nuance: df2 = df</h3><p>It’s important to distinguish between:</p><pre>df2 = df</pre><p>and:</p><pre>df2 = df.copy(deep=False)</pre><p>df2 = df creates another reference to the <em>same</em> object. Modifying one modifies the other, because there is still only one DataFrame.</p><p>Copy-on-Write applies when <strong>distinct pandas objects</strong> share underlying data — such as after indexing or copy(deep=False).</p><p>In short: CoW applies to derived objects, not to multiple references to the same object.</p><p>This distinction clarifies where CoW applies and prevents confusion.</p><h3>5. Real-World Example: Feature Engineering</h3><p>Suppose you build training and test sets:</p><pre>train = df[df[&quot;split&quot;] == &quot;train&quot;]<br>test = df[df[&quot;split&quot;] == &quot;test&quot;]<br><br>train[&quot;log_income&quot;] = np.log(train[&quot;income&quot;])<br>test[&quot;log_income&quot;] = np.log(test[&quot;income&quot;])</pre><p>In pandas 3.0:</p><ul><li>train and test behave as independent objects</li><li>Modifications do not affect df</li><li>Behavior is consistent and predictable</li></ul><p>Filtering now cleanly produces working datasets.</p><h3>6. Behavioral Changes to Watch For</h3><p>Copy-on-Write improves safety, but it does change behavior.</p><p>Code that relied on implicit shared mutation will behave differently.</p><p>For example:</p><pre>df2 = df.copy(deep=False)<br>df2[&quot;A&quot;] = 0</pre><p>In pandas 3.0:</p><ul><li>The modification does not mutate df</li><li>Isolation is enforced</li></ul><p>Pandas now prioritizes mutation safety over implicit shared behavior.</p><h3>7. Performance Considerations</h3><p>Copy-on-Write balances correctness and performance.</p><h3>When It Helps</h3><ul><li>Read-heavy workflows</li><li>Analytical queries</li><li>Large datasets with limited mutation</li><li>Exploratory data analysis</li></ul><h3>When It May Add Cost</h3><ul><li>Frequent mutation of many small derived objects</li><li>Immediate writes after every slice</li></ul><p>In those cases, isolation still requires copying. But for most workloads, the tradeoff strongly favors:</p><p>✅ Correctness<br>✅ Predictability<br>✅ Maintainability</p><h3>8. The New Mental Model</h3><p>Under pandas 3.0:</p><ul><li>Derived objects behave as copies</li><li>Writes trigger isolation when needed</li></ul><p>You no longer need to ask:</p><blockquote><em>“Is this a view or a copy?”</em></blockquote><p>That question is no longer central to writing correct pandas code.</p><h3>9. Why This Matters</h3><p>Copy-on-Write reflects a broader design philosophy shift.</p><p>After being gradually introduced in earlier releases, making CoW the only mode in 3.0 signals a clear priority:</p><blockquote><strong><em>Semantic clarity over legacy ambiguity.</em></strong></blockquote><p>Copy-on-Write:</p><ul><li>Removes hidden mutation bugs</li><li>Simplifies teaching pandas</li><li>Makes code reviews clearer</li><li>Improves confidence in production pipelines</li><li>Encourages explicit mutation patterns</li></ul><p>Modern data workflows depend on predictable transformations. Clear mutation boundaries make those workflows safer and easier to trust.</p><h3>10. Final Thoughts</h3><p>Pandas 3.0’s adoption of Copy-on-Write as the only mode is a major milestone.</p><p>Before:</p><ul><li>Views vs copies were ambiguous</li><li>Warnings were confusing</li><li>Accidental mutation was common</li></ul><p>Now:</p><ul><li>Derived objects behave as copies</li><li>Mutation is isolated</li><li>Behavior is deterministic</li></ul><p>For programmers, that means:</p><p>✅ Fewer hidden bugs<br>✅ Cleaner pipelines<br>✅ Safer transformations<br>✅ Reduced debugging time<br>✅ Easier onboarding</p><p>Copy-on-Write makes pandas behave the way most users always expected: selecting data gives you something you can safely modify without worrying about unintended side effects.</p><p>And that clarity may be the biggest win of all.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=87a1870a144f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Understanding Collation in ClickHouse: Sorting, Comparison, and Character Handling]]></title>
            <link>https://medium.com/@sathishdba/understanding-collation-in-clickhouse-sorting-comparison-and-character-handling-94e47e881c77?source=rss-369246c21caf------2</link>
            <guid isPermaLink="false">https://medium.com/p/94e47e881c77</guid>
            <dc:creator><![CDATA[sathish kumar srinivasan]]></dc:creator>
            <pubDate>Wed, 18 Jun 2025 12:15:14 GMT</pubDate>
            <atom:updated>2025-06-19T07:00:36.376Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xCCeh94h9gjbw0QB8jldQQ.png" /></figure><h3>Introduction to Collation in ClickHouse</h3><p>Collation defines how string comparison and sorting are performed. In ClickHouse, this influences how data is ordered, filtered, joined, and grouped.</p><p>By default, ClickHouse uses binary collation — comparisons are byte-by-byte, case-sensitive, and language-agnostic. However, ClickHouse supports several collation options that allow case-insensitive and locale-aware behavior.</p><h3>Default Behavior (No Collation)</h3><pre>-- Default binary comparison<br>SELECT &#39;a&#39; = &#39;A&#39;; -- Returns 0<br>SELECT &#39;a&#39; &lt; &#39;B&#39;; -- Returns 1 (based on ASCII values)<br><br>-- String sorting without collation<br>SELECT *<br>FROM (<br>    SELECT &#39;Apple&#39; AS fruit<br>    UNION ALL SELECT &#39;apple&#39;<br>    UNION ALL SELECT &#39;Banana&#39;<br>    UNION ALL SELECT &#39;árbol&#39;<br>)<br>ORDER BY fruit;</pre><p><strong>Output:</strong></p><pre>Apple<br>Banana<br>apple<br>árbol</pre><h3>Using COLLATE in Queries</h3><p><strong>3.1 Basic String Comparison</strong></p><pre>-- Case-insensitive comparison<br>SELECT &#39;a&#39; = &#39;A&#39; COLLATE &#39;ci&#39;;</pre><p><strong>Output:</strong></p><pre>1<br>-- Language-specific comparison<br>SELECT &#39;hello&#39; = &#39;hełło&#39; COLLATE &#39;pl&#39;;</pre><p><strong>Output:</strong></p><pre>0 (depends on locale rules)</pre><p><strong>3.2 ORDER BY with Collation</strong></p><pre>-- Case-insensitive sorting<br>SELECT *<br>FROM (<br>    SELECT &#39;Apple&#39; AS fruit<br>    UNION ALL SELECT &#39;banana&#39;<br>    UNION ALL SELECT &#39;Cherry&#39;<br>)<br>ORDER BY fruit COLLATE &#39;ci&#39;;</pre><p><strong>Output:</strong></p><pre>Apple<br>banana<br>Cherry<br><br>-- Language-specific sorting<br>SELECT * FROM fruits ORDER BY name COLLATE &#39;de&#39;;</pre><h3>Collation in Different Contexts</h3><p><strong>4.1 WHERE Clause</strong></p><p>Using COLLATE in a WHERE clause allows case-insensitive or locale-specific filtering. This is useful when user input may vary in case or accents.</p><pre>SELECT * FROM users WHERE name COLLATE &#39;ci&#39; = &#39;john&#39;;</pre><p>This will match any variation like “John”, “JOHN”, or “john” because of the case-insensitive collation.</p><p><strong>4.2 JOIN Operations</strong></p><p>Joins using collation enable more flexible string matching across tables, especially where case or language-specific differences might otherwise prevent a match.</p><pre>SELECT *<br>FROM table1 t1<br>JOIN table2 t2<br>ON t1.name COLLATE &#39;ci&#39; = t2.name;</pre><p>This ensures that names like “Alice” and “ALICE” will match during the join operation.</p><p><strong>4.3 GROUP BY</strong></p><p>Applying collation in GROUP BY ensures that values differing only by case or accents are grouped together.</p><pre>SELECT name COLLATE &#39;ci&#39;, COUNT(*)<br>FROM users<br>GROUP BY name COLLATE &#39;ci&#39;;</pre><p>This treats “Anna” and “anna” as the same group and provides a consolidated count.</p><h3>Available Collation Types</h3><p><strong>5.1 Binary (Default)</strong></p><p>Binary collation is the default behavior in ClickHouse. It compares strings based on their underlying byte values without considering case, accents, or language rules. This is fast and simple but may produce unexpected results when working with multilingual or user-facing text.</p><pre>SELECT * FROM users ORDER BY name COLLATE &#39;binary&#39;;</pre><p>This sorts the name column purely by binary order (e.g., &quot;Zebra&quot; will come before &quot;apple&quot; because uppercase letters come before lowercase ones in ASCII).</p><p><strong>5.2 Case-Insensitive (ci)</strong></p><pre>SELECT * FROM users WHERE name COLLATE &#39;ci&#39; = &#39;JOHN&#39;;</pre><p><strong>5.3 Language-Specific Collations</strong></p><pre>-- German<br>SELECT * FROM products ORDER BY name COLLATE &#39;de&#39;;<br><br>-- Turkish<br>SELECT * FROM users WHERE name COLLATE &#39;tr&#39; = &#39;İstanbul&#39;;</pre><h3>Performance Implications</h3><p><strong>6.1 Index Usage</strong></p><p>ClickHouse currently does <strong>not support collation-aware secondary indexes</strong>. Any expression involving COLLATE will typically bypass indexes because index expressions are built on raw binary comparisons.</p><pre>CREATE TABLE users (<br>    name String,<br>    INDEX name_idx (name) TYPE minmax GRANULARITY 1<br>) ENGINE = MergeTree() ORDER BY name;<br><br>-- This may not use the index due to collation<br>SELECT * FROM users WHERE name COLLATE &#39;ci&#39; = &#39;John&#39;;</pre><p><strong>Important:</strong> If you define an index using COLLATE, it will be accepted syntactically but <strong>not respected</strong> during execution. This is a known limitation in current ClickHouse versions.</p><p>As a workaround, consider pre-normalizing values (e.g., storing lowercase versions) in a separate column and indexing that instead.</p><p><strong>6.2 Optimization Strategies</strong></p><ul><li>Use collation only when needed.</li><li>Prefer binary operations for performance-critical filters.</li><li>Apply collation late in query if possible.</li></ul><h3>Common Use Cases</h3><p><strong>7.1 Multi-language Applications</strong></p><pre>SELECT * FROM products WHERE name COLLATE &#39;unicode&#39; LIKE &#39;%café%&#39;;</pre><p><strong>7.2 Search Functionality</strong></p><pre>SELECT * FROM documents WHERE content COLLATE &#39;ci_ai&#39; LIKE &#39;%search_term%&#39;;</pre><h3>Best Practices</h3><p><strong>8.1 Column Definitions</strong></p><p>As of current ClickHouse versions, COLLATE is <strong>not supported in column definitions</strong>. While you may see syntax examples with COLLATE in table schemas, they are either outdated, not executed, or not functionally respected.</p><p>Collation must be applied <strong>explicitly in each query</strong> (e.g., in WHERE, ORDER BY, or JOIN conditions). There is no persistent collation behavior tied to a column.</p><pre>-- Invalid: This will produce a syntax error<br>CREATE TABLE users (<br>    id UInt32,<br>    name String COLLATE &#39;ci&#39;,<br>    email String<br>) ENGINE = MergeTree() ORDER BY id;</pre><p><strong>8.2 Query Optimization</strong></p><ul><li>Avoid unnecessary collations on large datasets.</li><li>Preprocess text into normalized forms if applicable.</li></ul><h3>FAQ</h3><p><strong>Q1: What is the default collation in ClickHouse?</strong><br>A: Binary collation (byte-by-byte, case-sensitive).</p><p><strong>Q2: Does collation affect indexes?</strong><br>A: Yes. Using collation may bypass index usage.</p><p><strong>Q3: Can I set collation on a column?</strong><br>A: Yes, use COLLATE in column definition.</p><p><strong>Q4: Are multiple collations allowed in one query?</strong><br>A: Yes. You can apply different collations to different expressions or clauses within a single query. For example, one column can be ordered using COLLATE &#39;ci&#39;, while another uses COLLATE &#39;de&#39;. This provides flexibility for multilingual datasets.</p><p><strong>Q5: Is UTF-8 handled correctly with collation?</strong><br>A: Yes. Collations apply to Unicode (UTF-8) strings.</p><p><strong>Q6: Is default collation case-sensitive?</strong><br>A: Yes. Use ci for case-insensitive comparison.</p><p><strong>Q7: Can collation be used in materialized views?</strong><br>A: Yes.</p><p><strong>Q8: Does collation affect NULL sorting?</strong><br>A: No. NULL sorting is separate from collation.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=94e47e881c77" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[What is UTF-8 and How It Is Handled in ClickHouse]]></title>
            <link>https://medium.com/@sathishdba/what-is-utf-8-and-how-it-is-handled-in-clickhouse-7c1dfe8a7f88?source=rss-369246c21caf------2</link>
            <guid isPermaLink="false">https://medium.com/p/7c1dfe8a7f88</guid>
            <dc:creator><![CDATA[sathish kumar srinivasan]]></dc:creator>
            <pubDate>Wed, 18 Jun 2025 11:57:55 GMT</pubDate>
            <atom:updated>2025-06-19T07:04:32.777Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*diiKsHWNRDqevv6JjmqG2g.png" /></figure><h3>What is UTF-8?</h3><p>UTF-8 is a <strong>variable-length character encoding</strong> used to encode all Unicode characters. It uses between <strong>1 and 4 bytes per character</strong>:</p><ul><li><strong>1 byte</strong> for ASCII (U+0000 to U+007F)</li><li><strong>2–4 bytes</strong> for non-ASCII characters</li></ul><p>UTF-8 is:</p><ul><li>Backward-compatible with ASCII</li><li>Compact for English</li><li>Capable of representing every character in Unicode</li></ul><p>Because of its flexibility and efficiency, UTF-8 is now the <strong>default encoding on the web</strong>, in most databases, and across programming languages.</p><h3>How UTF-8 Is Handled in ClickHouse</h3><p>ClickHouse is a <strong>column-oriented OLAP database</strong> designed for high-performance analytics. It stores text data as <strong>UTF-8 encoded byte sequences</strong>, but it does <strong>not fully parse or validate Unicode</strong> at a semantic level.</p><p>Here’s how ClickHouse deals with UTF-8:</p><h3>Storage</h3><ul><li>ClickHouse stores String and LowCardinality(String) types as raw <strong>byte sequences</strong>.</li><li>These are <strong>assumed to be UTF-8</strong>, but no strict validation is enforced.</li><li>It is your responsibility to ensure input strings are valid UTF-8.</li></ul><h3>String Functions</h3><p>ClickHouse provides a variety of UTF-8-aware functions:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*x10jNp7Rx31Wkt4-TekLaw.png" /></figure><p>Contrast with:</p><ul><li>length(s): counts bytes</li><li>lower(s), upper(s): ASCII only</li></ul><p>Use the UTF8-suffixed versions when working with non-ASCII data.</p><h3>Sorting and Collation</h3><ul><li>By default, ClickHouse sorts strings by <strong>raw byte order</strong> (binary collation).</li><li>However, ClickHouse supports <strong>locale-aware collation</strong> via the COLLATE clause in ORDER BY.</li><li>You can specify a locale directly:</li></ul><pre>SELECT *<br>FROM my_table<br>ORDER BY city_name COLLATE &#39;fr&#39;</pre><ul><li>This enables sorting based on <strong>French collation rules</strong>, including accents and proper alphabetical order.</li><li>Collation applies per expression and supports String, LowCardinality, Nullable, Array, and Tuple types.</li><li>Be aware that COLLATE-based sorting is less performant than raw byte sorting, so it’s best used on <strong>final result sets with </strong><strong>LIMIT</strong> or small sorted subsets.</li></ul><p>Example:</p><pre>SELECT city_name FROM cities ORDER BY city_name COLLATE &#39;de&#39; LIMIT 5;</pre><p>This returns German-localized order for the top 5 city names.</p><h3>Search and Matching</h3><ul><li>ClickHouse allows regex and LIKE pattern matching.</li><li>UTF-8 is respected in functions like match(s, regex) or positionUTF8(), but:</li><li>Regex patterns must be UTF-8 valid.</li><li>Pattern matching still operates on byte sequences under the hood.</li></ul><h3>Input/Output</h3><ul><li>JSON, CSV, and TSV inputs/outputs are UTF-8 by default.</li><li>If input data contains invalid UTF-8, you may encounter parsing errors or silent corruption.</li><li>Use format_raw or preprocess input to sanitize encodings if needed.</li></ul><h4>Best Practices When Using UTF-8 in ClickHouse</h4><ul><li><strong>Validate UTF-8</strong> before inserting: ClickHouse won’t reject invalid encodings unless explicitly caught.</li><li>Use <strong>UTF8-aware string functions</strong> when working with non-ASCII data.</li><li>Avoid assuming length() means “number of characters” — use lengthUTF8().</li><li>For proper text sorting, process data outside ClickHouse if locale-sensitive ordering is needed.</li></ul><h3>Summary</h3><p>UTF-8 is a powerful and efficient encoding for storing multilingual text, and ClickHouse supports it well at the byte level. However, <strong>higher-level Unicode semantics (like normalization, locale sorting, or grapheme clusters)</strong> are not handled natively. For most analytics workloads, this is sufficient — as long as you’re careful with input validation and string function selection.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7c1dfe8a7f88" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[RoundBankers in ClickHouse: Achieving Financial Precision in Data Analytics]]></title>
            <link>https://medium.com/@sathishdba/roundbankers-in-clickhouse-achieving-financial-precision-in-data-analytics-28e9c6e9b5a7?source=rss-369246c21caf------2</link>
            <guid isPermaLink="false">https://medium.com/p/28e9c6e9b5a7</guid>
            <dc:creator><![CDATA[sathish kumar srinivasan]]></dc:creator>
            <pubDate>Wed, 18 Jun 2025 11:53:17 GMT</pubDate>
            <atom:updated>2025-06-19T07:07:26.206Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3SaWttmzu1Uw0lFvNmwlSg.png" /></figure><h3>Introduction to RoundBankers</h3><p>ClickHouse provides roundBankers()—an implementation of <strong>Banker’s Rounding</strong> (also known as <em>round half to even</em>). Unlike standard rounding which always rounds .5 values upward, Banker&#39;s Rounding rounds to the <strong>nearest even number</strong>. This method helps avoid systemic bias in data—especially critical in financial, statistical, and regulatory applications.</p><h4>Why this matters</h4><p>Even small rounding inconsistencies can compound across thousands or millions of records, introducing measurable financial bias. roundBankers() neutralizes this risk by fairly distributing rounding direction across datasets.</p><h3>Understanding RoundBankers Behavior</h3><pre>SELECT <br>    number/2.0 AS original,<br>    round(number/2.0, 0) AS standard_round,<br>    roundBankers(number/2.0, 0) AS bankers_round<br>FROM numbers(10)<br>WHERE number % 2 = 1;</pre><p><strong>Why</strong>: This demonstrates how .5 values are handled under both rounding strategies. Let’s break down what happens when dividing odd numbers by 2:</p><p>3 / 2 = 1.5</p><ul><li>round(1.5) → 2</li><li>roundBankers(1.5) → 2</li></ul><p>5 / 2 = 2.5</p><ul><li>round(2.5) → 3</li><li>roundBankers(2.5) → 2</li></ul><p>7 / 2 = 3.5</p><ul><li>round(3.5) → 4</li><li>roundBankers(3.5) → 4</li></ul><p>9 / 2 = 4.5</p><ul><li>round(4.5) → 5</li><li>roundBankers(4.5) → 4</li></ul><p>Standard round() always moves up from .5, whereas roundBankers() rounds to the nearest even number. Over time, this helps avoid overstatements or understatements in aggregate results.</p><h3>Real-Life Use Cases</h3><p><strong>3.1 Financial Statement Preparation</strong></p><pre>SELECT <br>    department,<br>    roundBankers(sum(revenue), 2) as rounded_revenue,<br>    roundBankers(sum(expenses), 2) as rounded_expenses,<br>    roundBankers(sum(revenue - expenses), 2) as rounded_profit<br>FROM financial_transactions<br>GROUP BY department;</pre><p><strong>Why</strong>: Accurate, bias-free rounding ensures departments aren’t unfairly misrepresented in reports. This matters for audits, financial transparency, and internal analytics.</p><p><strong>3.2 Banking Interest Calculations</strong></p><pre>SELECT <br>    account_id,<br>    balance,<br>    interest_rate,<br>    roundBankers(balance * (interest_rate/100)/12, 2) as monthly_interest<br>FROM accounts;</pre><p><strong>Why</strong>: Even a $0.01 rounding bias per account can aggregate to significant financial discrepancies. roundBankers() keeps interest payouts fair and auditable.</p><p><strong>3.3 Currency Exchange Operations</strong></p><pre>SELECT <br>    from_currency,<br>    to_currency,<br>    amount,<br>    exchange_rate,<br>    roundBankers(amount * exchange_rate, 2) as converted_amount<br>FROM forex_transactions;</pre><p><strong>Why</strong>: In FX systems, consistent over- or under-rounding can lead to unfair profit/loss margins. roundBankers() avoids bias in conversion precision.</p><h3>Industry-Specific Applications</h3><p><strong>4.1 Investment Portfolio Analysis</strong></p><pre>SELECT <br>    asset_class,<br>    total_value,<br>    roundBankers(100.0 * total_value / sum(total_value) OVER (), 2) as percentage<br>FROM portfolio_holdings;</pre><p><strong>Why</strong>: Ensures allocation percentages remain accurate and sum to 100% — crucial for investor reporting.</p><p><strong>4.2 Insurance Premium Calculations</strong></p><pre>SELECT <br>    policy_id,<br>    base_premium,<br>    risk_factor,<br>    roundBankers(base_premium * risk_factor, 2) as adjusted_premium<br>FROM insurance_policies;</pre><p><strong>Why</strong>: Premium adjustments based on risk must be fairly rounded. Skewed results could unfairly penalize customers or hurt margins.</p><p><strong>4.3 Payroll Processing</strong></p><pre>SELECT <br>    employee_id,<br>    gross_salary,<br>    roundBankers(gross_salary * tax_rate, 2) as tax_amount,<br>    roundBankers(gross_salary * (1 - tax_rate), 2) as net_salary<br>FROM payroll;</pre><p><strong>Why</strong>: Payroll precision avoids both overpayments and underpayments — key for HR trust and regulatory compliance.</p><h3>When to Use RoundBankers</h3><p><strong>5.1 Ideal Scenarios</strong></p><ul><li>Tax or financial audit reporting</li><li>Currency conversions at scale</li><li>Monthly or quarterly aggregation</li><li>Anywhere .5-value bias matters</li></ul><p><strong>5.2 Advantages</strong></p><ul><li>Prevents cumulative bias</li><li>Supports fairness and compliance</li><li>Helps maintain report consistency</li><li>Especially important in large data pipelines</li></ul><h3>Common Pitfalls and Solutions</h3><p><strong>6.1 Data Migration Challenges</strong></p><pre>SELECT <br>    transaction_id,<br>    old_amount,<br>    roundBankers(old_amount, 2) as migrated_amount,<br>    abs(old_amount - roundBankers(old_amount, 2)) as rounding_difference<br>FROM legacy_transactions;</pre><p><strong>Why</strong>: Helps identify discrepancies when migrating from systems that used non-Bankers rounding. Necessary for reconciling old and new pipelines.</p><p><strong>6.2 Aggregation Issues</strong></p><pre>SELECT <br>    category,<br>    sum(roundBankers(amount, 2)) as rounded_then_summed,<br>    roundBankers(sum(amount), 2) as summed_then_rounded,<br>    abs(sum(roundBankers(amount, 2)) - roundBankers(sum(amount), 2)) as difference<br>FROM transactions<br>GROUP BY category;</pre><p><strong>Why</strong>: Choosing to round before or after aggregation affects final totals. Use case and financial logic should guide your strategy.</p><h3>Performance Considerations</h3><ul><li>Slightly more compute-intensive than round(), but negligible for most workloads</li><li>No increase in memory footprint</li><li>Fully safe and consistent across distributed ClickHouse clusters</li></ul><h3>Best Practices</h3><ul><li>Document when and why roundBankers() is applied</li><li>Write tests specifically for .5 edge cases</li><li>Validate reports against original sources post-migration</li><li>Be consistent — don’t mix round() and roundBankers() in the same data path</li></ul><h3>Integration Patterns</h3><p><strong>9.1 With Materialized Views</strong></p><pre>CREATE MATERIALIZED VIEW financial_summary<br>ENGINE = AggregatingMergeTree()<br>ORDER BY (department, date)<br>AS SELECT <br>    department,<br>    toDate(timestamp) as date,<br>    roundBankers(sum(amount), 2) as daily_total<br>FROM transactions<br>GROUP BY department, date;</pre><p><strong>Why</strong>: Ensures your views contain consistently rounded data without needing on-the-fly recalculations.</p><p><strong>9.2 In ETL Processes</strong></p><ul><li>Apply roundBankers() in transformation logic</li><li>Use it in computed columns or column defaults</li><li>Run post-load validation on cumulative sums to ensure no drift</li></ul><h4>FAQ Section</h4><p><strong>Q1: Why use </strong><strong>roundBankers() instead of regular rounding?</strong><br> <strong>A:</strong> Because it avoids systematic rounding bias. Regular rounding always rounds .5 upward, which introduces a consistent overstatement. roundBankers() (round half to even) balances rounding up and down, leading to more accurate and unbiased financial reporting over time.</p><p><strong>Q2: How does </strong><strong>roundBankers() handle negative numbers?</strong><br> <strong>A:</strong> It applies the same logic—round to the nearest even number. For example, -2.5 rounds to -2, just like 2.5 rounds to 2.</p><p><strong>Q3: Is </strong><strong>roundBankers() compliant with financial regulations?</strong><br> <strong>A:</strong> Yes. Banker&#39;s rounding is accepted in many accounting standards such as GAAP and IFRS, and is used in financial audits, tax calculations, and legal reporting frameworks.</p><p><strong>Q4: What’s the performance impact compared to regular rounding?</strong><br> <strong>A:</strong> Minimal. While roundBankers() has slightly more logic than round(), the performance impact is negligible in production workloads.</p><p><strong>Q5: Can </strong><strong>roundBankers() be used in distributed queries?</strong><br> <strong>A:</strong> Yes. It is deterministic and behaves consistently across ClickHouse clusters.</p><p><strong>Q6: How does </strong><strong>roundBankers() handle </strong><strong>NULL values?</strong><br> <strong>A:</strong> It returns NULL when the input is NULL, just like most other scalar functions in ClickHouse.</p><p><strong>Q7: Is </strong><strong>roundBankers() deterministic?</strong><br> <strong>A:</strong> Yes. It always returns the same output for the same input.</p><p><strong>Q8: What’s the maximum precision supported by </strong><strong>roundBankers()?</strong><br> <strong>A:</strong> It depends on the input type: Float32, Float64, or Decimal. Use Decimal for high-precision currency operations.</p><p><strong>Q9: How should </strong><strong>roundBankers() be used in aggregation?</strong><br> <strong>A:</strong> Use it <strong>before</strong> aggregation when precision per record is important, and <strong>after</strong> when only the final total matters.</p><p><strong>Q10: Can </strong><strong>roundBankers() be used in column defaults or materialized views?</strong><br> <strong>A:</strong> Yes, it can be used in both default expressions and view definitions to enforce consistent rounding across pipelines.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=28e9c6e9b5a7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Mastering ROUND Functions in ClickHouse: Precision and Performance in Data Processing]]></title>
            <link>https://medium.com/@sathishdba/mastering-round-functions-in-clickhouse-precision-and-performance-in-data-processing-1af25b1f85b0?source=rss-369246c21caf------2</link>
            <guid isPermaLink="false">https://medium.com/p/1af25b1f85b0</guid>
            <dc:creator><![CDATA[sathish kumar srinivasan]]></dc:creator>
            <pubDate>Wed, 18 Jun 2025 10:52:07 GMT</pubDate>
            <atom:updated>2025-06-19T07:12:27.952Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*q-qD7QJco0NToB0jK9qHDA.png" /></figure><h3>Introduction</h3><p>Rounding functions are critical tools in any analytical engine where numeric precision and presentation matter. In ClickHouse, rounding isn’t just a cosmetic operation — it plays a role in aggregation, performance, and even schema design. This article explores the family of rounding functions available in ClickHouse, their applications, performance impact, and best practices.</p><h3>Understanding ClickHouse Rounding Functions Family</h3><p>ClickHouse provides several rounding functions tailored to specific needs:</p><p>round(x[, decimal_places]): <strong>Standard Rounding</strong></p><p>Rounds to the nearest integer or to a specified number of decimal places.</p><pre>SELECT round(12.345),       -- 12<br>       round(12.5),         -- 13<br>       round(12.345, 1),    -- 12.3<br>       round(12.345, 2);    -- 12.35</pre><p>roundBankers(x[, decimal_places]): <strong>Banker&#39;s Rounding (Round-Half-To-Even)</strong></p><p>Banker’s rounding, also called “round half to even”, rounds .5 values to the nearest even number to minimize cumulative rounding error in large datasets. This method is commonly used in financial calculations to prevent systemic rounding bias.</p><pre>SELECT roundBankers(2.5),    -- 2 (even)<br>       roundBankers(3.5),    -- 4 (even)<br>       roundBankers(1.45, 1); -- 1.4 (rounds to even 0.4)</pre><p>roundDuration(num): <strong>Human-Friendly Duration Buckets</strong></p><p>roundDuration() rounds numerical values representing durations into intuitive thresholds like 100, 1,000, 10,000, etc. It&#39;s useful for presenting elapsed time, latency, or similar metrics in UI/UX.</p><p>Internally, ClickHouse chooses thresholds like 1, 2, 5 multiplied by powers of ten (e.g., 10, 20, 50, 100, etc.) based on human-friendly intervals.</p><pre>SELECT roundDuration(123),       -- 100<br>       roundDuration(9999),      -- 10000<br>       roundDuration(55000);     -- 60000</pre><p>roundAge(num): <strong>Round Age Values to Conventional Units</strong></p><p>This function is designed to map numerical age inputs into typical age brackets. Rather than always rounding to the nearest 10, it rounds to commonly used milestones.</p><p>Examples:</p><ul><li>Ages 0–24 → 0, 5, 10, 15, 20</li><li>Ages 25–74 → rounded to nearest 10 (30, 40, etc.)</li><li>Ages 75+ → rounded to 100 to avoid misleading granularity</li></ul><p>This explains why roundAge(86) returns 100.</p><pre>SELECT roundAge(21),   -- 20<br>       roundAge(32),   -- 30<br>       roundAge(86);   -- 100</pre><p>roundDown(num, arr): <strong>Round Down to Nearest Value in Array</strong></p><pre>SELECT roundDown(45, [10, 20, 30, 50]); -- 30<br>SELECT roundDown(5, [2, 4, 6, 8]);      -- 4</pre><p>roundUp(num, arr): <strong>Round Up to Nearest Value in Array</strong></p><pre>SELECT roundUp(45, [10, 20, 30, 50]); -- 50<br>SELECT roundUp(5, [2, 4, 6, 8]);      -- 6</pre><h3>Basic ROUND Function Deep Dive</h3><pre>-- Basic rounding<br>SELECT<br>    round(10.4),    -- Returns 10<br>    round(10.5),    -- Returns 11<br>    round(-10.4),   -- Returns -10<br>    round(-10.5);   -- Returns -11<br><br>-- Specifying decimal places<br>SELECT<br>    round(10.44, 1),    -- Returns 10.4<br>    round(10.45, 1),    -- Returns 10.5<br>    round(123.45, -1);  -- Returns 120</pre><h3>Common Use Cases</h3><ul><li><strong>Financial calculations and reporting</strong>: Currency precision.</li><li><strong>Statistical analysis</strong>: Rounded percentiles, means.</li><li><strong>Data normalization</strong>: Align numeric data to fixed buckets.</li><li><strong>Time series aggregation</strong>: Smooth time-based metrics.</li><li><strong>Performance metrics</strong>: Reduce precision for compactness.</li><li><strong>Bucket creation</strong>: Group numeric data into histograms.</li></ul><h3>Performance Considerations</h3><ul><li>Rounding functions are lightweight, especially round() and roundBankers().</li><li>Negligible memory impact, even at large scales.</li><li>Avoid using in WHERE clauses for index-dependent queries.</li><li>Prefer rounding after aggregation when performance is critical.</li></ul><h3>Best Practices</h3><ul><li>Use roundBankers() for compliance with financial standards.</li><li>Don’t over-round — preserve meaningful decimals.</li><li>Validate edge cases like negative values and .5 boundaries.</li><li>Document your rounding logic in production models.</li></ul><h3>Advanced Applications</h3><p>7.1 Financial Data Processing</p><pre>-- Price rounding for different currencies<br>SELECT<br>    price,<br>    round(price, 2) as USD,<br>    round(price, 0) as JPY,<br>    round(price, 3) as BTC<br>FROM transactions;</pre><p>7.2 Statistical Applications</p><pre>-- Percentile calculations with rounding<br>SELECT<br>    category,<br>    round(quantile(0.95)(value), 2) as p95,<br>    round(avg(value), 2) as mean<br>FROM measurements<br>GROUP BY category;</pre><p>7.3 Time Series Data</p><pre>-- Rounding timestamps for aggregation<br>SELECT<br>    toStartOfHour(timestamp) as hour,<br>    round(avg(value), 1) as avg_value<br>FROM sensors<br>GROUP BY hour;</pre><h3>Comparing Different Rounding Methods</h3><ul><li>round() vs roundBankers(): Bankers uses round-half-to-even; useful in finance.</li><li>round() vs floor()/ceil(): Different mathematical behavior.</li><li>round() vs trunc(): trunc() removes decimals without rounding.</li><li>round() is generally faster than casting or mathematical tricks.</li></ul><h3>Common Pitfalls and Solutions</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/952/1*LOG2vTRFJxxGDzKifQ80Rw.png" /></figure><h3>Integration Patterns</h3><h4>GROUP BY</h4><p>Use round() to bin numeric values into buckets for aggregation.</p><pre>SELECT<br>    round(salary, -3) AS salary_group,<br>    COUNT(*) AS employees<br>FROM employees<br>GROUP BY salary_group;</pre><h4>Window Functions</h4><p>Combine round() with aggregate window functions to standardize output precision.</p><pre>SELECT<br>    user_id,<br>    round(avg(duration) OVER (PARTITION BY user_id), 2) AS avg_duration<br>FROM user_sessions;</pre><h4>Materialized Views</h4><p>Apply rounding before inserting into a materialized view to ensure consistency and reduce cardinality.</p><pre>CREATE MATERIALIZED VIEW rounded_sales<br>ENGINE = AggregatingMergeTree()<br>AS<br>SELECT<br>    toDate(order_date) AS day,<br>    round(sumState(amount), 2) AS total_sales<br>FROM orders<br>GROUP BY day;</pre><h4>JOINs</h4><p>Avoid rounding join keys as it may cause mismatches. Instead, round values only for final output or analysis.</p><pre>-- Not recommended:<br>-- JOIN ON round(order_value, -2) = round(target_value, -2)<br><br>-- Recommended:<br>WITH round(order_value, -2) AS bucket<br>SELECT o.id, bucket, t.segment<br>FROM orders o<br>JOIN targets t ON o.id = t.order_id;<br>``` Avoid rounding join keys; round only for output or analysis.<br><br>## 11. Practical Examples<br><br>### 11.1 Sales Data Analysis<br>```sql<br>SELECT<br>    date,<br>    round(sum(amount), 2) as total_sales,<br>    round(avg(amount), 2) as avg_sale,<br>    round(max(amount), 2) as max_sale<br>FROM sales<br>GROUP BY date;</pre><h4>11.2 Metric Aggregations</h4><pre>SELECT<br>    user_id,<br>    round(sum(duration)/60, 1) as hours_spent,<br>    round(avg(score), 1) as avg_score<br>FROM user_sessions<br>GROUP BY user_id;</pre><h3>Tips and Tricks</h3><ul><li>Use Decimals for monetary values.</li><li>Combine with quantile() for rounded percentiles.</li><li>Avoid rounding in JOINs.</li><li>Test edge inputs: 0.5, -0.5, large floats.</li></ul><h3>Alternative Approaches</h3><ul><li>CAST() to Int/Decimal: Less flexible, sometimes faster.</li><li>Mathematical FLOOR(), CEIL(): Simpler logic.</li><li>Custom rounding functions via lambdas/macros.</li><li>Skip rounding altogether where precision isn’t critical.</li></ul><h3>FAQ</h3><p><strong>Q1: What’s the difference between round() and roundBankers()?</strong><br>A: round() uses standard rounding (0.5 rounds up), while roundBankers() uses banker’s rounding (rounds to the nearest even number).</p><p><strong>Q2: How does round() handle NULL values?</strong><br>A: round() returns NULL when the input is NULL.</p><p><strong>Q3: Is there a performance impact when using round() with many decimal places?</strong><br>A: Slight, but generally negligible.</p><p><strong>Q4: Can round() be used in materialized views?</strong><br>A: Yes, it works well and ensures consistency.</p><p><strong>Q5: How does round() handle very large numbers?</strong><br>A: It maintains precision within the limits of the numeric type.</p><p><strong>Q6: What’s the maximum number of decimal places supported?</strong><br>A: Depends on the underlying data type — Decimal types offer high precision.</p><p><strong>Q7: How does round() behave with negative numbers?</strong><br>A: Follows standard math rules: e.g., -1.5 rounds to -2.</p><p><strong>Q8: Can round() be used with aggregate functions?</strong><br>A: Yes, it’s commonly used with SUM(), AVG(), etc.</p><p><strong>Q9: How does round() compare to CAST in terms of performance?</strong><br>A: More flexible and designed specifically for rounding.</p><p><strong>Q10: Is round() deterministic?</strong><br>A: Yes, given the same input, it always returns the same output.</p><h3>Conclusion</h3><p>ClickHouse offers a versatile and performant set of rounding functions suitable for everything from financial reports to time-series aggregation. When used correctly, they improve not only numerical clarity but also query efficiency and dataset manageability. Be mindful of rounding semantics and choose the function that matches your domain logic.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1af25b1f85b0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Understanding ANY() in ClickHouse: Beyond Simple Aggregation]]></title>
            <link>https://medium.com/@sathishdba/understanding-any-in-clickhouse-beyond-simple-aggregation-104bdf0aa4a3?source=rss-369246c21caf------2</link>
            <guid isPermaLink="false">https://medium.com/p/104bdf0aa4a3</guid>
            <dc:creator><![CDATA[sathish kumar srinivasan]]></dc:creator>
            <pubDate>Tue, 17 Jun 2025 13:44:47 GMT</pubDate>
            <atom:updated>2025-06-19T07:49:04.492Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MsGF2TaoOJHSyKCD5bJnCg.png" /></figure><h3>Introduction</h3><p>The ANY() function in ClickHouse is a lesser-known yet extremely powerful aggregation tool. Unlike more conventional functions like MIN() or MAX(), ANY() offers a non-deterministic yet high-performance way to grab one value from a group—<strong>without caring which one</strong>.</p><p>This article dives into how ANY() works, when (and when not) to use it, and why it can make a major difference in performance-sensitive or exploratory analytics workloads. We&#39;ll also cover common misconceptions and edge cases.</p><h3>Understanding ANY()</h3><p>Basic Syntax and Usage</p><pre>SELECT<br>    key_column,<br>    ANY(value_column) AS some_value<br>FROM table<br>GROUP BY key_column;</pre><p>It returns <strong>one arbitrary value</strong> from each group, without guarantees about which specific one.</p><h4>How ANY() Differs from Other Aggregates</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nYUELC733A9aip6aUBaxjw.png" /></figure><h4>Deterministic vs Non-Deterministic</h4><p>ANY() is <strong>non-deterministic</strong>. That means the same query can return different results across executions if the data changes or is distributed differently.</p><h4>Performance Characteristics</h4><ul><li><strong>No comparisons or sorting</strong>.</li><li><strong>Minimal memory usage</strong>.</li><li>Ideal for <strong>massive, distributed datasets</strong>.</li></ul><h3>Code Examples</h3><p>Basic Usage</p><pre>SELECT<br>    user_id,<br>    ANY(first_name) AS sample_first_name<br>FROM users<br>GROUP BY user_id;</pre><p>With Subqueries</p><pre>SELECT<br>    department_id,<br>    ANY((SELECT salary FROM employees WHERE dept_id = department_id)) AS sample_salary<br>FROM departments;</pre><p>Combining with Other Aggregates</p><pre>SELECT<br>    date,<br>    COUNT(*) AS total_orders,<br>    ANY(customer_name) AS sample_customer,<br>    SUM(amount) AS total_amount<br>FROM orders<br>GROUP BY date;</pre><h3>Real-Life Use Cases</h3><h4>Sampling values for reporting dashboards</h4><p><strong>Scenario:</strong> You’re building a dashboard that shows summary metrics per product, including a sample product name.</p><pre>SELECT<br>    product_id,<br>    ANY(product_name) AS sample_name,<br>    AVG(rating) AS avg_rating<br>FROM product_reviews<br>GROUP BY product_id;</pre><p><strong>Why it works:</strong> Product names are usually stable per ID. ANY() lets you avoid costly joins or sorting.</p><h4>Quick exploration of large datasets without full aggregation</h4><p><strong>Scenario:</strong> You’re debugging raw logs for a specific API version rollout across services.</p><pre>SELECT<br>    api_version,<br>    ANY(user_id) AS example_user,<br>    COUNT(*) AS total_hits<br>FROM api_logs<br>GROUP BY api_version;</pre><p><strong>Why it works:</strong> ANY() gives you a real data point fast — ideal when you just want to &quot;see something.&quot;</p><h4>Performance optimizations in development/testing queries</h4><p><strong>Scenario:</strong> You’re prototyping an aggregation pipeline on billions of rows.</p><pre>SELECT<br>    country,<br>    ANY(city) AS example_city,<br>    COUNT(*) AS user_count<br>FROM geo_users<br>GROUP BY country;</pre><p><strong>Why it works:</strong> You reduce memory and CPU load significantly while developing the core logic.</p><h4>Event deduplication with complex data</h4><p><strong>Scenario:</strong> A user may trigger multiple log entries for the same action. You want to keep one representative record.</p><pre>SELECT<br>    user_id,<br>    action_type,<br>    ANY(json_payload) AS example_payload<br>FROM user_event_logs<br>WHERE action_type = &#39;purchase&#39;<br>GROUP BY user_id, action_type;</pre><p><strong>Why it works:</strong> For deduplication, ANY() is a fast way to collapse multiple near-identical events.</p><h3>When to Use ANY() – With Examples</h3><h4>✅ Large dataset sampling</h4><pre>SELECT<br>    region,<br>    ANY(customer_name) AS sample_customer<br>FROM customers<br>GROUP BY region;</pre><h4>✅ Cases where exact value selection isn’t critical</h4><pre>SELECT<br>    product_id,<br>    ANY(category_name) AS category<br>FROM product_catalog<br>GROUP BY product_id;</pre><h4>✅ Performance-critical aggregations</h4><pre>SELECT<br>    toDate(event_time) AS day,<br>    ANY(sensor_id) AS sample_sensor,<br>    AVG(temperature) AS avg_temp<br>FROM sensor_logs<br>GROUP BY day;</pre><h4>✅ Temporary/development queries</h4><pre>SELECT<br>    campaign_id,<br>    ANY(ad_title) AS example_title,<br>    SUM(clicks) AS total_clicks<br>FROM ad_analytics<br>GROUP BY campaign_id;</pre><h4>✅ Data quality checks</h4><pre>SELECT<br>    warehouse_id,<br>    ANY(location_code) AS sample_code<br>FROM warehouse_inventory<br>GROUP BY warehouse_id;</pre><h4>When NOT to Use ANY()</h4><ul><li>When deterministic results are mandatory</li><li>In <strong>financial</strong> or <strong>compliance</strong> contexts</li><li>When downstream systems rely on exact values</li><li>Critical pipelines (e.g., ETL/ELT jobs)</li><li>When sort order or precedence is important</li></ul><h4>Advantages vs Other Methods</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/912/1*3IBPegmrwT9HwDG2AGBVqw.png" /></figure><h3>Best Practices</h3><ul><li>Combine with stable aggregates (COUNT, SUM) for balanced insights.</li><li>Use in dev or debug scenarios where speed &gt; accuracy.</li><li>Avoid relying on its value in production logic.</li><li>Always document where ANY() is used and why.</li><li>Validate results via tests if used in pipelines or automated jobs.</li></ul><h3>Common Pitfalls and Solutions</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/1*u-D9oSH8n1s_-zMODaX_Sg.png" /></figure><h3>FAQ</h3><p><strong>Q1: What exactly does </strong><strong>ANY() return?</strong><br>A: An arbitrary value from the group. It&#39;s non-deterministic and not based on order.</p><p><strong>Q2: Is </strong><strong>ANY() faster than other aggregation functions?</strong><br>A: Yes. It&#39;s one of the fastest because it avoids comparisons and sorting.</p><p><strong>Q3: Can </strong><strong>ANY() be used with all data types?</strong><br>A: Yes. It works with strings, numbers, arrays, and nested structures.</p><p><strong>Q4: How is </strong><strong>ANY() different from </strong><strong>LIMIT 1?</strong><br>A: LIMIT 1 returns the first row based on query sort order. ANY() ignores order.</p><p><strong>Q5: Is </strong><strong>ANY() deterministic across query executions?</strong><br>A: No. Use MIN(), MAX(), or argMin() for deterministic behavior.</p><p><strong>Q6: Can </strong><strong>ANY() be used in materialized views?</strong><br>A: Yes, but with caution due to its non-determinism.</p><p><strong>Q7: Does </strong><strong>ANY() impact query performance significantly?</strong><br>A: Yes — positively. It’s very performant.</p><p><strong>Q8: Can </strong><strong>ANY() be used with window functions?</strong><br>A: No. It’s not a window function — only an aggregate.</p><p><strong>Q9: How does </strong><strong>ANY() handle NULL values?</strong><br>A: It may return NULL if it&#39;s part of the group — no filtering is applied.</p><p><strong>Q10: Is there a way to make </strong><strong>ANY() deterministic?</strong><br>A: No. Use argMin() or FIRST_VALUE() with ORDER BY if determinism is required.</p><h3>Conclusion</h3><p>ANY() is a highly efficient tool in ClickHouse for getting a sample value from each group — but with great power comes responsibility. Understanding when and where to use it (and when not to) will help you write faster, clearer, and safer queries.</p><p>Use it wisely — and always document its purpose when accuracy isn’t your top priority.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=104bdf0aa4a3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Mastering Cardinality in Data Modeling: From Basics to Best Practices]]></title>
            <link>https://medium.com/@sathishdba/mastering-cardinality-in-data-modeling-from-basics-to-best-practices-7d3019aac6ef?source=rss-369246c21caf------2</link>
            <guid isPermaLink="false">https://medium.com/p/7d3019aac6ef</guid>
            <dc:creator><![CDATA[sathish kumar srinivasan]]></dc:creator>
            <pubDate>Thu, 12 Jun 2025 08:17:30 GMT</pubDate>
            <atom:updated>2025-06-12T08:17:30.709Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introduction</h3><p>Cardinality defines how many instances of one entity relate to instances of another. It is fundamental to data modeling because it directly informs table structure, foreign key placement, and query logic.</p><p>Design errors in cardinality can result in data redundancy, orphan records, failed joins, or performance bottlenecks. Whether designing OLTP systems or analytics platforms, understanding cardinality is essential for integrity, performance, and scalability.</p><p><strong>Example:</strong><br> A poorly modeled many-to-many relationship without a junction table can crash complex joins or inflate storage due to duplication.</p><h3>Section 1: Cardinality Fundamentals</h3><h4>Definition</h4><p>Cardinality specifies the number of allowed or required relationships between entities:</p><ul><li><strong>Maximum cardinality:</strong> One or many.</li><li><strong>Minimum cardinality:</strong> Optional (0) or mandatory (1).</li></ul><h4>Types of Relationships</h4><ul><li><strong>One-to-One (1:1):</strong> Each row in A links to one row in B.</li><li><strong>One-to-Many (1:N):</strong> One row in A links to many in B.</li><li><strong>Many-to-Many (M:N):</strong> Many rows in A link to many in B.</li></ul><h4>Minimum/Maximum Constraints Example:</h4><ul><li>A person <strong>must</strong> have one and only one passport → (1,1)</li><li>A product <strong>can</strong> have many reviews, or none → (0,N)</li></ul><h4>Code Sample: ERD Notations</h4><p><strong>Crow’s Foot Notation:</strong></p><ul><li>1:1 → │───│</li><li>1:N → │───&lt;</li><li>M:N → &gt;───&lt;</li></ul><p><strong>Chen Notation:</strong></p><ul><li>Attributes are ovals, entities rectangles, relationships diamonds.</li><li>Cardinality is written near relationships (e.g., (1,N))</li></ul><p><strong>UML Class Diagram Example (PlantUML):</strong></p><pre>class Customer<br>class Order<br><br>Customer &quot;1&quot; -- &quot;0..*&quot; Order : places</pre><h3>Section 2: Implementing Cardinality in Relational Databases</h3><h4>Foreign Keys</h4><p>Used to enforce 1:1 and 1:N relationships via referential integrity.</p><h4>Junction Tables</h4><p>Handle M:N relationships by breaking them into two 1:N relationships.</p><h4>Code Sample: SQL Cardinality Patterns</h4><pre>-- One-to-One<br>CREATE TABLE Employee (<br>    id INT PRIMARY KEY,<br>    ssn VARCHAR(11) UNIQUE<br>);<br><br>CREATE TABLE EmployeeDetails (<br>    id INT PRIMARY KEY,<br>    emp_id INT UNIQUE,<br>    FOREIGN KEY (emp_id) REFERENCES Employee(id)<br>);<br><br>-- One-to-Many<br>CREATE TABLE Department (<br>    id INT PRIMARY KEY<br>);<br><br>CREATE TABLE Employee (<br>    id INT PRIMARY KEY,<br>    dept_id INT,<br>    FOREIGN KEY (dept_id) REFERENCES Department(id)<br>);<br><br>-- Many-to-Many<br>CREATE TABLE Student (<br>    id INT PRIMARY KEY<br>);<br><br>CREATE TABLE Course (<br>    id INT PRIMARY KEY<br>);<br><br>CREATE TABLE Enrollment (<br>    student_id INT,<br>    course_id INT,<br>    PRIMARY KEY (student_id, course_id),<br>    FOREIGN KEY (student_id) REFERENCES Student(id),<br>    FOREIGN KEY (course_id) REFERENCES Course(id)<br>);</pre><h4>Referential Integrity</h4><ul><li>Use ON DELETE CASCADE or SET NULL for managing orphan data.</li><li>Index foreign keys to optimize joins.</li></ul><h3>Section 3: Advanced Cardinality Patterns</h3><h4>Recursive Relationships</h4><p>An employee can supervise other employees:</p><pre>CREATE TABLE Employee (<br>    id INT PRIMARY KEY,<br>    supervisor_id INT,<br>    FOREIGN KEY (supervisor_id) REFERENCES Employee(id)<br>);</pre><h4>Conditional Relationships</h4><p>A contract <strong>must</strong> have a vendor <strong>if</strong> the status is ‘Active’ → Enforce using CHECK constraints or triggers.</p><h4>Temporal Cardinality</h4><p>Track evolving relationships over time:</p><pre>CREATE TABLE Assignment (<br>    employee_id INT,<br>    project_id INT,<br>    start_date DATE,<br>    end_date DATE,<br>    PRIMARY KEY (employee_id, project_id, start_date)<br>);</pre><p>Code Sample: Complex Constraints</p><pre>ALTER TABLE Contract<br>ADD CONSTRAINT chk_active_requires_vendor<br>CHECK (<br>    status != &#39;Active&#39; OR vendor_id IS NOT NULL<br>);</pre><h3>Performance Considerations</h3><ul><li>1:N relationships can be indexed easily.</li><li>M:N relationships can explode join sizes.</li><li>Recursive relationships require CTEs for depth traversal.</li></ul><h3>Section 4: Cardinality in Different Modeling Paradigms</h3><h4>Dimensional Modeling (Star Schemas)</h4><ul><li>Fact table uses 1:N to dimension tables.</li><li>Many-to-many handled via bridge tables.</li></ul><h4>Document Databases (e.g., MongoDB)</h4><ul><li>Embed 1:N relationships (e.g., user and addresses).</li><li>Reference or normalize for M:N relationships.</li></ul><h4>Graph Databases (e.g., Neo4j)</h4><ul><li>Model all relationships as first-class entities.</li><li>Native support for M:N and recursive patterns.</li></ul><h3>Code Sample: Cross-Paradigm Relationship</h3><p><strong>Relational:</strong></p><pre>-- Enrollment table from earlier</pre><p>MongoDB (embedded 1:N):</p><pre>{<br>  &quot;student_id&quot;: 1,<br>  &quot;courses&quot;: [<br>    { &quot;course_id&quot;: 101 },<br>    { &quot;course_id&quot;: 102 }<br>  ]<br>}</pre><p>Cypher (Neo4j):</p><pre>CREATE (s:Student {id: 1})<br>CREATE (c:Course {id: 101})<br>CREATE (s)-[:ENROLLED_IN]-&gt;(c)</pre><h3>FAQ Section</h3><p><strong>Q: What’s the difference between cardinality and multiplicity?</strong><br> A: Same concept; “multiplicity” is used in UML and “cardinality” in ER modeling.</p><p><strong>Q: How do I determine correct cardinality?</strong><br> A: Base it on business rules. Interview stakeholders or analyze actual data samples.</p><p><strong>Q: Can cardinality change over time?</strong><br> A: Yes. Schema updates or business rule changes (e.g., from 1:N to M:N) require structural changes.</p><p><strong>Q: What are fan traps and chasm traps?</strong><br> A:</p><ul><li><strong>Fan trap:</strong> Occurs when a 1:N relationship hides a N:M semantic (e.g., customer→order→product).</li><li><strong>Chasm trap:</strong> Happens when optional relationships break assumed connectivity between entities.</li></ul><p><strong>Q: How does cardinality affect indexing?</strong><br> A: 1:N benefits from indexing the foreign key. M:N often needs join indexes or materialized views.</p><p><strong>Q: Tools to validate cardinality constraints?</strong><br> A: ER/Studio, Vertabelo, MySQL Workbench, dbdiagram.io</p><p><strong>Q: How do I represent optional relationships in ERDs?</strong><br> A: Crow’s foot: use a circle at the zero-end (0,N). UML: multiplicity of 0..1.</p><p><strong>Q: Performance implications of many-to-many relationships?</strong><br> A: Joins are expensive, may require denormalization or pre-aggregation for large-scale queries.</p><h3>Conclusion</h3><p><strong>Summary of Best Practices</strong></p><ul><li>Use foreign keys and junction tables to model cardinality explicitly.</li><li>Align cardinality with real-world constraints, not just system defaults.</li><li>Use appropriate indexes to optimize joins and lookups.</li></ul><h4>Common Pitfalls</h4><ul><li>Omitting junction tables in M:N relationships</li><li>Allowing NULLs in required foreign keys</li><li>Misjudging optional vs. mandatory relationships</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/961/1*yCTYZIGOp5OAazkloa7Sxg.png" /></figure><h4>Impact on Scalability</h4><p>A well-designed cardinality model reduces redundancy, improves data quality, and scales better under load. Poor cardinality modeling is a root cause of performance degradation in both transactional and analytical systems.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7d3019aac6ef" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ClickHouse: Understanding LowCardinality vs Enum Columns]]></title>
            <link>https://medium.com/@sathishdba/clickhouse-understanding-lowcardinality-vs-enum-columns-9c09b26467d9?source=rss-369246c21caf------2</link>
            <guid isPermaLink="false">https://medium.com/p/9c09b26467d9</guid>
            <dc:creator><![CDATA[sathish kumar srinivasan]]></dc:creator>
            <pubDate>Thu, 12 Jun 2025 07:56:01 GMT</pubDate>
            <atom:updated>2025-06-12T07:56:01.909Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YU_zLJ2sHX1VN3ESPpkedQ.png" /></figure><p>When working with ClickHouse, column types play a crucial role in performance and storage efficiency. Two commonly used optimizations for storing repeated string values are LowCardinality(String) and Enum. While they may seem similar, they serve distinct purposes and have different trade-offs.</p><p>This post breaks down what they are, how they work, and when to use one over the other.</p><h3>What is LowCardinality(String)?</h3><p>LowCardinality(String) is a storage optimization for columns that contain a limited number of distinct string values (i.e., low cardinality), even if the dataset itself is large.</p><p><strong>Under the hood:</strong></p><ul><li>ClickHouse stores a <strong>dictionary</strong> of unique string values.</li><li>Each row stores a <strong>reference (index)</strong> into this dictionary.</li><li>New values are dynamically added to the dictionary.</li></ul><p><strong>Example:</strong></p><pre>CREATE TABLE licenses (<br>ContentLicense LowCardinality(String)<br>) ENGINE = MergeTree</pre><p><strong>Typical use case:</strong> When you store data like license types (&quot;MIT&quot;, &quot;GPL&quot;, &quot;Apache&quot;), or country codes, and you want flexibility without redefining the schema when new values appear.</p><h3>What is Enum?</h3><p>Enum is a <strong>fixed-length, dictionary-encoded</strong> type that maps string values to integers. Unlike LowCardinality, it’s strictly defined in the schema.</p><p><strong>Example:</strong></p><pre>CREATE TABLE posts (<br>PostTypeId Enum8(<br> &#39;Question&#39; = 1,<br>  &#39;Answer&#39; = 2,<br> &#39;Wiki&#39; = 3<br>  )<br>) ENGINE = MergeTree</pre><p>ClickHouse stores the corresponding integer (e.g., 1 for &#39;Question&#39;) in place of the string, making it extremely compact and fast to compare.</p><p><strong>Typical use case:</strong> When you have a <strong>fixed, controlled set of values</strong> such as status codes, post types, or flags.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_eOZ0BLb_eUKZGW5y3iTCA.png" /></figure><h3>When Not to Use</h3><ul><li><strong>Don’t use </strong><strong>LowCardinality for high-cardinality strings like UUIDs, URLs, or email addresses — the dictionary overhead will hurt performance.</strong></li><li><strong>Avoid </strong><strong>Enum if your data has unknown or evolving values. You’ll need to manually alter the schema every time new values appear.</strong></li></ul><h3>Best Practices</h3><ul><li>Use Enum for strict categoricals that don’t change — e.g., status, type, level</li><li>Use LowCardinality(String) for <strong>semi-structured</strong> or <strong>user-defined</strong> labels where you want some optimization but also flexibility.</li><li>Always benchmark: depending on your query patterns and dataset size, performance may differ.</li></ul><h3>Conclusion</h3><p>LowCardinality(String) and Enum are both effective tools for optimizing repeated string values in ClickHouse — but they serve different needs.</p><ul><li>Need strict control and max speed? Use Enum.</li><li>Need flexibility with decent speed? Use LowCardinality.</li></ul><p>Choose wisely based on your data’s structure and evolution needs.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TI6XtOer0iArZLzuWjNoEQ.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9c09b26467d9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Junk Dimensions in Data Warehousing: Concept, ETL, and Usage]]></title>
            <link>https://medium.com/@sathishdba/junk-dimensions-in-data-warehousing-concept-etl-and-usage-48bf3a08d001?source=rss-369246c21caf------2</link>
            <guid isPermaLink="false">https://medium.com/p/48bf3a08d001</guid>
            <dc:creator><![CDATA[sathish kumar srinivasan]]></dc:creator>
            <pubDate>Thu, 12 Jun 2025 07:04:05 GMT</pubDate>
            <atom:updated>2025-06-12T07:04:05.553Z</atom:updated>
            <content:encoded><![CDATA[<p>In dimensional modeling, a common challenge is how to handle miscellaneous flags and low-cardinality indicators that don’t belong in a major dimension. A <strong>junk dimension</strong> solves this by consolidating these attributes into a single dimension, keeping the fact table lean and the model manageable.</p><h3>📌 What Is a Junk Dimension?</h3><p>A <strong>junk dimension</strong> is a structure that combines unrelated low-cardinality attributes — usually flags, indicators, or small enums — into a single dimension table. These attributes do not belong logically to other dimensions and are often not worth creating separate dimensions for.</p><h3>Why Use a Junk Dimension?</h3><p>Without junk dimensions, you’d face two options — both poor:</p><ul><li>Store each flag in the fact table (bloats the fact table).</li><li>Create a dimension per flag (results in many tiny, inefficient tables).</li></ul><p>Instead, you group them.</p><h3>🧠 Real-World Example: Order Transactions</h3><p>Assume a transactional source provides the following for each order:</p><ul><li>is_returned: Yes/No</li><li>promotion_applied: Yes/No</li><li>payment_method: Cash, CreditCard, Online</li><li>delivery_type: Standard, Express</li></ul><p>These fields:</p><ul><li>Are low-cardinality</li><li>Don’t belong in product, customer, or date dimensions</li><li>Are too small to justify individual dimension tables</li></ul><h3>🧱 Design of the Junk Dimension</h3><p><strong>Table: </strong><strong>dim_order_flags</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/932/1*IFXyIi-IUR_LjlcSncVn9w.png" /></figure><ul><li>Each row represents a unique combination of the flags.</li><li>A <strong>surrogate key</strong> junk_dim_id is generated.</li><li>This ID is stored in the <strong>fact table</strong>.</li></ul><h3>🔄 ETL Process to Load a Junk Dimension</h3><h4>Step 1: Extract Unique Combinations from Source</h4><pre>-- Staging: load raw data<br>SELECT DISTINCT <br>    is_returned,<br>    promotion_applied,<br>    payment_method,<br>    delivery_type<br>FROM staging_order_transactions;</pre><h4>Step 2: Check for Existing Rows in dim_order_flags</h4><pre>-- Assume we store the above result in a temp table: staging_flags<br><br>SELECT s.*<br>FROM staging_flags s<br>LEFT JOIN dim_order_flags d<br>  ON s.is_returned = d.is_returned<br> AND s.promotion_applied = d.promotion_applied<br> AND s.payment_method = d.payment_method<br> AND s.delivery_type = d.delivery_type<br>WHERE d.junk_dim_id IS NULL;</pre><h4>Step 3: Insert New Combinations</h4><pre>INSERT INTO dim_order_flags (is_returned, promotion_applied, payment_method, delivery_type)<br>SELECT <br>    is_returned,<br>    promotion_applied,<br>    payment_method,<br>    delivery_type<br>FROM (<br>    -- same join as above<br>) new_combinations;</pre><h3>🧾 ETL: Loading the Fact Table</h3><p>Once the junk dimension is built and populated, load the fact table by joining on it:</p><pre>INSERT INTO fact_order_transactions (<br>    order_id,<br>    order_date,<br>    customer_id,<br>    product_id,<br>    amount,<br>    junk_dim_id<br>)<br>SELECT<br>    st.order_id,<br>    st.order_date,<br>    st.customer_id,<br>    st.product_id,<br>    st.amount,<br>    d.junk_dim_id<br>FROM staging_order_transactions st<br>JOIN dim_order_flags d<br>  ON st.is_returned = d.is_returned<br> AND st.promotion_applied = d.promotion_applied<br> AND st.payment_method = d.payment_method<br> AND st.delivery_type = d.delivery_type;</pre><h3>📊 Using Junk Dimensions in Reporting</h3><p>In a BI tool or SQL report, you can join the fact table with dim_order_flags using junk_dim_id to filter or group data based on flags:</p><pre>SELECT <br>    f.order_date,<br>    d.payment_method,<br>    d.delivery_type,<br>    SUM(f.amount) AS total_sales<br>FROM fact_order_transactions f<br>JOIN dim_order_flags d<br>  ON f.junk_dim_id = d.junk_dim_id<br>GROUP BY <br>    f.order_date,<br>    d.payment_method,<br>    d.delivery_type;</pre><h3>🧩 Key Considerations</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/875/1*Zw4EbNd7b-yD0UmmtBbxKA.png" /></figure><h3>🧪 Pro Tip: Pre-generate All Possible Combinations</h3><p>If you have static flag values, consider generating all possible combinations in advance during ETL initialization. This removes the need for runtime insert logic and ensures deterministic foreign key joins.</p><h3>✅ Summary</h3><p>A <strong>junk dimension</strong> is an efficient, normalized way to manage miscellaneous low-cardinality flags. It simplifies your data model, avoids bloated fact tables, and keeps reporting performant.</p><p><strong>When used right</strong>, it’s a powerful tool for dimensional modelers.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=48bf3a08d001" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>