Synthetic data, ETL from scratch, and how ETL meets TDM

Every tool in this guide moves data. Informatica TDM copies a slice of a database, changes some values and writes it somewhere else. Delphix reads a source, masks it and delivers it to a target. GenRocket creates rows and writes them to a database or a file. Underneath, all three do the same three things: get data from somewhere, change it, put it somewhere else. The industry has had a name for that pattern for decades: ETL, which stands for Extract, Transform, Load.

This section covers ETL on its own, without test data management, so the pattern is recognisable wherever it appears. It builds a small but complete ETL job for ShopSphere in plain SQL, then describes the machinery that real ETL tools wrap around those SQL steps: schedulers, logs, error handling and reusable components. The ETL and TDM chapter lays the TDM tools on top. Tables and keys are explained in the data basics chapter (online).

3.15 Words to know first

ETL

Extract, Transform, Load: copy data out of one or more sources, reshape and clean it, and write it into a target.

Staging area

A temporary landing zone, usually a set of tables or files, where extracted data sits unchanged before it is transformed.

Operational database (OLTP)

The database an application uses to run the business: place an order, update an address. Built for many small, fast reads and writes.

Data warehouse

A separate database built for reporting and analysis across lots of history. Built for a few big reads.

Full extract / incremental extract

Reading everything every time, versus reading only what is new or changed since last time.

Watermark

A saved marker, such as "last order id loaded was 5005", that tells the next run where to start.

Change data capture (CDC)

Any technique that detects which rows were inserted, updated or deleted in the source so only those are processed.

Upsert

"Update if the row exists, insert if it does not." Short for update plus insert.

Slowly changing dimension (SCD)

A way of keeping history in a warehouse table when descriptive data (an address) changes over time.

Mapping

The definition of how source columns become target columns, including every transformation in between.

Workflow, session, job

The runnable wrappers around mappings. A job or workflow runs one or more sessions or steps on a schedule.

Mapplet

Informatica's word for a reusable group of transformations that you build once and drop into many mappings.

Metadata

Data about data: table definitions, column types, mapping definitions, run history.

Reject (error) row

A row the job could not process, written aside with a reason instead of stopping the whole run.

ELT

Extract, Load, Transform: load raw data into the target first and transform it there using the target database's own power.

3.16 What ETL is and why it exists

Concept. ETL is a repeatable process that moves data from where it is created to where it is needed, fixing it on the way.

Simple explanation. Think of a school canteen. Vegetables arrive muddy from several farms (extract brings them to the back door), staff wash, cut and combine them in the prep room (transform), and trays go to the serving counter (load). Each stage has its own place and rules.

The analogy stops being accurate here: an ETL job runs many times and must give the same kind of result each time, even if interrupted and restarted. Cooks can improvise; ETL jobs must not.

ETL appears in data warehouses and reports (the classic case), system migrations, keeping two systems in step, feeding analytics, and test data provisioning, the topic of the next chapter.

3.17 Operational database versus data warehouse

ETL usually connects those two kinds of database, so the difference is worth knowing.

A common warehouse shape is the star schema: a central fact table that records events or measurements (one row per order line, with quantity and amount), surrounded by dimension tables that describe the who, what and when (customer, product, date). A fact table is mostly numbers and keys; a dimension table is mostly descriptive text. We will build a tiny star for ShopSphere later in this section.

3.18 The big picture

Here is the whole pattern on one page. Read it left to right, then read the three boxes under the main flow, which sit around the flow and keep it running.

3.19 Extract: getting data out

Concept. Extraction reads data from a source system and hands it to the ETL process, without changing the source.

Why it matters. Extraction is where ETL jobs first meet the real world: slow networks, locked tables, files that arrive late or half-written, and sources that must not be slowed down because customers are using them. A good extract takes only what it needs, takes it consistently, and leaves the source as it found it.

3.19.1 Kinds of source

The Informatica guide shows the same range in test data work: it mentions relational sources, flat files and XSD (XML) sources as things that data subset, masking and generation plans can run on (User Guide 10.5.10, pp.274 to 277). GenRocket's FAQ describes its targets in the same vein (databases, flat files, mainframe formats, NoSQL). GenRocket General FAQ ↗

3.19.2 Full versus incremental extraction

3.19.3 The watermark technique

The simplest incremental method is a watermark: store the highest value you have processed, and next time ask only for rows beyond it. It needs a column whose value only ever grows, such as an increasing order number or a "last modified" timestamp.

ShopSphere's order feed has an increasing order_id, so we keep a small control table with one row per job:

