Delphix (2): algorithms, file masking and hands-on labs

Five labs put the earlier Delphix chapters together, run in order on one small data set (ShopSphere) so that each lab leaves behind what the next one needs: Lab 4 (connector to a VDB), Lab 5 (connections and profiling), Lab 6 (masking files), Lab 7 (masking FirstName) and Lab 8 (masking alphanumeric text, SSN and telephone number). Concepts are not re-taught; each lab links to the chapter that owns them.

UI steps come from the Delphix Continuous Compliance 2026.5 documentation; the sources list names each page. The official screenshots come from different releases (the corner label reads 21.0.0.0, 22.0.0.0, 24.0.0.0, 26.0.0.0 and, in one, a development build number), so labels, button order and extra fields on a real engine will differ; read each screenshot as "what to look for". Database type, file access method and login method may also differ from the lab setup.

9.30 Words to know first

Masking engine

The Delphix Continuous Compliance engine, where you define connectors, rule sets, profiling jobs and masking jobs.

VDB (virtual database)

A full read-and-write copy of source data that Delphix Continuous Data provisions from a dSource or another VDB. To the masking engine it is another database. See the Delphix overview and terminology chapter (online).

Application and environment

An application is a tag for the real system (here ShopSphere). An environment groups connectors, rule sets and jobs; its purpose is Mask or Tokenize/Re-Identify.

Connector

Saved details the engine uses to reach one database or file location (connectors and VDB (online)).

Rule set and inventory

A rule set lists the tables or files to profile or mask. Its inventory records, per column or field, the domain and algorithm (rule sets and keys (online)).

Profiling job, domain

A profiling job assigns a domain (a kind of sensitive data such as FIRST_NAME, with one default algorithm) to columns that look sensitive (Delphix profiling (online)).

In-place and on-the-fly

In-place masking updates data where it sits; on-the-fly reads a source and writes masked values to a target (algorithms and masking jobs).

Non-conformant data

A value that does not fit what an algorithm expects. By default it is left unmasked and the job records a warning, so a "successful" job can still leave data exposed.

9.31 How the five labs chain together

Lab 4 gets the engine talking to a database. Lab 5 asks which columns are sensitive. Lab 6 repeats the idea for files. Labs 7 and 8 fix algorithm choices and run real masking jobs. If a lab fails, the later ones starve, so do not skip its verification.

9.32 Setup data for every lab

All labs use the ShopSphere tables.

9.32.1 The database

The lab copy must be a database the engine may change: a VDB or other disposable copy, never SHOPSPHERE_PROD. The docs recommend a VDB as the masking target and call it the best security practice because it runs in a sandbox (Preparing data). Load the four ShopSphere tables with three customers (plus two or three orders each and a few items and products):

The table omits email and date_of_birth for width; the other columns are as in the running example. Take the "before" snapshot now and save the output:

SELECT customer_id, first_name, last_name, phone, ssn, zip_code, tax_id
FROM   customers ORDER BY customer_id;

SELECT COUNT(*) AS orphans
FROM   orders o LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE  c.customer_id IS NULL;

The orphan query counts orders whose customer is missing. It must return 0 before and after masking; if it is not 0 now, the "keys survived" check later means nothing. A masking job can report success while a column was never masked, so only a comparison of the data proves the work: the snapshot is the left-hand side of that proof.

9.32.2 The files

Lab 6 needs two files with the same customers, in a location the engine can reach. A pipe-delimited customers.dat without a header:

1001|Asha|Menon|123-45-6789|9876543210
1002|Ravi|Kumar|234-56-7890|9123456780
1003|Meera|Nair|345-67-8901|9988776655

And a fixed-width customers_fixed.txt: customer_id 4 characters, first_name 10, last_name 10, ssn 11, phone 10 (45 per line):

1001Asha      Menon     123-45-67899876543210
1002Ravi      Kumar     234-56-78909123456780
1003Meera     Nair      345-67-89019988776655

Copy both to an original folder first: Lab 6 compares against them and an in-place job overwrites the file it masks.

9.32.3 Prerequisites

