Operations guide

You know what is wrong with your data, or what you want from it, but not what the step is called. Find your problem here, work out which version of it you have, and follow it to the operations that answer it.

How operations are arranged

We arrange operations by the stages a dataset passes through on its way from a source to a result. There are five, in the order a pipeline meets them:

  • Acquire: reading the data in, from files, databases and the web.
  • Shape: cleaning, inspecting and rearranging one table.
  • Join: bringing two or more tables together.
  • Model: analysing the data or learning from it.
  • Publish: sending the result out, as a file, a table or a message.

Control sits outside the five. Its operations run the pipeline around the data: they order and repeat steps, handle errors, set parameters, run scripts and commands, test the pipeline and log what happened, rather than changing the data.

Within a stage, we group operations by the shape of what you get back, compared with the table you started from:

  • A new table, from no input table.
  • One row out per row in, with columns added or changed.
  • Fewer rows, same columns.
  • More rows, each traceable to one input row.
  • A new grain: each row stands for a group of input rows.
  • The same cells, rearranged.
  • One table from two or more.
  • A table describing the input rather than holding its rows.
  • A model or a file, not a table.
  • A model or file and a table in, the table out with columns added.
  • The table sent out, with nothing new to look at.

Once you can say which stage you are in and what shape you want back, the operation follows. Say you want the repeated rows of a table gone: you are shaping one table, and you want fewer rows back with the same columns. That leaves a handful of operations, and Deduplicate rows is the one.

Start from your problem

Each problem below comes in more than one version. Find the sentence that is true of your data, and follow it to the operations that answer it, in the order you would run them.

Shape

My customer list has the same company three times.

Repeated companies are at least three problems, and each needs a different fix.

Acquire

I've been sent a file and need it as a table.

Which reader you need depends on how the file lays out its values.

Shape

I don't know what's in this file, or whether to trust it.

Before you change anything, you can ask what the file holds, and then whether it holds what it should.

  • You don't yet know its columns or what type each one is.

    1. List columns and types

  • You know the columns, but not how many values are blank or distinct in each.

    1. Profile a table

  • You know what the data should look like and want each rule checked, such as no blank customer IDs.

    1. Check data quality

Shape

My dates won't sort or filter as dates.

The column holds dates in a form the table treats as something else, and the form tells you the fix.

Shape

My table is the wrong way round for what I need.

The cells are all there; what changes is whether months, or any repeated category, run across the columns or down the rows.

  • You have a column for each month and want one row for each month, with the month as a value.

    1. Unpivot a table

  • You have a row for each month and want a column for each month, as in a cross-tab.

    1. Pivot a table

  • You want the rows and columns swapped wholesale, each row becoming a column.

    1. Transpose a table

Join

My two tables should join, but the keys don't match.

Keys fail to match for different reasons, and each reason has its own kind of join.

Join

I need to know what changed between last month's file and this month's.

You may need a yes or no, every difference, or only the rows that came or went.

Join

I need to write this month's results into a table that holds the earlier months.

Writing into a table that already has rows means deciding what happens to the rows already there.

Model

I want to put my customers into groups.

Whether the groups are already in your data decides whether you summarise or model.

  • The groups are already a column, such as region or plan, and you want figures for each.

    1. Summarise by group

  • There is no such column, and you want customers who are alike put together.

    1. Cluster rows

Model

I want to know what next month's numbers will look like.

A forecast reads a series of periods, so what matters first is whether your rows already are one.

Publish

I need to hand the result to someone else.

The right file depends on what they will open it with.

Publish

Others need to know what my dataset holds and where it came from.

Where the description goes depends on where they will look for it.

Every operation

The 149 operations with a page, by stage and then by the shape of the result, each with its status in arcform today.

Acquire

A new table, from no input table

Shape

One row out per row in, with columns added or changed

Fewer rows, same columns

  • Deduplicate rowsRuns as a SQL step

    Distinct rows, unique by key, keep first or last per key (3 variants with a page)

  • Filter rowsRuns as a SQL step

    Keep or drop rows by a condition (5 variants with a page)

  • Take the first or last rowsRuns as a SQL step

    First N, last N, top N, row range, skip rows (3 variants with a page)

  • Sample rowsRuns as a SQL step

    Random or stratified sample, train and test split (3 variants with a page)

  • Split a tableRuns as a SQL step

    Split one table into several by a key or a condition