CREATE TABLE etl_control (
  job_name      TEXT PRIMARY KEY,
  last_order_id INTEGER
);
INSERT INTO etl_control VALUES ('orders_daily', 0);

-- Extract only new orders (the feed holds text, so convert for the comparison)
INSERT INTO stg_orders
SELECT 1, o.*
FROM   feed_orders o
WHERE  CAST(o.order_id AS INTEGER) >
       (SELECT last_order_id FROM etl_control WHERE job_name = 'orders_daily');

-- After the whole run succeeds, move the watermark forward
UPDATE etl_control
SET    last_order_id = (SELECT MAX(CAST(order_id AS INTEGER)) FROM stg_orders)
WHERE  job_name = 'orders_daily';

Expected result: on the first run (watermark 0) five orders (5001 to 5005) were staged and the watermark moved to 5005. On the second run, with orders 5006 and 5007 newly added to the feed, only those two were staged and the watermark moved to 5007.

3.19.4 Change data capture at concept level

Change data capture (CDC) is the umbrella name for ways to learn what changed. There are four common approaches. Each product implements them differently, so check your tool's documentation before relying on one.

3.19.5 Good extraction habits

1. Record the run.

   Give every run a batch id and a timestamp, and write both next to the extracted rows. When something looks wrong later, you can say exactly which run brought it in.

2. Read a consistent snapshot.

   If you extract CUSTOMERS and then ORDERS five minutes later, new orders may point at customers you did not capture. Extract related tables in one transaction or point in time, or cut the orders to a time you know customers cover.

   What you should see: every order in staging has its customer in staging, or the job knows why not.

3.20 The staging area

Concept. The staging area is a temporary landing zone between the source and the final target. Extracted data is stored there in nearly the same form as it was found.

Simple explanation. In the canteen, it is the delivery bay where crates are put down before anyone peels anything.

Why it matters. Staging gives you four things:

• A frozen copy. You can transform, check and re-run without hitting the source again.
• Separation of duties. Extraction only reads; transformation only reshapes; neither depends on the other being fast.
• Safe restarts. A failure in the transform step does not need a new extract.

Informatica's plans give you the same idea in their connection options. When you create a plan, you can choose a staging connection, "a connection to a database for staging data", and the default shown comes from the Preferences view (User Guide 10.5.10, p.276). That is a documented example of a staging area as a configured part of a real tool.

3.20.1 How to design staging tables

• Mirror the source, with all columns as text. A staged row should never fail because a date has a strange format. Conversion is the transform step's job.
• Add audit columns. At least batch_id; often a load timestamp and the source file name.
• No keys, no foreign keys, no constraints. Staging must accept bad data so that the transform step can decide what to do with it.
• Empty it each run (or partition by batch). Old staged data is usually not worth keeping beyond debugging.

CREATE TABLE stg_customers (
  batch_id    INTEGER,
  customer_id TEXT,
  first_name  TEXT,
  last_name   TEXT,
  email       TEXT,
  zip_code    TEXT
);

The order feed is staged the same way. Two of its rows are traps handled later: order 5004 has a total of "abc", and order 5005 belongs to customer 9999, who does not exist.

3.21 Transform: cleaning and reshaping

Concept. Transformation turns staged raw data into the clean, consistent, correctly typed shape the target needs.

Why it matters. This is where business rules live. If the transform is wrong, the data is wrong everywhere downstream, and nobody can tell from the target alone. Transformations are therefore the part of ETL most worth documenting and testing.

There are nine everyday kinds of transformation: cleaning, type conversion, mapping, deduplication, lookups, filtering, joining, derived columns and aggregation. We cover each in turn using the ShopSphere staging rows from the previous section. Each subsection shows the idea, the SQL, and the before and after.

3.21.1 Cleaning

Idea. Make values consistent so that equal things look equal. "MENON", "Menon" and "menon " are the same surname to a person, but three different strings to a database.

SELECT
  UPPER(SUBSTR(TRIM(first_name),1,1)) || LOWER(SUBSTR(TRIM(first_name),2)) AS first_name,
  UPPER(SUBSTR(TRIM(last_name),1,1))  || LOWER(SUBSTR(TRIM(last_name),2))  AS last_name,
  COALESCE(LOWER(TRIM(email)), 'not provided')                              AS email
FROM stg_customers;

Function names differ by database (for example SUBSTR is SUBSTRING in SQL Server). Replacing a missing email with "not provided" is a business choice; some teams keep NULL.

3.21.2 Type conversion

Idea. Staging holds everything as text. The target needs real dates and numbers so that sorting, comparing and adding work properly. As text, "9" sorts after "10", and "20/10/2026" sorts before "21/09/2026" only by accident.

