External Table Partitions Not Visible in CDP Data Warehouse
A CDP Public Cloud Data Warehouse uses external Hive tables over S3. A NiFi flow lands Parquet files under s3a://lake/events/dt=YYYY-MM-DD/hour=HH/ every 10 minutes; 600 tables produce about 2,500 new partitions per day. The HMS is shared by Hive and Impala. A nightly MSCK REPAIR TABLE job takes 45 minutes for the largest table and sometimes times out after adding only part of the day's partitions. Analysts querying the latest hour see zero rows even though aws s3 ls shows objects. Some jobs use INSERT OVERWRITE with dynamic partitions and do see data. The team wants to remove the nightly repair and guarantee that a partition is queryable within two minutes of the file being committed, without giving analysts S3 list permissions. They also need to handle late-arriving files for yesterday and schema evolution that adds two columns.
1. Why is the latest data invisible to analysts, and why do dynamic INSERT OVERWRITE jobs see their data? What would you check to confirm the diagnosis?
Read the worked answer
Hive external table reads are driven by HMS partition metadata, not by the object store listing. NiFi is writing Parquet objects directly to S3; that creates files but does not create partition entries in the HMS. A query with a dt/hour predicate is pruned against HMS partitions, so the latest partition is absent from the plan and the query returns zero rows. aws s3 ls proves objects exist, not that the table knows about them.
Dynamic INSERT OVERWRITE behaves differently because Hive writes the data and registers the dynamic partitions as part of the write. That is why those jobs see data immediately while the NiFi landing path does not. It is not evidence that external tables auto-discover S3 prefixes.
Confirm the diagnosis with SHOW PARTITIONS db.events and compare the latest entries to the S3 prefix. Use DESCRIBE FORMATTED db.events to confirm the table location and partition columns. Query the HMS partition table or use SHOW PARTITIONS filtered to the date. Run EXPLAIN on the analyst query and check partition pruning. If the partition is missing from HMS, the query cannot see the files. Also verify Impala's catalog state: after a HMS change, Impala may need REFRESH db.events or INVALIDATE METADATA db.events if its catalogd event processing missed the DDL. Analysts must not depend on S3 list permissions; metadata access through HMS is the correct control point.
2. Design a replacement for the nightly MSCK job that registers partitions idempotently within two minutes. Include ordering, retries, late data, and Impala visibility.
Read the worked answer
Move partition registration to the write path. NiFi should write objects to a temporary prefix, then commit or copy them to the final prefix only after the object write succeeds. After the final commit, call HiveServer2 with ALTER TABLE db.events ADD IF NOT EXISTS PARTITION (dt='...', hour='...') LOCATION 's3a://lake/events/dt=.../hour=.../'. This is a metadata operation scoped to one partition, so it is fast and idempotent; IF NOT EXISTS makes retries safe. The order must be data first, metadata second. If metadata is added before the data commit, readers can see an empty partition.
Drive the call from a control table or NiFi queue with at-least-once retries and a deduplication key of table, dt, and hour. For late-arriving files for yesterday, run the same ADD IF NOT EXISTS; if the partition already exists, no metadata change is needed, but issue REFRESH db.events in Impala so the new files are visible. For the large existing table, backfill with a one-time scripted ADD PARTITION loop or MSCK during a maintenance window, then stop using nightly MSCK. Do not run MSCK every night for 2,500 partitions; its directory scan is the wrong tool for a known partition path.
For Impala, rely on HMS event processing where possible, but add a post-batch REFRESH db.events for the affected table if visibility exceeds the two-minute SLA. Avoid INVALIDATE METADATA on every file; it is heavier and can cause catalog storms. Test from both Hive and Impala as an analyst who has no S3 list permission.
3. How would you handle schema evolution for two new columns, and when would you recommend moving these tables to Iceberg instead of patching the external-table flow?
Read the worked answer
Add the new columns at the end of the table schema with ALTER TABLE db.events ADD COLUMNS (...) and keep the same names and types in the NiFi Parquet writer. Parquet readers resolve fields by name, so older files that lack the new columns return null for them. Do not insert the columns in the middle of the schema or rename existing columns; that creates mismatches across old and new files and can break readers that rely on position. After the DDL, refresh Impala metadata and test a query that projects old and new partitions. If the new columns are nested or the types are incompatible, plan a backfill instead of relying on schema evolution.
This external-table design is acceptable when the partition path is stable, partition registration is part of the writer, and schema changes are additive and infrequent. It becomes fragile when you need 2,500 partitions per day, late data, frequent schema changes, and two-minute visibility across Hive and Impala. Each of those increases metadata operations and reconciliation logic.
Recommend Iceberg when the team wants the table format to manage files, snapshots, and schema evolution rather than maintaining HMS partition registration. Iceberg supports hidden partitioning, snapshot isolation, and maintenance procedures such as rewrite_data_files and expire_snapshots. Migrate by creating an Iceberg table with the same logical schema, dual-writing or backfilling a bounded date range, comparing row counts and query results, then switching readers. Keep S3 permissions unchanged: analysts query through the SQL engine, NiFi keeps write access, and no analyst receives S3 list rights.