Designing tables¶

What tables do you emit, and at what grain? Plotsim has an opinion: a star schema of dimensions, facts, and event tables, generated in topological order. This page covers when to let plotsim auto-generate the schema, when to declare it explicitly, and every table shape you have to choose from.

Auto-generation¶

If you don't declare dimensions, facts, and events, the builder emits a minimal three-table schema:

dim_date — a date spine with date_key, date, year, month, quarter.
dim_<unit> — one row per entity. Carries the entity's faker name (the unit-to-faker map: company → faker.company; employee or customer → faker.name; everything else → faker.company), plus one pool:<attr> column per common segment attribute.
fct_<unit> — one row per entity per period. Every metric appears as a column.

This is enough for half of all simple workflows. Declare a schema only when you need more.

When you declare any of dimensions, facts, or events, the builder stops auto-generating that block — but it still auto-fills dim_date if you forgot, and auto-fills dim_<unit> if any bridge references it.

Table types and grains¶

Three types: dim, fact, event. Each combines with a small set of grains:

Type	Grain	Meaning
dim	`per_entity`	One row per entity (`dim_company`, `dim_employee`)
dim	`per_period`	One row per time period (`dim_date` — the canonical case)
dim	`per_reference`	Static lookup (e.g. `dim_category`)
dim	`variable` (sub-entity)	Sub-entity dim — `count × Σ segment.count` rows (e.g. `dim_user`)
fact	`per_entity_per_period`	The main behavioral fact — one row per (entity, period)
fact	`per_period`	No entity axis; metrics aggregated across entities via `nanmean`
fact	`variable`	Row count driven by a metric (parent of `per_parent_row` children)
fact	`per_parent_row`	Child fact — `(min, max)` rows fanning out from a parent fact row
event	`variable`	Variable row count via `proportional` or `threshold` triggers

Caps: 50 fact tables, 100 columns per table, 50,000 entities total across all segments.

Declaring a fact¶

Every fact lists the metrics it carries and the columns to emit. The builder translates metric.<name> columns into the engine's metric sources for you.

PythonYAML

facts=[
    {
        "name": "fct_engagement",
        "metrics": ["engagement", "feature_adoption"],
        "columns": [
            {"name": "date_key",         "type": "ref.dim_date"},
            {"name": "company_id",       "type": "ref.dim_company"},
            {"name": "engagement",       "type": "metric.engagement"},
            {"name": "feature_adoption", "type": "metric.feature_adoption"},
            {"name": "active_seats",     "type": "range", "range": [1, 200]},
        ],
    },
]

facts:
  - name: fct_engagement
    metrics: [engagement, feature_adoption]
    columns:
      - { name: date_key,         type: ref.dim_date }
      - { name: company_id,       type: ref.dim_company }
      - { name: engagement,       type: metric.engagement }
      - { name: feature_adoption, type: metric.feature_adoption }
      - { name: active_seats,     type: range, range: [1, 200] }

