Dataform partition by Pseudo column when creating a table

[yardenpel]

If I want to use _PARTITIONTIME / _PARTITIONDATE pseudo column in dataform when I create a new table - How can I do it? If I add it to the partitionBy configuration setting, it doesn't recognize this column.

[ms4446]

In BigQuery, ingestion-time partitioned tables offer powerful optimization features through the use of pseudo-columns:

• _PARTITIONTIME: This pseudo-column automatically stores the timestamp at which a row was ingested into the table. The timestamp is truncated to match the level of granularity of your partitioning (such as daily or hourly).

• _PARTITIONDATE: This pseudo-column provides a simplified representation of the _PARTITIONTIME, truncated to a DATE value. It's particularly useful for queries focused on daily partitions.

Dataform streamlines data transformations but doesn't directly manage the pseudo-columns _PARTITIONTIME or _PARTITIONDATE in partition configurations. Here's why:

• Implicit Columns: BigQuery automatically creates and manages these pseudo-columns when you set up ingestion-time partitioning.
• Dataform's Role: Dataform focuses on defining the data transformation logic and explicit table structure. BigQuery internally handles the partitioning details.

How to Utilize Pseudo-Columns in Dataform

1. Create the Partitioned Table: Use the partitionBy configuration within your Dataform SQLX file to define the desired partitioning, and optionally use clustering or partitionExpirationDays:

   --js config { 
        type: "table", 
        bigquery: { 
            partitionBy: "DATE(ingestion_time_column)", 
            clustering: ["customer_id"], 
            partitionExpirationDays: 365 
        }
    }
   

   • Replace ingestion_time_column with the appropriate timestamp column in your data.

2. Reference Pseudo-Columns in Queries: In your Dataform SQLX files, use _PARTITIONTIME or _PARTITIONDATE in your BigQuery SQL queries for partition-based operations:

   SELECT * FROM your_project.your_dataset.your_partitioned_table 
   WHERE _PARTITIONTIME >= TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY) -- Today's data
   

Key Points

• Implicit Definition: Don't include _PARTITIONTIME or _PARTITIONDATE in your Dataform table definitions. Let BigQuery handle them.
• Alignment: Make sure the column you use for partitionBy in Dataform matches the column used for partitioning within BigQuery.
• Efficient Queries: Dataform supports working with partitioned data, allowing you to optimize queries using the pseudo-columns.

[yardenpel]

When you say 

"Replace ingestion_time_column with the appropriate timestamp column in your data."

You mean to replace it with _partitiondate or just the date column in the data (which can be for example eventdate)?

"Reference Pseudo-Columns in Queries: In your Dataform SQLX files, use _PARTITIONTIME or _PARTITIONDATE in your BigQuery SQL queries for partition-based operations:"

When I try to reference "_PARTITIONDATE" in my WHERE, dataform says "_partitiondate is not recognized"... 😞 

[ms4446]

I apologize for any confusion. Let me clarify these points for better understanding:

1. Replacing ingestion_time_column:

   • When I mentioned "Replace ingestion_time_column with the appropriate timestamp column in your data," I meant you should use the actual date or timestamp column from your dataset that you intend to use for partitioning. This is not referring to _PARTITIONDATE or _PARTITIONTIME. For example, if your data has a column named event_date that you want to use to partition your table by date, you would use event_date in the partitionBy configuration. This column should be a part of your dataset and explicitly defined in your table schema.

2. Reference Pseudo-Columns in Queries:

   • If you're encountering issues with Dataform not recognizing _PARTITIONDATE when trying to reference it in your SQL queries, it's likely because _PARTITIONDATE and _PARTITIONTIME are pseudo-columns that BigQuery automatically provides for partitioned tables. However, their direct use is typically within BigQuery's query environment.

   • In Dataform, when you're working with partitioned tables, you usually don't need to reference _PARTITIONDATE or _PARTITIONTIME directly in your transformations or when defining your table configurations. Instead, you work with the actual columns of your data. The partitioning is handled by BigQuery based on the partitionBy configuration you set in Dataform, which should reference an actual column in your table, like event_date.

   • If you need to filter data based on the partition, you would typically use the actual data column that corresponds to the partition. For example, if your table is partitioned by event_date, you would use event_date in your WHERE clause to filter for specific dates.

• When configuring partitioning in Dataform, use an actual column from your dataset that represents the date or timestamp you want to partition by (e.g., event_date).
• For filtering or performing operations based on partitions within Dataform SQLX files, use the actual data columns that the table is partitioned on, rather than attempting to use pseudo-columns like _PARTITIONDATE or _PARTITIONTIME.

This approach aligns with how Dataform and BigQuery manage partitioned tables and ensures compatibility with Dataform's capabilities.

[yardenpel]

But using "event_date" in the partition by, when the table is created the partitioning doesn't utilize "_partitiondate" cost savings (for example, how counts on the partitioned field are free. so creating the table with Dataform instead of other tools makes quaring the final table up to x10 higher price and lower performance... 

(The Dataform itself is very simple, just joining 3 tables together and filtering out some rows).

[ms4446]

While Dataform significantly streamlines data transformations, understanding the nuances of maximizing partitioning in BigQuery is crucial for optimizing query performance and managing costs effectively. Here are the key considerations:

Ensure your partitioning column, such as event_date, has the correct data type. For date-based partitioning, event_date must be of the DATE type. This is essential for BigQuery to perform efficient partition pruning.

Remember, _PARTITIONTIME is relevant only for ingestion-time partitioned tables. For tables partitioned on a custom column like event_date, BigQuery relies on filters using that specific column for optimization, not _PARTITIONTIME.

Transformations and Partitioning

Complex transformations in Dataform can affect query performance, but they shouldn't prevent BigQuery from utilizing the partitioning column for optimization. The effectiveness hinges on using event_date appropriately in your query filters.

Troubleshooting and Optimization

• Verify Data Type: Double-check that event_date is consistently a DATE type throughout your data pipeline.

• Examine Generated SQL: Ensure your final SQL effectively uses event_date in filter clauses, such as WHERE event_date >= '2024-02-28'.

• Explicit Casting (if needed): If event_date is initially a TIMESTAMP, explicitly cast it to DATE within your Dataform's partitionBy definition.

• Dataform Support: Consult Dataform documentation, community forums, or support channels for insights into partition optimization strategies.

• BigQuery Best Practices: Align your partitioning strategy with your primary query patterns, incorporating BigQuery best practices for querying partitioned tables.

• Dataform SQLX Snippet: The relevant part of your transformation where partitionBy is defined.

• Sample Queries: BigQuery queries that are more expensive than expected.

Additional Considerations

• Clustering: Consider clustering alongside partitioning. Clustering organizes data within each partition based on specified columns, potentially further optimizing query performance and costs.

• Query Patterns: Maximize the benefits of partitioning by designing queries that leverage the partitioned structure, specifically filtering on the partitioning column.

• Monitoring and Adjusting Strategies: Regularly monitor query performance and costs, adjusting your partitioning and clustering strategies as needed based on insights from BigQuery's tools and reports.

• Testing Different Configurations: Experiment with different partitioning and clustering configurations in a development environment to identify the best performance outcomes for your specific use cases.

By addressing these considerations and continuously refining your approach, you can significantly enhance the efficiency and cost-effectiveness of your BigQuery operations within Dataform.