# Using Astera Data Models in ETL Pipelines A deployed data model is available as a virtual database that can be used in ETL pipelines. In Astera Data Stack, you can use the tables in these virtual databases as sources or destinations in dataflows, which represent the ETL or ELT functionality of the product. ## **Video** {% embed url="" %} ## **Dataflows** The dataflow toolbox contains an extensive variety of objects that can be used as part of an ETL pipeline via the drag and drop interface of the dataflow designer. These include sources, destinations, transformations, and numerous other object types. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FaXvJloH2GIojaLqQGW2t%2F0.png?alt=media) You’ll notice a section titled *Data Warehouse* in the toolbox. This contains the *Fact Loader* and *Dimension Loader* objects, which can be used to load data into fact and dimension tables, respectively. We will discuss these objects in further detail at a later stage in the article. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FMJjzrRufZcRLPgYHRq7C%2F1.png?alt=media) Here is an example of a simple ETL pipeline designed in a dataflow: ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FrRIHWX8GEJYvfK92jn1W%2F2.png?alt=media) In this dataflow, we’ve done the following: * Extracted data from a database table using the *Database Table Source* object. * Transformed the incoming dataset by filtering out certain records using the *Filter Transformation* object. * Loaded the transformed dataset into an Excel worksheet using the *Excel Workbook Destination* object. ## **How to Use Astera Data Models in Dataflows** Multiple objects in the dataflow toolbox can be used to access data models that have been deployed on the server. These include: * *Database Table Source* * *Data Model Query Source* * *SQL Query Source* * *Fact Loader* * *Dimension Loader* Of these, the first three can be used as source objects and the other two can be used as destination objects. However, the *Database Connection* screen in the *Properties* configuration window is common in all of these objects. Let’s take a look at this *Database Table Source* object as an example. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FdX8sqJnhG1vJ4pbApX53%2F3.jpeg?alt=media) To access the properties of an object, right-click on the object header and select *Properties* from the context menu. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FdZ9MwAByw2lPj8oIDQd7%2F4.png?alt=media) A configuration window will appear on your screen. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2F0LQ5bsRs0veIdrCIY1AC%2F5.png?alt=media) The *Database Connection* screen on this window can be used to access the virtual databases representing the deployed data models. The *Data Provider* dropdown menu features a comprehensive list of data providers available from within Astera. Among these, you’ll notice an option by the name of *Astera Data Model*. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2F7Ege6zHnJAWr5xr4qku9%2F6.png?alt=media) On selecting the *Astera Data Model* option, a list of required credentials will appear on the screen. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2F3dEjKx4oMdPu9eKG3Iku%2F7.png?alt=media) * *User*: Here, you must provide your Astera username. The default username is *admin.* * *Password*: Here, you must provide your Astera password. The default password is *Admin123*. * *Server*: Here, you must provide the server connection in the given format. This is the server where you’ve deployed your data models. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2F8y7IEzpzDP5FSBnwGTDh%2F8.png?alt=media) Once you’ve filled in the required credentials, the *Database* dropdown menu will provide a list of deployments that are running on the server. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2Fl90Fl2RXBOKH7aaQkp64%2F9.png?alt=media) Choose the deployment that you wish to access. In this case, we’ll select *Demo\_Source*. You can test the connection by clicking on the *Test* icon. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2Fy1UXNHx4yJn7m7Y6MkiS%2F10.png?alt=media) A pop-up window will show you the test results. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FR7F0eu2D11GMWilAY7Gh%2F11.png?alt=media) This *Database Table Source* object now has access to the tables present in *Demo\_Source*. Similarly, you can connect to a deployed data model from the *Database Connection* screen in the *Data Model Query Source*, *SQL Query Source*, *Fact Loader*, and *Dimension Loader* objects. Click *Next* to move to the *Pick Source Table and Reading Option* screen. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FUm09DuwJxEgMC2jubD2r%2F12.png?alt=media) This screen is specific to the *Database Table Source* object and will vary according to the object you’re using. Here, you can pick a table from the virtual database representing the data model. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2F8lOeZEE1vwsKvugLBFe2%2F13.png?alt=media) For reference, here is a look at the *Demo\_Source* data model. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2F2KlPTpDb1jUsIMV33IRA%2F14.png?alt=media) In this case, we’ll select the *Customers* table from the dropdown menu. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FCI0HO3mDcHhq1CRsxHiE%2F15.png?alt=media) Click *OK* to close the configuration window. You’ll notice that the layout of the *Customers* table is now visible in the *Database Table Source* object. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FQAZhckidLwxMZYaYeSvM%2F16.png?alt=media) You’ve successfully accessed an *Astera Data Model* table in a dataflow and can use it as part of an ETL pipeline. ## **Fact Loader & Dimension Loader** As mentioned earlier, the *Fact Loader* and *Dimension Loader* objects can be used as destination objects to load data into fact and dimension tables, respectively. These tables are represented by fact and dimension entities in a dimensional model, which denotes the schema of a data warehouse. Collectively, the *Fact Loader* and *Dimension Loader* objects are used to populate a data warehouse. You can use the *Database Connection* screen in the properties of both objects to access the virtual databases representing deployed data models, in the same way as we saw earlier in the *Database Table Source* object. Once you’ve done so, click next to move to the *Pick Table* screen, where you can pick a table from the virtual database. Let’s assume that we’ve deployed the following dimensional model: ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FioritlvuJjN4q1c6X4GU%2F17.png?alt=media) The *Sale* entity represents the fact table, and the rest of the entities represent dimension tables. **Fact Loader – Pick Table** ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FthC47RttT3WSnkMtDLmL%2F18.png?alt=media) On expanding the *Pick Table* dropdown menu, you’ll notice that it only shows tables that are marked as facts in the dimensional model. In this case, the only table which fits that criterion is the *Sale* table. ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FzxAifeqHb1ZHMdb4FfRj%2F19.png?alt=media) ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FxBTW3YcbDfk9J2FLfnCQ%2F20.png?alt=media) Click *Next* to move to the *Layout Builder* screen. Here, you can view the layout of the fact table.
Click *OK* to the close the configuration window. The layout will now be visible in the *Fact Loader* object.
You can now make the required mappings from a relevant source object and run the dataflow in pushdown mode to populate the fact table. **Dimension Loader – Pick Table** The *Pick Table* dropdown menu in the *Dimension Loader* object only shows tables that are marked as dimensions in the dimensional model.
In this case, we’ll select the *Customer* table.
Click *Next* to move to the *Layout Builder* screen.
Here, you can view the layout of the dimension table. Click *OK* to close the configuration window. The layout will now be visible in the *Dimension Loader* object.
You can now make the required mappings from a relevant source object and run the dataflow to load data into the dimensional table. This concludes our discussion on using *Astera Data Models* in ETL pipelines.