PowerCenter repository XML parsed structurally. Mappings and workflows converted to BigQuery SQL and Dataform models. Tasks orchestrated with Cloud Composer or Dataform schedules.
Upload a mapping, get converted code →No patches, no compliance coverage. BigQuery eliminates both the ETL middleware license and the on-premise database it was writing to. Serverless, zero infrastructure.
PowerCenter Source Qualifier SQL overrides, filter conditions, and SQL transformations convert directly to BigQuery views and Dataform SQLX models. The patterns are structurally similar.
PowerCenter requires dedicated servers. BigQuery scales on demand with no cluster sizing, no DBA, no capacity forecasting.
An Aggregator with sorted input, group-by ports, and multiple aggregate expressions — converted to a BigQuery GROUP BY with computed columns.
-- Aggregator Transformation: AGG_SALES_SUMMARY
-- Sorted Input: Yes (pre-sorted by Region, Product)
-- Group By Ports: REGION, PRODUCT_LINE
-- Aggregate Ports:
-- TOTAL_REVENUE = SUM(SALE_AMOUNT)
-- AVG_DEAL_SIZE = AVG(SALE_AMOUNT)
-- MAX_SALE = MAX(SALE_AMOUNT)
-- DEAL_COUNT = COUNT(SALE_ID)
-- Output: SALES_SUMMARY_TGT
Sorted Input: Enabled
Group By: REGION, PRODUCT_LINE
Aggregates: SUM(SALE_AMOUNT) → TOTAL_REVENUE
AVG(SALE_AMOUNT) → AVG_DEAL_SIZE
MAX(SALE_AMOUNT) → MAX_SALE
COUNT(SALE_ID) → DEAL_COUNT
-- Aggregator → BigQuery GROUP BY
SELECT
region,
product_line,
SUM(sale_amount) AS total_revenue,
AVG(sale_amount) AS avg_deal_size,
MAX(sale_amount) AS max_sale,
COUNT(sale_id) AS deal_count
FROM sales_transactions
GROUP BY region, product_line
ORDER BY region, product_line;
Sorted input optimization handled natively by BigQuery's columnar engine. Group-by ports become GROUP BY columns. Aggregate ports map directly to SQL aggregate functions.
| PowerCenter Component | BigQuery Equivalent | Notes |
|---|---|---|
| Source Qualifier | BigQuery SQL SELECT | SQL override and filters preserved |
| Expression Transformation | BigQuery SQL expression / UDF | Built-in functions mapped |
| Aggregator | GROUP BY + aggregate functions | All aggregate functions supported |
| Joiner | BigQuery JOIN | All join types preserved |
| Lookup (connected + unconnected) | LEFT JOIN / subquery | Dynamic cache → subquery |
| Router | CASE / multiple CTEs | Per-group output via CTEs |
| Filter | WHERE clause | Filter conditions preserved |
| Update Strategy | BigQuery MERGE INTO | DD flags → merge predicates |
| Mapplet | Dataform macro / BigQuery UDF | Reusable, testable |
| Workflow | Cloud Composer DAG / Dataform schedule | Task DAG, failure handling |
| Session config | BigQuery job settings | Partitions, timeouts, labels |
| Target (relational) | BigQuery table / Dataform model | Dataset-managed |
Data Matching compares PowerCenter output against BigQuery output — row by row, column by column. Tolerance rules handle type differences between Oracle/SQL Server and BigQuery numeric types.
See how Data Matching works →PowerCenter repository XML is parsed once. The conversion engine generates BigQuery SQL and Dataform SQLX using the same structural analysis, same lineage, and same validation framework.
Upload a PowerCenter XML export. Get parsed lineage, BigQuery SQL, and a validation report.