Upon downloading the DLC, a courier will arrive while you are inside a city and deliver you two letters: one advertising the Honorhall Orphanage in Riften and the other informing you that you can now build a children's bedroom to accommodate the child.
Before even bothering to head to Riften, make sure you own a home and have built the children's bedroom. If you're adding it to an existing home (pre-DLC) it will replace your alchemical lab and cost 250 Gold (except in Windhelm and Solitude, which will not overwrite the lab but costs 3000 Gold). Otherwise, you'll need to manually put in a child's bed and child's chest at any of the DLC homes.
Now that you have your house and children's room, visit the orphanage and tell Constance Michel that you'd like to adopt. She'll ask you to pick a home (probably only one choice unless you build multiple children's rooms in multiple homes) as well as a child, so talk to one you like afterward and the achievement will unlock when you accept them.
A full list of adoptable children can be found here. Note that you do not have to have your wife at the home or even be married to do this!