Athena NULL Values: Diagnosing CSV Schema Mismatch in Glue Data Catalog
A company is storing customer transaction data in Amazon S3 as CSV files. A data scientist uses AWS Glue to crawl the data and create a table in the AWS Glue Data Catalog. When querying the table with Amazon Athena, the data scientist notices that some columns have NULL values where data should exist. The data scientist examines the raw CSV files and confirms the data is present. What is the most likely cause of the NULL values?
Quick Answer
The answer is a schema mismatch across CSV partitions, where the Glue crawler infers the table schema from only the first few files and ignores columns present in later files. This occurs because the crawler samples data to define the schema in the Glue Data Catalog, so when different partitions contain varying columns—such as an extra field in newer files—Athena cannot map those unmapped columns and returns NULL values for the missing schema entries. On the AWS Certified Machine Learning Specialty MLS-C01 exam, this scenario tests your understanding of how Glue crawlers handle semi-structured data and the common pitfall of assuming uniform schemas across partitioned datasets. A frequent trap is blaming data type inference or SerDe issues, but the root cause is almost always inconsistent column structures between partitions. Remember: if your CSV data has NULLs where data exists, think “schema drift across partitions”—the crawler’s first-file snapshot is the culprit.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
The CSV files have different schemas (e.g., different columns) across partitions.
The most likely cause is that the CSV files have different schemas across partitions. When AWS Glue crawler infers the schema, it samples a subset of files. If partitions have different columns or column order, the inferred schema may not include columns present only in later partitions. When Athena queries the table, it uses the schema from the Data Catalog; columns missing from the schema appear as NULL. Option A is correct. Option B is incorrect because Athena does not skip corrupted records by default; it would fail on parse errors. Option C is incorrect because data type inference errors would cause different issues, such as type mismatches, not NULLs for existing data. Option D is incorrect because the Glue crawler can handle custom delimiters if configured; the issue here is schema mismatch, not delimiter recognition.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
The CSV files have different schemas (e.g., different columns) across partitions.
Why this is correct
Schema evolution causes missing columns to appear as NULL when queried.
- ✗
Athena is configured to skip corrupted records, causing NULLs.
Why it's wrong here
Athena does not silently skip records; it would throw an error.
- ✗
The Glue crawler incorrectly inferred the data type of the columns.
Why it's wrong here
Incorrect data type would cause conversion errors, not NULLs.
- ✗
The CSV files use a custom delimiter that the Glue crawler does not recognize.
Why it's wrong here
The crawler can be configured to handle custom delimiters, but the issue is NULLs, not parsing.
Quick reference
AWS S3 Storage Class Comparison
| Storage Class | Min Duration | Retrieval | Use Case |
|---|---|---|---|
| S3 Standard | None | Immediate | Frequently accessed data |
| S3 Standard-IA | 30 days | Immediate | Infrequent access, rapid retrieval |
| S3 One Zone-IA | 30 days | Immediate | Non-critical infrequent data |
| S3 Intelligent-Tiering | None | Immediate–hours | Unknown or changing access patterns |
| S3 Glacier Instant | 90 days | Milliseconds | Archive with instant retrieval |
| S3 Glacier Flexible | 90 days | Minutes–hours | Archive, flexible retrieval |
| S3 Glacier Deep Archive | 180 days | Hours | Long-term compliance archive |
Go deeper
Related to this question
About these practice questions
One of 1,672 original MLS-C01 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
Same concept, more angles
2 more ways this is tested on MLS-C01
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. A data engineer runs a SQL query on Amazon Athena to explore a dataset stored in S3 as CSV. The query returns zero rows for a column that should have numeric values. Which step should the engineer take to diagnose the issue?
medium- A.Verify that the S3 bucket has encryption enabled.
- B.Run an AWS Glue crawler to update the table schema.
- C.Add a partition to the table for the date column.
- ✓ D.Check the table schema in AWS Glue Data Catalog to ensure the column data type is correct.
Why D: Checking the table schema in the AWS Glue Data Catalog helps identify data type mismatches. If Athena returns zero rows for a numeric column, it may be because the column's data type in the catalog is incorrect (e.g., string instead of int). Fixing the schema to match the actual data allows Athena to parse the values correctly. Option A is incorrect because encryption does not affect query results. Option B is incorrect because running a crawler will only re-infer the schema, which may not solve the issue if the underlying data format is inconsistent. Option C is incorrect because partitioning is unrelated to data type issues.
Variation 2. A data scientist is using Amazon Athena to query a CSV file stored in S3. The query fails with the error: 'HIVE_CANNOT_OPEN_SPLIT: Number of fields in line 1502 does not match number of fields in the first line.' What is the most likely cause?
hard- A.The CSV file uses a different delimiter than comma.
- B.The CSV file is missing a header row.
- C.The CSV file is too large for Athena to process.
- ✓ D.The CSV file has inconsistent number of columns in some rows.
Why D: The error indicates that a row has more fields than the header, which is exactly what happens when the CSV file has inconsistent number of columns in some rows. Option A is incorrect because the error does not mention delimiter; a different delimiter would cause all rows to have wrong number of fields, not just some. Option B is incorrect because missing header would cause Athena to treat the first row as data, not cause mismatched field counts later. Option C is incorrect because Athena can handle large files; the error is about schema mismatch, not file size.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This MLS-C01 practice question is part of Courseiva's free Amazon Web Services certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the MLS-C01 exam.