Deutsch
Support

2025-08-15

Grundlegendes Feature Engineering mit DuckDB

Petrica Leuca

Einleitung

Daten-Preprocessing ist ein notwendiger Schritt in jedem Machine-Learning-Workflow und beeinflusst sowohl die Wirksamkeit des Modells als auch die Wartbarkeit. scikit-learn wird für Preprocessing häufig genutzt, weil es ins breitere Python-Ökosystem integriert. DuckDB bietet eine praktische Alternative, indem es SQL-basierte Datentransformationen innerhalb von Python ermöglicht. Die deklarative Syntax unterstützt modulare Workflows und macht Preprocessing-Schritte leichter isolierbar, inspizierbar und debuggbar. Zusätzlich tragen DuckDBs Unterstützung für effizientes Querying spaltenorientierter Datenformate und die Möglichkeit, Preprocessing-Logik als SQL-Skripte zu persistieren, zu reproduzierbareren und transparenteren Pipelines bei.

Datenvorbereitung

Wir arbeiten mit einem synthetischen Finanztransaktionsdatensatz von Kaggle, der generische Informationen zur Betrugserkennung in Finanztransaktionen enthält.

CREATE TABLE financial_trx AS
    FROM read_csv('https://blobs.duckdb.org/data/financial_fraud_detection_dataset.csv');

Wir beginnen mit der Analyse der Daten, indem wir SUMMARIZE ausführen:

FROM (SUMMARIZE financial_trx)
SELECT
    column_name,
    column_type,
    count,
    null_percentage,
    min;
┌─────────────────────────────┬─────────────┬─────────┬─────────────────┬────────────────────────────┐
│         column_name         │ column_type │  count  │ null_percentage │            min             │
│           varchar           │   varchar   │  int64  │  decimal(9,2)   │          varchar           │
├─────────────────────────────┼─────────────┼─────────┼─────────────────┼────────────────────────────┤
│ transaction_id              │ VARCHAR     │ 5000000 │            0.00 │ T100000                    │
│ timestamp                   │ TIMESTAMP   │ 5000000 │            0.00 │ 2023-01-01 00:09:26.241974 │
│ sender_account              │ VARCHAR     │ 5000000 │            0.00 │ ACC100000                  │
│ receiver_account            │ VARCHAR     │ 5000000 │            0.00 │ ACC100000                  │
│ amount                      │ DOUBLE      │ 5000000 │            0.00 │ 0.01                       │
│ transaction_type            │ VARCHAR     │ 5000000 │            0.00 │ deposit                    │
│ merchant_category           │ VARCHAR     │ 5000000 │            0.00 │ entertainment              │
│ location                    │ VARCHAR     │ 5000000 │            0.00 │ Berlin                     │
│ device_used                 │ VARCHAR     │ 5000000 │            0.00 │ atm                        │
│ is_fraud                    │ BOOLEAN     │ 5000000 │            0.00 │ false                      │
│ fraud_type                  │ VARCHAR     │ 5000000 │           96.41 │ card_not_present           │
│ time_since_last_transaction │ DOUBLE      │ 5000000 │           17.93 │ -8777.814181944444         │
│ spending_deviation_score    │ DOUBLE      │ 5000000 │            0.00 │ -5.26                      │
│ velocity_score              │ BIGINT      │ 5000000 │            0.00 │ 1                          │
│ geo_anomaly_score           │ DOUBLE      │ 5000000 │            0.00 │ 0.0                        │
│ payment_channel             │ VARCHAR     │ 5000000 │            0.00 │ ACH                        │
│ ip_address                  │ VARCHAR     │ 5000000 │            0.00 │ 0.0.102.150                │
│ device_hash                 │ VARCHAR     │ 5000000 │            0.00 │ D1000002                   │
├─────────────────────────────┴─────────────┴─────────┴─────────────────┴────────────────────────────┤
│ 18 rows                                                                                  5 columns │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘

Feature Encoding

