Tag: Data Mart

Posted on

Data Mart

A Data Mart is a subject-oriented analytical data store designed to support the reporting, analytics, and decision-making needs of a specific business function, department, domain, or analytical workload.

For example:

  • Sales Data Mart
  • Finance Data Mart
  • Customer Data Mart
  • Marketing Data Mart
  • Supply Chain Data Mart
  • HR Data Mart
Enterprise Data Warehouse / Lakehouse
企业中央数据平台
          |
          |---------------- Sales Data Mart
          |                  销售数据集市
          |
          |---------------- Finance Data Mart
          |                  财务数据集市
          |
          |---------------- Customer Data Mart
          |                  客户数据集市
          |
          |---------------- Marketing Data Mart
                             市场数据集市

A Data Mart typically contains curated, integrated, business-friendly data optimized for analytical queries rather than operational transactions.

What data marts actually address

What data marts actually address is: How do we turn enterprise data into a business-consumable analytical model?

The Real Value of a Data Mart

  • Business Simplification 业务简化
  • Consistent Business Definitions 统一业务定义
  • Performance 查询性能
  • Security 安全
  • Self-Service Analytics 自助分析

Data Mart vs Data Warehouse

  • Scope and Scale / 范围与规模
    • DWH: Centralizes cross-functional data across the entire enterprise. / 汇集整个企业跨部门的全局数据。
    • DM: Isolate highly specific data for teams like sales, marketing, or finance. / 仅隔离出销售、市场或财务等特定团队所需的局部数据。
  • Structural Architecture / 架构关系
    • Top-down (Inmon): The DWH is built first as a central repository, and Data Marts are extracted from it. / 自上而下架构： 先建立中央数仓，再从中抽取并生成各个数据集市。
    • Bottom-up (Kimball): Independent Data Marts are built first for business units, then integrated to form the DWH. / 自下而上架构： 先为各业务单元建立独立的数据集市，最终组合在一起构成全局数仓。
  • Speed and Performance / 速度与性能
    • DWH: Handles massive, complex analytical queries across different business domains, which can be slower. / 处理跨领域的海量复杂分析，查询可能较慢。
    • DM: Offers much faster query responses because it scans smaller, curated datasets. / 因为只扫描较小的、精选的数据集，所以查询响应速度极快。
Feature / 特性Data Warehouse (DWH) / 数据仓库Data Mart (DM) / 数据集市
Data Scope / 数据范围Enterprise-wide, cross-functional data / 汇集全企业、跨功能数据Single subject or specific department / 单一主题或特定部门
Data Sources / 数据来源Multiple operational systems and external feeds / 多个业务系统和外部数据源Mostly derived from the central warehouse / 大多衍生自中央数仓
Size / 数据容量Typically hundreds of gigabytes to petabytes / 通常为数百 GB 到 PB 级Usually less than 100 gigabytes / 通常小于 100 GB
Implementation / 实施周期Months to years to fully deploy / 全面部署需要数月到数年Days to weeks for a targeted setup / 针对性搭建仅需数天到数周
Primary Users / 主要用户Data scientists, enterprise analysts / 数据科学家、企业级分析师Department managers, local teams / 部门经理、业务团队

The Complete Data Mart Lifecycle

Business Requirements
        |
        v
Identify Business Process
        |
        v
Identify Grain
        |
        v
Identify Dimensions
        |
        v
Identify Facts
        |
        v
Source-to-Target Mapping
        |
        v
ETL / ELT
        |
        v
Data Quality
        |
        v
Data Mart
        |
        v
Semantic Layer
        |
        v
Power BI / Reporting

Where is Data Mart in Lakehouse

              Azure
                |
       +--------+---------+
       |                  |
     CRM                ERP
       |                  |
       +--------+---------+
                |
              Bronze
                |
              Silver
                |
              Gold
                |
      +---------+---------+
      |         |         |
      v         v         v
   Sales      Finance   Customer
   Mart        Mart       Mart
      |         |         |
      +---------+---------+
                |
             Power BI

The complete implementation of a data mart

assuming, we have those in Silver:

  • silver.orders
  • silver.order_items
  • silver.customers
  • silver.products

We are creating Sales Data Mart

Step 1 — Dimension

Dim Customer

-- ============================================================
-- PURPOSE:
--   Build the Customer Dimension for the Sales Data Mart.
--
-- WHY:
--   Fact_Sales will reference customer_key instead of storing
--   all customer descriptive attributes repeatedly.
-- ============================================================

CREATE TABLE gold.dim_customer
USING DELTA
AS
SELECT
    -- --------------------------------------------------------
    -- Surrogate Key
    -- --------------------------------------------------------
    -- A surrogate key is the warehouse/Data Mart key used
    -- internally by the dimensional model.
    -- --------------------------------------------------------
    monotonically_increasing_id() AS customer_key,

    -- --------------------------------------------------------
    -- Business Key
    -- --------------------------------------------------------
    -- This is the identifier coming from the source system.
    -- Example: CRM customer ID.
    -- --------------------------------------------------------
    customer_id,

    -- --------------------------------------------------------
    -- Descriptive Attributes
    -- These attributes describe the customer.
    -- --------------------------------------------------------
    customer_name,
    city,
    province,
    customer_segment

