Day 09 of SQL for Data Engineers: Databases, Schemas & Namespaces

Part of Week 2: Physical Design & Storage

In today edition we’ll organise your world: environments, databases/schemas, and namespaces. You’ll learn clean naming conventions, how to fully qualify objects, how to set defaults safely, and how to apply least-privilege grants so your Medallion layout stays tidy and secure.

TL;DR

• Use separate environments (dev/test/prod) and clear namespaces (e.g., analytics.bronze.orders_raw).

• Prefer fully qualified names (db.schema.table) in pipeline SQL; avoid accidental cross-schema access.

• Create roles → grant to roles → grant roles to users (not direct user grants).

---

The Patterns

Keep dev/test/prod separate to protect prod data and support safe iteration. Map Medallion layers to schemas.

• Database (or Catalog): analytics (business/warehouse)

• Schemas: bronze, silver, gold, ops, ref

• Tables: analytics.bronze.orders_raw, analytics.silver.orders_silver, analytics.gold.dim_customer

Readers can guess purpose from the name; pipelines target specific layers by schema.

Naming conventions you can scale

Consistent names avoid collisions and make grep/search easy.

• snake_case for identifiers: order_items_silver, dim_customer.

• Prefixes for data type (optional): dim_, fct_, map_ (reference/lookup), v_ (view).

• Stable suffixes for layers: _raw, _silver.

analytics.bronze.orders_raw
analytics.silver.orders_silver
analytics.gold.fct_order
analytics.ref.status_map
analytics.ops.high_watermark

Create databases/schemas idempotently

CREATE DATABASE analytics;                       -- DuckDB: implicit
CREATE SCHEMA IF NOT EXISTS analytics.bronze;
CREATE SCHEMA IF NOT EXISTS analytics.silver;
CREATE SCHEMA IF NOT EXISTS analytics.gold;
CREATE SCHEMA IF NOT EXISTS analytics.ops;
CREATE SCHEMA IF NOT EXISTS analytics.ref;

CREATE CATALOG IF NOT EXISTS analytics;         -- Spark: UnityCatalog
USE CATALOG analytics;
CREATE SCHEMA IF NOT EXISTS bronze;
CREATE SCHEMA IF NOT EXISTS silver;
CREATE SCHEMA IF NOT EXISTS gold;
CREATE SCHEMA IF NOT EXISTS ops;
CREATE SCHEMA IF NOT EXISTS ref;

Fully-qualified names & safe defaults

Relying on defaults (public, dbo, “current schema”) can route queries to the wrong place. In pipeline SQL, prefer fully-qualified names.

-- Preferred in pipelines
SELECT * FROM analytics.silver.orders_silver WHERE order_month = DATE '2025-07-01';

-- Session defaults for interactive work (but still qualify in code)
-- Postgres/DuckDB:
SET search_path TO analytics.silver, analytics.ref;
-- SQL Server (per user default schema):
ALTER USER [jane] WITH DEFAULT_SCHEMA = silver;
-- Spark:
USE CATALOG analytics; USE SCHEMA silver;

Roles & least-privilege grants

Grant permissions to roles, then assign roles to users/service principals. Separate writer and reader access per layer.

-- Roles
CREATE ROLE de_writer;
CREATE ROLE bi_reader;

-- Object privileges (schema → tables/views)
GRANT USAGE ON SCHEMA analytics.silver TO de_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA analytics.silver TO de_writer;

GRANT USAGE ON SCHEMA analytics.gold TO bi_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.gold TO bi_reader;

-- Future objects too
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics.silver GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO de_writer;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics.gold   GRANT SELECT ON TABLES TO bi_reader;

-- Assign to people/principals
GRANT de_writer TO "svc_etl";
GRANT bi_reader TO "reporting_group";

-- SPARK UNITY CATALOG
GRANT USAGE ON CATALOG analytics TO `account users`;
GRANT USAGE ON SCHEMA analytics.silver TO `data-engineering`;
GRANT SELECT, MODIFY ON SCHEMA analytics.silver TO `data-engineering`;

GRANT USAGE ON SCHEMA analytics.gold TO `bi-analysts`;
GRANT SELECT ON SCHEMA analytics.gold TO `bi-analysts`;

Multi-tenant or multi-domain layouts

If you support multiple domains (e.g., retail, finance) or tenants (clients/brands), encode that in the namespace to isolate data.

• Domain-first: analytics.retail.bronze.orders_raw (requires 3-level: catalog → schema → table; in Postgres, consider multiple databases or retail_bronze schema).

• Tenant-first: analytics.bronze_jd, analytics.bronze_footpatrol (simple but flatter).

• Unity Catalog: catalog=sales, schema=bronze_jd.

---

Data Engineering Wisdom

• Document the namespace: a one-pager with examples is gold in PRs.

• Lock down Bronze writes to ETL roles; let BI read Gold only.

• Use default privileges (or Unity Catalog grants) so new tables inherit correct permissions automatically.

• Fully qualify objects in migration scripts and pipeline SQL.

• Add CI checks that reject unqualified table references in PRs.

---

---

Mini-exercises (5–10 minutes)

1. Create the layout: Provision schemas bronze/silver/gold/ops/ref in your engine.

   • Check: List schemas and verify they exist under your database/catalog.

2. Grant roles: Create de_writer (write to silver), bi_reader (read gold). Assign to a test user.

   • Check: As the test user, verify permissions work and fail where expected.

3. Qualify a query: Rewrite a prior query to fully qualify all object names.

   • Check: The query should run regardless of session defaults.

---

What’s Next?

Day 10: Data Types, Keys & Identities

We’ll choose the right types (e.g., DECIMAL for money), discuss surrogate vs natural keys, when to use IDENTITY/SEQUENCES/GENERATED columns, and how type choices impact performance and storage.

So as per usual, see you for the next one and Happy Coding!