• A login with a role that can add environments, connectors, rule sets and jobs and run jobs; check which one you hold (Users and roles).
• Network access from the engine to the database, and a masking user. The docs recommend a separate masking user with read, write and update rights plus index, trigger and constraint rights if you use those job options; profiling alone needs schema view and read (Database user permissions).
• A SQL client, and a text editor and diff for the files.
• Job and object names must be unique on the engine (the docs say job names are unique across the application), so add your initials if several people share one engine.

9.33 Lab 4: create a connector to a VDB

9.33.1 Goal and prerequisites

Create an application, an environment and a database connector so that the engine can reach the ShopSphere VDB, and prove the connection works. Every later lab depends on it. You need the setup data and "before" snapshot, plus from the DBA: database type, host, port, database name, schema, and a masking user and password. A wrong guess here is the most common reason Test Connection fails.

What "a connector to a VDB" means. The documentation describes database connectors and describes a VDB as a database that Delphix Continuous Data provisions (Delphix masking terminology); it describes no special "VDB connector". Create an ordinary database connector and point it at the VDB's host, port and database name. In-place masking rewrites data, so doing it on a disposable VDB keeps production untouched. (Masking as part of VDB provisioning is a separate setup with restrictions, described on Provision masked VDBs; this lab does not use it.)

9.33.2 Steps

1. Log in and open the Environments page.

   Everything else (connectors, rule sets, jobs) hangs under an environment.

   A grid with ID, Application, Environment, Purpose and Approval, plus + Application, + Environment and Actions buttons.

2. Add the application, then the environment.

   Click + Application, enter ShopSphere, Save: the docs say an application must exist before an environment can be created. Then + Environment: application ShopSphere, name ShopSphere_DB_Mask, Purpose Mask. Leave the approval-workflow box off: it makes an approver sign off a database inventory before any job runs, which suits real projects but is a hurdle in a first lab. Choose Save And View.

   The environment shows three empty tabs: Jobs, Rule Sets, Connectors.

3. Open Connectors and click + Connection.

   The wizard has the steps Details, Credentials, Custom Properties and Summary.

4. Details: name it and choose the type.

   Connection Name SS_VDB_Conn. Each Source Type entry is prefixed with its Meta Data Source ("Database - Sybase" in the screenshot). For Oracle, MS SQL Server and Sybase you also choose Basic (host and port) or Advanced (a full JDBC URL); pick Basic unless told otherwise.

5. Credentials: the database facts.

   Fields depend on the type. The docs list Schema Name (the schema holding the tables), Database Name (case-sensitive: it must match what the instance knows), Host Name/IP, Port (SQL Server defaults to 1433), SID (Oracle) and Instance Name (SQL Server: if given, the port is ignored and port 1434 of the SQL Server Browser Service is used, so the firewall must allow it). Then the masking user's name and password.

   All required fields filled; the password shows as dots.

6. Skip Custom Properties.

   That step uploads a Java properties file. The docs warn that its values are readable by anyone who can view connectors, so secrets belong in the form fields.

7. Click Test Connection, then Save.

   The docs say the engine uses the wizard's information to attempt a database connection and reports success or failure. Testing before saving lets you fix mistakes while the form is open.

   A success message. After Save, SS_VDB_Conn appears under DATABASE with a Connector ID.

8. Test the saved connector from the grid.

   Open the row's actions menu and choose Test Connection. This proves the saved connector works, not only the form.

9.33.3 Verification checks

1. Test Connection succeeds twice (wizard and grid); screenshot the message.
2. SS_VDB_Conn is under DATABASE with an ID and no warning triangle. The docs say the numeric ID is how the masking API refers to the connector.
3. With the masking user in your SQL client, SELECT COUNT(*) FROM customers; returns 3.
4. Record the exact database name, schema and host.

9.33.4 What can go wrong

9.34 Lab 5: create a rule set and profile the VDB

9.34.1 Goal

Turn the connector from Lab 4 into a rule set that lists the four ShopSphere tables, run a profiling job over it, read what the profiler found, and correct anything it got wrong. At the end you have an inventory: a table that says which column gets which domain and algorithm.

9.34.2 Prerequisites and setup data

• Lab 4 is finished: connector SS_VDB_Conn tests successfully.
• The CUSTOMERS, ORDERS, ORDER_ITEMS and PRODUCTS tables exist in the VDB. The concepts behind this lab are in the rule sets and keys chapter (online) and the Delphix profiling chapter (online).

9.34.3 Steps

1. Open the environment's Rule Sets tab and click + Rule Set.

   A rule set is the engine's "shopping list" of tables. Profiling and masking jobs both work on a rule set, never on a connector directly.

   A dialog asks "What type of data does this Rule Set contain?" with four choices: Database, Semi-Structured Files (JSON or XML), Flat Files (delimited or fixed-width) and Copybook (mainframe).

2. Choose Database, click Continue, and fill in the Details step.

   Name the rule set SS_DB_RS and pick SS_VDB_Conn from the connector drop-down. The docs say the drop-down lists only connectors that match the chosen type; if it is empty, you have no suitable connector yet.

3. Data Tables step: click Add Tables and select the four tables.

   The Add Database Table dialog lists the tables the connector can see. Tick CUSTOMERS, ORDERS, ORDER_ITEMS and PRODUCTS and click Save. The docs note that this dialog shows tables only, not views, indexes or stored procedures.

   The grid shows four rows with the columns Table Name, Logical Key, Filter and Custom SQL. Logical Key, Filter and Custom SQL are empty.

4. Check keys before you save.

   Each table should have a primary key. The docs explain what happens otherwise: with in-place masking and no primary key, you must name an existing column as a logical key (it does not change the database; it only tells Delphix how to identify a row). If you give neither, the engine creates an identity column, which needs permission to change the schema, and the job may fail without it. All four ShopSphere tables have primary keys, so leave Logical Key empty.

5. Click Next, review the Summary, and click Save.

   For a database rule set the save is an asynchronous task: a task ID appears in a bottom message, and you can watch it under Monitor, Async Tasks. The engine is reading the table definitions from the database in the background.

   The rule set SS_DB_RS appears in the Rule Sets grid. The docs say the Refresh/Save icon shows an hourglass while the engine rescans tables; in the official grid screenshot finished database rule sets show a green tick.

6. Run the rule set Checklist.

   In the rule set's actions menu choose the verify (Checklist) action. The docs describe an Identity Column check: every table must have a primary key, an indexed identity column or a logical key. Catching a missing key now is cheaper than a masking job that fails later.

7. Create the profiling job.

   Go to the Jobs tab, click + Profiling Job, and fill in the wizard. Job Name: SS_Profile_DB (must be unique). Rule Set: SS_DB_RS. Profile Set: ASDD Standard, the set recommended for databases (Out of the box profiling settings). Leave Multi Tenant, Multiple Profiler Expression Check and "Clear previous auto domain assignments" unticked for the first run.

8. Configuration step: keep the defaults.

   Number of Streams defaults from an application setting (the screenshot shows 1). Feedback Size, Min and Max Memory, Comments and Email are optional. The docs add one rule worth remembering: with an ASDD profile set, Max Memory must be at least 1024 MB for each stream, so 4 streams need 4096 or more. Click Next, check the Summary and Save.

9. Run the job.

   On the Jobs tab open the row's actions menu (the three dots) and click Run. The engine first checks free memory and the number of simultaneous jobs (the docs give a default limit of 7) and may queue the job instead of starting it.

   The job's Status changes from CREATED to QUEUED or RUNNING and then to SUCCEEDED. WARNING or FAILED means you must read the logs before moving on.

10. Read the Profile Results.

    Click the job name (or use Monitor, Job Executions). The docs say a finished profiling job shows the findings table by table and column by column under Profile Results. For a database you can also open the rule set: the assigned algorithm column in the inventory is the result.