SELECT
  CAST(order_id AS INTEGER) AS order_id,
  SUBSTR(order_date,7,4) || '-' || SUBSTR(order_date,4,2) || '-' || SUBSTR(order_date,1,2) AS order_date,
  CAST(total_amount AS REAL) AS total_amount
FROM stg_orders
WHERE total_amount NOT GLOB '*[^0-9.]*';   -- only rows whose amount is numeric

Oracle and PostgreSQL would use TO_DATE(order_date, 'DD/MM/YYYY'); the string is sliced here because SQLite has no date parser with a format string. Confirm with the data owner whether dates are day-first or month-first, and reject dates that do not parse instead of guessing.

3.21.3 Mapping and standardising

The word mapping has two meanings in ETL, and both matter. In the broad sense a mapping is the whole definition of how source columns become target columns (we meet this in 8.11). In the narrow sense, value mapping means translating codes: the order system stores S, P and C, and reports want the words.

CREATE TABLE status_map (status_code TEXT PRIMARY KEY, status_name TEXT);
INSERT INTO status_map VALUES ('S','Shipped'),('P','Pending'),('C','Cancelled');

SELECT o.order_id, COALESCE(m.status_name, 'Unknown') AS order_status
FROM   stg_orders o
LEFT JOIN status_map m ON m.status_code = o.status_code;

The unmatched code Z becomes "Unknown" because of the LEFT JOIN plus COALESCE. An inner join here would silently drop order 5007 altogether, which is one of the most common ETL bugs: a lookup that loses rows when it fails to match.

3.21.4 Deduplication

Idea. Keep one row for each real-world thing. Customer 1002 arrived twice in the staging data.

SELECT customer_id, first_name, last_name, email, zip_code
FROM (
  SELECT s.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY customer_id) AS rn
  FROM stg_customers s
) t
WHERE rn = 1;

ROW_NUMBER() numbers the rows inside each customer id (1, 2, ...); keeping only number 1 removes the extras. The ORDER BY inside the window decides which duplicate survives. Here the two copies were identical so it did not matter. In real data the copies often differ, and you must choose a rule such as "keep the latest by modified date" or "keep the one with the most filled-in columns".

3.21.5 Lookups

Idea. A lookup enriches a row by finding a related value in a reference table. Order items carry only product_code. A lookup in PRODUCTS adds the product name.

SELECT i.item_id, i.product_code,
       COALESCE(p.product_name, 'UNKNOWN PRODUCT') AS product_name
FROM   stg_order_items i
LEFT JOIN products p ON p.product_code = i.product_code;

Choose deliberately what a miss does: use a default such as "UNKNOWN PRODUCT" and report it, or reject the row. Never let it vanish. Real ETL tools provide a ready-made lookup transformation for this and handle caching, because looking up a million rows one at a time would be slow. In the Informatica guide, lookup transformations appear among the restrictions on mapplets used for Hadoop masking (User Guide 10.5.10, p.137), which confirms that lookup is a standard kind of transformation in that product.

3.21.6 Filtering, validating and rejecting

Idea. Some rows should not reach the target. Filtering removes rows that are valid but unwanted (for example, test orders). Validating checks rows against rules and rejects those that fail, keeping them aside with a reason so someone can fix them.

CREATE TABLE reject_orders (batch_id INTEGER, order_id TEXT, reason TEXT);

INSERT INTO reject_orders
SELECT 1, o.order_id,
       CASE WHEN o.total_amount = '' OR o.total_amount GLOB '*[^0-9.]*'
            THEN 'total_amount is not a number'
            ELSE 'customer ' || o.customer_id || ' not found' END
FROM   stg_orders o
WHERE  o.total_amount = '' OR o.total_amount GLOB '*[^0-9.]*'
   OR  NOT EXISTS (SELECT 1 FROM dim_customer d
                   WHERE d.customer_id = CAST(o.customer_id AS INTEGER));

The valid orders carry on. This is the key design choice: one bad row must not stop the whole run, and must not vanish. It goes to a reject table with a batch id and a human-readable reason.

3.21.7 Joining and derived columns

Joining combines rows from different inputs. To build the sales fact table we join order items to their orders for date, customer and status. Ask of every join: "what happens to a row with no match?" An inner join drops it; a left join keeps it with empty columns. That is a business decision, not a technical one. Derived columns calculate values that do not exist in the source, such as line_amount as quantity times unit price (item 2: 2 × 100.00 = 200.0).

3.21.8 Aggregating

