Best practices for designing data warehouses and analytical pipelines using the bronze/silver/gold medallion architecture, validations-as-code, and idempotent transforms. Use when building or modifying data pipelines, ETL/ELT jobs, dbt models, SQL warehouses, lakehouses, or any layered analytics workload (DuckDB, Snowflake, BigQuery, Postgres, Spark, etc.).
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
Best practices for designing data warehouses and analytical pipelines using the bronze/silver/gold medallion architecture, validations-as-code, and idempotent transforms. Use when building or modifying data pipelines, ETL/ELT jobs, dbt models, SQL warehouses, lakehouses, or any layered analytics workload (DuckDB, Snowflake, BigQuery, Postgres, Spark, etc.).
Data Warehousing Best Practices
These best practices are based on my (the user's) university masters course ECBS5294 — Introduction to Data Science: Working with Data at Central European University, taught by Eduardo Ariño de la Rubia. The user particularly liked the bronze → silver → gold layered division and the validations-as-code discipline, and wants those principles applied consistently.
The guidance is tool-agnostic. The user is not always using DuckDB — apply the same patterns whether the warehouse is DuckDB, Snowflake, BigQuery, Redshift, Postgres, Databricks/Spark, ClickHouse, or a dbt project on top of any of them.
Upstream source for further reference:https://github.com/earino/ECBS5294 (course repository the user inspired this skill from — check it for full notebooks, slides and worked examples).
When to Use
Apply this skill whenever the user is:
Designing or modifying a data warehouse / lakehouse / analytics database.
Writing or reviewing ETL/ELT pipelines, dbt models, Airflow DAGs, notebooks, or SQL transforms.
A pipeline step is idempotent if running it N times produces the same result as running it once.
Patterns that are idempotent:
CREATE OR REPLACE TABLE silver_x AS SELECT ...
DROP TABLE IF EXISTS silver_x; CREATE TABLE silver_x AS ...
MERGE INTO target USING source ON ... WHEN MATCHED ... WHEN NOT MATCHED ...
df.to_sql(..., if_exists='replace') or writing to a partition with overwrite semantics.
Patterns that are not idempotent (avoid unless you really mean it):
INSERT INTO ... SELECT ... without a dedup/merge step.
df.to_sql(..., if_exists='append') in a notebook that may be re-run.
Mutating bronze in place.
Modeling Cheatsheet
Tidy data first (Wickham): one variable per column, one observation per row, one observational unit per table.
Identify the grain of every table in one sentence ("one row per order line per day"). If you can't, the model is wrong.
Prefer surrogate keys (order_sk BIGINT) for joins; keep natural keys as attributes for traceability.
Use composite keys when the grain is naturally multi-column (store_id, date).
For analytics, star schemas (fact + dimensions) age well; full 3NF is rarely worth it in a warehouse.
SQL Style (warehouse-flavor)
Uppercase keywords, lowercase identifiers, one clause per line, trailing commas off.
Always alias tables in joins (o, c) and qualify every column.
Prefer LEFT JOIN + explicit WHERE right_table.id IS NULL for anti-joins (clearer than NOT IN, NULL-safe).
Use CTEs (WITH) to layer logic; avoid deep nested subqueries.
Use TRY_CAST (or the engine's safe-cast equivalent) on untrusted source data; bare CAST belongs only on already-validated silver.
Handle NULLs explicitly with IS NULL, IS NOT NULL, COALESCE. Never compare with = NULL.
Notebooks: embedding SQL
When placing multiline SQL scripts into notebooks, always put the SQL text in its own cell and make it callable from another cell. Declare the SQL as a Python multiline string using triple quotes """ followed by a newline, the SQL, a newline, and the closing """. Keep execution logic separate from the SQL declaration so queries are readable and reusable.
Example pattern (Python notebook):
Cell 1 — SQL declaration:
query = """
SELECT
user_id,
COUNT(*) AS events
FROM events
WHERE event_date >= '2026-01-01'
GROUP BY user_id;
"""
Cell 2 — Execution / use:
df = run_sql(query) # run_sql is your DB helper that accepts a SQL string
display(df)
Notes:
Use descriptive variable names like orders_sql or daily_revenue_sql.
Do not embed execution logic inside the SQL declaration cell.
This pattern works for Jupyter/Colab and keeps SQL testable, lintable, and copy-pastable into other environments.
Recommended Workflow
When asked to build or modify a pipeline:
Identify the grain of each target table. State it explicitly.
Sketch the layers: which sources land in bronze, what cleaning happens in silver, which marts in gold.
Define the PK and the validations for every silver/gold table before writing the SELECT.
Write the transform with CREATE OR REPLACE / MERGE (idempotent).
Add assertions immediately after the transform — in the same script/model.
Run end-to-end on a small sample, then on full data.
Document assumptions as inline comments where they bite (e.g. "NULL price = quote pending, excluded from revenue").
Magic numbers in filters with no comment explaining the business reason.
Data Sources Used in the Course (good for practice pipelines)
ECBS5294 deliberately uses small, deliberately messy datasets so the bronze→silver→gold
journey actually has work to do at every layer. They are excellent for prototyping a new pipeline
or testing validation snippets:
