IBM watsonx Data Lakehouse Engineer Associate cheat sheet

watsonx.data is a lakehouse built on open formats: data resides in object storage (IBM COS, AWS S3, Azure Blob) and metadata is held in a separate metastore (Hive metastore or the built-in metastore service).

Core services: the control plane (UI, API, provisioning), Presto (default SQL engine), and optional engines - Presto, Spark, and Db2 Warehouse (formerly Db2) - each added and scaled independently.

Provisioning options: IBM Cloud (SaaS, managed by IBM), AWS (BYOC-style marketplace deployment), and self-managed on OpenShift (CP4D). Instance sizing is chosen at creation; engine sizes can be changed later.

A catalog is a namespace that binds a storage location and a metastore. Multiple catalogs can point at different buckets, enabling cross-source queries from one Presto coordinator.

Storage registration requires: bucket/endpoint, credentials or an IAM trusted profile, and a catalog association. Supported sources include IBM COS, AWS S3, Azure Blob, Google Cloud Storage, and HDFS.

Hive metastore integration allows watsonx.data to reuse existing Hive tables; a separate 'metastore' service can be provisioned to serve multiple engines.

Presto workers and coordinators run in separate pods; the coordinator plans queries and workers execute. Spark engines run on Kubernetes and are used for ETL and ML workloads.

The console exposes Infrastructure Manager (view/add engines and storage), Data Manager (browse catalogs, create schemas/tables), Query Workspace (SQL editor), and Query History/Monitoring.

A 'database' in watsonx.data corresponds to a schema; tables can be native (created in watsonx.data) or external (registered from existing files/metastore).

Ingestion paths: (1) register existing object storage as a catalog, (2) use the Data Manager UI to create/load tables, (3) run Spark ingestion jobs, (4) use the ingestion API or CLI, (5) federated query via Presto connectors.

File formats supported for external tables include Parquet, ORC, Avro, CSV, JSON, and Delta (Delta Lake is supported for read and write in newer releases).

Presto connectors for federation include: PostgreSQL, MySQL, Db2, Oracle, SQL Server, MongoDB, Kafka, Elasticsearch, and Hive. Each connector is configured in the Presto catalog properties.

A connection in watsonx.data stores host, port, database, and credentials; credentials can be stored as secrets or referenced via a vault/trusted profile.

For IBM COS, use HMAC keys or IAM API keys; for AWS S3, use access key/secret or an IAM role. Endpoint URLs must match the region (e.g., s3.us-south.cloud-object-storage.appdomain.cloud).

Schema evolution: adding columns to Parquet/ORC external tables requires refreshing metadata (e.g., ALTER TABLE ... REFRESH or re-registering) so the metastore sees new columns.

Partitioned external tables use Hive-style directory layout (col=value). Partition columns must be declared in the CREATE TABLE statement to enable partition pruning.

The ingestion job API accepts source (JDBC/object store), target catalog/schema/table, format, and write mode (append/overwrite). Jobs can be scheduled or run on demand.

Data lineage and asset metadata are captured in the catalog; ingestion jobs record source-to-target mappings visible in the UI.

Presto is the default MPP SQL engine; it uses a coordinator + workers. Query parallelism is set by task.concurrency and node count; adding workers increases throughput for large scans.

Spark engines are used for ETL, ML, and complex transformations. Spark SQL and DataFrame APIs are supported; Spark can read/write the same catalogs as Presto.

Db2 Warehouse engine (if provisioned) provides a relational MPP engine for high-concurrency BI workloads; it can query data in object storage via external tables.

Optimization levers: partition pruning (filter on partition columns), predicate pushdown (Presto pushes filters to JDBC connectors), columnar formats (Parquet/ORC), and compression (Snappy/Zstd).

Join strategies in Presto: broadcast join for small tables (set join distribution to BROADCAST), partitioned join for large-large. Use EXPLAIN to inspect the plan.

Statistics: Presto relies on metastore statistics for cost-based optimization. Run ANALYZE on tables or enable automatic statistics collection to improve join ordering.

File sizing: target 128–512 MB per Parquet file; too many small files cause excessive task overhead. Use compaction (Spark or Presto CTAS) to merge small files.

Query monitoring: the Query History page shows duration, rows processed, and stage-level details. Slow queries can be diagnosed by examining the query plan and stage timings.

Caching: Presto supports file system caching and alluxio-style caching for repeated scans; results caching can be enabled for dashboards.

Resource groups in Presto can isolate workloads (e.g., ETL vs BI) by limiting concurrent queries and memory per group.

Authentication: IBM Cloud IAM for SaaS; OpenShift uses IBM Cloud Pak foundational services (Keycloak) for identity. Users log in via the console or API with an IAM token or API key.

Authorization: role-based access control (RBAC) at the instance, catalog, schema, and table level. Roles include Admin, User, and custom roles; privileges can be granted/revoked via SQL (GRANT/REVOKE).

Presto supports fine-grained access control via file-based or custom access control plugins; column-level and row-level security can be enforced through views or security policies.

Data encryption: at rest via object storage encryption (COS/S3 SSE) and in transit via TLS. Credentials should be stored in a secrets manager, not in plain text.

Governance integration: watsonx.data can integrate with IBM Knowledge Catalog (IKC) for metadata enrichment, lineage, and policy enforcement; assets can be published to the catalog.

Audit logging: administrative actions and query history are logged. In SaaS, activity can be exported to IBM Cloud Activity Tracker; self-managed uses OpenShift logging.

Monitoring: the Infrastructure Manager shows engine health (CPU, memory, pod status). Alerts can be configured for engine failures and storage thresholds.

Backup and restore: metadata (metastore) and object storage data must be backed up separately; SaaS provides automated backups of the control plane metadata.

Compliance: watsonx.data supports HIPAA, SOC2, and GDPR controls depending on deployment; data residency is determined by the region chosen at provisioning.