Idea. Summarise many rows into fewer, such as a daily sales table for dashboards.

SELECT order_date,
       COUNT(DISTINCT order_id) AS orders_count,
       SUM(quantity)            AS units,
       SUM(line_amount)         AS revenue
FROM   fact_order_line
GROUP BY order_date;

Hand check: on 20 October, 1 + 2 + 1 = 4 units and 1499 + 200 + 50.5 = 1749.5 revenue.

3.22 Load: writing into the target

Concept. Loading writes the transformed rows into the target tables, in a way that is correct, repeatable and fast enough.

Why it matters. A load that half-finishes leaves the target in a state nobody understands: some rows from tonight, some from yesterday. Loads must either complete or leave things as they were, and they must be safe to run again.

3.22.1 Load strategies

What the tools say about these ideas, all in their own terms:

• Informatica's plan has update strategy options: treat source rows as Insert or Update, and an "Update as" choice of Update, Insert or Update else Insert, which updates a row if it exists in the target and inserts the rest (User Guide 10.5.10, p.284). That is the upsert strategy, named.
• Informatica lets you run SQL before and after writing to a target. Its example is TRUNCATE TABLE $OWNERNAME.$TABLENAME;, which deletes all rows from the target table first (pp.277 to 278). That is truncate and load.
• Delphix on-the-fly masking jobs have a Truncate option that sets whether target database tables are emptied before the masked data is loaded, and a Batch Update option for loading in batches sized by the commit size. Create, view, edit, delete, verify jobs ↗
• GenRocket's FAQ says that for very large volumes you do not want to connect through JDBC; you generate data in the database's native bulk load format using bulk load receivers. GenRocket General FAQ ↗

3.22.2 Upsert in SQL

Suppose Asha Menon's zip code changes in the next feed. A plain insert would collide with the existing row. An upsert updates it:

-- SQLite and PostgreSQL syntax
INSERT INTO dim_customer_simple (customer_id, full_name, email, zip_code)
VALUES (1001, 'Asha Menon', 'asha.menon@example.com', '560002')
ON CONFLICT (customer_id)
DO UPDATE SET full_name = excluded.full_name,
              email     = excluded.email,
              zip_code  = excluded.zip_code;

-- Oracle and SQL Server use MERGE instead

Notice the property that makes upserts valuable: running the same load twice gives the same result. A property like this is called idempotence, and it is what lets you safely re-run a failed job.

3.22.3 Keeping history: a slowly changing dimension

An upsert overwrites. After it, the warehouse says Asha always lived in 560002, so a report on October 20 sales would attribute them to the wrong zip. If history matters, keep both versions with validity dates. This pattern is commonly called a Type 2 slowly changing dimension.

The pattern needs two statements, run in Lab 2: first close the current row of any customer whose data changed (set valid_to and is_current = 0), then insert a new current row for every customer who has none. The table gets a surrogate key (customer_key), an artificial id, because the business key customer_id now appears more than once.

A fact row can then be joined to the customer version valid on the order date. The date 9999-12-31 is a common convention for "still current".

3.22.4 Making a load safe

• Transactions and commit size. Rows are written in groups; each group is committed. Informatica's plan lets you set a source-based or target-based commit (target-based is the default) and a commit interval whose default is 10,000 rows (User Guide 10.5.10, p.285). Delphix masking jobs have a Commit Size field with the same meaning.
• Recovery. Decide in advance what a failed load does: roll back and stop, skip the bad row, or resume from a checkpoint (see 8.13).

3.23 Batch, streaming, and ETL versus ELT

3.23.1 Batch versus streaming at a glance

Batch collects data for a period (an hour, a night) and processes it as one group. It is easy to reason about and to re-run, and results are always "as of the last run". Most ETL, and nearly all test data provisioning, is batch. Streaming processes each event within seconds of arrival. Results are fresh, but you must handle duplicates and out-of-order events, so it is used where the answer must be current, such as fraud alerts. A middle path, micro-batching, runs a small batch every minute or so.

3.23.2 ETL versus ELT

In ETL, transformations run in the ETL engine, on its own servers, before data reaches the target. In ELT, raw data is loaded into the target first (often a powerful cloud warehouse) and transformed there using its SQL engine.

Look at the last row, because it matters for test data. If raw data holds real personal details, ELT would place them in the target before masking. That is why test data work usually follows the ETL order: mask first, then load. The ShopSphere example in this section is really a hybrid: staging is a lightweight "EL" step and the transformation is done by SQL. Many practical systems mix both styles.

3.24 ETL architecture

