DEV Community: Arpit Bangre

Why Using FLOAT for Financial Pipelines is a Silent $100k Trap (and How PostgreSQL NUMERIC Saves Your Ledger)

Arpit Bangre — Tue, 08 Sep 2026 09:27:41 +0000

Here is a simple SQL query that should return 0.3:

SELECT 0.1::FLOAT4 + 0.2::FLOAT4;

In PostgreSQL, MySQL, and most relational SQL engines, the result is:

0.30000001192092896

If you calculate sales tax, loan interest, or wallet balances across 10,000,000 transactions a day, those tiny fractional drifts accumulate into real cash discrepancies during month-end ledger reconciliation.

🔍 Why Does Binary Floating-Point Drift Happen?

Hardware Implementation: Modern computer CPUs represent FLOAT and DOUBLE PRECISION using binary floating-point numbers (IEEE 754 standard).
Base-2 vs. Base-10 Math: In base-10, fractions like 0.1 (1/10) and 0.2 (2/10) look clean and simple. But in base-2 binary, 0.1 is an infinite recurring fraction:

   0.000110011001100110011... (binary)

Because hardware registers have finite bits (32-bit for FLOAT4, 64-bit for FLOAT8), the value is truncated, introducing a tiny approximation error on every calculation.

⚙️ How PostgreSQL NUMERIC Works Under the Hood

Unlike FLOAT, PostgreSQL's NUMERIC (or DECIMAL) data type does NOT use IEEE 754 binary floating-point hardware representation.

┌────────────────────────────────────────────────────────────────────────┐
│ PostgreSQL NUMERIC Internal Memory Representation                      │
│ 1. Header (4 Bytes): Sign, weight, display scale, digit count          │
│ 2. Digits Array: Stores exact base-10000 integer chunks (0000 to 9999) │
│ ➔ 100% Exact Arbitrary-Precision Base-10 Arithmetic                    │
└────────────────────────────────────────────────────────────────────────┘

It stores exact decimal digits in memory using base-10000 arithmetic.
There is ZERO floating-point drift.
10.50 + 20.25 is always 100% exactly 30.75.

💡 The Senior Data Engineer Production Standard

When designing production DDL schemas for transactional, warehousing, or financial pipelines:

Never use FLOAT, REAL, or DOUBLE PRECISION for:
- Product pricing (unit_price)
- Account balances (wallet_balance, available_funds)
- Tax & GST calculations (tax_amount, discount_rate)
Always enforce NUMERIC(precision, scale):

   CREATE TABLE customer_orders (
       order_id        BIGINT PRIMARY KEY,
       customer_id     INT NOT NULL,
       order_amount    NUMERIC(12, 2) NOT NULL CHECK (order_amount >= 0.00),
       created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
   );

precision = 12: Total digits allowed (supports up to ₹9,999,999,999.99 / ~999 Crore).
scale = 2: Exactly 2 digits reserved after the decimal point (paise/cents).

🏆 Key Takeaway for System Design & Interviews

Feature	`FLOAT` / `REAL`	`NUMERIC` / `DECIMAL`
Storage Engine	Hardware IEEE 754 Binary	Software Base-10000 Digit Array
Calculation Speed	Extremely fast (native CPU ALU)	Slightly slower (software math)
Precision	Approximate (drifts on fractions)	100% Exact (penny-perfect)
Best Use Case	Machine learning embeddings, GPS coordinates, physics simulations	Financial ledgers, billing, invoices, banking, e-commerce

💡 What is your team's strict rule for financial columns in production DDL? Drop your thoughts below!

💼 Let's connect: linkedin.com/in/arpitmbangre

SQL DATEDIFF Internals: Why 2 Seconds Can Equal 1 Full Day (Boundary Traps)

Arpit Bangre — Fri, 04 Sep 2026 12:00:13 +0000

DATEDIFF(DAY, '2026-08-31 23:59:59', '2026-09-01 00:00:01') returns 1 day.

Only 2 seconds passed in real life.

Yet the SQL engine says: "1 day difference."

Why? Because this is NOT a bug. It is 100% by architectural design.

Here is the deep relational engine truth that trips up data engineers in production pipelines.

🔍 The Engine Mechanics: Boundary Lines vs Elapsed Time

Most engineers assume DATEDIFF calculates elapsed chronological time.

It does NOT.

DATEDIFF counts how many calendar boundary lines were crossed between two timestamps:

23:59:59 (Day 1)  ─────────| MIDNIGHT BOUNDARY |─────────>  00:00:01 (Day 2)
                             [+1 Boundary Crossed]

Between 11:59:59 PM and 12:00:01 AM, exactly one midnight boundary was crossed.
Result: DATEDIFF(DAY) = 1.

The exact same rule applies to YEARS:
DATEDIFF(YEAR, '2025-12-31 23:59:59', '2026-01-01 00:00:01') returns 1 YEAR, even though only 2 seconds passed!

💥 Where This Silently Corrupts Production Pipelines

SLA Monitoring: A pipeline running from 11:59 PM to 12:02 AM looks like it took a full 24-hour day.
Financial Interest & Billing: Charging a customer for a full day of rental or interest for a 5-minute transaction.
User Retention & Churn Analytics: Falsely computing consecutive-day streaks for users active at 11:58 PM and 12:02 AM.

🛠️ The Senior Fix: How to Measure True Elapsed Time

1. SQL Server & Snowflake Fix (Second-Level Precision):

-- ❌ DANGEROUS: Counts boundaries crossed, breaks SLAs
SELECT DATEDIFF(DAY, order_timestamp, delivery_timestamp) AS days_taken
FROM orders;

-- ✅ THE ARCHITECT FIX: Calculate exact elapsed seconds and convert
SELECT 
    order_id,
    order_timestamp,
    delivery_timestamp,
    -- Exact fractional days elapsed based on 86,400 seconds/day
    ROUND(DATEDIFF(SECOND, order_timestamp, delivery_timestamp) / 86400.0, 2) AS true_elapsed_days,
    -- Exact hours elapsed
    ROUND(DATEDIFF(SECOND, order_timestamp, delivery_timestamp) / 3600.0, 2) AS true_elapsed_hours
FROM orders;

2. PostgreSQL Native Interval Arithmetic:

PostgreSQL calculates true intervals natively:

-- 🐘 PostgreSQL returns exact elapsed interval (e.g. 00:00:02)
SELECT 
    order_id, 
    (delivery_timestamp - order_timestamp) AS true_elapsed_interval,
    EXTRACT(EPOCH FROM (delivery_timestamp - order_timestamp)) / 86400.0 AS true_elapsed_days
FROM orders;

🎯 Senior Architect Takeaway

DATEDIFF answers: "How many boundary lines did we step over?"

It does NOT answer: "How much time actually ticked on the clock?"

Always know your engine's date arithmetic rules before designing SLAs, churn metrics, or billing aggregations.

💼 Connect on LinkedIn: linkedin.com/in/arpitmbangre

Window Functions Demystified: ROW_NUMBER vs RANK vs DENSE_RANK (Execution Internals)

Arpit Bangre — Tue, 01 Sep 2026 07:00:02 +0000

In high-scale enterprise data engineering, ranking events, deduplicating records, and calculating leaderboards are everyday tasks.

While ROW_NUMBER(), RANK(), and DENSE_RANK() look similar on the surface, choosing the wrong one can corrupt your financial aggregations or deduplication pipelines.

Here is the exact execution breakdown and when to use each.

🔍 The 3 Ranking Functions at a Glance

Function	Tie Handling	Gaps in Sequence?	Primary Production Use-Case
`ROW_NUMBER()`	Assigns arbitrary distinct sequence (1, 2, 3, 4)	❌ No Gaps	Strict Deduplication & Pagination
`RANK()`	Assigns same rank to ties, skips next (1, 2, 2, 4)	⚠️ Has Gaps	Competition Leaderboards & Percentiles
`DENSE_RANK()`	Assigns same rank to ties, no skips (1, 2, 2, 3)	❌ No Gaps	Finding N-th Highest Salary / Top Tiers

💻 Visual SQL Execution

Imagine an Employees table with salaries: [100k, 90k, 90k, 80k].

