Skip to main content

Product Requirements Document

Mission​

We want to enable self-service capability for data movement in the UI at a granular and independent level.

Background​

Today, no users of Empower can perform any self-service on their data movement. More specifically, to configure inbound, outbound, or intra-bound data movement, they must directly manipulate StateDB. We aim to build Data Flows as a feature to make this an easier experience. Data Flows leverages a user interface through which to manipulate StateDB to configure and schedule data movement of various types.

📘 The intended impact of flows

is to enable users to monitor and manage their data estate themselves, rather than having to rely on our managed services teams to do it for them. For users who rely on our delivery teams to do this today, this could save them between $50k-100k (SWAG) off their initial implementation, and free up our own delivery team up to 20 hours a month per client (SWAG) to provide more value on retainer.

Targeted Personas​

The primary target users of this feature are customer data engineering, customer data architect personas, and IT managers who don’t want or have the capacity to manipulate StateDB. Secondary target users could include members of the Empower delivery team, who regularly update StateDB.

Current Behavior​

Outcomes / Goals​

  • To develop self-service capability for data movement in the UI
    Please see the attached outline of intent for data flows for more context.

User Stories​

Use cases​

Creating a new Data Source​

  • Primary Actor: Data Engineer (client or delivery)
  • Description: Users should be able to create new data sources from the UI.
  • Preconditions: User has the necessary connection information for the given data source type.
  • Postconditions: The newly created data source exists and is viewable in the UI. The user may now trigger a metadata extraction for this source.
  • Flow:
    • User navigates to the data sources page.
    • User clicks “+” to create a new data source.
    • User can select/search for a specific data source type (this functionality already exists)
    • A form is presented to the user to fill out relevant connection information. At the bottom of this form, the user may CANCEL or SAVE AND CONNECT.
    • User can now enter relevant connection information in the form.
    • The user presses SAVE AND CONNECT, this immediately creates a test connection task.
    • If this connection fails/succeeds, this is displayed in the newly created data source’s test connection status (this is existing functionality)
    • In either case, the user can trigger an extract metadata job on this source.

Extracting Metadata for a Data Source​

  • Primary Actor: Data Engineer (client or delivery)
  • Description: Regardless of a connection test status, a user should be able to trigger a metadata extraction for a given data source of any type. After extracting metadata successfully, the user should be able to view this metadata as a view-only metadata table.
  • Preconditions: User has a data source already created for metadata extraction.
  • Postconditions: Data source has a viewable metadata catalog upon successful metadata extraction.
  • Flow:
    • User navigates to the data sources page.
    • User clicks on an existing data source (regardless of connection test status)
    • User can click on EXTRACT METADATA to set off an immediate metadata extraction job.
    • Within 2 minutes, the UI displays whether this extraction was successful or not.
    • If successful, the user can METADATA CATALOG (not visible unless there is at least one successful metadata extraction within the lifetime of this data source).
    • The status of the last metadata extraction for this data source is updated (this is existing functionality)

Viewing Metadata for a Data Source​

  • Primary Actor: Data Engineer (client or delivery)
  • Description: If there has been at least one successful metadata extraction within the lifetime of a data source, the user should be able to view the metadata specific to that source as a read-only catalog.
  • Preconditions: There is at least one successful metadata extraction within the lifetime of this data source.
  • Postconditions: The metadata catalog for this source is viewable.
  • Flow:
    • User navigates to the data sources page.
    • User clicks on an existing data source (with at least one successful metadata extraction over the lifetime of this source)
    • User can click on the METADATA CATALOG
    • The metadata catalog for this source is displayed. This looks much like any acquisition flow’s catalog with some changes:
      • It is VIEW ONLY, the user is not able to interact with any pieces of this table, except for search and filter.
      • The columns displayed are as follows:
        • Schema Name
        • Object Name
        • Est. Rows
        • Field Details (functionality exists)
        • Enabled in
          • This is a new column that deviates from existing functionality
          • This column will list out the IDs of all data acquisition flows that have this object enabled.
        • FUTURE STATE: clicking on the ID of any flow will take you to its metadata catalog, filtered so that this object is the only one visible. The user should be easily able to remove this filter to see all objects in that flow.