An ETL tool is more than a place to run SQL. Look back at the diagram in 8.4. Here is each part and what a real product adds to the plain SQL of our example.

3.24.1 A documented example: the pieces in Informatica TDM

Informatica TDM's architecture section lists these parts, and they line up with the general model. All quoted from the User Guide chapter 1, pages 25 to 27 and 29.

• The PowerCenter Integration Service is the engine. It "runs sessions and workflows that you run from Test Data Manager" (p.26) and runs the data subset, data generation and data masking workflows (p.27).
• The PowerCenter Repository Service manages the PowerCenter repository, the metadata store. That repository holds "information required to extract, transform, and load data", along with permissions and privileges for users (p.26).
• The PowerCenter Client is where you "design mappings, mapplets, and sessions to load the data" (p.25).
• The Informatica Administrator is a web application to "manage, monitor, deploy, and undeploy data flows" (p.25).

PowerCenter, an ETL product, is the engine under TDM. The next chapter builds on that link.

3.25 Mappings, sessions, workflows and jobs

Every ETL tool has a few layers of "thing you can define and run". Names vary, so learn the layers and then the names.

1. Transformation.

   One operation on rows: filter, lookup, aggregate. The smallest piece.

2. Mapping.

   A diagram or definition that connects a source to a target through a chain of transformations. It describes what happens to the data, but does not run by itself.

3. Session (or task).

   A runnable instance of one mapping, with the run settings: which connections, commit size, error rules.

4. Workflow (or job, or pipeline).

   A set of sessions and other steps with an order and conditions: "run session A; if it works, run B and C; if it fails, send an email".

5. Schedule or trigger.

   When the workflow starts: a time, a file arriving, another workflow ending.

3.26 Reusable components and the mapplet

Concept. A reusable component is a piece of ETL logic you build once and use in many places, like a function in programming.

Why it matters. Without reuse, the same rule (clean a phone number, standardise a country name) is copied into twenty mappings. When the rule changes, someone must find and fix twenty copies, and will miss one.

In Informatica's world, the reusable group of transformations is called a mapplet. A mapplet has input and output ports (named connection points), contains one or more transformations, and can be placed into many mappings. Think of it as a small mapping with sockets at each end. The LearningMart video "How to create mapplet in informatica" defines a mapplet the same way: a reusable object created in the Mapplet Designer, for example one that joins first and last name into a full name for many mappings. A step-by-step build is in the mapplet walkthrough in the Informatica masking chapter (online).

What the TDM guide says about mapplets (the rest of the product is not needed to follow this):

• Test Data Manager can create a masking rule from a mapplet. The mapplet holds the logic that masks the input columns and returns data to the target columns (User Guide 10.5.10, p.137).
• You import the mapplet from an XML file exported from the PowerCenter repository or the Model repository, and the mapplet can contain "any passive transformations" (p.137). A passive transformation is one that does not change the number of rows.
• A mapplet can have multiple input and multiple output columns; at least one input and one output must be marked required. When you assign the rule to a column you map the mapplet's column names to the real table columns (p.137).
• The TDM repository stores the mapplet logic when imported, and you cannot change the mapplet inside Test Data Manager. The guide also says that if you change the name or type of a field of a custom masking rule, you must import the mapplet again (p.144).
• Data generation uses the same idea: a custom generation rule is based on PowerCenter mapplet logic, for example one that joins first and last name into a full name (pp.196 and 211).

The Informatica data masking chapter (online) builds a mapplet.

3.27 Error handling

Concept. Error handling is the plan for what the job does when something goes wrong, decided before it goes wrong.

Why it matters. Something will go wrong. A file will arrive empty, a database will be down for maintenance, a date will say 31 February. The question is whether you find out from an alert at 2 a.m. with a clear row count, or from an angry tester at 10 a.m. with a half-loaded database.

3.27.1 Three kinds of error

3.27.2 Strategies

• Reject and continue for data errors, as in 8.7.6. Each reject row has a batch id and a reason.
• Threshold stop. Continue unless more than N errors occur. A file where 40% of rows fail is a broken file, not 40% bad customers.
• All-or-nothing. Load inside a transaction so a failure undoes everything.
• Checkpoint and resume. Remember how far you got, and on restart continue from there.
• Retry with a limit. Temporary glitches (a dropped connection) often pass on a second attempt.

Delphix gives a second example of the same ideas for masking jobs. Data that does not fit what an algorithm expects (non-conformant data) can, at job level, make the job fail, leave the value unmasked, or set it to null (jobs page; terminology page). It also warns that when a masking job fails, the target database or files may be left in a partially masked or unmasked state (job monitoring page). Job monitoring ↗ That sentence is a good reminder: after a failed job, never assume the target is usable.