11. Open the inventory and correct the findings.

    Click the rule set name to open its inventory. For any column the profiler got wrong, click the Edit icon in the Actions column. Untick Enable Automatic Updates first: while it is ticked, a later profiling run may overwrite your choice. Then pick the Domain and Algorithm you want and click Save. To record the proof, use Actions, Export Rule Set: it downloads the inventory as a CSV.

9.34.4 Expected results for ShopSphere

The table below is a prediction, made by reading the published classifier patterns in the ASDD standard profile set (ASDD standard profile set) against the ShopSphere column names. Confidence values depend on your engine's settings, so the real result can differ. Where it differs, that is a finding to record, not a failure of the lab.

9.34.5 Verification checks

1. The profiling job ends SUCCEEDED, and the Execution Summary lists all four tables as profiled with none remaining.
2. The inventory shows a Domain and an Algorithm for each of the eight sensitive ShopSphere columns above, or you have recorded which ones are missing and fixed them by hand.
3. No key column (customer_id, order_id, item_id) has an algorithm. Masking a primary or foreign key without a plan breaks joins.
4. Export Rule Set and keep the CSV. This is your record of "what did the profiler find and what did we decide".

9.34.6 What can go wrong

9.35 Lab 6: mask a delimited and a fixed-width file

9.35.1 Goal

Mask the customer files customers.dat (pipe-delimited) and customers_fixed.txt (fixed-width) with the file side of the engine. Files carry no built-in description of their columns, so this lab adds one step the database labs did not need: telling the engine what the fields are, with a file format.

9.35.2 Prerequisites and setup data

• The two files from the setup section exist in a working folder, and the originals are copied to a separate original folder.
• You know how the engine reaches that folder. The docs list the connection modes Filesystem Mount Point, SFTP, FTP, FTPS (mainframe datasets only), AWS S3, Other S3 Compatible Storage and Azure Blob Storage. A mount point also needs a mount created through the Mount Filesystem API before you can pick it in the connector (Managing remote mounts). The file concepts are in the file masking chapter.
• A new environment named ShopSphere_File_Mask (same steps as in Lab 4, Purpose: Mask).

9.35.3 Steps

1. Create two file connectors.

   In the new environment open Connectors, click + Connection, and choose a File source type: Delimited for SS_Delim_Conn and Fixed Width for SS_Fixed_Conn. The type matters because it decides what the rule set will ask for later. For SFTP the credential step asks for Path (the directory that holds the files), Server Name, Port and User Name, plus Password or a public key. The docs add that, from version 6.0.9.0, an "User Directory as root" option exists when the path is relative to the SFTP user's home directory. Test the connection if the wizard offers it.

   Both connectors appear under the FILE group in the Connectors grid, with types Delimited and Fixed Width.

2. Write the two format files.

   The docs say the engine needs a description of the fields and that a format file contains only the structure, never production data or personal information. For a delimited file the format file lists the field names, one per line, with no header. For a fixed-width file each line is a field name, a comma and the field length. The two official sketches below show the shape.

   Formats for ShopSphere, saved as ss_delim.fmt and ss_fixed.fmt:

   customer_id
   first_name
   last_name
   ssn
   phone

   customer_id,4
   first_name,10
   last_name,10
   ssn,11
   phone,10

   Give the fields meaningful names. The profiler uses names (and, with ASDD, values) to decide what is sensitive, so first_name is findable and FIELD_03 is not. On Windows, save with LF line endings, because the docs warn that CRLF files can fail the engine's validation.

3. Import the formats.

   Go to Settings, Data Formats, tab Files, click + File Format. The wizard asks for the Format Type, then the file to import (a custom name is optional), then, for delimited and fixed-width formats only, the number of header and footer records (leave them empty: the lab files have none), and ends with a Summary. Save. Do this twice, once per file type.

   Both formats appear in the Data Formats grid with their Type (Delimited or Fixed Width).

4. Create a flat-file rule set for each file.

   In the environment's Rule Sets tab click + Rule Set, choose Flat Files, and pick the matching connector. In the Data Files step click Add File Name, tick customers.dat (or customers_fixed.txt), and set its properties: Format File (ss_delim.fmt or ss_fixed.fmt), End of Record (Linux LF unless the file came from Windows), and, for the delimited file, the Delimiter |. Text Enclosure stays empty because the sample has no quotes. Save as SS_Delim_RS and SS_Fixed_RS.

   Each rule set lists one file with its format and delimiter.

