Flat, Normalized or Unstructured

Module 2 in the chapter Understanding Your Data Structures, chapter 3 of the Data 360 Architect course. Each module stands on its own. Previous: Know It Before It Comes In · Next: What Makes It Unique

The question this module answers: What shape is your data, and what does each shape mean on its way into Data 360?

Before you bring a source in, ask the simplest question about it: what shape is it? Is it flat, is it normalized, is it something nested like JSON, or is it free text? Each shape comes into Data 360 differently, and each one needs a different decision before the first stream is built.

The Data Architect course covers these shapes in depth: normalization in Modeling Data for Meaning, and the shapes beyond tables in Beyond Relational. Here we look at what each one means for Data 360, with real data.

The data we’ll use

The airports in this course come from OurAirports, an open data set of the world’s airports, runways and radio frequencies. Its terms are simple: “All data is released to the Public Domain.” It’s the data I loaded into my own Data 360 org (chapter 8), and into a larger aviation project of my own. The copy in this course is SkyData’s OurAirports v1.0 (opens in a new tab), a dated snapshot taken on 2026-11-09 that never changes, so every row and count in this module matches the files you download. The radio frequencies file came in a later version, v1.1, so the frequency counts here come from v1.1.

We’ll follow one airport through it: Toronto Pearson.

Flat: one row per thing

airports.csv is flat. One row per airport, 86,350 rows, 19 columns. Here’s Toronto Pearson, with nine of the columns:

id	ident	type	name	iso_country	iso_region	municipality	iata_code	keywords
1990	CYYZ	large_airport	Toronto Pearson International Airport	CA	CA-ON	Toronto	YYZ	YTO, Toronto International Airport, Malton

From OurAirports, public domain.

Flat is the easiest shape to bring in. Data 360 takes delimited text files such as CSV, and Parquet files from cloud storage such as Amazon S3. But even a flat file has things to notice before you build the stream:

• Not every column is filled. Most of the world’s airfields have no IATA code, the three-letter code you see on a bag tag: iata_code is blank on 77,297 of the 86,350 rows. A field that’s usually empty can’t identify anything, and it changes what you can match or segment on.
• A list can hide in one cell. keywords holds several values separated by commas: other names and old codes for the same airport. Data 360 sees one text value. If you’ll ever need to search or match on one of those names, that’s a decision now: leave it as text, or split it into rows of its own.
• One row, one thing, but which thing? A row here is an airport. That sounds obvious until you look at the next file.

The pilots file from chapter 1 is flat too: one row per pilot, with their email, years flying and flying role.

Normalized: things that point to each other

An airport has several runways and many radio frequencies, so OurAirports keeps them in their own files, each row pointing back to its airport. And each airport points up to its region and its country. That’s normalized: each fact stored once, in the place it belongs, joined by a key.

Here’s the chain for Toronto Pearson:

File	Rows in the file	Toronto’s rows	Points to its parent by
countries.csv	249	CA, Canada	(top of the chain)
regions.csv	3,987	CA-ON, Ontario	iso_country = CA
airports.csv	86,350	1990, CYYZ	iso_region = CA-ON
runways.csv	48,412	5 runways	airport_ref = 1990 and airport_ident = CYYZ
airport-frequencies.csv	30,395 (SkyData’s OurAirports v1.1)	16 frequencies	airport_ref = 1990 and airport_ident = CYYZ

Toronto’s five runways, with the columns that matter here:

id	airport_ref	airport_ident	length_ft	width_ft	surface	le_ident	he_ident
234792	1990	CYYZ	11120	200	ASP/CONC	05	23
234794	1990	CYYZ	9697	200	ASP	06L	24R
234793	1990	CYYZ	9000	200	ASP	06R	24L
234796	1990	CYYZ	11050	200	ASP	15L	33R
234795	1990	CYYZ	9088	200	ASP	15R	33L

From OurAirports, public domain. The file has 20 columns; eight are shown.

Three things to see.

Every runway points to its airport twice: by a number, airport_ref 1990, and by a code, airport_ident CYYZ. Both work in this file. Which one you join on is a key decision, and module 3 makes it.

A runway row holds both ends of the runway. Runway 05 and runway 23 are the same strip of concrete, seen from opposite ends. The file keeps one row per runway, with a set of le_ fields for the low end and he_ fields for the high end: heading, elevation, position, and more. That’s fine if you think of a runway as one thing. If you need to work with each end on its own, such as the end in use for a departure, you’d split each row into two. Normalization isn’t only a rule. It’s a decision about what you’ll use.

