This project delivers a vendor-agnostic, open-source Modern Data Lakehouse and Data Federation architecture powered by PostgreSQL, MinIO, Unity Catalog OSS, Trino, and Keycloak.
It unifies operational relational databases (OLTP) and analytical object storage data (Apache Iceberg / Parquet) in a single distributed SQL query engine without requiring costly and complex ETL data duplication pipelines. Furthermore, it enforces end-to-end Role-Based Access Control (RBAC), Row-Level Security (RLS), and Dynamic Column Masking across federated datasets.
1. Definition and Scope
In legacy data architectures, integrating operational systems with analytical storage requires fragile, delayed, and expensive ETL pipelines. This platform provides:
Zero ETL Duplication: In-place and in-memory federated querying across transactional and analytical stores using a distributed SQL engine.
Open Data Standards: Prevention of vendor lock-in through the Apache Iceberg open table format and the Unity Catalog open metadata specification.
Centralized Identity & Access Management: Seamless authentication and role-based token delegation via Keycloak using OpenID Connect (OIDC) and OAuth 2.0.
Zero-Trust Data Governance: Enforcing fine-grained access control (table-level RBAC, row-level filtering, and column-level masking) natively within the query engine execution plan.
2. Overall Architecture
The diagram below illustrates the service topology and the complete lifecycle of a federated SQL query:
Press enter or click to view image in full size
Layer Roles & Responsibilities:
Identity & Access Management (Keycloak): Manages user authentication, group memberships, and platform roles (admin, data-engineer, data-analyst).
Distributed Query & Security Engine (Trino): Enforces System Access Control policies (rules.json), analyzes and optimizes query execution plans, and executes high-performance in-memory joins across diverse data sources.
Catalog & Governance Metastore (Unity Catalog OSS): Implements the open Iceberg REST Catalog protocol to serve table schemas, snapshots, and storage locations to Trino while storing metadata in PostgreSQL.
Operational Relational Store (PostgreSQL): Stores live transactional business tables (users, salaries) as well as internal state databases for Unity Catalog and Keycloak.
Analytical Object Storage (MinIO): High-performance, S3-compatible local object store hosting Apache Iceberg Parquet files for analytical datasets such as clickstream and bonuses.
3. Tools and Methods Used
Kubernetes (K8s): Container orchestration providing network isolation and scalability across the lakehouse namespace.
Trino Distributed SQL Engine (v444): Massively parallel processing (MPP) query engine executing relational pushdowns and cross-catalog federated joins in memory.
Unity Catalog OSS (v0.6.0): Multi-engine data and AI governance platform implementing the open Iceberg REST Catalog specification.
This section guides you through seeding relational operational data, verifying catalog connectivity, executing cross-catalog federation queries, and verifying fine-grained RBAC, RLS, and column masking directly from the command line.
Phase A: Seed Operational Tables in PostgreSQL
Connect directly to the PostgreSQL pod to seed both the general users table and the sensitive salaries table in operasyonel_db:
kubectl exec -i -n lakehouse deployment/postgres -- psql -U postgres -d operasyonel_db << 'EOF' -- 1. General Operational Users Table DROP TABLE IF EXISTS public.users CASCADE; CREATE TABLE public.users ( user_id VARCHAR(50) PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(255) NOT NULL, status VARCHAR(20) NOT NULL );
The complete Lakehouse seeding, cross-catalog federation query execution, and analytical visualization can be executed interactively via JupyterLab using the bundled notebook [lakehouse_quickstart.ipynb]
To launch JupyterLab from the workspace root:
jupyter lab
Below is the complete, cell-by-cell walkthrough, Python source code, and exact execution outputs from lakehouse_quickstart.ipynb:
Prerequisites: Ensure Kubernetes Port-Forwarding is Active
Make sure the following services are forwarded from your Kubernetes cluster in separate terminals:
import os import sys import uuid import subprocess from datetime import datetime, timezone
import requests import pandas as pd import pyarrow as pa from pyiceberg.catalog.sql import SqlCatalog from pyiceberg.schema import Schema from pyiceberg.types import NestedField, StringType, TimestamptzType, DoubleType, IntegerType from minio import Minio from trino.dbapi import connect import matplotlib.pyplot as plt
print("All libraries imported successfully!")
Cell 1 Output:
All libraries imported successfully!
Cell 2: Create Catalog and Schemas via Unity Catalog REST API
We use the REST API of Unity Catalog (http://localhost:8083) to register the catalog and schemas:
click_arrow = pa.Table.from_arrays( [ pa.array([r[0] for r in click_data], type=pa.string()), pa.array([r[1] for r in click_data], type=pa.string()), pa.array([r[2] for r in click_data], type=pa.string()), pa.array([r[3] for r in click_data], type=pa.string()), pa.array([r[4] for r in click_data], type=pa.timestamp("us", tz="UTC")), ], schema=click_table.schema().as_arrow(), ) click_table.append(click_arrow) print("Clickstream table created in MinIO:", click_table.metadata_location)
bonus_arrow = pa.Table.from_arrays( [ pa.array([r[0] for r in bonus_data], type=pa.string()), pa.array([r[1] for r in bonus_data], type=pa.float64()), pa.array([r[2] for r in bonus_data], type=pa.float64()), pa.array([r[3] for r in bonus_data], type=pa.int32()), ], schema=bonus_table.schema().as_arrow(), ) bonus_table.append(bonus_arrow) print("Bonuses table created in MinIO:", bonus_table.metadata_location)
Cell 3 Output:
Clickstream table created in MinIO: s3://warehouse/analytics_schema/clickstream/metadata/00001-60a7d132-249c-4f84-ab95-b55ac03c5e20.metadata.json Bonuses table created in MinIO: s3://warehouse/finance_schema/bonuses/metadata/00001-c6b37c52-cb2a-462f-8aa5-f364235412c5.metadata.json
Cell 4: Register Tables in Unity Catalog
We copy the generated Iceberg metadata manifests to Unity Catalog and register the tables in the unity_catalog PostgreSQL metastore:
# 3. Register in PostgreSQL unity_catalog database psql_reg = f""" DO $$ DECLARE v_catalog_id uuid; v_analytics_id uuid; v_finance_id uuid; BEGIN SELECT id INTO v_catalog_id FROM uc_catalogs WHERE name = 'unity'; SELECT id INTO v_analytics_id FROM uc_schemas WHERE name = 'analytics_schema' AND catalog_id = v_catalog_id; SELECT id INTO v_finance_id FROM uc_schemas WHERE name = 'finance_schema' AND catalog_id = v_catalog_id;
-- uc_tables clickstream DELETE FROM uc_tables WHERE name = 'clickstream' AND schema_id = v_analytics_id; INSERT INTO uc_tables ( id, name, column_count, data_source_format, schema_id, type, uniform_iceberg_metadata_location, url, created_at, updated_at ) VALUES ( gen_random_uuid(), 'clickstream', 0, 'DELTA', v_analytics_id, 'EXTERNAL', 'file://{remote_click_file}', 's3://warehouse/analytics_schema/clickstream', NOW(), NOW() );
-- uc_tables bonuses DELETE FROM uc_tables WHERE name = 'bonuses' AND schema_id = v_finance_id; INSERT INTO uc_tables ( id, name, column_count, data_source_format, schema_id, type, uniform_iceberg_metadata_location, url, created_at, updated_at ) VALUES ( gen_random_uuid(), 'bonuses', 0, 'DELTA', v_finance_id, 'EXTERNAL', 'file://{remote_bonus_file}', 's3://warehouse/finance_schema/bonuses', NOW(), NOW() ); END $$; """
Cell 5: Federated Cross-Catalog SQL with Trino & Pandas
Query Trino across PostgreSQL (postgresql.public.users) and Unity Catalog/MinIO (unity.analytics_schema.clickstream) in a single query:
# Helper function to execute query and format as Pandas DataFrame def run_query(sql, conn): cur = conn.cursor() cur.execute(sql.strip().rstrip(";")) rows = cur.fetchall() cols = [d[0] for d in cur.description] return pd.DataFrame(rows, columns=cols)
query = """ SELECT u.user_id, u.first_name, u.last_name, COUNT(c.event_id) AS total_clicks, MAX(c.event_timestamp) AS last_seen_date FROM postgresql.public.users u JOIN unity.analytics_schema.clickstream c ON u.user_id = c.user_id WHERE u.status = 'ACTIVE' GROUP BY u.user_id, u.first_name, u.last_name ORDER BY total_clicks DESC """
Joining PostgreSQL operational salaries with object storage bonuses:
payroll_query = """ SELECT s.emp_id, s.employee_name, s.department, s.base_salary, b.annual_bonus, (s.base_salary + b.annual_bonus) AS total_compensation, b.performance_score FROM postgresql.public.salaries s JOIN unity.finance_schema.bonuses b ON s.emp_id = b.emp_id ORDER BY total_compensation DESC """
Execute a federated query joining transactional data (postgresql.public.users) with analytical event data resolved by Unity Catalog on MinIO object storage (unity.analytics_schema.clickstream):
kubectl exec -n lakehouse deployment/trino -- trino --execute " SELECT u.user_id, u.first_name, u.last_name, COUNT(c.event_id) AS total_clicks, MAX(c.event_timestamp) AS last_seen_date FROM postgresql.public.users u JOIN unity.analytics_schema.clickstream c ON u.user_id = c.user_id WHERE u.status = 'ACTIVE' GROUP BY u.user_id, u.first_name, u.last_name ORDER BY total_clicks DESC LIMIT 10; "
The security rules mounted inside Trino at /etc/trino/rules.json enforce the following policies:
Get Tuncay YAYLALI’s stories in your inbox
Join Medium for free to get updates from this writer.
— admin.* User: Full administrative privileges across all catalogs and tables (SELECT, INSERT, DELETE, UPDATE, OWNERSHIP).
— analyst.* User:
On postgresql.public.salaries:
Row-Level Security (RLS): "filter": "department = 'Engineering'" (The analyst can only see employees in Engineering).
Column Masking:
employee_name: "mask": "CAST(concat(substr(employee_name, 1, 2), '****') AS varchar(100))" (Only the first two characters remain visible).
base_salary: "mask": "CAST(0.00 AS decimal(10, 2))" (The base salary is masked to 0.00).
On unity.finance_schema.bonuses:
Column Restriction: annual_bonus column has "allow": false (Direct access to this column is denied).
— General Fallback (.*): Non-sensitive analytics tables (users, clickstream) remain accessible with SELECT permission for all authenticated identities.
Phase F: Live Security Verification (Admin vs Analyst)
Execute the following verification queries to observe how Trino enforces security policies:
Test 1: Admin Role Running the Sensitive Payroll Query (Full Access)
The admin joins relational salaries (salaries) with object storage bonuses (bonuses) to calculate total compensation:
kubectl exec -n lakehouse deployment/trino -- trino --user admin --execute " SELECT s.emp_id, s.employee_name, s.department, s.base_salary, b.annual_bonus, (s.base_salary + b.annual_bonus) AS total_compensation, b.performance_score FROM postgresql.public.salaries s JOIN unity.finance_schema.bonuses b ON s.emp_id = b.emp_id ORDER BY total_compensation DESC; "
Admin Result (Success — All 5 employees visible unmasked):
Zero Licensing Costs: Built entirely on open-source technologies (Trino, Unity Catalog, MinIO, PostgreSQL, Keycloak) to provide enterprise-grade data lakehouse capabilities.
Cross-Catalog Data Federation: Unifying transactional OLTP data (PostgreSQL) with scalable analytical lakehouse tables (MinIO Iceberg Parquet) in memory with zero ETL duplication.
End-to-End Zero-Trust Data Governance:
Table-level access control (RBAC),
Row-level filtering (RLS — restricting visibility to department-specific rows),
Dynamic column data masking (Ca****, 0.00),
Column-level access restrictions (allow: false)
verified in production-like scenarios on Kubernetes, providing a complete blueprint for enterprise data compliance (GDPR/KVKK) and federated analytics.