per_entity_per_period is the default fact grain. The engine produces one row per entity per period in entity-major layout (each entity's full time series back-to-back).

Parent / child facts¶

When one parent fact has many child rows — orders and order items, for instance — declare the parent as a variable-grain fact and the child as per_parent_row.

PythonYAML

facts=[
    {
        "name": "fct_orders",
        "metrics": [],
        "row_count_driver": "purchase_intent",
        "row_count_scale": 4.0,
        "columns": [
            {"name": "order_id",    "type": "id"},
            {"name": "date_key",    "type": "ref.dim_date"},
            {"name": "customer_id", "type": "ref.dim_customer"},
            {"name": "order_total", "type": "range", "range": [10, 800]},
        ],
    },
    {
        "name": "fct_order_items",
        "metrics": [],
        "parent_table": "fct_orders",
        "children_per_row": (1, 5),
        "columns": [
            {"name": "item_id",  "type": "id"},
            {"name": "sku",      "type": "faker.bothify"},
            {"name": "qty",      "type": "range", "range": [1, 6]},
            {"name": "price",    "type": "range", "range": [5, 200]},
        ],
    },
]

facts:
  - name: fct_orders
    metrics: []
    row_count_driver: purchase_intent
    row_count_scale: 4.0
    columns:
      - { name: order_id,    type: id }
      - { name: date_key,    type: ref.dim_date }
      - { name: customer_id, type: ref.dim_customer }
      - { name: order_total, type: range, range: [10, 800] }

  - name: fct_order_items
    metrics: []
    parent_table: fct_orders
    children_per_row: [1, 5]
    columns:
      - { name: item_id, type: id }
      - { name: sku,     type: faker.bothify }
      - { name: qty,     type: range, range: [1, 6] }
      - { name: price,   type: range, range: [5, 200] }

row_count_driver names a metric; row_count_scale is the multiplier. Per cell, the engine emits round(metric_value * scale) rows. NaN cells produce zero rows.

children_per_row is an inclusive (min, max) range. The engine emits rng.integers(min, max + 1) child rows per parent row, with the child's FK to the parent auto-synthesized — do not declare a ref.fct_<parent> column on the child.

A variable-grain fact cannot carry metric.<name> columns: per-row metrics are ambiguous when multiple rows share the same trajectory position. Use static-value columns, range, FKs, or faker columns instead.

Children of children are rejected — one level of nesting only.

SCD Type 2¶

Track a slowly-changing attribute on a per_entity dim. Add a column of type scd and name the metric whose trajectory crossing each threshold triggers a new version:

PythonYAML

dimensions=[
    {
        "name": "dim_company",
        "per": "unit",
        "columns": [
            {"name": "company_id",   "type": "id"},
            {"name": "company_name", "type": "faker.company"},
            {
                "name": "plan_tier",
                "type": "scd",
                "tracks": "mrr",
                "tiers": ["free", "starter", "growth", "enterprise"],
                "at":    [0.25, 0.55, 0.80],
            },
        ],
    },
]

dimensions:
  - name: dim_company
    per: unit
    columns:
      - { name: company_id,   type: id }
      - { name: company_name, type: faker.company }
      - name: plan_tier
        type: scd
        tracks: mrr
        tiers: [free, starter, growth, enterprise]
        at:    [0.25, 0.55, 0.80]

The engine rewrites dim_company to one row per (entity, version), appending dim_row_id, valid_from, valid_to, and is_current columns. The sentinel valid_to for the active version is 99991231 (YYYYMMDD), so SQL joins predicate without NULL handling.

Each fact or event table that FKs into the SCD dim gets an additional dim_row_id column resolved per (entity, period) automatically.

SCD constraints: per-entity dims only; one SCD column per dim; thresholds strictly ascending in the open interval (0, 1), capped at 20; len(labels) == len(thresholds) + 1.

Sub-entity dims¶

When the unit is "company" but each company has multiple users, model users as a sub-entity dim — count × Σ segment.count rows.

PythonYAML

dimensions=[
    {
        "name": "dim_user",
        "per": "unit",
        "count": 4,                       # four users per company
        "columns": [
            {"name": "user_id",    "type": "id"},
            {"name": "company_id", "type": "ref.dim_company"},
            {"name": "user_name",  "type": "faker.name"},
            {"name": "user_role",  "type": "static.admin,member,member,viewer"},
        ],
    },
]

dimensions:
  - name: dim_user
    per: unit
    count: 4
    columns:
      - { name: user_id,    type: id }
      - { name: company_id, type: ref.dim_company }
      - { name: user_name,  type: faker.name }
      - { name: user_role,  type: "static.admin,member,member,viewer" }

count: N on per: unit produces N rows per entity. The static.<comma-list> source cycles through the values across rows.

Reference dims¶

A reference dim is a static lookup with no entity or time axis (e.g. dim_category). Use reference: true. The dim's row count is implicitly the longest static.<comma-list> column.

PythonYAML

dimensions=[
    {
        "name": "dim_support_category",
        "reference": True,
        "columns": [
            {"name": "category_id",   "type": "id"},
            {
                "name": "category_name",
                "type": "static.bug,billing,onboarding,integration,feature_request,access,outage,docs",
            },
        ],
    },
]

dimensions:
  - name: dim_support_category
    reference: true
    columns:
      - { name: category_id,   type: id }
      - { name: category_name, type: "static.bug,billing,onboarding,integration,feature_request,access,outage,docs" }

reference: true is mutually exclusive with per:.

Bridges¶

Many-to-many between two per_entity dims (or one per_entity and one per_reference). Each bridge declares left and right dim names, an inclusive (min, max) cardinality, and optional metric / static / faker columns on the bridge rows themselves.

PythonYAML

bridges=[
    {
        "name": "company_product_usage",
        "left":  "dim_company",
        "right": "dim_product",
        "cardinality": (2, 5),
        "driver": "engagement",
        "columns": [
            {"name": "first_used", "type": "static.2024-01-01"},
            {"name": "usage_intensity", "type": "metric.engagement"},
        ],
    },
]

bridges:
  - name: company_product_usage
    left: dim_company
    right: dim_product
    cardinality: [2, 5]
    driver: engagement
    columns:
      - { name: first_used,      type: static.2024-01-01 }
      - { name: usage_intensity, type: metric.engagement }

When a driver is set, per-entity cardinality is interpolated from the entity's mean trajectory position between min and max (position 0.0 → min, 1.0 → max). With no driver, the engine draws uniformly.

Bridge rows are static — generated once per run, not per period. Only metric.<name>, static.<value>, and faker.<kind> column types are valid on a bridge.

Self-join bridges (left and right naming the same dim — manager hierarchies, referral graphs) are supported. The two endpoint FK columns are suffixed _a / _b (<pk>_a, <pk>_b) so the realized DataFrame stays well-formed when both FKs point at the same dim. The sampler excludes each entity from its own candidate pool, so no (A, A) self-edges are emitted.

By default bridges are directed: (A, B) and (B, A) are distinct rows. Set directed: false on a self-join bridge for an undirected graph — pairs collapse to one row with the FKs sorted as (min, max). Under directed: false the per-entity cardinality.min floor is not a runtime guarantee: dedup can drop one of each (A, B) / (B, A) pair, so a realized per-entity count may fall below the configured min. The cardinality.max ceiling is still enforced.

Up to twenty bridges per config; left dim must be per_entity; right dim may be per_entity or per_reference (never per_period).

Events¶

Event tables emit variable row counts. Two trigger mechanisms:

Proportional. round(metric_value * scale) rows per (entity, period). Use when "more engagement → more login events."

PythonYAML

events=[
    {
        "name": "evt_login",
        "trigger": "proportional",
        "driver": "engagement",
        "scale": 10.0,
        "columns": [
            {"name": "event_id",  "type": "id"},
            {"name": "date_key",  "type": "ref.dim_date"},
            {"name": "company_id","type": "ref.dim_company"},
            {"name": "event_ts",  "type": "timestamp"},
        ],
    },
]

events:
  - name: evt_login
    trigger: proportional
    driver: engagement
    scale: 10.0
    columns:
      - { name: event_id,   type: id }
      - { name: date_key,   type: ref.dim_date }
      - { name: company_id, type: ref.dim_company }
      - { name: event_ts,   type: timestamp }

Threshold. Fires at most one row per entity, at the period where the metric value first satisfies the threshold for for consecutive periods. Use when "churn risk above 0.7 for two months → one churn event."

PythonYAML

events=[
    {
        "name": "evt_churn",
        "trigger": "threshold",
        "metric": "churn_risk",
        "above": 0.7,
        "for_periods": 2,
        "columns": [
            {"name": "event_id",   "type": "id"},
            {"name": "date_key",   "type": "ref.dim_date"},
            {"name": "company_id", "type": "ref.dim_company"},
            {"name": "reason",     "type": "faker.sentence"},
            {"name": "voluntary",  "type": "flag"},
        ],
    },
]

events:
  - name: evt_churn
    trigger: threshold
    metric: churn_risk
    above: 0.7
    for: 2
    columns:
      - { name: event_id,   type: id }
      - { name: date_key,   type: ref.dim_date }
      - { name: company_id, type: ref.dim_company }
      - { name: reason,     type: faker.sentence }
      - { name: voluntary,  type: flag }

above and below are mutually exclusive. The streak resets on any cell that's null, NaN, or fails to satisfy the threshold. Both comparisons are strict.

The Python API uses for_periods; YAML accepts both for and for_periods.

CDC audit columns¶

Set cdc: true on a fact to append _inserted_at, _updated_at, _op columns. Column-level quality issues on a CDC fact flip the row's _op to "U" and bump _updated_at; row-level issues leave it as "I".

PythonYAML

facts=[
    {
        "name": "fct_revenue",
        "metrics": ["mrr"],
        "cdc": True,
        "columns": [...],
    },
]

facts:
  - name: fct_revenue
    metrics: [mrr]
    cdc: true
    columns: [...]

Lifecycle stages¶

Annotate a fact with a stage column derived from a metric crossing ascending thresholds.

PythonYAML

lifecycle={
    "track": "engagement",
    "stages": [
        {"trial":      0.0},
        {"active":     0.2},
        {"engaged":    0.5},
        {"power_user": 0.75},
    ],
}

lifecycle:
  track: engagement
  stages:
    - { trial:      0.0 }
    - { active:     0.2 }
    - { engaged:    0.5 }
    - { power_user: 0.75 }

Default: stateless free mode — each period independently picks the highest threshold satisfied. Add enforce_order: true for a monotonic cursor that can't jump back to an earlier stage on a transient dip. Add downgrade_delay: N (1..120) for hysteresis: under enforce_order, the cursor may step backward only after N consecutive sub-threshold periods.

The stage column is hardcoded to stage.

Denormalization¶

Set output.denormalized: true to emit a <fct>_wide table alongside each normalized fact — every FK'd dim left-joined onto the fact, dim columns prefixed with <dim>__, SCD dims filtered to current rows.

See Output and scaling for the full pattern.

Going deeper¶

Filling columns — every column source type with config examples.
Adding realism — CDC interplay with quality injection, log-file companion writer.
Reference: Column types — the validity matrix for every column type on every table grain.