Dataform Engineering Fundamentals

When to Use

Use this skill for ANY Dataform work:

• Creating new SQLX transformations
• Modifying existing tables
• Adding data sources
• Troubleshooting pipeline failures
• "Quick" reports or ad-hoc analysis

Especially use when:

• Under time pressure or deadlines
• Stakeholders are waiting
• Working late at night (exhausted)
• Tempted to "just make it work"

Related Skills:

• Before designing new features: Use superpowers:brainstorming to refine requirements into clear designs before writing any code
• When troubleshooting failures: Use superpowers:systematic-debugging for structured problem-solving
• When debugging complex issues: Use superpowers:root-cause-tracing to trace errors back to their source
• When writing documentation, commit messages, or any prose: Use elements-of-style:writing-clearly-and-concisely to apply Strunk's timeless writing rules for clarity and conciseness

Non-Negotiable Safety Practices

These are ALWAYS required. No exceptions for deadlines, urgency, or "simple" tasks:

1. Always Use --schema-suffix dev for Testing

# WRONG: Testing in production
dataform run --actions my_table

# CORRECT: Test in dev first
dataform run --schema-suffix dev --actions my_table

Why: Writes to schema_dev.my_table instead of schema_prod.my_table (or adds _dev suffix based on your configuration). Allows safe testing without impacting production data or dashboards.

2. Always Use --dry-run Before Execution

# Check compilation
dataform compile

# Validate SQL without executing
dataform run --schema-suffix dev --dry-run --actions my_table

# Only then execute
dataform run --schema-suffix dev --actions my_table

Why: Catches SQL errors, missing dependencies, and cost estimation before using BigQuery slots.

3. Source Declarations Before ref()

WRONG: Using tables without source declarations

-- This will break dependency tracking
FROM `project_id.external_schema.table_name`

CORRECT: Create source declaration first

-- definitions/sources/external_system/table_name.sqlx
config {
  type: "declaration",
  database: "project_id",
  schema: "external_schema",
  name: "table_name"
}

-- Then reference it
FROM ${ref("table_name")}

4. ALWAYS Use ${ref()} - NEVER Hardcoded Table Paths

WRONG: Hardcoded table paths

-- NEVER do this
FROM `project.external_schema.table_name`
FROM `project.reporting_schema.customer_metrics`
SELECT * FROM project.source_schema.customers

CORRECT: Always use ${ref()}

-- Create source declaration first, then reference
FROM ${ref("table_name")}
FROM ${ref("customer_metrics")}
SELECT * FROM ${ref("customers")}

Why:

• Dataform tracks dependencies automatically with ref()
• Hardcoded paths break dependency graphs
• ref() enables --schema-suffix to work correctly
• Refactoring is easier when references are managed

Exception: None. There is NO valid reason to use hardcoded table paths in SQLX files.

5. Proper ref() Syntax

WRONG: Including schema in ref() unnecessarily

FROM ${ref("external_schema", "sales_order")}

CORRECT: Use single argument when source declared

FROM ${ref("sales_order")}

When to use two-argument ref():

• Source declarations that haven't been imported yet
• Special schema architectures where schema suffix behavior needs explicit control
• Cross-database references in multi-project setups

Why:

• Single-argument ref() works for most tables
• Dataform resolves the full path from source declarations
• Two-argument form is only needed for special cases

6. Basic Validation Queries

Always verify your output:

# Check row counts
bq query --use_legacy_sql=false \
  "SELECT COUNT(*) FROM \`project.schema_dev.my_table\`"

# Check for nulls in critical fields
bq query --use_legacy_sql=false \
  "SELECT COUNT(*) FROM \`project.schema_dev.my_table\`
   WHERE key_field IS NULL"

Why: Catches silent failures (empty tables, null values, bad joins) immediately.

Architecture Patterns (Not Optional)

Even for "quick" work, follow these patterns:

Reference: For detailed guidance on repository structure, naming conventions, and managing large workflows, see https://cloud.google.com/dataform/docs/best-practices-repositories

Layered Structure

definitions/
  sources/          # External data declarations
  intermediate/     # Transformations and business logic
  output/           # Final tables for consumption
    reports/        # Reporting tables
    marts/          # Data marts for specific use cases

Don't: Create monolithic queries directly in output layer

Do: Break into intermediate steps for reusability and testing

Incremental vs Full Refresh

config {
  type: "incremental",
  uniqueKey: "order_id",
  bigquery: {
    partitionBy: "DATE(order_date)",
    clusterBy: ["customer_id", "product_id"]
  }
}

