Prepstellar
Get the app

DP-900 · Common Data Workloads

22 cards

Analytical Workloads

Swipe, scroll or use ← →

  1. Recognize analytical processing

    Analytical data processing typically uses read-only or read-mostly systems that store vast volumes of historical data or business metrics. The shape of the workload is the opposite of a transactional one: few, large questions rather than millions of small events.

    The analysis can use data captured at one point in time, or data captured at a series of points in time — a picture of the business today, or the same measure tracked month by month for five years.

    What defines the workload is the emphasis. It queries data for insight rather than continually recording small business transactions. A system optimized for writing individual live transactions, or one built around create, retrieve, update, and delete operations on current records, is describing the transactional side of the picture instead.

    1 / 22

  2. Quick check

    Which system pattern is typical of analytical data processing?

    1. AA write-optimized system for individual live transactions

      Optimizing for writes of individual live transactions describes a transactional system, not an analytical one.

    2. BA create-and-delete system holding current line-of-business events

      Record-level operations on current business events belong to the transactional workload pattern.

    3. CA read-mostly system holding vast volumes of historical data

      Right. Analytical data processing typically uses read-only or read-mostly systems that store vast volumes of historical data or business metrics.

    2 / 22

  3. Move the data: extract, transform, load

    A common enterprise-scale analytical architecture begins with operational data — the records the business writes as it runs. Getting that data into a place where it can be analyzed is the first stage, and there are two orders in which it can be done.

    In extract, transform, and load (ETL), operational data is extracted, transformed, and then loaded into a data lake for analysis. The order is exactly the name: the transformation happens in flight, before anything lands.

    1. Extract the operational data from its source.
    2. Transform it into the shape analysis needs.
    3. Load the result into the destination.

    Every other ordering — transforming before extracting, or extracting after loading — reverses steps that depend on one another.

    3 / 22

  4. Quick check

    What is the operation order in an ETL process?

    1. AExtract, transform, and then load

      Right. In extract, transform, and load, operational data is extracted, transformed, and then loaded into a data lake for analysis.

    2. BLoad the data, extract it, and then transform it

      Nothing can be loaded before it has been extracted from the operational source that holds it.

    3. CTransform the data first, load it, and then extract it

      Data cannot be transformed before it has been extracted, and extraction is the first step rather than the last.

    4 / 22

  5. Move the data: extract, load, transform

    Extract, load, and transform (ELT) keeps the same three operations and moves one of them. Data is extracted and loaded before transformations are applied — a pattern common in modern lakehouses.

    The single difference is where the transformation happens: after the data has landed, using the destination's own processing power, rather than on the way there.

    ETL ELT
    Step 1 Extract Extract
    Step 2 Transform Load
    Step 3 Load Transform
    Transformation happens Before landing After landing

    Everything else stays put. ELT does not transform before extracting, does not aggregate before loading anywhere, and does not query reports before their source data exists.

    5 / 22

  6. Quick check

    How does ELT differ from ETL in an analytical architecture?

    1. AELT aggregates the data before loading it into any store

      Aggregating before the load is not what the pattern names; the distinguishing move is loading before transforming.

    2. BELT loads the extracted data before applying transformations

      Right. In extract, load, and transform, data is extracted and loaded before transformations are applied, a pattern common in modern lakehouses.

    3. CELT applies transformations before extracting operational data

      Extraction always comes first, because there is nothing to transform until the operational data has been read.

    6 / 22

  7. Keep your progress in the app

    That’s 3 of 10 quick checks. In the app they stay answered, and every lesson remembers where you left off.

    Get the app Keep reading here

  8. Represent the data as tables

    Once the data has landed, it is represented through tables — and there are two common places for those tables to live.

    Home for the tables What provides the table behavior
    Data lakehouse Tabular abstractions layered over data lake files
    Data warehouse A fully relational SQL engine

    The distinction is worth holding onto because it explains why the two feel similar to query and differ underneath. A lakehouse keeps the files of a lake and adds a table-shaped view of them; a warehouse is relational all the way down.

    From either home, data can be aggregated and loaded into an OLAP model, which is the next step up in summarization.

    7 / 22

  9. Quick check

    Where do the tabular abstractions of a data lakehouse sit, compared with a data warehouse?

    1. ABoth sit on a fully relational SQL engine, with no file layer involved

      The relational SQL engine describes the warehouse; the lakehouse builds its tables over files in a data lake.

    2. BThe lakehouse layers tables over data lake files, and the warehouse uses a relational SQL engine

      Right. A lakehouse provides tabular abstractions over data lake files, while a data warehouse uses a fully relational SQL engine.

    3. CThe lakehouse stores only preaggregated measures, and the warehouse stores only raw files

      Preaggregated measures belong to an OLAP model, and a warehouse stores relational tables rather than raw files.

    8 / 22

  10. Aggregate it: the OLAP or semantic model

    Data in a warehouse may be aggregated and loaded into an online analytical processing (OLAP) model. The same thing is now more commonly called a semantic model, and was historically called a cube — three names, one idea.

    Inside it, aggregated numeric values called measures come from fact tables. Those measures are calculated for intersections of dimensions, which come from dimension tables: sales revenue summarized by date, by customer, and by product.

    Get the direction right, because it reverses easily. Measures live in fact tables and are aggregated; dimensions live in dimension tables and are what the measures are sliced by. Measures do not come from dimension tables, dimensions are not written as individual transactions, and nothing is deleted after a query.

    9 / 22

  11. Aggregate it: the OLAP or semantic model

    The aggregation has a second consequence: it is preaggregated storage optimized for analytical workloads, so the summaries are already computed when the question arrives.

    Its aggregations also span dimensions at different levels, which lets users drill up or down through a hierarchy — from region, to city, to individual address.

    Requirement Fits an OLAP or semantic model?
    Fast access to precomputed sales summaries Yes — the aggregations are already stored
    Navigate from region to city to address Yes — dimensions are aggregated at several levels
    Explore raw files that have not been prepared No — that is a data lake
    Record live create, retrieve, update, delete work No — that is a transactional database
    10 / 22

  12. Quick check

    How are numeric summaries organized in an OLAP or semantic model?

    1. AMeasures from dimension tables are stored unaggregated

      The direction is reversed: measures come from fact tables, and they are aggregated rather than left raw.

    2. BMeasures from fact tables are aggregated across dimensions

      Right. Aggregated numeric values called measures come from fact tables and are calculated for intersections of dimensions taken from dimension tables.

    3. CDimensions from fact tables are written as individual transactions

      Dimensions come from dimension tables, and an analytical model summarizes rather than records individual transactions.

    11 / 22

  13. Data lakes and data warehouses

    Two of the analytical stores answer very different requirements, and each is picked by one phrase in the question.

    A data lake is common when large volumes of file-based data must be collected and analyzed. If the requirement says files, and says a lot of them, the lake is the store.

    A data warehouse stores data in a relational schema optimized for read operations, primarily the queries that support reporting and data visualization. If the requirement says relational tables queried for reports, the warehouse is the store.

    12 / 22

  14. Data lakes and data warehouses

    Neither is a transactional system, and that is the distinction most often blurred.

    Store Optimized for Not for
    Data lake Collecting and analyzing large volumes of file-based data Frequent transactional record updates
    Data warehouse Read queries supporting reporting and visualization Raw event write operations, or a create-and-delete workload

    A live database built for create, retrieve, update, and delete operations is an operational source that analytical architecture extracts from — it is not one of the analytical stores it loads into.

    13 / 22

  15. Quick check

    A team must collect and analyze a very large volume of file-based data. Which analytical store matches the requirement?

    1. AA data lake

      Right. A data lake is common when large volumes of file-based data must be collected and analyzed.

    2. BA live database used for transactional operations

      A transactional database is an operational source that analytical work extracts from, not an analytical store.

    3. CA line-of-business application

      A line-of-business application produces operational data; it is not a store designed for analysis.

    14 / 22

  16. Data lakehouses combine both

    A data lakehouse combines the flexible, scalable storage of a data lake with the relational querying semantics of a data warehouse. It is not a third, unrelated store — it is those two capabilities in one place.

    Borrowed from What it contributes
    Data lake Flexible, scalable file-based storage
    Data warehouse Relational querying semantics over that storage

    That combination is the whole definition, and it is what the alternatives get wrong. A store limited to transactional writes is not offering warehouse-style querying; preaggregated storage with no file-based foundation is an OLAP model; and an operational database with create-only record access is neither half of the pair.

    15 / 22

  17. Quick check

    Which combination of capabilities defines a data lakehouse?

    1. APreaggregated semantic storage with no underlying file-based foundation

      Preaggregated storage without files describes an OLAP or semantic model rather than a lakehouse.

    2. BRelational warehouse storage with processing limited to transactional writes

      Limiting processing to transactional writes removes the analytical querying that a lakehouse exists to provide.

    3. CFlexible data lake storage with relational warehouse-style querying

      Right. A data lakehouse combines the flexible, scalable storage of a data lake with the relational querying semantics of a data warehouse.

    16 / 22

  18. Denormalize for query speed

    A lakehouse table schema may use denormalized data from an OLTP source, introducing some duplication so that queries perform faster.

    That trade is deliberate, and it runs opposite to transactional design. A transactional schema normalizes to avoid storing the same detail twice, because it is constantly writing. An analytical schema accepts the duplication because it is mostly reading, and a value already sitting in the row is a join that does not have to be performed.

    Normalized OLTP source Denormalized lakehouse table
    Duplication Avoided Accepted
    Optimized for Writing discrete transactions Analytical query speed

    So when a team designs lakehouse tables from an OLTP source and accepts duplicate values to make analytical queries faster, the design choice has a name: denormalizing the source data.

    17 / 22

  19. Quick check

    A team designs lakehouse tables from an OLTP source and accepts duplicate values to improve analytical query speed. Which design choice is this?

    1. ADenormalizing the source data when designing the lakehouse schema

      Right. A lakehouse table schema may use denormalized data from an OLTP source, introducing some duplication so that queries perform faster.

    2. BNormalizing every value to accelerate transactional writes

      Normalization avoids duplication and suits the transactional source; the choice described deliberately accepts it.

    3. CRemoving duplicate values after each reporting query has run

      Nothing is cleaned up after each query: the duplication is part of the schema design and stays there.

    18 / 22

  20. Know who works at each stage

    Different users work at different stages of the analytical architecture, and each one is recognizable by where their data comes from.

    Role Works with Typical output
    Data scientist Files in a data lake, directly Exploration and data models
    Data analyst Data warehouse tables Complex reports and visualizations
    Business user Preaggregated analytical-model data Reports and dashboards consumed as they are

    The analyst's position is the one to fix in memory: querying warehouse tables to create complex reports and visualizations. Exploring raw lake files to build scientific models is the data scientist's stage, consuming preaggregated dashboards is the business user's, and recording a retail payment in a live application is not part of this architecture at all — it is the operational source it draws from.

    19 / 22

  21. Quick check

    Which activity belongs to a data analyst in the analytical architecture?

    1. AExploring raw data lake files directly to build scientific models

      Working directly with data lake files to explore and model data is the data scientist's stage.

    2. BConsuming preaggregated dashboards as a business user would

      Consuming preaggregated analytical-model data through reports or dashboards describes the business user.

    3. CQuerying warehouse tables for complex reports and visualizations

      Right. Data analysts may query data warehouse tables to create complex reports and visualizations.

    20 / 22

  22. Key takeaways

    • Analytical processing is read-only or read-mostly, over vast volumes of historical data or business metrics, captured at one point in time or at a series of points.
    • ETL transforms before loading; ELT loads before transforming, and ELT is common in modern lakehouses.
    • The stores differ by requirement: a data lake for large volumes of file-based data, a data warehouse for a relational schema optimized for read queries and reporting, and a data lakehouse for lake storage with warehouse-style relational querying.
    • OLAP or semantic models are preaggregated: measures from fact tables, aggregated across dimensions from dimension tables, at several levels so users can drill from region to city to address.
    • Denormalization is deliberate in a lakehouse schema, trading duplication for query speed.
    • Data scientists work in the lake, data analysts query the warehouse, and business users consume the model.
    21 / 22

  23. Quick check

    Which statement matches the analytical architecture described here?

    1. AA data warehouse stores raw files, and ETL loads data before transforming it

      Raw files belong to a data lake, and loading before transforming is ELT rather than ETL.

    2. BA data warehouse uses a relational schema for read queries, and ETL transforms data before loading it

      Right. A data warehouse stores data in a relational schema optimized for read operations, and ETL extracts, transforms, and then loads.

    3. CAn OLAP model stores unaggregated transactions, and ELT transforms data before extracting it

      An OLAP model holds preaggregated measures, and nothing can be transformed before it has been extracted.

    22 / 22

  24. 10 quick checks · then the test

    In the app, finishing the quick checks opens this lesson’s 10-question test, and the ones you miss come back exactly when you’re about to forget them.

    Take this lesson’s test → Get the app
← Previous Keep your progress · Get the app Next card →

The whole course, on your phone

Lessons you can read, audio you can listen to on the way to work, and practice that remembers what you got wrong.

Get the app