3.27.3 Reprocessing rejected rows

Run 1 rejected orders 5004 and 5005, and the watermark moved past them, so nothing picks them up again. Rejected rows need their own path: group the reject table by reason, have the owner fix the cause (the "abc" amount; whether customer 9999 arrives tomorrow), then re-stage the corrected rows as a new batch and run transform and load again.

3.28 Data quality and reconciliation

Concept. Data quality checks are tests on the data itself, run as part of the job, that prove the result is what you expected.

Why it matters. The most dangerous failure is a job that "succeeds" and loads the wrong thing. Only checks find logic errors.

3.28.1 Reconciliation queries

Reconciliation means comparing counts and totals between two stages to show nothing was lost or invented. These are the queries to run after run 2 of the ShopSphere job.

-- Completeness: orders read = orders loaded + orders rejected
SELECT (SELECT COUNT(*) FROM feed_orders)                      AS read_total,
       (SELECT COUNT(DISTINCT order_id) FROM fact_order_line)  AS loaded_orders,
       (SELECT COUNT(DISTINCT order_id) FROM reject_orders)    AS rejected_orders;

-- Consistency: no fact row without a customer version (expect no rows)
SELECT DISTINCT f.customer_id
FROM   fact_order_line f
LEFT JOIN dim_customer d ON d.customer_id = f.customer_id
WHERE  d.customer_id IS NULL;

Expected result: the first query returns 7, 5 and 2 (7 = 5 + 2); the second returns no rows.

3.29 Performance ideas

Performance tuning is its own craft, and the right fix depends on measurement. These are the ideas that help most often, in the order to try them.

1. Think in sets, not rows.

   One SQL statement that updates a million rows is much faster than a program that updates them one by one. Many ETL tools give you a row-by-row feeling in their diagrams, but good ones process rows in streams and batches.

2. Use bulk loading for big loads.

   See 8.8.1 for the documented examples from GenRocket's FAQ.

3. Tune commit size.

   A commit after every row is slow; a commit after 50 million rows risks a huge rollback. Informatica's default of 10,000 rows is a hint at the ballpark, not a rule.

4. Handle indexes and constraints during the load.

   Maintaining an index for every inserted row costs time. Some loads drop indexes first and rebuild afterwards. Delphix exposes this as the "Drop Indexes" job option and warns that multiple update threads can cause deadlocks, advising fewer threads if that happens (jobs page).

5. Run in parallel.

   Independent tables can load at the same time. Delphix jobs have "Number of Streams", described as the number of parallel streams, so several tables are processed at once; some streams finish before others, so the number of active streams falls during the job (jobs page). Informatica allows up to eight concurrent workflows (p.276).

3.30 Common ETL tools

Informatica PowerCenter is the ETL product under Informatica TDM, and the TDM notes cover how TDM uses it (8.10 and 8.11). You will also meet Microsoft SQL Server Integration Services, Talend, cloud services such as AWS Glue and Azure Data Factory, and schedulers such as Apache Airflow. Nothing here goes beyond the general ideas of this section. Plain SQL scripts plus a scheduler such as cron, as in this section, are a real way to do small ETL. TDM tools are ETL-style tools specialised for producing safe test data; the next chapter compares the two.

3.31 The complete ShopSphere ETL job on one page

Sections 3.19 to 8.8 showed the pieces. Here is how they fit into one job, the way you would document it for a colleague. The target is a small star schema for sales reporting.

3.31.1 What the two runs did

These row counts come from two SQLite runs of the full job. Run 1 is the first load; run 2 is the next day, after Asha's zip code changed and two new orders (5006 and 5007) arrived.

Order 5007 has an unknown product (P-999) and an unmapped status (Z); it still loads, as "UNKNOWN PRODUCT" and "Unknown", because the lookups use left joins. The 2026-10-23 summary row (2 orders, 3 units, 220.0) includes that line. Whether unknown products count as revenue is a decision to raise, not to bury.

3.32 Hands-on labs

You need any SQL database. SQLite is free and has no setup: install the SQLite command-line tool or use an online SQLite playground, open a new empty in-memory database, and paste each block. If you use another database, adjust the function names as noted in section 3.21.

-- STEP 1: clean, deduplicate and load customers
INSERT INTO dim_customer (customer_id, full_name, email, zip_code)
SELECT CAST(customer_id AS INTEGER),
       UPPER(SUBSTR(TRIM(first_name),1,1)) || LOWER(SUBSTR(TRIM(first_name),2)) || ' ' ||
       UPPER(SUBSTR(TRIM(last_name),1,1))  || LOWER(SUBSTR(TRIM(last_name),2)),
       COALESCE(LOWER(TRIM(email)), 'not provided'),
       zip_code
