Enterprise ETL Platform · UX Case Study · Enterprise ETL
Nine source systems, one way of working
The extraction, transformation and load tool that feeds an enterprise archive platform. It profiles a source system, maps its metadata, defines what to extract, runs the job and proves what moved. End-to-end UX ownership: strategy, architecture, complex workflow design, interaction design, design system and developer handoff.
- Employer
- Platform 3 Solutions
- Role
- Senior UX Lead
- Participation
- UI / UX 100%
- Years
- 2019 onward · core platform 2019–2023, extended since
- Scope
- 9 source families, 13 database types
- Team
- Directed a small UX team
- Type
- Enterprise product
- Modules
- Source and target profiles · Workspaces · Metadata entities · Extraction entities · Execution · Deletion · Character analysis · Monitoring · Configuration · User management
What this deck shows, and what it withholds
Everything described here is work I did. The product and the systems it connects to are withheld under confidentiality, and the interfaces shown here are redrawn rather than captured.
- 01
Withheld
The product name, the specific systems it connects to, and any customer or sample data. No production screenshot appears here.
- 02
Shown
The structure I designed, the decisions I made and why, how I led the work, and what changed as a result.
- 03
Redrawn
Where a screen carries the argument, it is reconstructed: the structure, layout and behaviour kept, the identifying content removed and every value replaced with a placeholder.
What the tool does
and why it is harder than it looks
The platform migrates and archives data from legacy systems. It connects to a source, learns its structure, extracts what is asked for, and loads it into a long-term archive target, with a chain-of-custody record of what moved.
It handles nine source families across thirteen database types: relational databases, flat files, document and collaboration stores, network file shares, mainframe file systems, report archives, multi-value databases and custom connectors. Each has a different shape and a different set of questions to answer.
A microservices architecture runs the work as distributed background jobs. Automation of mapping, extraction, transformation and load is the product’s stated purpose, so the interface exists to configure and prove that automation.
- 01
Nine source families, one product
Each source type asks different questions: a relational source needs host, port and schema; a flat file needs a format questionnaire; a report archive needs report inputs. The product cannot become nine products.
- 02
Configuration now, consequences later
An extraction defined today runs as a background job against production data. Mistakes surface in the job, not in the form, so the configuration screens carry the burden of getting it right first.
- 03
Proof is part of the job
Chain-of-custody files, extraction reports and performance reports are outputs of the run, not afterthoughts. The product sells on demonstrable integrity.
Who configures, who watches
and the gap between the two
Nine source families, four ways to define an extraction, ten output formats and an irreversible delete, all configured before anything runs and all proven after it does.
The work is front-loaded. Everything a user decides sits in configuration screens, and the consequence of a wrong decision only appears once a background job has already touched production data.
So the design problem is not the running of the job. It is making the configuration legible enough that the job is right the first time, and the result provable afterwards.
- 01
Administrator
System settings, email, session duration and retention periods, licence, storage profiles, groups, user management and announcements.
- 02
Workspace manager
Administrative privileges for workspace management. Approves or rejects requests from other users to access a source database profile.
- 03
Workspace member
Basic access to workspace features: profiles, metadata and extraction entities, execution and monitoring within their workspace.
- 04
Auditor
Reviews audit details. Can be configured to land directly on the audit page at login rather than the dashboard.
Access is permission-controlled throughout. Navbar options a user has no privilege for are not shown at all, and the landing page itself changes with role and user preference.
What I owned
the whole platform experience, end to end
- 01
UX strategy and architecture
Defined the six-stage spine every source family travels, the separation of metadata from extraction, and configuration from execution.
- 02
Requirement translation
Took feature-list requirements from product owners back to the task and the role behind them, and to which source families each had to serve.
- 03
Information architecture
Separated global destinations from workspace-scoped modules, and decided when a task belongs on a page, in a side panel, or in a dialog.
- 04
Interaction and visual design
Flows, wireframes, high-fidelity screens and prototypes across the platform. I owned the wireframes and final designs, and signed off designs before build.
- 05
Design system
Worked to one shared component language across this product and the archive platform it feeds, so patterns common to both stayed common rather than diverging into two products.
- 06
Handoff and design QA
Wrote the developer spec, reviewed built UI against approved designs with engineering, and drove inconsistencies to closure.
- 07
Leadership
Directed a small UX team, ran the review gate before high-fidelity work, and facilitated reviews with product, engineering and QA.
Closest to the client
what informed the design, and how it was checked
Discovery ran through the teams closest to our clients. The implementation team works with clients directly and brings their requirements, questions and enhancement requests into the product.
Discovery inputs
- Implementation-team expectations and recurring pain points
- Client feedback, relayed or raised directly by client teams
- Client expectations and implementation challenges
- Business requirements and stakeholder objectives
- Competitive analysis and product research
Design validation
- UX and design reviews inside the team
- Stakeholder walkthroughs and demonstrations
- Technical and engineering feasibility discussions
- QA and design review before release
- Implementation review against approved designs
Source: internal implementation team, informed by their client experience.
One path for most features
from requirement to shipped screen, with a design gate before build
Most feature work followed a consistent path from requirements and stakeholder discussions through UX definition, design review, implementation and UX review.
- 01
Stakeholder reviews
Requirements discussed with product, BAs, engineering and the teams closest to clients.
- 02
Wireframes first
I settled structure, states and edge cases in low fidelity before visual design.
- 03
Final design
I produced the high-fidelity screens, including error, empty and scale states where needed.
- 04
A written gate
Requirement documents named me to confirm the final design before UI implementation.
A typical sequence. Smaller changes combined or skipped steps.
Five patterns
that the source families kept pointing at
- 01
Variety belongs inside the shell
Nine source families differ in what they ask, not in how they are set up. One wizard, variable questions.
- 02
Complexity should graduate, not average
Four extraction modes on one ladder, from no configuration to hand-written SQL.
- 03
Direct manipulation earns its cost when the rule is visual
A join is a relationship between two columns, so validity shows during the drag.
- 04
Defaults are decisions made by omission
Files extracted with system-generated names are technically complete and practically unusable. The default has to be the safe outcome for users who never open the option.
- 05
Configuration screens carry the risk
Nothing fails in front of the user. It fails in a job, hours later, against production data.
One spine, six stages
that every source type travels along
Nine source families could have become nine products. The architecture decision was to make every source travel the same six stages, and let only the questions inside each stage differ by type.
Source profile (connect and learn the structure) → Workspace (scope, users and groups) → Metadata entity (tables, columns, relationships) → Extraction entity (what to take, and how) → Execution (run, schedule or template) → Monitoring (watch, log and prove).
Every extractor reuses this spine, relational, collaboration store, file share, mainframe, flat file and report archive alike. The guide says so plainly: the remaining tools follow the same steps in their own workspace.
- 01
The workspace is the unit of scope
A workspace carries the source profile, its users, its groups and its jobs. Access, ownership and audit all resolve to it, so nothing has to be scoped twice.
- 02
Metadata is separated from extraction
One metadata entity describes what the source contains. Many extraction entities decide what to take from it. Describing and selecting are different jobs, done by different screens.
- 03
Configuration is separated from execution
An entity is defined once and executed many times, immediately, on a schedule, or as a saved template for an external scheduler.
Where the spine is reused: relational · collaboration store · file share · mainframe file system · flat file · report archive · multi-value database · custom connectors. Each gets its own workspace and its own entities. None gets its own flow.
One wizard, nine sources
where only the questions change
Every source profile is created through the same wizard. A relational source adds a schema step. Flat file, mainframe and custom connectors add a format questionnaire. A report archive adds report inputs. A multi-value database adds a metadata upload. The shell never changes.
Basic info → Questionnaire (by type) → Connectivity → Schema or library (by type) → Create workspace → Review. Dashed steps appear only for the source types that need them. A user who has created one profile has created them all.
- 01
The shell carries the wayfinding
Step position, mandatory fields and Next are identical across all nine source families, so the type-specific questions are the only thing a user has to read.
- 02
Connection is verified before schema
Test Connection sits inside the connectivity step. Schema selection is unreachable until the connection resolves, so a bad host fails in the form rather than in a job.
- 03
Schema can be chosen or uploaded
Choose Schema lists what the connection found. Upload Schema takes a CSV, with a downloadable template, for users who already know exactly what they want.
Only the questions step varies: relational adds a schema step; flat file, mainframe and custom add a format questionnaire; a report archive adds report inputs; a multi-value database adds a metadata upload.
Room for a new capability
change data capture, placed inside the existing model
Change data capture moves ongoing source changes to the target, in batches or continuously. It was added after the core platform was in place.
- 01
A new entity, not a new flow
The requirement and my wireframes placed CDC as another entity type and another execution type, inside screens users already knew.
- 02
Policies with stated defaults
Table drops, truncates and alters, and record updates and deletes, mirror the source by default, with an option to ignore each. Inserts always apply.
- 03
Two entry points, one job
A job starts from the entity or from the execution screen, with the same configuration either way.
- 04
Existing monitoring
Manual runs appear in status monitoring and scheduled runs in schedule monitoring, like other jobs.
Shipped.
Making a join safe to draw
direct manipulation, with the rules enforced live
Relationships between tables drive how data is extracted together. They were the most technical thing a user had to create, and the easiest to get quietly wrong.
- 01
Drag the column, not a form field
A join is a relationship between two columns, so the interaction is dragging one onto the other rather than choosing both from dropdowns and hoping they match.
- 02
Validity is shown during the drag
A compatible target highlights white. A datatype mismatch highlights red, at the moment of the drop rather than on submit.
- 03
Check and Add, not just Add
The system checks whether the join already exists before creating it, so repeated attempts cannot quietly produce duplicates.
- 04
User-defined joins are labelled
Created joins are listed as USER DEFINED alongside the ones discovered from the source, so authored structure is always distinguishable from inherited structure.
Add Join. Primary table columns on the left, secondary on the right, with the instruction stated in the panel and Check & Add disabled until a valid pair exists. The same builder produces the filename join.
Two ways to list entities
and why grouping beat browsing
Entities are listed by category. A workspace can hold many categories, and each category can hold many entities. I explored two ways to present that.
- 01
Titled card lists per category: explored
Each category gets a heading, with its entities as cards beneath it. Reads well with three or four categories. Beyond that the page becomes a long scroll, and finding one category means passing every category above it.
- 02
Accordions per category: shipped
Each category is a collapsible row. Open the one you want, leave the rest closed. Every category stays visible and reachable in one screen height. The user opens what they need instead of scrolling past what they do not.
Four ways to say what to take
from no configuration to hand-written SQL
Defining an extraction is where the range of users is widest. Someone archiving a whole legacy database wants none of it. Someone extracting one reporting slice wants all of it. The same screen serves both by offering four modes on one ladder.
The ladder matters more than any one rung. A user starts at Quick Configuration and moves right only when the job demands it, without ever leaving the tool or restarting the entity.
- 01
Quick Configuration: everything, with rules
Extract the whole database. Empty schema means all schemas; empty rule means all tables. Name or regex rules narrow it, for tables or views.
- 02
Table mode: pick and shape
Choose schemas, move tables into a selected list, apply filter conditions, and configure blob columns per table.
- 03
SIP mode: structured for ingestion
Table selection plus drag-and-drop relationship editing, then a downloadable PDI schema tied to a holding name carried through to ingestion.
- 04
Query mode: hand-written SQL
Queries typed in the editor or uploaded as a file, validated on entry, with a required schema-table title format.
Two safeguards apply to every mode. Queries are validated on entry, including inside uploaded files, so a malformed query is rejected at the upload stage rather than failing downstream in a running job. Sample data settings let a user extract a limited row count first, so an extraction can be proved against real data before it is run in full.
A job is a pipeline, not a form
so the shape of the work is visible before it runs
An extraction job has a source, a connection, an entity, output settings and optional downstream tasks such as IA ingestion and application creation. Presented as a form, that is a long scroll with no sense of sequence.
Define (saved configuration) → Validate (before anything runs) → Execute (now, scheduled or templated) → Prove (counts, logs, reports). Configuration is separated from execution, so an entity defined once can run on a schedule or hand off to an external scheduler without being rebuilt.
- 01
The pipeline is the navigation
Each node opens its own configuration. The canvas shows what is set and what is still empty, so an incomplete job is visible rather than discovered on submit.
- 02
Output is a first-class decision
Ten formats, each with different needs: SIP takes holding and producer names, delimited formats take chunk settings, ORC takes neither because it cannot chunk. The panel changes with the format.
- 03
Downstream tasks attach here
IA ingestion and IA app creation are added to the same job rather than run separately, so the whole chain is scheduled and monitored as one thing.
Three run modes, one configuration: scheduled now (run immediately) · scheduled later (a future date and time) · save job template (reused by an external scheduler for live archival).
Sources that don’t match the model
extracting from folders and files as they actually are
Flat-file extraction expected one folder structure: schema, then table, then data file. A client team’s sources did not follow it. Extracted unstructured files also arrived with system-generated IDs as their names.
- 01
Ask before assuming
The wizard asks whether the source follows the expected structure, and shows that structure beside the question.
- 02
Three layouts, explained
A single schema folder, several schema folders, or all files in one folder, each with a note on when to use it. A schema template upload is the alternative.
- 03
Table names from file names
Users define how schema and table names appear in file names, with a live sample of the format.
- 04
File names from the data
Extracted files can take their name from a column in the same or a related table, instead of a generated ID.
Shipped. After extraction, users choose to move, delete or leave processed files. Edge cases were worked through with the BA and engineering.
Designing around safe defaults
what happens when the user makes no choice
Two later additions turned on the same question: what the system should do when a user does not change a setting.
- 01
Fail rather than lose data
An invalid blob path fails the job by default. Continuing with an empty value is an explicit opt-in.
- 02
Both outcomes explained
The option explains what each choice does, because the requirement named misunderstanding as a risk.
- 03
A default that cannot disappear
A built-in local storage profile cannot be deleted, though another profile can be made the default.
- 04
Running work is protected
Deleting a storage profile does not affect running jobs. Storage details can also be entered for a single job without saving a profile.
Shipped. The requirements set these defaults; I designed how the choices and their consequences appear. The invalid-path option was requested by the implementation team.
The one action with no way back
deletion at source, acknowledged before it runs
Deletion removes records from the source system after extraction. It is exclusive to the RDBMS tool, it cannot be rolled back, and it can orphan records in tables the user was not thinking about.
So the design does not put deletion behind a confirmation. It puts a review stage in front of it, and makes the dependency the user has to resolve.
- 01
Review, not confirm
Deletion opens a dedicated review page, not a dialog. The step exists to be read.
- 02
Dependencies are acknowledged
The user must acknowledge dependent tables, so orphan records and broken constraints are a decision rather than a side effect.
- 03
Three explicit selections
Select all standalone tables. Select all linked tables to force delete. Deselect all. Force is named as force.
- 04
Saved, then scheduled
The selection is saved as configuration and the deletion is scheduled as a job, so the destructive act is never one click away from the review.
The same principle runs through the rest of the tool. An entity can be set to final so it can no longer be modified, or locked, or cloned rather than edited. Where an action cannot be undone, the product makes the user pass through a state that names the consequence.
Watching work you cannot see
live status, live logs, and the inputs that produced them
Everything the tool does runs in the background. Workspace creation, pre-analysis, metadata generation, extraction, ingestion, deletion. The user configures, commits, and then has nothing to look at unless the product gives them something.
- 01
Status arrives without asking
Live updates are pushed over a socket for workspace creation, source creation and deletion, entity extraction and metadata generation, so the view stays current without a refresh.
- 02
Logs are live, then downloadable
Live logs stream while the job runs and become a downloadable artefact once it finishes, so the same record serves operations during and evidence after.
- 03
The inputs are recoverable
The job-inputs view reopens the configuration the job actually ran with: profile, host, port, task type, schemas, output path. A failed job can be diagnosed without reconstructing it.
Scheduled work gets its own view. Jobs scheduled for later sit in schedule monitoring until they trigger, with tool and status filters, a link through to their run, and a schedule edit history. Work that has not started yet is a different question from work in flight, so it is a different screen. On the operations dashboard, metric cards link to their sections, charts group workspaces beyond the top five as “Other” and use patterns alongside colour, and inconsistent data shows an error rather than wrong numbers.
Making the licence visible
time and volume limits, shown to the right people
Both time and data-volume limits needed to be visible so teams could act before reaching the licence limit.
- 01
Consumption, level by level
Allocated versus consumed volume, then volume per workspace, then trends, with an export at each level.
- 02
Severity that escalates
A warning at 30 days and a critical alert at 7. A dismissed warning returns as critical when the level escalates.
- 03
Messages by role
Admins get a link to licence settings. Other users are told to contact their administrator.
- 04
Visible, not blocking
Alerts are dismissible per user and return at the next login until the licence is renewed.
Shipped. I wrote the volume-licensing brief together with the BA.
A screen the client asked us to rethink
from everything at once to a list you can read
The metadata entity is where a user understands what a source system actually contains. The first design put the whole job on one screen. Client feedback, relayed through the implementation team, was that it needed to be more user friendly.
- 01
Before: three jobs competing, none prominent
Tree listing, relationship panel and join builder competing on one screen. Everything visible, nothing prominent.
- 02
After: the tree owns the screen
A scannable table: schema, table, unstructured flag, row count, column count. The overview became an overview.
- 03
After: detail is summoned
Description, summary and column details open in a side panel, so depth never costs the user their place in the list.
- 04
Scanning before configuring
The table answers what is in this source. Row and column counts and an unstructured flag are readable without opening anything.
- 05
Depth moved to a panel
Table description, summary and column details became a side panel rather than a competing region on the page.
- 06
The same pattern, reused
The panel is the pattern used elsewhere for detail on demand, so the redesign added no new vocabulary.
Source: client feedback relayed through the internal implementation team.
One language, two products
holding a shared vocabulary while both products grew
This product and the archive platform it feeds are separate products used by the same customers, often by the same people. They were built on one shared component language, so the harder job was not either product. It was keeping what they had in common actually common.
What the shared language covered
- The wizard shell: creation flows step, validate and review the same way in both products
- Job scheduling and background work: run now, schedule later, save as template, then hand off to job monitoring
- Job monitoring and evidence: job listing, expandable detail, downloadable logs and reports
- Detail on demand: a side panel for depth, so a list never has to become a page
- One component set, not two: shared controls, spacing and states maintained once rather than rebuilt per product
What this product asked that the other did not
- The shell had to be settled first. Nine source families meant the wizard, the entity model and the job pattern had to be decided once and hold, because every connector after the first was a variation rather than a new design
- Consistency had to be defended, not assumed. Each new source type arrived with its own vocabulary and its own reasons to be special. Most of the design leadership was deciding which differences were real and which were habit
- Feedback came through the implementation team. They sit closest to the client. The metadata entity redesign came from them, and taking that seriously meant rebuilding a screen that already worked technically
The archive platform has since been modernised onto a new design system, covered in that case study. This describes the shared language both products were built on.
Design outcomes and shipped capabilities
stated plainly
Experience outcomes
- One wizard shell across nine source families, so learning one profile type teaches the rest
- Extraction complexity graduated across four modes rather than averaged into one
- Joins created by direct manipulation, with datatype validity shown during the interaction
- Blob extractions that produce identifiable files instead of randomly named ones
Architecture outcomes
- Six-stage spine reused by every extractor rather than a separate flow per source type
- Metadata describing the source kept separate from extraction entities selecting from it
- Configuration separated from execution, so one entity runs now, later, or on a template
- Workspace established as the single unit of scope for access, ownership and audit
- 01
Irreversible deletion gated behind a review stage that names dependencies
- 02
Query validation moved to the point of entry, including uploaded files
- 03
Sample extraction available before committing a full run
- 04
One shared component language with the archive platform, so common patterns were maintained once
Shipped after the core build: change data capture · flexible flat-file sources · cloud output storage · licence visibility and expiry alerts · operations dashboard. These describe how the product works, not a projection. Quantified results were not captured on this product, so none are claimed here.
What I would do differently
the review I had available and did not use
The metadata entity was redesigned after the client asked for something more usable. The feedback was right and the redesign was better, but it arrived after the screen had been built.
- 01
Used the implementation team as an input
They gave me requirements, client expectations and the pain points they saw in the field. That shaped what the screens needed to do.
- 02
Did not use them as a review
They already knew what clients expected. I never put the rough design in front of them before it went to high fidelity and into build.
What carries forward: keep variety inside one shell · graduate complexity rather than average it · treat defaults as design decisions.