create-table

Create Table Skill

Help you integrate existing SQL into a layer of The Nomad Data Stack.

Project Architecture

This project uses the Airflow + DuckDB + MinIO architecture:

• Data layering:raw → ods → dwd → dim → ads
• Storage format: Parquet + Hive partitioning (dt=YYYY-MM-DD)
• Query engine: DuckDB views
• Scheduling: Apache Airflow

⚠️ Table naming convention (must be followed)

All table names must use one of the following suffixesto ensure consistency and maintainability of the data stack:

Suffix	Type	Description	Example
_full	Full snapshot table	Fully overwritten each time, stores the current complete data snapshot	dim_etf_universe_whitelist_full
_inc	Incremental table	Stores only incremental change data	dwd_stock_price_inc
_zip	Slowly changing dimension table	Records historical state changes of data, supports time travel	dim_customer_zip

Naming rules：

• Format:{layer}_{业务描述}_{类型后缀}
• Example:
  • ✅ dim_trade_calendar_full (DIM layer trading calendar full table)
  • ✅ dwd_order_detail_inc (DWD layer order detail incremental table)
  • ✅ dim_customer_zip (DIM layer customer slowly changing dimension table)
  • ❌ dim_trade_calendar (missing type suffix)

Type selection guide：

• Use _full: data needs a daily full snapshot, or the data volume is not large and the current full data needs to be queried
• Use _inc: large data volume, only incremental changes need to be stored (usually with stream processing)
• Use _zip: historical state changes need to be tracked, supporting queries at any point in time

Two integration scenarios

Scenario A: Integrate into the ODS layer

Prerequisite: the data is already in the lake/raw/daily/{target} directory (CSV format)

Step 1: Configure the data source

In dags/dw_config.yaml add the configuration:

sources:
  ods_{table_name}:
    path: "lake/raw/daily/{target}"
    format: "csv"

Step 2: Create the SQL file

In the dags/ods/ directory, create ods_{target}.sql：

SELECT
  field1,
  field2,
  CAST(STRPTIME(CAST(date_field AS VARCHAR), '%Y%m%d') AS DATE) AS trade_date,
  '${PARTITION_DATE}' AS dt
FROM tmp_ods_{target};

Key points：

• The file name must include the ods_ prefix (for example ods_fund_etf_spot.sql）
• Read from tmp_ods_{target} (temporary table automatically created by Airflow)
• Perform field type conversion
• Add ${PARTITION_DATE} as the partition column

Step 3: Create the Migration file

In catalog/migrations/ create {number}_ods_{target}_table.sql

Refer to the Migration file format in the example, including:

• Schema definition macro
• Seed parquet initialization
• DuckDB view definition
• Partition query macro

Step 4: Complete

• Airflow will automatically recognize new files
• Run the dw_ods DAG

---

Scenario B: Integrate into the DIM/DWD/ADS layer

Prerequisite: the SQL query statement is ready

Step 1: Analyze same-layer dependencies

Check whether the SQL depends on other tables in the same layer.

Example: if the SQL uses dim.other_table, then same-layer dependencies need to be configured.

Step 2: Create the SQL file

In dags/{layer}/ directory create {layer}_{table_name}_{type}.sql

⚠️ Important: The table name must include a type suffix (_full、_inc or _zip）

SELECT
  field1,
  field2,
  '${PARTITION_DATE}' AS dt
FROM ods.ods_source_table;

Key points：

• Use standard SQL to read from the lower layer
• Add ${PARTITION_DATE} as a partition column
• The tables used in the SQL are the dependencies
• Table name format：{layer}_{业务描述}_{类型后缀}

Step 3: Configure same-layer dependencies

In dags/dw_config.yaml add configuration:

table_dependencies:
  {layer}:
    {table_name}: [dependency_table1, dependency_table2]

Example：

table_dependencies:
  dim:
    dim_trade_calendar_full: [dim_other_table_full]

If there are no same-layer dependencies, you can skip this step.

Step 4: Create the Migration file

In catalog/migrations/ create {number}_{table_name}_table.sql

Refer to the Migration file format in the example, including:

• Schema definition macro
• Seed parquet initialization
• DuckDB view definition
• Partition query macro

Step 5: Complete

• Cross-layer dependencies are handled automatically by the framework
• Run the corresponding dw_{layer} DAG

---

Quick reference

File locations

Layer	SQL file location	Migration location
ODS	dags/ods/ods_{target}.sql	catalog/migrations/00XX_ods_{target}_table.sql
DIM	dags/dim/dim_{table}_{type}.sql	catalog/migrations/00XX_dim_{table}_{type}_table.sql
DWD	dags/dwd/dwd_{table}_{type}.sql	catalog/migrations/00XX_dwd_{table}_{type}_table.sql
ADS	dags/ads/ads_{table}_{type}.sql	catalog/migrations/00XX_ads_{table}_{type}_table.sql

Note：{type} is _full、_inc or _zip, except for the ODS layer.

Configuration file

dags/dw_config.yaml：

# ODS 数据源配置
sources:
  ods_fund_etf_spot:
    path: "lake/raw/daily/fund_etf_spot"
    format: "csv"

# 同层依赖配置（仅用于有同层依赖的情况）
table_dependencies:
  dim:
    dim_table_a_full: [dim_table_b_full]
  dwd:
    dwd_table_x_inc: [dwd_table_y_inc]

Migration naming rules

• Find existing migrations:ls catalog/migrations/
• Use the next number: e.g., if existing 0006_*.sql, the new file uses 0007_*.sql
• Format:00XX_{table_name}_table.sql

---

FAQ

Q: How do I determine what type suffix a table should use?

A:

• _full: Daily full snapshot, suitable for scenarios with a small data volume or that need to query the current full data (such as dimension tables, configuration tables)
• _inc: Stores only incremental data, suitable for scenarios with a large data volume and that only need change records (such as logs, transaction streams)
• _zip: Needs to track historical state changes and supports querying at any point in time (such as customer information, account status)

Q: How do I determine same-layer dependencies?

A: Check the FROM or JOIN tables in the SQL. If it references a table in the same layer (such as dim.xxx), it is a same-layer dependency.

Q: Do cross-layer dependencies need to be configured?

A: No. The framework automatically handles cross-layer dependencies through layer_dependencies .

Q: Must the SQL contain ${PARTITION_DATE} ?

A: Yes. All tables are partitioned tables, and the partition column must be added.

Q: What happens if the table name does not have a type suffix?

A: This will break the naming consistency of the data stack, causing:

• Inability to clearly identify the table's update strategy
• Possible confusion with full/incremental/zipper tables
• The correct type suffix must be specified when creating the table

Q: How do I test a newly integrated table?

A:

1. Run the corresponding DAG
2. Query verification:SELECT * FROM {layer}.{table_name} LIMIT 10

---

Example

View the complete example:

• Complete ODS integration process：examples/ods_integration.md
• Complete DIM integration process：examples/dim_integration.md

Quick reference：

• Command quick reference, SQL functions, data types, etc.:REFERENCE.md

---

Get started

Tell me:

1. Which layer do you want to integrate? (ODS/DIM/DWD/ADS)
2. What is the SQL? (Or describe the data source)
3. What are the dependencies?
4. What is the table type?(full _full / incremental _inc / zipper _zip）

I will guide you through the onboarding process.