SELECT 
    EmployeeID,
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS row_num,
    RANK()       OVER (ORDER BY Salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS dense_rnk
FROM Employees;

📊 Result Set:

Salary | ROW_NUMBER | RANK | DENSE_RANK
-------+------------+------+------------
100k   |     1      |  1   |     1
90k    |     2      |  2   |     2   <-- Tie!
90k    |     3      |  2   |     2   <-- Tie!
80k    |     4      |  4   |     3   <-- Notice RANK jumped to 4, DENSE_RANK went to 3!

⚠️ The #1 Production Interview Trap: "2nd Highest Salary"

If you use RANK() or LIMIT 1 OFFSET 1 to find the 2nd highest salary, and multiple employees tie for the 1st highest salary (e.g. two people earn 100k):

RANK() = 2 returns 0 rows!
DENSE_RANK() = 2 guarantees the correct 2nd highest salary every single time.

WITH RankedSalaries AS (
    SELECT 
        EmployeeID,
        Salary,
        DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
    FROM Employees
)
SELECT Salary 
FROM RankedSalaries 
WHERE rnk = 2;

🏆 Summary Rule for Data Engineers

Need to delete duplicates? -> Always use ROW_NUMBER() = 1.
Need Top N distinct salary tiers? -> Always use DENSE_RANK().
Need competition rankings with point ties? -> Always use RANK().

💡 Have you ever run into a tie-ranking bug in production? Drop your thoughts below!

💼 Let's connect: linkedin.com/in/arpitmbangre

Subqueries vs CTEs: Query Optimizer Internals & Memory Spooling Explained

Arpit Bangre — Sat, 29 Aug 2026 06:38:48 +0000

Many engineers believe Common Table Expressions (CTEs) are always faster than subqueries.

In modern SQL Server (and PostgreSQL), that is a myth. Here is what actually happens under the hood:

1. Inlining & The Query Optimizer

By default, the SQL optimizer treats standard CTEs and derived tables (subqueries) almost identically:

The engine expands both into the same relational tree.
They generate the exact same execution plan and I/O cost.

-- Pattern A: Derived Table (Subquery)
SELECT DeptID, EmpName, Salary
FROM (
    SELECT DeptID, EmpName, Salary,
           DENSE_RANK() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS rnk
    FROM Employees
) RankedData
WHERE rnk <= 2;

-- Pattern B: Common Table Expression (CTE)
WITH RankedData AS (
    SELECT DeptID, EmpName, Salary,
           DENSE_RANK() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS rnk
    FROM Employees
)
SELECT DeptID, EmpName, Salary 
FROM RankedData 
WHERE rnk <= 2;

2. When CTEs Truly Win:

Readability & Pipeline Stacking: You can chain 5 CTEs sequentially without deeply nested pyramid brackets.
In-Place Deduplication: In SQL Server, you can run DELETE directly on a CTE, and it deletes duplicate rows straight from the real underlying table!

WITH DuplicateCleaner AS (
    SELECT CustomerID, Email,
           ROW_NUMBER() OVER (PARTITION BY Email ORDER BY RegistrationDate ASC) AS rn
    FROM Customers
    WHERE Email IS NOT NULL
)
DELETE FROM DuplicateCleaner 
WHERE rn > 1; -- ✅ Clean in-place deletion!

3. The Big Trap (Spooling Overhead):

If you reference the same CTE multiple times in a query (e.g. CTE_A JOIN CTE_A), SQL Server may execute the underlying CTE query multiple times or create a Lazy Spool in tempdb.

-> Fix: For heavy multi-million row reuse, use a Temporary Table (#TempTable) with an explicit Clustered Index instead!

💡 How do you choose between CTEs, Temp Tables, and Subqueries in your pipelines?

💼 Connect on LinkedIn: linkedin.com/in/arpitmbangre

SQL Execution Order Internals: Why WHERE Fails on Aliases but ORDER BY Succeeds

Arpit Bangre — Fri, 28 Aug 2026 07:08:52 +0000

Ever wondered why this query fails in SQL?

SELECT department_id, COUNT(*) AS emp_count
FROM employees
WHERE emp_count > 5 -- ❌ Error: Invalid column name 'emp_count'
GROUP BY department_id;

The 6-Stage Execution Engine:

FROM & JOIN — Load source tables & evaluate join conditions
WHERE — Filter raw rows before grouping
GROUP BY — Aggregate rows into buckets
HAVING — Filter aggregated buckets
SELECT — Compute expressions & column aliases
ORDER BY — Sort final output

Why It Fails:

Because WHERE executes at Stage 2, the emp_count alias created at Stage 5 (SELECT) does not exist in memory yet!

However, ORDER BY runs at Stage 6 (after SELECT), which is why ORDER BY emp_count DESC works seamlessly.

How to Fix:

Use HAVING for aggregated filtering:

SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

💡 What's your favorite SQL execution order quirk? Drop your thoughts below!

💼 Let's connect: linkedin.com/in/arpitmbangre