Archiving a Data Source (w/ Acquisition Flows!)​

  • Primary Actor: Data Engineer (client or delivery)
  • Description: Users should be able to archive a data source that has acquisition flows attached to it. This will have a butterfly affect by archiving any attached data flows to this source.
  • Preconditions: There is a data source that has a successful metadata extraction in its lifetime, with one or more acquisition flows attached to it.
  • Postconditions: The user’s desired source has been archived, the user has been notified of any downstream effects of this action, and any downstream flows have also been archived.
  • Flow:
    • User navigates to the data sources page page.
    • The user clicks “…” and clicks on “ARCHIVE”
    • The user is then presented a warning “WARNING: achiving this data source will result in the additional archival of # Acquisition Flows. Please confirm you wish to take this action – CANCEL | ARCHIVE”
    • Continuing the archival will result in the data source and any subsequent flows to be archived.

Creating new Acquisition Flows​

  • Primary Actor: Data Engineer (client or delivery)
  • Description: Users should be able to create new data acquisition flows using the UI. There can be multiple flows created for a given data source, BUT the data source must have at least one successful metadata catalog extraction for a flow to be created on it.
  • Preconditions: The user’s desired source has at least one successful metadata catalog extraction in its entire lifetime.
  • Postconditions: A new, unscheduled acquisition flow now exists for this data source.
  • Flow:
    • User navigates to the acquisition flows page.
    • The user clicks “+” to create a new acquisition flow.
    • The user is then presented a form with several fields to configure, including the name of the acquisition flow, and dropdown list to select the data source. Note only data sources with at least one successful metadata extraction in their history will be shown in this drop-down list.
    • QoL Feature: The user should be able to quickly search this dropdown list by typing.
    • Clicking “SAVE” will cause this flow to be saved. The newly created flow will be unscheduled and will appear at the top of the acquisition flow list.

Configuring an Acquisition Flow​

  • Primary Actor: Data Engineer (client or delivery)
  • Description: Users should be able to configure an acquisition flow, in that they should be able to select which objects from the flow’s source will be acquired, and the load strategy for each enabled object.
  • Preconditions: The user’s selected acquisition flow exists.
  • Postconditions: The user’s modifications to the flow’s metadata catalog have been saved.
  • Flow:
    • User navigates to the acquisition flows page.
    • User clicks on METADATA CATALOG for the desired flow.
    • The user is brought to a table representing that flow’s metadata catalog. By default every object is set to a “FULL” load strategy and is disabled. From here, the user can view any object’s schema and object name information, its estimated row count, and field details.
    • The user may enable any object, select a load strategy for that object, and define the object’s watermark column, watermark type, and candidate key (this is existing functionality today)
    • The user may search the catalog by object name, and they may filter the catalog by schema name, est. rows, load strategy, watermark type, and whether the objects are enabled or disabled.
    • When an object is enabled, this adds the object to this specific flow. Note that an object may be a member of any number of acquisition flows. But an acquisition flow can only ever have 1 data source.
      • If the user edits the acquisition flow to have a different data source after configuring the flow’s metadata catalog, then the prior metadata catalog is removed from this flow, and the default metadata catalog for the new data source will be displayed.
    • Upon modifying any field in the metadata catalog, the flow configuration is automatically saved.

Viewing Analytics Engineering Flows​

  • Primary Actor: Data Engineer (client or delivery)
  • Description: Viewing existing analytics engineering flows through the UI will allow users to be aware of the existing transformation processes and their status. This will help me stay informed about the data transformation progress and make any necessary adjustments. However, creating new analytics engineering flows using the UI will be reserved for future versions of Empower.
  • Preconditions: User has an analytics engineering flow already defined in StateDB.
  • Postconditions: User can view and monitor the flow, both the model build configuration (Advanced Settings StateDB tables for model, model entity, entity column, entity dependency. The user may edit this configuration from advanced options as defined in the Advanced Options initiative) and the flow configuration (view when flow has been historically run and respective statuses).
  • Flow:
    • User navigates to the analytics engineering flows page.
    • User can click on the configuration for the analytics engineering flow, this will take them to the existing Advanced Analytics tables for modeling.
    • User can view flow details on this page (and click in for more details).

Creating Analytics Engineering Flows​

(just like acquisition and publishing, press “+” and only define name, press save)

  • Primary Actor: Data Engineer (client or delivery)
  • Description: Users should be able to create new analytics engineering flows using the UI.
  • Preconditions: N/A
  • Postconditions: A new, unscheduled analytics engineering flow now exists. Note: there will not be any configured data created for this flow, other than a model entry in statedb attached to this flow.
  • Flow:
    • User navigates to the analytics engineering flows page.
    • The user clicks “+” to create a new analytics engineering flow.
    • The user is then presented a form with several fields to configure, namely only the name of the flow.
      • QoL Feature: The user should be able to quickly search this dropdown list by typing.
    • Clicking “SAVE” will cause this flow to be saved. The newly created flow will be unscheduled and will appear at the top of the analytics engineering flow list.