FROM (SELECT s.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY customer_id) AS rn
      FROM stg_customers s)
WHERE rn = 1;
SELECT * FROM dim_customer;
-- STEP 2: reject bad orders with a reason
INSERT INTO reject_orders
SELECT o.order_id,
       CASE WHEN o.total_amount = '' OR o.total_amount GLOB '*[^0-9.]*'
            THEN 'total_amount is not a number'
            ELSE 'customer ' || o.customer_id || ' not found' END
FROM stg_orders o
WHERE o.total_amount = '' OR o.total_amount GLOB '*[^0-9.]*'
   OR NOT EXISTS (SELECT 1 FROM dim_customer d WHERE d.customer_id = CAST(o.customer_id AS INTEGER));
SELECT * FROM reject_orders;
-- STEP 3: build the fact table (convert, look up, derive)
INSERT INTO fact_order_line
SELECT CAST(i.item_id AS INTEGER), CAST(o.order_id AS INTEGER),
       SUBSTR(o.order_date,7,4) || '-' || SUBSTR(o.order_date,4,2) || '-' || SUBSTR(o.order_date,1,2),
       CAST(o.customer_id AS INTEGER), i.product_code, p.product_name,
       CAST(i.quantity AS INTEGER), CAST(i.quantity AS INTEGER) * CAST(i.unit_price AS REAL)
FROM stg_order_items i
JOIN stg_orders o ON o.order_id = i.order_id
JOIN products p   ON p.product_code = i.product_code
WHERE o.order_id NOT IN (SELECT order_id FROM reject_orders);
SELECT * FROM fact_order_line;
-- STEP 4: aggregate
INSERT INTO agg_daily_sales
SELECT order_date, COUNT(DISTINCT order_id), SUM(quantity), SUM(line_amount)
FROM fact_order_line GROUP BY order_date;
SELECT * FROM agg_daily_sales ORDER BY order_date;
-- STEP 5: reconcile
SELECT (SELECT COUNT(*) FROM stg_orders) AS read_rows,
       (SELECT COUNT(DISTINCT order_id) FROM fact_order_line) AS loaded,
       (SELECT COUNT(*) FROM reject_orders) AS rejected;

-- STEP 1: extract. Customers: full copy. Orders and items: only beyond the watermark.
INSERT INTO stg_customers SELECT * FROM feed_customers;
INSERT INTO stg_orders
SELECT * FROM feed_orders
WHERE CAST(order_id AS INTEGER) > (SELECT last_order_id FROM etl_control WHERE job_name = 'orders_daily');
INSERT INTO stg_order_items
SELECT * FROM feed_order_items WHERE order_id IN (SELECT order_id FROM stg_orders);
SELECT order_id FROM stg_orders;
-- STEP 2: close the old version of any customer whose data changed
UPDATE dim_customer
SET valid_to = '2026-10-24', is_current = 0
WHERE is_current = 1
  AND EXISTS (SELECT 1 FROM stg_customers s
              WHERE CAST(s.customer_id AS INTEGER) = dim_customer.customer_id
                AND (s.zip_code <> dim_customer.zip_code
                  OR COALESCE(LOWER(TRIM(s.email)),'not provided') <> dim_customer.email));
-- STEP 3: insert a current version for every customer who has none
INSERT INTO dim_customer (customer_id, full_name, email, zip_code, valid_from, valid_to, is_current)
SELECT CAST(s.customer_id AS INTEGER),
       UPPER(SUBSTR(TRIM(s.first_name),1,1)) || LOWER(SUBSTR(TRIM(s.first_name),2)) || ' ' ||
       UPPER(SUBSTR(TRIM(s.last_name),1,1))  || LOWER(SUBSTR(TRIM(s.last_name),2)),
       COALESCE(LOWER(TRIM(s.email)), 'not provided'), s.zip_code,
       '2026-10-24', '9999-12-31', 1
FROM stg_customers s
WHERE NOT EXISTS (SELECT 1 FROM dim_customer d
                  WHERE d.customer_id = CAST(s.customer_id AS INTEGER) AND d.is_current = 1);
