I85PREP
⌘ K
Cloudera CDP Data Analyst study materials
Cloudera CDP Data Analyst study resources

Cloudera CDP Data Analyst: Data Exploration and Preparation

Cloudera CDP Data Analyst · Work through your exam, one topic at a time.

All guide sections

Data Exploration and Preparation

This domain covers profiling unfamiliar tables, cleaning types and nulls, deduplicating, and producing trustworthy curated tables. Exam items are practical: given a messy dataset description, which check or transformation reveals the defect and which silently corrupts results.

Profiling before you transform

  • DESCRIBE FORMATTED tbl shows SerDe, location, input format, and whether the table is managed, external, or transactional.
  • SHOW PARTITIONS tbl and SHOW TABLE STATS tbl reveal partition layout; SHOW COLUMN STATS gives distinct counts, null counts, and min/max in Impala.
  • Basic profile per column: COUNT(*), COUNT(col), COUNT(DISTINCT col), MIN, MAX, and a null/blank ratio.

Cleaning rules that matter

  • Null versus empty string: in Hive and Impala, '' is not NULL. Use NULLIF(TRIM(col),'') before null checks.
  • Safe casting: CAST('abc' AS INT) returns NULL rather than raising an error, so bad rows vanish silently. Detect them with CASE WHEN col RLIKE '^[0-9]+$' THEN CAST(col AS INT) END.
  • Date ambiguity: 2024-03-05 is unambiguous; 03/05/2024 is not. Pin the format with to_date(from_unixtime(unix_timestamp(col,'MM/dd/yyyy'))) in Hive or TO_DATE(col,'MM/dd/yyyy') in Impala, and confirm with a min/max sanity check.
  • Deduplication: use ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY ingest_ts DESC) and keep rn = 1, then persist with CTAS so downstream queries are cheap.
  • Malformed CSV: set skip.header.line.count and an explicit SerDe; a single bad row can shift every subsequent column.

Worked example

A customers landing table has 10,000,000 rows but COUNT(DISTINCT customer_id) returns 9,400,000. Profiling shows duplicates created by re-runs. Build a curated table:

CREATE TABLE customers_cur AS
SELECT customer_id, email, region, updated_at
FROM (SELECT c.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) rn
      FROM customers_landing c) x
WHERE rn = 1;

Then add COMPUTE STATS customers_cur and verify COUNT(*) = COUNT(DISTINCT customer_id).

Traps

  • Using COUNT(*) for a null check - only COUNT(col) excludes nulls.
  • Deduplicating with DISTINCT when you need the latest record per key, not any record.
  • Sampling with LIMIT (first-N, not random) instead of TABLESAMPLE or ORDER BY RAND().
  • Trusting a schema-on-read table whose columns silently shifted after a delimited-file change.