Data Transformation Experience Design

Internship Project

June - August 2019, 10 weeks



State Street Corporation 


State Street Corporation is an American financial services and bank holding company headquartered  in Boston with operations worldwide.

Why Data Transformation ?

State Street has large volumes of both structured and unstructured data. To ensure a high level of data quality and accessibility for business intelligence and big data analytics applications, we need to do data transformation to standardize the format or structure of all the metadata.

Data transformation is a combination of technical and analytical processes in data management.

The data management  system allows

  • Business Analysts to do the transformation efficiently

  • Engineers to review and implement the transformation consistently




Secondary User

Primary User



What Is Data Transformation?

Data transformation contains two parts: Data Mapping  & Make Transformation Rules

Here I use an analogy to illustrate data transformation :

Data Mapping


Building the Relationship


Data Transformation 


Defining the Relationship

Attached Detailed Example For Data Transformation

(In case you would like to learn more about data transformation)

Current Solution  --  20+ Spreadsheets in One Excel File

Currently,  Business Analysts do all the data processing manually using excel. It's so easy to make typos or cause errors.

And each Business Analyst needs to wait for others since there is no way for them to work simultaneously. 


Data Editing


Data Transformation 


Tracking Changes




Here is the example of how they do the data mapping excel file. First, they selected two lists of data, one has already been pasted into the file as a template, the other used as reference to find the right elements and then copy and paste into the right rows.

Gaps In Current Workflow

 Business Analysts need to circle back with stakeholders frequently to define business needs. After they get some updates, they will work on excel files individually and hold group meetings afterward to resolve current iteration problems. When Business Analysts finished this iteration, they will send the latest version to Developers to implement all the updated data transformation. Just like it is so hard to read others' codes, the excel file looks like a puzzle to developers with various marks which can only be understood by the owners. Even Business Analysts themselves consider this is confusing.

Work Individually then group meet to solve current iteration problems

Circle back with stakeholders to define business needs

"We need to do all the input manually. It's too time-consuming."

Unclear Responsibility among Business Analysts

  • Each BA has the full right to change all the data

  • No methods to track who changed the data

  • Unnormalized mark methods

Communication Gap Between Business Analysts & Engineers

  • Find the file confusing and incomprehensible

  • the only way to identify all the changes is open all 20+ spreadsheets and check through all the colorful rows one by one

  • Don't know who to ask for clarification or verification

  • Always wait long time to get feedback

"It's hard to tell the differences among iterations intuitively."

Confusing Mark Methods

The standard legend is very simple, which only contains three mark methods. So when new problems occur during Business Analysts' work session, they may just create a new one to remind themselves, like need to be verified, ask our clients, redundant information. It's so easy to cause chaotics in the file since each Business Analyst has the full right to do any change. Here are some examples of various marks made by different Business Analysts existing in the current excel file.

Standard legend


Problem Synthesis Model

Inconsistency In Different Levels

There is inconsistency in different levels: from attributes to elements, to iterative spreadsheets, to version control, to accountability of Business Analysts, to the collaboration between Business Analysts and Developers and etc..

  • Unclear information hierarchy between elements and attributes. Attributes are affiliations of elements. The visibility of elements should higher than attributes for users to pinpoint the key information quickly and make horizontal comparisons between mapped elements efficiently.

  • Uninformed changes out of expectation & Require large efforts to tell the difference. Users need to open all the sheets one by one to learn about the latest changes. Current legend combined with chaotic marks made by different Business Analysts freely is really confusing and adds difficulty to understand what had happened in this excel file, causing lots of problems in readability and comprehension.

  • Ambiguous responsibility & accountability. Each Business Analyst has the full right to make changes in all spreadsheets and there is no way to track who made the changes and who should be responsible for the errors. Although we use the iterative files to store all the historic versions. Some corner cases can happen and the changes are irreversible.

  • Feel disconnected from each other. We have the internal software named Webex to message whomever you want to talk to. However, the problem is developers don't know who they should message and lack of instant feedback is a detrimental problem in an agile environment. Though they have other ways to meet each other in 15 min stand-up meeting twice a week, that the situation to report the project process instead of detailed misunderstanding in excel files.

  • Unclarity & Inconsistency

    • Elements —— attributes
    • Elements —— elements

    • Elements —— files

    • Files —— BAs

    • BAs—— BAs

    • BAs —— ENGs

Design Goal

How Might We

Help BAs & ENGs to maintain the global   CONSISTENCY 


Challenge 1: Global View OR NOT?

To gain more domain knowledge and have a thorough understanding of data mapping, I conducted literature research on data mapping products and did some analysis. There two normal options on the market. One shows all the tables in one window which can show where the data origins, what happens to it and where it moves over time clearly. But practically, the lines won't be so neat and tidy as shown in the picture. The other combine multiple tables into several folders, and you can just click to expand or collapse the tables then do the mapping between the elements. However, this may overload our users and they need to search frequently to pinpoint the right element.