When to use incremental: Tables that grow daily (events, transactions, logs)

When to use full refresh: Small dimension tables, aggregations with lookback windows

Dataform Assertions

config {
  type: "table",
  assertions: {
    uniqueKey: ["call_id"],
    nonNull: ["customer_phone_number", "start_time"],
    rowConditions: ["duration >= 0"]
  }
}

Why: Catches data quality issues automatically during pipeline runs.

Source Declarations: Prefer .sqlx Files

STRONGLY PREFER: .sqlx files for ALL new declarations

-- definitions/sources/external_system/table_name.sqlx
config {
  type: "declaration",
  database: "project_id",
  schema: "external_schema",
  name: "table_name",
  columns: {
    id: "Unique identifier for records",
    // ... more columns
  }
}

ACCEPTABLE (legacy only): .js files for existing declarations

// definitions/sources/legacy_declarations.js (existing file)
declare({
  database: "project_id",
  schema: "source_schema",
  name: "customers"
});

Rule: ALL NEW source declarations MUST be .sqlx files. Existing .js declarations can remain but should be migrated to .sqlx when modifying them.

Why: .sqlx files support column documentation, are more maintainable, and integrate better with Dataform's dependency tracking.

Schema Configuration Rules

Operations: Files in definitions/operations/ should NOT include schema: config

-- CORRECT
config {
  type: "operations",
  tags: ["daily"]
}

-- WRONG
config {
  type: "operations",
  schema: "dataform",  // DON'T specify schema
  tags: ["daily"]
}

Tests/Assertions: Files in definitions/test/ should NOT include schema: config

-- CORRECT
config {
  type: "assertion",
  description: "Check for duplicates"
}

-- WRONG
config {
  type: "assertion",
  schema: "dataform_assertions",  // DON'T specify schema
  description: "Check for duplicates"
}

Why: Operations live in the default dataform schema and assertions live in dataform_assertions schema (configured in workflow_settings.yaml). Specifying schema explicitly can cause conflicts.

Documentation Standards (Non-Negotiable)

All tables with type: "table" MUST include comprehensive columns: {} documentation in the config block.

Writing Clear Documentation: When writing column descriptions, commit messages, or any prose that humans will read, use elements-of-style:writing-clearly-and-concisely to ensure clarity and conciseness.

columns: {} Requirement

WRONG: Table without column documentation

config {
  type: "table",
  schema: "reporting"
}

SELECT customer_id, total_revenue FROM ${ref("orders")}

CORRECT: Complete column documentation

config {
  type: "table",
  schema: "reporting",
  columns: {
    customer_id: "Unique customer identifier from source system",
    total_revenue: "Sum of all order amounts in USD, excluding refunds"
  }
}

SELECT customer_id, total_revenue FROM ${ref("orders")}

Where to Get Column Descriptions

Column descriptions should be derived from:

1. Source Declarations: Copy descriptions from upstream source tables
2. Third-party Documentation: Use official API documentation for external systems (CRM, ERP, analytics platforms)
3. Business Logic: Document calculated fields, transformations, and business rules
4. BI Tool Requirements: Include context that dashboard builders and analysts need
5. Dataform Documentation: Reference https://cloud.google.com/dataform/docs for Dataform-specific configuration and built-in functions

Example with ERP source documentation:

config {
  type: "table",
  schema: "reporting",
  columns: {
    customer_id: "Unique customer identifier from ERP system",
    customer_name: "Customer legal business name",
    account_group: "Customer classification code for account management",
    credit_limit: "Maximum allowed credit in USD"
  }
}

Source Declarations Should Include columns: {}

When applicable, source declarations should also document columns:

-- definitions/sources/external_api/events.sqlx
config {
  type: "declaration",
  database: "project_id",
  schema: "external_api",
  name: "events",
  description: "Event records from external API with enriched data",
  columns: {
    event_id: "Unique event identifier from API",
    user_id: "User identifier who triggered the event",
    event_type: "Type of event (click, view, purchase, etc.)",
    timestamp: "UTC timestamp when event occurred",
    properties: "JSON object containing event-specific properties"
  }
}

Why document sources: Downstream tables inherit and extend these descriptions, creating documentation consistency across the pipeline.

Test-Driven Development (TDD) Workflow

REQUIRED BACKGROUND: You MUST understand and follow superpowers:test-driven-development