More rows, each traceable to one input row

  • Split a list into rowsRuns as a SQL step

    One row to many: unnest, explode, split to rows (2 variants with a page)

A new grain: each row stands for a group of input rows

The same cells, rearranged

A table describing the input rather than holding its rows

  • Chart the dataA feature, not a step

    Plot the data for exploration (2 variants with a page)

  • Measure correlationRuns as a SQL step

    Correlation or association matrix, contingency table (2 variants with a page)

  • See a column's distributionRuns as a SQL step

    Histogram, quantiles of one column

  • Profile a tableRuns as a SQL step

    Summary statistics, distinct counts, null counts

  • List columns and typesRuns as a SQL step

    List columns, types, metadata (3 variants with a page)

  • Check data qualityRuns as a SQL step

    Data-quality rule, assertion, test, constraint or schema check (8 variants with a page)

Join

One row out per row in, with columns added or changed

  • Group matched recordsRuns as a SQL step

    Turn matched pairs into one group id per record, following matches transitively

A new grain: each row stands for a group of input rows

  • Pick a surviving recordRuns as a SQL step

    Collapse each group of matched records into one surviving record by per-field rules

One table from two or more

Model

One row out per row in, with columns added or changed

Fewer rows, same columns

More rows, each traceable to one input row

A new grain: each row stands for a group of input rows

One table from two or more

  • Find similar rowsRuns as a SQL step

    Row-to-row distance, nearest-neighbour search, vector search (2 variants with a page)

A table describing the input rather than holding its rows

  • Evaluate a modelRuns as a SQL step

    Performance metrics, confusion matrix, ROC, lift, cross-validation (3 variants with a page)

  • Explain a modelNot built yet

    Variable importance, partial dependence, SHAP, model inspection (3 variants with a page)

  • Optimise a planNot built yet

    Linear programming, simulation, prescriptive optimisation

  • Use a probability distributionNot built yet

    Distribution functions, density estimation (1 variant with a page)

  • Fit a regressionRuns as a SQL step

    Linear, logistic or generalised linear regression as statistics (1 variant with a page)

  • Run a hypothesis testNot built yet

    Hypothesis test, ANOVA, chi-square, t-test (1 variant with a page)

A model or a file, not a table

A model or file and a table in, the table out with columns added

Not a data operation

Publish

The table sent out, with nothing new to look at

Control

Not a data operation

  • Create or change tablesRuns as a SQL step

    Create, alter or maintain tables and indexes (2 variants with a page)

  • Plan and run queriesA feature, not a step

    Query planning, optimisation, memory, caching, partitioning, parallelism (2 variants with a page)

  • Handle errors and retriesA feature, not a step

    Error handling, retry, abort, dead-letter routing (2 variants with a page)

  • Run an external commandA feature, not a step

    Run an external job or process, trigger another pipeline, version control (6 variants with a page)

  • Branch or loopNot built yet

    Branch, loop, iterate, conditional execution, wait (10 variants with a page)

  • Undo and restoreA feature, not a step

    Undo, redo, snapshot, freeze, replay a recorded path

  • Run a step inside a databaseNot built yet

    Push a step down into a database rather than the tool's own engine

  • Build an app interfaceNot built yet

    User-interface widgets for building apps on a workflow

  • Log and annotate a runA feature, not a step

    Logging, audit, metrics, annotations, comments (3 variants with a page)

  • Set parametersA feature, not a step

    Variables, parameters, context, configuration (8 variants with a page)

  • Check before runningA feature, not a step

    Check that a service, file or connection is available before running

  • Run a scriptA feature, not a step

    Run user code: Python, R, JavaScript, Java, shell (6 variants with a page)

  • Call a reusable workflowNot built yet

    Call a sub-workflow, macro, mapping or reusable fragment (4 variants with a page)

  • Test a pipelineNot built yet

    Unit-test a pipeline against expected output