Files Plus a Catalog Equals a Table: Lake, Warehouse, Lakehouse

Level 6: Level 6: Cloud & Data Engineering Foundationseasy24 mindata catalogHive metastore / Gluedata lake vs warehouse vs lakehouseschema-on-readfiltering

How a pile of files in a bucket becomes a queryable table (the Hive metastore / Glue catalog), the lake vs warehouse vs lakehouse split, and auditing the catalog with SQL.

Files in a bucket are not a table, yet

A folder of Parquet files in S3, say s3://lake/raw/events/, is just bytes. Nothing there knows it is an "events table," what columns it has, or their types. Two more pieces turn a pile of files into something you can run SQL against.

The first is a catalog (also called a metastore). It records, for each table, the column names and types, the partition columns, the file format, and the S3 location of the files. Add a catalog entry and the same pile of files becomes a queryable table. The two names to know are the Hive metastore (the original open-source standard) and the AWS Glue Data Catalog (AWS's managed, Hive-compatible version). A Glue crawler can even infer the schema from the files and fill the catalog in for you.

The second is a query engine that reads the catalog, then reads the files: Amazon Athena (serverless SQL), Apache Spark, or Trino. The engine looks up the table to learn "this prefix of Parquet files has these columns," then scans only the files it needs.

Order of evaluation

Files in S3raw Parquet bytes
Catalog entrycolumns, types, location
Query engineAthena / Spark / Trino
SQL tableyou can now SELECT from it

A pile of files plus a catalog entry becomes a table an engine can read. Storage, catalog, and compute stay separate.

So the defining equation of the lake is: a pile of files in a bucket, plus a catalog entry that gives them a schema, equals a table.

Lake, warehouse, lakehouse

Those files-plus-catalog tables make up a data lake: raw data in its native format on cheap object storage, structured only when you query it (schema-on-read). Cheap and flexible, but on its own a lake has no transactions and no easy row-level updates.

A data warehouse is the other end: a curated, structured, columnar, SQL-first store that you load conformed data into (schema-on-write). Amazon Redshift, Snowflake, and Google BigQuery are the three names to know. Warehouses are fast for BI and governed, at the cost of loading into their system.

A lakehouse is the newer middle: it layers warehouse features (ACID transactions, schema enforcement, time travel) onto open files on object storage, using an open table format. Apache Iceberg, Delta Lake, and Apache Hudi are the ones to recognize. The point is one copy of the data on the lake, with warehouse-grade guarantees on top.

Auditing the catalog with SQL

The catalog is itself a set of tables you can query, and auditing it is real work: which tables are still stored as slow CSV instead of Parquet, which tables have no partitions and will full-scan, which database owns the most tables. The exercises here query a stand-in glue_catalog to answer questions like these.

Common mistake: believing the files are the table. A folder of Parquet in a bucket is just bytes until a catalog entry gives it a schema and a location, and a query engine reads that catalog first.

Interview nuance: "how would you query a bunch of Parquet files in S3 with SQL, and where does the schema come from" is a direct junior question. The answer is Athena (or Spark or Trino) reading the file locations and column types from the Glue Data Catalog or a Hive metastore. "A table equals files plus a catalog entry" is the sentence that lands it.

On a real platform this differs. The real Glue Data Catalog exposes this metadata through an API and through queryable information_schema views, with far more columns (serde, compression, per-column stats). The glue_catalog table here keeps the few columns that carry the lesson: database, table, location, format, and partition keys.

Sample data for this example

CREATE TABLE glue_catalog (
  db_name        TEXT,
  table_name     TEXT,
  location       TEXT,     -- the S3 prefix the files live under
  file_format    TEXT,     -- parquet | json | csv
  partition_keys TEXT      -- comma-separated, '' when the table is unpartitioned
);
INSERT INTO glue_catalog (db_name, table_name, location, file_format, partition_keys) VALUES
  ('analytics', 'events',       's3://lake/raw/events/',   'parquet', 'dt'),
  ('analytics', 'daily_active', 's3://lake/curated/dau/',  'parquet', 'dt'),
  ('analytics', 'app_logs',     's3://lake/raw/logs/',     'json',    ''),
  ('finance',   'invoices',     's3://lake/raw/invoices/', 'csv',     ''),
  ('finance',   'fx_rates',     's3://lake/ref/fx/',       'parquet', ''),
  ('ml',        'features',     's3://lake/ml/features/',  'parquet', 'dt');

Worked example (SQL)

-- The catalog: each table's format and whether it declares partition keys.
SELECT db_name, table_name, file_format,
       CASE WHEN partition_keys = '' THEN 'no' ELSE 'yes' END AS partitioned
FROM glue_catalog
ORDER BY db_name, table_name;

Apply

Your turn

The task this lesson builds to.

Write a query that returns every catalog table not stored as Parquet, as (db_name, table_name, file_format), ordered by db_name then table_name, over glue_catalog(db_name, table_name, location, file_format, partition_keys).

These are the tables a migration would convert to Parquet for cheaper, faster scans. Keep only rows whose file_format is not parquet.

2 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, plus 4 bonus drills.

Write a query that returns every Parquet table that declares no partition keys, as (db_name, table_name), ordered by db_name then table_name, over the same glue_catalog table.

A Parquet table with an empty partition_keys gets scanned in full by every query, so these are the tables to consider partitioning. Keep only rows where file_format is parquet and partition_keys is the empty string.

2 hints and 1 automated check are waiting in the workspace.