cleandataengineering.aiData Platform, from the big picture to the code
You are here: Start here › Bronze to silver › The thirteen stages › 8 Row filter

Data PlatformZoom level 3

Stage 8: row filter, rows that are not wanted

The eighth stage removes rows that silver is not meant to hold, by a declared condition. An example is test orders that a source system mixes in with real ones.

The difference from quarantine is the point of the stage. A quarantined row is a fault that someone should look at. A filtered row is fine, just not wanted. So the stage does not store it. It only counts it, so that the numbers of rows in and rows out can still be explained.

Rowsafter the personal data check8 Row filterkeep rows that satisfy every filterRows keptevery filter is trueDroppedcounted, not stored
The dashed box is a count only. Nothing is written for a dropped row.
Goes inRows, and the plan's list of filters. Each filter is a name and a condition.
What happensAll conditions are joined with AND. A row is kept only if the result is true.
Comes outThe kept rows, and the dropped rows as a frame to count.

A worked example

Suppose the sales order line has one filter named real_orders with the condition dealer_code <> 'D-TEST'.

order_iddealer_codeCondition givesResult
SO-1001D-1001trueKept.
SO-9001D-TESTfalseDropped and counted.
SO-1002missingunknownDropped and counted. A condition that cannot be decided counts as not satisfied.

The run log would show row_filter.dropped = 2 for this batch.

The settings that drive it

There is no manifest key for a row filter today. Filters are passed in code, as an argument of the silver run: a dictionary from the dataset name to its filters.

SettingWhat it meansAllowed valuesDefault
row_filters argument of build_silverFor each dataset name, a tuple of rules. Only each rule's name and condition are used here.A condition in the engine's SQL expression languageNo filters. The stage passes every row.
src/dataplatform/silver/runner.py:69The silver run's arguments. row_filters is the second from last.
def build_silver(
    store: MetadataStore,
    tables: SilverTables,
    stages: StageRunner,
    domain: str,
    env: str,
    run_id: str,
    as_of: str,
    token_pattern: str = "",
    only: set[str] | None = None,
    row_filters: dict[str, tuple[Rule, ...]] | None = None,
    rebuild: set[str] | None = None,
) -> SilverReport:

This stage has no switch. With no filters it does nothing.

The code

def row_filter(df: DataFrame, ctx: StageCtx) -> Out:
    """Declared filters: rows that do not satisfy one are dropped and counted (not quarantined: they are not wrong)."""
    if not ctx.plan.row_filters:
        return Out(df)
    keep = F.lit(True)
    for r in ctx.plan.row_filters:
        keep = keep & F.coalesce(F.expr(r.predicate), F.lit(False))
    marked = df.withColumn("_keep", keep)
    return Out(marked.where("_keep").drop("_keep"), tally={"dropped": marked.where(~F.col("_keep"))})
  1. Nothing declared. If the plan has no filters the frame is returned untouched and nothing is counted.
  2. Start from true. keep starts as the constant true.
  3. Add each filter. Each filter's condition is turned into a column expression with F.expr and joined to keep with AND. A row must satisfy every filter.
  4. Decide the undecided. A condition on a missing value gives neither true nor false. coalesce(..., False) turns that into false. Without it, the outcome for such a row would depend on how the engine treats an unknown value in a filter.
  5. Mark, then split. The result is stored in a working column _keep. The kept rows are the ones where it is true. The dropped rows are the others, returned in the tally under the name dropped.

The condition is SQL text that the engine runs as written. It is written by whoever sets up the run, never taken from data.

How it is tested

tests/dataplatform/test_silver_stages.py has four cases over the same three rows: no filter, one filter, two filters, and a condition that cannot be decided for two of the rows.

tests/dataplatform/test_silver_stages.py:223The four cases: the filters, the ids kept and the number dropped.
        ("no filter", (), ["a", "b", "c"], 0),
        ("one filter", (("positive", "v > 0"),), ["a", "b"], 1),
        ("two filters", (("positive", "v > 0"), ("small", "v < 5")), ["a"], 2),
        ("a null predicate drops", (("w_set", "w IS NOT NULL AND w = 'x'"),), ["a"], 2),
tests/dataplatform/test_silver_stages.py:229The test body and its three rows.
def test_row_filter(spark, case, filters, kept, dropped):
    p = plan_from_view(view(columns=[("id", "string"), ("v", "integer"), ("w", "string")]), TOKEN,
                       row_filters=tuple(Rule(n, pred, "", "drop") for n, pred in filters))
    df = frame(spark, "id string, v long, w string", [("a", 1, "x"), ("b", 9, None), ("c", -1, None)])
    out = run("row_filter", df, p)
    assert [r[0] for r in rows(out.df, "id")] == kept, case
    assert (out.tally["dropped"].count() if out.tally else 0) == dropped
Not built yet, or known gaps
  • No manifest key declares a row filter, so a filter cannot be set as metadata.
  • The two places in the code that start a silver run, the day-by-day run and the baseline writer, do not pass any filters. As the code stands, the stage is exercised only by its tests.
  • Dropped rows are counted in total. The count is not split by filter, and the rows are not kept.