> For the complete documentation index, see [llms.txt](https://docs.quilt.bio/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.quilt.bio/quilt-platform/advanced/iceberg-tables.md). # Iceberg Tables > **NOTE:** This feature requires Quilt Platform version 1.70.0 or higher. Quilt automatically maintains Apache Iceberg tables that provide high-efficiency, externally queryable access to package information. This is particularly useful for buckets that contain thousands of packages or for queries that span multiple buckets. You can query package revisions, tags, file entries, and metadata using Amazon Athena or external data warehouses that support Iceberg (e.g., Databricks, Snowflake). ## Tables > **NOTE:** The per-bucket tables are deprecated: the [stack-wide package tables](#stack-wide-package-tables) replace them. For each bucket registered with Quilt, four per-bucket tables are maintained in the Iceberg Glue database, whose name is exposed as the `IcebergDatabaseName` output in your CloudFormation stack: * `{bucket}_package_revision` — package revisions with timestamps * `{bucket}_package_tag` — named package tags (currently only `latest`) * `{bucket}_package_manifest` — package-level metadata and commit messages * `{bucket}_package_entry` — individual file entries within packages The bucket is encoded in the table name, so the tables do not carry a `bucket` column. Because S3 bucket names can contain hyphens, the resulting Glue table names (e.g. `my-bucket_package_tag`) must be double-quoted in Athena SQL, as shown in the examples below. Every Quilt role automatically receives Athena read access to the per-bucket tables for the buckets it can read — managed users are scoped to their readable buckets via the registry-applied session policy; non-managed roles have stack-wide access by design. > **Admin note — Lake Formation.** If AWS Lake Formation enforcement is enabled on your **account** (data lake), you must set the `EnableLakeFormationGrants` CloudFormation parameter to `true` so the stack emits the `PrincipalPermissions` (Lake Formation) grants its service roles need to reach the data lake. **This is opt-in and off by default:** if your account enforces Lake Formation but the stack has *not* enabled these grants, Lake Formation denies the stack and per-bucket Iceberg access (among other things) breaks. Leave the parameter off only on accounts that do not enforce Lake Formation. ## Stack-wide package tables Quilt also keeps one set of package tables for all of a stack's buckets, with the bucket as a column, so one query spans every bucket a role reads. AWS Lake Formation governs the tables. ### The database and its tables The tables live in a Glue database of their own, named `quilt__` followed by the stack's name in lower case, with every character outside `a-z` and `0-9` replaced by `_`: a stack named `My-Stack` gets `quilt__my_stack`. The database's location is the `package-tables/` prefix of the stack's service bucket. The database holds four tables, `package_revision`, `package_tag`, `package_manifest` and `package_entry`, with the columns of the per-bucket tables of the same names and one more, `registry`: the bucket's URI, such as `s3://my-bucket`. For example, the latest revision of every package in every bucket your role reads, but for some [packages whose name begins with an underscore in either part](#updating-an-existing-stack): ```sql SELECT registry, pkg_name, top_hash FROM "quilt__my_stack"."package_tag" WHERE tag_name = 'latest' ORDER BY registry, pkg_name ``` ### Who reads what * A user of a managed role reads the rows of the buckets the role reads, and no others. * A role reads at most 100 buckets' rows. Quilt refuses an admin's edit that would give a role more, and fails an edit whose Lake Formation grants it cannot write, leaving the role as it was. To read more buckets, split them across roles. * Users of non-managed roles, the built-in ones such as `ReadQuiltBucket` and those added by ARN, read nothing of the tables until a later release. * For a managed role's users, the **Database** dropdown in the catalog's **Queries** tab lists the stack's database. ### Reading from your own principals An IAM principal of your own reads the tables once your Lake Formation administrator grants it `SELECT` on them, and `DESCRIBE` on the database where the database lacks an `IAM_ALLOWED_PRINCIPALS` permission. Its own IAM policies must also allow it Athena, the Glue reads on the database and its tables, and `lakeformation:GetDataAccess`. A principal with S3 read access to the `package-tables/` prefix reads every bucket's rows: Lake Formation's row filters bind Athena, not the tables' files. ### Reserving the `quilt:stack` tag key Lake Formation tells a role's users apart by tags: the stack tags its `ManagedUserRole` with `quilt:stack`, set to the stack's name, and Quilt tags each session on that role with the user's Quilt role. A principal can read any role's rows if it can set `quilt:stack` and a `quilt:role:` key on itself or on a session, whether from inside your account, from another account through a role whose trust policy allows `sts:TagSession`, or through an IAM Identity Center attribute mapping; if it can tag `ManagedUserRole` or edit its trust policy; or if it can act as the registry's role. Reserve the key to each stack's deployer, the principal CloudFormation deploys it as, with a service control policy or with the same document as an IAM policy on your account's principals: ```json { "Version": "2012-10-17", "Statement": [ { "Sid": "ReserveQuiltStackTag", "Effect": "Deny", "Action": [ "iam:CreateRole", "iam:TagRole", "iam:CreateUser", "iam:TagUser", "sts:TagSession" ], "Resource": "*", "Condition": { "ForAnyValue:StringEqualsIgnoreCase": { "aws:TagKeys": "quilt:stack" }, "StringNotEquals": { "aws:PrincipalArn": [ "" ] } } }, { "Sid": "ReserveQuiltManagedRoleTagsAndTrust", "Effect": "Deny", "Action": [ "iam:TagRole", "iam:UpdateAssumeRolePolicy" ], "Resource": "arn:aws:iam:::role/quilt//*", "Condition": { "StringNotEquals": { "aws:PrincipalArn": [ "" ] } } } ] } ``` Replace `` with your AWS account ID, `` with the stack's name as given (case included), `` with the deployer's ARN (the stack's service role, where it has one), and, in AWS GovCloud (US), the `aws` partition in each ARN with `aws-us-gov`. The second statement names `ManagedUserRole` by the stack's IAM path, since CloudFormation generates the role's name, and so reserves tagging and trust-policy edits of every role under that path, not `ManagedUserRole` alone, to the deployer. For each further Quilt stack in the account, add its deployer's ARN to both `aws:PrincipalArn` lists, and make the second statement's `Resource` a list holding each stack's `arn:aws:iam:::role/quilt//*`. As an IAM policy on your principals, the document binds neither a principal created after you attach it nor one that can detach it. CloudFormation uses a stack's service role for every operation on the stack, so the deployer's exemption reaches any principal allowed to update the stack or execute its change sets. A service control policy binds the principals of your organization's member accounts alone, not a caller from outside your organization or a federated one. Beside it, attach a resource control policy that denies `sts:TagSession` under the same `aws:TagKeys` condition: it binds your account's roles whoever calls them, so it covers both. Neither binds your organization's management account, and both need AWS Organizations with all features enabled: without them, the trust-policy rule below is the whole control for callers from outside your account. No role's trust policy may let SAML or OIDC federation, or a principal of another account, pass `quilt:stack` as a session tag, and no IAM Identity Center attribute mapping may produce `quilt:stack`. The session tags Quilt sets, `quilt:role:` and `quilt:user`, need no reservation: they match no grant without `quilt:stack`, and Quilt and the roles assumed from its sessions need `sts:TagSession` with them. ### Lake Formation enforcement and reachability On an account that enforces Lake Formation, set the `EnableLakeFormationGrants` parameter to `Enabled`, which requires the stack's deployer to be a Lake Formation data lake administrator. Quilt takes its rights on the stack's database from those grants: until the parameter is `Enabled`, the registry's migration logs the tables it cannot create, the tables' writer sends its batches to its dead-letter queue, and their maintenance fails on its schedule. Setting the parameter re-runs the migration. Quilt's registry calls Lake Formation at every deploy and whenever an admin changes a role's buckets, as it calls Glue, Athena, IAM and STS. Lake Formation must be reachable from the registry's VPC, through its egress or an interface endpoint, and allowed by your service control policies. Where the registry cannot call it, admins' edits of policies and managed roles fail. The registry's migration, which runs at each release's update, sets who reads what on the tables at each run with Lake Formation reachable. Until its first such run, no user can read the tables. After it, the registry's failure to call Lake Formation takes no reads from users, but no role reads the rows of a bucket first added to the stack while that failure lasts, until the next such run. ### Updating an existing stack **The fill.** The update creates the database and its tables, and the registry's migration task fills them from every registered bucket. The update does not wait for the fill, and the tables lack rows until it ends. If the fill stops short, the next release's update completes it. **Packages the fill leaves out.** At the update, and whenever the migration runs again or an admin adds a bucket to the stack, the fill leaves the revisions and tags of packages whose name begins with an underscore in either part, such as `_team/data` or `team/_data`, out of `package_revision` and `package_tag`, though their manifests and entries are in `package_manifest` and `package_entry`. The tables' writer records such a package's revisions pushed after the update or the bucket's addition, and the `latest` tag they move, but none pushed before. The bucket's per-bucket tables hold such packages' revisions and tags pushed since those tables' creation. **Reserved executions.** The tables' writer reserves 2 concurrent Lambda executions and their maintenance 1, on top of the per-bucket tables' writer's 3: the update fails on an account that cannot reserve the new 3 above Lambda's unreserved-concurrency minimum. **The database's name.** The update fails at the database's creation, and rolls back, where a Glue database of that name already exists in the account and region, as for the second of two stacks whose names differ only in case. **The service role.** Before the update, bring a CloudFormation service role scoped like the installation guide's [example service role](https://github.com/quiltdata/deployment/tree/main/docs/cfn-service-role.yaml) up to date with the example. The update needs the four Lambda event invoke configuration actions, `lambda:PutFunctionEventInvokeConfig`, `lambda:GetFunctionEventInvokeConfig`, `lambda:UpdateFunctionEventInvokeConfig` and `lambda:DeleteFunctionEventInvokeConfig`, which every stack takes, and the reads that creating the tables' Glue database, functions and log groups takes; the example carries them all. **Roles past 100 buckets.** A managed role that reads more than 100 buckets at the update reads nothing of the tables until its buckets are split across roles. ### Rolling back, downgrading or deleting a stack A rollback of the update that added the tables, a downgrade past the release that brought them and the stack's deletion each remove the tables, but leave the `package-tables/` prefix registered with Lake Formation and its files in the service bucket. A Lake Formation data lake administrator in your account clears them by hand, in one of two forms. **Where the tables stand**, such as before a downgrade or deletion: 1. On each of the four tables, delete the data cells filters named `quilt-bucket-`. 2. Revoke any grant on the database to `IAMPrincipals` under a condition on `quilt:stack`, the tables' grants to the stack's `IcebergSetLambdaRole` and `IcebergSetMaintenanceRole`, and any grant your account made on the tables or the database to a principal of its own. Leave the grants of the stack's `AmazonECSTaskExecutionRole`, the registry's own. The tables' grants to `IAMPrincipals` under that condition are on the filters, and went with them in step 1. 3. Deregister the prefix from Lake Formation, leaving its role, `StackDatabaseRegistrationRole`, to the stack. 4. Grant `IAM_ALLOWED_PRINCIPALS` `ALL` back on each table, only while the prefix is deregistered: on a registered location it would hand every principal with Glue read access the tables whole. 5. Downgrade or delete the stack, if you mean to. 6. Once the tables are gone, empty the prefix, if the service bucket still stands. **Where the tables are gone**, including after a failed update's rollback, once its migration task has stopped and before you retry the update: 1. Deregister the prefix, if it is registered: with its role gone, the registration vends no credentials. 2. Empty the prefix, if the service bucket still stands: retrying the update recreates the tables over any files left there. A rollback, a downgrade or a deletion that removes the tables before the first form has run takes their filters and grants with them. On an account that does not enforce Lake Formation, deleting the stack deletes the tables' database too, with or without the first form. Quilt has not verified whether a rollback or a downgrade does, or a deletion on an account that enforces Lake Formation. ### The per-bucket tables are deprecated The per-bucket tables keep working, and a later release removes them. The stack-wide package tables replace them, with the bucket as their `registry` column. ## Finding the tables in the Queries tab As of Quilt Platform 1.71.0, managed users can select the Iceberg package-index database directly from the **Database** dropdown in the catalog's **Queries** tab, instead of typing its fully-qualified name. On a stack with the [stack-wide package tables](#stack-wide-package-tables), the dropdown also lists their database. ## Example: Get entries and metadata for the latest version of a package ```sql SELECT e.logical_key, e.physical_key, e.size, e.metadata FROM "my-bucket_package_tag" t JOIN "my-bucket_package_entry" e ON t.top_hash = e.top_hash WHERE t.pkg_name = 'analytics/results' AND t.tag_name = 'latest' ``` ## Example: Find latest packages matching specific metadata ```sql SELECT t.pkg_name, t.tag_name, m.metadata FROM "my-bucket_package_tag" t JOIN "my-bucket_package_manifest" m ON t.top_hash = m.top_hash WHERE t.tag_name = 'latest' AND json_extract_scalar(m.metadata, '$.experiment_id') = 'EXP-123' AND json_extract_scalar(m.metadata, '$.status') = 'complete' ``` ## Cross-bucket queries To search across multiple buckets, `UNION ALL` the per-bucket tables explicitly: ```sql SELECT 'bucket-a' AS bucket, pkg_name, tag_name, top_hash FROM "bucket-a_package_tag" WHERE tag_name = 'latest' UNION ALL SELECT 'bucket-b' AS bucket, pkg_name, tag_name, top_hash FROM "bucket-b_package_tag" WHERE tag_name = 'latest' ``` The [stack-wide package tables](#stack-wide-package-tables) span buckets without a `UNION ALL`. ## See also * [Query](/quilt-platform/query.md): Use the Catalog's Queries tab * [Athena](/quilt-platform/advanced/athena.md): Query package manifests using AWS Athena * [Tabulator](/quilt-platform-administration/advanced/tabulator.md): Query tabular data within packages --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://docs.quilt.bio/quilt-platform/advanced/iceberg-tables.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.