If you work with data long enough, you’ll eventually face the classic “Why does my number look weird?” problem.

You format a number beautifully in your visualization tool — for example:

8.40

But when you export the data to CSV, it suddenly becomes:

8.400000000023

If this has happened to you — welcome to the club. It’s a floating-point precision issue, and it’s surprisingly common in SQL and modern analytics tools like Power BI, Tableau, Looker, Mode, and Snowflake dashboards.

In this post, we’ll break down why this happens and how to fix it, along with practical data-type tips & conversion examples for SQL and BI layers.

---

Why Numbers Sometimes Show Unexpected Decimals

This issue occurs when:

• A number is stored as text/string
• Then converted to a floating-point numeric type
• And later exported or processed without formatting rules

Why?

Floating-point values (FLOAT/DOUBLE) use binary precision — great for performance, but they can’t always represent decimal values exactly.

So 8.4 might be stored internally like 8.40000000002317.

In BI tools, formatting hides it.
In raw CSVs, formatting is lost → the “ugly” value appears.

---

Real-World Scenario

You received data where numbers are stored as string:

Revenue (string)
“8.40”
“12.30”

You convert it in your BI tool:
STRING → FLOAT → FORMAT(decimal)

Looks perfect…
Until someone exports the underlying data → surprise float expansion.

This happens a lot when importing:

• CSVs
• Google Sheets
• Excel files
• API payloads
• Legacy systems storing numbers as text

---

Best Practice: Convert Data Types in SQL, Not Visualization Layer

SQL data warehouse rule:

Clean your data before it reaches the dashboard.

Instead of converting string to float in the BI tool, convert in SQL:

✅ Convert String → Decimal

SQL Server

SELECT CAST(Revenue AS DECIMAL(10,2)) AS Revenue
FROM Sales;

Snowflake

SELECT TO_DECIMAL(Revenue, 10, 2) AS Revenue
FROM Sales;

PostgreSQL

SELECT CAST(Revenue AS NUMERIC(10,2)) AS Revenue
FROM Sales;

MySQL

SELECT CAST(Revenue AS DECIMAL(10,2)) AS Revenue
FROM Sales;

✅ If data source is fixed and string can’t be changed

Convert & round:

SELECT ROUND(CAST(Revenue AS FLOAT), 2) AS Revenue

---

When You Must Convert in the BI Tool

Sometimes you can’t edit the database (common for analysts!).

Power BI (DAX)

Revenue = ROUND(VALUE([RevenueString]), 2)

Tableau

Create calculated field:

ROUND(FLOAT([RevenueString]), 2)

Looker / LookML

dimension: revenue {
  sql: CAST(${table}.revenue_string AS DECIMAL(10,2)) ;;
}

Excel / Google Sheets Layer (for CSV exports)

Force text formatting with:

=TEXT(A1, "0.00")

---

Why FLOAT Causes Trouble (and What to Use Instead)

Data Type	Good For	Bad For
FLOAT/DOUBLE	Large data, ML, scientific calc	Currency, finance, KPIs, dashboards
DECIMAL/NUMERIC	Finance, metrics, currency	Extreme precision needs
STRING	Display-only	Math! Sorting! Exporting numerics!

Rule:
✅ Use DECIMAL for business numbers
❌ Avoid FLOAT for currency or KPIs

---

Bonus Tips: Avoid Surprises

1. Enforce schema in data warehouse

Don’t trust upstream sources blindly.

2. Validate data types in ETL pipelines

Data coming from APIs often arrives as text — normalize early.

3. Test your export workflows

A dashboard looking nice ≠ data being clean.

4. Document expected data types

Helps engineers and analysts stay aligned.

---

Quick Fix Checklist ✅

Problem	Fix
String numbers → float exports messy	Convert to DECIMAL in SQL
BI formatting lost on CSV export	ROUND() in SQL or calc layer
Finance data rounding errors	Always use DECIMAL/NUMERIC
Unexpected float expansion	Never store numbers as text

---

Conclusion

Floating-point precision bugs aren’t your fault — they’re a side effect of how computers store numbers.

What matters is knowing how to handle them:

• Store numbers as DECIMAL, not string or float
• Convert types in SQL, not only in BI tools
• Use ROUND() consistently
• Validate results when exporting to CSV

This will save you debugging time and avoid confusion for stakeholders — especially when handling financial or KPI data.