Python and SQL for FDEs: count the keys and check the JOIN before trusting the numbers

On Monday you are given read access to the customer’s Postgres database. By noon, their head of sales wants revenue by region. You write a JOIN between the orders and customers tables, it runs in three seconds, and you send over the results.

That afternoon they call back. The total revenue in your table is higher than the figure accounting has. The query has no syntax errors and threw no errors. It simply counted some orders twice, without telling anyone.

Situations like this are why Python and SQL are treated as the floor for FDE work. You do not need to know a long list of frameworks. What you need is the habit of checking data before you trust it, and enough skill to turn those checks into code you can run again.

What do job descriptions say about this floor?

Capicua describes FDE work as writing production code that runs directly on the customer’s infrastructure and data. Job postings point the same way; they differ mainly in whether SQL is named.

Databricks lists Python and SQL explicitly in its requirements for a Sr. FDE role in Singapore, and also wants FDEs who can integrate AI APIs such as OpenAI, Anthropic and Gemini into applications. Palantir asks for proficiency in a language such as Python, Java or C++, and names Python for data processing and analysis, but does not mention SQL.

Even so, another Palantir posting mentions Postgres, Cassandra, Hadoop and Spark. Paraform sums it up as one backend language plus the ability to pull and shape data quickly.

When you read a job description, look for phrases such as “pulling and shaping data” or “data processing”, or for named systems such as Postgres and Spark. They signal that you will be working directly on real data. Even AI API integration sits on top of data, and if the data is wrong, the product is wrong too.

The key word is “customer’s”: someone else designed that data, and you have no idea how many generations of systems have modified it.

Why is SQL first a checking tool?

HCL GUVI calls SQL one of the most practical skills an FDE can have. Its reasoning is that customer environments are full of databases, operational records and inconsistent data. It also warns that a bad JOIN can create duplicate records or silently erase important information.

Its advice is to investigate customer data rather than trust it by default.

So an FDE’s SQL has two jobs. The first is to question the data: is the key really unique, does the JOIN inflate the row count, have any rows been dropped? Only after that does it answer the business question.

Example: taking apart an inflated revenue figure

Back to Monday afternoon. Suppose the customers table has the columns customer_id, region and updated_at. The first question is whether customer_id is really a unique key. (The column aliases below are in Vietnamese: row_count is row count, customer_count is customer count, record_count is record count.)

-- 1. Is the key unique?
SELECT COUNT(*) AS row_count,
       COUNT(DISTINCT customer_id) AS customer_count
FROM customers;

-- If row_count > customer_count, see who is duplicated
SELECT customer_id, COUNT(*) AS record_count
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY record_count DESC
LIMIT 10;

Imagine the result shows that some customers have two or three records: each time they changed address, the system added a new row instead of updating the old one. Every order from those customers gets multiplied in the JOIN. That is where the extra revenue came from.

The next step is to measure the JOIN’s effect directly rather than guess:

-- 2. Does the JOIN change the row count?
SELECT
  (SELECT COUNT(*) FROM orders) AS before_join,
  (SELECT COUNT(*) FROM orders o
     JOIN customers c ON c.customer_id = o.customer_id) AS after_join;

-- 3. Are there orders with no matching customer (an INNER JOIN would drop them)?
SELECT COUNT(*) AS unmatched_orders
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

before_join (before the JOIN) and after_join (after it) must be equal. If after_join is larger, you are multiplying rows. If unmatched_orders (unmatched orders) is greater than 0, the original INNER JOIN is quietly dropping those orders.

The fix: keep only the latest record for each customer, and use a LEFT JOIN so no order is lost.

WITH cust AS (
  SELECT customer_id, region,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY updated_at DESC) AS rn
  FROM customers
)
SELECT COALESCE(cust.region, '(unknown)') AS region,
       SUM(o.amount) AS revenue
FROM orders o
LEFT JOIN cust ON cust.customer_id = o.customer_id AND cust.rn = 1
GROUP BY 1
ORDER BY revenue DESC;

The (unknown) (“unknown”) row is not an error to hide. It is a finding to take back to the customer: how many orders cannot be linked to any customer, and why.

Python makes the checks repeatable

The SQL above only answers for today. Next week, when new data arrives, you need those checks to run and fail on their own. Python does that, and it is where the “data processing and analysis” in Palantir’s posting becomes concrete work.

import os
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine(os.environ["CUSTOMER_DB_URL"])

orders = pd.read_sql("SELECT order_id, customer_id, amount FROM orders", engine)
customers = pd.read_sql(
    "SELECT customer_id, region, updated_at FROM customers", engine
)

# Keep the most recent record for each customer
customers = (customers.sort_values("updated_at")
                      .drop_duplicates("customer_id", keep="last"))

# validate raises an error if the customers side still has duplicate keys
merged = orders.merge(customers, on="customer_id",
                      how="left", validate="many_to_one")
assert len(merged) == len(orders), "JOIN changed the row count"

unmatched_rate = merged["region"].isna().mean()
print(f"Share of orders with no matching customer: {unmatched_rate:.1%}")

report = (merged.fillna({"region": "(unknown)"})
                .groupby("region")["amount"].sum()
                .sort_values(ascending=False))

The three most valuable lines here are validate="many_to_one", which raises an error if the customers side still has duplicate keys; the assert, which fails if the JOIN changed the row count; and the line that prints the share of orders with no matching customer. Together they turn the lesson of Monday afternoon into a hard requirement. Next time the data has a problem, the pipeline stops before a wrong number reaches the customer.

In what order should you work?

Every time you meet a new customer table, follow the same sequence. First, count the rows and the distinct keys to see whether the key really is unique. Then measure the row count before and after each JOIN, and count dropped records with a LEFT JOIN plus IS NULL.

Once you see a problem, fix it at the query level with ROW_NUMBER() or a record-selection rule you have confirmed with the customer. Finally, move all the checks into Python as asserts, and always report the share of unmatched data alongside the total, not just the total.

Common mistakes

The most common mistake is using INNER JOIN out of habit. It looks tidy but drops exactly the records you need to see. Another is deciding the deduplication rule yourself: “take the latest record” is a business decision, so ask the customer rather than guess.

A further mistake is patching the problem with SELECT DISTINCT at the end of the query. It hides the symptom without telling you the cause, and it can merge rows that are genuinely different.

The last is checking by hand in a notebook without saving the work as a script. Next week the data changes, the old bug returns, and nobody remembers what was checked.

How to show this skill on a CV

For a developer moving into FDE work, “proficient in Python and SQL” on a CV says almost nothing. A more convincing line tells a story: found that a customer table had duplicate keys inflating reported revenue, wrote automated checks in pandas, and reported the rate of unmatched orders to the operations team.

This week’s exercise

Take a real database you have read access to, either at work or a public dataset with at least two related tables. Pick a report query that you or a colleague once wrote, run the three checks above, then rewrite it as a Python script with validate and assert.

If all three checks come back clean, you now have evidence that the number is right. If not, you have found the bug before the customer did, and that is an FDE’s job.