SELECT customer_key, customer_id, zip_code, valid_from, valid_to, is_current
FROM dim_customer ORDER BY customer_id, customer_key;
-- STEP 4: load new fact rows, with lookups that do not lose rows
INSERT INTO fact_order_line
SELECT CAST(i.item_id AS INTEGER), CAST(o.order_id AS INTEGER),
       SUBSTR(o.order_date,7,4) || '-' || SUBSTR(o.order_date,4,2) || '-' || SUBSTR(o.order_date,1,2),
       CAST(o.customer_id AS INTEGER), i.product_code,
       COALESCE(p.product_name, 'UNKNOWN PRODUCT'),
       CAST(i.quantity AS INTEGER), CAST(i.quantity AS INTEGER) * CAST(i.unit_price AS REAL)
FROM stg_order_items i
JOIN stg_orders o ON o.order_id = i.order_id
LEFT JOIN products p ON p.product_code = i.product_code;
SELECT item_id, order_id, order_date, product_name, quantity, line_amount
FROM fact_order_line WHERE order_id >= 5006;
-- STEP 5: move the watermark, last
UPDATE etl_control
SET last_order_id = (SELECT MAX(CAST(order_id AS INTEGER)) FROM stg_orders)
WHERE job_name = 'orders_daily';
SELECT * FROM etl_control;
-- STEP 6: run it again by mistake: the watermark makes the second run a no-op
DELETE FROM stg_orders;
INSERT INTO stg_orders
SELECT * FROM feed_orders
WHERE CAST(order_id AS INTEGER) > (SELECT last_order_id FROM etl_control WHERE job_name = 'orders_daily');
SELECT COUNT(*) AS rows_extracted_on_rerun FROM stg_orders;

3.33 Common mistakes and gotchas

3.34 Recap

-- STEP 1 (profile): which columns look sensitive?
SELECT COUNT(*) AS ssn_like_values FROM customers
WHERE ssn GLOB '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]';
SELECT COUNT(*) AS phone_like_values FROM customers
WHERE phone GLOB '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';
-- STEP 2 (extract a subset): choose the driver rows
INSERT INTO subset_keys SELECT DISTINCT customer_id FROM orders WHERE order_date = '2026-10-20';
SELECT * FROM subset_keys;
-- STEP 3 (transform = mask, and load customers)
INSERT INTO t_customers
SELECT c.customer_id,
       m.first_name,
       'Tester' || c.customer_id,
       'user' || c.customer_id || '@example.test',
       '90000' || SUBSTR('00000' || c.customer_id, -5),
       '900-00-' || SUBSTR('0000' || c.customer_id, -4),
       SUBSTR(c.date_of_birth, 1, 4) || '-01-01',
       SUBSTR(c.zip_code, 1, 3) || '000',
       'MASK' || SUBSTR('000000' || c.customer_id, -6)
FROM customers c
JOIN subset_keys k ON k.customer_id = c.customer_id
JOIN mask_names m ON m.slot = c.customer_id % 4;
SELECT * FROM t_customers;
-- STEP 4 (extract and load the children that belong to the subset)
INSERT INTO t_orders SELECT o.* FROM orders o JOIN subset_keys k ON k.customer_id = o.customer_id;
INSERT INTO t_order_items SELECT i.* FROM order_items i JOIN t_orders o ON o.order_id = i.order_id;
SELECT * FROM t_orders;
-- STEP 5 (test the result): no orphans, no leaked values, no lost rows
SELECT COUNT(*) AS orphan_orders FROM t_orders o
LEFT JOIN t_customers c ON c.customer_id = o.customer_id WHERE c.customer_id IS NULL;
SELECT COUNT(*) AS leaked_ssn FROM t_customers t JOIN customers c ON c.ssn = t.ssn;
SELECT COUNT(*) AS leaked_email FROM t_customers t JOIN customers c ON c.email = t.email;
SELECT (SELECT COUNT(*) FROM t_customers) AS customers,
       (SELECT COUNT(*) FROM t_orders) AS orders,
       (SELECT COUNT(*) FROM t_order_items) AS items;
-- STEP 6 (repeatability): the same input gives the same masked output
SELECT 'Tester' || 1001 = last_name AS same_as_first_run FROM t_customers WHERE customer_id = 1001;

-- Customer keys masked, order keys left alone: the join breaks
SELECT COUNT(*) FROM orders o JOIN (SELECT customer_id + 70000 AS customer_id FROM customers) c
  ON c.customer_id = o.customer_id;                        -- returns 0

-- The same rule applied to both tables: the join works
SELECT COUNT(*) FROM (SELECT order_id, customer_id + 70000 AS customer_id FROM orders) o
  JOIN (SELECT customer_id + 70000 AS customer_id FROM customers) c
  ON c.customer_id = o.customer_id;                        -- returns 4