Welcome: why TDM exists and the data basics you need

Everything a TDM tool does is done to data: it finds data, cuts it down, hides parts of it, makes more of it, and moves it. That takes a small, firm vocabulary about how data is organised: tables, keys, files and basic SQL.

These ideas return constantly. Subsetting, masking and generation all have one rule in common: keep the relationships between records intact. You cannot follow that rule if you do not know what a relationship is.

1.20 Words to know first

Data

Raw facts, such as "1001", "Asha" and "560001", before anyone says what they mean.

Information

Data with meaning: "customer 1001 is Asha, living in postal area 560001".

Database

An organised store of data that a program can search and change reliably.

Table

A grid of data about one kind of thing, like one sheet in a spreadsheet.

Row / record

One item in a table: one customer, one order.

Column / field

One attribute of every row: first name, phone, order date.

Primary key (PK)

A column (or columns) whose value is unique for every row and identifies it.

Foreign key (FK)

A column whose values point to the primary key of another table.

Referential integrity

The rule that every foreign key value must match an existing primary key value.

Schema / metadata

The description of the data (table names, columns, types, keys) as opposed to the data itself.

Flat file

A plain file holding records as lines of text, with no built-in database engine behind it.

SQL

The language used to ask a relational database questions and to change it.

1.21 Data and information

Look at this: 1001 | Asha | Menon | 560001. On its own it is just data: four values. You can guess it is a person, but you are guessing. Add labels (customer_id, first_name, last_name, zip_code) and a table name (CUSTOMERS) and it becomes information: customer 1001 is Asha Menon in postal area 560001.

That distinction matters for TDM because tools often see only data and have to work out the meaning. Discovery (see the discovery and subsetting concepts chapter) is largely the job of recovering meaning: "this column, called col_17, contains phone numbers". Column names, data types and descriptions are the clues.

1.22 Databases and tables

1.22.1 The spreadsheet analogy

If you have used a spreadsheet you already know the idea. A table is one sheet: columns across the top with names, rows below, one item per row. A database is the whole workbook, with several sheets (tables) that belong together.

Here is the ShopSphere CUSTOMERS table:

• Each row (also called a record) is one customer.
• Each column (also called a field or attribute) is one fact about every customer.
• Each cell is one value: the phone number of customer 1002.

Unlike a spreadsheet sheet, a database table enforces each column's declared type, can be linked to other tables by keys, can hold millions of rows, and is queried with SQL (later in this section). ShopSphere uses a relational database (Oracle, SQL Server, MySQL, PostgreSQL and DB2 are examples): tables relate through keys.

1.22.2 Data types

Each column has a data type that says what kind of value it holds. The type controls what is allowed, how much space it takes and how it can be used (you can add numbers but not names).

Notice something about the ShopSphere sample data: phone, ssn and zip_code look like numbers, but they are stored as text. You never add two phone numbers, and a leading zero would be lost if they were numbers. That choice affects how you mask them: a text column can hold dashes (123-45-6789); a numeric column cannot.

1.22.3 Schema and metadata

Metadata means "data about data". For a database it is the list of tables, their columns, each column's name and type, which columns are keys, and which tables relate to which. The collection of all that is the schema.

Here is part of the ShopSphere schema written as a simple description (not the data):

CUSTOMERS
  customer_id    INTEGER       PRIMARY KEY
  first_name     VARCHAR(50)
  last_name      VARCHAR(50)
  email          VARCHAR(100)
  phone          VARCHAR(15)
  ssn            VARCHAR(11)
  date_of_birth  DATE
  zip_code       VARCHAR(10)
  tax_id         VARCHAR(20)

The metadata tells a tool a lot without reading a single row. A column called ssn of type VARCHAR(11) is a strong hint (11 characters fits 123-45-6789). TDM tools therefore read metadata first, then sample the actual data. The Informatica guide describes a TDM repository that stores the metadata you import into Test Data Manager from a source database (User Guide 10.5.10, p.26). The Delphix introduction describes two profiling methods, one scanning column names (metadata) and one scanning the data itself (Introduction to Delphix Masking).

1.23 Keys and relationships

A single table is easy. Real applications split data across many tables and connect them. Keys are the connectors. This section is the most important in the whole chapter.

1.23.1 Why data is split into several tables

Imagine ShopSphere stored everything in one big sheet: one row per ordered item, with the customer's name, phone and address repeated on every row. Customer Asha has two orders with three items in total, so her phone number would be typed three times. If she changes her phone number you must update every copy, and if you miss one the data disagrees with itself.

The fix is to store each fact once, in the table where it belongs, and refer to it from elsewhere. Customer facts live in CUSTOMERS. An order stores only the customer's ID, not the name and phone. To get the name, you look the ID up. The ID is a key.

1.23.2 Primary key

A primary key is a column (or a small group of columns) whose value is unique in every row and is never empty (null). It is the row's identity, like a library card number or a passport number.

• CUSTOMERS.customer_id: 1001, 1002, 1003. No two customers share an ID.
• ORDERS.order_id, ORDER_ITEMS.item_id and PRODUCTS.product_code are the primary keys of their tables.

A database enforces this: it refuses to insert a second customer with ID 1001.

Why not use the name as the key? Two customers can share a name; names change; spellings vary. A key must be stable and unique. That is why tables carry an artificial ID such as customer_id. (Keys made up of real-world data, like an email address, are called natural keys; made-up IDs are surrogate keys. Both appear in the real world.)

A key can have more than one column. Suppose a table ORDER_ITEM_NOTES allowed one note per item per language; then (item_id, language) together would be unique, though neither alone is. That is a composite key.

1.23.3 Foreign key

A foreign key is a column in one table whose values must match the primary key of another table. It is the link.

• ORDERS.customer_id is a foreign key to CUSTOMERS.customer_id: it says which customer placed the order.
• ORDER_ITEMS.order_id is a foreign key to ORDERS.order_id: it says which order this line belongs to.
• ORDER_ITEMS.product_code is a foreign key to PRODUCTS.product_code: it says which product was bought.

The foreign key column is allowed to repeat. Customer 1001 can appear in many order rows, because Asha can place many orders. The primary key side cannot repeat.

1.23.4 Parent and child tables, one-to-many

The table that holds the primary key is the parent; the table with the foreign key pointing at it is the child. One customer (parent) can have many orders (children), and one order can have many items. That shape is called one-to-many.

1.23.5 The linked sample data

Here are the other three ShopSphere tables with fake rows, linked to the customers. Trace the keys with your finger: order 5001 belongs to customer 1001, and its items 9001 and 9002 point to products P100 and P200.

Customer 1003 (Meera) has no orders: a parent with no children is perfectly legal. The reverse is not.

1.23.6 Referential integrity

Referential integrity is the rule that every foreign key value must match a primary key value that exists. Every order must belong to a customer who exists; every item to an order that exists.

A child row whose parent is missing is called an orphan. Suppose someone deletes customer 1001 but leaves orders 5001 and 5002. Those orders now point to nobody. The "my orders" page crashes. When the database has the foreign key declared, it refuses such a delete, and it refuses to insert an order for a customer ID that does not exist. (In SQLite with foreign key enforcement switched on, both attempts are rejected with a foreign key constraint error.)

Each tool has its own way to say this. The Informatica documentation says a data subset retains primary and foreign key relations so that the data stays referentially intact (User Guide 10.5.10, p.23). The Delphix introduction says it scans metadata and data to identify and preserve primary/foreign key relationships so that data is masked the same way across tables and databases (Introduction to Delphix Masking).

1.23.7 Logical keys: keys the database does not know about

Everything above assumed the keys are declared in the database. In real life they often are not. Older systems, performance-minded designs and imported data frequently have tables with no declared primary or foreign keys at all, even though the application treats certain columns as keys. The relationships exist in the developers' heads and in the code, not in the schema.

TDM tools cannot follow a relationship they do not know about. So they let you (or their profilers) describe the relationships separately. Keys defined this way are called logical keys: the tool is told "treat this column as the key" without any change to the real database.

The idea is the same in both: a key you declare to the tool, which the database may know nothing about. Chapters on Informatica subsetting and Delphix rule sets and keys show the screens.

1.24 Flat files

Not all test data lives in databases. Banks, insurers and retailers exchange huge amounts of data as files: one system writes a file overnight, another reads it in the morning. Masking text and .dat files is a common task.

A flat file is a file holding records as lines of text. "Flat" means there is no table engine, no keys, no data types: it is just text in a layout that both sides agree on. All the structure lives in the agreement, not in the file.

1.24.1 .txt, .dat, .csv: what the extension tells you (little)

A file extension is only a label. .txt says "text", .csv says "comma-separated values", .dat says "data". None of them tells you how the records are laid out. A .dat file can be comma-separated, pipe-separated, fixed-width or something else; a .txt file can hold any of those too. You find the layout from the file specification (a document from the team that produces the file), or by opening the file and looking.

1.24.2 Records, fields, headers and footers

• A record is one line (one customer, one order), ending with an end-of-record marker, usually a line break.
• A field is one value within the record.
• A header is a record at the top that is not data: often the column names, or a label with a date.
• A footer (or trailer) is a record at the end, often a count or a total so the reader can check nothing was lost.

1.24.3 Delimited files

In a delimited file a special character, the delimiter, separates the fields. The most famous is the comma, giving the CSV (comma-separated values) format. Pipes (|), tabs and semicolons are also common, especially when data itself contains commas.

Here is a ShopSphere customer file in CSV with a header row. All values are fake:

customer_id,first_name,last_name,email,phone
1001,Asha,Menon,asha.menon@example.com,9876543210
1002,Ravi,Kumar,ravi.k@example.com,9123456780
1003,Meera,Nair,meera.n@example.com,9988776655

And an orders file, .dat extension, pipe-delimited, with a header record (H), data records (D) and a footer record (T) that carries the record count:

H|ORDERS|20260215
D|5001|1001|2026-01-15|1098.00
D|5002|1001|2026-02-03|1499.00
D|5003|1002|2026-02-10|598.00
T|3

Reading it: the first field of each line says what kind of record it is. This is a file with several record types in one file, a layout common in business exchanges. The footer says "3 data records", so a reader can detect a truncated file.

What if the data contains the delimiter? If a last name were "O'Neil, Jr." in a comma file the comma would split it into two fields. The usual answer is an enclosure (also called a qualifier or quote character), commonly the double quote: 1004,Liam,"O'Neil, Jr.",liam@example.com,9000000001. Then a literal quote inside the field needs an escape, often by doubling it. The Delphix rule-set page documents settings for exactly this: an enclosure escaping strategy for delimited files, with a default of "Double enclosure" (the escape character is the same as the enclosure), and an option to use a custom single character (Managing rule sets).

1.24.4 Fixed-width files

In a fixed-width file there is no delimiter. Each field occupies an exact range of character positions, padded with spaces. The layout is defined by position:

The data, with a ruler line on top so you can count columns (the ruler is not part of the file):

1234567890123456789012345678901234
1001Asha      Menon     9876543210
1002Ravi      Kumar     9123456780
1003Meera     Nair      9988776655

Every record is exactly 34 characters long. "Asha" is followed by six spaces so that the last name starts at position 15. Without the layout table you could not tell where "Asha" ends and "Menon" begins.

1.24.5 Line endings

The end of a record is marked by invisible characters that differ by platform. Windows traditionally uses two characters (carriage return then line feed, often written CR+LF); Linux uses one (line feed, LF). If a tool assumes the wrong one, records are read wrongly or every line gets an odd extra character. Delphix asks you to choose the end-of-record, Windows/DOS (CR+LF) or Linux (LF), when you assign a file format, and to enter the delimiter for a delimited file (Managing file formats). It also supports using a control character as the delimiter or end of record, entered in a particular format (Managing rule sets).

1.24.6 Records of different shapes in one file

The orders file above mixed header, data and footer lines. Other files mix different kinds of data record. The Delphix documentation gives an example where some records carry an account number and a postal address while others carry an account number and an email address, told apart by the first field ("address" or "email"). It calls the differentiating field a record type qualifier, supports up to three qualifiers, and says a record gets exactly one record type. It also says that header and footer records can be configured so unmodified copies go into the masked output, and that these features apply only to delimited and fixed-width formats (Managing record types and header/footer records).

The point for you: a file is rarely "just rows". Before touching it, list the record types it contains. Masking a header line as if it were a customer would corrupt it.

1.24.7 Encodings: how text becomes bytes

Computers store characters as numbers; an encoding is the agreed table. Two matter to you:

• ASCII: an old, small table with 128 characters: English letters, digits, punctuation. One byte per character.
• UTF-8: a modern encoding that can represent essentially every writing system, including Tamil, Hindi, Chinese and emoji. It is ASCII-compatible: plain English text looks identical, but other characters take two, three or four bytes.

This causes two classic problems.

1. Garbled text. A file written in one encoding and read as another turns "Müller" into something like "Müller". The data is not lost; it was read with the wrong table.
2. Fixed-width surprises. If a layout counts bytes but a name has an accented letter, "Müller" is 7 bytes in UTF-8 though it is 6 characters. The field after it shifts by one. Always ask whether widths are in bytes or characters.

The Delphix page on control characters says the control-character value supports the UTF-8 character set (Managing rule sets). No encoding-selection setting is described there, so check your version for how it handles encodings.

1.24.8 XML and JSON at a glance

Some files are not line-and-column shaped at all; they are nested. Two formats dominate.

XML wraps values in named tags (for example <firstName>Asha</firstName>); JSON uses braces, names and values:

{
  "customerId": 1001,
  "firstName": "Asha",
  "orders": [
    { "orderId": 5001, "total": 1098.00 },
    { "orderId": 5002, "total": 1499.00 }
  ]
}

In both formats the orders are inside the customer. The parent-child relationship is shown by nesting instead of by a foreign key. For TDM, the thing to remember is that sensitive values (here the name) hide inside the structure, and a tool must know the path to them. The Delphix rule-set page lists source types of Database, Fixed Width, Delimited, JSON, XML and DataSet, and the rule-set creation dialog offers Database, Semi-Structured, Flat Files and Copybook (Managing rule sets). A copybook describes mainframe record layouts.

1.24.9 How the Delphix documentation treats file formats and keys

A short orientation, so that later Delphix chapters feel familiar. All statements below are from the pages linked; details come later in the Delphix rule sets and keys chapter and the Delphix file masking chapter.

• Formats are defined separately from files. You add them under Settings, in a Data Formats area with a tab for File formats and one for Mainframe (Managing file formats).
• A format is shared. Changing a file format's inventory affects every data file that uses that format. This applies to Fixed-Width, Delimited, Mainframe, JSON and XML formats.
• Rule sets group the tables or files a job will work on, and a rule set can be of type database, flat file, semi-structured or copybook (Managing rule sets).
• Keys appear in the rule set. A database table's rule-set row shows a logical key column, and a table without a primary key needs one for in-place masking (see the logical keys section above).
• A filter is a WHERE clause that limits the rows from a table, which is how a rule set does a simple subset. You will see WHERE in the SQL section next.

1.25 Basic SQL you will see

SQL (Structured Query Language, often said "sequel") is the language for asking a relational database questions and changing its data. You will meet it in TDM tools in small doses: a WHERE filter for a subset, a custom query for a table, a check of results. You need to read it, not master it.

The statements below use the ShopSphere tables and sample rows from this section; SQLite is a free way to run them on a laptop. The create-table statements are in the lab at the end of this section. Details such as date handling differ slightly between databases.

1.25.1 SELECT: read data

SELECT chooses columns; FROM names the table.

SELECT first_name, last_name, zip_code
FROM customers;

SELECT * means "all columns". Handy for a quick look, but on a big table it returns everything, which can be slow and can display sensitive columns you did not need.

1.25.2 WHERE: choose rows

WHERE keeps only the rows that satisfy a condition.

SELECT * FROM orders
WHERE customer_id = 1001;

Text values go in quotes; numbers do not. Because zip_code is text in our table, WHERE zip_code = '600017' returns Ravi Kumar. You can combine conditions with AND and OR, and compare with <, >, LIKE and others.

1.25.3 JOIN: combine tables through keys

A join follows a foreign key. Here, each order is matched to its customer by customer_id:

SELECT c.first_name, o.order_id, o.total_amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

c and o are short nicknames (aliases) for the tables. The ON line states the key match. Meera does not appear: she has no orders, and a plain JOIN keeps only matches. A LEFT JOIN keeps every customer and shows empty (null) where there is no order:

SELECT c.first_name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

This returns Asha 5001, Asha 5002, Ravi 5003 and Meera with no order id. You can chain joins to walk the whole chain customer, order, item, product:

SELECT c.last_name, p.product_name, i.quantity
FROM order_items i
JOIN orders o    ON o.order_id = i.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p  ON p.product_code = i.product_code
ORDER BY i.item_id;

The result has four rows: Menon with Wireless Mouse (1), Menon with USB-C Cable (1), Menon with Laptop Stand (1) and Kumar with USB-C Cable (2). A subsetting tool does something like this walk in reverse: start from the customers you chose and follow the keys out to every related row.

1.25.4 COUNT: how many?

SELECT COUNT(*) AS customer_count FROM customers;

Returns 3. With GROUP BY you can count per group:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

Returns 1001 with 2 orders and 1002 with 1 order. TDM people use COUNT constantly: how many rows before and after a subset? Does the masked table have as many rows as the source? Does the number of distinct values in a candidate key equal the number of rows? (COUNT(DISTINCT customer_id) counts the unique values.)

1.25.5 INSERT and UPDATE: change data

INSERT INTO customers (...) VALUES (...) adds a row (the lab below has full examples); the column list and value list must line up. If you inserted an order with customer_id 9999 when no such customer exists, a database that enforces the foreign key rejects it.

UPDATE customers
SET zip_code = '560002'
WHERE customer_id = 1001;

After the UPDATE, a SELECT for customer 1001 returns zip_code 560002. Never write UPDATE without a WHERE unless you really mean to change every row: the statement above, without its last line, would give all three customers the same postal code.

Masking is, at its core, a very large controlled UPDATE: replace the value in a sensitive column in every row (with a consistent rule). Delphix's in-place masking updates the source table; a copy-style approach writes to a different target. The tool generates the SQL for you, but understanding that it is an UPDATE explains why a table with no key is a problem: the tool needs a way to say "this exact row". Delphix's documentation says the custom SQL for a table must include the primary key column(s) and the columns to be masked (Managing rule sets).

1.26 Data volumes and why size matters

ShopSphere's sample tables hold ten rows between them. The real database holds millions of customers and tens of millions of orders. Size changes everything about TDM.

Sizes run from KB (a short text file) through MB (a CSV of tens of thousands of customers) and GB (a small production database) to TB (a large enterprise database).

Why size matters to a TDM engineer:

• Time. Masking updates every row of every sensitive column. Ten times the rows is roughly ten times the work unless you add parallelism. Delphix profile jobs have a setting for the number of parallel streams, for example two streams to run two tables of a rule set at once (Delphix masking terminology).
• Storage. Every environment that holds a full copy multiplies the cost. See the why-TDM chapter.
• Memory and file limits. A file of many gigabytes cannot be opened in a spreadsheet; you work on it with tools or by sampling.
• Test fitness. Performance tests need volume; functional tests do not. Matching size to purpose is part of the job.
• Row count versus width. A table with a hundred million narrow rows and a table with a million rows of large documents behave very differently. Ask for both numbers.

1.27 Hands-on: which keys exist in the ShopSphere files?

This exercise rehearses the real situation from the logical keys section: data arrives as files, no database has declared any keys, and you must work out the relationships. It needs no tool, only reasoning.

ShopSphere's partner system sends three files. None of them states any key.

customers.csv
customer_id,first_name,last_name,email
1001,Asha,Menon,asha.menon@example.com
1002,Ravi,Kumar,ravi.k@example.com
1003,Meera,Nair,meera.n@example.com
1004,Asha,Menon,asha.m2@example.com

orders.dat  (pipe-delimited, no header)
5001|1001|2026-01-15|1098.00
5002|1001|2026-02-03|1499.00
5003|1002|2026-02-10|598.00
5004|1004|2026-02-11|299.00

order_items.txt  (fixed width: item_id 1-4, order_id 5-8, product 9-12, qty 13-15)
90015001P100  1
90025001P200  1
90035002P300  1
90045003P200  2
90055004P200  1

Splitting the first item line by position gives item_id 9001, order_id 5001, product P100 and qty 1 (padded with two spaces to fill its field). Without the layout line above the file, the run of digits at the start would be meaningless. The layout specification is part of the data.

1.28 Common mistakes and gotchas

1.29 Recap