Query habits for DE

What you will be able to do

• Validate grain with COUNT and COUNT DISTINCT
• Write a post-load checklist query
• Avoid SELECT * in saved job SQL

Why this matters for data work

After a load, DE asks: row count, nulls, duplicates, totals. These habits catch bad pipelines before dashboards lie.

Concepts

• Grain: what one row means (order_id unique?).
• COUNT(*) vs COUNT(DISTINCT key).
• Duplicate hunt: GROUP BY key HAVING count(*) > 1.
• Pin columns in scripts and PR-reviewed SQL.

Practice on your machine

export PGPASSWORD=dcubes PGHOST=127.0.0.1 PGPORT=15432 PGUSER=dcubes PGDATABASE=retail
psql << 'SQL'
-- uniqueness
SELECT count(*) AS rows, count(DISTINCT order_id) AS distinct_ids FROM orders;

-- duplicate keys (expect 0)
SELECT order_id, count(*)
FROM orders
GROUP BY order_id
HAVING count(*) > 1;

-- null check on required fields
SELECT
  count(*) FILTER (WHERE store_id IS NULL) AS null_store,
  count(*) FILTER (WHERE line_total IS NULL) AS null_total,
  count(*) FILTER (WHERE status IS NULL) AS null_status
FROM orders;

-- status distribution
SELECT status, count(*) FROM orders GROUP BY 1 ORDER BY 2 DESC;
SQL

What you should see

• rows = distinct_ids = 300.
• No duplicate order_id groups.
• Zero nulls on checked columns.

Common mistakes

• Trusting a single SUM without row counts.
• Fixing data in a dashboard instead of the load.
• Skipping DISTINCT checks on supposed unique keys.

Next

You are ready for the capstone: First SQL shift.