FROM silver.customers;

Product Dimension

-- ============================================================
-- PURPOSE:
--   Create the Product Dimension.
--
-- BUSINESS PURPOSE:
--   Allow analysts to slice sales by product, category,
--   brand, etc.
-- ============================================================

CREATE TABLE gold.dim_product
USING DELTA
AS
SELECT
    -- Internal surrogate key used by the Data Mart
    monotonically_increasing_id() AS product_key,

    -- Source-system business key
    product_id,

    -- Business descriptive attributes
    product_name,
    category,
    brand

FROM silver.products;

Date Dimension

-- ============================================================
-- PURPOSE:
--   Create a reusable Date Dimension.
--
-- WHY:
--   Business users frequently analyze data by:
--       Year
--       Quarter
--       Month
--       Week
--       Day
--
--   Instead of repeatedly calculating these values from the
--   transaction timestamp, we centralize the business calendar.
-- ============================================================

CREATE TABLE gold.dim_date
USING DELTA
AS
SELECT
    date,

    -- Calendar attributes
    year(date)    AS year,
    quarter(date) AS quarter,
    month(date)   AS month,
    day(date)     AS day,

    -- Useful reporting attributes
    dayofweek(date) AS day_of_week,
    date_format(date, 'MMMM') AS month_name

FROM (
    -- Generate a calendar covering the required business period.
    SELECT explode(
        sequence(
            DATE '2020-01-01',
            DATE '2030-12-31',
            INTERVAL 1 DAY
        )
    ) AS date
);

Step 2 — Fact Sales

Fact_Sales

-- ============================================================
-- PURPOSE:
--   Build the Sales Fact Table.
--
-- GRAIN:
--   One row = one order line item.
--
-- THIS IS CRITICAL.
--   We must define the grain BEFORE loading the fact table.
-- ============================================================

CREATE TABLE gold.fact_sales
USING DELTA
AS
SELECT

    -- ========================================================
    -- Dimension Foreign Keys
    -- ========================================================

    d.date_key,

    c.customer_key,

    p.product_key,

    -- ========================================================
    -- Degenerate / Business Identifiers
    -- ========================================================

    oi.order_id,

    -- ========================================================
    -- Measures
    -- ========================================================

    oi.quantity,

    oi.sales_amount,

    oi.discount_amount,

    oi.cost_amount,

    -- Profit is derived from business measures.
    -- ========================================================
    oi.sales_amount
        - oi.discount_amount
        - oi.cost_amount AS profit_amount

FROM silver.order_items oi

-- ============================================================
-- Join Customer Dimension
-- ============================================================
JOIN gold.dim_customer c
    ON oi.customer_id = c.customer_id

-- ============================================================
-- Join Product Dimension
-- ============================================================
JOIN gold.dim_product p
    ON oi.product_id = p.product_id

-- ============================================================
-- Join Date Dimension
-- ============================================================
JOIN gold.dim_date d
    ON CAST(oi.order_date AS DATE) = d.date;
                     dim_customer
                          |
                          |
                          |
dim_date ---------- fact_sales ---------- dim_product
                          |
                          |
                       dim_store

What was revenue by customer segment and month?

SELECT
    d.year,
    d.month,
    c.customer_segment,
    SUM(f.sales_amount) AS revenue
FROM gold.fact_sales f

JOIN gold.dim_customer c
    ON f.customer_key = c.customer_key

JOIN gold.dim_date d
    ON f.date_key = d.date_key

GROUP BY
    d.year,
    d.month,
    c.customer_segment

ORDER BY
    d.year,
    d.month,
    c.customer_segment;

Key Takeaways

Data Mart: A subject-oriented analytical data store designed for a specific business domain or analytical purpose.
面向特定业务领域、部门或分析需求的数据分析存储层。

ConceptWhat is it?Main Purpose
Data LakeLarge-scale flexible data repositoryStore diverse data
Data WarehouseIntegrated analytical platformEnterprise analytics
Data MartDomain-oriented analytical storeBusiness-domain analytics
LakehouseArchitecture combining lake + warehouse capabilitiesUnified data platform

For easy remember:

Data Lake: Store Data

  • Raw
  • Flexible
  • Large scale
  • Structured + Semi-structured + Unstructured

Data Warehouse: Integrate and analyze enterprise data

  • Enterprise-wide
  • Integrated
  • Structured
  • Governed
  • Historical
  • Analytics

Data Mart: Serve a specific business domain

  • Sales
  • Finance
  • Customer
  • Risk
  • Marketing
  • HR

Lakehouse: Unify Data Lake + Data Warehouse capabilities

Scalable storage
+
ACID
+
Governance
+
SQL Analytics
+
BI
+
Data Engineering
+
ML / AI

Data Lake / Warehouse / Lakehouse describe data-platform architecture;
Data Mart describes a business-oriented analytical data product or store.

Proudly powered by WordPress