Aus den obigen Datenstatistiken sehen wir ein paar Kategorie-Spalten, etwa transaction_type, merchant_category und payment_channel. Weil die meisten Machine-Learning-Modelle numerische Eingaben erwarten, wird dieser Datentyp in eine numerische Darstellung umgewandelt. Dieser Prozess heißt Encoding und lässt sich auf mehrere Arten umsetzen. Im Folgenden zeigen wir ein paar gängige Encoding-Techniken in SQL.

In diesem Beitrag nutzen wir mehrere von DuckDBs „Friendly SQL“-Features, einschließlich der FROM-first-Syntax und Prefix Aliases.

One-Hot Encoding

Beim One-Hot Encoding über eine Kategorie-Spalte wird jeder Distinct-Wert in eine eigene Spalte transponiert und erhält den Wert 1 bei Match und 0 bei Nicht-Match:

FROM financial_trx
SELECT DISTINCT
    transaction_type,
    deposit_onehot: (transaction_type = 'deposit')::INT,
    payment_onehot: (transaction_type = 'payment')::INT,
    transfer_onehot: (transaction_type = 'transfer')::INT,
    withdrawal_onehot: (transaction_type = 'withdrawal')::INT
ORDER BY transaction_type;
┌──────────────────┬────────────────┬────────────────┬─────────────────┬───────────────────┐
│ transaction_type │ deposit_onehot │ payment_onehot │ transfer_onehot │ withdrawal_onehot │
│     varchar      │     int32      │     int32      │      int32      │       int32       │
├──────────────────┼────────────────┼────────────────┼─────────────────┼───────────────────┤
│ deposit          │              1 │              0 │               0 │                 0 │
│ payment          │              0 │              1 │               0 │                 0 │
│ transfer         │              0 │              0 │               1 │                 0 │
│ withdrawal       │              0 │              0 │               0 │                 1 │
└──────────────────┴────────────────┴────────────────┴─────────────────┴───────────────────┘

Eine andere Art, One-Hot zu encoden, ist die Anweisung PIVOT:

PIVOT financial_trx
ON transaction_type
USING coalesce(max(transaction_type = transaction_type)::INT, 0) AS onehot
GROUP BY transaction_type;

In der obigen Anweisung:

Gibt es mehr Kategorie-Spalten, die one-hot encoded werden sollen, kann PIVOT in Subqueries oder WITH-Klauseln genutzt werden:

WITH onehot_trx_type AS (
    PIVOT financial_trx
    ON transaction_type
    USING coalesce(max(transaction_type = transaction_type)::INT, 0) AS onehot
    GROUP BY transaction_type
), onehot_payment_channel AS (
    PIVOT financial_trx
    ON payment_channel
    USING coalesce(max(payment_channel = payment_channel)::INT, 0) AS onehot
    GROUP BY payment_channel
)
SELECT
    financial_trx.*,
    onehot_trx_type.* LIKE '%\_onehot' ESCAPE '\',
    onehot_payment_channel.* LIKE '%\_onehot' ESCAPE '\'
FROM financial_trx
INNER JOIN onehot_trx_type USING (transaction_type)
INNER JOIN onehot_payment_channel USING (payment_channel);

In der obigen Query holen wir alle mit „onehot“ gesuffixten Spalten mit dem LIKE-Operator auf Spaltennamen.

Ordinal Encoding

Ordinal Encoding weist jedem kategorialen Wert eine eindeutige Kennung zu und wird üblicherweise angewendet, wenn es eine gewisse Hierarchie in den kategorialen Werten gibt. Zum Beispiel können wir die Kennung mit der Funktion row_number zuweisen und nach dem Transaction Type ordnen:

WITH trx_type_ordinal_encoded AS (
    SELECT
        transaction_type,
        trx_type_oe: row_number() OVER (ORDER BY transaction_type) - 1
    FROM (
        SELECT DISTINCT transaction_type
        FROM financial_trx
    )
)
SELECT
    transaction_type,
    trx_type_oe,
    number_trx: count(*)
