A file lands in your inbox called customers_final_v3.csv. You open it in Excel and three things are quietly wrong. The phone numbers have dropped their leading zeros. One row has slid a column to the right. A birthday shows up as the number 43891. Nothing crashed, so nothing looks broken, but the data is now wrong. Every one of those glitches traces back to a single thing you have not learned yet, which is how the data was written down before it ever reached you.

Part 4 treated a spreadsheet as a small calculation engine. This part goes underneath the surface. Before you write a line of SQL in Part 6, you need a clear picture of four things: what a table really is, what a data type is and why a column insists on one, what actually sits inside a CSV file, and why a real database spreads information across many small linked tables instead of one enormous sheet. Get this model straight and the SQL that follows will read like plain sentences instead of a puzzle.

A table is a spreadsheet with rules

A table looks like a spreadsheet: a grid of rows and columns. The vocabulary is just more precise. Each row is a record, one complete thing you are tracking, such as a single customer or a single order. Each column is a field, one attribute of that thing, such as the customer name or the order total. The top row is a header that names every column. So far this is any sheet you have ever seen.

The difference is the rules. In a plain spreadsheet you can type a word into a cell that mostly holds numbers and nobody stops you. In a database table every column is declared up front with a name and a data type, and the table refuses anything that does not fit. A column defined to hold dates will reject the text hello. That strictness feels like a straitjacket at first, and it is exactly what keeps a million-row table trustworthy. The table cannot fill with the silent text-in-a-number-column mess that Part 4 warned you about, because it will not accept it in the first place.

Data types, and why the column cares what you put in it

A data type is a promise about what lives in a column and how the computer stores it underneath. Pick the right one and everything downstream behaves. Pick the wrong one and you get the mangled birthday from the opening. There are only a handful you need on day one, and they fall into four plain families: whole numbers, decimal numbers, text, and things that are neither, such as true-or-false and dates.

The type matters for three concrete reasons. It controls sorting: stored as text, the value 9 comes after 100 because text sorts character by character, while as an integer 9 correctly comes before 100. It controls math: you can average a column of integers, but not a column of text that merely looks like numbers. And it controls storage and range, because a column typed for small whole numbers uses less space and rejects a value too big to fit. The table below lists the types you meet first, using the names PostgreSQL gives them, since PostgreSQL is the database this series writes SQL against from Part 6 on.

The core PostgreSQL types. Sizes and ranges are from the PostgreSQL documentation. integer is the everyday default for whole numbers.

One rule worth burning in now: store money as numeric, never as real or double precision. The approximate types round in the fifteenth digit, and a fraction of a cent multiplied across a million rows becomes a real discrepancy an auditor will find. Exact decimals cost a little speed and save you that argument. The decision path below covers the everyday choice, and when you are torn between text and something stricter, pick the stricter type so the table can guard the column for you.

flowchart TD
  A[What is in the column?] --> B{Whole count?}
  B -->|Yes| C[integer]
  B -->|No| D{Money or exact decimal?}
  D -->|Yes| E[numeric]
  D -->|No| F{Only true or false?}
  F -->|Yes| G[boolean]
  F -->|No| H{A calendar day?}
  H -->|Yes| I[date]
  H -->|No| J[text]

What is actually inside a CSV file

CSV stands for comma separated values, and the format is exactly as plain as it sounds. Open one in a text editor instead of Excel and you see the truth: it is ordinary text, one record per line, with the fields on each line separated by commas. The first line is usually the header. There is no hidden machinery, no types, no formatting, just characters and commas. The rules were finally written down in 2005 as RFC 4180, the closest thing CSV has to an official standard.

Because there are no types, every value in a CSV is really just text until some program decides what to make of it. That single fact explains the opening disaster. The leading zero on a phone number vanishes because Excel reads 007 as the number seven and drops the padding. The birthday turns into 43891 because a spreadsheet stores dates as a count of days and shows you the raw count when it guesses wrong. The row that slid sideways almost always means a value contained a comma, like a city written as Pune, Maharashtra, and nothing told the reader that this particular comma was part of the text rather than a field separator.

