Greenplum at the data engineering interview

Why Greenplum still shows up in interviews

Greenplum is an open-source MPP database built on top of Postgres, and it sits inside more enterprise warehouses than its low public profile suggests. Healthcare networks, telcos, insurance carriers, and a long tail of regulated industries still run Greenplum as their core analytical warehouse, often alongside newer engines like Snowflake or Databricks. If you are interviewing for a senior data engineer role at a company with a multi-decade data platform, the recruiter will ask whether you can read a Greenplum EXPLAIN plan without flinching.

The reason this question filters candidates so aggressively is simple. Most analysts write SQL that runs fine on a single Postgres node, push it into Greenplum, and watch the cluster collapse under redistribute motion on every join. The DBA pulls up the plan, sees four motion operators stacked on top of each other, and the post-mortem turns into a discussion of distribution keys. That conversation is exactly what the interviewer is trying to simulate.

This guide walks through the concepts in the order they tend to come up — architecture, distribution, motion, partitioning, skew — and ends with a pitfalls section that mirrors the real failure modes engineers hit in production.

MPP architecture in one diagram

Massively Parallel Processing means the data is physically split across N segment hosts. A master node (with an optional standby) handles parsing, planning, and final aggregation. The segments store data and execute query fragments in parallel.

                 [Master]
                    |
        +-----------+-----------+
    [Segment 1] [Segment 2] [Segment N]

Each segment is its own Postgres instance with its own slice of every table. The master compiles the query plan, ships it to all segments, and the segments execute in lockstep. Only the final result — usually small — comes back to the master for the client.

Distribution keys

Every table in Greenplum has a distribution policy that tells the engine how to spread rows across segments. You pick this at CREATE TABLE time, and it dictates the performance of every join the table participates in for the rest of its life.

CREATE TABLE orders (
    order_id   BIGINT,
    user_id    BIGINT,
    amount     DECIMAL(12, 2),
    event_date DATE
)
DISTRIBUTED BY (user_id);

There are three flavors you should be able to recite without thinking:

The criteria for a good distribution key are mechanical: high cardinality, stable business meaning, and frequent use in joins. A user_id with millions of distinct values is almost always a safer pick than a status column with eight values.

Bad candidates show up constantly in interview screens. gender puts 99% of the table on two segments. Any nullable column dumps every NULL row onto a single segment because NULLs all hash to the same bucket. Date columns are tempting but usually wrong — recent dates dominate and the cluster heats up unevenly.

Motion operators

Whenever Greenplum has to move rows between segments to satisfy a query, it inserts a motion operator into the plan. The three you need to name on demand:

• Redistribute Motion — rehash rows on a new key so a join can complete segment-locally.
• Broadcast Motion — send a full copy of a small table to every segment. Cheap for tiny tables, ruinous for large ones.
• Gather Motion — pull final results back to the master. Always present at the top of the plan.

A typical bad plan looks like this:

                 Gather Motion
                       |
                 Hash Join (user_id)
                 /              \
      Redistribute Motion    [orders]  (DISTRIBUTED BY user_id)
                 |
            [users]  (DISTRIBUTED BY id)

The users table is distributed by id but joined on user_id, so the planner has to redistribute it. If both tables were distributed by the join key, that motion would disappear and the join would run segment-locally.

The classic interview question — "how would you speed up a join between orders and users?" — has exactly one good answer: make sure both tables share the same distribution column, usually user_id.

Partitioning on top of distribution

Distribution and partitioning are orthogonal concerns and people confuse them constantly. Distribution decides which segment a row lives on. Partitioning decides which physical sub-table on that segment holds the row.

CREATE TABLE events (
    event_id   BIGINT,
    user_id    BIGINT,
    event_date DATE
)
DISTRIBUTED BY (user_id)
PARTITION BY RANGE (event_date) (
    START ('2026-01-01') END ('2028-01-01')
    EVERY (INTERVAL '1 month')
);

This table is sharded across segments by user_id and, on each segment, further sliced into monthly partitions by event_date. A WHERE event_date BETWEEN '2026-03-01' AND '2026-03-31' filter triggers partition pruning, and every segment reads only the March chunk in parallel.

The mental model: distribution is horizontal scaling across machines, partitioning is logical pruning within a machine. Both are required to get a decent plan on a multi-billion-row fact table.

Data skew and how to spot it

Skew is the term for an uneven distribution — too many rows on one segment, too few on the others. The whole cluster runs at the speed of the slowest segment, so a single hot segment turns a 64-node cluster into a glorified laptop.

SELECT gp_segment_id, count(*)
FROM orders
GROUP BY gp_segment_id
ORDER BY 2 DESC;

If the top segment holds more than roughly 2x the median row count, you have a skew problem. A healthy distribution lands within ±10% across all segments.

The fixes, in order of preference: switch to a higher-cardinality key, add a synthetic salt column like hash(user_id || ':' || random_bucket), or replicate the offending table if it's small enough to fit on every segment. Salting is the one most candidates forget, and it's the answer interviewers like best because it shows you understand the underlying hash distribution.

Greenplum is Postgres underneath, which means dead tuples accumulate after every UPDATE and DELETE, and the optimizer's statistics drift fast. Regular VACUUM ANALYZE is not optional on a busy table — skipping it is one of the top causes of plans that flip from a fast redistribute to a catastrophic broadcast overnight.

