Now you have your BigQuery project and warehouse credentials, you’re going to set up your Dataform project using Dataform Web. In this step you'll create a project, connect your warehouse and build out your first dataset.
To create a new project in Dataform:
Create a Dataform account here.
Follow the sign up flow:
As part of the project creation flow you'll be asked to connect to a warehouse. You’re going to use the credentials you generated in the earlier part of this tutorial to connect:
On the Configure Warehouse modal click
Google BigQuery from the drop down menu.
Enter your Project ID.
Browse for the service account key JSON file you created in the
Setting Up part of this tutorial and upload it.
Check your connection is working:
Test Connectionto check that the connection is working.
Now that you've created your project and connected a warehouse, you're ready to start defining your data model.
Make sure you are in a development branch:
Create a new dataset:
New Datasetbutton in the left hand side bar.
Define the dataset:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
SELECT orders.date AS order_date, orders.id AS id, orders.customer_id AS customer_id, orders.status AS order_status, charges.status AS payment_status, charges.payment_method AS payment_method, SUM(orders.item_count) AS item_count, SUM(charges.amount) AS amount FROM dataform-demos.dataform_tutorial.shopify_orders AS orders LEFT JOIN dataform-demos.dataform_tutorial.stripe_payments AS charges ON orders.payment_id = charges.id WHERE orders.id <= 999 AND orders.item_count > 0 AND orders.status <> 'internal' AND charges.payment_method IN ('debit_card', 'subscription', 'coupon') GROUP BY 1, 2, 3, 4, 5, 6
order_stats.sqlxbelow the config block.
Preview Resultsto check that the data looks correct
Create the table in your warehouse:
Publish Tableto create the table in your warehouse
Check Run Logs:
Run Logsby clicking on the hamburger menu in the top left hand corner.
Run Logsyou can see all the past runs in the project. You can see their status, as well as how and when they were triggered.
You now have a new table called
order_stats which has been created in your warehouse and you're ready to add to your data model!
For more detailed info on publishing datasets in Dataform, see our [docs].(https://docs.dataform.co/guides/datasets/publish)