5. Profile both rule sets.

   Create one profiling job per rule set exactly as in Lab 5 (+ Profiling Job, rule set, ASDD Standard) and run them. ASDD supports delimited and fixed-width files. The results grid lists File Name, Field Name, Algorithm and Domain.

   Fields first_name, last_name, ssn and phone get domains. If the field names in your format were meaningless, only a data-level match could find them.

6. Set the algorithms in the inventory.

   Open each rule set's inventory, choose the file format in the drop-down if asked, and edit the fields: first_name gets dlpx-core:FirstName, last_name gets dlpx-core:LastName, ssn gets dlpx-core:CM Digits (the reason is in Lab 8). Leave customer_id and phone alone in this lab. Untick Enable Automatic Updates first if you override a profiler assignment.

7. Create a masking job per rule set.

   Jobs tab, + Masking Job. Choose Masking Method In-Place so the working copy is overwritten, pick the rule set, and keep the other settings at their defaults. The docs say in-place jobs update the source with masked values, and on-the-fly jobs read from one place and write to another. A file job does not show the database-options step. Names: SS_Mask_Delim and SS_Mask_Fixed.

8. Run both jobs and check the status.

   Run, then open Monitor, Job Executions. Click the job name and read the Execution Summary: files masked, rows masked, files with non-conforming data.

   Status SUCCEEDED, rows masked 3 per file, no non-conforming data. A yellow WARNING means some values were left unmasked.

9.35.4 Verification checks

Run these in a shell on the folder that holds both the masked and the original files:

diff original/customers.dat customers.dat
awk -F'|' '{print NF}' customers.dat | sort | uniq -c
awk '{print length($0)}' customers_fixed.txt | sort | uniq -c
diff <(cut -c1-4,36-45 original/customers_fixed.txt) <(cut -c1-4,36-45 customers_fixed.txt)

The last command compares only the customer_id (characters 1 to 4) and phone (characters 36 to 45) columns of the fixed-width file. It should print nothing. It needs a shell that supports process substitution, such as bash.

1. Names and SSN differ between original and masked, and the SSN still looks like ddd-dd-dddd.
2. customer_id and phone are identical to the original (they were out of scope).
3. Every line of the delimited file has 5 fields, and every line of the fixed-width file is still 45 characters. A different count means the masked value did not fit the field or the format did not match the data.
4. Row count is unchanged: 3 lines in each file.
5. Consistency: save the masked files as run1, copy the originals back into the working folder, run both jobs again, and run diff against the run1 copies. It should print nothing, because the docs describe the Name and Character Mapping frameworks as deterministic for the same configuration and key.

9.35.5 What can go wrong

9.36 Lab 7: mask FirstName in the database

9.36.1 Goal

Run one real in-place masking job on the ShopSphere VDB that changes customers.first_name and nothing else, then prove by query that the job did exactly that. The algorithm rationale is in the algorithms and masking jobs chapter; here you see it work.

9.36.2 Prerequisites and setup data

• Labs 4 and 5 are done: rule set SS_DB_RS exists and has an inventory.
• The VDB can be restored if a job goes wrong, for example from a snapshot or bookmark taken before the run. Not documented: the exact restore route for a given VDB (the docs describe rewind and refresh for masked VDBs only).
• Two extra lab-only things in the VDB (delete them afterwards): a copy table for before/after comparison, and an edge-case customer. The copy table holds unmasked fake data. In real work you would never copy sensitive data next to the masked data.

CREATE TABLE customers_before AS SELECT * FROM customers;

INSERT INTO customers
  (customer_id, first_name, last_name, email, phone, ssn, date_of_birth, zip_code, tax_id)
VALUES
  (1004, 'Asha', 'Iyer', 'asha.i@example.com', 'CALL-ME-NOW', 'N/A',
   '1992-01-15', '560002', NULL);