FROM financial_trx
INNER JOIN trx_type_ordinal_encoded USING (transaction_type)
GROUP BY ALL
ORDER BY trx_type_oe;
┌──────────────────┬─────────────┬────────────┐
│ transaction_type │ trx_type_oe │ number_trx │
│     varchar      │    int64    │   int64    │
├──────────────────┼─────────────┼────────────┤
│ deposit          │           0 │    1250593 │
│ payment          │           1 │    1250438 │
│ transfer         │           2 │    1250334 │
│ withdrawal       │           3 │    1248635 │
└──────────────────┴─────────────┴────────────┘

Label Encoding

Ähnlich wie Ordinal Encoding weist Label Encoding eine eindeutige Kennung zu, berücksichtigt aber keine Ordnung und wird üblicherweise auf Ausgabedaten angewendet:

WITH trx_type_label_encoded AS (
    SELECT
        transaction_type,
        trx_type_le: row_number() OVER () - 1
    FROM (
        SELECT DISTINCT transaction_type
        FROM financial_trx
    )
)
SELECT
    transaction_type,
    trx_type_le,
    number_trx: count(*)
FROM financial_trx
INNER JOIN trx_type_label_encoded USING (transaction_type)
GROUP BY ALL
ORDER BY trx_type_le;
┌──────────────────┬─────────────┬────────────┐
│ transaction_type │ trx_type_le │ number_trx │
│     varchar      │    int64    │   int64    │
├──────────────────┼─────────────┼────────────┤
│ deposit          │           0 │    1250593 │
│ withdrawal       │           1 │    1248635 │
│ payment          │           2 │    1250438 │
│ transfer         │           3 │    1250334 │
└──────────────────┴─────────────┴────────────┘

Eine andere Möglichkeit, das oben zu erreichen, sind Listenfunktionen wie array_agg, um ein Array mit den Distinct-Werten zu erzeugen, und list_position, um die Position jedes Werts im Array zu extrahieren:

WITH trx_ref AS (
    SELECT trx_type_values: array_agg(DISTINCT transaction_type)
    FROM financial_trx
)
SELECT
    transaction_type,
    trx_type_le: list_position(trx_type_values, transaction_type) - 1,
    number_trx: count(*)
FROM
    financial_trx,
    trx_ref
GROUP BY ALL
ORDER BY trx_type_le;
┌──────────────────┬─────────────┬────────────┐
│ transaction_type │ trx_type_le │ number_trx │
│     varchar      │    int32    │   int64    │
├──────────────────┼─────────────┼────────────┤
│ payment          │           0 │    1250438 │
│ deposit          │           1 │    1250593 │
│ transfer         │           2 │    1250334 │
│ withdrawal       │           3 │    1248635 │
└──────────────────┴─────────────┴────────────┘

Die obigen Queries sind nicht-deterministisch, deshalb kann für inkrementelle Verarbeitung eine Sortierung oder das Speichern der Daten in einer Referenztabelle nötig sein.

Feature Scaling

Ein weiterer üblicher Daten-Preprocessing-Schritt im Machine Learning ist, numerische Features zu skalieren, sodass die Werte unterschiedlicher Features in einen ähnlichen Bereich oder eine ähnliche Verteilung gebracht werden. Scaling, auch Feature Normalization oder Standardization genannt, bedeutet, Features so zu transformieren, dass sie vergleichbare Magnituden haben; typischerweise durch Rescaling auf einen festen Bereich (etwa 0 bis 1) oder durch Anpassung auf Mittelwert 0 und Varianz 1. Dieser Prozess ist nötig, weil viele Algorithmen auf Distanzberechnungen oder Gradient Updates beruhen, die verzerrt werden können, wenn Features in der Skala stark variieren.

Wird Encoding auf den initialen Rohdaten ausgeführt (weil die gesamte kategoriale Werteliste bekannt sein muss), erfordert Scaling das Splitting der Daten in Trainings- und Testdatensätze, um Data Leakage zu vermeiden. In DuckDB können wir die Daten durch Sampling splitten:

SET threads = 1;


CREATE TABLE financial_trx_training AS
FROM financial_trx
USING SAMPLE 80 PERCENT (reservoir, 256);