Competitive Analysis to Boost Ideation

multiple tables in one window

two tables in one window

Global-View Is Easy to Input, While Hard to Read

"We want a global view to see the big picture. Unlike Excel, limited within two tables. "
"Yeah...It's a fancy feature. But we don't so much time. Also, it's hard to read!"

Business Analysts expressed their urgent needs for a global view when doing data mapping, for a global view would allow them to learn more context and do the mapping accurately and efficiently. How Engineers oppose the idea for "That is a nice to have feature, not a must to have". And it would multiple the technology efforts we need to devote and may take much more time than we expected. But the most convincing one is "It's hard to read!"  Engineers need to read all the info from the window and translate them into code. Information presented in the window is overwhelming and chaotic for Engineers to understand the relationship between different elements. This will cause extra effort for them to digest the information.

Achieve An Agreement: Two Tables Each Time

After discussion, we reach an agreement that two tables in one window with a clear information hierarchy. From the wireframe, users can easily tell the differences between the target elements, source elements, and attributes. Business analysts can also make transformation rules when they finish the mapping of each element.

Clear Information Hierarchy

Easy To Make Comparison

Challenge 2: Drop Down Is Best !?

Developers Strongly Support Drop Down

Design and Develop started at the same time. When I still don't have enough time to have a better understanding of users' needs, developers convinced us that drop down is the best approach to do the data mapping,  for minimal technology efforts and clear relationship between the target elements and source elements. Then I designed two versions of wireframes for quick prototyping and chose the last one for easy to compare between the target elements & source elements, more efficient for Business Analysts to see more elements in one window.

Version 1

Version 2

Unexpected Developers’ Input.

Design and develop start at the same time. When I was trying to envision the entire project, Engineers already began developing based on the wireframes and added some new features on their own. But honestly speaking, they came up with some useful features that I didn't think of or touch the users' needs. 

  • Categorized and named the columns in a simple and intuitive way 

  • 3 types of relationships: one-to-one, one-to-multiple, none

  • Use "+", " - " buttons to add or subtract one row to change the relationship

Drop Down Turns Out To be a Disaster

  • Lack of overview

    • Overwhelming numbers of options can be bewildering and intimidating
    • Hard to navigate

  • Scroll issues

    • If the mouse cursor is outside of the drop-down, users will most likely scroll down the page instead of the drop-down, hiding the drop-down options from the screen.
  • Inconsistent UI

    • Cause friction with other information
    • Introduce unnecessary friction into the checkout process by hiding information that could simply have been exposed.

  • Miss the one-to-multiple relationship​

It turned out drop down is a disaster for Business Analysts. And they would like to go back to excel.

"I can't see where this is better than Excel. The choices are overwhelming."

multiple - to - one

one - to - one


one - to - multiple 


The Trade-off between Technology Constraints and User Experience

We held another round of group discussion trying to find out a better solution which keeps the balance between user experience and technology constraints. Then we selected to use the draw lines method for appropriate technology effort, efficiency.


Challenge 3: NEEDS Behind BAs' Workflow

Wireframes For Version 1

Wireframes For Version 2

Re-understand Workflow: Mapping & Making Transformation Rules

However, from the usability testing, we found out that the mapping efficiency is lowered for scrolling up and down frequently to find the hidden elements out of the window. Also, Business Analysts rarely check the attributes of the elements when doing the mapping. The redundant information will cause friction and distraction. Business Analysts don't need to learn everything to conduct the operation. Full disclosure is less helpful than just the key pieces of information that are most important in this scenario. But when doing the transformation, all relevant information is necessary for rulemaking.

Data Transformation

Data Mapping

Final Design



Test Often, Be Bold

Real-time feedback is critical in agile development. Communicate with them directly when in doubt. Have instant feedback, using feedback to make the most informed decisions and iterate on the employee experience quickly. 

Get Comfortable of Working with Ambiguities

There are many times when I can't get feedback from seniors and users at any time.  It's important to have the ability to iterate on your own and constantly criticize your design from different dimensions.

Have Fun, Make Friends

It’s an enjoyable experience to see the project from the engineers’ perspective, understand their mental model and choose the optimal way to collaborate with them smoothly!


Contextual Design In Agile 

Contextual design helps create an immersion experience and buy-in in agile environment.


Working with empathy data: consolidate the low-level detailed data and presented in models that can be easily consumed by teams

It's  a great way to make the message sticky, foster empathy amongst stakeholders, and invoke the desire to make changes.

View Other Projects !




© 2020  by  Scarlett  Wu - All rights reserved.