Configuring Analytics Engineering Flows​

(Advanced Settings StateDB table manipulation)

  • Primary Actor: Data Engineer (client or delivery)
  • Description: Users should be able to edit and modify analytics engineering flows using the UI. Note, users will only be able to manipulate the StateDB tables that orchestrate dims and facts. They will still have to go into Databricks to modify the staging notebooks.
  • Preconditions: The analytics engineering flow in question has already been created. The staging notebooks used during the flow have been built.
  • Postconditions: The dims/facts stages and the entity and entity dependencies have been defined and configured.
  • Flow:
    • User navigates to the analytics engineering flows page.
    • The user clicks “VIEW CONFIGURATION” on the relevant flow
    • The user is taken to a page that looks exactly like the Advanced Settings tables, with tabs for Model, Entity, Entity Column, and Entity Dependency (the four model builder tables)
    • The user can edit any of these tables as the existing functionality is today.
    • Any edits are automatically saved on change, just like the existing functionality today.

Creating Publishing Flows​

  • Primary Actor: Data Engineer (client or delivery)
  • Description: From the UI, a user can create new publishing flows, which will result in the creation of an unscheduled outbound data flow.
  • Preconditions: User has data tables within their lakehouse to publish. Additionally, user has a supported data source target to which to publish data.
  • Postconditions: User has created a new publishing flow to push tables from their lakehouse to the supported data source target (SFTP, SQL Server only in this version)
  • Flow:
    • User navigates to the publishing flows page.
    • User presses “+” to create a new publishing flow.
    • The user is then presented a form with several fields to configure, including the name of the publishing flow, a dropdown list to select the data source target, and (only if the data source target is an SFTP type) a file format dropdown (to select between CSV, TSV, or Parquet)
    • Upon clicking save, the user will see a new, unscheduled, publishing flow at the top of the list of existing publishing flows.

Viewing and Editing Publishing Flows​

  • Primary Actor: Data engineer (client or delivery)
  • Description: From the UI, users should be enabled to view and edit existing publishing flows from the publishing flows tab.
  • Preconditions: User has existing publishing flows to view
  • Postconditions: User can view existing flows, and any edits made to existing flows are saved.
  • Flow:
    • User navigates to the publishing flows tab.
    • User is able to view existing publishing flows including file format info (if relevant) and scheduling information, with the option to click into the configuration and into the flow details.
    • If the user elects to click into the configuration, they are able to view a global lakehouse metadata catalog of bronze, silver, and gold tables.
    • Users are able to add objects to this flow at-will similar to acquisition flow metadata catalogs by using a simple toggle. They should be able to filter objects by medallion type and schema name and be able to search by object name.
    • Users should be able to set conditional modifiers on an object using a SQL statement WHERE clause
      • This will not be supported in the UI in MVP.
      • Rather, this will be setup in Advanced Settings directly.
      • If an object has a conditional modifier already, this column will read “YES”, else “NO”
    • Editing this publishing “metadata catalog” will result in automatic saving, just like acquisition metadata catalogs do.

OnDemand Triggering Flows​

  • Primary Actor: Data engineer (client or delivery)
  • Description: From the UI, users should be enabled to trigger any type of flow to run on-demand (i.e. right now).
  • Preconditions: User has a configured flow, ready to be triggered.
  • Postconditions: The user’s selected flow is triggered to run and is in a queued state.
  • Flow:
    • User navigates to the relevant flows page (could be ANY of the flow tabs).
    • User clicks on “Run Flow”.
    • User is given a preview of the flow, with the option to cancel or “RUN” the flow.
      • If an acquisition flow, the preview is a read-only version of that flow’s metadata catalog.
      • If an analytics engineering flow, the preview is a read-only table of all the dims/facts that will be built during the flow run.
      • If a publishing flow, the preview is a read-only table of all the tables that will be published.
    • User clicks “RUN”.
    • Flow moves to queued state and user is returned to the relevant flows page (or flow details page if this user flow was begun there)