SET threads = 8;


CREATE TABLE financial_trx_testing AS
FROM financial_trx
ANTI JOIN financial_trx_training USING (transaction_id);

Wir konfigurieren DuckDB, beim Sampling einen einzelnen Thread zu nutzen, und setzen einen seed, um sicherzustellen, dass das Sampling reproduzierbar ist. Wir wenden außerdem die Sampling-Strategie reservoir an, um genau 80 % der Datensätze im resultierenden Sample zu haben.

Standard Scaling

Standard Scaling ist eine Preprocessing-Technik, die numerische Features transformiert, indem der Mittelwert abgezogen und durch die Standardabweichung geteilt wird, sodass jedes Feature Mittelwert 0 und Standardabweichung 1 hat.

Um zum Beispiel velocity_score standard zu skalieren, können wir ausführen:

WITH scaling_params AS (
    SELECT
        avg_velocity_score: avg(velocity_score),
        stddev_pop_velocity_score: stddev_pop(velocity_score)
    FROM financial_trx_training
)
SELECT
    ss_velocity_score: (velocity_score - avg_velocity_score) /
        stddev_pop_velocity_score
FROM
    financial_trx_testing,
    scaling_params;

Die obige Query lässt sich mit DuckDB-Macros stark vereinfachen. Mit Scalar Macros können wir eine Funktion für die Standard-Scaler-Transformation erzeugen:

CREATE OR REPLACE MACRO standard_scaler(val, avg_val, std_val) AS
    (val - avg_val) / std_val;

Mit Table Macros können wir eine Funktion erzeugen, die die vom Standard-Scaler-Macro benötigten Scaling-Parameter zurückgibt:

CREATE OR REPLACE MACRO scaling_params(table_name, column_list) AS TABLE
    FROM query_table(table_name)
    SELECT
        "avg_\0": avg(columns(column_list)),
        "std_\0": stddev_pop(columns(column_list));

In der obigen Macro-Definition:

Das Standard Scaling können wir jetzt so berechnen:

SELECT
    ss_velocity_score: standard_scaler(
        velocity_score,
        avg_velocity_score,
        std_velocity_score
    ),
    ss_spending_deviation_score: standard_scaler(
        spending_deviation_score,
        avg_spending_deviation_score,
        std_spending_deviation_score
    )
FROM financial_trx_testing,
    scaling_params(
        'financial_trx_training',
        ['velocity_score', 'spending_deviation_score']
    );

Min-Max Scaling

Min-Max Scaling ist eine Normalisierungstechnik, die Features auf einen festen Bereich transformiert, typischerweise 0 bis 1, indem der Minimalwert abgezogen und durch den Range (max − min) geteilt wird. Das erhält die Form der Originalverteilung und stellt gleichzeitig sicher, dass alle Werte auf derselben Skala liegen.

Um unser Feature min-max zu skalieren, erweitern wir das Macro scaling_params um die Berechnung von min und max über die Input-Spaltenliste:

CREATE OR REPLACE MACRO scaling_params(table_name, column_list) AS TABLE
    FROM query_table(table_name)
    SELECT
        "avg_\0": avg(columns(column_list)),
        "std_\0": stddev_pop(columns(column_list)),
        "min_\0": min(columns(column_list)),
        "max_\0": max(columns(column_list));

Dann definieren wir eine Macro-Definition für die Min-Max-Berechnung:

CREATE OR REPLACE MACRO min_max_scaler(val, min_val, max_val) AS
(val - min_val) / nullif(max_val - min_val, 0);

Schließlich extrahieren wir die Werte:

SELECT
    min_max_velocity_score: min_max_scaler(
        velocity_score,
        min_velocity_score,
        max_velocity_score
    ),
    min_max_spending_deviation_score: min_max_scaler(
        spending_deviation_score,
        min_spending_deviation_score,
        max_spending_deviation_score
    )
FROM financial_trx_testing,
    scaling_params(
        'financial_trx_training',
        ['velocity_score', 'spending_deviation_score']
    );

Robust Scaling

