AWS S3 Data Source Template

Overview

The AWS S3 data source template turns an S3 bucket (optionally scoped to a prefix) into a Validatar data source. It lists objects with boto3, catalogs every object as a table, reads and profiles the common data formats (CSV, Excel, Parquet, Avro, JSON, XML, GeoJSON), and captures statistics for non-tabular objects (PDF, Markdown, images, plain text). Key prefixes become schemas, objects become tables, and columns are inferred from each object's contents.

This is a Python (Script) template that uses the same shared file-reading library as the Windows Directory and Azure Blob Storage templates. The macros, profiling, and custom fields are identical — only the storage layer (boto3 against S3) and the connection parameters differ.

Platform: Amazon S3
Connection Category: Script (PythonScript)
Runs on: any runtime that has boto3 and network reachability to S3 — typically the Data Agent
Template version: 1.1.0

What's new in 1.1.0: seven macros for nested JSON (see Nested JSON); a fix for pretty-printed .json arrays, which were previously misdetected as line-delimited JSON and failed to read; and a fix for folder names whose first character formed a Python escape sequence.

How It Works

• Prefixes are schemas, objects are tables. The template lists the bucket under the configured prefix. Each key "folder" becomes a schema (.-rooted, e.g. .\raw\parquet\customer_1001); each object becomes a table.
• Columns are inferred on read. Objects are read into data frames and columns/types inferred (Parquet and Avro carry explicit types; CSV and Excel are inferred).
• One ingestion pass catalogs the whole scope at the data-source level.
• Scope with bucket + prefix and control depth with include_sub_folders.

Authentication

The template supports two authentication styles from a single template. Which one is used is determined by whether you fill in the access-key parameters.

With the access-key parameters blank, boto3 resolves credentials from its standard default chain (environment variables → shared config → instance/container role). This is the recommended pattern when Validatar runs inside AWS, because there are no long-lived secrets to store or rotate.

Tip: Prefer the IAM-role path in production. There are no long-lived secrets to store or rotate, and the policy is identical either way — see below.

IAM Policy

This template is read-only. It never writes to S3, never deletes, and never modifies bucket configuration, so the policy it needs is small.

Attach it to whichever identity the runtime resolves: the IAM user behind the access key, or the instance profile / task role if you left the key parameters blank. The policy is the same in both cases — only the identity it hangs off differs.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "S3ReadObjects",
      "Effect": "Allow",
      "Action": "s3:GetObject",
      "Resource": "arn:aws:s3:::YOUR-BUCKET/*"
    },
    {
      "Sid": "S3ListBucket",
      "Effect": "Allow",
      "Action": [
        "s3:ListBucket",
        "s3:GetBucketLocation"
      ],
      "Resource": "arn:aws:s3:::YOUR-BUCKET"
    }
  ]
}

Why there are two different ARNs

This is the detail that causes most S3 policy problems, and it looks like a typo until you know:

• s3:GetObject is an object-level action. Its resource is arn:aws:s3:::YOUR-BUCKET/* — with the /*.
• s3:ListBucket and s3:GetBucketLocation are bucket-level actions. Their resource is arn:aws:s3:::YOUR-BUCKET — no /*.

Put ListBucket on the /* ARN and it silently matches nothing: ingestion completes, no error is raised, and the catalog comes back empty. Put GetObject on the bare bucket ARN and listing works while every read fails.

Note: The IAM action is s3:ListBucket, but the API call it authorizes is ListObjectsV2. If you're reading CloudTrail to debug a denial, those are the same thing under two names.

Scoping to a prefix

If the data source uses the prefix parameter, you can narrow the object permission to match:

{
  "Sid": "S3ReadObjects",
  "Effect": "Allow",
  "Action": "s3:GetObject",
  "Resource": "arn:aws:s3:::YOUR-BUCKET/data/warehouse/*"
}

You can also constrain listing with an s3:prefix condition, but do so carefully — the condition has to permit every prefix the template actually lists, including the parent when include_sub_folders is enabled. A prefix condition that is slightly too tight produces the same silent empty catalog as the wrong ARN. Restricting s3:GetObject by resource path is the simpler and more predictable control.

Encrypted buckets

If the bucket uses SSE-S3 (AES256), the policy above is sufficient — decryption is transparent.

If it uses SSE-KMS, reading also requires permission on the key, or every object read fails with AccessDenied even though the S3 permissions are correct:

{
  "Sid": "DecryptWithKmsKey",
  "Effect": "Allow",
  "Action": [
    "kms:Decrypt",
    "kms:DescribeKey"
  ],
  "Resource": "arn:aws:kms:REGION:ACCOUNT:key/YOUR-KEY-ID"
}

Note that kms:GenerateDataKey is not needed — that is a write-path permission, and this template only reads.

Confirming it works

Test with the same identity Validatar will use, rather than your own:

aws s3 ls s3://YOUR-BUCKET/ --recursive --profile YOUR-PROFILE

If that lists objects, ingestion will catalog them. An AccessDenied here is a permissions matter, not a template one — and it is far quicker to diagnose at the CLI than through a failed ingestion run.

Athena is different. If you are also using the AWS Athena template, note that its policy is substantially larger and is not read-only — Athena has to write query results to S3, and it needs Glue catalog permissions plus the easily-missed athena:GetQueryResultsStream. Do not reuse this policy for Athena.

Connection Parameters

Required Python Packages

These must be present on the runtime that executes the source (the Data Agent, or a cloud runtime that carries the SDK).

Required

• boto3 — the AWS S3 client (required for this template)
• pandas, numpy — the core data-frame and numeric layer
• pyarrow — Parquet / ORC / Feather
• fastavro — Avro
• openpyxl — Excel (.xlsx / .xlsm)

Optional (features degrade gracefully if absent)

• fitz (PyMuPDF) — PDF statistics
• Pillow (PIL) — image statistics
• charset_normalizer — text-encoding detection
• lxml — XML reading

Note: The library degrades per object, not per run — a missing optional package only affects the objects that need it; the rest of the catalog continues.

Capabilities

Supported File Types

Note: Line-delimited JSON is detected even when the object has a .json extension — common for S3 export jobs — and flattened into columns.

Macros

The template ships with 21 generic macros. Because every macro goes through the shared reader, the same test works against any object and any supported format. Parameters are metadata-linked (Folder → Schema, File → Table, Column) so they render as catalog-driven dropdowns.

A. Single-object data

B. Pattern & union (multi-object)

C. Prefix & object information

D. Content statistics (including non-tabular)

Tip: Folder - Union Files Matching a Pattern is ideal for partitioned S3 layouts — point it at a prefix of *.parquet files and it returns one unified dataset with a _source_file column, so you can validate the combined set and still trace any row to its object.

E. Nested JSON

The seven macros above deal with JSON as a flat table. These seven deal with it as a tree.

The distinction matters more than it sounds. The standard reader normalizes nested objects into dotted columns — customer.address.city works fine. But a nested array cannot become a column, so it stays in the cell as a raw list:

order_id     customer.address.city   items
ORD-00001    Austin                  [{'sku': 'WID-001', 'quantity': 3, ...}, {...}]

You cannot count that, group it, join on it, or assert anything about it. For order payloads, event streams, API captures — anything where the interesting data is inside a repeated element — that is where testing usually stops.

Start with Discover Nested Structure. It is the map — run it once against a sample object and it tells you every path, how deep it is, and which ones are arrays, so you know what to feed the other six.

Then reach for Explode an Array Path, not Flatten to Table. Both produce tabular output, but flattening with arrays=explode expands every array at once, and sibling arrays multiply: a payload with 3 line items and 2 contacts yields 6 rows, and any sum over it is wrong by a factor of 2. Exploding one path does not have that problem. Flatten's default is therefore join, which keeps one row per record and encodes arrays as text.

Note: explode is the right choice when you genuinely want the cross-product, or when only one array is present. The parameter exists so the behaviour is a decision rather than a surprise.

See Macros for how macros are used when building tests, and the AWS S3 — Nested JSON Sample Tests pack for six worked examples, including one that joins a SKU two levels deep inside an array to a column in an Excel file.

Metadata Ingestion

Ingestion runs once at the data-source level. It lists the bucket under the prefix, registers each key prefix as a schema and each object as a table, and populates the File Info catalog custom fields — including per-prefix rollups (object count, total size, distinct file types, newest/oldest modified). Column-level metadata is discovered on demand by the profile set.

See Metadata Ingestion Scripts — Python Templates.

Profiling

The template ships one profile: File Profile (columns + table metrics, any file type) — the same type-safe profiler as the Windows Directory template. Run it as a profile set against a prefix to profile every object in it. Following the principle that numeric/statistical values are profiles and descriptors are custom fields, it produces:

• Column-level profiles: null/blank/distinct counts and percentages, length statistics, most-common value, numeric statistics (min/max/mean/median/standard deviation, zero/negative counts), and date range.
• Table-level file metrics: file size (bytes), file age (days), row count, column count, and type-specific metrics (PDF page count, content word/line/record count, image width/height).

See Profiling Configuration — Python Templates.

Catalog Custom Fields (required dependency)

The ingestion script emits File Info catalog custom fields to describe objects and prefixes — the text/tag/date descriptors and prefix rollups that don't fit the numeric profile model. Import the File Info - Catalog Custom Fields set before running ingestion; Validatar silently drops custom-field columns with no matching definition.

Schema level (prefix rollups): Contains Files, Schema Object Represents, Full Path, File Count, Total Size (bytes), Distinct File Types, Readable File Count, Newest Modified, Oldest Modified

Table level (per object): File Type, File Name, Sheet Name, File Size, File Total Bytes, File Extension, Date Modified, File Logical Table

Warning: Catalog custom fields import through the UI only (Settings > Catalog Custom Fields > Import) and require the GlobalCustomMetadataAdmin role.

Installation

1. Import the custom fields first — the File Info - Catalog Custom Fields set (Settings > Catalog Custom Fields > Import).
2. Import the template — Settings > Data Source Templates > Import, select the AWS S3 template file.
3. Create a data source and set Bucket, Prefix, and Region. For access-key auth, add the two secret keys; for IAM-role auth, leave them blank.
4. Assign a runtime with boto3 and network access to S3.
5. Run metadata ingestion, then run the File Profile profile set.

What You Still Need to Configure

• Credentials strategy — access keys vs. IAM role. Prefer the role when Validatar runs in AWS.
• IAM permissions — read on the target bucket/prefix (s3:GetObject, s3:ListBucket).
• Runtime placement — a Data Agent or cloud runtime that carries boto3.
• Scope — set prefix to avoid cataloging an entire large bucket, and include_sub_folders to control depth.

Troubleshooting

Parquet writer/reader version skew

Parquet is not as version-portable as it looks. A file written by pyarrow 20 or newer records size statistics that older readers reject outright:

Columnar read failed for sales_fact.parquet: Repetition level histogram size mismatch

This is a reader limitation, and no writer setting avoids it — format version (1.0, 2.4, 2.6), data_page_version, store_schema, write_statistics, use_dictionary and flavor="spark" all still produce a file the older reader refuses. pyarrow exposes no size-statistics toggle in its Python API.

If you hit this, upgrade pyarrow on the Data Agent host to at least the version that wrote your files. It is worth checking proactively: modern Spark, pandas and dbt all write Parquet with recent Arrow, so a lagging agent runtime can fail on files that every other tool in your stack reads without complaint.

To see what your agent actually has, run a one-line Python test against the data source:

import pyarrow, pandas, sys
dataframe = pd.DataFrame({"VALUE": [f"py={sys.version.split()[0]} pyarrow={pyarrow.__version__} pandas={pandas.__version__}"]})

Related Articles

• What Is a Data Source Template?
• Windows Directory Data Source Template
• Azure Blob Storage Data Source Template
• Python Script Data Source Template
• Macros
• Metadata Ingestion Scripts — Python Templates
• Profiling Configuration — Python Templates