Scheduling Flows​

  • Primary Actor: Data engineer (client or delivery)
  • Description: From the UI, users should be able to schedule flows to run at specific times or with specific cadences using an intuitive user interface like Data Factory's pipeline scheduler. This includes scheduling flows to run by the minute, hourly, weekly, daily, monthly, or annually.
  • Preconditions: User has a configured flow, ready to be scheduled
  • Postconditions: The user’s selected flow is scheduled to run at the specified cadence/time.
  • Flow:
    • User navigates to the relevant flows page (could be ANY of the flow tabs).
    • User clicks on “Schedule Flow”.
    • A modal with schedule options pops up on the screen:
      • User can select the start and end dates, as well as repeatability of the flow in a format similar to ADF. [See Figma for granularity of scheduling]
      • User selects CONTINUE
    • User is shown a preview of the flow, with the option to cancel or “SCHEDULE” the flow.
      • If an acquisition flow, this is a read-only version of that flow’s metadata catalog.
      • If an analytics engineering flow, this is a read-only table of all the dims/facts that will be built during the flow run.
      • If a publishing flow, this is a read-only table of all the tables that will be published.
    • User clicks “SCHEDULE”.
    • The flow’s next scheduled run state is updated and user is returned back to the relevant flows page (or the flow details page if this user flow was begun there).

Viewing Historical Flow Runs​

  • Primary Actor: Data Engineer (client or delivery)
  • Description: From any flows page, a user should be able to view historical flow runs by clicking on VIEW FLOW DETAILS
  • Preconditions: There have been historical flow runs to view for the given flow
  • Postconditions: The user can view any previous flow run.
  • Flow:
    • User navigates to any Flow page.
    • User clicks on VIEW FLOW DETAILS of any flow with historical runs.
    • A page is displayed with a table of every previous flow, currently running flow, and the next scheduled flow (if not currently running).
    • At MVP, the required columns for all flows will be:
      • Run ID – This should link to a modal containing the historical configuration for this run.
      • Status
      • Time Started
      • Time Finished
      • Duration
    • Historical Configuration
      • Acquisition Flows: historical read-only metadata catalog as a modal from the established Data Acquisition Flow “View Configuration”
      • Analytics Engineering Flows: historical Entity, Entity Dependency, Entity Column, Models tables (read-only for all of these)
      • Publishing Flows: historical data source and read-only metadata catalog from the established Data Publishing Flow “View Configuration”

Requirements​

There are three types of data movement we want Data Flows to provide self-service capability for in the UI:

  • Acquisition Flows: the inbound movement of data
  • Analytics Engineering Flows: the transformation of the data into a usable data model
  • Publishing Flows: the outbound movement of data

Users can archive data sources at will. Upon initiating archiving a data source, a warning should be displayed notifying the user that # data flows will also be archived along with this source if they proceed. When a data source is archived, the data flow cannot be unarchived without also unarchiving the data source.

Users can create, modify, and remove acquisition flows using the user interface. Acquisition flows must be attached to a data source that has at least one successful metadata extraction within that sources history. Users may manually trigger metadata extraction from either the data source itself or from any acquisition flow attached to it. Users may edit the acquisition configuration for each flow, which determines the load strategies, watermarking, and candidate keys, etc, for each object in the metadata catalog for that specific flow.

~~Users can archive acquisition flows at will, and this will keep that flows historical run information viewable for the user. Users can archive data sources, which will automatically archive all associated acquisition flows. The user should receive a pop up alerting them to the repercussions of archiving the source, i.e. “WARNING: this will also archive ### acquisition flows.” ~~

User’s cannot delete a data source if there are any associated flows with that data source. The delete button should be disabled and a tooltip available on hover for such situations (publishing and acquisition).

Users can view analytics engineering flows that have already been made, create new ones, and archive/remove existing ones. They will not be able to modify analytics engineering flows in any creative UI, only through the Advanced Settings tables.

Users can create, modify, archive, and remove publishing flows using the user interface. Publishing flows also require a data source to be selected for outbound data movement, like acquisition flows. The selected data source type must also support data publishing.

Flows can be scheduled to run at select times/cadences, with a user interface similiar to Data Factory's pipeline scheduler. A user can schedule these flows to run hourly, weekly, daily, monthly, or annually.

A user can also trigger any type of flow on demand.

Furthermore, a user can make a flow dependent upon the successful execution of another flow, of any type. Flows can be chained together into complex dependency trees, but do not allow for cyclical dependencies. If a flow in a dependency chain FAILS, then the scheduled execution of any further dependent flows should cease. Dependency chaining functionality is not part of UI MVP but is a requirement for API functionality to meet Empower v1.18 feature parity, and therefore is in scope for the backend.