Robust Scaling ist eine Daten-Normalisierungstechnik, die numerische Features transformiert, indem der Median abgezogen und durch den Interquartilsabstand (IQR) geteilt wird. Anders als Standard Scaling, das Mittelwert und Standardabweichung nutzt, reduziert Robust Scaling den Einfluss von Outliers, indem es sich auf die mittleren 50 % der Daten konzentriert. Das macht es gut geeignet für Datensätze mit schiefen Verteilungen oder Extremwerten.

In DuckDB können wir die Quantilbereiche mit dem statistischen Aggregat quantile_cont berechnen:

CREATE OR REPLACE MACRO scaling_params(table_name, column_list) AS TABLE
    FROM query_table(table_name)
    SELECT
        "avg_\0": avg(columns(column_list)),
        "std_\0": stddev_pop(columns(column_list)),
        "min_\0": min(columns(column_list)),
        "max_\0": max(columns(column_list)),
        "q25_\0": quantile_cont(columns(column_list), 0.25),
        "q50_\0": quantile_cont(columns(column_list), 0.50),
        "q75_\0": quantile_cont(columns(column_list), 0.75);

Wir definieren das Scalar Macro für die Robust-Scaling-Berechnung:

CREATE OR REPLACE MACRO robust_scaler(val, q25_val, q50_val, q75_val) AS
    (val - q50_val) / nullif(q75_val - q25_val, 0);

Und analog zu den anderen Scaling-Transformationen rufen wir es direkt in SQL auf:

SELECT
    rs_velocity_score: robust_scaler(
        velocity_score,
        q25_velocity_score,
        q50_velocity_score,
        q75_velocity_score
    ),
    rs_spending_deviation_score: robust_scaler(
        spending_deviation_score,
        q25_spending_deviation_score,
        q50_spending_deviation_score,
        q75_spending_deviation_score
    )
FROM financial_trx_testing,
    scaling_params(
        'financial_trx_training',
        ['velocity_score', 'spending_deviation_score']
    );

Fehlende Werte behandeln

Es kommt oft vor, dass unsere Eingabedaten unvollständig sind, d. h. fehlende Daten haben. Je nach Use Case werden solche Daten ausgeschlossen, so genutzt wie sie sind oder mit einem konstanten Wert gefüllt. In DuckDB können wir die Funktion coalesce nutzen, um den Wert der Spalte oder einen Default-Wert zu holen, wenn die Spalte NULL ist.

Einige gängige Techniken sind:

Wir erweitern das Macro scaling_params um die Median-Berechnung:

CREATE OR REPLACE MACRO scaling_params(table_name, column_list) AS TABLE
    FROM query_table(table_name)
    SELECT
        "avg_\0": avg(columns(column_list)),
        "std_\0": stddev_pop(columns(column_list)),
        "min_\0": min(columns(column_list)),
        "max_\0": max(columns(column_list)),
        "q25_\0": quantile_cont(columns(column_list), 0.25),
        "q50_\0": quantile_cont(columns(column_list), 0.50),
        "q75_\0": quantile_cont(columns(column_list), 0.75),
        "median_\0": median(columns(column_list));

Und wir wenden coalesce an, um fehlende Werte je nach Use Case zu behandeln:

SELECT
    time_since_last_transaction_with_0: coalesce(time_since_last_transaction, 0),
    time_since_last_transaction_with_mean: coalesce(time_since_last_transaction, avg_time_since_last_transaction),
    time_since_last_transaction_with_libraryn: coalesce(time_since_last_transaction, median_time_since_last_transaction)
FROM
    financial_trx_testing,
    scaling_params('financial_trx_training', ['time_since_last_transaction'])
WHERE time_since_last_transaction IS NULL;

Das Füllen fehlender Daten sollte vor dem Feature Scaling geschehen.

Benchmark

Die obigen Datenverarbeitungsschritte zusammengeführt, haben wir uns entschieden, die Ausführungszeit gegen die scikit-learn-Daten-Preprocessing-Pipelines zu benchmarken. Der Code steht in unserem Blog-Examples-Repository.