INSERT INTO customers_before SELECT * FROM customers WHERE customer_id = 1004;

Customer 1004 has the same first name as 1001. Two customers named Asha should receive the same masked first name; that is the consistency check. Its phone and SSN values are deliberately bad: Lab 8 uses them to show non-conformant data.

9.36.3 Steps

1. Make a rule set that contains only today's masking.

   A masking job masks every column that has an algorithm in the inventory. After Lab 5 your inventory has eight columns assigned. To mask only first_name, duplicate the rule set (the row's actions menu, Duplicate), name the copy SS_DB_RS_FN, and open its inventory. The docs say a copy includes the inventory and any filters. Use the "Clear Masking Assignment" icon on each assigned row (it works per column, as in the profiling chapter): the docs say it removes that assignment and disables automatic updates, after a short confirmation bar. Then edit first_name only.

   The inventory shows an algorithm for first_name and blank algorithm cells for every other column.

2. Assign the algorithm.

   Edit CUSTOMERS.first_name: untick Enable Automatic Updates, Domain FIRST_NAME, Algorithm dlpx-core:FirstName, Save. This is the algorithm the profiler proposed in the official profiling screenshot, so you are confirming, not inventing.

3. Create the masking job.

   Jobs tab, + Masking Job. Job Name SS_Mask_FirstName. Masking Method: In-Place. Rule Set: SS_DB_RS_FN. Leave "Stop job on first occurrence" off and Multi Tenant off. Next, the database options: Commit Size and Update Threads can stay default; the docs warn that several update threads can cause deadlocks, especially on tables without an index. Do not tick Disable Constraints, Drop Indexes or Disable Triggers: first_name is not a key. Number of Streams: 1. Save.

4. Run the Checklist, then run the job.

   The job's Checklist action repeats the identity-column check. Then choose Run in the actions menu. Take the VDB snapshot first.

5. Watch the execution.

   Open the job name from Monitor, Job Executions. The Execution Summary counts tables and rows masked and, importantly, tables and columns with non-conforming data. The Events list shows the stages in order, from initialising and collecting configuration to job completed. Execution Components lists each table with progress and rows per minute.

   Masking Completed, one table masked (CUSTOMERS), rows masked equal to the row count (4), nonconforming data 0.

9.36.4 Verification checks

-- 1. first names changed
SELECT c.customer_id, b.first_name AS before_name, c.first_name AS after_name
FROM   customers c JOIN customers_before b ON b.customer_id = c.customer_id
ORDER  BY c.customer_id;

-- 2. how many unchanged?
SELECT COUNT(*) AS unchanged
FROM   customers c JOIN customers_before b ON b.customer_id = c.customer_id
WHERE  c.first_name = b.first_name;

-- 3. same input, same output (1001 and 1004 are both Asha)
SELECT COUNT(DISTINCT first_name) AS distinct_masked
FROM   customers WHERE customer_id IN (1001, 1004);

-- 4. nothing else changed
SELECT COUNT(*) AS other_changes
FROM   customers c JOIN customers_before b ON b.customer_id = c.customer_id
WHERE  c.last_name <> b.last_name OR c.phone <> b.phone OR c.ssn <> b.ssn;

1. Query 1 shows a different name on every row. Query 2 should return 0, but it is not guaranteed to: the docs say the masked value can equal the input. If one row matches, record it and move on; it is a property of the algorithm, not a failure.
2. Query 3 returns 1: both Asha rows got the same masked name.
3. Query 4 returns 0 and the "orphans" query from the setup still returns 0.
4. Screenshot the Execution Summary and save the job report (Report and Logs).

9.36.5 What can go wrong

9.37 Lab 8: mask alphanumeric text, SSN and telephone number

9.37.1 Goal

Mask three different kinds of value with three algorithms, verify format and consistency with queries, and deliberately feed in two bad values to see what non-conformant data looks like in a log. The ShopSphere table has no free alphanumeric column, so this lab adds one, membership_code, such as SS-A7K29X.

9.37.2 Prerequisites and setup data

ALTER TABLE customers ADD membership_code VARCHAR(12);
UPDATE customers SET membership_code = 'SS-A7K29X' WHERE customer_id = 1001;
UPDATE customers SET membership_code = 'SS-B3M81Q' WHERE customer_id = 1002;
UPDATE customers SET membership_code = 'SS-C9D55R' WHERE customer_id = 1003;
-- customer 1004 keeps membership_code NULL on purpose
ALTER TABLE customers_before ADD membership_code VARCHAR(12);
UPDATE customers_before b SET membership_code =
  (SELECT c.membership_code FROM customers c WHERE c.customer_id = b.customer_id);

Then refresh the rule set SS_DB_RS (actions menu, Refresh) so it sees the new column, duplicate it as SS_DB_RS_Sens, clear its assignments, and assign the three below. Lab 7's first-name masking is not repeated here.

9.37.3 Choosing the algorithms

The table separates what the docs state from the design choice. The default algorithm for the SSN domain is not stated in the documentation, so confirm it in Settings, Domains.

Pages: CM Alpha-Numeric, CM Digits, Phone US, Character Mapping and Segment Mapping.

9.37.4 Steps

1. Assign the algorithms in SS_DB_RS_Sens.

   For each of the three columns, Edit, untick Enable Automatic Updates, set Domain (membership_code has no built-in domain: pick one that fits or leave the domain empty as your engine allows) and Algorithm, Save.

   Three assigned rows, every other column blank.

2. Create and run the job SS_Mask_Sensitive.

   In-place, rule set SS_DB_RS_Sens, defaults elsewhere. Take the snapshot first. Run it.

   The status is WARNING (non-conformant) rather than SUCCEEDED, because customer 1004's SSN and phone do not fit. This is the expected outcome in this lab.

3. Read the warning.

   Open the job, then View Logs on the CUSTOMERS row in Execution Components. The docs say each event shows Event type UNMASKED_DATA (or JOB_ABORTED), Cause PATTERN_MATCH_FAILURE, a row count and the column name, and that only a pattern of the bad value is shown (L for letters, N for digits, P for punctuation), never the value itself. For "CALL-ME-NOW" expect a pattern like letters, punctuation, letters, punctuation, letters.

9.37.5 Verification checks

-- SSN keeps its shape and stays unique (rows with a real SSN)
SELECT COUNT(*) AS bad_shape FROM customers
WHERE customer_id <= 1003 AND ssn NOT LIKE '___-__-____';
SELECT COUNT(DISTINCT ssn) AS distinct_ssn FROM customers WHERE customer_id <= 1003;

-- nothing left equal to the original
SELECT COUNT(*) AS unmasked FROM customers c
JOIN customers_before b ON b.customer_id = c.customer_id
WHERE c.customer_id <= 1003
  AND (c.ssn = b.ssn OR c.phone = b.phone OR c.membership_code = b.membership_code);

-- phone pattern, PostgreSQL syntax (use REGEXP_LIKE on Oracle)
SELECT customer_id, phone FROM customers
WHERE customer_id <= 1003 AND phone !~ '^[0-9]{3}555[0-9]{4}$';

-- the edge row stayed as it was, which is the problem to report
SELECT customer_id, ssn, phone, membership_code FROM customers WHERE customer_id = 1004;

1. bad_shape is 0 and distinct_ssn is 3. unmasked is 0, with the same caution as Lab 7: an equal value is possible but unlikely.
2. The phone query returns no rows.
3. membership_code values differ from the originals, keep the "-" and keep the 9-character shape; the NULL stays NULL (the docs say null, empty and whitespace-only values never trigger non-conformant handling in Character Mapping).
4. Row 1004 still shows "N/A" and "CALL-ME-NOW". These bad values stayed unmasked and produced the warning. In real life this is how bad source data leaks through, so a WARNING status must never be ignored.
5. Orphan query from the setup: 0. Other columns unchanged.

9.37.6 What can go wrong

9.38 Where your engine may differ from the docs

9.39 Common mistakes and gotchas

9.40 Recap