Skip to content

← Training overview

8. Partitioning

A feature table that grows by tens of millions of rows per day quickly becomes the slowest part of every query that touches it. Teradata's solution is table partitioning: physically split the rows on a column so that a predicate on that column scans only a fraction of the table. tdfs4ds exposes partitioning as first-class parameters on upload_features.

Why partition

Two different concepts share the name "index" in Teradata; they do different jobs and you usually want both:

  • PRIMARY INDEX governs row distribution across AMPs. A good primary index spreads rows evenly so the work parallelises. A WHERE clause on the primary index does not skip data — it just routes the query to the right AMP.
  • PARTITION BY governs scan footprint within an AMP. A WHERE clause on a partition column lets Teradata skip whole partitions on disk — partition elimination. This is where the wins on large fact tables come from.

A typical pattern for a temporal feature table:

  • Primary index: the entity key (e.g. customer_id) — even distribution and fast lookup of a single entity's history.
  • Partitioning: the event date — partition elimination when a downstream query filters by date range.

How to declare it

Both controls are arguments to upload_features:

tdfs4ds.upload_features(
    df=df,
    entity_id=['customer_id', 'event_date'],
    feature_names=['daily_amount'],
    primary_index='customer_id',
    partitioning=(
        "PARTITION BY RANGE_N("
        "event_date BETWEEN DATE '2020-01-01' AND DATE '2030-12-31' "
        "EACH INTERVAL '1' MONTH)"
    ),
)

primary_index accepts a column name (or comma-separated list); partitioning accepts a raw Teradata clause — anything you would normally append after the column list in a CREATE TABLE — which lets you use RANGE_N, CASE_N, or multi-level partitioning without tdfs4ds having to know about every variant.

The definition is checked before anything is registered (0.3.4.3). upload_features tries the entity columns, primary index and partitioning on a throwaway volatile table; if the database refuses it, nothing is registered and the log gives the database message and what to change. The usual refusals:

Partitioning Refusal Fix
RANGE_N on a DECIMAL / NUMBER / FLOAT column Error 3732 cast it in the process view (CAST(col AS INTEGER) AS col), or use CASE_N
RANGE_N on a TIMESTAMP column with DATE bounds Error 5714 TIMESTAMP '2020-01-01 00:00:00' bounds, or a DATE column
a column that is not an entity column Error 3810 partition and index on entity columns (and FEATURE_ID) only

The definition is recorded with the process. Registering the same view again with another primary_index / partitioning keeps the recorded one and logs a warning: change it with plan_relocation(process_id=..., primary_index=..., partitioning=...), which moves the stored history, or remove the process first if it never stored anything.

Partitioning enables FilterManager

Module 6 › FilterManager becomes much more powerful when the source it iterates over is partitioned. Each filter iteration adds a WHERE event_date = ... (or WHERE region = ...) predicate; if those columns are also partition columns, Teradata skips every partition that doesn't match — so the per-iteration cost shrinks linearly with the number of partitions you have. The query optimizer in Module 3 flags partitioned-but-unfiltered tables and offers a FilterManager rewrite suggestion.

Diagnosing existing tables

The lineage graph exposes the primary-index and partition-column information for every table it walks:

from tdfs4ds.lineage import build_teradata_dependency_graph

graph = build_teradata_dependency_graph(sql_query="SELECT * FROM MY_DB.V_TX_FACT")
for name, info in graph['table_info'].items():
    print(name,
          'PI:', info.get('primary_index_columns'),
          'partitions:', info.get('partition_columns'))

Use this before refactoring an ingestion to confirm which columns Teradata will eliminate on — then design your FilterManager and feature view to exercise those columns.

Learn more