BEFORE TDD: When creating NEW features with unclear requirements, use superpowers:brainstorming FIRST to refine rough ideas into clear designs. Only start TDD once you have a clear understanding of what needs to be built.

When creating NEW features or tables in Dataform, apply the TDD cycle:

1. RED: Write tests first, watch them fail
2. GREEN: Write minimal code to make tests pass
3. REFACTOR: Clean up while keeping tests passing

The superpowers:test-driven-development skill provides the foundational TDD principles. This section adapts those principles specifically for Dataform tables and SQLX files.

TDD for Dataform Tables

WRONG: Implementation-first approach

1. Write SQLX transformation
2. Test manually with bq query
3. "It works, ship it"

CORRECT: Test-first approach

1. Write data quality assertions first
2. Write unit tests for business logic
3. Run tests - they should FAIL (table doesn't exist yet)
4. Write SQLX transformation
5. Run tests - they should PASS
6. Refactor transformation if needed

Example TDD Workflow

Step 1: Write assertions first (definitions/assertions/assert_customer_metrics.sqlx)

config {
  type: "assertion",
  description: "Customer metrics must have valid data"
}

-- This WILL fail initially (table doesn't exist)
SELECT 'Duplicate customer_id' AS test
FROM ${ref("customer_metrics")}
GROUP BY customer_id
HAVING COUNT(*) > 1

UNION ALL

SELECT 'Negative lifetime value' AS test
FROM ${ref("customer_metrics")}
WHERE lifetime_value < 0

Step 2: Run tests - watch them fail

dataform run --schema-suffix dev --run-tests --actions assert_customer_metrics
# ERROR: Table customer_metrics does not exist ✓ EXPECTED

Step 3: Write minimal implementation (definitions/output/reports/customer_metrics.sqlx)

config {
  type: "table",
  schema: "reporting",
  columns: {
    customer_id: "Unique customer identifier",
    lifetime_value: "Total revenue from customer in USD"
  }
}

SELECT
  customer_id,
  SUM(order_total) AS lifetime_value
FROM ${ref("orders")}
GROUP BY customer_id

Step 4: Run tests - watch them pass

dataform run --schema-suffix dev --actions customer_metrics
dataform run --schema-suffix dev --run-tests --actions assert_customer_metrics
# No rows returned ✓ TESTS PASS

Why TDD Matters in Dataform

• Catches bugs before production: Tests fail when logic is wrong
• Documents expected behavior: Tests show what the table should do
• Prevents regressions: Future changes won't break existing logic
• Faster debugging: Test failures pinpoint exact issues
• Confidence in refactoring: Change code safely with test coverage

TDD Red Flags

If you're thinking:

• "I'll write tests after the implementation" → NO, write tests FIRST
• "Tests are overkill for this simple table" → NO, simple tables break too
• "I'll test manually with bq query" → NO, manual tests aren't repeatable
• "Tests after achieve the same result" → NO, tests-first catches design flaws

All of these mean: You're skipping TDD. Write tests first, then implementation.

See also: The superpowers:test-driven-development skill contains additional TDD rationalizations and red flags that apply universally to all code, including Dataform SQLX files.

Quick Reference

Common Rationalizations (And Why They're Wrong)

Red Flags - STOP Immediately

If you're thinking any of these thoughts, STOP and follow the skill:

• "I'll skip --schema-suffix dev this once"
• "No time for --dry-run"
• "I'll just hardcode the table path instead of using ${ref()}"
• "I'll use backticks instead of ${ref()} (it's faster)"
• "I'll just create one file instead of intermediate layers"
• "Tests are optional for ad-hoc work"
• "I'll write tests after the implementation"
• "I'll add column documentation later"
• "This table doesn't need columns: {} block"
• "I'll use a .js file for declarations (faster to write)"
• "I'll add schema: config to this operation/test file"
• "I'll fix the technical debt later"
• "This is different because [business reason]"

All of these mean: You're about to create problems. Follow the non-negotiable practices.

Common Mistakes

Mistake 1: Using tables before declaring sources

-- WRONG: Direct table reference
FROM `project.external_schema.contacts`

-- CORRECT: Declare source first
FROM ${ref("contacts")}

Fix: Create source declaration in definitions/sources/ before using in queries.

Mistake 2: Mixing ref() with manual schema qualification

-- WRONG: When source exists
FROM ${ref("dataset_name", "table_name")}

-- CORRECT
FROM ${ref("table_name")}

Fix: Use single-argument ref() when source declaration exists. Dataform handles full path resolution.

Mistake 3: Skipping dev testing under pressure

Symptom: "I'll deploy directly to production because it's urgent"

Fix: --schema-suffix dev takes 30 seconds longer than production deploy. Production failures take hours to fix.

Mistake 4: Creating monolithic transformations

Symptom: 200-line SQLX file with 5 CTEs doing multiple transformations

Fix: Break into intermediate tables. Each table should do ONE transformation clearly.

Mistake 5: Missing columns: {} documentation

Symptom: Table config without column descriptions

Fix: Add comprehensive columns: {} block to EVERY table with type: "table". Get descriptions from source docs, upstream tables, or business logic.

Mistake 6: Writing implementation before tests

Symptom: Creating SQLX file, then adding assertions afterward (or never)

Fix: Follow TDD cycle - write assertions first, watch them fail, write implementation, watch tests pass.

Mistake 7: Using .js files for NEW source declarations

Symptom: Creating NEW definitions/sources/sources.js files with declare() functions

Fix: Create .sqlx files in definitions/sources/[system]/[table].sqlx with proper config blocks and column documentation. Existing .js files can remain until they need modification.

Mistake 8: Hardcoded table paths instead of ${ref()}

Symptom: Using backtick-quoted table paths in queries

FROM `project.external_api.events`
SELECT * FROM project.source_schema.customers

Fix: ALWAYS use ${ref()} after creating source declarations

FROM ${ref("events")}
SELECT * FROM ${ref("customers")}

Why critical: Hardcoded paths break dependency tracking, prevent --schema-suffix from working, and make refactoring impossible.

Mistake 9: Adding schema: config to operations or tests

Symptom: Operations or test files with explicit schema configuration

config {
  type: "operations",
  schema: "dataform",  // Wrong!
}

Fix: Remove schema: config - operations and tests use default schemas from workflow_settings.yaml

Time Pressure Protocol

When under extreme time pressure (board meeting in 2 hours, production down, stakeholder waiting):

1. ✅ Still use dev testing - 3 minutes saves 60 minutes debugging
2. ✅ Still use --dry-run - Catches errors before wasting BigQuery slots
3. ✅ Still create source declarations - Broken dependencies waste MORE time
4. ✅ Still add columns: {} documentation - Takes 2 minutes, saves hours explaining to Looker users
5. ✅ Still write tests first (TDD) - 5 minutes writing assertions prevents production bugs
6. ✅ Still do basic validation - Wrong results are worse than delayed results
7. ⚠️ Can skip: Extensive documentation files, peer review, performance optimization
8. ⚠️ Must document: Tag as "technical_debt", create TODO with follow-up tasks

The bottom line: Safety practices save time. Skipping them wastes time. Even under pressure.

Troubleshooting Dataform Errors

RECOMMENDED APPROACH: When encountering ANY bug, test failure, or unexpected behavior, use superpowers:systematic-debugging before attempting fixes. For errors deep in execution or cascading failures, use superpowers:root-cause-tracing to identify the original trigger.

Official Reference: For Dataform-specific errors, configuration issues, or syntax questions, consult https://cloud.google.com/dataform/docs

"Table not found" errors

Quick fixes:

1. Check source declaration exists in definitions/sources/
2. Verify ref() syntax (single argument if source exists)
3. Check schema/database match in source config
4. Run dataform compile to see resolved SQL

If issue persists: Use superpowers:systematic-debugging for structured root cause investigation.

Dependency cycle errors

Quick fixes:

1. Use ${ref("table_name")} not direct table references
2. Check for circular dependencies (A → B → A)
3. Review dependency graph in Dataform UI

If issue persists: Use superpowers:root-cause-tracing to trace the dependency chain back to the source of the cycle.

Timeout errors

Quick fixes:

1. Add partitioning/clustering to config
2. Use incremental updates instead of full refresh
3. Break large transformations into smaller intermediate tables

If issue persists: Use superpowers:systematic-debugging to investigate query performance systematically.

Real-World Impact

Scenario: "Quick" report created without source declarations, skipping dev testing.

Cost:

• 10 minutes saved initially
• 2 hours debugging "table not found" errors in production
• 3 stakeholder escalations
• 1 broken morning dashboard
• Net loss: 110 minutes

With proper practices:

• 13 minutes total (3 extra for dev testing)
• Zero production issues
• Zero escalations
• Net gain: 97 minutes

Takeaway: Discipline is faster than shortcuts.