RFC 4180 has an answer for that last one: any field that contains a comma, a line break, or a quote should be wrapped in double quotes, and a real quote inside the field is written by doubling it. So the Pune, Maharashtra value becomes a single quoted field and the reader keeps it in one column. Good exporters do this automatically. The trouble starts when a file is built by hand or by a sloppy script that forgets the quoting, which is more common than you would like.

Why one giant table is a trap

You could keep every fact in one wide sheet: one row per order, with the customer name, email, and full address copied onto every single order that customer places. It works until it does not. A regular buyer with forty orders now has her address written forty times. She moves, and you have to find and fix all forty, and you will miss one. Storage bloats, and worse, the copies disagree, so you end up with two versions of the truth and no way to know which is right. This is the update problem, and it is why serious data is not kept in one giant table.

The fix is to split the data into several narrow tables, each about one kind of thing, and connect them. Customers go in a customers table, one row each. Orders go in an orders table, one row each. The order does not repeat the customer address; it just stores a customer_id that points at the matching row in the customers table. The address is written once. When the customer moves, you change one row and every order automatically reflects it, because the orders never held a copy in the first place.

Two pieces of vocabulary make this work. A primary key is a column whose value is unique for every row and never empty, so it names each row without ambiguity; in the customers table that is customer_id. A foreign key is a column in one table that holds a primary key value from another table, wiring the two together, so the customer_id sitting in the orders table is a foreign key pointing back at the customer. The database can even enforce that every foreign key matches a real customer, a guarantee called referential integrity, so you cannot record an order for a customer who does not exist. Keys are the relational in relational database, and joining tables on them is the whole subject of Part 7.

CSV, spreadsheet, or database, which one should hold your data

These three are not rivals so much as tools for different stages. A CSV is the universal shipping container: every tool on earth can read and write it, which makes it perfect for moving data between systems, but it holds no types, no formulas, and no relationships, so it is a poor place to actually work. A spreadsheet is the fast workbench for a human: types are loose but present, formulas and pivots are instant, and it is hard to beat for a quick look at up to a few hundred thousand rows. A database is the warehouse: strict types, real relationships, many users at once, and comfortable with millions of rows, at the cost of needing SQL to talk to it.

The practical ceiling matters more than people expect. A single Excel worksheet stops at 1,048,576 rows, and long before you reach that the file turns slow and fragile. A CSV has no built-in row limit, but opening a large one in a spreadsheet drags it back under the same ceiling and can silently truncate whatever does not fit. A database does not blink at those volumes. The rule I give beginners is simple: explore and prototype in a spreadsheet, ship and store in a database, and use CSV only to move data between the two.

Treat every CSV as untyped text until you say otherwise

If you remember one habit from this part, make it this: a CSV knows nothing about types, so never trust a tool to guess them for you. Import deliberately, set your ID and phone columns to text, store money as numeric and long identifiers as text, and keep the strict types a database gives you rather than fighting them. The strictness you resist as a beginner is the same strictness that lets a table hold ten million clean rows without rotting.

You now have the mental model under the data: tables with typed columns, CSV files as plain untyped text, and databases that split facts across linked tables joined by keys. That is exactly the picture SQL was built to work on. Part 6 puts it to use with your first real queries, SELECT, WHERE, and ORDER BY, to pull specific rows out of a table on demand. If any of the table-and-column vocabulary here felt shaky, reread the opening of this part and glance back at the clean-data warnings in Part 4 before you go on. Keep the Data Analyst guide open as your map.

This week, open any CSV you have in a plain text editor, not a spreadsheet, and read the raw commas for yourself. Seeing the untyped text with your own eyes is the fastest way to make everything above stick.

References

• Chapter 8. Data Types, PostgreSQL Documentation
• RFC 4180, Common Format and MIME Type for CSV Files
• Excel specifications and limits, Microsoft Support
• Primary and foreign keys, IBM Documentation

About The Author

Dr. Pranay Jha

Dr. Pranay Jha is a Cloud and AI Consultant with 18+ years of experience in hybrid cloud, virtualization, and enterprise infrastructure transformation. He specializes in VMware technologies, multi-cloud strategy, and Generative AI solutions. He holds a PhD in Computer Applications with research focused on Cloud and AI, has published multiple research papers, and has been a VMware vExpert since 2016 and a VMUG Community Leader.

See author's posts