Common pitfalls

The first pitfall is leaving DISTRIBUTED BY off the table definition entirely. Greenplum will fall back to the first column with a unique constraint, or to random distribution if none exists. Random distribution forces a motion on every join, every time. The fix is to always write the distribution clause explicitly, even when you think the default would work — the explicitness alone is worth the code review it triggers.

A second trap is picking a distribution key with low cardinality. A column with under a few thousand distinct values guarantees skew, and adding salt only papers over the problem. The cleaner fix is to denormalize a higher-cardinality key into the table, or to accept that a DISTRIBUTED REPLICATED table is the right shape for what is effectively a dimension lookup.

A third pitfall is running distributed joins without aligning the keys. This is the single most common cause of slow Greenplum queries, and it is almost always invisible in the SQL itself. The only way to catch it is to read the plan and look for Redistribute Motion underneath any Hash Join — when you see one, the answer is to change the upstream distribution rather than to add an index.

A fourth failure mode is ignoring gp_segment_id during diagnostics. Without grouping by segment, you cannot see skew, and you will spend hours tuning the query when the actual problem is data layout. Build the segment-count query into your standard diagnostic toolkit and run it any time a query is slower than expected.

A fifth pitfall is forgetting ANALYZE after a large load. Stale statistics push the optimizer toward broadcast motions on tables that have grown ten times larger since the last stats refresh, and a single broadcast of a 500GB table will OOM the cluster. Schedule ANALYZE after every batch load and after any significant UPDATE campaign.

A sixth trap is ORDER BY on a massive table without a LIMIT. The Gather Motion pulls the entire result back to the master, which then tries to sort it in memory. The master runs out of RAM and the query dies with a misleading error message. Always pair ORDER BY with LIMIT, or replace it with aggregates that the segments can compute in parallel before the gather.

The last one bites people only once: changing the distribution key on a production table. ALTER TABLE ... SET DISTRIBUTED BY physically rewrites the entire table, which on a multi-terabyte fact means hours of downtime. The fix is to script a create-table-as-select into a new table, swap the names in a transaction, and drop the old one in a quiet window.

Related reading

• ClickHouse as a DWH — data engineering interview
• Spark shuffle and skew — data engineering interview
• Database sharding — data engineering interview
• EXPLAIN and query plan — data engineering interview
• DWH modeling — data engineering interview

If you want to drill questions like this every day, NAILDD is launching with hundreds of data engineering interview problems covering exactly these MPP and distribution patterns.

FAQ

Greenplum vs ClickHouse — which one should I pick?

Greenplum is a traditional MPP analytical database, stronger on complex multi-way joins, ACID guarantees, and the kind of regulated workloads where consistency matters more than raw scan speed. ClickHouse is purpose-built for time-series and append-only fact tables, with extremely fast aggregations on wide events but weaker join performance. Many shops run both — Greenplum as the core enterprise warehouse, ClickHouse as the hot analytics layer for product telemetry and dashboards. For an interview answer, frame it as a tradeoff between join flexibility and scan speed, not as one being better than the other.

What is the interconnect and why does it matter?

The interconnect is the network layer that carries data between the master and the segments, and between segments during motion operations. On large clusters it becomes the bottleneck for any query that triggers a redistribute or broadcast. Greenplum supports both UDP and TCP interconnects, with tunable parameters around buffer sizes and timeouts. If your queries hit interconnect timeout errors, the fix is usually network tuning rather than query tuning — but you should still try to eliminate the motion operator first.

Apache Greenplum vs commercial forks?

Apache Greenplum has been open source since 2015 and the upstream project is actively maintained. Several vendors ship hardened distributions with their own management tools, observability, and support contracts. For an interview, you only need to know the underlying concepts — distribution, motion, partitioning, skew — because those are identical across every distribution. If the company runs a specific fork, the vendor-specific operational details are something you can learn in the first two weeks on the job.

Are bitmap indexes worth using in Greenplum?

Bitmap indexes are a good fit for analytical queries on low-cardinality columns like status, country_code, or gender, especially when the predicate is an equality check. They take less space than B-tree indexes and answer equality lookups faster. For high-cardinality columns like user_id or order_id, stick with B-tree. Greenplum also supports bitmap index AND/OR combinations, which can replace several single-column B-trees on a fact table — worth knowing if the interviewer probes on index strategy.

What are resource queues and resource groups?

Resource queues and the newer resource groups are the workload management features that cap how much CPU, memory, and concurrency each user or role can consume. Without them, a single runaway analytical query can OOM the cluster and take down every other tenant. In a shared warehouse you assign analytics, ETL, and ad-hoc users to different groups with different memory and concurrency limits. Interviewers usually ask about this in the context of multi-tenant clusters or noisy-neighbor problems.

Is this article official documentation?

No. This is a study guide based on the Apache Greenplum documentation, vendor whitepapers, and real on-call experience from data engineers running Greenplum clusters in production. Specific parameter values, behavior on ALTER TABLE, and resource group syntax can differ slightly between versions, so always cross-check the docs for whatever build your target company runs before you walk into the interview.