The keys look like numbers, but some aren’t. ident codes such as 00A and 00AA start with a zero: 1,092 of them. In regions.csv, 525 local codes start with a zero, such as 02. Read as a number, 02 becomes 2, and it no longer matches anything. That’s why I bring keys and foreign keys in as text, even when they look like numbers.

Data 360 keeps this shape. Each file comes in as its own stream and lands in its own data lake object. The airports, the runways and the frequencies are joined later: in transforms, as chapter 7 joins countries, regions and airports, or through relationships between data model objects, as chapter 8 relates airports and runways. For that join to work, the key has to match on both sides, character for character. So the question before you bring in normalized data is: what points to what, and by which key?

Semi-structured: JSON

Flights are different. There are many flight-data APIs out there, free and paid. For my own project, SkyOrbita, I chose AeroDataBox (opens in a new tab), a commercial one. Its data is licensed to me, not mine to share, so you won’t find its files or its proprietary details in this course. If you want live flights of your own, you can sign up for it, or for any other flight API, with your own key. Ask an API like that for an airport’s arrivals, and it sends back JSON: a list of flights, each one nested.

To show you that shape with data you can check, here’s one real flight from BTS’s August 2026 data on SkyData (opens in a new tab), written as JSON the way an API might send it. The values are BTS’s; the nesting is ours:

{
  "flightDate": "2026-08-14",
  "airline": { "code": "F9" },
  "flightNumber": "3871",
  "tailNumber": "N318FR",
  "departure": {
    "airport": { "iata": "SLC", "name": "Salt Lake City International Airport" },
    "scheduledLocal": "1247",
    "actualLocal": "1441"
  },
  "arrival": {
    "airport": { "iata": "PHX", "name": "Phoenix Sky Harbor International Airport" },
    "scheduledLocal": "1330",
    "actualLocal": "1543",
    "delayMinutes": 133
  },
  "delayCauses": [
    { "cause": "Carrier", "minutes": 4 },
    { "cause": "National Air System", "minutes": 19 },
    { "cause": "Late aircraft", "minutes": 110 }
  ]
}

One flight, F9 3871 from Salt Lake City to Phoenix on 2026-08-14, from BTS’s data on SkyData (airport names from OurAirports). The delay causes are as reported to BTS.

JSON is called semi-structured because it carries its own structure: objects inside objects, and lists. Here, the airline, the departure and the arrival are each an object inside the flight, the airports are objects inside those, and the delay causes are a list.

Notice what’s missing. There are only three causes, but BTS has five (carrier, weather, national air system, security, late aircraft). The two with no minutes simply aren’t in the list. A field that’s sometimes there and sometimes not is normal in JSON, and it becomes a blank column the moment you make a table.

Data 360 works in rows and columns, so somewhere that structure has to become a table. You get two choices.

Flatten it before you send it. If you push data in through the Ingestion API, Salesforce’s schema rules say: “Objects can’t have nested objects.” So each nested field becomes a plain column, named by its path:

In the JSON	As a flat column
flightNumber	flight_number
airline.code	airline_code
departure.airport.iata	dep_airport_iata
departure.scheduledLocal	dep_scheduled_local
arrival.airport.iata	arr_airport_iata
arrival.delayMinutes	arr_delay_minutes

That’s exactly the shape BTS publishes: one flight, one row, with columns such as Origin, Dest, CRSDepTime and ArrDelay. BTS has done the flattening for you. My own project does the same thing to the API’s JSON before it sends a flight to Data 360.

Or land it as a string and flatten it inside. A batch data transform has a Flatten JSON step that turns JSON stored in one field into rows and columns. Salesforce explains why that matters: “to use data in downstream features, such as identity resolution and segmentation, the data must be stored as rows and columns.” It handles JSON up to six levels deep, and it needs a schema file that describes the JSON. Chapter 7 covers transforms.

Either way, look for the lists. An API’s response is usually a list itself: one call, many flights, and each one becomes a row. And inside each flight here, the delay causes are a list: one flight, up to five causes. That can’t be one column on the flight. Each cause becomes a row of its own, pointing back to the flight. (BTS solves it the other way: five columns, one per cause, most of them empty.) Spot the lists in your JSON before you bring it in. Each one is a table you’ll have to design.

Unstructured: free text and documents

Last, the text that doesn’t have a structure of its own.

OurAirports has one: airport-comments.csv, comments members write about airports. The comments are written by real people, and some name other members and people, so SkyData doesn’t copy that file; you can get it from OurAirports itself (opens in a new tab). For the course, SkyData publishes a synthetic stand-in in the same layout: airport-comments-synthetic (opens in a new tab), 1,000 comments by invented members about 174 real airports, where every fact about an airport is taken from a real file and the row says which one. Each comment has a few structured fields (an ID, the airport, a date, a subject) and then a body of free text:

id	airportIdent	date	subject	body
CMT-000188	KPHX	2025-12-11 12:00:00	Field info	First solo cross-country today. Nervous, but fine.⏎Tower frequency is 118.7 MHz.

A row from SkyData’s synthetic comments (v1.0; the member is invented, the tower frequency is Phoenix’s real one, from OurAirports). ⏎ marks a line break inside the text.

That line break is the first thing to catch. SkyData’s file has 1,000 comments, and 207 of them have line breaks inside the body. Count the lines in the file and you get 1,466, not 1,001. OurAirports’ own file has the same problem at a larger size: in a copy from 2026-05-12, 3,387 of its 16,189 comments had line breaks. Salesforce Help is direct about this: “files containing newline characters and multiline text columns are not supported.” So this file can’t come in as it is, through a file-based stream. You clean the line breaks out before it comes in, or you send it a different way. That’s a decision you make by opening the file before you build the stream, not after the first load fails.

The second thing: the file also has each member’s nickname. That’s about a person, and nothing we’d do with comments needs it. It stays out (module 5). Personal data can hide in the free text too: 30 of SkyData’s comments end with an invented email address or phone number, put there on purpose, so you can practice finding and masking them in chapter 17.

Then there are documents: PDFs, web pages, manuals. Our aviation data has them too: SkyData has started a synthetic set of airport notices: member advisories about real airports, from an invented travel platform to its own members. Each one is clearly labelled synthetic and not for navigation, and every fact in it is computed from a real file, here the BTS flights and OurAirports. The first one is about a real day at Phoenix: > SYNTHETIC SAMPLE: Sky Data. Not for navigation or operations. Not a NOTAM.

Member advisory: weather cancellations at PHX, Friday, 26 September 2025

On Friday, 26 September 2025, 221 of the 981 scheduled flights to and from Phoenix Sky Harbor International Airport (PHX) were cancelled: 22.5% of the day’s schedule. BTS records weather as the cause for 217 of them.

An excerpt from SkyData’s synthetic notice SDN-PHX-D-20250926 (airport-notices-synthetic, v1.0-preview (opens in a new tab); the issuer is invented, the counts are real, from BTS On-Time). The same notice comes as PDF, Word, HTML, Markdown and plain text, and v1.0 will contain it unchanged.

Its manifest describes the same document as fields: doc_id SDN-PHX-D-20250926, airport_ident KPHX, doc_type disruption advisory, issued_date 2025-09-26, key_figure flights cancelled, key_value 221. Hold on to that: the document is for reading and searching, and the few values you’d want to count on are already fields beside it.

Documents like these don’t have rows at all. Data 360 handles them differently. An unstructured data lake object doesn’t hold the content as rows; it holds references to the files. It maps to an unstructured data model object. When you build a search index on it, the content is split into chunks and indexed, and that’s how Data 360 grounds AI answers. That’s chapter 18.

For text and documents, the question to ask is simple: are you bringing them in to be searched, or to be counted? If you need to count or segment on something inside them, like the date a comment was written or an expiry date in a document, that’s a field, and it belongs in structured data. Data Architect’s chapter on going from structured to unstructured covers that choice.

Click to enlarge

What an architect decides here

• The shape of each source, written down before its stream is built.
• For flat files, which columns are mostly blank and which cells hide a list.
• For normalized data, which key joins each pair, and that it matches on both sides, as text.
• For JSON, whether to flatten it before sending or inside Data 360, and which lists become tables of their own.
• For free text, whether line breaks have to be cleaned first, and whether it’s there to be searched or whether something inside it should be a field.

Explore

All checked 2026-11-14.

• OurAirports: Open data (opens in a new tab), the airport files and their terms.
• AeroDataBox (opens in a new tab) (checked 2026-11-14), the flight-data API SkyOrbita uses; one of several. Its terms apply to anyone who signs up.
• Supported File Formats in Data 360 (opens in a new tab), Salesforce Help.
• Requirements for Ingestion API Schema File (opens in a new tab), Salesforce Developers.
• Flatten JSON Data (opens in a new tab), Salesforce Help.
• Data Objects in Data 360 (opens in a new tab), Salesforce Help: data lake objects, including unstructured ones.
• SkyData: Understanding Your Data Structures (opens in a new tab), this chapter’s data files.
• SkyData: synthetic airport notices v1.0-preview (opens in a new tab), the data card and its notice.