Skip to main content

PCA: dimensionality reduction

Project six correlated measurements into two components and understand the array return contract.

The practical problem and model​

An equipment-monitoring team collects several related measurements and wants a compact view for visualization or downstream analysis. Principal component analysis (PCA) finds orthogonal directions that capture large amounts of variation in the input data.

PCA is unsupervised: the training SELECT contains features only. Its components are weighted combinations of the original columns, not predicted classes. Keeping two components compresses the representation but can discard information that matters for a later task.

Here eight synthetic samples each have six measurements. Every function call returns two numbers, so the model must declare RETURNS ARRAY<DOUBLE>. The older scalar RETURNS DOUBLE declaration is incompatible.

Before you run​

Complete the shared setup. This walkthrough uses synthetic data and recreates its tutorial objects. Use a sandbox tenant or schema, and run the steps in order.

Studio: Tutorial: PCA — Dimensionality Reduction. Download the complete SQL.

Step 1: Create eight six-dimensional samples​

All feature columns use DOUBLE. The sample identifier is retained for displaying results but is excluded from training.

CREATE CATALOG IF NOT EXISTS tutorial;

CREATE SCHEMA IF NOT EXISTS tutorial.pca;

CREATE OR REPLACE TABLE tutorial.pca.metrics (
    sample_id BIGINT,
    f1 DOUBLE, f2 DOUBLE, f3 DOUBLE, f4 DOUBLE, f5 DOUBLE, f6 DOUBLE
);

INSERT INTO tutorial.pca.metrics VALUES
    (1, 2.5,  2.4,  0.5,  0.7,  2.2,  2.9),
    (2, 0.5,  0.7,  2.2,  2.9,  0.5,  0.7),
    (3, 2.2,  2.9,  0.5,  0.7,  2.5,  2.4),
    (4, 1.9,  2.2,  3.1,  3.0,  1.6,  1.1),
    (5, 3.1,  3.0,  1.9,  2.2,  1.1,  1.6),
    (6, 2.3,  2.7,  2.0,  1.6,  1.5,  1.1),
    (7, 2.0,  1.6,  1.5,  1.1,  2.3,  2.7),
    (8, 1.0,  1.1,  1.5,  1.6,  2.3,  2.7);

Step 2: Fit a two-component projection​

num_components = 2 controls the output width. There is no label column, and the declared return type is an array.

CREATE OR REPLACE MODEL tutorial.pca.reducer
    (DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE)
RETURNS ARRAY<DOUBLE>
TYPE 'pca'
OPTIONS (num_components = 2)
AS SELECT f1, f2, f3, f4, f5, f6 FROM tutorial.pca.metrics;

EXPLAIN MODEL tutorial.pca.reducer;

Step 3: Project each sample​

The result keeps the sample ID beside its two-element principal_components array. Input order must match the training feature order.

SELECT
    sample_id,
    tutorial.pca.reducer(f1, f2, f3, f4, f5, f6) AS principal_components
FROM tutorial.pca.metrics
ORDER BY sample_id;

What to check in the results​

Expect eight rows and an array of exactly two finite values in every row. Sample 1 in the recorded run was approximately [1.962077, -0.101857]; sample 2 was [-2.633442, -1.066515].

The complete projection was compared with an independent SVD. A component's sign can flip without changing the PCA solution: compare the full component consistently, not isolated signs. For the fitted samples, each projected component is centered near zero.

Adapt it to real data​

Decide whether to standardize measurements with different units before fitting: high-variance units can otherwise dominate the projection. Fit centering and scaling only on training data and keep them with the model used for later projection.

Choose the component count using retained variance and downstream performance. A visually clean two-dimensional plot does not prove that important predictive information was preserved.

PCA algorithm reference

All tutorials · Setup and troubleshooting

Last updated on