In scikit-learn geschieht Daten-Preprocessing über Transformer und Pipelines. Ein Transformer ist eine Klasse, die die Methoden fit und transform umsetzt, eine Pipeline ist eine Sequenz von Transformern, die in einer bestimmten Reihenfolge auf die Daten angewendet werden. Wenn nicht anders angegeben, gibt jeder Schritt der Pipeline in NumPy-Arrays nur das Ergebnis des Transformationsschritts zurück. Weil wir in DuckDB die Daten über SQL-Ausdrücke transformieren, können wir den vollen Datensatz nach jedem Schritt inspizieren. Deshalb enthalten in unserem Benchmark die scikit-learn-Daten-Preprocessing-Schritte die folgenden Transformationen:

from sklearn.compose import ColumnTransformer
from sklearn.impute import SimpleImputer
from sklearn.pipeline import Pipeline
from sklearn.preprocessing import StandardScaler, MinMaxScaler, RobustScaler




def scikit_feature_scaling_training_data(x_train):
    impute_missing_data = Pipeline(
        [
            ("imputer", SimpleImputer(strategy="mean")),
            ("scaler", MinMaxScaler(copy=False)),
        ]
    )


    scaling_steps = ColumnTransformer(
        [
            (
                "ss",
                StandardScaler(copy=False),
                ["velocity_score"]
            ),
            (
                "minmax_time_since_last_transaction",
                impute_missing_data,
                ["time_since_last_transaction"],
            ),
            (
                "minmax",
                MinMaxScaler(copy=False),
                ["spending_deviation_score"]
            ),
            (
                "rs",
                RobustScaler(copy=False),
                ["amount"]
            ),
        ],
        remainder="passthrough",
        verbose_feature_names_out=False,
    )


    scaling_steps.set_output(transform="pandas")
    scaling_steps.fit(x_train)


    return scaling_steps, scaling_steps.transform(x_train)

Die Abbildung unten zeigt die Ausführungszeiten auf einem MacBook Pro mit 16 GB und demonstriert, dass DuckDB für die Daten-Preprocessing-Schritte eine deutliche Performanceverbesserung gegenüber scikit-learn bietet.

Daten-Preprocessing-Benchmark, während des Trainings

Im Skript reconcile_results.py werden die Ergebnisse zwischen den DuckDB- und scikit-learn-Preprocessing-Schritten abgeglichen und zeigen, dass beide Implementierungen dieselben Ergebnisse erzeugen.

In den obigen Beispielen haben wir gezeigt, wie Daten-Preprocessing mit SQL-Ausdrücken während des Trainings umgesetzt wird. In der Praxis müssen dieselben Preprocessing-Schritte zur Inferenzzeit angewendet werden, sodass neue Daten konsistent mit den Trainingsdaten transformiert werden. Mit scikit-learn erreicht man das, indem man die Pipeline zusammen mit dem Modell persistiert und die Pipeline zur Inferenzzeit anwendet. Mit DuckDB wird die entsprechende Konsistenz erreicht, indem man die originalen Trainingsdaten persistiert (oder die vom Macro scaling_params zurückgegebenen Metriken, die während des Trainings berechnet wurden). Obwohl die (transformierten) Trainingsdaten deutlich größer sind als die Modell-Artefakte, ist das Versionieren der Daten und Features, die zur Trainingszeit berechnet wurden, eine gängige Praxis, die Modell-Traceability und Reproduzierbarkeit sicherstellt.

Für effizientes (Trainings-)Datenmanagement kann man Lösungen nutzen, die Time Travel bieten, etwa DuckLake.

Fazit

In diesem Artikel haben wir gezeigt, wie DuckDB einen performanten, SQL-nativen Ansatz für Daten-Preprocessing in Machine-Learning-Workflows bietet. Indem Aufgaben wie Imputation fehlender Werte, kategoriale Kodierung und Feature Scaling direkt in der Datenbank-Engine behandelt werden, kann man unnötige Datenbewegung während des Trainings eliminieren und die Preprocessing-Latenz senken.