Functional​

  1. User Interface for creating, modifying, archiving, and deleting acquisition flows:
    1. Automatically create unscheduled acquisition flows when connecting to a new data source
    2. Modify existing acquisition flows.
    3. Archive or delete acquisition flows.
  2. User Interface for viewing existing analytics engineering flows. Note: Creating, modifying, archiving, and deleting analytics engineering flows using the UI will be reserved for future versions of Empower.
  3. User Interface for creating, modifying, archiving, and removing publishing flows.
    1. Select a data source for outbound data movement that supports data publishing.
  4. Flow scheduling and triggering with specific times or cadences from the UI.
    1. Schedule flows to run hourly, weekly, daily, monthly, or annually.
    2. Trigger any flow on demand.
  5. Creation and management of dependency trees between flows without cyclical dependencies using direct StateDB manipulation or API calls (i.e. NOT FROM THE UI).
    1. Cease scheduled execution of subsequent dependent flows when any flow in a dependency chain fails.
  6. Migration support from load groups into Flows. We want to ensure that there is a seamless transition from load groups to flows.
  7. Ability to view historical flow runs and configurations of those runs.

Non-functional requirements​

  1. Scalability: The feature should be able to handle increased data volume and a growing number of users without significantly impacting performance.
  2. Maintainability: The codebase should be well-documented and organized so that the engineering team can easily make updates or troubleshoot issues.
  3. Usability: The user interface should be intuitive and easy to use, enabling users to quickly understand and interact with the various elements.
  4. Performance: The feature should operate efficiently without causing noticeable delays or bottlenecks during data movement processes.
  5. Self-Servicable Error Handling: Users should be able to get visbility of and handle common errors, like wrong data source credentials and schema mismatches (e.g. modeling or publishing).

Out of Scope​

Data source archiving vs Data Flows “disabling”​

Users might be confused about the verbiage and action space of an archived flow vs a disabled flow.

  • Archived: force disabled from the data source level as the parent object.
  • Disabled: manually disabled by the user from the data flow level.

We may want to differentiate between these two in a future version of Data Flows, but this does not meet MVP requirements.

Dependencies & Constraints​

  • The successful implementation of Data Flows is dependent on integration with Azure Data Factory for triggering flows in its MVP stage.
  • Future iterations of Data Flows will depend on integration with Queue Interactive Clusters, such as Spark jobs reading from a queue for flow execution.

Assumptions​

  • VP will initially rely on Azure Data Factory (ADF) pipeline calls for triggering flows, with the expectation of transitioning to Queue Interactive Clusters (i.e., Spark jobs reading from a queue) in the future.
  • MVP will not include UI capability for modifying, creating, archiving, or deleting analytics engineering flows. The design for this functionality will be implemented in future iterations.
  • Dependency chaining will not be an available feature in the UI as part of the MVP, though the API should support it to match feature parity with Empower v1.18. Users with sufficient knowledge will be able to configure dependencies in StateDB.
  • If source metadata changes metadata extract process runs to verify it has the necessary objects to run the flow:
    • If existing object is removed from a source that is part of a flow, flow run will fail
    • If new object is added to a source, the metadata catalog is updated with new

Risks​

  1. Inefficient coordination and communication across teams
    1. Risk:
      1. Miscommunications or delays in sharing essential information could lead to development bottlenecks, duplicated work, or misalignment in feature implementation.
    2. Mitigation:
      1. Establish clear communication channels and protocols for all teams involved. Utilize tools like shared documents, communication platforms, and project management software to ensure information is easily accessible.
      2. Schedule regular cross-team meetings or sync-ups to discuss progress, share updates, and address any questions or blockers.
  2. Integration challenges between different components developed by separate teams
  3. Risk:
    1. Challenges may arise when integrating work completed by different teams, potentially causing delays, inconsistencies, or technical debt.
  4. Mitigation:
    1. Define clear requirements and guidelines for each component to ensure consistency across the development process.
    2. Implement and follow an integration management plan. Schedule regular integration checkpoints to minimize integration challenges and identify issues early on.
  5. Unresolved interdependencies
  6. Risk:
    1. Dependencies between components developed by different teams might not be adequately addressed, leading to development roadblocks or unforeseen problems.
  7. Mitigation:
    1. At the onset, create a clear map of interdependencies between various pieces of the feature and communicate it to all involved teams.
    2. Assign a project lead or project manager responsible for overseeing dependencies and ensuring that they are addressed in the development process.

Timeline and Milestones​

Phase I​

  • Version: 2.0
  • Details:
    • Advent of data flows for scheduling and on-demand triggering.
    • Migration path from load groups to flows

Phase II​

  • Version: 2.1
  • Details:
    • UI for creating acyclical DAGs for flow dependency.
    • Also includes QuICs for some choice flow types.

Phase III​

  • Version: 2.2
  • Details:
    • UI for “streaming” option for scheduling.
    • All flows that can be QuICs are now QuICs

Questions?​