The Dictionary Before You Map
Tento obsah zatím není dostupný ve vašem jazyce.
Module 4 in the chapter Understanding Your Data Structures, chapter 3 of the Data 360 Architect course. Each module stands on its own. Previous: What Makes It Unique · Next: Don’t Bring In Everything
The question this module answers: What goes in a data dictionary for a Data 360 source, and why build it before the first stream?
I’ve done entire Service Cloud implementations where I don’t even touch Salesforce. Every field, every picklist, the assignment rules, the escalation rules, the milestones: I map everything in a spreadsheet, get it all dialed in, and then plug it into the org. It catches things. And as I got into Data 360, I found it almost requires that you do this mapping ahead of time.
What a data dictionary is
Section titled “What a data dictionary is”A data dictionary is a list of every field in your data, one row per field, that says what the field means, what type of value it holds, which values are allowed, where it comes from and where it goes. It’s written for people: the architect, the admin who builds the stream, the analyst who builds the segment, and whoever has to fix it in two years. If someone asks “what does closed mean in the runways file, and what values can it have?”, the dictionary answers in one row.
A dictionary as a design tool
Section titled “A dictionary as a design tool”The Data Architect course covers the data dictionary as a record of what data means: definitions, sources and owners. Here I use it for something else: as the place where the design happens, before anything is built.
The idea is simple. A shared spreadsheet, one row per field. For each field, you write down what it means, what type it is, where it’s going and why you need it. You don’t open Data 360 until the sheet is right.
And I keep one habit on every project. When someone adds a field to the design, it gets flagged yellow. It stays yellow until it actually exists: built in the dev org and in source control. Then the color clears. At any moment, the yellow rows are the difference between the design and what’s built. You catch the deltas very quickly.
That sheet has saved me more than once. It has caught wrong data types, missing keys, and keys missing their source prefix (chapter 3, module 3), all before anything was built. In Salesforce core, a dictionary like this is a lifesaver. In Data 360 it matters even more, because everything is so concrete. Find a problem in a data model object, and you may have to go all the way back to the stream, with every mapping on the way dropped and recreated. So you need to see these things ahead of time.
First, every feed: its structure and a sample
Section titled “First, every feed: its structure and a sample”Before the first row of the dictionary, list every feed that will come in: every file, every API, every org. Then get two things for each one from whoever produces it.
- Its structure. The layout of the file, the schema of the API, the fields of the object: whatever says what’s supposed to be in it.
- A sample of real data. Not a made-up example. Real rows, enough of them to see the variety: the blanks, the odd spellings, the values nobody mentioned.
Then lay the sample against the structure. Is every field in the structure actually in the sample? Is there anything in the sample the structure doesn’t mention? Does a field called a date hold dates, all in one format? Does the field that’s supposed to be unique repeat? The structure tells you what the producer meant. The sample tells you what you’re really getting. Where they disagree, ask before you design, not after the first load.
Then, for each feed, answer a few questions once, at the top of its part of the dictionary:
| Question | The pilots file | The flights |
|---|---|---|
| Who produces it? | An outside pilot records system, as a file in Amazon S3 | US airlines report every flight to the Bureau of Transportation Statistics (BTS), which publishes them by month |
| Where’s the structure? | The file’s header row and the producer’s note (SkyData’s card) | BTS’s column guide, and SkyData’s card |
| Do we have a real sample? | Yes, the whole file: 1,000 pilots | Yes, a whole month: 29,524 flights at PHX, August 2026 |
| How often does it come? | Once a day | Once a month, after the month is over |
| Every record each time, or only changes? | Every pilot, every time | A new month’s flights each time |
| How is a row uniquely identified? | pilot_id | Four values together: date, airline, flight number, origin (module 3) |
| Is it a time series: one row for each thing that happens, at a point in time? | No | Yes |
| If it’s a time series, which date never changes? | None: airlines reschedule flights, sometimes weeks ahead (so flights are other data, chapter 6) |
A feed sheet for this course’s two sources. Both files are on SkyData (opens in a new tab): the pilots are synthetic, the flights are real.
Most of these answers have a module of their own: the key in module 3, every record or only changes in chapter 4. The last one needs a closer look.
Dates: which ones never change
Section titled “Dates: which ones never change”If a feed is a time series, one row for each event, then one of its dates has a special job. In Data 360, a time series of what people do comes in as engagement data, and an engagement stream needs an event time field: the date that says when the event happened (chapter 6, module 3). That date has to be immutable: once the record exists, it never changes.
Salesforce is direct about why: “Using a mutable date field causes duplication of records where multiple records with the same primary key get added to the data lake object (DLO).” And you can’t fix it later: “The Event Time Field isn’t editable after initial setup.”
So in the dictionary, every date field gets one more answer: does it change after the record is written? Here are the flight’s dates and times:
| Field | What it holds | Changes after the record is written? | Can it be the event time? |
|---|---|---|---|
FlightDate | The day of the flight, 2026-08-14 | Yes, if the flight is moved to another day | No |
CRSDepTime | Scheduled departure, local time, hhmm | Yes: airlines reschedule, sometimes more than once, weeks ahead | No |
DepTime | Actual departure, local time, hhmm | Yes, in a live feed: blank until the plane leaves | No |
ArrTime | Actual arrival, local time, hhmm | Yes, in a live feed: blank until it lands | No |
From BTS’s August 2026 flights on SkyData.
One more thing the file teaches. BTS’s own record layout says FlightDate is written yyyymmdd. The file says 2026-08-02. Same lesson as the airport types in chapter 7: check the data, not only the documentation, and write down what you actually found.
BTS publishes a month only after it’s over, so nothing in this file changes any more. But design for the feed, not the file. In a live feed, the actual times fill in as the day goes on and change while a flight is delayed, and even the schedule moves: airlines reschedule flights, sometimes two or three times, weeks or months ahead. No flight date holds still. That decides the category in chapter 6: in this course, flights are other data, and their dates are ordinary fields.
And look at what the scheduled time is missing. It’s local time, written hhmm, with no time zone. (And in the actual times, BTS writes midnight as 2400, never 0000: chapter 6.) Salesforce’s rule for dates and times (below) means you can’t send it as it is. You build a proper date and time from three things: the date, the scheduled time, and the origin airport’s time zone. PHX is easy: Arizona stays on UTC−7 all year. Most US airports change for daylight saving time. That’s a field you make, so every departure can be read in one time, and it gets a row of its own (below).
The pilots file isn’t a time series, and it has no date at all. That’s a gap worth writing down: with no “last changed” date, a daily file can’t tell you which pilots changed today. Ask the producer for one now, while it’s a question in a spreadsheet.
And if a time series has no date that never changes? Salesforce’s tip: “If you have a time-series dataset and you don’t have an immutable date field, choose the Other category.” That’s our flights. For a source about people’s activity, ask the producer for one.
The columns for a Data 360 source
Section titled “The columns for a Data 360 source”For a source on its way into Data 360, the dictionary needs a few columns a core org project doesn’t. Here’s the pilots file from chapter 1:
| Source field | What it means | Data 360 type | Key | Sensitive? | Lands in (DLO field) | Maps to (DMO field) | Used for |
|---|---|---|---|---|---|---|---|
| pilot_id | The pilot, as the file’s owner identifies them, PLT-000238 | Text | Primary | No | pilot_id | Individual ID | Every load, every delete |
| first_name, last_name | The pilot’s name | Text | Yes | first_name, last_name | Individual: first and last name | Matching, display | |
| Match | Yes | Contact Point Email | Identity resolution | ||||
| phone | Phone, +15205550138 | Phone | Match | Yes | phone | Contact Point Phone | Identity resolution |
| home_airport | The home airport’s IATA code, PHX | Text | Foreign, to the airport | No | home_airport | (stays on the lake for now) | Which airport’s Account the contact belongs to |
| flying_role | What kind of flying, Airline pilot | Text | No | flying_role | Individual: flying role (custom) | The enrichment | |
| highest_certificate | The highest pilot certificate held | Text | No | highest_certificate | (stays on the lake for now) | Segments, later | |
| years_flying | Whole years | Number | No | years_flying | Individual: years flying (custom) | The segment; the enrichment | |
| record_source | Says the row is synthetic, and which generator made it | Text | No | record_source | (stays on the lake) | Tracing |
A dictionary for SkyData’s pilots file (synthetic). The custom fields on the Individual are this course’s example.
Every column is a decision from this chapter. The type comes from module 1. The key column comes from module 3. Sensitive? is module 5. Lands in and Maps to are the mapping you’ll build in chapters 6 and 8, decided here first. And Used for is the question that keeps fields out: if nobody can fill it in, the field doesn’t come in.
A real one: the runways file
Section titled “A real one: the runways file”Here’s one for a file somebody else made, with no generator to tidy it: the OurAirports runways file from modules 2 and 3, 48,412 runways and 20 columns in SkyData’s v1.0. I’ve added two columns this time: Values, what’s actually in the field, and Decision.
| Source field | What it means | Values in the file | Data 360 type | Key | Decision | Used for |
|---|---|---|---|---|---|---|
| id | The runway’s own ID | 234792 | Text | Primary | Bring in | Every load, every delete |
| airport_ref | The airport’s ID | 1990 | Text | Foreign, to the airport | Bring in | The relationship to the airport |
| airport_ident | The airport’s code | CYYZ | Text | Bring in | Display and search | |
| length_ft | Length in feet | 11120 | Number | Bring in | Segments: airports with a runway over 10,000 ft | |
| width_ft | Width in feet | 200 | Number | Bring in | Not yet known | |
| surface | What the runway is made of | 667 different values, and blanks (see below) | Text | Bring in, clean in a transform | Segments by surface | |
| lighted | Lit for night use | 1 or 0 | Boolean? (see below) | Bring in | Segments: lit runways | |
| closed | Closed | 1 or 0 | Boolean? (see below) | Bring in | Leave closed runways out | |
| le_ident, he_ident | The two ends’ names | 05 and 23 | Text | Bring in | Display | |
| le_ and he_ latitude, longitude, elevation, heading, displaced threshold | Where each end is, and how it’s laid out | 43.67, -79.66, 564, 47, 135 | Number | Leave out | Nothing yet |
A dictionary for the OurAirports runways file (public domain), for this course. “Used for” is this course’s example.
Filling in the Values column means opening the file and looking, and that’s where the real file starts talking.
The surface column has 667 different values. Asphalt alone is ASP on 11,396 runways, ASPH on 1,681, Asphalt on 272 and asphalt on 133. Turf is TURF, Turf and turf. Concrete is CON, CONC and concrete. 510 runways have no surface at all. Nothing in Data 360 will complain: they’re all valid text. But a segment of “asphalt runways” built on ASP misses more than 2,000 of them. The dictionary is where you notice, and where you decide: a transform that maps each spelling to one value (chapter 7), and a list of the allowed values written in the row.
lighted and closed say 1 and 0. That looks like a yes or no, so Boolean is the natural type. But Salesforce Help says a Boolean’s “Possible values are ‘true’, ‘false’, and blank.” A 1 isn’t one of them. So it’s a decision, made here, not discovered after the load: test a sample to see what a 1 becomes, bring the field in as a number, or turn it into true or false on the way in. Help also says a Boolean can’t be a primary key or a record modified field, which doesn’t matter here, but would for a different field.
Ten fields stay out. The two ends’ positions, elevations and headings are good data, and nobody has a use for them yet. Bringing them in is easy, but every field that comes in is one more field to type, map, explain and keep. They stay in the file, and in the dictionary, marked “Leave out.” The day someone needs them, the row is already written.
Fields that aren’t in the source
Section titled “Fields that aren’t in the source”Some fields in your dictionary won’t come from the source at all. For the flights, a few are made on the way in:
| Field | Where it comes from |
|---|---|
| Flight_Key | Made: four values joined into one key, 2026-08-14_AA_336_DFW (module 3) |
| sched_dep_at | Made: FlightDate plus CRSDepTime, in the origin airport’s time zone, so departures can be compared in one time. A field, not an event time (chapter 6) |
| source_file_name | Made: which month’s file the row came from (see below) |
This course’s design for BTS’s flights on SkyData. BTS already supplies the delays (DepDelay, ArrDelay) and their causes, so we don’t work them out.
So add one more column: Comes from. “The source” for most rows; for the rest, how it’s made. A field that’s worked out needs its rule written down, because six months later, the scheduled departure time will be in a dashboard, someone will ask what time zone it’s in and what happened to the flights at 2400, and the answer has to be in one place. And where it’s made is a design decision too: before it’s sent, as here, or in Data 360 with a formula field or a transform (chapters 6 and 7).
Which file did this row come from?
Section titled “Which file did this row come from?”Six months in, someone finds a strange value on one record and asks: which file did it come from? Data 360 adds a few fields of its own to every lake object (chapter 6, module 5), and one of them, Data Source Object, names “the object where the record came from, such as the name of a cloud storage file.” For a stream that reads one fixed file, that answers it. When one stream picks up a new file every day, it may not say which day’s file. I haven’t tested whether it then holds each file’s own name or the stream’s pattern, and I wouldn’t design on either.
The sure way is to ask whoever makes the file to write it into every row. I put three of these in the dictionary for any file source, with “Comes from: the producer”:
| Field | What it holds | Why |
|---|---|---|
| source_file_name | The name of the file this row was in | Trace any row back to its file |
| batch_id | One ID for each run that made a file | Find, count or remove everything from one bad run |
| extracted_at | When the producer read the record from its system | Tell which version of a record is newest |
SkyData’s files carry a cousin of these: record_source on every synthetic row, and a dated version on every file. These columns are for tracing, not for the key. Put the file name into the primary key and the same record sent again in a new file becomes a second row (module 3). Keep them out of the key, unless every file’s rows are meant to be kept.
Why the type column matters
Section titled “Why the type column matters”When you create a data stream, Salesforce Help says, “Data 360 attempts to detect the data type of each field.” Attempts. It’s a good starting point, not a decision. In my org it read years flying as a number, which was right, and the flight number as a number too, which wasn’t: you never add flight numbers up (chapter 7). Module 1’s years flying problem is what happens when nobody decides. The dictionary is where you decide the type yourself, before the stream suggests one.
Salesforce’s own description of the types shows why it’s worth the effort:
- Numbers: “If an ingested number is out of the supported range or is a non-numerical value, a null value is stored.” A years flying of “twelve” doesn’t fail. It becomes blank.
- Dates and times: “A valid datetime must include the time part and time zone. If they’re not included, it’s inferred as 00:00:00 UTC.” A departure sent as a date alone, with no time and no zone, becomes midnight UTC, which in much of the world is the day before.
- Phone and email: “Data 360 doesn’t validate the format of the phone number.” If you need clean phone numbers for matching, you clean them before they come in, or in a transform.
- Mapping: “The data type of the DLO field must match the data type of the DMO field,” with one exception: a Number can map to Text. So the type you pick at the lake has to suit the model you’ll map it to.
None of these make a load fail. They make it quietly wrong, which is worse. A dictionary with a type column, checked against a sample of the real file, catches them while they’re still a cell in a spreadsheet.
Read it right to left
Section titled “Read it right to left”One last habit. When I review a dictionary, or a finished mapping, I like tracing it from right back to left, so I get to trace the keys and the mappings. Start at the Used for column, then the DMO field, then back to the source. If a row can’t be traced all the way back to a real field in the real file, something is missing. If a source field has nothing on its right, ask why it’s coming in at all.
Start your own: it grows with the course
Section titled “Start your own: it grows with the course”Why carry it through a whole course? Because so much of what you create in Data 360 can’t be undone. You’ve met the rules already, in Salesforce’s words. “After a DLO or DMO is created, you can’t change the data type of any fields.” A data stream’s category can’t be changed after it’s saved, and “the Event Time Field isn’t editable after initial setup.” Fields can’t be deleted from a lake object, only disabled. And the names you give streams and lake objects are the names everything downstream uses. Every one of those is a decision you make once, on the day you create the stream. The dictionary is where you make it on purpose, with the whole picture in front of you, instead of in a hurry on a setup screen. And it’s how you find out what depends on a field before you touch it.
From here on, this course builds one dictionary with you. Start it now, for the three feeds you’ve met: the pilots, the flights and the airports. Use the columns above: what each field means, its type, its key, whether it’s sensitive, and what it’s used for. A sheet is fine.
Every chapter after this adds to it. Chapter 4 adds how each feed arrives. Chapter 6 adds the category, the event time and the lake object’s fields. Chapter 7 adds the transforms, and chapter 8 the model it all maps to. Each chapter ends with a short step to update yours. SkyData publishes the dictionary as it should look at the end of each chapter, so you can check your work: start from its blank dictionary template (opens in a new tab), and compare yours with the chapter 3 edition (opens in a new tab). By the end of the course, it’s a complete map of everything you built, from each file to the model. The Data Dictionary reference page explains the method, including how a dictionary grows from a sheet into a model in Git.
Watch it on video
Section titled “Watch it on video”In 2024 I filmed the data dictionary I use to plan a design before I set anything up in Salesforce: a shared sheet with every object and field, its type, its purpose, and whether it’s built yet. That video was about the core platform, and in it I said that Data Cloud, now Data 360, “almost requires” this kind of mapping ahead of time. This chapter, and the dictionary you’ll grow through the course, is that same habit, carried into Data 360. What’s changed is how much it covers. In 2024 it described the Salesforce objects I was going to build. In Data 360 it has to describe everything that comes in, whatever its shape: objects, flat files, pivoted tables, JSON, free text. And it’s now something AI tools can read and work from too, which is why my own moved from a sheet to YAML files in Git. The Data Dictionary reference page tells that story. The note under the video says what’s still right and what’s changed since.
What an architect decides here
Section titled “What an architect decides here”- Who owns the dictionary, and that nothing is built in Data 360 that isn’t in it first.
- For every feed: its structure and a real sample, received and compared before the design starts.
- For every feed: how a row is uniquely identified, every record or only changes, and whether it’s a time series.
- For every time series: the date that never changes, or the decision to make it Other.
- The type of every field, decided and checked against real sample data, not left to detection.
- The allowed values for each coded field, and where the different spellings are cleaned.
- For every field that’s made or worked out, its rule, and where it’s made.
- Which columns the producer writes into every row so any record can be traced to its file and run.
- Which fields have no use yet, and stay out until they do.
Explore
Section titled “Explore”All checked 2026-11-14.
- Category (opens in a new tab), Salesforce Help, checked 2026-11-14: time-series data and the immutable event time field.
- Data Lineage Fields (opens in a new tab), Salesforce Help: the fields Data 360 adds about where a record came from.
- Data Types in Data 360 (opens in a new tab), Salesforce Help.
- OurAirports: Open data (opens in a new tab), the runways file (public domain).
- SkyData: Understanding Your Data Structures (opens in a new tab), this chapter’s data files.
- SkyData: dictionary template (CSV) (opens in a new tab), a blank dictionary to start your own.
- SkyData: data dictionary, chapter 3 edition (opens in a new tab), to check your dictionary against.