Analyzing Configuration and Metadata Held in YAML With SQL

A repository of YAML is a database that nobody has queried. The files describe what runs: which services, which versions, which limits, which tests, who owns what. Every question a team asks about its infrastructure, such as "how many services use PostgreSQL 15", "which workloads have no resource limits", "which models are untested", is answered somewhere in those files, and the usual way to get the answer is to open them. That works for five files and fails at thirty. This guide treats the YAML as data: turn it into tables, ask SQL and turn the answers into reports and gates. Each technique is tested on a synthetic repository of 35 files, built with deliberate inconsistencies, and the results are real output. For the language itself, begin with YAML syntax you need to stop guessing.

Why analyze YAML as data

Text search finds strings. It does not know that image: postgres:18 and postgres: {image: postgres:17} are both references to a database image, that a - name: web under containers is a container and under volumes is a volume, or that two keys with the same name at different depths are different things. YAML is structure, and questions about structure need a tool that sees it. The alternatives are a bespoke script for every question, which is slow to write and rarely reused, and a policy tool for every domain, which is powerful and narrow. Turning the files into tables gives you one skill that covers every question: write SQL.

The method has three steps.

1. Parse each file with a safe loader, handling multiple documents.

2. Flatten the structure into rows, either generic (file, path, value) or domain-specific (one row per service or container).

3. Query with an engine that handles joins, grouping and filters, and write the result as a report or a gate.

DuckDB is a good engine for the third step: it is an in-process SQL database, it needs no server, it reads and writes Parquet and its Python API takes rows directly. SQLite or PostgreSQL work as well. The first two steps are where the care goes.

Two table shapes

There are two ways to turn YAML into rows, and the choice shapes every query.

The generic table has one row per scalar leaf: the file, the document index, the dotted path, the value's type and its text. It works for any YAML and needs no knowledge of the schema. Flattening all 35 files this way:

files: 35 | rows: 333 | distinct paths: 45
rows by kind: [('compose', 187), ('k8s', 98), ('config', 18), ('workflows', 15), ('helm', 15)]
value types: [('str', 295), ('int', 24), ('bool', 14)]
most common keys: [('image', 80), ('test', 60), ('ports', 41), ('name', 23), ('restart', 20)]

Thirty-five files became 333 rows over 45 distinct paths (with list indexes normalized, so ports[0] and ports[1] count as one). Eighty of the rows are image references. That table already answers "which images" (where path like '%image'), "which keys are most common" and "what types do values have". Its weakness is that facts about one entity are spread over several rows: a container's image, limits and probes are three rows, and a query that needs all three has to pivot or self-join.

The entity table has one row per thing you care about (a service, a container, a model) with one column per attribute. It is what the Compose and Kubernetes sections below use: a row per service with image, tag, ports and flags. Queries on it are short and readable. Its weakness is that you write a loop per kind of file, and the loop encodes your assumptions about the structure.

Use the generic table to explore and for questions that span kinds of file; use entity tables for the recurring reports. A third option for flat records is to let DuckDB's YAML extension read the files directly as tables (using DuckDB to query data stored as YAML), which suits files that are lists of similar records and suits Compose and Kubernetes badly, where the interesting data is under keys that are names.

A single caveat applies to all of them: the parser decides types. In our table there were 14 booleans stored as bool and 30 strings with the text true. Those 30 are the true command in healthchecks (["CMD", "true"]), and a reminder that a value that looks boolean is not always one. If your analysis depends on types, load with the same rules your tools use (catching type mistakes in YAML early).

Compose: counting a fleet

Twenty-four Compose files hold 66 services, two to four per file. One row per service, with image, tag, port count, healthcheck flag and restart policy, answers the fleet questions in one query each. Images by count: mysql 17, redis 15, app 14, postgres 14, nginx 6. Databases without a healthcheck: 16 of 46. Services that set restart: always: 20. Images under more than one tag: postgres with four (15, 16, 18, 18.6), app with three including latest, redis with three, mysql with two. The table is a second set of eyes on the fleet, rebuilt on every run, and it makes the healthcheck finding actionable because you can list the 16 files. See counting services across many Compose files.

Versions: where the fleet disagrees

The same database appears in Compose, CI, Helm and Kubernetes files, and each pin was right once. An extraction pass over every file, with a regex for single-string images and a structured read for Helm's split repository and tag, grouped by name:

postgres   {'15': 6, '16': 2, '17': 3, '18': 6, '18.6': 3}
redis      {'6-alpine': 5, '7-alpine': 3, '7.4': 8}
app        {'1.4.2': 6, '1.5.0': 4, 'latest': 4}
postgres majors: ['15', '16', '17', '18']

PostgreSQL is referenced twenty times under five tags, with four major versions. A CI workflow testing on 16 says little about production on 18. The scan also lists files with latest. The rule that follows is an expectation per image (major version for databases, exact tag for your own images), a job that fails on deviation, and an exceptions list with reasons. See finding inconsistent versions across YAML configs.

Kubernetes: auditing with SQL

A multi-document manifest becomes one row per container, with flags for limits, probes, privileged mode and runAsNonRoot. The eight containers:

no resource limits: ['billing', 'cache', 'legacy']
no probe: ['etl', 'legacy', 'search']
latest or untagged image: ['billing', 'etl']
privileged: ['legacy']
may run as root: ['legacy', 'mailer']

Each is a where clause. A score that adds a point per failed check ranks legacy at 4, billing and etl at 2 and cache at 1, which is a work queue. Combined questions find risks that single checks miss: three deployments run one replica with no probe, so a hang is neither restarted nor covered. The audit reports what the manifests say, which differs from what a cluster admits after defaults and policies, so render templates first and, for truth, parse the live objects with the same loop. See auditing Kubernetes manifests with SQL.

dbt: coverage from the manifest

A dbt project's YAML properties are merged into target/manifest.json by dbt parse, which makes the manifest the right input for coverage reports. On a five-model project:

models: 5 | tests: 5 | sources: 2
columns: 10 | with description: 3 (30%)
models with no tests: ['payments', 'stg_events']
owners: {None: 2, 'team-a': 2, 'team-b': 1}
sources with freshness: 1 of 2

Documentation coverage, untested models, missing owners and sources without freshness checks come out of about thirty lines of Python. dbt accepts all of it, since nothing is required, so coverage drifts unless something measures it. Ranking the backlog by the number of descendants (child_map) turns a long list into the five models that matter most. See summarizing dbt project metadata from YAML.

Duplicate keys and drift

Two different problems share a name. A key repeated within a file is silently overwritten by PyYAML, which loaded dev.yaml with port: 8082 and dropped two earlier ports, and staging.yaml with log_level: info, dropping debug. ruamel.yaml raised DuplicateKeyError for exactly those two files out of 35, and yamllint's key-duplicates rule named the lines. Fail the build on those. A key with different values across environment files is not an error; flattening each file and grouping by path listed port, db.host and log_level as differing. Some of that is the point of environment files and some is leftover, and the report is where the review starts. See finding duplicate keys across a config repository.

The same job with other tools

You do not need Python for every question. yq is a one-line answer for many of them: counting images across all the Compose files was

yq eval-all '[.services[].image] | .[]' repo/compose/*.yaml | sort | uniq -c | sort -rn | head -4

which printed 9 mysql:8.4, 8 redis:7.4, 8 mysql:8.0 and 6 postgres:15 in a few hundredths of a second. The shell version is the right size for one question. The table version wins when you ask the second question, because the rows are still there. jq over yq -o=json output works the same way. DuckDB's YAML extension is a shortcut for files that are lists of records. A policy engine is right for enforcement. A rule of thumb: one question once, use yq; a question you will ask again, or two questions that join, build the table.

A first hour, step by step

Suppose you have inherited a repository and want to know what is in it. Here is a path that works, with the commands in the order you would run them.

Minutes 0 to 10: inventory. List the YAML files and group them by directory or by name. Our repository: 35 files across compose, k8s, helm, workflows and config. Run yamllint over everything and read the counts, not the lines; a repository with hundreds of style complaints needs a configuration file before it needs an analysis. Note any file that fails to parse at all, since those fall out of every later number.

Minutes 10 to 25: the generic table. Flatten everything into (file, kind, doc, path, type, value) rows and load them into DuckDB. This costs about twenty lines and gives you every later question for free. Check the shape: how many rows, how many distinct paths with list indexes normalized, how many value types. For our repository that was 333 rows, 45 paths and three types (295 strings, 24 integers, 14 booleans). A path with an unexpected type is a lead.

Minutes 25 to 40: the obvious questions. Which images and tags; which ports; which restart policies. For example, the CI workflows ran on ubuntu-latest (3 of 3) and tested against PostgreSQL 16, 17 and 18, one version each, while the Compose files ran four majors; that single contrast is a finding. The Helm values set log_level to debug twice and info once. In the Kubernetes file, the replica counts were 1 (four deployments), 2 (two), 3 and 4, so half the workloads are single-replica.

Minutes 40 to 55: the dangerous questions. Privileged containers, latest tags, missing limits, missing healthchecks, duplicate keys. Each is a query or a short script. These are the findings that earn attention.

Minutes 55 to 60: write it down. Save the table as Parquet and the queries in a file. Next week you can rerun in seconds and compare. Send the findings to the owners, not as a long report but as five lines, each with a file name.

The point of the sequence is that the expensive step, flattening, happens once, and every later step is a question. You do not need to know the structure of the files in advance; you discover it from the paths.

Query patterns that come up again and again

A handful of SQL shapes cover most YAML questions.

Count by value. select value, count(*) from kv where path like '%image' group by value order by 2 desc. The first query to run on any key.

Distinct values per key. select path, count(distinct value) from kv group by path having count(distinct value) > 1. Finds the keys that disagree, which is the drift report.

Present in A, absent in B. select path from kv where file = 'a' and path not in (select path from kv where file = 'b'). Compares two files' key sets. We ran it for prod.yaml against staging.yaml, and the result was empty in both directions: they define the same paths, and differ only in values, which tells you the files are structurally in step.

Anomaly by group. group by a directory or kind column to compare teams or environments, with having to keep only groups that deviate.

Ranking. A sum of boolean checks cast to integers gives a score, as in the Kubernetes audit.

List aggregation. string_agg(distinct tag, ', ' order by tag) shows the set of versions on one line, which is what a human wants to read.

Write these as parameterized snippets in a file and reuse them. Analysts who know SQL but not YAML can edit them, and that is half the point of the approach.

Who runs it and who acts

A report needs a place to live and a person to read it. For configuration analysis, three arrangements work.

A scheduled job runs the script weekly, writes the findings to a shared place and posts a summary to the team's channel. It suits drift reports and coverage, where the audience is the platform team.

A pull request check runs on changes to YAML files and compares with the base branch: new latest tags, new containers without limits, a lowered coverage number. It suits gates and regressions, where the audience is the author.

An on-demand tool, a command in the repository's Makefile, answers "what do we run" for anyone. It suits investigations, where the audience is whoever is asking.

Ownership decides whether any of it matters. Findings assigned to a team with a due date get fixed; findings addressed to "everyone" get discussed. Keep the mapping from path to team in the repository (a CODEOWNERS file works), and let the report group by it. A final habit: delete a rule that nobody acts on. A short list of checks that people trust beats a long list they have learned to ignore.

Adding a new kind of file

When someone asks about a file type the scripts do not cover, the extension is mechanical. Open one example file and note where the entities sit (a CI workflow keeps jobs under jobs.<name>, a Helm values file keeps settings at the top level, an Argo or Tekton file keeps steps in a list). Write a loop that emits one row per entity with the attributes you need. Add a test with a small example file and the expected rows. Add the new table to the report. For a kind with a published JSON Schema, such as GitHub workflows, validate with the schema first (validating YAML before it breaks a deploy), so the loop can assume a valid shape. The marginal cost of the tenth file type is a fraction of the first, because the parse, load and report scaffolding is shared.

From a query to a report

A result nobody reads changes nothing. The pattern that works:

Pick a few numbers and track them. Distinct majors per database, containers without limits, documentation coverage. Print them at the end of a CI run and store them with the date.

Compare to the last run. A report that says "postgres tags: 4, was 5" shows progress, and one that says "containers without limits: 3, was 2" shows a regression in the pull request that caused it.

Attach an owner. Findings with an owning team (from a directory, a label or CODEOWNERS) get fixed. Add the owner as a column and group by it.

Separate gates from reports. Duplicate keys within a file, privileged containers and latest in production are gates: fail the build. Version drift and coverage are reports: show them, set a trend.

Keep exceptions visible. An allowed deviation has a reason and an expiry, in the repository.

Write the table out. copy kv to 'kv.parquet' produced a 4,296-byte Parquet file for our 333 rows; a notebook, a dashboard or a colleague can read it without your scripts.

What it costs to run

The pipeline is cheap enough to run on every commit. We timed each script over the 35-file repository, including Python start-up and the DuckDB import:

an_compose: 0.36 s, peak 81920 KB
an_versions: 0.08 s, peak 14592 KB
an_k8s: 0.30 s, peak 72680 KB
an_dupes: 0.16 s, peak 20692 KB
an_repo: 0.44 s, peak 83696 KB

Under half a second and under 90 MB each. The version scan is the fastest because it uses a regex over text and only loads YAML for the Helm files. The scripts that load DuckDB pay for the import. Scaling linearly, a repository of 3,500 files would take under a minute with the pure Python loader, and a few seconds less with libyaml. If that becomes a cost, parse files in parallel with a process pool and cache the parsed rows by file hash so that only changed files are re-read. The query step is negligible at these sizes: DuckDB answers aggregate queries over millions of rows in well under a second.

Which loader to analyze with

The parser's rules affect every number. Three choices matter.

PyYAML's safe loader is the default, fast with libyaml and follows YAML 1.1: booleans from no and on, octal from leading zeros, dates as objects. If your tools (dbt, Ansible, Rails) use the same rules, analyzing with it gives results that match what they see.

**ruamel.yaml** follows YAML 1.2 by default and rejects duplicate keys, which is how the duplicate-key scan found its two files. It is slower, and it keeps line numbers on nodes, which a report can use to link a finding to a line.

A text-level scan (a regex) ignores YAML entirely. It is fast and tolerant of files that do not parse, and it sees comments and templates as text. The version scan used it for single-string images and a structured read for the rest, which is a good combination.

The practical rule: use the loader the files' consumers use for facts about what they will read, use ruamel.yaml or yamllint for quality checks such as duplicates, and use regex only for patterns that do not need structure. When two loaders disagree about a file, you have found a file that deserves a quote or a fix.

Testing the analysis itself

An analysis script is code that other people will trust, so test it. Keep a small fixture repository with files that have known properties: a service with no healthcheck, a privileged container, a pair of files with a duplicate key, a values file with a quoted tag and one without. Run the scripts on it in CI and assert the output. When the output changes, you know whether the script or the fixture changed. Two further checks catch silent failures: compare the number of files the script reports reading with the number on disk, and fail if any file could not be parsed. A report computed from half the files looks perfectly normal and is wrong. We saw the same failure mode in front matter work, where a library returned an empty result for a file it could not read (reading front matter from Markdown files in bulk), and the defense is the same: count what you read.

Pitfalls

The files are not the system. Templates, includes, overrides and defaults change the effective configuration. Helm values need rendering; Compose files with include or overrides need docker compose config; Kustomize needs a build. Analyze the effective output.

The loader's rules apply. NO as a key becomes False, 3.10 becomes 3.1, dates become objects. Quote what is text and choose a loader deliberately.

Multi-document files. Use safe_load_all, skip None, and keep a document index so errors can point at "document 3".

Anchors. safe_load expands them, so counts reflect the effective data, which is usually what you want; a text-level scan sees only the short form.

Secrets in reports. A table of every value in every file includes every password in them. Redact by key name before writing the table anywhere shared, or exclude those paths from the flatten.

Scale. A few thousand files are fine. Beyond that, parsing time matters (large YAML files and parsing speed): use the C loader, parse in parallel, cache by file hash.

Wrong files. Vendored directories, test fixtures and generated files pollute counts. Exclude them with a pattern, in one place.

A reusable skeleton

The core is small enough to copy: walk the files, parse with safe_load_all, flatten with a generator, insert into DuckDB, run a folder of .sql files and print the results.

for p in sorted(Path("repo").rglob("*.y*ml")):
    for n, doc in enumerate(yaml.safe_load_all(p.read_text())):
        for path, typ, value in walk(doc or {}):
            rows.append((str(p), p.parent.name, n, path, typ, value))

Add entity loops beside it for the kinds you report on regularly. Keep the SQL in files so analysts can edit it without touching Python, and keep a test with a tiny repository whose expected output is known (the one used here has a privileged container, a latest tag and two duplicate-key files for exactly that purpose). When a report changes unexpectedly, you want to know whether the files changed or the script did.

When analyzing YAML as data is the wrong approach

You need enforcement. An analysis reports; an admission controller, a linter or a schema check in CI enforces. Use the analysis to decide what to enforce.

The truth is at run time. Query the cluster, the registry or the database for what is actually deployed.

There are few files. Reading ten files is faster than building a pipeline.

The files are generated. Analyze the generator's input, and fix problems there.

The question is about meaning. Whether a limit is the right limit is a human judgment; the table can tell you only that one exists.

References: the DuckDB documentation, the yq manual, the dbt manifest reference and the Kubernetes pod security standards. Convert a file to JSON for a first look with the YAML to JSON converter.