data-management

Data Management

Workflow

1. Pipeline Architecture

Batch vs streaming:

Approach	Latency	Use case	Tools
Batch ETL	Hours	Daily reporting, historical analysis	Airflow, dbt, Fivetran
Micro-batch	Minutes	Near-real-time dashboards	Spark Streaming, dbt + scheduler
Streaming	Seconds	Real-time alerts, live feeds	Kafka, Flink, Kinesis

Decision: Start with batch. Move to streaming only when business requires sub-minute latency.

Standard pipeline pattern:

Sources → Extract → Landing/Raw → Transform → Staging → Serve → BI/Analytics
  ↓         ↓          ↓             ↓           ↓        ↓
 APIs    Fivetran    Raw zone     dbt models   Clean    Looker/
 DBs     Airbyte    (immutable)  (versioned)  tables   Metabase
 Files   Custom     S3/GCS       SQL tests    Views    API

2. Warehouse Schema Design

Declare the grain first. A fact table's grain is the business meaning of one row. Every measure and foreign key must be true at that grain. The most common modeling bug is mixing header-level and line-level facts in one table — an order with 3 products is 1 order but 3 lines, so order_id cannot be a unique key at line grain.

-- FACT (line grain): one row per order line. PK is the line, NOT the order.
-- Additive measures (quantity, revenue) live here.
CREATE TABLE fact_order_lines (
  order_line_key  BIGINT PRIMARY KEY,                 -- surrogate, one per line
  order_id        BIGINT NOT NULL,                    -- degenerate dimension (header id)
  customer_key    INT  NOT NULL REFERENCES dim_customers(customer_key),
  product_key     INT  NOT NULL REFERENCES dim_products(product_key),
  order_date_key  INT  NOT NULL REFERENCES dim_dates(date_key),
  quantity        INT          NOT NULL,
  unit_price      DECIMAL(12,2) NOT NULL,
  line_revenue    DECIMAL(12,2) NOT NULL,             -- quantity * unit_price - line_discount
  line_discount   DECIMAL(12,2) NOT NULL DEFAULT 0,
  loaded_at       TIMESTAMP    NOT NULL
);

-- FACT (header grain): one row per order. PK = order_id.
-- Put header-only measures here (shipping, order-level discount). Do NOT sum these
-- after joining to lines or you fan-out and double-count — keep grains separate.
CREATE TABLE fact_orders (
  order_id          BIGINT PRIMARY KEY,               -- header grain ⇒ order_id is unique
  customer_key      INT NOT NULL REFERENCES dim_customers(customer_key),
  order_date_key    INT NOT NULL REFERENCES dim_dates(date_key),
  order_total       DECIMAL(12,2) NOT NULL,           -- sum of line_revenue at load time
  shipping_amount   DECIMAL(12,2) NOT NULL DEFAULT 0, -- header-only, non-additive across lines
  order_discount    DECIMAL(12,2) NOT NULL DEFAULT 0,
  line_count        INT NOT NULL,
  loaded_at         TIMESTAMP NOT NULL
);