Data Types Every Analyst Must Know (Numbers, Text, Dates, Booleans)

What You Will Learn

• The core data types used by SQL, Python, and spreadsheets
• How storage type differs from analytical type (from lesson 03)
• Why storing a number as text causes silent bugs
• How dates are actually stored (and why it matters)

Why This Topic Matters

In lesson 03 you classified data by what kind of value it represents. In this lesson we go one level deeper: how the computer stores that value. Getting the storage type wrong is one of the most common and most frustrating beginner mistakes — your SUM returns 0, your sort puts "10" before "2", or your date filter silently matches nothing. All of these trace back to type errors.

The Five Types You Will Meet Everywhere

These five cover 95% of what you will encounter. There are more types (binary blobs, JSON, arrays), but you do not need them as a beginner.

Integer vs Decimal — When It Matters

Money is the classic trap. If you store ₹499.95 as an integer, it becomes ₹499 or ₹500 — you lose 95 paise on every transaction. Across a million transactions, that is ₹9,50,000 lost.

Why not just use float for everything?

Because floats cannot represent most decimal fractions exactly. Try this in Python:

>>> 0.1 + 0.2
0.30000000000000004

The result is not 0.3. The error is tiny, but in financial calculations it compounds. That is why databases offer DECIMAL(10,2) — a fixed-precision number that always rounds correctly to 2 decimal places. For money, always use DECIMAL, never FLOAT.

Text — More Than Just "Words"

Text type (string) holds any characters: words, sentences, IDs, phone numbers, even JSON. Two things beginners miss:

1. Numbers stored as text behave like text. If amount is text, then sorting gives "1, 10, 100, 2, 20" (alphabetical) instead of "1, 2, 10, 20, 100" (numeric). And SUM returns 0 or an error.
2. Whitespace matters. "Mumbai" and "Mumbai " (with a trailing space) are different strings. They look identical but will not match in a filter. Always trim whitespace when cleaning data — we cover this in lesson 27 — Handling Missing Values and Duplicates.

Boolean — The Two-Value Type

Booleans store True or False. They are perfect for yes/no questions:

• is_active — is the customer account currently active?
• is_returned — was this order returned?
• has_subscribed — has the user opted in to emails?

Many datasets store booleans as 0/1 or "Y"/"N" instead of True/False. They mean the same thing but use different storage. Always check which convention your dataset uses.

Dates — The Most Misunderstood Type

Dates look like text (2026-08-23) but they are stored as numbers internally. Most systems count days (or seconds) from a fixed starting point. For example, Excel counts days since 1900-01-01; Unix systems count seconds since 1970-01-01.

Why this matters

• You can do math on dates. '2026-08-23' - '2026-08-01' gives 22 days. You cannot do this if the date is stored as text.
• Date formats vary by country. 08/09/2026 means August 9 in the US but September 8 in most of the world. Always use ISO format YYYY-MM-DD to avoid ambiguity.
• Time zones destroy data. A timestamp stored as "2026-08-23 14:30:00" without a timezone is meaningless — 14:30 in Mumbai is a different moment from 14:30 in London. Use UTC whenever possible, and store the timezone explicitly if you must keep local time.

Step-by-Step: Choosing the Right Type

When designing a column, ask these questions:

1. Is it a count of whole things? → Integer
2. Is it money or a measurement? → Decimal (or float if precision is not critical)
3. Is it a label, name, or free text? → Text
4. Is it yes/no, true/false? → Boolean
5. Is it a moment in time? → Date or DateTime (with timezone)

Worked example: an orders table

Common Mistakes

1. Storing numbers as text because they came in that way from a CSV. Always convert numeric columns to a numeric type before analyzing.
2. Using FLOAT for money. Compounding rounding errors will eventually cause a mismatch in totals.
3. Storing dates as text in a non-ISO format. You lose the ability to subtract dates or filter by date range.
4. Assuming booleans are always True/False. They are often 0/1, "Y"/"N", or "yes"/"no". Confirm the convention in each dataset.
5. Ignoring timezones. If your data spans multiple regions, naive timestamps will silently misalign events.

Practical Exercise (5 minutes)

Here is a CSV row:

order_id,customer,amount,items,paid,ordered_at
4521,Anita,620.50,3,yes,2026-08-23 14:30:00

Write down the correct storage type for each column. Then check your answers against the worked example above. (For paid, the value is "yes" — it would be cleaner as a Boolean, but if your source uses "yes"/"no" strings, you can either keep them as text or convert to boolean during cleaning.)

Mini Challenge

Open any CSV file you can find (search "sample CSV" online if you do not have one). For each column, guess the type. Then open it in Excel or Google Sheets and check whether the tool guessed the same type. Tools often get it wrong — especially with phone numbers, pin codes, and dates. Spotting those mistakes is a real analytics skill.

Key Takeaways